For this quiz, create an amortization table spreadsheet for a loan with initial balance of $50,000...

For this quiz, create an amortization table spreadsheet for a loan with initial balance of $50,000 at 6.43% with a monthly payment of $1200. Answer the following questions based on that spreadsheet.
What will the loan balance be after the first payment is made?
After which monthly payment does the loan become fully paid off?
Using the $1200 regular monthly payment in that last month will overpay the loan. How much should that last monthly payment be reduced to so that the final loan balance is exactly $0.00

What is the total amount of interest that will be paid on this loan?

Expert Solution

What will the loan balance be after the first payment is made?
49067.91667

After which monthly payment does the loan become fully paid off?
48

Using the $1200 regular monthly payment in that last month will overpay the loan. How much should that last monthly payment be reduced to so that the final loan balance is exactly $0.00
Reduced by 865.839 to 334.1607

What is the total amount of interest that will be paid on this loan?
6734.160699

Month	Beginning Balance	Interest Paid	Principal Paid	Total Payment	Ending Balance
1	50000	267.9166667	932.0833333	1200	49067.91667
2	49067.91667	262.9222535	937.0777465	1200	48130.83892
3	48130.83892	257.9010785	942.0989215	1200	47188.74
4	47188.74	252.8529985	947.1470015	1200	46241.593
5	46241.593	247.7778691	952.2221309	1200	45289.37087
6	45289.37087	242.6755456	957.3244544	1200	44332.04641
7	44332.04641	237.545882	962.454118	1200	43369.59229
8	43369.59229	232.388732	967.611268	1200	42401.98103
9	42401.98103	227.2039483	972.7960517	1200	41429.18497
10	41429.18497	221.9913828	978.0086172	1200	40451.17636
11	40451.17636	216.7508866	983.2491134	1200	39467.92724
12	39467.92724	211.4823101	988.5176899	1200	38479.40955
13	38479.40955	206.1855029	993.8144971	1200	37485.59506
14	37485.59506	200.8603135	999.1396865	1200	36486.45537
15	36486.45537	195.50659	1004.49341	1200	35481.96196
16	35481.96196	190.1241795	1009.87582	1200	34472.08614
17	34472.08614	184.7129282	1015.287072	1200	33456.79907
18	33456.79907	179.2726817	1020.727318	1200	32436.07175
19	32436.07175	173.8032845	1026.196716	1200	31409.87503
20	31409.87503	168.3045804	1031.69542	1200	30378.17961
21	30378.17961	162.7764124	1037.223588	1200	29340.95603
22	29340.95603	157.2186227	1042.781377	1200	28298.17465
23	28298.17465	151.6310525	1048.368948	1200	27249.8057
24	27249.8057	146.0135422	1053.986458	1200	26195.81924
25	26195.81924	140.3659315	1059.634069	1200	25136.18518
26	25136.18518	134.6880589	1065.311941	1200	24070.87323
27	24070.87323	128.9797624	1071.020238	1200	22999.853
28	22999.853	123.240879	1076.759121	1200	21923.09388
29	21923.09388	117.4712447	1082.528755	1200	20840.56512
30	20840.56512	111.6706948	1088.329305	1200	19752.23582
31	19752.23582	105.8390636	1094.160936	1200	18658.07488
32	18658.07488	99.97618456	1100.023815	1200	17558.05106
33	17558.05106	94.08189028	1105.91811	1200	16452.13295
34	16452.13295	88.15601241	1111.843988	1200	15340.28897
35	15340.28897	82.19838171	1117.801618	1200	14222.48735
36	14222.48735	76.20882804	1123.791172	1200	13098.69618
37	13098.69618	70.18718034	1129.81282	1200	11968.88336
38	11968.88336	64.13326665	1135.866733	1200	10833.01662
39	10833.01662	58.04691407	1141.953086	1200	9691.063537
40	9691.063537	51.92794879	1148.072051	1200	8542.991486
41	8542.991486	45.77619605	1154.223804	1200	7388.767682
42	7388.767682	39.59148016	1160.40852	1200	6228.359162
43	6228.359162	33.37362451	1166.626375	1200	5061.732787
44	5061.732787	27.12245152	1172.877548	1200	3888.855238
45	3888.855238	20.83778265	1179.162217	1200	2709.693021
46	2709.693021	14.51943844	1185.480562	1200	1524.212459
47	1524.212459	8.167238428	1191.832762	1200	332.3796978
48	332.3796978	1.781001214	332.3796978	334.160699	0

