# Math Help - Historical simulation of value at risk

1. ## Historical simulation of value at risk

Suppose that portfolio has 1000 dollars in cash and is long 1000 shares of JPM with the price $42 and long 500 shares of MSFT at$33.50. Calculate in Excel portfolio value at risk and conditional value at risk for 1 day 95% confidence interval using historical data going back 200 days
a). assuming number of shares stays the same;
b). assuming weights of JPM and MSFT stay the same.

My textbook gives only a basic description of historical simulation, but I do not understand how it would be possible to calculate in Excel. I suspect that I should calculate returns and then simulate them in some way, but I'm not sure. I searched Internet, but I found general descriptions only. Please help me or give me a link to a website that explains how to calculate VaR and conditional value at risk in Excel.

2. ## HS VaR Calculation

1. Download or input into columns A and B the most recent 200 days of closing prices for the 2 stocks.
2. Convert these prices into continuously compounded returns by applying a forumula such as =LN(A2/A1), and put those results into columns C and D.
3. Create the "simulated" price changes (P&Ls) by applying a formula such as =$A$200*(1-C2), and copy those into columns E and F.
4. Sum the rows of E and F, while multiplying by the number of shares held, with a formula such as, =1000*E2+500*F2 and put the results in column G
5. apply the percentile function, =percentile(G2:G200,0.05), for the 95th percentile, which gives the VaR
6. To calculate the CVaR, first sort the data in column G.
7. As you have 199 returns and you are looking at the 95th percentile, the CVaR will be the average of the 10 worst (most negative) values in the sorted list. If you want to get fance, then use the Index function with the percentile function to find the row number in the sorted list that corresponds to the 95th percentile and average the results that are "more negative" than the entry in that row.
8. The amount in cash is irrelevant to the answer, unless it is earning interest over the interval.
9. It makes no sense to say "assuming the number of shares stays the same" because VaR assumes a portfolio passively held over the forecast horizon.
10. Similarly, it makes no sense to say "the weights are the same" both for the first reason above and because in the simulated P&Ls the weights will vary row by row as a result of the market price changes applied to the positions.

3. Thank you!