Create a spreadsheet model that will compute bond valuation

Question

Create a spreadsheet model that will compute bond valuation, given the following assumptions:

1.      $1,000 face value

2.      5 year maturity

3.      Annual 6% coupon

4.      Fixed market yield

 

The model should show the periodic cash flows, and use these to calculate:

1. Yield to Maturity (YTM)
2. Modified Duration

 

In an output section on your spreadsheet, show the Current Price of the bond if the fixed yield is:

a)      8%

b)      3%

c)      Zero