need answer for problem4 !!! not 3.

Problem #3: A 4 year bond has semiannual coupons of 10% per annum. The continuously compounding yield is 15%. The bond has a face value of $100. You will be pricing the bond initially, and at future times throughout the life of the bond as it pulls to par at maturity, using the same continuously compounding yield throughout. Since the yield is given with continuous compounding, the usual formulas will not work without changing the yield to the equivalent discrete frequency. Instead, string out the cash flows (each of the coupons separately plus the final redemption value) in the columns of a spreadsheet similar to the one shown here. Each row will compute the bond price at a different point in time: B D F G H 1 2. Face cyour face value> Coupon rate your coupon rate> Frequency-m 2 Yield syour yield> 3 4 5 Coupon 1 Coupon 2 Coupon 3 ccoupon cash flow> . Redemption value =C3 etc... 6 - 7 - 8 Time of payment Time of payment 9 0.5 1 1 Time of payment 1.5 Value etc... Time of payment your maturity> Value 10 Value 11 12 12 12 13 14 14 15 16 17 18 19 Time by month Bond value Value 0 =sum all cells to the right = 0.08333 =sum all cells to the right 0.16667 =sum all cells to the right 0.25000 0.33333 Each cell in the table is the above cash flow valued at the time in column B for that row. If the time of payment is less than the time in column B, the value is zero, B, otherwise discount the cash flow back to the time given in column B for that row etc... You are to compute the value of the bond for each month in column B by adding up the outstanding cash flows along that row, each discounted from the time of its payment, back to the time at which you are valuing the bond. For the values in the table, use an "if' statement to set the value to zero if the "Time of payment" is less than the Time by month, otherwise discount that cash flow using the continuously compounding rate. In order to copy your "if' statement containing the formula to every cell in the table, place a "s" in front of the column letter and/or the row number in the cell references as appropriate. What is the initial value of the bond? Problem #3: 81.10 Answer correct to 2 decimals Just Save Submit Problem #3 for Grading Attempt #1 Attempt #2 Attempt #3 Attempt #4 Attempt #5 # Problem #3 Your Answer: Your Mark: a Problem #4: Referring to Problem #3 above, make a line graph of the bond value month by month over the life of the bond. Manually set the minimum value on the vertical axis of your graph to be 0. You should see a "shark fin" or "saw tooth pattern as coupons come off. Take a screen shot clearly showing your chart, paste the screen shot into an application (like Paint), and save it as a (.png) file. Upload your screenshot below. Problem #3: A 4 year bond has semiannual coupons of 10% per annum. The continuously compounding yield is 15%. The bond has a face value of $100. You will be pricing the bond initially, and at future times throughout the life of the bond as it pulls to par at maturity, using the same continuously compounding yield throughout. Since the yield is given with continuous compounding, the usual formulas will not work without changing the yield to the equivalent discrete frequency. Instead, string out the cash flows (each of the coupons separately plus the final redemption value) in the columns of a spreadsheet similar to the one shown here. Each row will compute the bond price at a different point in time: B D F G H 1 2. Face cyour face value> Coupon rate your coupon rate> Frequency-m 2 Yield syour yield> 3 4 5 Coupon 1 Coupon 2 Coupon 3 ccoupon cash flow> . Redemption value =C3 etc... 6 - 7 - 8 Time of payment Time of payment 9 0.5 1 1 Time of payment 1.5 Value etc... Time of payment your maturity> Value 10 Value 11 12 12 12 13 14 14 15 16 17 18 19 Time by month Bond value Value 0 =sum all cells to the right = 0.08333 =sum all cells to the right 0.16667 =sum all cells to the right 0.25000 0.33333 Each cell in the table is the above cash flow valued at the time in column B for that row. If the time of payment is less than the time in column B, the value is zero, B, otherwise discount the cash flow back to the time given in column B for that row etc... You are to compute the value of the bond for each month in column B by adding up the outstanding cash flows along that row, each discounted from the time of its payment, back to the time at which you are valuing the bond. For the values in the table, use an "if' statement to set the value to zero if the "Time of payment" is less than the Time by month, otherwise discount that cash flow using the continuously compounding rate. In order to copy your "if' statement containing the formula to every cell in the table, place a "s" in front of the column letter and/or the row number in the cell references as appropriate. What is the initial value of the bond? Problem #3: 81.10 Answer correct to 2 decimals Just Save Submit Problem #3 for Grading Attempt #1 Attempt #2 Attempt #3 Attempt #4 Attempt #5 # Problem #3 Your Answer: Your Mark: a Problem #4: Referring to Problem #3 above, make a line graph of the bond value month by month over the life of the bond. Manually set the minimum value on the vertical axis of your graph to be 0. You should see a "shark fin" or "saw tooth pattern as coupons come off. Take a screen shot clearly showing your chart, paste the screen shot into an application (like Paint), and save it as a (.png) file. Upload your screenshot below