Assessment 2: Written Assignment
Model development + Report
The assignment will be a report based around portfolio management and Excel modelling.
Historical data on share prices for several FTSE100 companies has been collected and can be seen in an excel file that will be posted on Blackboard one month prior to the submission deadline. The data denote the share price at the beginning of each week over several years.
Firstly, you are required to carry out some statistical analysis on this data to measure the expected return and the level of risk for each share, and the correlations between the returns on the various shares.
Secondly you are required to complete the following 6 analyses. Most importantly, you need to explain your results and show an understanding of the underlying theory and limitations of your analysis.
- Working with the file of share prices, estimate the weekly and the annual expected return from each share.
- Estimate the standard deviation of the annual returns.
(Note that annual variance = weekly variance x 52, but annual SD = weekly SD x √52)
- Produce a matrix of correlations between the weekly returns on each pair of shares.
- Assuming that the appropriate risk-free rate is currently 2% and using as inputs your previous answers for expected return, standard deviation, and correlation, create a spreadsheet model which will identify efficient portfolios of the shares, and from this derive the efficient frontier chart. Explain, in the context of share portfolios the relationship between risk and return, and how diversification reduces risk.
- Discuss the practical limitations of this approach to portfolio management and of the model itself.
- Based upon the volatility of the tangency portfolio, calculate the 95% VaR of this portfolio. Comment upon how this measure may not be a good estimate of the VaR as time progresses while the portfolio remains the same.
Total: 100 marks
You need to submit (via Blackboard) to Turnitin:
- The amended data file showing how you estimated the return, risk, and correlation.
- A spreadsheet wherein you have calculated the required information. A Word document (i) explaining the methods for estimating the return, risk and correlations and the spreadsheet model (where this is not obvious) and (ii) discussing your results and findings.
Type of service: Academic paper writing
Type of assignment: Report
Number of Sources: 7
Academic level: Master’s
Paper format: Harvard
Line Spacing: Double
Language Style: UK English