Your wealthy uncle has invested some of his money on a government bond. The annual coupon rate of the bond is 6 percent. 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 percent 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) d) Calculate if requested buy/sell is a good choice, and update number of shares of holding, and profit of the transaction. Note, you need to use IF fuctions to consider both buy or sell offer in one cell. Input Section: Bond market price Sell Requested to buy/sell bonds(buy/s Requested number of bonds in the Requested transaction Offered bond price shares $1,000 6.00% 4 years 2 times 10.00% Par value Coupon rate Years remaining to maturity Number of times interest paid YTM Original number of bonds owned 5 2 850 Answer Section: Bond value $0.00 Bond Duration Is the requested transaction a good deal? (Yes/No) Updated number of bonds Profit from the transaction Solution section Period Bond Value Cash flow Discount CF PVT 0