Reference no: EM131099007
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
|
Job description of a certified public accountant
: Write the answer as a discussion post. What is the job description of a Certified Public Accountant?
|
|
What is difference between private ownership and open access
: Suppose you are considering buying a gold deposit. It will cost $1 million per year to construct a mine so that gold can be extracted. The construction period lasts 3 years. In the fourth year, production starts. What is the difference between privat..
|
|
When the fed uses expansionary monetary policy
: When the Fed uses expansionary monetary policy, Consumer spending and autonomous government spending increase while investment spending decreases. Consumer spending, investment spending, and autonomous government spending decrease.
|
|
The economy short-run and long-run phillips curves
: Suppose the economy is in long-run equilibrium. (a) Draw the economy’s short-run and long-run Phillips curves. (b) Suppose a wave of business pessimism reduces aggregate demand. Show the effect of this shock on your diagram from part (a).
|
|
Determine the yield on a australian treasury bond
: 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
|
|
Calculate contribution margin ratio at the projected levels
: You are pitching a marketing proposal to a company that sells electronic equipment. For a particular product line, their current sales price is $20 per unit, cost is $9 per unit and they have $20,000 in fixed costs associated with this line. Calculat..
|
|
Should the state of texas implement term limits
: Should the State of Texas implement term limits for our members of the appointed boards and committees, since most boards and committees handle the day-to-day operations of state government? Why?
|
|
How will activity impact work as nonprofit administrator
: What field of nonprofit activity do you believe is experiencing the most changes and why? How will this activity impact your work as a nonprofit administrator.
|
|
Construction equipment-trailers and crutches
: Round Table Rental Yards provides construction equipment, trailers, crutches, etc., on short-term rentals. Historically, Art, the owner, has purchased the items that he rents out, but his business has been expanding so rapidly that he is considering ..
|