Feb
19
Value at Risk xls
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
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
People viewing this post also viewed:
Hot posts:
15 Incredibly Stupid Ways People Made Their Millions
Ino.com: Don't Join Marketclub until You Read This MarketClub Reviews
Online stock practice
World Changing Mathematical Discoveries
Value at Risk xls
Random posts:
Markov Regime Switching Models
CDS Standard Model
Constant Maturity Swap (CMS) option pricing
Matlab implementation of cointegration tests
My blog is published at Amazon Kindle
Hot posts:
Random posts:
SIMTOOLS and FORMLIST Excel add-ins
Financial Analytics & Risk Mana
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
Thank you very much!
Do you know where I can find a good paper about GBM process linked to Monte Carlo method?
Thanks again
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
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
Thanks a lot for sharing this application.
Cheers,
Z
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