Excel代写-ECO-6004B

ECO-6004B The Economics of Alternative Investments
Summative Assessment 01
Dr. Andrea Calef*
22nd March 2021
The return date for this summative assignment is Thursday 25th March 2021 by 3pm BST.
Please submit your work via Blackboard. Solutions must be in a .pdf file. While there is no require-
ment to submit your Excel file, I suggest to do it.
Answer to ALL the questions.
Academic integrity
To maintain academic integrity students are required to:
• Not keep an electronic copy of this paper or share the content with others.
• Ensure that all work produced is solely their own. Students are advised that their work will
be checked using text matching software to determine any similarity with the work of other
students or with other published materials.
The University policy regarding plagiarism and collusion will apply to this examination. A copy of
the policy can be viewed here: Plagiarism and Collusion.
*University of East Anglia
1
Data
You have also been provided with an Excel file with the title Summative Assessment 01 – Data.
There are many worksheets, which provide you with “some” suggested structure to address Ques-
tions 01 and 02. The sheet named “Portfolio Optimisation” also contains three time series to be used
in Question 03. There is no requirement to follow the suggested structures. Please use two decimals
in your calculations for Questions 01 an 02, and four decimals (and percentages) when solving Ques-
tion 03. While, on average, you will probably need fewer words to correctly and completely reply,
the word limit is 150 words per subquestion.
The following 1–year daily (non-annualised) price time series are provided:
a. MSCI EM – Price Index
b. 10Y T–Bond Index
c. S&P GSCI Commodity Index
2
You are an investment manager of EAI Finance Company Ltd. The CEO asked you to diversify the
portfolio to include an investment in equity real estate. Being a cautious manager, you explore many
investment opportunities under different scenarios. You detect a potentially good investment into
an office building, located in the city centre of London. Considering many factors, the following
assumptions are made under the baseline scenario:
i) The initial value is equal to £12,500,000.00.
ii) The depreciation rate is equal to 1.20% per annum.
iii) The expected gross income in the first year is equal to 8.80% of the initial value of the building.
iv) The estimated vacancy loss rate is 4.20% each year.
v) Operating expenses are estimated to be equal to 31.40% of the effective gross income for the
first year.
vi) Rents are assumed to be readjusted every year with a perfect matching of the depreciation rate.
vii) Operating expenses are assumed to grow in line with inflation at 0.80% per annum.
viii) The office build is set to be sold after 18 years, and the net sale proceeds are estimated to be
equal to the depreciated value of the property at the end of year 18.
ix) The annual risk–free rate is assumed to be equal to 1.50%.
x) The discount rate of this investment is assumed to be equal to the annual risk–free rate + the
annual risk premium for RE. The latter is equal to 6.00%.
xi) The tax rate on the net operating income is estimated to be equal to 30.00%.
xii) As EAI Finance Company Ltd does not have enough capital, you assess the need of granting a
mortgage from AC Bank Ltd, which offers a 25-years 3.80% (annual risk-free rate + mortgage
premium equal to 2.30%) fixed-rate mortgage of £7,500.000.00 with a final balloon payment
equal to 8.00% of the principal value in order to finance the investment. The mortgage pay-
ments are paid with a semi-annual frequency and every payment is made at the end of its own
period. Finally assume that the mortgage is fully repaid in advance, when the property is sold.
3
As this is a long term investment, you wish to calculate the intrinsic value of this investment under
different scenarios that considers shocks to the fiscal policy and lending rates by changing the as-
sumptions made in x) and xi) as follows:
• Scenario I: At the end of the second year the mortgage is renegotiated and the mortgage rate
decreases to 1.60% for the residual term to maturity of the mortgage. At the end of the fifth
year the government permanently lowers the tax rate to 25.00%.
• Scenario II: At the end of the second year the mortgage is renegotiated and the mortgage rate
decreases to 1.60% for the residual term to maturity of the mortgage. At the end of the fifth
year the government permanently rises the tax rate to 35.00%.
• Scenario III: At the end of the second year the mortgage is renegotiated and the mortgage rate
increases to 3.00% for the residual term to maturity of the mortgage. At the end of the fifth year
the government permanently lowers the tax rate to 25.00%.
• Scenario IV: At the end of the second year the mortgage is renegotiated and the mortgage rate
increases to 3.00% for the residual term to maturity of the mortgage. At the end of the fifth year
the government permanently rises the tax rate to 35.00%.
• Scenario V: it is identical to Scenario I, except for the net sales proceeds that are assumed to the
equal to £9,500,000.00 instead of the depreciated value of the property at the end of year 18.
You, as an investment manager, are going to follow the structure of questions written below to finally
report your analysis to the CEO.
Question 01 (28 marks)
i) Compute the amortisation schedule for the initial mortgage, the renegotiated mortgage with a
mortgage premium equal to 1.60%, and the renegotiated mortgage with a mortgage premium
equal to 3.00%. Report the first five rows and the last five rows on the .pdf file. Write the
mathematical formulae you used. Write and briefly explain the steps you followed (in bullet
points) to compute the amortisation schedules.
ii) Compare and discuss the features of a) a fixed-rate, constant payment, fully amortised mort-
gage, b) a fixed-rate interest-only mortgage, c) a fixed-rate, constant payment with a final bal-
loon payment, fully amortised mortgage, and d) a variable-rate mortgage.
iii) Calculate and report the total mortgage payments and the total interest payments for each of
the three mortgages computed in subquestion i), if they are kept until the term to maturity.
Please comment your findings.
4
Question 02 (36 marks)
i) Calculate the intrinsic value for the baseline scenario and the other five scenarios. Report the
first ten rows of each scenario’s schedule.
ii) Fill in the summary table you find in the sheet named “Summary of cases”. Compute the
internal rate of return (IRR) of the equity real estate investment for each scenario, if we assume
that each of them are to have the same intrinsic value of the baseline scenario. List (in bullet
points) the steps you performed to find the IRRs.
iii) Comment the table’s data.
Question 03 (36 marks)
i) Compute the daily returns time series for each price time series you find in the sheet named
“Portfolio Optimisation”. Report the mathematical formula you used and the first ten rows.
ii) Compute the following descriptive statistics using non-annualised data: mean, standard devi-
ation, skewness, kurtosis. Report them in the solutions’ file. Comment the findings and briefly
discuss the features of the time series of returns for the real estate investment. Assume that the
IRRs you found in the last subquestion of Question 02 are your (annualised) observations you
start with for computing the request statistics of the asset named “Real estate”.
iii) Compute the variance–covariance matrix for the three returns time series. To be able to fill all
the elements of the matrix, you assume that the real estate investment (“Real estate”) has zero
correlation with each of the other three assets. Report the mathematical formulae you used and
the findings: briefly discuss the latter.
iv) Based on previous findings and using daily (and non-annualised) data, compute the weights
for the minimum variance portfolio, and the optimal risky portfolio, both before investing on
the real estate project and after investing on it. Assume the naive diversification as initial port-
folio weights for each of the four cases. Report the portfolio weights, mean return, variance,
standard deviation and Sharpe ratio for each case. Briefly introduce the theory related to min-
imum variance and optimal risky portfolios, respectively, before commenting your findings.
5