jeff jeffy answered 1 year ago

Create a loan amortization table for a $100,000 2 year loan at 4.875% annual interest payable...

Create a loan amortization table for a $100,000 2 year loan at 4.875% annual interest payable semi-annually: a) Calculate the payment amount. b) Do the loan amortization table. c) What is the journal entry to receive the third payment?

Create a loan amortization schedule in excell for a $27,000 car loan that will be repaid...

Create a loan amortization schedule in excell for a $27,000 car loan that will be repaid over 48 months at an annual interest rate of 6%. What is your monthly payment? _$_ What is the total dollar amount of payments made over the life of this loan? $____ What is the total dollar amount of interest paid over the life of this loan? $___________ How many months will it take to pay off the loan if you pay an extra...

We suggest the use of a spreadsheet to create the amortization tables. You take out a...

We suggest the use of a spreadsheet to create the amortization tables. You take out a 30-year mortgage for $70,000 at 9.65%, to be paid off monthly. Construct an amortization table showing how much you will pay in interest each year for the first 15 years and how much goes toward paying off the principal. If you sell your house after 15 years, how much will you still owe on the mortgage according to the amortization table? HINT [See Example...

For this lab, we will create a spreadsheet that allows somebody to type in a loan...

For this lab, we will create a spreadsheet that allows somebody to type in a loan amount, interest rate, and length of the loan in years. The spreadsheet will then calculate the monthly payment required and the actual amount paid on the loan. First, setup your spreadsheet: In Cell A1, put the label Loan Amount:. The corresponding value would be input in Cell B1. In Cell A2, put the label Interest Rate:. The corresponding value would be input in Cell...

Finance- Amortization Schedule Create the amortization schedule for a loan of $5,000, paid monthly over two...

Finance- Amortization Schedule Create the amortization schedule for a loan of $5,000, paid monthly over two years using an 8 percent APR.

Create an amortization schedule in Excel for the following loan: (Hint: Show Calculations) The loan term...

Create an amortization schedule in Excel for the following loan: (Hint: Show Calculations) The loan term is 15 years, the payments are made monthly, the loan amount is $300,000 and the interest rate is 4.00% APR. Also include the "totals" over the loan term for the "interest" payment, the "principal" payment, as well as "total" payments.

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...

Problem 5-50 Amortization Schedule (LG9) Create the amortization schedule for a loan of $4,300, paid monthly...

Problem 5-50 Amortization Schedule (LG9) Create the amortization schedule for a loan of $4,300, paid monthly over two years using an 9 percent APR. (Round your answers to 2 decimal places.) Month Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance 1 2 3 4 5 6 7 ...

Problem 5-49 Amortization Schedule (LG9) Create the amortization schedule for a loan of $14,000, paid monthly...

Problem 5-49 Amortization Schedule (LG9) Create the amortization schedule for a loan of $14,000, paid monthly over three years using a 9 percent APR. (Round your answers to 2 decimal places.) Month Beginning Balance Total Payment Interest Paid Principal Paid Ending Balance 1 2 3 4 5 6 7...

Create a loan amortization schedule in Excell for a $275,000 mortgage that will be repaid over...

Create a loan amortization schedule in Excell for a $275,000 mortgage that will be repaid over 20 years with monthlypayments. The annual interest rate is 5.5 %. What is your monthly payment? $ What is the total dollar amount of payments made over the life of this loan? $__ What is the total dollar amount of interest paid over the life of this loan? $_ How many months will it take to pay off the loan if you pay an extra $100...

Question