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)

>> CLICK HERE TO ORDER 100% ORIGINAL PAPERS FROM Academic Writers Bay <<

#065