Question 1
This question has seven parts (a) - (g). Attempt all of them.
Refer to worksheet "Data Q1" in the file 2018 FIN205.xlsx to answer this question. This worksheet contains monthly data on yields of Australian Government Bonds and New South Wales (NSW) Treasury Bonds from January 2008 - December 2009. You may use the worksheet "Yield curves" as a template to answer part "a" of the question.
For questionsa - e, consider Australian Government Bonds only.
a. Pick the month you were born. Draw the yield curve for that monthin 2008 and 2009. Also, draw the yield curve for the month falling exactly in the middle. (For instance, if you were born in April, draw the yield curves for April 2008, October 2008 and April 2009.)
b. What should be the shape of the yield curve under normal circumstances? Why?
c. Compare the shapesof the three yield curves that you have drawn. From the yield curves, can you infer anything about the market's expectation about the future of Australian economy?
d. What is the yield on 2-year bond in January 2008? Under the expectations theory, what interest rate do investors expect on a 1-year bond next year(i.e., January 2009)? Using the same method calculate the expected interest rates on 1-year bonds for all the months of 2009.
e. How do the expected interest rates compare with the actual interest rates in 2008? Do your calculations validate/not validate the expectations theory? List some possible reasons why the expectations hypothesis may not hold.
For questionsf - g, consider bothAustralian Government Bondsand NSW Treasury Bonds for theperiod January 2008 - December 2009.
f. For 3, 5 and 10 year bonds what was the average spread between Australian Government Bonds andNSW Treasury bonds?
g. Draw a graph plotting the spread between Australian Government Bonds and NSW Treasury Bonds. Comment on the pattern of the spread. Can you provide an explanation for this pattern?
Question 2
This question has six parts (a) - (f). Attempt all of them.
Refer to worksheet "Data Q2" in the file 2018 FIN205.xlsx to answer this question. This worksheet contains data on the adjusted end of month share price from January 2015 - June 2018 of two companies: AMP Limited (AMP.AX) and Qantas Airways Limited (QAN.AX). You will also find the value of S&P/ASX200 market index over this period. The basic data is provided in columns A - D. You will need to manipulate the data to answer the questions below. Some excel formulas are provided in the worksheet.
Pick the month you were born. For you the relevant data will be month 2015- June 2018.
a. Suppose you are a financial analyst working for Star Bank.Use an appropriate graph to plot the AMP and Qantas share prices from month 2015 - June 2018. Provide a brief comment on the pattern you observe.
b. Compute the monthly return on AMP, Qantas shares and the ASX200 index by using the following formula:
Returnt = (Pt - P(t-1)/P(t-1)×100,
where, Pt is the share price (or the value of ASX200 index) in time period t. (Please note that you lose the first observation when calculating returns. For instance, if you were born in June, you will only be able to calculate the returns only from July 2015.)
Compute the mean, variance and standard deviationof all three returns. Based on your calculations what can you infer about the performance of AMP, Qantas and ASX200 index over the last three years? Provide a brief explanation.
c. Calculate the beta of AMP and Qantas. For this you will have to first calculate the covariance of AMP and Qantas returns with the ASX index. Use the following formula in excel:
=covariance(Return of company X,Return of ASX200)
Beta's can then be calculated by
βX= (Covariance(Return of Company X, Return of ASX200))/(Variance (return of ASX200)).
d. Briefly explain why AMP and Qantas have high/low values of beta.
e. Assume the monthly risk-free rate to be 0.14%. According to the Capital Asset Pricing Model (CAPM) are the AMP and Qantas shares are underpriced? Explain.
f. Write a short report for the management of your company evaluating the past performance and future prospects of AMP and Qantas shares.
Instructions for part (f):For this question you are expected to do some research on AMP and Qantas.
In addition to these websites you are free to use any other appropriate website/article as reference for your report. Make sure that you cite all your references.
You can find the financial statements of AMP and Qantas by doing a search on the Yahoo finance website. Also, search for news coverage and announcements related to these companies. Classify the financial situation and the news/announcements as "positive" or "negative". Summarize how the share prices have reacted to "positive/negative" developments in the past. Use your findings to provide some predictions for future prospects of these companies.
Attachment:- Major Assignment.rar