Portfolio construction. Investments 3.4
Question 1.
Calculate the daily returns of the market index:
We used the formula as provided in the case guideline: ln(Rday/Rday-1), where R represents the return
for the specific date. By inserting LN($B4/$B3) in excel, we could simply drag down the formula to
obtain all the daily returns. Note: using this formula results in a sample of 249, not 250. For the
return on day 1, we miss the value Rday-1.
Answer: too many values to display, returns ranged from -0,0954643 to 0,1079719
VaR based on normal distribution:
We first calculated the average return and standard deviation of the 250 sample. We used
=AVERAGE(D4:D253) and =STDEV(D4:D253) to calculate the average return and standard deviation,
respectively. Average return= -0,0015 and SD= 0,0214
To calculate the VaR, we used NORMINV. In excel, this resulted in =NORMINV(α; -0,0015;0,0214). For
the ES, we used the =NORMDIST formula. Because we assume a ND, we used 0 for the mean and 1 for
the SD. We got NORMDIST(α;0;1;FALSE)/(1- α). We multiplied the outcome with the SD of the market
