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

Before checking the excel, few sentences explaining

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

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:

Macro Code:

Hot posts:

15 Incredibly Stupid Ways People Made Their Millions

Online stock practice

Ino.com: Don't Join Marketclub until You Read This MarketClub Reviews

World Changing Mathematical Discoveries

Value at Risk xls

Random posts:

Parameters estimation of GARCH model

Binomial Tree

A Simple Trick to Avoid Oscillation in Binomial Trees

Maximize Sharpe Ratio or Geometric Mean?

The Quants: How a New Breed of Math Whizzes Conquered Wall Street and Nearly Destroyed It

**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 0Given 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:

Random posts:

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