  Feb 19

## Value at Risk xls

Posted by abiao at 18:04 | Code » VBA/Excel | Comments(32) | Reads(88767)
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 0 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: 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.
Excel: Download

Macro Code: Download

Tags:
hello,
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.
Hi,

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

Surely this just equals 1 and is unnecessary.

Cheers,
Ali
Ali, right, that step is unnecessary, as it equals to one. I just tried to annualize and de-annualize.

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
 Add a comment Emots Enable HTML Enable UBB Enable Emots Hidden Remember Nickname   Password   Optional Site URI   Email   [Register]         