Question

ABC Co. issued 11% bonds with a face amount of $8 million on Jan. 1, 2020, maturing Dec. 31, 2039. The market rate is 12%. Interest is paid twice a year on 6/30 and 12/31. ABC Co. uses the effective method for recording interest.

1. Create an amortization schedule via Excel with the following headlines: Date / Cash Interest / Effective Interest / Increase in Balance / Outstanding Balance. Round to the nearest whole number. Create a second table with your formulas.

2. Prepare journal entries for:

1. issuance on 1/1/2020
2. recording interest on 6/30/2020
3. recording interest on 12/31/2020
4. recording interest on 6/30/2039
5. payment of interest and principle on 12/31/2039.

3. What is the selling price using Excel’s PV function?

Answer 1

Face Value of Bonds PV factor (12%/2, 20 years x2) Present Value of Face Value $ 8,000,000 0.09722 $ 777,760 (a) Interest @ 5.5% semi annual PV annuity factor (6%, 20x2) Present Value of Interest $ 440,000 15.04630 $ 6,620,372 (b) Issue Price of Bond (a+b) $ 7,398,132

Amortization table

Effective Interest Increase in Outstanding Date Cash Int paid Exp Balance Balance 1/1/2020 $ 7,398,132 30/6/2020 $ 440,000 $ 443,888 $ 3,888 $ 7,402,020 12/31/2020 $ 440,000 $ 444,121 $ 4,121 $ 7,406,141 30/6/2021 $ 440,000 $ 444,368 $ 4,368 $ 7,410,510 12/31/2021 $ 440,000 $ 444,631 $ 4,631 $ 7,415,140 30/6/2022 $ 440,000 $ 444,908$ 4,908 $ 7,420,049 12/31/2022 $ 440,000 $ 445,203 $ 5,203 $ 7,425,251 30/6/2023 $ 440,000 $ 445,515$ 5,515 $ 7,430,767 12/31/2023 $ 440,000 $ 445,846 $ 5,846 $ 7,436,613 30/6/2024 $ 440,000 $ 446,197 $ 6,197 $ 7,442,809 12/31/2024 $ 440,000 $ 446,569 $ 6,569 $ 7,449,378 30/6/2025 $ 440,000 $ 446,963 $ 6,963 $ 7,456,341 12/31/2025 $ 440,000 $ 447,380 $ 7,380 $ 7,463,721 30/6/2026 $ 440,000 $ 447,823 $ 7,823 $ 7,471,544 12/31/2026 $ 440,000 $ 448,293 $ 8,293 $ 7,479,837 30/6/2027 $ 440,000 $ 448,790 $ 8,790 $ 7,488,627 12/31/2027 $ 440,000 $ 449,318 $ 9,318 $ 7,497,945 30/6/2028 $ 440,000 $ 449,877 $ 9,877 $ 7,507,821 12/31/2028 $ 440,000 $ 450,469 $ 10,469 $ 7,518,291 30/6/2029 $ 440,000 $ 451,097 $ 11,097 $ 7,529,388 12/31/2029 $ 440,000 $ 451,763 $ 11,763 $ 7,541,151 30/6/2030 $ 440,000 $ 452,469 $ 12,469 $ 7,553,621 12/31/2030 $ 440,000 $ 453,217 $ 13,217 $ 7,566,838 30/6/2031 $ 440,000 $ 454,010 $ 14,010 $ 7,580,848 12/31/2031 $ 440,000 $ 454,851 $ 14,851 $ 7,595,699 30/6/2032 $ 440,000 $ 455,742 $ 15,742 $ 7,611,441 12/31/2032 $ 440,000 $ 456,686 $ 16,686 $ 7,628,127 30/6/2033 $ 440,000 $ 457,688 $ 17,688 $ 7,645,815 12/31/2033 $ 440,000 $ 458,749 $ 18,749 $ 7,664,564 30/6/2034 $ 440,000 $ 459,874 $ 19,874 $ 7,684,438 12/31/2034 $ 440,000 $ 461,066 $ 21,066 $ 7,705,504 30/6/2035 $ 440,000 $ 462,330 $ 22,330 $ 7,727,834 12/31/2035 $ 440,000 $ 463,670 $ 23,670 $ 7,751,504 30/6/2036 $ 440,000 $ 465,090$ 25,090 $ 7,776,594 12/31/2036 $ 440,000 $ 466,596 26,596 $ 7,803,190 30/6/2037 $ 440,000 $ 468,191 $ 28,191 $ 7,831,382 12/31/2037 $ 440,000 $ 469,883 $ 29,883 $ 7,861,264 30/6/2038 $ 440,000 $ 471,676 $ 31,676 $ 7,892,940 12/31/2038 $ 440,000 $ 473,576 $ 33,576 $ 7,926,517 30/6/2039 $ 440,000 $ 475,591 $ 35,591 $ 7,962,108 12/31/2039 $ 440,000 $ 477,892 $ 37,892 $ 8,000,000

