- Download or input into columns A and B the most recent 200 days of closing prices for the 2 stocks.
- 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.
- 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.
- 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
- apply the percentile function, =percentile(G2:G200,0.05), for the 95th percentile, which gives the VaR
- To calculate the CVaR, first sort the data in column G.
- 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.
- The amount in cash is irrelevant to the answer, unless it is earning interest over the interval.
- 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.
- 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.