Stock Analysis
It has been over a month since you started working for the small asset management company (seePart I) and in that time your boss agreed to implement the portfolio you constructed on your veryfirst day on the job (after some more minor ‘tweeks’). You have now just returned from the breakand your boss has set you on your next project!
The company is thinking of adding an index tracking fund to their investment offerings and yourboss wants you to investigate the different methods of constructing such a tracking portfolio. Todo this you should perform the following preliminary analysis:
1. (a) Transform the stock prices and index values in the ‘Sample Data’ tab into simple weeklyreturns (you do not need to report these in your submission).
(b) Using the resulting returns data, estimate (and report) the vector of expected returnsfor the 12 stocks and the index. You should also report the variancecovariance matrixfor the 12 stocks as well as the variance of the index. The expected returns etc. shouldbe annualised (i.e., in annual units).
(c) Using the ASX200 index as a proxy for the market portfolio (MP), estimate and reportthe betas of the 12 stocks.
(d) Decompose the total risk (variance) of each asset into its systematic and unsystematiccomponents, 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 riskfree borrowing and lending atrF= 2% per annum, plot the capitalmarket line (CML), and indicate the positions of the 12 stocks as well as the MP.
(f) Plot the security market line (SML), and indicate the positions of the 12 stocks as wellas that of the MP. Based on this graph, discuss which stocks look overvalued, andwhich stocks look undervalued?
Since the ASX200 index is not traded, you wish to construct a portfolio out of the 12 stocks that‘tracks’ the index as close as possible (in some sense). Your boss asks you to propose at leasttwo different methods for constructing such a tracker portfolio. After some careful research youcome up with two possible methods and to implement these you must perform the following tasks(Hint—you will need to useSolver):
2. (a) Report the weights (in the 12 stocks) of the portfolio whose variance is minimised butwhose exposure to the index is exactly one, i.e. that hasβP= 1. You should describe inwords what you have done in Excel and report the value of your portfolio’s (minimised)variance.∗
(b) Report the weights (in the 12 stocks) of the portfolio that minimises the RootMeanSquare Error (RMSE) of thedifferencein weekly returns between the portfolio and theASX200 index. More specifically, letr1,...,rTbe the vectorvalued sample returnsof the 12 stocks, fort= 1,...,Tweeks. Similarly, letrI,1,...,rI,Tdenote the samplereturns of the index. Then you want to find the vector of portfolio weights that solves he following minimisation problem:
Again you should describe in words what you have done in Excel as well as report theminimumvalue of the RMSE achieved.
(c) You are also interested in knowing if all 12 stocks are required for your tracker portfolioin (b). To investigate this, report the value of the minimised RMSE achieved if the topsix capitalised stocks were used to construct the tracker portfolio (instead of the top12). Comment briefly on your results.
(d) Report the expected return, variance, beta andR2for your three tracker portfolios constructed above; i.e. the trackers in (a), (b) and (c). Which method do you recommendto your boss and why?
You present this evidence to your boss, who, after careful consideration, decides to implement thetracking portfolio you constructed in 2(b).
Since the tracker portfolio is a passive strategy, your boss moves you on to other projects. However,almost 12 months have now passed and your boss asks you to look into the performance of thetracker portfolio. The portfolio was constructed on September 22, 2017 using the portfolio weightsfound in 2(b) and with an investment amount of $1,000,000. You should assume that this wasdone without any transaction costs at the prices quoted on that date in the ‘OutofSample’ taband that any fraction of a share can be purchased. You should also assume that the portfoliowas held, without any further transactions, until September 14, 2018 (i.e., the portfolio was notrebalanced). To assess the performance of the tracking portfolio you need to perform the followingtasks:
3. (a) Calculate and report a timeseries plot of the tracker portfolio value from Sep 22, 2017,to Sep 14, 2018, along with the performance of the ASX200 index, clearly indicatingwhich series is which. You should also normalise the values of both time series so thattheir values are 100 on Sep 22, 2017.
(b) Report the simple annualised return of the tracker portfolio and the ASX200 index overthe investment period.
(c) Using the weekly simple returns for the tracker portfolio and the index, report the beta,R2, and RMSE for the tracker portfolio over the investment period. Comment on howclose these values are to the values found for the tracker portfolio ‘in sample’ fromQuestions 2.
You put some finishing touches to the report, print it, and take it to your bosses office. Shescans 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 ribeye steakat Cafe Sydney looking out across the harbour, you start to realise that maybe all your hard workin 25503 Investment Analysis was worth it after all!
The purpose of this report is to investigate the different methods of constructing a tracking portfolio using the 12 stocks below and the ASX200 index.
Commonwealth Bank 

BHP Billiton Limited 

CSL Limited 

Westpac Banking Corp 

ANZ Banking Group Limited 

National Aust. Bank 

Wesfarmers Limited 

