Question

You are analyzing the returns of a mutual fund portfolio for the past 5 years.

Year	Return
2014	-30%
2015	-25%
2016	40%
2017	-10%
2018	15%

Question 6: What is the standard deviation of the returns?   29.28%

Question 7:  Use Excel to compute the VaR at the 1% level (you can write the Excel formula as your work).

Answer Question 7 plz

Answer 1

Value at risk (VaR) is a measure of the risk of loss for investments. It estimates how much a set of investments might lose (with a given probability). For example, if a portfolio of stocks has a one-day 10% VaR of $5 million, that means that there is a 0.1 probability that the portfolio will fall in value by more than $5 million over a one-day period if there is no trading.

For the calculating VaR using excel follow the steps given below:

For calculating VaR we'll be requiring mean , standard deviation and confidence level.

1. First add the given data of historical returns on excel sheet:

Year	Return
2014	-30%
2015	-25%
2016	40%
2017	-10%
2018	15%

2. Calculate the mean of the returns over the year using AVERAGE function, put the following formula in cell E1 :

Mean

-2%

=AVERAGE(B2:B6) (see the image given below for better understanding)

3. Calculate the standard deviatiion of the returns over the year using STDEV function, put the following formula in cell E2 :

Stdev

29.28%

=stdev(B2:B6)

4. Finally, we calculate VaR for various confidence levels using NORM.INV function.This function has three parameters: confidence level, mean, and standard deviation. Put the following formula in cell E3 :

=NORMINV(1-D5/100,$E$1,$E$2)

Confidence level	VaR
1	66.12%
50	-2.00%
99	-70.12%

5. Thus, for 1% level the VaR value is 66.12%.