Question

Simulation 5 Use the attached template to build an Excel spreadsheet for a purchase of $1,000,000 face 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 are purchased. Calculations Section 2: --Amortization schedule for each interest payment 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 payment 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. Make sure you use as many formula as possible in the Excel spreadsheet so that your spread sheet will recalculate automatically when the inputs are changed.

Answer 1

Answer :

n=	10
i =	2.5%
Cash flow	Table value	Amount	Present value
Interest	8.75206	$30,000	$2,62,562
Principle	0.7812	$10,00,000	$7,81,200
Price of bond			$10,43,762
Premium on bonds = $1,043,762 - $1,000,000 = $43,762

Amortization table

	Col 1	Col 2	Col 3	Col 4	Col 5
Date	Interest payment ($1,000,000*3%)	Interest expenses (Col4*2.5%)	Premium amortization (col 1-col 2)	Unamortized premium	Bond carrying amount
Issue Date				43,762	10,43,762
Payment 1	30.000	26,094	3,906	39,856	10,39,856
Payment 2	30,000	25,996	4,004	35,852	10,35,852
Payment 3	30,000	25,896	4,104	31,749	10,31,749
Payment 4	30,000	25,794	4,206	27,542	10,27,542
Payment 5	30,000	25,689	4,311	23,231	10,23,231
Payment 6	30,000	25,581	4,419	18,812	10,18,812
Payment 7	30,000	25,470	4,530	14,282	10,14,282
Payment 8	30,000	25,357	4,643	9,639	10,09,639
Payment 9	30,000	25,241	4,759	4,880	10,04,880
Payment 10	30,000	25,120	4,880	0	10,00,000

Date	Accounts Title and explanation	Debit ($)	Credit ($)
Issue date	Cash	$10,43,762
	Bonds payable		$10,00,000
	Premium bonds payable		$43,762
Payment 1	Interest expenses	26,094
	Premium on bonds payable	3,906
	Cash		30,000
Payment 2	Interest expenses	25,996
	Premium on bonds payable	4,004
	Cash		30,000
Payment 3	Interest expenses	25,896
	Premium on bonds payable	4,104
	Cash		30,000

Similarly the interest entry will be same for all the other payment dates with the corresponding amount shown in the Amortization table