Question

please solve without excel

2. Suppose you are in the market for a new car worth $35,000. You are offered a deal to make a 15% down payment now and to pay the balance in equal end-of-month payments over 60 months with an APR of 12%. a. What should be the required monthly payment? b. Complete the following table: End of Month Interest Payment Principle Payment Remaining Balance 31 $114 42 49

Answer 1

Cost of car= $35,000

Down payment=15%

Therefore, loan amount= $35,000*(1-15%)= $29,750

Monthly payment required is $661.77 calculated as follows:

A B C D 1 Equal Monthly payments of fixed rate loan (EMI) 2 Payments at the end of each month 3 Monthly payments is calculated using the formula EMI=[P*r*(1+r)^n]/[(1+r)^n]-1 4 Where P= Principal (loan amount), 5 n= Number of installments. ie if N= number of years, n=N*12 6 r=Rate of interest per month in decimals ie. If R= Yearly rate, r=R/(100*12) 7 All amounts in $ 8 Given that 9 Principal (loan amount) (P)= 29,750 No. of years (N)= 10 Rate of interest (% p.a)=R= 12 No. of months (n)= 11 12 Calculations: 13 Formula Cell reference Value 14 Interest rate per month in decimals (r = R/(100*12) D10/(100*12) 0.01 15 No. of months (n)= N*12 G9*12 60 16 (1+r)= 1+G14 1.01 17 (1+r)^n= G164G10 1.8166967 [(1+r)^n)-1= G17-1 0.8166967 19 P*r*(1+r)^n= D9*G14*G17 540.46727 20 Monthly payments [P*r*(1+r)^n]/[(1+r)^n]-1 G19/618 661.77232 21 Rounded to $ 661.77

The given table is filled up as follows:

End of month	Interest Payment	Principal payment	Remaining balance
31	$170.79	$490.98	$16,587.84
42	$114	$547.78	$10,851.92
49	$74,48	$587.29	$6,861.01

Relevant portion of amortization schedule is given below:

Month Shedule of amortization Beginning Monthly Monthly interest Principal End balance interest Payments component Component balance rate =Col (2)*(3) =Col (4)-(5) Col (2)-(6) (2) (3) (4) (5) (6) (7) 29750 0.01 661.77 297.5 364.27 29385.73 29385.73 0.01 661.77 293.8572768 367.92 29017.81 (1) 1 2 30 31 32 17564.95 17078.83 16587.84 0.01 0.01 0.01 661.77 175.6494973 661.77 170.7882691 661.77 165.8784286 486.12 490.98 495.89 17078.83 16587.84 16091.95 41 42 43 11942.05 11399.70 10851.92 0.01 0.01 0.01 661.77 119.4204743 661.77 113.9969558 661.77 108.5192022 542.35 547.78 553.25 11399.70 10851.92 10298.67 48 49 8029.77 7 448.30 0.01 0.01 661.77 80.29773302 661.77 74.48298716 581.47 587.29 7448.30 6861.01