Below you find two problems that must be solved using Excel. Because it is a coursework
each point of each problem must be explained in details using both the formulas from
excel and the formulas that would normally be used without any type of finance
programs. Example: you write both STDEV command in a Word Document that you use
in Excel but also the formula for standard deviation: � = #∑(#$%&’)
)
. Of course all the
steps, rationing and calculus used must appear in a work document for this coursework.
Even though the problems can be solved using Excel you must provide explanation for
each point in a Word document as mentioned with all the steps that you used to solve the
problems.
Problem 1: Use “monthly returns” in “ Data For Finance Coursework .xlsx” to calculate
the return and variance of a portfolio (call it “ptf1”) consisting of IBM and LUV (at
increments of 10%)
1. Assume that the correlation between IBM, and LUV is +1 (case a) and -1 (case b).
Calculate the variance of ptf1 returns with these two stocks’ returns for both cases.
Graph it together with ptf1 .
2. Graph the returns on the Y-axis and the variances on the X-axis from question (a) and
chose the % (or weights) that deliver the lowest variance (i.e., the one furthest to the
left). (Would you invest in portfolio 90% IBM & 10% LUV? Why?)
3. Calculate the returns for pt1, for the whole period with the “chosen” portfolio weights
from (c) (that is, the ones which delivered the lowest variance).
4. Calculate the average return, the standard deviation and variance of ptf 1 and its
correlation with DIS.
5. Calculate the return, and variance of a new portfolio (call it ptf2) that adds DIS to ptf1
(always at increments of 10%).
6. Graph the return and variances from (f) (use the same graph as before) and chose the
weights that deliver the lowest variance (i.e., the one furthest to the left), as you did
previously.
7. Assuming that you invest in ptf1 and DIS with proportions from part (g), calculate the
returns for the whole period of ptf2
8. Calculate the mean, standard deviation of ptf2 returns and its correlation with
S&P500.
9. Calculate the return and variance of a new portfolio (ptf_final) that invests in ptf2 and
in S&P500 (always at increments of 10%)
10. In the same graph as used earlier, graph the return and variance of ptf_final and chose
the % with the lowest variance (i.e., the one furthest to the left).
11. Summarise and comment on your calculations from the perspective of modern
portfolio theory. Do you see any “anomalies” in the data? Could you reach your final
conclusions earlier in your calculations?