Question

Please answer in excel with an explanation of each step

-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 aHoo15/2xuroowa if her valuation date was at t-2.5 years (25 marks). (1+0045/2)0-00s4/2) Table 1: Reinvestment rates Year 1 Year 3 (inclusive) J2-4.3% Year 4 Year 7 (inclusive) | J2 = 4.6% Year 8.- Year iO (inclusive) | = 4.7% Table 2: Market rates Year 1 Year 3 (inclusive) | J2 = 4.4% Year 4 Year 7 (inclusive)-tj2=-4.5%. Year 8 Year 10 (inclusive) | J2-4.1% a. [8.5 marks] 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)