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 Row # 3 4 A B Be sure to watch the video for this exercise. 1.10% 1.50% 2.30% 1.20% 2 1.60% 2.40% 3 1.40% 1.80% 2.60% Par Value: 1.60% 2.80% 1,000.00 3.10% 4 2.00% Coupon Rate: Bond Term (choose): Bond Rating (choose): 2.20% 3.50% 3.00% 4.30% 4.70% 5 1.80% 3.10% 6. 10 20 10 11 see rows 31 & 32 below 15 3.50% 3.90% 12 5.30% 20 4.90% 6.10% 13 8/1/15 8/1/35 calculate maturity date for chosen term** 5.60% Issue Date 30 6.00% 6.80% 14 10 Maturity date: 15 16 Table row 9 use MATCH() function 6.10% use HLOOKUP() function 17 Yield to Maturity: 18 19 Coupon pmts p/year: 20 21 22 How many interest payments remain to be paid? What is the amount of each semiannual coupon payment? 23 Calculate all of these as if the 24 bond pays two interest payments 25 What is the PV of the Par Value of the bond? per year 26 What is the PV of the semiannual coupon payments?* 27 28 What is the Price of the Bond today? If the term is less than 5 years, assume it's a 0-Coupon Bond, otherwise there are 2 interest payments per year*** 29 30 Set Term to 15, Bond rating to A.. Value should be: Set Term to 3, Bond rating to B.. Value should be: 953.63 31 947.66 LEAVE cells E11 & E12 set to 3 and B respectively for SELF GRADING and before turning in 32 33 * 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 term to year of issue date. Month and Day should be the same. 34 35 = Date(Year()+term, Month(), Day() 36 *** Use the IF() function 37