Question

You borrow $29,000 to buy a car. You plan to payoff the car within 6 years and the APR on the car loan is 2.99%. Using an excel to create an amortization for this car payment for 72 months. Please print out your excel and turn it in for 6 points extra credit on the test. You need to turn in your own work.

Month	Beginning Balance	Monthly Payment	Interest Paid	Principal Paid	Ending Balance
1 2 3 ... 72
Total

Answer 1

To create, amortization table we need to calculate monthly payment

Monthly payment = (Principal * Rate * (1 + Rate)^N) / ((1 + Rate)^N - 1)

here Rate = 2.99% /12 = 0.002492, N = 6 *12= 72

Monthly payment = (29000 * 0.002492 * (1 + 0.002492)^72) / ((1 + 0.002492)^72 -1)

= 440.49

Month	Beginning balance	Monthly payment	Interest paid	Principal Paid	Ending balance
			Beginning balance * Rate	Monthly payment - Interest	Beginning balance - Principal
1	29,000.00	440.49	72.26	368.23	28,631.77
2	28,631.77	440.49	71.34	369.15	28,262.62
3	28,262.62	440.49	70.42	370.07	27,892.55
4	27,892.55	440.49	69.50	370.99	27,521.56
5	27,521.56	440.49	68.57	371.92	27,149.64
6	27,149.64	440.49	67.65	372.84	26,776.80
7	26,776.80	440.49	66.72	373.77	26,403.03
8	26,403.03	440.49	65.79	374.70	26,028.33
9	26,028.33	440.49	64.85	375.64	25,652.69
10	25,652.69	440.49	63.92	376.57	25,276.12
11	25,276.12	440.49	62.98	377.51	24,898.61
12	24,898.61	440.49	62.04	378.45	24,520.16
13	24,520.16	440.49	61.10	379.39	24,140.76
14	24,140.76	440.49	60.15	380.34	23,760.43
15	23,760.43	440.49	59.20	381.29	23,379.14
16	23,379.14	440.49	58.25	382.24	22,996.90
17	22,996.90	440.49	57.30	383.19	22,613.71
18	22,613.71	440.49	56.35	384.14	22,229.57
19	22,229.57	440.49	55.39	385.10	21,844.47
20	21,844.47	440.49	54.43	386.06	21,458.41
21	21,458.41	440.49	53.47	387.02	21,071.38
22	21,071.38	440.49	52.50	387.99	20,683.40
23	20,683.40	440.49	51.54	388.95	20,294.44
24	20,294.44	440.49	50.57	389.92	19,904.52
62	4,773.49	440.49	11.89	428.60	4,344.89
63	4,344.89	440.49	10.83	429.66	3,915.23
64	3,915.23	440.49	9.76	430.73	3,484.49
65	3,484.49	440.49	8.68	431.81	3,052.68
66	3,052.68	440.49	7.61	432.88	2,619.80
67	2,619.80	440.49	6.53	433.96	2,185.84
68	2,185.84	440.49	5.45	435.04	1,750.80
69	1,750.80	440.49	4.36	436.13	1,314.67
70	1,314.67	440.49	3.28	437.21	877.45
71	877.45	440.49	2.19	438.30	439.15
72	439.15	440.49	1.09	439.40	-
Total		31,715.28	2,715.03	29,000.25