Group Spreadsheet Task: SM BONDS
Two Macquarie academics have come with a new type of bond to help generate affordable and efficient retirement income streams. You can read about it here. Your modelling team has been hired to do some preliminary descriptive work on the bond.
BACKGROUND
An SM bond is a 35-year Australian government bond with a face value of $1. They are marketed to consumers saving for retirement (who buy the S part) and investors (who buy the M part). The bonds are marketed as SM bonds (with all the characteristics of an Australian Treasury bond) to consumers. Immediately on purchase by a consumer, the M portion of the bond is then stripped out and marketed to investors.
For consumers (who buy the S part of the SM bond), they will receive $1 in 35 years' time if they are alive. If they die over this period, they receive nothing. Consumers who purchase an S bond cannot sell it over its lifetime. (Note that S bond holders do not receive coupon payments.)
For investors (who buy the M part of the SM bond), they receive 35 years of semi-annual coupon payments (just like an Australian Treasury bond). However, at maturity, in 35 years' time, they receive the face value of the bond only if the matching S bond holder is dead. If the matching S bond holder is alive, the M bond holder receives no repayment of the face value at maturity (they do, however, always receive coupon payments). An M bond holder can sell their holding of M bonds at any time.
TASK
You and your team have been hired to provide an illustrative spreadsheet of some of the features of the new SM bonds.
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.
ONLY question b and c.
other files are practicals, just for you to understand and follow.
Attachment:- Assignment.rar