Question

Use the following to answer questions 20 - 24 On January 1, year 1, AJ borrows $41,000 to purchase a new vehicle by agreeing to a 4.0%, 6-year loan with the bank. Payments are due at the end of each month with the first installment (vehicle payment) due on January 31, year 1. After completing the problem, ROUND YOUR ANSWERS TO THE NEAREST DOLLAR. IMPORTANT!!!! when inputting the monthly interest rate DO NOT ROUND IT (use the math function in the spreadsheet/financial calculator). 20. Determine the monthly vehicle payment installment) $ 21. Determine the interest expense for the first car payment $_ 22. How much of the payment will decrease the amount owed (principal)? S. 23. After the first vehicle payment is made the amount owed on the vehicle would be: $_ 24. Determine interest expense for the second car payment $

Answer 1

The Following Spreadsheet will layout the Vehicle Payments In Monthly & Per Month Interest & Principal amounts plus Opening & Closing Principal Balances.

Formulas USED in SPREADSHEET are as Follows

Monthly Installment Amount: =PMT(4%/12, 6*12 (or) (72months), -41000)

Monthly Principal Amount: =-PPMT(4%/12, 1(No of Months), 6*12 (or) (72months), 41000)

Monthly Interest Amount: =-IPMT(4%/12, 1(No of Months), 6*12 (or) (72months), 41000)

Note: In formulas a Negative sign (-) is added to represent it in a positive figure. You may avoid if you want in it in Negative.

Answers for Questions:

20. Monthly Vehicle Payment (installment) = $ 641.45

21. Interest Expense for the first car payment = $ 5,184.58

22. How much of the Payment will decrease the amount owed (principal)?

Total Payment of $ 46,184.40 at the end of 72 months.

23. After the first vehicle payment is made the amount owed on the vehicle would be $ 40,495.21 (Principal closing Bal.)

