Questions are in the attached PDF file and the data associated with the questions are also attached and named Data.xlsx.

Finance Discipline Group UTS Business School 25503 Investment Analysis AssignmentPart II Spring 2016 1. The assignment should be completed in groups of 3-4 students. Your group should be the same as for the first part of the assignment (unless explicit permission has been granted from the subject coordinator). All group members receive the same mark. 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 1\" located in Building 8, Level 5, by 5:00pm Wednesday 24 October 2016. 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. 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. Subject Coordinator: Kristoffer J. Glover In this assignment you will implement an index tracking investment strategy as well as construct an immunized bond portfolio. 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 the six largest capitalised companies on the Australian stock market along with the levels of the All Ordinaries index for the last three years. 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 'tweeks'). 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 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 six stocks, as well as the variance-covariance matrix of these returns. These expected returns etc. should be annualized (i.e., in annual units). (c) Using the All Ordinaries index as a proxy for the market portfolio (MP), estimate and report the betas of the six 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 = 2% per annum, plot the capital market line (CML), and indicate the positions of the six stocks as well as the MP. (f) Plot the security market line (SML), and indicate the positions of the six 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 All Ordinaries index is not traded, you wish to construct a portfolio out of the six 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 (Hintyou will need to use Solver ): 2. (a) Report the weights (in the six 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 six stocks) of the portfolio that minimises the variance of the difference in weekly returns between the portfolio and the AORD index. More specifically, let r 1 , . . . , r T be the vector-valued sample returns of the six 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 that 2 solves the following minimisation problem: \u0001 min Var x> r t rI,t . x Again you should describe in words what you have done in Excel as well as report the minimum value achieved for the variance of the differences. (c) Report the expected return, variance, beta and R2 for your two tracker portfolios constructed above. Which method do you recommend to your boss and why? You present this evidence to your boss, who, after careful consideration, asks you to construct and implement a completely different method of her own! Oh well, at least you learnt something. Since the tracking portfolio is a passive strategy, there is not much work to do on it once it has been constructed. The next morning, when your boss realises this she decides to loan you to one of the other partners who specialises in the company's fixed-income offerings. Within five minutes of arriving at his desk you are asked to help with an urgent bond portfolio immunization in which you need to perform the following tasks and have the results on your new supervisor's desk in two hours: 3. A firm has a liability, L, which requires a payment of $25,000 per year (paid annually at the end of the year), for 15 years, plus a final payment of $1,000,000 at the end of the 15th year. The following semi-annual coupon-bearing bonds, with a face-value of $1,000, are available for investment: Bond 1 2 3 Maturity (Years) 20 15 5 Coupon 6% 7% 8% Assume that the market YTM is a flat 6% per annum, with semi-annual compounding. (a) Construct an immunizing portfolio for L with 50% invested in Bond 1 and 50% invested in Bonds 2 and 3 combined. Report the portfolio weights and show your workings. (b) Assess the effectiveness of your immunizing portfolio if the market YTM increases by 0.5%. (c) What would the coupon rate need to be on Bond 1 for the immunization to be done by simply investing 100% in this (20 year) bond? With more than an hour to spare, you finish the last calculation and hand the results to your new supervisor. He's impressed. So much so that he offers to take you for lunch on expenses. As you tuck into your rib eye at Rockpool you start to realise that maybe all your hard work in 25503 Investment Analysis was worth it after all! The End. 3 Stocks Date 1/2/2013 1/7/2013 1/14/2013 1/21/2013 1/29/2013 2/4/2013 2/11/2013 2/18/2013 2/25/2013 3/4/2013 3/11/2013 3/18/2013 3/25/2013 4/2/2013 4/8/2013 4/15/2013 CBA 48.52 47.09 47.91 48.78 49.90 49.73 51.42 52.12 53.95 55.52 55.80 54.57 54.07 53.21 54.11 54.68 WBC 20.39 20.65 20.66 21.40 21.99 21.82 22.65 23.21 24.11 24.28 24.00 23.70 23.90 23.50 24.49 24.26 NAB 18.40 18.58 19.15 19.80 20.35 21.09 21.45 21.67 22.09 22.60 22.75 22.26 22.41 22.22 22.98 22.86 ANZ 19.87 19.86 20.13 20.51 21.03 22.12 21.84 22.15 22.51 22.91 22.59 22.46 22.44 21.83 22.58 22.35 4/22/2013 4/29/2013 5/6/2013 5/13/2013 5/20/2013 5/27/2013 6/3/2013 6/11/2013 6/17/2013 6/24/2013 7/1/2013 7/8/2013 7/15/2013 7/22/2013 7/29/2013 8/5/2013 8/12/2013 8/19/2013 8/26/2013 9/2/2013 9/9/2013 9/16/2013 9/23/2013 9/30/2013 10/7/2013 10/14/2013 10/21/2013 10/28/2013 11/4/2013 11/11/2013 56.32 56.50 56.11 58.21 54.68 53.16 51.70 53.35 52.52 55.00 54.85 56.43 56.82 58.56 58.69 57.92 58.28 59.62 60.28 60.54 61.09 61.03 60.54 59.38 59.84 61.38 63.12 62.72 65.46 64.37 25.30 26.07 25.60 25.42 23.78 23.06 22.38 23.47 22.48 23.42 22.99 23.61 23.73 24.75 25.51 24.95 25.35 25.52 25.45 25.66 26.02 26.56 27.00 26.29 26.75 27.57 27.86 28.04 28.06 27.91 23.72 24.52 23.60 24.05 22.74 22.09 21.59 22.38 22.07 22.60 22.22 22.80 23.11 23.61 23.74 23.28 23.82 24.72 24.71 24.96 25.54 26.55 26.74 26.51 26.56 27.40 27.48 27.14 27.61 27.23 23.50 24.86 24.51 24.62 22.52 22.41 21.72 22.63 22.30 23.25 23.10 23.37 23.45 23.93 24.34 23.78 23.96 24.11 24.15 24.15 24.63 25.29 25.64 25.02 25.46 25.92 26.57 27.43 27.68 27.31 11/18/2013 11/25/2013 12/2/2013 12/9/2013 12/16/2013 12/23/2013 12/30/2013 1/6/2014 1/13/2014 1/20/2014 1/28/2014 2/3/2014 2/10/2014 2/17/2014 2/24/2014 3/3/2014 3/10/2014 3/17/2014 63.30 64.40 62.20 61.40 62.83 63.97 64.20 64.21 62.45 61.85 61.43 60.84 62.88 64.44 63.99 65.14 64.50 64.85 27.49 27.81 26.65 26.22 26.70 27.14 27.35 27.25 26.73 26.32 26.11 26.43 27.70 28.17 28.31 28.67 28.46 28.42 26.93 27.45 26.57 26.47 27.20 27.55 27.54 27.44 26.78 26.85 26.39 26.02 27.09 27.42 27.58 27.58 27.25 27.52 26.80 26.99 26.22 25.59 26.89 27.21 27.25 26.70 26.25 25.93 25.49 24.92 26.52 26.92 27.19 27.57 26.96 27.29 3/24/2014 3/31/2014 4/7/2014 4/14/2014 4/22/2014 4/28/2014 5/5/2014 5/12/2014 5/19/2014 5/26/2014 6/2/2014 6/10/2014 6/16/2014 6/23/2014 6/30/2014 7/7/2014 7/14/2014 7/21/2014 7/28/2014 8/4/2014 8/11/2014 8/18/2014 8/25/2014 9/1/2014 9/8/2014 9/15/2014 9/22/2014 9/29/2014 10/6/2014 10/13/2014 66.12 65.98 66.31 66.49 67.62 67.83 68.14 68.91 69.69 69.93 70.09 70.05 69.79 69.83 70.24 69.63 69.66 70.53 70.99 68.68 69.60 71.86 72.49 72.47 71.52 69.36 67.09 68.33 66.68 68.23 29.12 29.26 29.31 29.55 30.26 29.49 29.55 30.10 30.02 30.22 30.34 30.08 29.99 30.00 30.21 29.81 29.77 30.10 29.90 29.00 29.93 30.64 30.77 30.52 30.07 29.13 28.00 28.62 28.32 29.06 28.04 28.09 28.05 28.07 28.43 27.43 27.29 27.78 27.85 27.78 27.82 27.55 27.48 27.40 27.98 27.93 28.35 28.70 28.98 28.06 28.78 28.59 29.20 28.90 28.41 28.12 27.13 27.35 26.48 27.50 27.77 28.23 28.64 28.67 29.33 29.05 28.69 28.88 29.46 29.37 29.52 29.59 29.80 29.46 29.62 29.24 29.30 29.59 29.43 28.29 28.40 29.35 29.31 29.23 28.79 27.99 27.17 27.74 27.38 28.00 10/20/2014 10/27/2014 11/3/2014 11/10/2014 11/17/2014 11/24/2014 12/1/2014 12/8/2014 12/15/2014 12/22/2014 12/29/2014 1/5/2015 1/12/2015 1/19/2015 1/26/2015 2/2/2015 2/9/2015 2/16/2015 70.22 71.74 73.78 72.89 71.40 71.96 72.78 72.87 74.62 75.70 76.43 76.70 74.50 76.87 79.63 82.89 83.04 82.94 30.04 30.54 30.59 30.15 29.44 29.71 30.13 29.25 29.65 30.03 30.37 30.49 29.90 31.33 31.45 33.74 34.26 34.35 28.43 29.03 28.81 28.35 27.99 28.27 28.10 27.71 28.33 28.95 29.11 29.69 28.94 30.16 30.90 32.27 32.51 32.51 28.95 29.37 30.04 29.54 29.08 29.17 29.33 28.33 28.95 29.24 29.45 29.60 28.56 29.42 30.15 31.89 32.66 31.81 2/23/2015 3/2/2015 3/9/2015 3/16/2015 3/23/2015 3/30/2015 4/7/2015 4/13/2015 4/20/2015 4/27/2015 5/4/2015 5/11/2015 5/18/2015 5/25/2015 6/1/2015 6/9/2015 6/15/2015 6/22/2015 6/29/2015 7/6/2015 7/13/2015 7/20/2015 7/27/2015 8/3/2015 8/10/2015 8/17/2015 8/24/2015 8/31/2015 9/7/2015 9/14/2015 84.51 83.75 83.96 88.56 86.74 86.79 86.54 84.66 84.68 81.75 75.98 77.92 76.41 78.23 73.82 74.86 77.66 79.67 79.68 78.88 80.89 78.89 80.50 74.75 75.50 72.71 73.45 69.40 72.27 72.94 34.68 34.39 34.50 36.26 35.78 36.01 36.40 35.51 35.07 33.53 31.08 31.40 30.93 31.88 29.63 30.09 31.16 31.36 31.12 31.45 32.84 32.53 33.12 30.73 29.77 29.80 30.00 28.45 28.98 30.16 32.87 32.93 32.73 34.16 33.68 33.56 34.28 33.58 33.02 31.93 30.53 31.68 30.49 31.49 29.23 29.60 30.89 31.28 30.93 30.35 31.51 31.12 31.91 30.12 29.14 28.59 28.98 27.34 27.70 28.50 32.29 32.34 32.38 33.62 33.63 33.51 33.63 32.53 32.64 31.20 30.70 31.43 30.42 31.50 29.58 30.10 31.09 31.41 30.81 30.61 30.79 30.42 31.02 28.61 27.80 26.90 27.08 25.49 26.12 26.95 9/21/2015 9/28/2015 10/5/2015 10/12/2015 10/19/2015 10/26/2015 11/2/2015 11/9/2015 11/16/2015 11/23/2015 11/30/2015 12/7/2015 12/14/2015 12/21/2015 68.72 70.60 72.65 73.64 74.41 73.81 73.67 72.88 76.79 77.07 77.17 75.94 78.46 80.15 28.45 28.26 29.32 28.92 30.19 30.02 30.27 30.52 31.86 32.12 32.30 31.47 32.28 32.54 27.34 27.62 29.09 29.15 29.77 27.67 27.64 27.26 28.91 28.65 28.57 27.60 27.77 28.36 25.63 25.56 27.01 27.21 27.43 25.82 25.97 26.31 27.74 27.34 27.11 26.22 26.48 27.25 12/29/2015 82.27 33.56 29.14 27.93 TLS 3.40 3.40 3.40 3.47 3.52 3.50 3.51 3.61 3.59 3.55 3.57 3.59 3.57 3.59 3.65 3.77 BHP 68.76 66.39 66.57 66.98 68.75 67.50 68.90 65.62 64.02 64.71 64.17 61.25 60.18 59.16 60.75 56.46 AORD 4742.90 4733.80 4794.70 4858.90 4941.90 4989.40 5054.60 5036.70 5100.90 5137.50 5129.30 4980.80 4979.90 4899.20 5016.00 4923.00 3.90 4.03 3.97 4.02 3.86 3.76 3.67 3.68 3.60 3.78 3.77 3.83 3.89 3.90 4.01 4.02 4.04 4.02 4.04 3.96 4.02 4.09 4.14 4.08 4.10 4.15 4.18 4.26 4.27 4.26 58.22 59.45 60.91 59.51 58.69 57.45 56.47 55.22 51.82 50.71 49.53 53.37 55.06 56.54 55.83 59.52 59.40 56.77 55.66 58.15 59.63 60.34 60.14 59.57 60.14 62.34 64.42 63.55 64.36 63.92 5082.70 5105.40 5191.10 5159.80 4964.30 4914.00 4729.30 4775.50 4723.80 4775.40 4826.40 4957.50 4959.40 5023.80 5098.70 5038.80 5100.10 5115.20 5125.30 5144.00 5214.70 5270.80 5302.30 5205.90 5228.80 5321.00 5385.70 5406.50 5394.40 5396.20 4.17 4.17 4.11 4.10 4.29 4.33 4.35 4.34 4.32 4.25 4.24 4.13 4.29 4.33 4.34 4.35 4.30 4.29 61.83 61.10 60.48 57.09 59.10 60.37 60.47 58.93 60.05 56.42 57.28 58.80 61.19 63.27 61.71 60.79 58.54 59.01 5330.30 5314.30 5186.00 5101.50 5261.50 5323.80 5351.80 5316.30 5316.40 5254.30 5205.10 5184.50 5366.90 5449.40 5415.40 5477.00 5347.10 5354.00 4.32 4.34 4.34 4.40 4.45 4.46 4.48 4.54 4.62 4.58 4.49 4.47 4.44 4.52 4.58 4.58 4.66 4.68 4.67 4.63 4.79 4.90 4.96 5.03 4.94 4.82 4.74 4.81 4.72 4.80 61.45 63.70 64.16 64.84 64.48 63.69 63.35 64.52 63.75 61.86 61.92 60.86 61.93 62.78 64.78 64.40 65.68 66.74 64.66 64.74 66.02 63.53 62.54 61.96 60.48 58.76 55.53 53.12 51.73 54.80 5376.80 5428.60 5423.50 5444.80 5515.50 5438.80 5442.00 5458.90 5470.30 5473.80 5443.50 5383.70 5401.60 5429.10 5511.80 5474.60 5519.20 5574.20 5547.60 5429.60 5559.60 5640.50 5624.60 5598.90 5532.30 5437.30 5316.60 5315.40 5185.70 5260.10 4.90 5.02 5.15 5.17 5.04 5.07 5.06 5.08 5.25 5.27 5.32 5.41 5.55 5.65 5.80 5.88 5.88 5.89 54.89 55.17 55.58 54.23 53.22 47.92 46.76 42.86 44.91 44.12 44.13 43.56 42.83 42.23 43.00 44.82 47.51 46.85 5399.30 5505.00 5522.10 5433.80 5292.10 5298.10 5313.60 5196.90 5312.70 5369.50 5415.00 5440.10 5278.80 5468.20 5551.60 5774.70 5835.50 5845.60 5.87 5.79 5.71 5.87 5.88 5.83 5.79 5.66 5.83 5.75 5.69 5.71 5.71 5.73 5.54 5.56 5.65 5.71 5.65 5.72 5.92 5.86 5.98 5.80 5.60 5.60 5.57 5.37 5.32 5.41 48.75 45.81 42.46 45.81 44.89 43.42 43.67 44.26 48.65 49.48 48.42 49.13 43.72 42.54 40.90 41.27 41.75 40.69 39.35 37.62 37.70 34.60 36.57 36.83 36.10 33.05 34.97 32.17 33.66 34.18 5898.50 5868.60 5788.00 5936.30 5888.90 5869.70 5935.40 5851.50 5906.80 5798.80 5635.40 5730.00 5668.20 5774.90 5506.50 5552.10 5591.50 5536.10 5528.00 5478.10 5652.50 5556.80 5681.70 5472.30 5360.00 5224.80 5274.70 5060.80 5096.30 5194.30 5.43 5.38 5.40 5.21 5.34 5.17 5.09 4.92 5.18 5.17 5.18 4.98 5.25 5.23 31.49 32.66 37.31 35.63 35.32 32.49 30.38 28.07 28.75 26.66 26.16 23.52 23.64 25.83 5076.70 5089.20 5309.20 5303.70 5388.10 5288.60 5269.70 5111.80 5305.50 5251.40 5201.50 5078.60 5156.50 5256.10 5.37 25.45 5344.60