Individual Assignment 2: Portfolio Analysis (Assessment Weight: 15%) Individual Assignment (approximately 1,000 words should be enough, plus workings and diagrams) Please do not submit spreadsheets....

1 answer below »
please finish the assignment accord this excel file


Individual Assignment 2: Portfolio Analysis    (Assessment Weight: 15%) Individual Assignment (approximately 1,000 words should be enough, plus workings and diagrams) Please do not submit spreadsheets. All relevant workings and analysis should be included in a word or pdf document. You are required to construct an investment portfolio containing investments in 5 Australian companies, chosen from stocks listed in the S&P/ASX 200. Moreover, you are required to graph two estimated efficient frontiers for your portfolio.   To complete your graphs, you are required to determine estimated expected returns, standard deviations and correlations of the 5 stocks in your portfolio. In order to complete this task, you should download the ASX 200 components file: ASX200 components .xls  download and select 5 stocks from the list, with a view to building a reasonably well-diversified portfolio. Then, go to http://au.finance.yahoo.com/  (Links to an external site.)to find these stocks selected. When you search for a certain stock, make sure to use the code and add ".AX" at the end as some stocks are cross-listing on multiple exchanges internationally. (For example, to find Newcrest Mining Ltd shares, search "NCM.AX"). For each company, go to ‘Historical Data’ and obtain monthly data covering the most recent 24 months up to and including the end of February 2021 (eg., February 28 2021 data). Your first adj. closing price will be February 28, 2019. For each month, use the Adjusted Close prices to estimate the monthly rate of return.  You will have a sample (size 24) of monthly returns for each of your 5 companies, covering the same two-year period. (Ensure your sample of 5 stocks is not identical to that of any other group. Do not just select the largest companies. There are 2,535,650,040 available combinations, so this should be easy for you to achieve. An identical portfolio between two groups is likely evidence of inappropriate collusion, and will lead to an investigation).   (a)        Using the Solver function in Microsoft Excel, determine the optimal weights for each stock in your portfolio across an appropriate set of monthly expected returns and graph   (i)         the unrestricted efficient frontier. (ii)        the restricted efficient frontier.  (50%)   (b)        Explain the sense in which your frontiers can be described as ‘efficient", and the difference (if any) between your restricted and unrestricted efficient frontiers. Which is the more efficient? (15%)   (c)        Suppose you are able to borrow or lend limitless amounts at a certain rate of interest.  Illustrate and explain the implications for the efficient frontier, and also for portfolio selection decisions where investors have the same information but differing degrees of risk aversion.  No calculations are necessary for this section.  (10%)   (d)  Suppose the rate of interest on borrowing is higher than the rate of interest at which you can lend. Illustrate and explain the implications for the efficient frontier, and also for portfolio selection decisions where investors have the same information but differing degrees of risk aversion.  No calculations are necessary for this section.  (15%)   (e)       With reference to your research (i)         Suggest one additional way in which your frontiers could be made more efficient. (5%) (ii)        Identify one key limitation of your analysis. (5%)
Answered 1 days AfterMay 04, 2021

Answer To: Individual Assignment 2: Portfolio Analysis (Assessment Weight: 15%) Individual Assignment...

Shakeel answered on May 05 2021
134 Votes
In 1952, Markowitz propounded famous “Modern Portfolio Theory” where he discussed in detail the framework of assets allocation through the technique of Mean-variance Optimization. He asserted the Investor’s preference of maximum return over minimum risk and then, provided an effective tool of constructing the efficient frontier that is combination of all possible portfolios of max return at given level of risk.
Figure 1 shows the efficient frontier that is highlighted in blue. Portfolio on the efficient frontier yields maximum return over a particular level of risk. The whole graph is called Global minimum variance frontier that represents all possible portfolio made of the given assets in different proportion. Portfolios above the frontier are non-achievable while all portfolios below the frontier (as shown by red dots in figure) are not desirable.
Figure 1: Efficient frontier
(a)
Here, to construct the portfolio, five stocks are taken –
1. Brambles ltd (BXB.AX)
2. Transurban Group (TCl.AX)
3. Rio Tinto (RIO.AX)
4. Sydney Airport (SYD.AX)
5. AGL Energy ltd (AGL.AX)
The closing monthly prices of all the five stocks are taken from Mar 2019 to April 2021. Their monthly returns are calculated by using the formula – (Pt – Pt-1) / Pt-1. Where, Pt is the price at time ‘t’ and Pt-1 is the price at time ‘t-1’.
Table 1 shows the Average monthly returns and Std. Deviation on all five stocks.
Table 1: Return and Risk
    
    BXB.AX
    TCL.AX
    RIO.AX
    SYD.AX
    AGL.AX
    Avg. Monthly return
    -0.0024
    0.0034
    0.0183
    -0.0031
    -0.0302
    Std. Deviation
    0.0694
    0.0642
    0.0683
    0.1030
    0.0633
    
    
    
    
    
    
Once, the monthly returns are calculated, the Correlation matrix is constructed that is shown in table 2 below.
Table 2: Correlation matrix
     
    BXB.AX
    TCL.AX
    RIO.AX
    SYD.AX
    AGL.AX
    BXB.AX
    1
    0.6910837
    0.3328814
    0.6560689
    0.5734334
    TCL.AX
    0.6910837
    1
    0.2527733
    0.7047281
    0.4130836
    RIO.AX
    0.3328814
    0.2527733
    1
    0.2302402
    0.012638
    SYD.AX
    0.6560689
    0.7047281
    0.2302402
    1
    0.31514
    AGL.AX
    0.5734334
    0.4130836
    0.012638
    0.31514
    1
Portfolio’s return and risk are calculated by using the formula –
To find the efficient frontier, SOLVER function in excel is used where the Objective function is maximizing the return for a given level of risk.
(i)
For the Unrestricted efficient frontier, the optimal weights for each stock in your portfolio across an appropriate set of monthly expected returns is given in table 3 and the graph is given in Graph 1 below.
Table 3: Optimal weight of stocks in...
SOLUTION.PDF

Answer To This Question Is Available To Download

Related Questions & Answers

More Questions »

Submit New Assignment

Copy and Paste Your Assignment Here