Spreadsheet Project Task
Today 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.
• 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 and j2 = 3.75% p.a. from 2026 to 2027.
a. 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. 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. Use a bar/column chart to plot your results in part b.
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 submit your file at least 20 minutes before the deadline to ensure a successful submission.