Question

1. Cooper corporation has borrowed $120,000 from the bank at 8% annual interest rate, compounded monthly. The company plans to pay $2000 per month for the first 12 months, and then pay $2500 per month for the next 12 months. Find the remaining balance of the loan after 24 months. (Answer: $82655.21)

Please answer in excel format

Answer 1

- Preparing the amortization schedule of borrowed loan payment:-

Year	Beg bal.	Payment	Interest amount	Principal Amount	End Bal.
1	120,000.00	2,000.00	800.00	1,200.00	118,800.00
2	118,800.00	2,000.00	792.00	1,208.00	117,592.00
3	117,592.00	2,000.00	783.95	1,216.05	116,375.95
4	116,375.95	2,000.00	775.84	1,224.16	115,151.79
5	115,151.79	2,000.00	767.68	1,232.32	113,919.46
6	113,919.46	2,000.00	759.46	1,240.54	112,678.93
7	112,678.93	2,000.00	751.19	1,248.81	111,430.12
8	111,430.12	2,000.00	742.87	1,257.13	110,172.99
9	110,172.99	2,000.00	734.49	1,265.51	108,907.47
10	108,907.47	2,000.00	726.05	1,273.95	107,633.52
11	107,633.52	2,000.00	717.56	1,282.44	106,351.08
12	106,351.08	2,000.00	709.01	1,290.99	105,060.09
13	105,060.09	2,500.00	700.40	1,799.60	103,260.49
14	103,260.49	2,500.00	688.40	1,811.60	101,448.89
15	101,448.89	2,500.00	676.33	1,823.67	99,625.22
16	99,625.22	2,500.00	664.17	1,835.83	97,789.39
17	97,789.39	2,500.00	651.93	1,848.07	95,941.32
18	95,941.32	2,500.00	639.61	1,860.39	94,080.92
19	94,080.92	2,500.00	627.21	1,872.79	92,208.13
20	92,208.13	2,500.00	614.72	1,885.28	90,322.85
21	90,322.85	2,500.00	602.15	1,897.85	88,425.00
22	88,425.00	2,500.00	589.50	1,910.50	86,514.50
23	86,514.50	2,500.00	576.76	1,923.24	84,591.27
24	84,591.27	2,500.00	563.94	1,936.06	82,655.21

The following Columns are calculated based on:

- Interest amount = Beg. Balance*Monthly interest rate

- Principal amount = Payment - Interest amount

- End Bal. = Beg. Bal + Interest - Payment

So, the remaining balance of the loan after 24 months is $82,655.21

If you need any clarification, you can ask in comments.     

If you like my answer, then please up-vote as it will be motivating