use IF function

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 lue 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 rs. 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) (19 points) b) Find out the bond duration (2 points) 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 2 value of 4, 5, 6, 7,8, 9 and 10 years) (4 points) input section Bond market pric Par value Coupon rate Years renaining to naturity Hunber of tines interest paid requested to buy se Buy requested nu ber of 5 shares requested t Year 2 offered bond price 850 $1,000 6.00 years 2 tines 10.00 Original number ot bonds owned 20 Is the requsted transaction a good deal? (yes/No) Updated number ot bonds Profit from tbe transaction Bond value Bond Duration Tinc Bond Valuc $0.00 Cash flow PV T