Use Excel to prepare a Bond Interest and Discount Amortization Table using the following information: $50,000,000...

Use Excel to prepare a Bond Interest and Discount Amortization Table using the following information:

$50,000,000 face value

coupon rate of interest - 6%

market rate of interest - 7%

term - 10 years payable semi-annually

First calculate the proceeds received upon issuance and the amount of the discount

Expert Solution

Step-1:Calculation of proceeds received from issuance of bond and discount on issuance of bond.

Proceeds from issuance of bond is the present value of cash flow from bond discounted at market rate.

Face Value			a	$ 5,00,00,000
Semi annual coupon			b=a*3%	$ 15,00,000
Semi annual yield				3.50%
Semi annual period				20

Present Value of annuity of 1			=	(1-(1+i)^-n)/i		Where,
			=	(1-(1+0.035)^-20)/0.035		i	3.50%
			=	14.2124		n	20

Present Value of 1			=	(1+0.035)^-20
			=	0.5026

Period	Cash flow		Discount factor	Present Value
1-20	$ 15,00,000		14.2124	2,13,18,605
20	$ 5,00,00,000		0.5026	2,51,28,294

	Total			4,64,46,899

Now,

Face Value of bonds				$ 5,00,00,000
Less proceeds recived from issuance of bonds				4,64,46,899

Discount on bonds payable				$ 35,53,101

Step-2:Bonds interest and discount amortization table
(effective interest method)
Semi annual period		Coupon interest paid in cash		Interest expense	Discount amorization	Carrying Value
0						4,64,46,899
1		$ 15,00,000		16,25,641	1,25,641	4,65,72,541
2		$ 15,00,000		16,30,039	1,30,039	4,67,02,580
3		$ 15,00,000		16,34,590	1,34,590	4,68,37,170
4		$ 15,00,000		16,39,301	1,39,301	4,69,76,471
5		$ 15,00,000		16,44,176	1,44,176	4,71,20,647
6		$ 15,00,000		16,49,223	1,49,223	4,72,69,870
7		$ 15,00,000		16,54,445	1,54,445	4,74,24,315
8		$ 15,00,000		16,59,851	1,59,851	4,75,84,166
9		$ 15,00,000		16,65,446	1,65,446	4,77,49,612
10		$ 15,00,000		16,71,236	1,71,236	4,79,20,849
11		$ 15,00,000		16,77,230	1,77,230	4,80,98,078
12		$ 15,00,000		16,83,433	1,83,433	4,82,81,511
13		$ 15,00,000		16,89,853	1,89,853	4,84,71,364
14		$ 15,00,000		16,96,498	1,96,498	4,86,67,862
15		$ 15,00,000		17,03,375	2,03,375	4,88,71,237
16		$ 15,00,000		17,10,493	2,10,493	4,90,81,730
17		$ 15,00,000		17,17,861	2,17,861	4,92,99,591
18		$ 15,00,000		17,25,486	2,25,486	4,95,25,076
19		$ 15,00,000		17,33,378	2,33,378	4,97,58,454
20		$ 15,00,000		17,41,546	2,41,546	5,00,00,000


(Straight Line method)
Semi annual period		Coupon interest paid in cash		Interest expense	Discount amorization	Carrying Value
0						4,64,46,899
1		$ 15,00,000		16,77,655	1,77,655	4,66,24,554
2		$ 15,00,000		16,77,655	1,77,655	4,68,02,209
3		$ 15,00,000		16,77,655	1,77,655	4,69,79,864
4		$ 15,00,000		16,77,655	1,77,655	4,71,57,519
5		$ 15,00,000		16,77,655	1,77,655	4,73,35,174
6		$ 15,00,000		16,77,655	1,77,655	4,75,12,829
7		$ 15,00,000		16,77,655	1,77,655	4,76,90,484
8		$ 15,00,000		16,77,655	1,77,655	4,78,68,140
9		$ 15,00,000		16,77,655	1,77,655	4,80,45,795
10		$ 15,00,000		16,77,655	1,77,655	4,82,23,450
11		$ 15,00,000		16,77,655	1,77,655	4,84,01,105
12		$ 15,00,000		16,77,655	1,77,655	4,85,78,760
13		$ 15,00,000		16,77,655	1,77,655	4,87,56,415
14		$ 15,00,000		16,77,655	1,77,655	4,89,34,070
15		$ 15,00,000		16,77,655	1,77,655	4,91,11,725
16		$ 15,00,000		16,77,655	1,77,655	4,92,89,380
17		$ 15,00,000		16,77,655	1,77,655	4,94,67,035
18		$ 15,00,000		16,77,655	1,77,655	4,96,44,690
19		$ 15,00,000		16,77,655	1,77,655	4,98,22,345
20		$ 15,00,000		16,77,655	1,77,655	5,00,00,000

