Assume that you manage a large portfolio with the current value of $50 million, and also assume the monthly return follow a normal distribution with mean of 1% and standard deviation of 4%. Help generate monthly return for 12-month period, and then calculate the portfolio value at the end of the 12th month using Norminv. (Hint: add 11 more columns to generate monthly return for all 12 months, and the end portfolio value is simply the beginning portfolio value multiply by 1+monthly return for all months, i.e., multiple 12 times). Then run this simulation for 5000 times, and then rank the end portfolio value from the lowest to the highest (1st to 5000th).

a) Report the 12-month portfolio VaR estimate, given a 95% confidence level. Save results in Sheet1 tab.

b) Now set the standard deviation of monthly return to 3% (instead of 4%), and report the new 12-month portfolio VaR, and save the results in Sheet2 tab. Is the new VaR higher or lower than the one in a)?

PLEASE show excel formulas and calculations used on excel sheet. Thank you!