Please solve step by step and I need Excel sheets with explanations..

Please do not copy from Chegg Answers..

Question:-

Your client would like to assess the risk of holding a portfolio of 75 lakhs invested in Nifty-50 and Nifty-midcap-50 indices with weights 0.2 and 0.8 respectively. Please provide the client with the following estimates for both 1-day time horizon and 7-day time horizon:

a) 99% VaR and 97.5% ES using historical simulation. You can use data of last one year to do this (this data is easily available on the web;

b) Same risk estimates as in (a) using weighted historical simulation with = 0.9. How sensitive are your risk estimates to the choice of ? (you can find this by altering the a little on either side)

c) 99% Analytical VaR, assuming a normal distribution for the portfolio returns. You can estimate the required parameters from the above data.

Upload your Excel workings and risk estimates to Moodle along with the complete dataset (with the dates included) and a brief report of your observations from the various estimates on a separate sheet.