Historical VaR

[[email protected]]

Hi, let me bother with a question about historical VaR.
I want to know how to extend the calculation shown in "Intro-to-VaR.xls" for a complete portfolio with several assets (both in nominal and inflation indexed).
In doing so i know i need to calculate the sumproduct for all days for all assets of asset weights & daily returns, and then calculate the desired percentile. But i'm not sure if the sum of weights must equal 100% or not? Can someone give me access to a historical VaR calculation for a complete portfolio (with several assets). Thanks much!

 

[David Harper CFA FRM]

Hi Josi,

We don't have that exactly, but 4a.3 (Structured MCS) within http://www.bionicturtle.com/how-to/spreadsheet/p1.t4.a-xls-bundle
... conducts a 5-factor (e.g., 5-asset) monte carlo simulation incorporating a correlation matrix (Cholesky Decomposition)
... actually, it does not have weights, come to think of it. I do have a 2-asset example (jorion) which utilizes matrix, so can be extended: https://www.dropbox.com/s/wb3o7f667v9g4be/8.b.2_Jorion_analytical_VaR_v2.xls

thanks,

 

[Ashok_Kothavle]

Dear Mr David,

Where can I get the following excels?

http://www.bionicturtle.com/how-to/spreadsheet/p1.t4.a-xls-bundle

and

https://www.dropbox.com/s/wb3o7f667v9g4be/8.b.2_Jorion_analytical_VaR_v2.xls

Unfortunately both links are not working. I am trying to learn VaR computation using Monte Carlo Simulation for say 50 risk factors (e.g. say equity instruments) and need to learn the application of Cholesky Decomposition.

With warm regards
Ashok Kothavle

 

[David Harper CFA FRM]

Hi @Ashok_Kothavle

• I moved the Cholesky to a T4 XLS but here are the two sheets at https://www.dropbox.com/s/un7ooixdel0wenz/0423-cholesky.xlsx?dl=0
  
• Here is a version of Jorion's analytical (portfolio) VaR https://www.dropbox.com/s/x393d8y66w6mdz5/8.b.2_Jorion_analytical_VaR_v2.xls?dl=0

I hope these are helpful, good luck!

 

[Ashok_Kothavle]

Dear Mr David

Thanks a lot for your great help. As usual very very informative and useful excels.

Regards

Ashok

 

[Ashok_Kothavle]

Dear Mr David,

I was trying to understand the excel 0423-cholesky.xlsx.

If possible, can you please clarify my following doubts please.

Suppose as given in excel, we have 5 risk factors. Assuming we have (say) 251 trading days and hence 250 daily returns. For each of these 5 risk factor returns, we will have 5 averages of daily returns and also 5 standard deviations of daily returns.

“In 1b. Returns” You have defined Expected Excess Returns.


Q 1 : What are these excess returns? Does it represent 5 simple averages of daily returns (which are annualized), or do they represent an excess of returns over some benchmark? If they represent excess over benchmark, we may be dealing with various risk factors e.g. tenor-wise interest rates, various exchange rates, various equity instrument prices etc. and hence for each risk factor we will have different bench marks. Isn’t it bit cumbersome? Or my interpretation is totally wrong?

Q.2 : If my daily return is (say) ‘m’, how do I calculate annualized return? Is it 250 * m?

Q.3 : Annualized standard deviation = Square root(250) * daily standard deviation? Am I right?

Q. 4 : We are scaling the Cholesky Decomposition to Holding period (in the example 10 days), but I understand the basic correlation matrix is based on daily returns.

 

[David Harper CFA FRM]

Hi @Ashok_Kothavle

1. I am using "excess" in "excess returns" to refer to returns in excess of the risk-free rate. See recent discussion here https://forum.bionicturtle.com/threads/p2-t8-19-hedge-fund-performance-evaluation.5585/#post-48914 (I believe also there is a theoretical justification w.r.t VaR: as theoretically a future VaR might be discounted to the present, using excess returns arguably does that already). You are correct about benchmarks, that would complicate this; it's definitely doable, but it is not in this XLS
2. Yes, return scales linearly. This model currently assumes 10 day horizon (input cell D10), so notice that row 20 returns a 10-day expected excess return = (excess annual return) * 10/250. In general, if the current return is r(Δt), then scale to r(ΔT) with r(ΔT) = r(Δt) * ΔT/Δt
3. Yes, because standard deviation scales with square root of time, so if the current volatility is σ(Δt), then scale to σ(ΔT) with σ(ΔT) = σ(Δt) * sqrt(ΔT/Δt). For example, if daily volatility = 1.0%, scale to 250 days σ(250) = σ(1) * sqrt(250/1) = 1.0%*sqrt(250); or if current volatility is 30.0% per annum, then scale to daily with same σ(ΔT) = σ(Δt) * sqrt(ΔT/Δt) --> σ(1) = σ(250) * sqrt(1/250) = 30.0% * sqrt(1/250)
4. The correlation matrix, as labelled, is unitless (correlation is unitless). It feeds into the second sheet which is the matrix version of covariance = σ*σ*ρ; this is scaled (from annual to ten days) in the second sheet. The second sheet returns matrx A' as a time-scaled (i.e., to ten days) matrix which shockst the standard normals into correlated random normals with the given volatilities (and given returns). I hope that's helpful!

 

[Ashok_Kothavle]

Dear Mr David,

Thanks a lot for an elaborate reply. Appreciate the same and your this helping nature make us feel very very connected to Bionic turtle.

Thanks again

Regards
Ashok