Question

Loan Plans: (a) 3% financing for 36-month loan or (b) 2% financing for 72-month loan.

If you are looking at $25000 car, what is your monthly payment under each option? Over the entire life of the loan, what is the total amount of interest paid under each option? Which financing option should you take?

Answer 1

Monthly payment in each method can be calculated as follows in excel

Option A =PMT(3%,36,-25000,0) = $1145.09

Here 1^st argument is interest rate

2^nd argument is months

3^rd argument is principal amount

4^th argument is future value, which is 0 since loan has to be paid in 36 months

Option B =PMT(2%,72,-25000,0) = $658.17

So the monthly payment is higher in Option A. Lets build a loan schedule for these options.

Option A

Month	Beginning Balance	Monthly Payment	Interest	Payment to Principal	Ending Principal
1	25,000	1,145	750	395	24,605
2	24,605	1,145	738	407	24,198
3	24,198	1,145	726	419	23,779
4	23,779	1,145	713	432	23,347
5	23,347	1,145	700	445	22,902
6	22,902	1,145	687	458	22,444
7	22,444	1,145	673	472	21,973
33	4,256	1,145	128	1,017	3,239
34	3,239	1,145	97	1,048	2,191
35	2,191	1,145	66	1,079	1,112
36	1,112	1,145	33	1,112	0

Here 1^st Column is Month

2^nd column is beginning balance in that month. So it is 25000 in 1^st month and beginning balance in 2^nd month is equal to ending balance of 1^st month

3^rd column is monthly payment as calculated above

4^th column in interest in that month = 3% * beginning balance

5^th column is principal payment = monthly payment – interest for that period

6^th column is ending principal = beginning principal – principal payment

So total of 4^th column = 16,223 is total interest paid in entire loan tenure

Option B

Month	Beginning Balance	Monthly Payment	Interest	Payment to Principal	Ending Principal
1	25,000	658	500	158	24,842
2	24,842	658	497	161	24,680
3	24,680	658	494	165	24,516
4	24,516	658	490	168	24,348
5	24,348	658	487	171	24,177
6	24,177	658	484	175	24,002
7	24,002	658	480	178	23,824
70	1,898	658	38	620	1,278
71	1,278	658	26	633	645
72	645	658	13	645	(0)

So total of 4^th column = 22,388 is total interest paid in entire loan tenure.

PS, I skipped few rows in between because of 65000 characters limitation.

So better option is to go with option A since the total interest paid is less.