Build an Excel spreadsheet for a purchase of $1,000,000 face value , 6 % 5 -year
ID: 2593570 • Letter: B
Question
Build an Excel spreadsheet for a purchase of $1,000,000face value, 6% 5-year bond with interest payments every 6 months. Market interest rate is 5%. Include the following items:
Inputs:
Bond initial purchase amount
Stated Interest Rate
Maturity in Years
Number of payments/year
Market interest rate
Calculations section 1:
Fair value with separate calculations for interest and principal
Discount or premium
Record the journal entry required when the bonds arepurchased.
Calculations Section 2:
Amortization schedule for each interest received (investment revenue). Use the general ledger accounts of cash, discount or premium, bonds payable and interest expense. (Similar to illustration 12-2)
Set up the spreadsheet consistent with journal entries necessary to record each interest received and related amortization. Also show the remaining principal and discount/premium at each interest receipt. NOTE: At the end of the bond investment term, the discount/premium account should be zero.
Explanation / Answer
1 6% 5 YEAR BOND OF 1,000,000 No of N=Bonds 10000 One Bond (assumed) 100 Semi Annual Interest Rate 3% 3 Market rate semi annual 2.5% n period 10 getting interest 3 each six month Value of a bond=3*(PVIF,2.5%,10)+100*(PVIF,2.5%,10) Value of Bond=3*8.7521+100*.7812 104.38 Total Value of bonds Issue Price 104.38 no of Bonds 10,000.00 Total Issue Value 10,43,760 Preimum on Face value of the Bonds =1,043,760-1,000,000=43760 Amortisation at each period of six month=43760/10=4,376 4376 Bonds Initial Purchase Amount 10,43,760 Stated Interest Rate 6% Maturity in year 10 No of Payment per year 2 Market Interest Rate 5% Journal Entry Date Account Title Debit Credit 1-Jan-17 6% Bonds 10,43,760 Cash 10,43,760 Date Account Title Debit Credit 1-Jul-17 Cash 30,000 6% Bond 4,376 Interest 25,624 Date Account Title Debit Credit 1-Jan-18 Cash 30,000 6% Bond 4,376 Interest 25,624 Date Account Title Debit Credit 1-Jul-18 Cash 30,000 6% Bond 4,376 Interest 25,624 Date Account Title Debit Credit 1-Jan-19 Cash 30,000 6% Bond 4,376 Interest 25,624 Date Account Title Debit Credit 1-Jul-19 Cash 30,000 6% Bond 4,376 Interest 25,624 Date Account Title Debit Credit 1-Jan-20 Cash 30,000 6% Bond 4,376 Interest 25,624 Date Account Title Debit Credit 1-Jul-20 Cash 30,000 6% Bond 4,376 Interest 25,624 Date Account Title Debit Credit 1-Jan-21 Cash 30,000 6% Bond 4,376 Interest 25,624 Date Account Title Debit Credit 1-Jul-21 Cash 30,000 6% Bond 4,376 Interest 25,624 Date Account Title Debit Credit 1-Jan-22 Cash 30,000 6% Bond 4,376 Interest 25,624 Date Account Title Debit Credit 1-Jan-22 Cash 10,00,000 6% Bond 10,00,000 2 Amortisation Table Issue Price Amortisation Carrying Value Interest Total Interest Income 0 01/01/2017 10,43,760 1 01/07/2017 4,376 10,39,384 30,000 25,624 2 01/01/2018 4,376 10,35,008 30,000 25,624 3 01/07/2018 4,376 10,30,632 30,000 25,624 4 01/01/2019 4,376 10,26,256 30,000 25,624 5 01/07/2019 4,376 10,21,880 30,000 25,624 6 01/01/2020 4,376 10,17,504 30,000 25,624 7 01/07/2020 4,376 10,13,128 30,000 25,624 8 01/01/2021 4,376 10,08,752 30,000 25,624 9 01/07/2021 4,376 10,04,376 30,000 25,624 10 01/01/2022 4,376 10,00,000 30,000 25,624
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.