Question

Please use the data to answer the following question:

Compute the total loan amount, monthly payment, and total interest payment for different down payment amount. Plot payment and total interest, and discuss how they change with respect to down payment amount.

Auto Loan Payment Calculator
	Inputs
		Auto Loan Amount	$20,000
		Annual Interest Rate	8.50%
		Term of Loan in Years	3
		First Payment Date	2/1/18
		Last Payment Date	1/1/21
		Frequency of Payment	Monthly
	Summary
		Number of Payments	36
		Rate (per period)	0.708%
		Payment (per period)	$631.35
		Total Interest	$2,728.63
		Total Payments	$22,728.63
No.	Due Date	Payment Due	Additional Payment	Interest	Principal	Balance
						$20,000.00
1	2/1/18	631.35	0.00	141.67	489.68	19,510.32
2	3/1/18	631.35	0.00	138.20	493.15	19,017.17
3	4/1/18	631.35	0.00	134.70	496.65	18,520.52
4	5/1/18	631.35	0.00	131.19	500.16	18,020.36
5	6/1/18	631.35	0.00	127.64	503.71	17,516.65
6	7/1/18	631.35	0.00	124.08	507.27	17,009.38
7	8/1/18	631.35	0.00	120.48	510.87	16,498.50
8	9/1/18	631.35	0.00	116.86	514.49	15,984.01
9	10/1/18	631.35	0.00	113.22	518.13	15,465.88
10	11/1/18	631.35	0.00	109.55	521.80	14,944.08
11	12/1/18	631.35	0.00	105.85	525.50	14,418.58
12	1/1/19	631.35	0.00	102.13	529.22	13,889.36
13	2/1/19	631.35	0.00	98.38	532.97	13,356.39
14	3/1/19	631.35	0.00	94.61	536.74	12,819.65
15	4/1/19	631.35	0.00	90.81	540.54	12,279.11
16	5/1/19	631.35	0.00	86.98	544.37	11,734.74
17	6/1/19	631.35	0.00	83.12	548.23	11,186.51
18	7/1/19	631.35	0.00	79.24	552.11	10,634.40
19	8/1/19	631.35	0.00	75.33	556.02	10,078.38
20	9/1/19	631.35	0.00	71.39	559.96	9,518.42
21	10/1/19	631.35	0.00	67.42	563.93	8,954.48
22	11/1/19	631.35	0.00	63.43	567.92	8,386.56
23	12/1/19	631.35	0.00	59.40	571.95	7,814.61
24	1/1/20	631.35	0.00	55.35	576.00	7,238.61
25	2/1/20	631.35	0.00	51.27	580.08	6,658.53
26	3/1/20	631.35	0.00	47.16	584.19	6,074.34
27	4/1/20	631.35	0.00	43.03	588.32	5,486.02
28	5/1/20	631.35	0.00	38.86	592.49	4,893.53
29	6/1/20	631.35	0.00	34.66	596.69	4,296.84
30	7/1/20	631.35	0.00	30.44	600.91	3,695.93
31	8/1/20	631.35	0.00	26.18	605.17	3,090.76
32	9/1/20	631.35	0.00	21.89	609.46	2,481.30
33	10/1/20	631.35	0.00	17.58	613.77	1,867.53
34	11/1/20	631.35	0.00	13.23	618.12	1,249.40
35	12/1/20	631.35	0.00	8.85	622.50	626.90
36	1/1/21	631.34	0.00	4.44	626.90	0.00

Answer 1

					Monthly
		Auto Loan Amount		$20,000
		Annual Interest Rate		8.50%	0.71%
		Term of loan		3	36
		Frequency of payment
		If the down payment made is $ 5000, than the monthly payment would be
		$20000-$5000		$15,000
		Using the PMT function in excel we get, monthly payments as
		($473.49)	PMT(0.708%,36,15000)
		Monthly Payments - $ 473.49
		Total Payment - $473.49*36				17045.64
		Less : loan amount				$15,000
		Interest on loan				$2,046
		If the down payment made is $ 7000, than the monthly payment would be
		$20000-$7000		$13,000
		Using the PMT function in excel we get, monthly payments as
		($410.35)	PMT(0.708%,36,13000)
		Monthly Payments - $ 410.35
		Total Payment - $ 410.35*36				14772.6
		Less : loan amount				$13,000
		Interest on loan				$1,773
		If the down payment made is $ 10000, than the monthly payment would be
		$20000-$10000		$10,000
		Using the PMT function in excel we get, monthly payments as
		($315.66)	PMT(0.708%,36,10000)
		Monthly Payments - $ 315.66
		Total Payment - $ 315.66*36				11363.76
		Less : loan amount				$10,000
		Interest on loan				$1,364
		If the down payment made is $ 3000, than the monthly payment would be
		$20000-$3000		$17,000
		Using the PMT function in excel we get, monthly payments as
		($536.62)	PMT(0.708%,36,17000)
		Monthly Payments - $ 532.62
		Total Payment - $ 532.62*36				19174.32
		Less : loan amount				$17,000
		Interest on loan				$2,174
		Summary of the above calculations
		Down payment	Loan Amount	Monthly Payment	Total Payment	Interest
		$0	$20,000	$631.35	$22,728.63	$2,728.63
		$3,000	$17,000	$536.62	$19,174.32	$2,174
		$5,000	$15,000	$473.49	$17,045.64	$2,045.64
		$7,000	$13,000	$410.35	$14,772.60	$1,772.60
		$10,000	$10,000	$315.66	$11,363.76	$1,363.76
		With the increase in downpayment, the interest expense and the total amount reduces
		Down payment	Total Payment	Interest
		0	22728.63	2728.63
		3000	19174.32	2174
		5000	17045.64	2045.64
		7000	14772.6	1772.6
		10000	11363.76	1363.76