Question

Simulation 5

Using 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 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. Make sure you use as many formula as possible in the Excel spreadsheet so that your spread sheet can accommodate automatically to any inputs you enter.

Answer 1

Table values are based on:
n=	10
i=	2.5%
Cash Flow	Table Value	Amount	Present Value
Interest	8.75206	$30,000	$2,62,562
Principal	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 I	Col II	Col III	Col IV	Col V
Date	Interest Payment($1,000,000*3%)	Interest expenses(Col IV*2.5%)	Premium amorrtization(Col I -Col II)	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 and explanation	Debit(in $)	Credit(in $)
	Issue date	Cash	$10,43,762
		Bonds Payable		$10,00,000
		Premium on 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 Amortization Table