Assessment Task 1
Be succinct: the fewer the better. Please do not misinterpret this: if you write nothing, your work will be awarded zero. If your answer is long but not relevant to the question, your work will be awarded zero as well. Your answer can exceed the word limit, but please consider what value the extra words add before you submit your assignment. Do you really need to write such a long answer?
Part A: Bond Pricing and Duration
Develop a spreadsheet in Excel that prices Australia Government Bond "5.50% Treasury Bonds due 21 April 2023" (ASX code: GSBG23), the term sheet is provided atAppendix 1.
Question A1: On 10 July 2018 14:00 Melbourne time, you observed the following information for Treasury bond GSBG23.
You can find the market quote price for GSBG23 is $115.89 for every $100 par value. Calculate the yield-to-maturity for bond GSBG23 on 10 July 2018 14:00. Calculate the cash price for GSBG23 on that time. (10 Marks) Assume 3 month later, i.e., on 10 October 2018 14:00, you observe that the prevalent market yield- to-maturity is 4% p.a. semiannual compounding, what would be the cashpriceof this bond on 10 October 2018 14:00?
Note: From above table, you may notice that the next payment date for GSBG23 is 22/10/2018 rather than 21/10/2018 because 21/10/2018 is Sunday. To simplify the calculation in this question, you can ignore this issue and continue to take 21/10/2018 as next payment date.
Question A2: Construct a graph for the above bond showing the relationship between bond price (Y-axis or vertical axis) and yield -to-maturity (X- axis or horizontal axis) for a range of yield-to-maturity (annualized) of 0.5% through 8.0% with step size of 0.5%. All yields are annualized and semiannual compounding. Discuss the relationship between bond price and yield-to-maturity. (100 - 150 words)
Question A3: Compute the duration of the bond on GSBG23 on 10 July 2018 14:00. Using graphs todemonstrate the effect of coupon and interest rate on duration respectively. Briefly discuss your graph results. (200 words)
Note:
(1) You can solve all questions without any need of Excel build-in Functions, except "goal seek..." which is necessary to find yield-to-maturity. If you choose to do it manually, i.e., use brute-force searching, it would be very time-consuming.
(2) In order to finish Part A, you need skills to use Excel. The information contained in appendix A may provide some extra help.
(3) You are encouraged to use Excel build-in functions as long as you can use them correctly. Warning: Some functions in Excel may cause confusion. For example, the description of"PRICE" function is "Returns the price per $100 face value of a security that pays periodicinterest." However, it does not specific whether it isquote priceorcash price. The output of"PRICE" function may not give you exactly what you want (it does give you useful output, from there, you can take few more steps to get what you exactly need) and you would better to check the detail of the equation that function "PRICE" use to produce output. This is only one example to show you that you must be cautious when you use build-in functions, which are black boxes.
Attachment:- Derivatives and fixed income securities.rar