The formulas used for table :

A B C D E F Face Value of Bonds PV factor (12% 12, 20 ye. Present Value of Face V 8000000 0.09722 =F2-F3 (a) 1 2 3 4 5 6 7 8 9 10 11 12 Interest @ 5.5% semi ar PV annuity factor (6%, Present Value of Interes =F2-5.5% 15.0463 =F6-F7 (b) Issue Price of Bond (a+b) =F4+F8 Cash Int paid 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 Date 43831 30/6/2020 44196 30/6/2021 44561 30/6/2022 44926 30/6/2023 45291 30/6/2024 45657 30/6/2025 46022 30/6/2026 46387 30/6/2027 46752 30/6/2028 47118 30/6/2029 47483 30/6/2030 47848 30/6/2031 48213 30/6/2032 48579 30/6/2033 48944 30/6/2034 49309 30/6/2035 49674 30/6/2036 50040 30/6/2037 50405 30/6/2038 50770 30/6/2039 51135 =F6 =C15 =C16 =C17 =C18 =C19 =C20 =C21 =C22 =C23 =C24 =C25 =C26 =C27 =C28 =C29 =C30 =C31 =C32 =C33 =C34 =C35 =C36 =C37 =C38 =C39 =C40 =C41 =C42 =C43 =C44 =C45 =C46 =C47 =C48 =C49 =C50 =C51 =C52 =C53 Effective Interest Outstanding Exp Increase in Balance Balance =F10 =F14-67 =D15-C15 =F14+E 15 F15*60 =D16-C16 =F15+E 16 =F16*6% =D17-C17 =F16+E17 =F17*6% =D18-C18 =F17+E 18 =F1876% =D19-C19 =F18+E 19 =F19-6% =D20-C20 =F19+E20 =F2067 =D21-C21 =F20+E21 =F2167 =D22-C22 =F21+E22 =F22-6% =D23-C23 =F22+E23 =F23 6% =D24-C24 =F23+E24 =F24-6% =D25-C25 =F24+E25 =F25-6% =D26-C26 =F25+E26 =F26 67 =D27-C27 =F26+E27 =F27-672 =D28-C28 =F27+E28 =F2876% =D29-C29 =F28+E29 =F29-6% =D30-C30 =F29+E30 =F30*6% =D31-C31 =F30+E31 =F316% =D32-C32 =F31+E32 =F32-6% =D33-C33 =F32+E 33 =F33-677 =D34-C34 =F33+E 34 =F3476% =D35-C35 =F34+E35 =F35*6% =D36-C36 =F35+E36 =F36*6% =D37-C37 =F36+E 37 =F37-6% =D38-C38 =F37+E38 =F38 6% =D39-C39 =F38+E39 =F39-67 =D40-C40 =F39+E 40 =F40-6% =D41-C41 =F40+E 41 =F416% =D42-C42 =F41+E 42 =F42-6% =D43-C43 =F42+E 43 =F43*6% =D44-C44 =F43+E 44 =F4456% =D45-C45 =F44+E 45 =F45*6% U4E-L45 =F45+E 46 =F46*6% =D47-C47 =F46+E 47 =F47-67 =D48-C48 =F47+E 48 =F48-6% =D49-C49 =F48+E 49 =F49*6% =D50-C50 =F49+E50 =F50-6% =D51-C51 =F50+E51 |=F516% =D52-C52 =F51+E52 =F52-6% =D53-C53 =F52+E53 =F53-6%+166 =D54-C54 =F53+E54

Rega. Credit Date 1/1/2020 Journal Entries in the books of ABC Co. General Journal Debit Cash $ 7,398,132 Discount on Issue of Bonds $ 601,868 Bonds Payable (To record issue of bonds at discount) $ 8,000,000 Credit Debit 443,888 $ Reqb. Date General Journal 30/6/2020 Interest Discount on Issue of Bonds Cash (To record interest expense) $ 3,888 $ 440,000 Credit Debit 444,121 $ Reqc. Date General Journal 12/31/2020 Interest Discount on Issue of Bonds Cash (To record interest expense) $ 4,121 $ 440,000 Credit Debit 475,591 $ Req d. Date General Journal 6/30/2039 Interest Discount on Issue of Bonds Cash (To record interest expense) $ 35,591 $ 440,000 Credit Reqe. Date General Journal Debit 6/30/2039 Bonds Payable $ 8,000,000 Interest $ 477,892 Discount on Issue of Bonds Cash (To record interest expense and principal repayment) $ 37,892 $ 8,440,000

In case of any doubts or issues, please do comment below