Spreadsheet Project Task 2

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. (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 and j2 = 3.75% p.a. from 2026 to 2027.
  1. [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).
  2. [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).
  3. [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 submit your file at least 20 minutes before the deadline to ensure a successful submission.

$40.00 – Buy Solution

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.