MATH2545 Practical Assignment
Please read the instructions below carefully.
This assignment has 75 marks in total and contributes 15% of your overall module mark for MATH2545. All questions will be given integer marks only.
You are asked to use the given Excel spreadsheet template for all calculations. The template file is available on Minerva → Assessment and Feedback. Before attempting the questions you must
• change the Excel file name to lastname firstname studentID.xlsx,
• enter your name and student ID into cell B1 and B2 respectively.
You can work out the questions in different worksheets inside the template file, but you must collate and clearly state your final answers to all questions (except for the first one) in order on the first worksheet titled “FrontSheet” . You must submit only one Excel file together with a signed academic integrity form.
The submission deadline is 17.00 on Friday 02 May 2025. Late submissions will not be accepted except for mitigating circumstances, which must be filed for before the deadline.
Choose two publicly traded stocks and fill in their tickers to cells B3 and B4 in the FrontSheet. For your chosen stocks, work out the following tasks.
1. On Worksheet Q1, use the function STOCKHISTORY to compute the weekly rates of return of the chosen stocks over the last 150 weeks. [5 Marks]
2. For each stock, plot a histogram for its weekly rates of returns. [10 Marks]
3. Estimate the means, variances and the correlation of the two stocks. (If the computed correlation equals ±1, choose a different set of stocks and restart.) [10 Marks]
4. Calculate the value at risk of the loss on the rate of returns at confidence level 95% for each stock. [10 Marks]
5. For the given range of values for the portfolio weight W1 , calculate the expected returns, the standard deviations, and the Sharpe ratios for the corresponding portfolios of the two chosen stocks, and plot the efficient frontier in the μ-σ plane. [15 Marks]
You may adjust the range of values for (W1, W2) so that the resulting segment of the efficient frontier in your plot is large enough - it should include the point of the global minimum.
6. Suppose that a money market account with weekly interest rate 0 .1% is added. Deter- mine the market portfolio and its Sharpe ratio. [15 Marks]
7. How does the Sharpe ratio of the market portfolio change if the weekly interest rate decreases? Justify your answer with numerical evidence. [10 Marks]