Question

You would like to purchase a car that costs $32,000. You have decided to finance the car with a four-year car loan. If the APR (annual percentage rate) is 5 percent, compute your monthly payment.

Construct a loan amortization table in Excel for the car loan.   You should do the problem in Excel using monthly payments and should submit the spreadsheet.

Answer 1

Monthly payment	=	[P * R * (1+R)^N ] / [(1+R)^N -1]
Using the formula:
Loan amount	P	32,000.00
Rate of interest per period:
Annual rate of interest		5.000%
Frequency of payment	=	Once in 1 month period
Numer of payments in a year	=	12/1 =	12
Rate of interest per period	R	0.05 /12 =	0.4167%
Total number of payments:
Frequency of payment	=	Once in 1 month period
Number of years of loan repayment	=	4
Total number of payments	N	4*12 =	48
Period payment using the formula	=	[ 32000*0.00417*(1+0.00417)^48] / [(1+0.00417 ^48 -1]
Monthly payment	=	736.94

Period	Opening liability	Total payment	Interest payment	Loan repaid	Closing liability
N	A	C	B= A* 0.004167	D=C-B	E=A-D
1	32,000.00	736.94	133.33	603.60	31,396.40
2	31,396.40	736.94	130.82	606.12	30,790.28
3	30,790.28	736.94	128.29	608.64	30,181.63
4	30,181.63	736.94	125.76	611.18	29,570.45
5	29,570.45	736.94	123.21	613.73	28,956.72
6	28,956.72	736.94	120.65	616.28	28,340.44
7	28,340.44	736.94	118.09	618.85	27,721.59
8	27,721.59	736.94	115.51	621.43	27,100.16
9	27,100.16	736.94	112.92	624.02	26,476.14
10	26,476.14	736.94	110.32	626.62	25,849.52
11	25,849.52	736.94	107.71	629.23	25,220.29
12	25,220.29	736.94	105.08	631.85	24,588.43
13	24,588.43	736.94	102.45	634.49	23,953.95
14	23,953.95	736.94	99.81	637.13	23,316.82
15	23,316.82	736.94	97.15	639.78	22,677.03
16	22,677.03	736.94	94.49	642.45	22,034.58
17	22,034.58	736.94	91.81	645.13	21,389.46
18	21,389.46	736.94	89.12	647.81	20,741.64
19	20,741.64	736.94	86.42	650.51	20,091.13
20	20,091.13	736.94	83.71	653.22	19,437.90
21	19,437.90	736.94	80.99	655.95	18,781.96
22	18,781.96	736.94	78.26	658.68	18,123.28
23	18,123.28	736.94	75.51	661.42	17,461.86
24	17,461.86	736.94	72.76	664.18	16,797.68
25	16,797.68	736.94	69.99	666.95	16,130.73
26	16,130.73	736.94	67.21	669.73	15,461.00
27	15,461.00	736.94	64.42	672.52	14,788.49
28	14,788.49	736.94	61.62	675.32	14,113.17
29	14,113.17	736.94	58.80	678.13	13,435.04
30	13,435.04	736.94	55.98	680.96	12,754.08
31	12,754.08	736.94	53.14	683.80	12,070.28
32	12,070.28	736.94	50.29	686.64	11,383.64
33	11,383.64	736.94	47.43	689.51	10,694.13
34	10,694.13	736.94	44.56	692.38	10,001.75
35	10,001.75	736.94	41.67	695.26	9,306.49
36	9,306.49	736.94	38.78	698.16	8,608.33
37	8,608.33	736.94	35.87	701.07	7,907.26
38	7,907.26	736.94	32.95	703.99	7,203.27
39	7,203.27	736.94	30.01	706.92	6,496.35
40	6,496.35	736.94	27.07	709.87	5,786.48
41	5,786.48	736.94	24.11	712.83	5,073.65
42	5,073.65	736.94	21.14	715.80	4,357.85
43	4,357.85	736.94	18.16	718.78	3,639.07
44	3,639.07	736.94	15.16	721.77	2,917.30
45	2,917.30	736.94	12.16	724.78	2,192.52
46	2,192.52	736.94	9.14	727.80	1,464.71
47	1,464.71	736.94	6.10	730.83	733.88
48	733.88	736.94	3.06	733.88	0.00