In this assignment you will implement an index tracking investment strategy. In order to help you do this you will find an Excel workbook called AssignmentData2.xlsx on UTSOnline. It contains weekly stock price data for fourteen companies listed on the Australian Security Exchange along with the levels of the ASX200 index. The ‘Sample Data’ will be used for Questions 1 and 2 and the ‘Out-of-Sample Data’ will be used for Question 3.
Finance Discipline Group UTS Business School 25503 Investment Analysis Assignment—Part II Spring 2019 1. The assignment should be completed in groups of maximum 4 students. If you are satisfied with the performance of your group for the Assignment Part I, your group may remain the same. If your submitted Group Work Declaration Forms indicate that all members of the group have contributed fairly to this assignment, each member will receive the same mark for the assignment. 2. Help: For consultation times, please check UTSOnline and the subject outline. Note that email is not an efficient way for asking questions about the assignment; please post any questions on the UTSOnline discussion board. 3. Due date: A hard copy of the assignment should be submitted in the assignment box marked “FINANCE 4” located in Building 8, Level 5, by 5:00pm Monday 21 October 2019. Late submissions will not be accepted and no soft copy is required. 4. Complete a cover sheet (available on UTSOnline) with all the signatures from your group members and attach it to a printout of your answers. Please note the in- structions regarding Group Work Declaration Forms. 5. The assignment computations are to be done in Excel, but the solutions may be pasted into Word and formatted for submission. The final report, including all text, tables and figures should be printed out on A4 paper with a minimum font size of 12. Also, the final report (excluding the cover sheet) should not exceed 12 pages in length. 6. Do not round your interim calculations. Your final values should be rounded to the nearest 6th decimal for reporting purposes. Subject Coordinator: Vitali Alexeev In this assignment you will implement an index tracking investment strategy. In order to help you do this you will find an Excel workbook called AssignmentData2.xlsx on UTSOnline. It contains weekly stock price data for fourteen companies listed on the Australian Security Exchange along with the levels of the ASX200 index. The ‘Sample Data’ will be used for Questions 1 and 2 and the ‘Out-of-Sample Data’ will be used for Question 3. 1 It has been over a month since you started working for the small asset management company (see Part I) and in that time your boss agreed to implement the portfolio you constructed on your very first day on the job (after some more minor ‘tweaks’). You have now just returned from the break and your boss has set you on your next project! The company is thinking of adding an index tracking fund to their investment offerings and your boss wants you to investigate the different methods of constructing such a tracking portfolio. To do this you should perform the following preliminary analysis: 1. (a) Transform the stock prices and index values in the ‘Sample Data’ tab into continuously compounded returns (you do not need to report these in your submission). (b) Using the resulting returns data, estimate (and report) the vector of expected returns for the fourteen stocks and the index. You should also report the variance-covariance matrix for the fourteen stocks as well as the variance of the index. The expected returns etc. should be annualised (i.e., in annual units). (c) Using the ASX200 index as a proxy for the market portfolio (MP), estimate and report the betas of the fourteen stocks. (d) Decompose the total risk (variance) of each asset into its systematic and unsystematic components, i.e., report all three values (variance, systematic risk, unsystematic risk) along with the diversification ratio (R2) for each stock and the index. (e) Assuming risk-free borrowing and lending at rF = 1% per annum, plot the capital market line (CML), and indicate the positions of the fourteen stocks as well as the MP. (f) Plot the security market line (SML), and indicate the positions of the fourteen stocks as well as that of the MP. Based on this graph, discuss which stocks look over-valued, and which stocks look under-valued? Since the ASX200 index is not traded, you wish to construct a portfolio out of the fourteen stocks that ‘tracks’ the index as close as possible (in some sense). Your boss asks you to propose at least two different methods for constructing such a tracker portfolio. After some careful research you come up with two possible methods and to implement these you must perform the following tasks (Hint—you will need to use Solver): 2. (a) Report the weights (in the fourteen stocks) of the portfolio whose variance is minimised but whose exposure to the index is exactly one, i.e., that has βP = 1. You should describe in words what you have done in Excel and report the value of your portfolio’s (minimised) variance.∗ (b) Report the weights (in the fourteen stocks) of the portfolio that minimises the Root- Mean-Square Error (RMSE) of the difference in weekly returns between the portfolio and the ASX200 index. More specifically, let r1, . . . , rT be the vector-valued sample returns of the fourteen stocks, for t = 1, . . . , T weeks. Similarly, let rI,1, . . . , rI,T denote the sample returns of the index. Then you want to find the vector of portfolio weights ∗Note you should use the variance-covariance matrix to calculate the portfolio’s variance. 2 that solves the following minimisation problem: min x √√√√ 1 T T∑ t=1 (x>rt − rI,t)2. Again you should describe in words what you have done in Excel as well as report the minimum value of the RMSE achieved. (c) Report the expected return, variance, beta and R2 for your two tracker portfolios con- structed above. Which method do you recommend to your boss and why? You present this evidence to your boss, who, after careful consideration, decides to implement the tracking portfolio you constructed in 2(b). Since the tracker portfolio is a passive strategy, your boss moves you on to other projects. However, 12 months have now passed and your boss asks you to look into the performance of the tracker portfolio. The portfolio was constructed on August 31, 2018 (seconds before the market closed for the day at 4pm) using the portfolio weights found in 2(b) and with an investment amount of $1,000,000. You should assume that this was done without any transaction costs at the prices quoted on that date in the ‘Out-of-Sample’ tab and that any fraction of a share can be purchased. You should also assume that the portfolio was held, without any further transactions, until August 30, 2019 (i.e., the portfolio was not re-balanced). To assess the performance of the tracking portfolio you need to perform the following tasks: 3. (a) Calculate and report a time-series plot of the tracker portfolio value from August 31, 2018 to August 30, 2019, along with the performance of the ASX200 index, clearly indicating which series is which. You should also normalise the values of both time series so that their values are 100 on August 31, 2018. (b) Report the simple annualised return of the tracker portfolio and the ASX200 index over the investment period. (c) Using the weekly continuously compounded returns for the tracker portfolio and the index, report the beta, R2, and RMSE for the tracker portfolio over the investment period. Comment on how close these values are to the values found for the tracker portfolio ‘in sample’ from Questions 2(b/c). You put some finishing touches to the report, print it, and take it to your bosses office. She scans over the documents briefly, clearly impressed by the level of detail and rigour of your analysis. So much so that she offers to take you to lunch on expenses! As you tuck into your rib-eye steak at Cafe Sydney looking out across the harbour, you start to realise that maybe all your hard work in 25503 Investment Analysis was worth it after all! The End. 3