Question

You are considering purchasing a car with a sticker price of $36,270 (nonnegotiable with no down payment required). You wish to make monthly payments for six years and the most you can afford to pay is $600 a month. Your local bank/credit union has agreed to loan you the money at a 4.39% annual interest rate. Create an amortization table reporting the beginning/ending loan balance, total payment, and portion of payment going to interest and principal. Create a IF statement that answers the question of whether you can afford the loan. What is your monthly loan payment and what is your total interest paid on the loan?

Answer 1

Calculation of monthly loan payment
We can use the present value of annuity formula to calculate the monthly loan payment.
Present Value of annuity = P x {[1 - (1+r)^-n]/r}
Present Value of annuity = loan amount = $36,270
P = monthly loan payment = ?
r = rate of interest per month = 4.39%/ 12 = 0.003658
n = number of monthly loan payments = 6 years x 12 = 72
36270 = P x {[1 - (1+0.003658)^-72]/0.003658}
36270 = P x {0.231196/0.003658}
36270 = P x 63.19712
P = 573.92
Monthly loan payment = $573.92
Yes, you can afford a loan payment per month.
Calculation of total interest paid on loan
Total interest paid on loan = [Monthly loan payment x number of monthly loan payments] - Original Loan amount
Total interest paid on loan = [$573.92 x 72] - $36,270
Total interest paid on loan = $41,322.14 - $36,270
Total interest paid on loan = $5,052.14
Loan amortization table
Monthly Installment No.	Loan Beginning balance	Monthly Payment	Towards Interest	Towards Pricipal	Loan Ending balance
1	$36,270	$574	$133	$441	$35,829
2	$35,829	$574	$131	$443	$35,386
3	$35,386	$574	$129	$444	$34,941
4	$34,941	$574	$128	$446	$34,495
5	$34,495	$574	$126	$448	$34,048
6	$34,048	$574	$125	$449	$33,598
7	$33,598	$574	$123	$451	$33,147
8	$33,147	$574	$121	$453	$32,695
9	$32,695	$574	$120	$454	$32,240
10	$32,240	$574	$118	$456	$31,784
11	$31,784	$574	$116	$458	$31,327
12	$31,327	$574	$115	$459	$30,867
13	$30,867	$574	$113	$461	$30,406
14	$30,406	$574	$111	$463	$29,944
15	$29,944	$574	$110	$464	$29,479
16	$29,479	$574	$108	$466	$29,013
17	$29,013	$574	$106	$468	$28,545
18	$28,545	$574	$104	$469	$28,076
19	$28,076	$574	$103	$471	$27,605
20	$27,605	$574	$101	$473	$27,132
21	$27,132	$574	$99	$475	$26,657
22	$26,657	$574	$98	$476	$26,181
23	$26,181	$574	$96	$478	$25,703
24	$25,703	$574	$94	$480	$25,223
25	$25,223	$574	$92	$482	$24,741
26	$24,741	$574	$91	$483	$24,258
27	$24,258	$574	$89	$485	$23,772
28	$23,772	$574	$87	$487	$23,286
29	$23,286	$574	$85	$489	$22,797
30	$22,797	$574	$83	$491	$22,306
31	$22,306	$574	$82	$492	$21,814
32	$21,814	$574	$80	$494	$21,320
33	$21,320	$574	$78	$496	$20,824
34	$20,824	$574	$76	$498	$20,326
35	$20,326	$574	$74	$500	$19,827
36	$19,827	$574	$73	$501	$19,325