Please use the data to answer the following question: Compute the total loan amo
ID: 2809083 • Letter: P
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.00Explanation / Answer
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