Question

Suppose you are buying an $80,000 car , you decide to buy it and get it financed and make monthly payments.Your budget is $3,000 for monthly payments, and you can get financing at 11% APR.

With the help of an amortization table, show approximately how long will it take you to pay the loan back? Remember the monthly payment must be around your budget. Work with Excel. Copy and paste the first four months and last two months of the table to the text field cleanly. Create borders on the table in Excel for better visibility.

Answer 1

Amortization table using excel formula

	Beginning Balance= 80000	PMT=3000	Interest part of PMT=11%/12*Beginning Principal	Principal part of PMT= PMT-Interest	Ending Balance= Beginning balance-Principal part of PMT
1.00	80000.00	3000.00	733.33	2266.67	77733.33
2.00	77733.33	3000.00	712.56	2287.44	75445.89
3.00	75445.89	3000.00	691.59	2308.41	73137.48
4.00	73137.48	3000.00	670.43	2329.57	70807.90
5.00	70807.90	3000.00	649.07	2350.93	68456.98
6.00	68456.98	3000.00	627.52	2372.48	66084.50
7.00	66084.50	3000.00	605.77	2394.23	63690.27
8.00	63690.27	3000.00	583.83	2416.17	61274.10
9.00	61274.10	3000.00	561.68	2438.32	58835.78
10.00	58835.78	3000.00	539.33	2460.67	56375.11
11.00	56375.11	3000.00	516.77	2483.23	53891.88
12.00	53891.88	3000.00	494.01	2505.99	51385.89
13.00	51385.89	3000.00	471.04	2528.96	48856.93
14.00	48856.93	3000.00	447.86	2552.14	46304.78
15.00	46304.78	3000.00	424.46	2575.54	43729.24
16.00	43729.24	3000.00	400.85	2599.15	41130.09
17.00	41130.09	3000.00	377.03	2622.97	38507.12
18.00	38507.12	3000.00	352.98	2647.02	35860.10
19.00	35860.10	3000.00	328.72	2671.28	33188.82
20.00	33188.82	3000.00	304.23	2695.77	30493.05
21.00	30493.05	3000.00	279.52	2720.48	27772.57
22.00	27772.57	3000.00	254.58	2745.42	25027.15
23.00	25027.15	3000.00	229.42	2770.58	22256.57
24.00	22256.57	3000.00	204.02	2795.98	19460.58
25.00	19460.58	3000.00	178.39	2821.61	16638.97
26.00	16638.97	3000.00	152.52	2847.48	13791.50
27.00	13791.50	3000.00	126.42	2873.58	10917.92
28.00	10917.92	3000.00	100.08	2899.92	8018.00
29.00	8018.00	3000.00	73.50	2926.50	5091.50
30.00	5091.50	3000.00	46.67	2953.33	2138.17
31.00	2138.17	3000.00	19.60	2980.40	-842.23

Number of months for loan =31