Using Data Analytics to Analyze Data DA13.1 Data analytics can be used to understand present values. Problem GlobalPie plans to issue 10 year, semi annual bonds. They want to know what the proceeds will be under various scenarios. Set up a chart that uses a formula to state whether the bond is at par, at a discount, or at a premium. Show the proceeds, the interest rates, and the payments under the three scenarios. Market Rate Bond term years Interest paid times per year Bond interest rate Face Value Instructions: Scenario 1 Scenario 2 Scenario 3 10% 10 2 10% 12% 8% $ 800,000 Student Work Area a. Formula for issue Scenario 1 Scenario 2 Scenario 3 The bond is issued at There are four parts to this problem. Use Excel to perform the following: a. Use a formula to calculate whether the bond is issued at a discount, at par, or at a premium. b. Use formulas to calculate the payment rate, market rate, n, PMT, FV, and PV given the information above for each of the three scenarios. Label with the outcomes in part a. c. Graph the PMT, FV, PV, Bond interest rate, Market Rate for the three scenarios. d. Discuss what you see in the graph in part c. b. Response to part b Bond Interest Rate Market Rate In PMT FV PV c. Chart for part c