Financial Management Project -

Overview: This assignment consists of 2 questions covering Bond Valuation and Portfolio Analysis.

Question 1: Bond Valuation

Let's suppose today is 16/01/2018, and you are observing the information for an Australian Government Bond which will mature on 21/04/2024. The first coupon was paid on 21/10/2012. The information is sufficient for you to identify the bond. Now collect the bond's additional information:

• Coupon rate, coupon frequency, face value, bond rating, last price and last yield to maturity as of today.
• Quarterly historical price/yield series of our bond from 31/12/2012 to 31/12/2017. (Bond Yield Series)
• Quarterly median forecasts on Australia Consumer Price by economists from 31/12/2012 to 31/12/2017. (Inflation Series AUCPIYOY Index)

Required:

A) Report the coupon rate, coupon frequency, face value, bond rating, price and yield to maturity as of today.

B) Given the yield to maturity and other characteristics you have collected, calculate the bond price as of today, assuming the face value is $100.

C) Report the correlation between Bond Yield and Inflation Series. Comment on the magnitude and implications of the correlation. (Hint: Your discussion should try to link nominal, real, and inflation rates.)

Question 2: Portfolio Analysis

You are a portfolio manager who has constructed an equity portfolio for a client who requires an annual return of 15%. Assume today is March 01, 2018 and you would like to determine how well your client's portfolio has performed over the last couple of months. On 31/12/2017, your equity analysts gave you a list of potentially best performing Australian stocks in 2018 which you have used to construct an equity portfolio. The list of eight stocks are below:

• Westfield Corp (WFD AU Equity)
• National Australia Bank (NAB AU Equity)
• Commonwealth Bank of Australia (CBA AU Equity)
• Woodside Petroleum Ltd (WPL AU Equity)
• BHP Billiton Ltd (BHP AU Equity)
• Westpac Banking Corp (WBC AU Equity)
• Rio Tinto Ltd (RIO AU Equity)
• Transurban Group (TCL AU Equity)

On 31/12/2017 your client wanted a portfolio with only three stocks. To achieve this objective, you built a three-asset portfolio using the mean-variance framework by implementing a three-step process:

1) Data collection: For each stock, you obtained the following information:

• Historical monthly returns on each stock for the previous 5 years from 31/12/2012 to 31/12/2017
• Price to Book ratio P/B as at 31/12/2017.

2) Screening: As a big believer in value-momentum-quality, you used these attributes to pick the best three stocks. For each stock, you obtained:

• The ratio P/B: This ratio captures value. The higher ratio indicates that a stock is more expensive or less cheap.
• Historical monthly volatility: This measure aims to capture quality. The lower volatility indicates better quality.
• Cumulative returns for the last 12 months up until 31/12/2017. The higher measure indicates winners (stocks performing well in the past 12 months).

Your goal was to pick cheap, high quality, and high past performing stocks. You determined that the easiest way to do this was to first rank the stocks based on the individual attributes, so that the higher the ranking, the more desirable the stocks. You then calculated the aggregate ranking score by summing up all three individual rankings for each stock. Finally, you chose the best three stocks based on this aggregate score.

3) Portfolio construction: After the screening process above, you constructed a three-asset portfolio relying on the mean-variance framework.

• Your client requires a portfolio return of 15% p.a. Also, you are not allowed to engage in any short-selling activities.
• The critical inputs for your portfolio construction are expected returns, volatility, and the correlation matrix. You use historical average returns, volatilities, and correlations to proxy for the expected values.

Finally, you would like to evaluate the performance of the portfolio by calculating the portfolio holding period return from 01/01/2018 to 28/02/2018.

Required:

A) Report the average return and volatility for each stock - on an annual basis.

B) Report the individual and aggregate ranking for each stock. What were the best three stocks that you chose on 31/12/2017?

C) Report the weights on each stock in the optimized portfolio. What is the Sharpe Ratio of your portfolio, assuming the risk free rate is 2.5% p.a.? You should demonstrate how you obtain these weights in Excel Solver.

D) Compute your portfolio holding period return for the first two months after the portfolio formation i.e. from 01/01/2018 to 28/2/2018.

E) The answer in part D is different from 2.5% per two months required by the client (15%). Your client demands an explanation for the difference.

How would you explain the difference to him? Your explanation should demonstrate:

a. Your understanding of the relevant theories,

b. Your understanding of the practical implementation.

F) Comment on the characteristics of your portfolio. What advice could you give the client to improve the portfolio? Your answer should relate to the risks of the portfolio.

Attachment:- Data File.rar