Your wealthy uncle has invested some of his money on a government bond. The annual coupon rate of the bond is 6%. The face value is $1000. Interest is paid each May and November, implying that coupon is made twice a year. The face value will be paid out in 4 years. The market interest rate is 10% and your uncle is not sure how much he should pay to buy the bond.
a) He asks you to prepare a spreadsheet that shows the value of the bond, cash flows from the bond each period, yield to maturity of the bond, and the price of the bond in each period, so if he decides to sell the bond in a few years he could sell it at the right price. He also wants your model to be complete enough to answer his questions if he buys other bonds in future with different maturity, coupon payments, etc.(You need to use IF function to make your model flexible )
b) Find out the bond duration
c) Perform a sensitivity analysis of bond duration to both coupon rate (with the value of 0%,2%,4%,6%,8%, 10%) and Maturity (with the value of 4,5,6,7,8,9, and 10 years)
Input section:
Par value: 1000
Coupon rate: 0.06
Years remaining: 4
Number of times: 2
YTM: 0.1
Original number : 20
Requested to buy/sell bonds: Buy
Requested number of bonds in the transaction: 5 shares
Requested transaction period: Year 2
Offered bond price: 850