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?
Problem 2
Spreadsheet “daily returns” in file “Data For Finance Coursework.xlsx” provides daily price
and total returns series for IBM, DIS, and LUV for the period 16/11/2020-12/11/2021. Daily
total return incorporates daily price change and any relevant dividends for the same day,
while price return incorporates daily price change only. The series are adjusted for the stock
splits etc.
a. Using only the data provided, estimate the Dividend Yield (discretely compounded) of
IBM, DIS, and LUV as of 12/11/2021. (Hint 1: how much return on IBM stock came from
the dividends? Hint 2: Consider using the properties of continuously compounded returns
which you can eventually convert into discretely compounded returns) in your calculations.
b. On 12-11-2021, IBM stock closed at $119. Could you fit a simple equity pricing model
with a set of reasonable parameters to justify this price? You are free to utilize any
information you might find useful, including your answers to the earlier questions.
Order Now!
