Results 1 to 3 of 3

Math Help - Historical simulation of value at risk

  1. #1
    Newbie
    Joined
    Nov 2006
    Posts
    14

    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.
    Follow Math Help Forum on Facebook and Google+

  2. #2
    bschachter
    Guest

    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.
    Follow Math Help Forum on Facebook and Google+

  3. #3
    Newbie
    Joined
    Nov 2006
    Posts
    14
    Thank you!
    Follow Math Help Forum on Facebook and Google+

Similar Math Help Forum Discussions

  1. Replies: 8
    Last Post: September 21st 2011, 09:18 AM
  2. Risk over time
    Posted in the Business Math Forum
    Replies: 1
    Last Post: May 23rd 2010, 08:27 AM
  3. Value at Risk
    Posted in the Advanced Statistics Forum
    Replies: 0
    Last Post: March 22nd 2010, 09:14 AM
  4. Replies: 0
    Last Post: December 15th 2009, 02:24 AM
  5. Simulation of Collective Risk model in R
    Posted in the Advanced Statistics Forum
    Replies: 0
    Last Post: May 22nd 2009, 03:56 PM

Search Tags


/mathhelpforum @mathhelpforum