Please help thank you, Can I get formula please too.

Spreadsheet Exercise: Problem 11.16 1 2 Lynn Parsons is considering investing in either of two outstanding bonds. The bonds both have $1,000 par values and 11% coupon interest rates and pay annual interest. Bond A has exactly 5 years to maturity, and bond B has 15 years to maturity. Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. 3 4 5 5 $1,000 11% 6 Par Value Coupon Rate Years to Maturity (Bond A) Years to Maturity (Bond B) 7 5 15 8 9 10 11 12 13 To Do a. Calculate the value of bond A if the required return is (1) 8%, (2) 11%, and (3) 14%. b. Calculate the value of bond B if the required return is (1) 8%, (2) 11%, and (3) 14%. c. Discuss the relationship between time to maturity and changing required returns. d. If Lynn wanted to minimize interest rate risk, which bond should she purchase? Solution 14 15 16 17 18 19 a. Calculate the value of bond A if the required return is (1) 8%, (2) 11%, and (3) 14%. b. Calculate the value of bond B if the required return is (1) 8%, (2) 11%, and (3) 14%. 20 21 Interest Payment 22 23 Required Return Value of Bond A Value of Bond B 24 8% 11% 14% 25 26 27 28 29 30 c. Discuss the relationship between time to maturity and changing required returns. The table clearly shows that price sensitivity to changes in interest rates as the years to maturity increase. d. If Lynn wanted to minimize interest rate risk, which bond should she purchase? 31 32 33 34 If Lynn wants to minimize interest rate risk, she should choose the 35 36 37 Requirements Points 11 na 11.16 + Ready Grader - Instructions Excel 2016 Project Smart Zutter. Problem P11.16 (Excel). V1 Project Description: In this problem, you will calculate the values of bonds, determine the relationship between time to maturity and changing required returns, and choose the bond that should be purchased to minimize interest rate risk. + Steps to Perform: Step Instructions Points Possible 1 1 Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. Start Excel. Download and open the workbook named: Smart_Zutter_Problem_11.16_Start.xlsx In cell D21, by using cell references, calculate the interest payment for any of the bonds. 2 1 In cell D24, by using cell references and the function PV, calculate the value of Bond A for the required return of 8%. Note: The output of the expression or function you typed in this cell is expected as a positive number 3 1 To calculate the values of bonds at different interest rates, copy cell D24 and paste it onto cells D25:D26 and E24:E26. Note: The outputs of the expressions or functions you typed in these cells are expected as positive numbers. 4 In cell F30, determine the relationship between time to maturity and changing required returns. 1 5 In cell F34, choose the bond that should be purchased to minimize interest rate risk. 1 6 0 Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. Total Points 5 EF