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.