Use Excel to prepare a Bond Interest and Discount Amortizati

Use Excel to prepare a Bond Interest and Discount Amortization Table using the following information:

$50,000,000 face value

coupon rate of interest - 6%

market rate of interest - 7%

term - 10 years payable semi-annually

First calculate the proceeds received upon issuance and the amount of the discount

Solution

Step-1:Calculation of proceeds received from issuance of bond and discount on issuance of bond. Proceeds from issuance of bond is the present value of cash flow from bond discounted at market rate. Face Value a $       5,00,00,000 Semi annual coupon b=a*3% $           15,00,000 Semi annual yield 3.50% Semi annual period 20 Present Value of annuity of 1 = (1-(1+i)^-n)/i Where, = (1-(1+0.035)^-20)/0.035 i 3.50% =                   14.2124 n 20 Present Value of 1 = (1+0.035)^-20 =                     0.5026 Period Cash flow Discount factor Present Value 1-20 $     15,00,000    14.2124            2,13,18,605 20 $ 5,00,00,000      0.5026            2,51,28,294 Total            4,64,46,899 Now, Face Value of bonds $       5,00,00,000 Less proceeds recived from issuance of bonds            4,64,46,899 Discount on bonds payable $           35,53,101 Step-2:Bonds interest and discount amortization table (effective interest method) Semi annual period Coupon interest paid in cash Interest expense Discount amorization Carrying Value 0         4,64,46,899 1 $ 15,00,000               16,25,641             1,25,641         4,65,72,541 2 $ 15,00,000               16,30,039             1,30,039         4,67,02,580 3 $ 15,00,000               16,34,590             1,34,590         4,68,37,170 4 $ 15,00,000               16,39,301             1,39,301         4,69,76,471 5 $ 15,00,000               16,44,176             1,44,176         4,71,20,647 6 $ 15,00,000               16,49,223             1,49,223         4,72,69,870 7 $ 15,00,000               16,54,445             1,54,445         4,74,24,315 8 $ 15,00,000               16,59,851             1,59,851         4,75,84,166 9 $ 15,00,000               16,65,446             1,65,446         4,77,49,612 10 $ 15,00,000               16,71,236             1,71,236         4,79,20,849 11 $ 15,00,000               16,77,230             1,77,230         4,80,98,078 12 $ 15,00,000               16,83,433             1,83,433         4,82,81,511 13 $ 15,00,000               16,89,853             1,89,853         4,84,71,364 14 $ 15,00,000               16,96,498             1,96,498         4,86,67,862 15 $ 15,00,000               17,03,375             2,03,375         4,88,71,237 16 $ 15,00,000               17,10,493             2,10,493         4,90,81,730 17 $ 15,00,000               17,17,861             2,17,861         4,92,99,591 18 $ 15,00,000               17,25,486             2,25,486         4,95,25,076 19 $ 15,00,000               17,33,378             2,33,378         4,97,58,454 20 $ 15,00,000               17,41,546             2,41,546         5,00,00,000 (Straight Line method) Semi annual period Coupon interest paid in cash Interest expense Discount amorization Carrying Value 0         4,64,46,899 1 $ 15,00,000               16,77,655             1,77,655         4,66,24,554 2 $ 15,00,000               16,77,655             1,77,655         4,68,02,209 3 $ 15,00,000               16,77,655             1,77,655         4,69,79,864 4 $ 15,00,000               16,77,655             1,77,655         4,71,57,519 5 $ 15,00,000               16,77,655             1,77,655         4,73,35,174 6 $ 15,00,000               16,77,655             1,77,655         4,75,12,829 7 $ 15,00,000               16,77,655             1,77,655         4,76,90,484 8 $ 15,00,000               16,77,655             1,77,655         4,78,68,140 9 $ 15,00,000               16,77,655             1,77,655         4,80,45,795 10 $ 15,00,000               16,77,655             1,77,655         4,82,23,450 11 $ 15,00,000               16,77,655             1,77,655         4,84,01,105 12 $ 15,00,000               16,77,655             1,77,655         4,85,78,760 13 $ 15,00,000               16,77,655             1,77,655         4,87,56,415 14 $ 15,00,000               16,77,655             1,77,655         4,89,34,070 15 $ 15,00,000               16,77,655             1,77,655         4,91,11,725 16 $ 15,00,000               16,77,655             1,77,655         4,92,89,380 17 $ 15,00,000               16,77,655             1,77,655         4,94,67,035 18 $ 15,00,000               16,77,655             1,77,655         4,96,44,690 19 $ 15,00,000               16,77,655             1,77,655         4,98,22,345 20 $ 15,00,000               16,77,655             1,77,655         5,00,00,000