Does anyone have VaR

damn , could not Save Target As...the file probably been removed

 

Try the following link:

http://www.gummy-stuff.org/SS/VaR.xls


I don't understand why the Save As link is broken. You can search the website from the mainpage for VaR, and find multiple spreadsheets that work.

- Greg

 

The index of all the spreadsheets available can be found here:

http://www.gummy-stuff.org/SS/

 

Greg , really appreciate your help. Thanks so much.
IV

 

Code:

'************************************************************
' VAR_sd standard deviation of a portfolio                               *
' a weights
' s standard deviations
' r correlations
'************************************************************
Private Function VAR_sd(weightArray As Range, sdArray As Range, correlArray As Range) As Double
d = correlArray.Columns.Count
ReDim r(d, d) As Double
ReDim a(d) As Double
ReDim sd(d) As Double
For i = 1 To d
    For j = 1 To d
        r(i, j) = Application.Index(correlArray, i, j)
    Next j
Next i
For i = 1 To d
    a(i) = Application.Index(weightArray, i, 1)
    sd(i) = Application.Index(sdArray, i, 1)
Next i
' main routine
Dim ss As Double
ss = 0
For i = 1 To d
    ss = ss + a(i) ^ 2 * sd(i) ^ 2
Next i
For i = 1 To d
    For j = 1 To d
        If j < i Then ss = ss + 2 * r(i, j) * a(i) * a(j) * sd(i) * sd(j)
    Next j
Next i
VAR_sd = Sqr(ss)
End Function

If you want 10 days 99% VAR then multiply with 2.33*sqrt(10)

 

Dude... can't believe you didn't ask on FT! No formula for you!

 

http://www.quantonline.co.za/Articles/article_volatility.htm

 

OK...here where I am so far : at this point I am looking at very simple output like : "One day VaR with 99% of confidence is XXXXX $..."

the inputs/steps are :

1. Calculating Annual vols per every stock ( using 20d MA) .
2. Assigning $ weight ( # shares , cost) to get combined annual vols for on portfolio level.
3. Confidence ratio/coefficient/multiplier. Still not clear , wdf is it ? # of SD's ?

so this way I am ignoring the correl's calculations between the stocks. Can I do it ? Can I "trust" the final output based on combined/average volty on portfolio level ?

TIA

 

Correlation is an important factor in portfolio volatility/VaR. 99% VaR is 2.33 times the st.dev. of the portfolio.

 

so if portfolio of 100k trades with annual volatility of 30 than "One day VaR with 99% of confidence " is :

(30/16 (square root of 256) * 2.33 )*100,000=4380 $ ?