FIND A SOLUTION AT Academic Writers Bay
Spreadsheet Project Task 2Today is 1 January 2018. Sue just purchased a 10-year 4.5% p.a. Treasury bond with
a face value of $100 at the yield rate of j2 = 4.3% p.a. The bond is redeemable at
par. The maturity date is 1 January 2028. On receipt of each coupon, Sue deposits
the coupon into a bank account earning the reinvestment rate. Sue predicts that
there are three potential states for the future economy. (25 marks)• State 1. Market yield is j2 = 5.05% p.a. from 2018 to 2023 and j2 = 5.2% p.a.
from 2024 to 2027. Reinvestment rate is j2 = 5.15% p.a. from 2018 to 2023
and j2 = 5.3% p.a. from 2024 to 2027.• State 2. Market yield is j2 = 4.55% p.a. from 2018 to 2022 and j2 = 4.7% p.a.
from 2023 to 2027. Reinvestment rate is j2 = 4.45% p.a. from 2018 to 2022
and j2 = 4.8% p.a. from 2023 to 2027.• State 3. Market yield is j2 = 3.8% p.a. from 2018 to 2025 and j2 = 3.7% p.a.
from 2026 to 2027. Reinvestment rate is j2 = 3.6% p.a. from 2018 to 2025 andj2 = 3.75% p.a. from 2026 to 2027.
a. [10 marks] Calculate Sue’s purchase price on 1 January 2018, ignoring future
economic states. For each economic state, calculate the accumulated value of
the reinvested coupons at the end of year 3, 6 and 9 (9 values in total).
b. [11 marks] For each economic state, calculate the Sue’s holding period yield
rate (express it as a j2 rate) for 3 years, 6 years and 9 years holding periods
(9 values in total).
c. [4 marks] Use a bar/column chart to plot your results in part b.
The deadline for Spreadsheet Project Task 2 submission is May 4th 2018, 9:00AM.
Present your answers to the above questions in a functional Excel spreadsheet (it
should be a XLSX or XLS file), giving each solution on a separate sheet (labelled
Part a, Part b, and Part c). Your spreadsheet should be clearly labelled and easy to
understand. Make sure you identify what the inputs and outputs are. Please include
the necessary information, e.g., title, axis titles, etc., in your plot. Document and
describe your working steps. Note that uploading a file can take up to 10 or 15
minutes. You need to submi…
Assignment status: Already Solved By Our Experts
(USA, AUS, UK & CA PhD. Writers)