***PLEASE SHOW EXACT EXCEL FORMULA***

**UTILIZE SOLUTION SECTION OF THE TIME PERIOD, BOND VALUE, PERIOD CASH FLOW***

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) input section: Bond market price Par value Coupon rate Years renaining to maturity Number of times interest paid YTN Original number of bonds ovned shares requested to buy/se Buy requested number of 5 requested Year 2 offered bond price 850 $1.000 6.00% 4 years 2 tines 10.00% 20 Answer Section: Bond value ? Bond Duration ? Is the requested transaction a good deal? (Yes/No) Updated nunber of bonds Profit from the transaction Solution section Tine Period Bond Value Period Cash flow 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) input section: Bond market price Par value Coupon rate Years renaining to maturity Number of times interest paid YTN Original number of bonds ovned shares requested to buy/se Buy requested number of 5 requested Year 2 offered bond price 850 $1.000 6.00% 4 years 2 tines 10.00% 20 Answer Section: Bond value ? Bond Duration ? Is the requested transaction a good deal? (Yes/No) Updated nunber of bonds Profit from the transaction Solution section Tine Period Bond Value Period Cash flow