Using the hypothetical bond example provided below, create an effective interest
ID: 2427159 • Letter: U
Question
Using the hypothetical bond example provided below, create an effective interest amortization table in Excel for the entire life of the bond using the following columns: payment number, payment date, beginning carrying value, effective interest rate, interest expense, cash (interest) paid, discount/premium on bond payable (amortization amount), and ending carrying value.
$10,000,000, 4% bond
Issued on 1/1/13
Matures in 10 years
Semiannual interest payments on 6/30 and 12/31 of each year
Market rate for bonds of this type was 6% at the time of their issue
Explanation / Answer
A B C D E F G $ Date Interest Payment @2% Interest expenses at 3%*G Amortization of Bond C-B cr, balance in the a/c Bond Discount a/c Credit balance in the Bond payable Carrying value of Bond F-E Credit cash Debit Interest Expense Bond Discount Jan,30 2016 1487667 10000000 8512333 June 30,2014 200000 255370 55370 1432297 10000000 8567703 Dec,31 2014 200000 257031 57031 1375266 10000000 11375266 June 30,2015 200000 341258 141258 1234008 10000000 11234008 Dec,31 2015 200000 337020 137020 1096987 10000000 11096987 June 30,2016 200000 332910 132910 964078 10000000 10964078 Dec,31 2016 200000 328922 128922 835155 10000000 10835155 June 30,2017 200000 325055 -1407 836563 10000000 10836563 Dec,31 2017 200000 325097 125097 711466 10000000 10711466 June 30,2018 200000 321344 121344 590122 10000000 10590122 Dec,31 2018 200000 317704 117704 472418 10000000 10472418 June 30,2019 200000 314173 114173 358246 10000000 10358246 Dec,31 2019 200000 310747 110747 247498 10000000 10247498 June 30,2020 200000 307425 107425 140073 10000000 10140073 Dec,31 2020 200000 304202 104202 35871 10000000 10035871 June 30,2021 200000 301076 101076 -65205 10000000 9934795 Dec,31 2021 200000 -65205 10000000 9934795 June 30,2022 200000 -65205 10000000 9934795 Dec,31 2022 200000 -65205 10000000 9934795 June 30,2023 200000 -65205 10000000 9934795 Dec,31 2023 200000 -65205 10000000 9934795 June 30,2017 200000 -65205 10000000 9934795 Total 1400000 2177566 651104 7774353 Price of the bond = c × F × (1 (1 + r)-t)/r+F(1 + r)t C=Interest Rate F= Face Value r=Market Interest Rate Price of Bond 200000*(1-(1.03)^-20)/.03)+10000000/(1.03)^20 (200000*(1-.5537)/.03)+10000000*.5537 8512333.333 Face vale 10000000 Discount 1487667
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.