Question

On January 1, 2020, Parker Company sold in exchange for cash, $25 million of bonds payable to investors. The bonds mature on December 31, 2049, have a stated interest rate of 8%, and pay interest annually on December 31 of each year. The market interest rate was 6% on the date that Parker sold the bonds and is expected to fluctuate between 5% and 9% over the 30-year life of the bonds. As required by GAAP, Parker uses the effective interest method of amortizing any bond discount or premium.

Use Excel (and only Excel) to prepare a table for Parker Company that shows the following for each of the 30 years in the life of the bonds: Interest Expense, Cash Paid as Interest, Discount or Premium Amortization, Remaining Unamortized Discount or Premium, and Carrying Amount of the Bonds. (Note: You must use only Excel to prepare and present all parts of this table. Do not use a hand-held calculator, and do not use the compound interest tables to solve any part of this problem.)

I need to have the excel formulas used to calculate the answers to be included so I can know how to properly do it and get full credit.

Answer 1

Table values are based on:
Face Amount	$25,000,000
Interest Payment	$25,000,000*8% =$2,000,000
Market Interest rate per period	6.00%
Cash Flow	Table Value(PV of 6% for 30 period)	Amount	Present Value
PV of Interest	13.76483	$20,00,000	$2,75,29,660
PV of Principal	0.17411	$2,50,00,000	$43,52,750
PV of Bonds Payable(Issue Price)			$3,18,82,410
Premium on Bond =$31,882,410 - $25,000,000 =$6,882,410
Amortization table
	Col I	Col II	Col III	Col IV	Col V
Date	Interest Payment($25,000,000*8%)	Interest expenses(Col V*6%)	Premium amorrtization(Col I -Col II)	Unamortized Premium	Bond carrying amount
01-Jan-20				68,82,410	3,18,82,410
31-Dec-20	20,00,000	19,12,945	87,055	67,95,355	3,17,95,355
31-Dec-21	20,00,000	19,07,721	92,279	67,03,076	3,17,03,076
31-Dec-22	20,00,000	19,02,185	97,815	66,05,260	3,16,05,260
31-Dec-23	20,00,000	18,96,316	1,03,684	65,01,576	3,15,01,576
31-Dec-24	20,00,000	18,90,095	1,09,905	63,91,671	3,13,91,671
31-Dec-25	20,00,000	18,83,500	1,16,500	62,75,171	3,12,75,171
31-Dec-26	20,00,000	18,76,510	1,23,490	61,51,681	3,11,51,681
31-Dec-27	20,00,000	18,69,101	1,30,899	60,20,782	3,10,20,782
31-Dec-28	20,00,000	18,61,247	1,38,753	58,82,029	3,08,82,029
31-Dec-29	20,00,000	18,52,922	1,47,078	57,34,951	3,07,34,951
31-Dec-30	20,00,000	18,44,097	1,55,903	55,79,048	3,05,79,048
31-Dec-31	20,00,000	18,34,743	1,65,257	54,13,791	3,04,13,791
31-Dec-32	20,00,000	18,24,827	1,75,173	52,38,618	3,02,38,618
31-Dec-33	20,00,000	18,14,317	1,85,683	50,52,935	3,00,52,935
31-Dec-34	20,00,000	18,03,176	1,96,824	48,56,111	2,98,56,111
31-Dec-35	20,00,000	17,91,367	2,08,633	46,47,478	2,96,47,478
31-Dec-36	20,00,000	17,78,849	2,21,151	44,26,326	2,94,26,326
31-Dec-37	20,00,000	17,65,580	2,34,420	41,91,906	2,91,91,906
31-Dec-38	20,00,000	17,51,514	2,48,486	39,43,420	2,89,43,420
31-Dec-39	20,00,000	17,36,605	2,63,395	36,80,026	2,86,80,026
31-Dec-40	20,00,000	17,20,802	2,79,198	34,00,827	2,84,00,827
31-Dec-41	20,00,000	17,04,050	2,95,950	31,04,877	2,81,04,877
31-Dec-42	20,00,000	16,86,293	3,13,707	27,91,169	2,77,91,169
31-Dec-43	20,00,000	16,67,470	3,32,530	24,58,640	2,74,58,640
31-Dec-44	20,00,000	16,47,518	3,52,482	21,06,158	2,71,06,158
31-Dec-45	20,00,000	16,26,369	3,73,631	17,32,527	2,67,32,527
31-Dec-46	20,00,000	16,03,952	3,96,048	13,36,479	2,63,36,479
31-Dec-47	20,00,000	15,80,189	4,19,811	9,16,668	2,59,16,668
31-Dec-48	20,00,000	15,55,000	4,45,000	4,71,668	2,54,71,668
31-Dec-49	20,00,000	15,28,332	4,71,668	(0)	2,50,00,000