Macquarie Group Limited 

Woolworths Group Limited 

Telstra Corporation 

Woodside Petroleum 

RIO Tinto Limited 
In Chapter 2, the 12 stock prices and index from the sample data are transformed into weekly returns. The data is then analyzed to determine the annualized returns, variance covariance matrix and beta.
In Chapter 3, three portfolios out of the 12 stocks that track the ASX200 index are constructed and compared using three different methods.
In Chapter 4, the performance of the tracker portfolio is analyzed after 12 months to determine how close it follows the index.
The table below shows the weekly returns annualized expected returns, variance and standard deviation for the 12 stocks and ASX200.
0.0002% 
0.0103% 
3.7387% 
19.3357% 

0.0477% 
2.4797% 
11.2708% 
33.5721% 

0.4376% 
22.7564% 
3.9468% 
19.8665% 

0.0015% 
0.0755% 
4.8913% 
22.1163% 

0.0189% 
0.9828% 
5.2796% 
22.9774% 

0.0384% 
1.9977% 
4.9629% 
22.2777% 

0.0238% 
1.2355% 
3.0230% 
17.3866% 

0.2725% 
14.1695% 
5.4349% 
23.3128% 

0.1589% 
8.2626% 
4.4082% 
20.9957% 

0.1882% 
9.7869% 
2.9918% 
17.2969% 

0.1701% 
8.8427% 
5.4856% 
23.4215% 

0.1457% 
7.5770% 
8.6946% 
29.4866% 

0.0443% 
2.3019% 
1.8244% 
13.5072% 
The table below shows the covariance variance matrix for the 12 stocks
3.74% 
2.95% 
1.12% 
3.41% 
3.50% 
3.26% 
1.23% 
2.67% 
1.75% 
1.03% 
2.06% 
1.97% 
2.11% 

2.95% 
11.27% 
0.82% 
3.97% 
4.09% 
3.91% 
2.23% 
3.06% 
2.82% 
0.68% 
4.66% 
8.35% 
3.15% 

1.12% 
0.82% 
3.95% 
1.28% 
0.91% 
1.15% 
1.29% 
1.44% 
0.91% 
0.73% 
0.86% 
0.54% 
1.16% 

3.41% 
3.97% 
1.28% 
4.89% 
4.31% 
4.02% 
1.73% 
2.92% 
2.19% 
0.92% 
2.57% 
2.76% 
2.52% 

3.50% 
4.09% 
0.91% 
4.31% 
5.28% 
4.29% 
1.39% 
3.02% 
2.19% 
0.65% 
2.64% 
2.98% 
2.52% 

3.26% 
3.91% 
1.15% 
4.02% 
4.29% 
4.96% 
1.79% 
3.38% 
2.66% 
0.97% 
2.54% 
2.68% 
2.52% 

1.23% 
2.23% 
1.29% 
1.73% 
1.39% 
1.79% 
3.02% 
1.15% 
2.32% 
1.09% 
1.85% 
1.50% 
1.47% 

2.67% 
3.06% 
1.44% 
2.92% 
3.02% 
3.38% 
1.15% 
5.43% 
1.92% 
0.55% 
1.92% 
1.54% 
2.10% 

1.75% 
2.82% 
0.91% 
2.19% 
2.19% 
2.66% 
2.32% 
1.92% 
4.41% 
1.09% 
2.33% 
1.70% 
1.78% 

1.03% 
0.68% 
0.73% 
0.92% 
0.65% 
0.97% 
1.09% 
0.55% 
1.09% 
2.99% 
0.92% 
0.60% 
0.94% 

2.06% 
4.66% 
0.86% 
2.57% 
2.64% 
2.54% 
1.85% 
1.92% 
2.33% 
0.92% 
5.49% 
3.11% 
2.08% 

1.97% 
8.35% 
0.54% 
2.76% 
2.98% 
2.68% 
1.50% 
1.54% 
1.70% 
0.60% 
3.11% 
8.69% 
2.28% 

2.11% 
3.15% 
1.16% 
2.52% 
2.52% 
2.52% 
1.47% 
2.10% 
1.78% 
0.94% 
2.08% 
2.28% 
1.82% 
The variance of the ASX200 is 1.8244%. Using the ASX200 index as a proxy for the market portfolio, the betas for the 12 stocks can be determined as:
The table below shows the Beta for the 12 stocks
1.1552 

1.7251 

0.6333 

1.3791 

1.3793 

1.3806 

0.8081 

1.1518 

0.9740 

0.5179 

1.1411 

1.2494 
The table below shows the variance of each stock decomposed into its systematic and unsystematic components. Furthermore, it also shows the diversification ratio (R^{2}) for all 12 stocks and ASX200.
Variance 
Systematic Risk 
Unsystematic Risk 
R^{2} 

0.0374 
0.0243 
0.0130 
0.6512 

