a. Create a spreadsheet to determine the yield on a 35-year Australian Treasury bond with a face value of $1 000 000. Inputs to the spreadsheet (used on this sheet and the others following) are coupon rate of the SM bond, the price paid by a consumer, the current probability a consumer survives from age 35 to age 70 and the fortnightly age pension rate.
b. Create a spreadsheet which takes the information calculated (and given) above to determine the price a 35 year old consumer would have to pay to receive a fortnight`s age pension in 35 years. That is, if the current fortnightly age pension is $658.70, determine the price now to purchase 659 (always round up) S bonds, each with a face value of $1.
c. Create a spreadsheet which takes the information calculated (and given) above to price a 35-year M bond with a face value of $100. Assume the M bond is matched to the 35 year old consumer mentioned above.
d. Returning to your first spreadsheet, insert a pie chart which gives the break-up of the value of the SM bond into its S and M components for the particular case of a 35 year investor with the survival probability that has been given.