Please complete questions with boxes in red

Bond Valuation with Ratings Array Term Bond rating Calculate the Coupon Payment and Price of the Bond do not round any calculations all calculations should result in positive numbers Be sure to watch the video for this exercise. Row # A 1.10% 1.20% 1.50% 1.60% 1.80% 2.00% 2.20% 2.30% 2.40% 2. 3 1.40% 2.60% Par Value: Coupon Rate: Bond Term (choose): Bond Rating (choose): 1,000.00 3.10% 2.80% 3.00% 4 1.60% 1.80% 6. 20 see rows 31 & 10 3.10% 3.50% 4.30% 32 below 3.50% 4.90% 3.90% 5.30% 6.00% 15 4.70% 6.10% 6.80% Issue Date Maturity date: 8/1/15 30 5.60% 10 8/1/35 calculate maturity date for chosen tem" Table row 9 use MATCH() function 6.10% use HLOOKUP() function Yield to Maturity: Coupon pmts p/year: 40 How many interest payments remain to be paid? What is the amount of each semiannual coupon payment? Calculate all of these as if the 15.50 bond pays two interest payments What is the PV of the Par Value of the bond? What is the PV of the semiannual coupon payments?* 300.66 per year 355.40 What is the Price of the Bond today? 656.06 if the tem is less than 5 years, assume it's a 0-Coupon Bond, otherwise there are 2 interest payments per year** Set Tem to 15, Bond rating to A... Value should be: Set Tem to 3, Bond rating to B.. Value should be: 953.63 947.66 LEAVE cells E11 & E12 set to 3 and B respectively for SELF GRADING and before turning in * Assume the first coupon payment is exactly 6 months from today unless the bond is 0-Coupon ** Use the Date(year,month,day) function. Add years of tem to year of issue date. Month and Day should be the same. = Date(Year)+tem, Month(),Day() a* Use the IF() function