0.1127 
0.0543 
0.0584 
0.4817 

0.0395 
0.0073 
0.0322 
0.1854 

0.0489 
0.0347 
0.0142 
0.7094 

0.0528 
0.0347 
0.0181 
0.6574 

0.0496 
0.0348 
0.0149 
0.7007 

0.0302 
0.0119 
0.0183 
0.3941 

0.0543 
0.0242 
0.0301 
0.4453 

0.0441 
0.0173 
0.0268 
0.3926 

0.0299 
0.0049 
0.0250 
0.1635 

0.0549 
0.0238 
0.0311 
0.4331 

0.0869 
0.0285 
0.0585 
0.3275 
Assuming the risk free rate is 2%, the graph below shows the capital market line with positions of the 12 stocks and index.
The graph below shows the security market line with positions of the 12 stocks and ASX200 index.
From the graph above, we observe the following:
 The CSL, RIO and MQG stocks are undervalued as they appear above the SML.
 TLS, WES, WOW, WPL, CBA, NAB, WBC, ANZ and BHP stocks are overvalued as they appear below the SML.
In this section, three portfolios that track the ASX200 index were constructed out of the 12 stocks using 3 methods as defined below.
In this method, excel solver in the data analysis tool pack was used to determine the weights that satisfy the following conditions:
 Minimizes the portfolio variance (the portfolio variance is determined as the product of the weights and covariance variance matrix for the 12 stocks).
 Sum of weights equals must equal one i.e. =
 Exposure to Beta is one i.e. =
The table below shows the weights which satisfy the conditions above. Based on these weights, the portfolio’s minimized variance is 1.94%.
11.92% 

1.03% 

11.16% 

5.52% 

11.23% 

1.22% 

13.59% 

10.24% 

4.33% 

14.82% 

6.77% 

8.17% 
In this method, excel solver in the data analysis tool pack was used to determine the weights that satisfy the following conditions.
 Minimizes the portfolio variance which is the difference between the portfolio return and the index.
 Sum of weights equals must equal one
The table below shows the weights which satisfy the conditions above. Based on these weights, the portfolio’s minimized variance is 2.08%.
Weights 

11.97% 

3.74% 

10.82% 

7.36% 

10.54% 

3.37% 

12.67% 

9.70% 

3.92% 

12.85% 

6.50% 

6.55% 
In this method, the top 6 stocks from method 2 are chosen to construct the portfolio. Based on these stocks, the portfolio’s minimized RMSE is 0.857%. Hence removal of less risky assets from portfolio reduces the portfolio’s variance.
11.97% 

10.82% 

10.54% 

12.67% 

9.70% 

12.85% 
The table below summarizes the expected return, variance, beta and R^{2} for the three portfolios above.
0.0227 
0.0221 
0.0263 

1.94% 
2.08% 
0.86% 

0.9406 
0.9493 
0.8524 

1.0000 
1.0398 
0.6329 
Based on the above, we recommend that the tracker portfolio that mininmizes the RMSE should be used to track the index because it has the highest R2.
The graph below plots the tracker portfolio and index from September 22, 2017 to September 14 2018. The values have been normalized to base 100 for comparison.
The simple annualized returns for the tracker portfolio and ASX200 index are 9.4085% and 7.7145% respectively.
The table below summarizes the beta, R^{2} and RMSE for the tracker portfolio over the investment period.
1.059% 

0.00213% 

0.90308 

1.08502 
In summary, the tracker portfolio did not perfectly track the index, because it is impossible to achieve this objective unless the portfolio is rebalanced regularly.
To export a reference to this article please select a referencing stye below:
My Assignment Help. (2021). Constructing Tracking Portfolio With 12 Stocks And ASX200 Index. Retrieved from https://myassignmenthelp.com/freesamples/25503investmentanalysis/portfolioconstruction.html.
"Constructing Tracking Portfolio With 12 Stocks And ASX200 Index." My Assignment Help, 2021, https://myassignmenthelp.com/freesamples/25503investmentanalysis/portfolioconstruction.html.
My Assignment Help (2021) Constructing Tracking Portfolio With 12 Stocks And ASX200 Index [Online]. Available from: https://myassignmenthelp.com/freesamples/25503investmentanalysis/portfolioconstruction.html
[Accessed 16 July 2024].
My Assignment Help. 'Constructing Tracking Portfolio With 12 Stocks And ASX200 Index' (My Assignment Help, 2021) <https://myassignmenthelp.com/freesamples/25503investmentanalysis/portfolioconstruction.html> accessed 16 July 2024.
My Assignment Help. Constructing Tracking Portfolio With 12 Stocks And ASX200 Index [Internet]. My Assignment Help. 2021 [cited 16 July 2024]. Available from: https://myassignmenthelp.com/freesamples/25503investmentanalysis/portfolioconstruction.html.