John borrows $30,000 to buy a new car from a local bank with 0.55% interest rate...

Free

70.2K

Verified Solution

Question

Finance

John borrows $30,000 to buy a new car from a local bank with0.55% interest rate per month (you don’t need to do any rateconversion). He agrees to pay off the loan in 36 equal end-of-monthpayments. The first payment will be made at the end of thismonth.
a. What should be the monthly payment? Report the answer with 2numbers after decimal place
b. Create an amortization table showing his payment, interestpayment, principal payment and loan balance over time. What is theTotal Interest payment of the loan over the 36 months? NOTE: if theloan 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 hepays to the bank over the life of the loan is 4,343.71? Report theanswer in percentage term with 2 numbers after decimal place suchas 12.43%.
please show excel formulas

Answer & Explanation Solved by verified expert

3.6 Ratings (411 Votes)

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%

Get Answers to Unlimited Questions

Join us to gain access to millions of questions and expert answers. Enjoy exclusive benefits tailored just for you!

Membership Benefits:

Unlimited Question Access with detailed Answers
Zin AI - 3 Million Words
10 Dall-E 3 Images
20 Plot Generations
Conversation with Dialogue Memory
No Ads, Ever!
Access to Our Best AI Platform: Flex AI - Your personal assistant for all your inquiries!

Become a Member