Question

John borrows $30,000 to buy a new car from a local bank with 0.55% interest rate per month (you don’t need to do any rate conversion). He agrees to pay off the loan in 36 equal end-of-month payments. The first payment will be made at the end of this month.

a. What should be the monthly payment? Report the answer with 2 numbers after decimal place

b. Create an amortization table showing his payment, interest payment, principal payment and loan balance over time. What is the Total Interest payment of the loan over the 36 months? NOTE: if the loan balance is not equal to 0 at the end, you did something wrong! Report the answer with 2 numbers after decimal place

c. What must be the interest rate so that the total interest he pays to the bank over the life of the loan is 4,343.71? Report the answer in percentage term with 2 numbers after decimal place such as 12.43%.

please show excel formulas

Answer 1

1.
=PMT(0.55%,36,-30000)=$920.84

2.
=PMT(0.55%,36,-30000)*36-30000=$3,150.10

Payment	Loan beginning balance	Payment	Interest payment	Principal payment	Loan ending balance
1	30000	$920.84	$165.00	$755.84	$29,244.16
2	$29,244.16	$920.84	$160.84	$759.99	$28,484.17
3	$28,484.17	$920.84	$156.66	$764.17	$27,720.00
4	$27,720.00	$920.84	$152.46	$768.38	$26,951.62
5	$26,951.62	$920.84	$148.23	$772.60	$26,179.02
6	$26,179.02	$920.84	$143.98	$776.85	$25,402.17
7	$25,402.17	$920.84	$139.71	$781.12	$24,621.04
8	$24,621.04	$920.84	$135.42	$785.42	$23,835.62
9	$23,835.62	$920.84	$131.10	$789.74	$23,045.88
10	$23,045.88	$920.84	$126.75	$794.08	$22,251.80
11	$22,251.80	$920.84	$122.38	$798.45	$21,453.35
12	$21,453.35	$920.84	$117.99	$802.84	$20,650.50
13	$20,650.50	$920.84	$113.58	$807.26	$19,843.25
14	$19,843.25	$920.84	$109.14	$811.70	$19,031.55
15	$19,031.55	$920.84	$104.67	$816.16	$18,215.38
16	$18,215.38	$920.84	$100.18	$820.65	$17,394.73
17	$17,394.73	$920.84	$95.67	$825.17	$16,569.57
18	$16,569.57	$920.84	$91.13	$829.70	$15,739.86
19	$15,739.86	$920.84	$86.57	$834.27	$14,905.60
20	$14,905.60	$920.84	$81.98	$838.86	$14,066.74
21	$14,066.74	$920.84	$77.37	$843.47	$13,223.27
22	$13,223.27	$920.84	$72.73	$848.11	$12,375.17
23	$12,375.17	$920.84	$68.06	$852.77	$11,522.39
24	$11,522.39	$920.84	$63.37	$857.46	$10,664.93
25	$10,664.93	$920.84	$58.66	$862.18	$9,802.75
26	$9,802.75	$920.84	$53.92	$866.92	$8,935.83
27	$8,935.83	$920.84	$49.15	$871.69	$8,064.14
28	$8,064.14	$920.84	$44.35	$876.48	$7,187.66
29	$7,187.66	$920.84	$39.53	$881.30	$6,306.35
30	$6,306.35	$920.84	$34.68	$886.15	$5,420.20
31	$5,420.20	$920.84	$29.81	$891.03	$4,529.18
32	$4,529.18	$920.84	$24.91	$895.93	$3,633.25
33	$3,633.25	$920.84	$19.98	$900.85	$2,732.40
34	$2,732.40	$920.84	$15.03	$905.81	$1,826.59
35	$1,826.59	$920.84	$10.05	$910.79	$915.80
36	$915.80	$920.84	$5.04	$915.80	$0.00

3.
=RATE(36,(30000+4343.71)/36,-30000)*12=9.00%