4. Using only the input data highlighted in green on the tab labeled Questions 4 and 5, create a macro in Visual Basic for Applications (VBA) that uses a For Next loop to calculate the Price (at time 0) of a semi-annual coupon paying bond with the following set of semi-annual cash flows. To receive full credit for this question you must keep the data structure/locations (in green) as given to you in the Excel template.

5. Now suppose that there are 184 days in the upcoming six month coupon period. What will be the clean price of the bond 87 days from now? Answer this question by creating a macro in Visual Basic for Applications using only the data highlighted in green below provided on the tab labeled Questions 4 and 5 and the price of the bond (at time 0) that you calculated in Problem 4 (in yellow):

A B C D E F G 1 Six Month YTM 2.2500% 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 4) 26 27 28 29 30 Days in Coupon Period 31 # of days in future 32 33 5) 34 25 Time Cash Period Flow 1 $ 25.00 2 $ 25.00 3 $ 25.00 4 $ 25.00 5 $ 25.00 6 $ 25.00 7 $ 25.00 8 $ 25.00 9 $ 25.00 10 $ 25.00 11 $ 25.00 12 $ 25.00 13 $ 25.00 14 $ 25.00 15 $ 25.00 16 $ 25.00 17 $ 25.00 18 $ 25.00 19 $ 25.00 20 $ 1,025.00 Time 0 Price = PV of Cash Flow 24.45 48.36 71.75 94.62 116.99 138.86 160.26 181.18 201.64 221.66 241.23 260.37 279.09 297.40 315.30 332.82 349.94 366.69 383.07 16362.81 184 87 Clean Price = A B C D E F G 1 Six Month YTM 2.2500% 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 4) 26 27 28 29 30 Days in Coupon Period 31 # of days in future 32 33 5) 34 25 Time Cash Period Flow 1 $ 25.00 2 $ 25.00 3 $ 25.00 4 $ 25.00 5 $ 25.00 6 $ 25.00 7 $ 25.00 8 $ 25.00 9 $ 25.00 10 $ 25.00 11 $ 25.00 12 $ 25.00 13 $ 25.00 14 $ 25.00 15 $ 25.00 16 $ 25.00 17 $ 25.00 18 $ 25.00 19 $ 25.00 20 $ 1,025.00 Time 0 Price = PV of Cash Flow 24.45 48.36 71.75 94.62 116.99 138.86 160.26 181.18 201.64 221.66 241.23 260.37 279.09 297.40 315.30 332.82 349.94 366.69 383.07 16362.81 184 87 Clean Price =