ekkarill92 answered 1 year ago

Bond Amortization and adjusting entry. Use excel to create an amortization table for 10 years and...

Bond Amortization and adjusting entry. Use excel to create an amortization table for 10 years and show the adjusting entry for the bond payable for the June payment. Show calculations. Had issued $1,500,000 of 4%, 10-year bond, dated 1/1/18 for $1,383,079 when the market rate was 5%. Interest is paid on June 30 and January 1 using the effective interest rate method. The June payment is included in the Dec. 1 TB. Trial Balance December 1, 2021 Description Debit Credit...

Prepare an amortization table for the calculation of interest and payment of the Document payable to...

Prepare an amortization table for the calculation of interest and payment of the Document payable to a natural person, for $ 2,000,000 at the annual rate of 12%, payable in 3 years. The interest plus the payment of the document will be made at the end of the 3 years. Calculate with simple interest and also calculate cost with compound interest

A. Determine the issue price of the debt. B. Prepare the amortization table for the bond...

A. Determine the issue price of the debt. B. Prepare the amortization table for the bond issue through January 1, 2021, assuming that Jones Road uses the effective interest rate method of amortization. C. Prepare the journal entries to record the bond issue, the first interest entry, and payment of the bonds at maturity. Assume the company uses a premium or discount account if needed. On January 1, 2018, the Jones Road Corporation issued $800,000 par value, 3%, 5-year bonds....

Question: In regard to a bond discount or premium, what is the effective-interest amortization method?

Prepare an amortization schedule for the 1st and 2nd years (effective interest method) using the following...

Prepare an amortization schedule for the 1st and 2nd years (effective interest method) using the following data: 1. On January 1, 2010 XYZ Co. issued $3,000,000, 6%, 10 year bonds, interest payable on June 30th and December 31st to yield 5%. Use the following format and round to the nearest dollar (may have small rounding error). The bonds were issued for $3,233,834. Date Cash paid Interest expense Amortization Bond Carry Value 2. Show how this bond would be reported on...

Exercise 1 Construct an amortization table in Excel to answer the following questions. You must use...

Exercise 1 Construct an amortization table in Excel to answer the following questions. You must use the corresponding Excel financial formulas whenever possible. Upon graduation, Federico Hernández, borrows $20,000 to finance a late model used car. The loan is made by a family member who was able to obtain an 8 % annual percent rate (APR). The loan is going to be payback in equal monthly payments over 5 years. a) How much are the monthly payments? b) How many...

Please explain, Excel assignment. Prepare an amortization table for a 30-year mortgage where the homeowner is...

Please explain, Excel assignment. Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000 at a 3.75% interest rate. In addition to the monthly table, provide a summary table showing the interest paid, principal paid, and ending balance on a yearly basis. Create three separate graphs illustrating interest paid over time, principal paid over time, and ending balance over time for the 30 annual periods in the summary table. Repeat the analysis, changing the interest rate...

Excel assignment Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000...

Excel assignment Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000 at a 3.75% interest rate. In addition to the monthly table, provide a summary table showing the interest paid, principal paid, and ending balance on a yearly basis. Create three separate graphs illustrating interest paid over time, principal paid over time, and ending balance over time for the 30 annual periods in the summary table. Repeat the analysis, changing the interest rate to 8.75%...

In Excel, create the amortization table for a 15-year mortgage for $780,000 at 6.6% interest. Individual...

In Excel, create the amortization table for a 15-year mortgage for $780,000 at 6.6% interest. Individual taxpayers may be able to deduct the amount of interest that they paid on a mortgage for a personal residence. If the mortgage was issued on July 1, how much interest will be earned in the first year? How much interest will be earned in the second year?

In Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual...

In Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual taxpayers may be able to deduct the amount of interest that they paid on a mortgage for a personal residence. If the mortgage was issued on May 1, how much interest will be earned in the first year? How much interest will be earned in the second year? Upload your Excel file below.

Question