CALULATION FOR PAYMENT OF LOAN
(Use PMT, PPMT, IPMT to determine Loan Repayment, Principal & Interest Amount respectively)
Loan Amt		$41,000.00	$41,000.00
Interest Rate		4%	4%
Period (term in Months)		6Y*12 = 72 months	72
Compounding Period/Year		12 months	12
Monthly Payment			$641.45
Months	Payments	Principal	Interest	Opening Bal.	Closing Bal.
1	$641.45	$504.79	$136.67	$41,000.00	$40,495.21
2	$641.45	$506.47	$134.98	$40,495.21	$39,988.75
3	$641.45	$508.16	$133.30	$39,988.75	$39,480.59
4	$641.45	$509.85	$131.60	$39,480.59	$38,970.74
5	$641.45	$511.55	$129.90	$38,970.74	$38,459.19
6	$641.45	$513.26	$128.20	$38,459.19	$37,945.93
7	$641.45	$514.97	$126.49	$37,945.93	$37,430.97
8	$641.45	$516.68	$124.77	$37,430.97	$36,914.28
9	$641.45	$518.40	$123.05	$36,914.28	$36,395.88
10	$641.45	$520.13	$121.32	$36,395.88	$35,875.75
11	$641.45	$521.87	$119.59	$35,875.75	$35,353.88
12	$641.45	$523.61	$117.85	$35,353.88	$34,830.27
13	$641.45	$525.35	$116.10	$34,830.27	$34,304.92
14	$641.45	$527.10	$114.35	$34,304.92	$33,777.82
15	$641.45	$528.86	$112.59	$33,777.82	$33,248.96
16	$641.45	$530.62	$110.83	$33,248.96	$32,718.34
17	$641.45	$532.39	$109.06	$32,718.34	$32,185.95
18	$641.45	$534.17	$107.29	$32,185.95	$31,651.78
19	$641.45	$535.95	$105.51	$31,651.78	$31,115.83
20	$641.45	$537.73	$103.72	$31,115.83	$30,578.10
21	$641.45	$539.53	$101.93	$30,578.10	$30,038.57
22	$641.45	$541.32	$100.13	$30,038.57	$29,497.25
23	$641.45	$543.13	$98.32	$29,497.25	$28,954.12
24	$641.45	$544.94	$96.51	$28,954.12	$28,409.18
25	$641.45	$546.76	$94.70	$28,409.18	$27,862.43
26	$641.45	$548.58	$92.87	$27,862.43	$27,313.85
27	$641.45	$550.41	$91.05	$27,313.85	$26,763.44
28	$641.45	$552.24	$89.21	$26,763.44	$26,211.20
29	$641.45	$554.08	$87.37	$26,211.20	$25,657.12
30	$641.45	$555.93	$85.52	$25,657.12	$25,101.19
31	$641.45	$557.78	$83.67	$25,101.19	$24,543.41
32	$641.45	$559.64	$81.81	$24,543.41	$23,983.77
33	$641.45	$561.51	$79.95	$23,983.77	$23,422.26
34	$641.45	$563.38	$78.07	$23,422.26	$22,858.88
35	$641.45	$565.26	$76.20	$22,858.88	$22,293.63
36	$641.45	$567.14	$74.31	$22,293.63	$21,726.49
37	$641.45	$569.03	$72.42	$21,726.49	$21,157.46
38	$641.45	$570.93	$70.52	$21,157.46	$20,586.53
39	$641.45	$572.83	$68.62	$20,586.53	$20,013.70
40	$641.45	$574.74	$66.71	$20,013.70	$19,438.96
41	$641.45	$576.66	$64.80	$19,438.96	$18,862.30
42	$641.45	$578.58	$62.87	$18,862.30	$18,283.72
43	$641.45	$580.51	$60.95	$18,283.72	$17,703.22
44	$641.45	$582.44	$59.01	$17,703.22	$17,120.78
45	$641.45	$584.38	$57.07	$17,120.78	$16,536.39
46	$641.45	$586.33	$55.12	$16,536.39	$15,950.06
47	$641.45	$588.29	$53.17	$15,950.06	$15,361.78
48	$641.45	$590.25	$51.21	$15,361.78	$14,771.53
49	$641.45	$592.21	$49.24	$14,771.53	$14,179.32
50	$641.45	$594.19	$47.26	$14,179.32	$13,585.13
51	$641.45	$596.17	$45.28	$13,585.13	$12,988.96
52	$641.45	$598.16	$43.30	$12,988.96	$12,390.80
53	$641.45	$600.15	$41.30	$12,390.80	$11,790.65
54	$641.45	$602.15	$39.30	$11,790.65	$11,188.50
55	$641.45	$604.16	$37.30	$11,188.50	$10,584.34
56	$641.45	$606.17	$35.28	$10,584.34	$9,978.17
57	$641.45	$608.19	$33.26	$9,978.17	$9,369.98
58	$641.45	$610.22	$31.23	$9,369.98	$8,759.76
59	$641.45	$612.25	$29.20	$8,759.76	$8,147.51
60	$641.45	$614.29	$27.16	$8,147.51	$7,533.21
61	$641.45	$616.34	$25.11	$7,533.21	$6,916.87
62	$641.45	$618.40	$23.06	$6,916.87	$6,298.48
63	$641.45	$620.46	$20.99	$6,298.48	$5,678.02
64	$641.45	$622.53	$18.93	$5,678.02	$5,055.49
65	$641.45	$624.60	$16.85	$5,055.49	$4,430.89
66	$641.45	$626.68	$14.77	$4,430.89	$3,804.21
67	$641.45	$628.77	$12.68	$3,804.21	$3,175.44
68	$641.45	$630.87	$10.58	$3,175.44	$2,544.57
69	$641.45	$632.97	$8.48	$2,544.57	$1,911.60
70	$641.45	$635.08	$6.37	$1,911.60	$1,276.52
71	$641.45	$637.20	$4.26	$1,276.52	$639.32
72	$641.45	$639.32	$2.13	$639.32	$0.00
Total	$46,184.40	$41,000.00	$5,184.58