Spreadsheet Project Task 2

Ruby purchased a 10-year 4.2% p.a. Treasury bond with a face value of $100 at a price of 98.545 10 years ago. The bond was redeemable at par. Immediately following the receipt of each coupon, Ruby deposited the coupon into a bank account earning a particular reinvestment rate. Over the 10 years the reinvestment rates Ruby earns are shown in table 1. At the time of purchase, Ruby used her financial modelling skills to model and predict the market rates of return rates for the next 10 years which are shown in table 2 below. Assume her 10-year forecast (in table 2) is actually correct. Hence, these rates represent the appropriate rates to discount Ruby’s future cash flows. Note that these rates are not constant, so Ruby would value a dollar paid at t = 3.5 years as $1/ (1+0.045/2) (1+0.044/2) if her valuation date was at t = 2.5 years.

Table 1: Reinvestment rates

Year 1—Year 3 (inclusive) j2 = 4.3%
Year 4—Year 7 (inclusive) j2 = 4.6%
Year 8—Year 10 (inclusive) j2 = 4.7%

Table 2: Market rates

Year 1—Year 3 (inclusive) j2 = 4.4%
Year 4—Year 7 (inclusive) j2 = 4.5%
Year 8—Year 10 (inclusive) j2 = 4.1%
  1. Calculate the accumulated value of Ruby’s reinvested coupons at the end of years 3, 6 and 9 (your calculation should refer to rates in table 1).
  2. Calculate Ruby’s sale price at the end of years 3, 6 and 9 (your calculation should refer to rates in table 2).
  3. Calculate Ruby’s holding period yield if she sells the bond after 3, 6 or 9 years (Express your answer as a rate). Calculate Ruby’s total realised compound yield rate (express your answer as a rate given that she holds the bond to maturity). Use a bar chart to plot your results of three holding period yields and the total realised compound yield rate.

Click on Buy Solution and make payment. All prices shown above are in USD. Payment supported in all currencies. Price shown above includes the solution of all questions mentioned on this page. Please note that our prices are fixed (do not bargain).

After making payment, solution is available instantly.Solution is available either in Word or Excel format unless otherwise specified.

If your question is slightly different from the above question, please contact us at info@myassignmentguru.com with your version of question.