125.250, Assessment #2
Risk and Return Project
Due: May 30 (Friday) 5 pm, New Zealand Time
Background
The project counts for 20% of your final grade (20 marks in total). It is not something that you can do in the last week before it is due, so I suggest that you start working on it well before the due date.
Requirements
You are an analyst working for a major stock broking firm. Your boss has asked you to prepare excel spreadsheets to analyse the risk and return characteristics of two S&P/NZX 50 Gross Index (^NZ50) companies. The S&P/NZX 50 Gross Index is designed to measure the performance of the 50 largest, eligible stocks listed on the Main Board (NZSX) of the NZX by float‐adjusted market capitalization. The index is widely considered as New Zealand's preeminent benchmark index. The index is covering approximately 90% of New Zealand equity market capitalization (https://www.nzx.com/markets/nzsx/indices/NZ50).
The components for the NXZ50 index are shown in the Appendix of the document. You can select two NZ50 companies that you want to evaluate for any reasons. You can download the historical trading data of the two companies at https://nz.finance.yahoo.com/ (as shown in the figure below) or follow the steps in Appendix 2.
1) Type in the symbol of the selected company at the “Search” box to access the data of the selected company (for example, “ATM.NZ” forA2 Milk Company).
2) Click on “Historical Data”.
3) Set the “Time period” and “Frequency” of “Historical prices” and click “Apply”.
4) Click “Download” to download the historical prices ofthe selected company.
“Historical prices” to download: Daily data for the period from 2019 to 2024” is required to perform the calculations. This is a compulsory requirement of this assignment.
Your manager points out that although the past is not always a good predictor of the future, the clients want to know the information on the historical return and risk characteristics of the selected NZX50 stocks. Please perform. the following estimations.
1. Weekly returns, using “Adj Close” prices to calculate the returns (3 marks)
1) Calculate the arithmetic daily returns ofthe two companies (1 mark)
2) Calculate the continuously compounded daily returns ofthe two companies (1 mark)
3) Create scatter plots using compounded daily returns ofthe two companies, and briefly discuss the results (1 marks)
2. Descriptive statistics (2 marks)
1) Obtain the descriptive statistics of the daily returns ofthe two companies. (1 mark)
2) Briefly discuss the results (return and risk). Which stock is a better choice based on the mean-variance criteria? (1 mark)
3. Histogram (3 marks)
1) Create the Histogram using compounded daily returns for the two companies. (2 marks)
2) Briefly discuss the results (skewness, kurtosis, normal distribution) (1 mark)
4. t‐test (4 marks)
1) Perform. “t-test: Paired Two Sample for Means” to examine whether the mean difference of the two companies’ compounded daily returns is statistically significant (2 marks)
2) Briefly discuss the results (2 marks)
5. Estimating Beta (4 marks)
1) Estimating Beta (measurement of systematic risk) of the two companies, based on the data from 2019 to 2024. The data of NZX50 index prices (to proxy the market return) is provided in the Excel temperate (2 mark)
2) Briefly discuss the results (which one is riskier based on systematic risk) (2 marks)
6. Portfolio Investment (4 marks)
Your company considers investing $100,000 in a portfolio that consists of two stocks that you analysed in the previous sections. Which proportion of each stock in the portfolio that you recommend your manager to invest? Justify your choice.
What needs to be handed?
Please submit the electronic copy of your Excel workbook via the “125250 project submission drop-box”.
• Please use separate workbooks to perform the calculations and name each workbook properly (the template is available on Stream).
• Please briefly discuss the results in the relevant excel spreadsheet if it is required. There is no need to submit a separate Word document to discuss the results.