Quantitative finance collector
C++ Matlab VBA/Excel Java Mathematica R/Splus Net Code Site Other
Feb 19

Value at Risk xls

Posted by abiao at 18:04 | Code » VBA/Excel | Comments(32) | Reads(71172)
A blog reader wrote me an email few weeks ago regarding if it is possible to share an excel for Value at Risk xls calculation, I didn't notice that email until recently, sorry for that. So this afternoon I created a naive excel xls file with VBA macro code available.

Before checking the excel, few sentences explaining Value at Risk calculation are necessary: Value at Risk (VaR) is the maximum loss not exceeded with a given confidence level 0Open in new window

Given confidence level and horizon day, the crucial point for quantile estimation is to find a suitable distribution of underlying risk factors, once distribution is known, VaR and ES can be easily calculated by the definition. Mina and Xiao (2001) explains in detail three popular methods to compute VaR: parametric approach (the simplest one is delta-normal), Monte Carlo simulation (MC) and Historical simulation (HS). I am not going to talk in detail how to calculate them as interested reader can refer to the paper or the book by John Hull, a short comparison of the above-mentioned three approaches are listed below,
• HS
– easy to implement, no distribution assumption;
– highly depends on the choice of sample data length, VaR result does not vary often or changes suddenly.
• MC
– flexible, almost suitable for any distribution;
– assumption of risk factors return required, time consuming.
• Parametric
– easy to implement, not hard to understand;
– assumption of risk factors return required, too simple assumption or too exotic to implement.

Attached is the ValueatRisk.xls file, where for simplicity, I treat volatility as normal standard deviation, Value at Risk is computed by delta-normal, monte carlo simulation and historical simultion for any single equity, you have to make sure internet is accessible for downloading data from Yahoo. Please keep in mind this file is created for illustration only, use at your own risk.

To use it, you need to fill in several parameters including:
Open in new window
where you can change stock symbol "IBM" to any stock you want, as long as its trading prices are available at Yahoo finance.

Please let me know any error, cheers.

Macro Code:

ParisGirl111 Email Homepage
Thanks for the excel download. :)
I'm trying to understand your macro. I have 2 questions (they might be easy for you!)
1. Why do you use a log difference and not a standard one (Return Calculation)
2.Could you explain this Formula?
"simreturn(i) = Exp((rf - 0.5 * vol * vol * 250) / 250 + vol * Sqr(250) * Sqr(1 / 250) * Application.NormInv(Rnd(), 0, 1)) - 1"
Thanks a lot

Have a nice day
Hi Gustin, 1, log return is preferred when we want to model a stock process, indeed log return and arithmetic return have similar results;  2, simulate stock return assuming it follows GBM process.
Hello again,
Thank you very much!
Do you know where I can find a good paper about GBM process linked to Monte Carlo method?
Thanks again
John Hull's book is a good one. Option, future and other derivatives.

Can you explain why in the simreturn calculation you do Sqr(250)*Sqr(1/250)?

Surely this just equals 1 and is unnecessary.

Ali, right, that step is unnecessary, as it equals to one. I just tried to annualize and de-annualize.
Thanks for your prompt reply.

Also, can I ask why you do the "-0.5" part in the simreturn calculation? I understand the rest of it, but can't quite get my head around that part.
Cheers, Ali
check this page http://en.wikipedia.org/wiki/Geometric_Brownian_motion.
Hi, if the relative difference is (S_t - S_t-1)/(S_t-1), which in turn is very close to ln(S_t/S_t-1) is it then correct to use the function log instead of the function ln as you did in your code? Since log(S_t/S_t-1)<>ln(S_t/S_t-1).

Thanks a lot for sharing this application.
Oops, how could I make such a stupid mistake, thanks a lot, zenzej, it should be ln instead of log, I mixed them up. Sorry for that, now I correct and re-upload it, apologize.
I got a problem when I open the this excel file. It showed "can't find the module" so that I can't see the code.  Do you know how to sovle it?  or could you paste the vba code on text? thanks
I re-upload the file and also attach the Macro code in a txt file.
I have a question: Do you know about the cash Flow at risk? the yare alike. And i am looking for it. thx a lot.
Do you mean cash flow mapping? it is more involved although can be done.
Pages: 1/3 First page 1 2 3 Next page Final page
Add a comment
Enable HTML
Enable UBB
Enable Emots
Nickname   Password   Optional
Site URI   Email   [Register]