Question

Problem 5

Use the Excel template to build a 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.

Bond Valution and Accounting 4 Bond Issue Face Value 5 Stated interest Rate Es Maturity in years 7 Payments/Year Market Interest Rate 10 Interest received every 6 month 11 12 Fair value of the Hond at Issuance 13 less Bond Issue Face Value 14 Premium/ Siscount) Issuance date Interest Payment 1 Interest Payment 2 interest Payment 3 Interest Payment 4 Interest Payment Interest Payment Interest Payment 7 interest Payment Interest Payment interest Payment 10 17 Amortization Table 18 Time: 19 Investment Bands 20 Premiur balance 21 Discount balance 22 Cash 23 24 Journal Entries 25 At the issue date of the bond 26 Investment in bonds 27 Premium on bond investment 20 Discount on bond investment 29 Cash 20 31 At every interest payment date: 32 Cash 33 Discount on bond investment 34 Preinumi un bonul investiment 35 interest Revenue 36 38 At the maturity date of the bond 39 Cash 40 Investment in Bonds 43

Answer 1

Period	Cashflows	Present value	Calculation
1	$30,000	$29,268.29	(30000/(1+0.025)^1)
2	$30,000	$28,554.43	(30000/(1+0.025)^2)
3	$30,000	$27,857.98	(30000/(1+0.025)^3)
4	$30,000	$27,178.52	(30000/(1+0.025)^4)
5	$30,000	$26,515.63	(30000/(1+0.025)^5)
6	$30,000	$25,868.91	(30000/(1+0.025)^6)
7	$30,000	$25,237.96	(30000/(1+0.025)^7)
8	$30,000	$24,622.40	(30000/(1+0.025)^8)
9	$30,000	$24,021.85	(30000/(1+0.025)^9)
10	$1,030,000	$804,634.35	(1030000/(1+0.025)^10)
		$1,043,760.32

1

Purchase Price of bonds

$1,043,760

Amortization Schedule
Period	Cash paid	Interest	Premium on Investment	Calculation	Closing bal.
0	0	$0			$    1,043,760
1	$30,000	$26,094	$          (3,905.99)	(26094.01-30000)	$    1,039,854
2	$30,000	$25,996	$          (4,003.64)	(25996.36-30000)	$    1,035,851
3	$30,000	$25,896	$          (4,103.73)	(25896.27-30000)	$    1,031,747
4	$30,000	$25,794	$          (4,206.33)	(25793.67-30000)	$    1,027,541
5	$30,000	$25,689	$          (4,311.49)	(25688.51-30000)	$    1,023,229
6	$30,000	$25,581	$          (4,419.22)	(25580.78-30000)	$    1,018,810
7	$30,000	$25,470	$          (4,529.70)	(25470.3-30000)	$    1,014,280
8	$30,000	$25,357	$          (4,642.95)	(25357.05-30000)	$    1,009,637
9	$30,000	$25,241	$          (4,759.02)	(25240.98-30000)	$    1,004,878
10	$1,030,000	$25,122	$ (1,004,877.99)	(25122.01-1030000)	$                     0

	Account titles and explanation	Debit	Credit
Purchase	Investment in Bond	$1,000,000
	Premium on bonds	$43,760
	Cash		$1,043,760
	(purchase of bond recorded)
1st payment	Cash	$30,000
	Premium on bonds		$3,906
	Interest income		$26,094
	(1st interest payment)
2nd payment	Cash	$30,000
	Premium on bonds		$4,004
	Interest income		$25,996
	(2nd interest payment)
Maturity	Cash	$1,000,000
	Investment in Bond		$ 1,000,000
	(To record sale of investment)