Calculating the total interest paid on a car lease

[Domenic]

Hi everyone,

As a purely academic exercise, I have the following car lease. The dealer has calculated the weekly payment as $130.22, and the total interest cost as $10,156.90. However, while I too have calculated the weekly payment as $130.22, I have calculated the total interest cost as $10,157.79. Can someone please confirm the correct amount for the total interest cost?

Weekly.xlsm
	A	B	C	D
1
2		Vehicle Price	$37,596.00
3		Down Payment	$0.00
4		Trade-in Allowance	$0.00
5		Net Vehicle Price	$37,596.00
6
7		Residual Value	$13,895.70
8		Present Value of Residual	$9,462.75
9
10		Total Interest Cost	$10,157.79
11
12		Term (months)	60
13
14		Annual Interest Rate	7.69%
15
16		Weekly Payment	$130.22
17
18
19		Note:
20		1) Interest compounded weekly, not in advance.
21		2) Payment made at the beginning of the period.
22
Weekly Payments

Cell Formulas
Range	Formula
C5	C5	=C2-C3-C4
C8	C8	=-PV(C14/52,C12/12*52,,C7,0)
C10	C10	=-CUMIPMT(C14/52,C12/12*52,C5-C8,1,C12/12*52,1)+(C7-C8)
C16	C16	=-PMT(C14/52,C12/12*52,C5,-C7,1)

Cells with Data Validation
Cell	Allow	Criteria
C12:C13	List	24,36,48,60,72

Thank you for your help, cheers!

 

[6StringJazzer]

They dealer did not use the same calculation you did to get the interest. They took the sales price minus residual value minus total of payments.

You have not rounded your monthly payment. You don't use it any calculations but you have calculated $130.22340693912 but you will be paying $130.22. If you calculate the total amount of payments using these two numbers, you will find that the difference is $0.89, which exactly the difference between the dealer's total interest and your total interest.

BTW I have never heard of weekly car lease payments. I have also never heard of any kind of a deal expressed as a number of months, when the payment term is weekly. Normally the term is expressed in the same units as the payment cycle.

 

[Domenic]

Hi Jeff,

Yeah, I see what you mean. Thanks for pointing that out.

Here's what I noticed, though. If I create an amortization table with all of the calculations rounded to 2 decimal places, I'm left with a balance of $1.04. Whereas if I don't round the calculations, I'm left with a balance of $0.00 (actually, less than 1 cent). And when I total the interest from the table without the rounded calculations, the result is the same as mine using the formula in my original post, not the dealers.

So does this mean that, in practice, since payments are rounded, there will always be a small balance remaining that I guess gets written off?

And yeah, some dealers here in Canada offer weekly and bi-weekly payments, in addition to monthly payments. And they also offer anywhere from 2 to 6 year terms, but they are usually expressed in months.

With rounded calculations...

Period	Payment	Interest	Principal	Balance
				$37,596.00​
1	$130.22​	$0.00​	$130.22​	$37,465.78​
2	$130.22​	$55.41​	$74.81​	$37,390.97​
3	$130.22​	$55.30​	$74.92​	$37,316.05​
4	$130.22​	$55.18​	$75.04​	$37,241.01​
5	$130.22​	$55.07​	$75.15​	$37,165.86​
---	---	---	---	---
---	---	---	---	---
---	---	---	---	---
256	$130.22​	$21.33​	$108.89​	$14,313.40​
257	$130.22​	$21.17​	$109.05​	$14,204.35​
258	$130.22​	$21.01​	$109.21​	$14,095.14​
259	$130.22​	$20.84​	$109.38​	$13,985.76​
260	$130.22​	$20.68​	$109.54​	$13,876.22​
261	$13,895.70​	$20.52​	$13,875.18​	$1.04​

Without rounded calculations...

Period	Payment	Interest	Principal	Balance
				$37,596.00​
1	$130.22​	$0.00​	$130.22​	$37,465.78​
2	$130.22​	$55.41​	$74.82​	$37,390.96​
3	$130.22​	$55.30​	$74.93​	$37,316.03​
4	$130.22​	$55.18​	$75.04​	$37,240.99​
5	$130.22​	$55.07​	$75.15​	$37,165.84​
---	---	---	---	---
---	---	---	---	---
---	---	---	---	---
256	$130.22​	$21.33​	$108.90​	$14,312.38​
257	$130.22​	$21.17​	$109.06​	$14,203.32​
258	$130.22​	$21.00​	$109.22​	$14,094.10​
259	$130.22​	$20.84​	$109.38​	$13,984.72​
260	$130.22​	$20.68​	$109.54​	$13,875.18​
261	$13,895.70​	$20.52​	$13,875.18​	$0.00​

 

[6StringJazzer]

in practice, since payments are rounded, there will always be a small balance remaining that I guess gets written off?

I doubt it. I assume that the bank will calculate a final payoff amount for the last payment that includes that $1.04. If banks wrote off rounding errors it would cost them a lot of money.

Thanks for the explanation, did not know you were in Canada.

 

[Domenic]

I doubt it. I assume that the bank will calculate a final payoff amount for the last payment that includes that $1.04. If banks wrote off rounding errors it would cost them a lot of money.

Yeah, I think you're right.

Thanks for the explanation, did not know you were in Canada.

Yeah, here in Canada, it seems we do things a bit differently.

When one checks the website for the various car companies here, everything is spelled out. They state the annual interest rate and the residual value, along with everything else.

In quickly checking a few websites in the US, nowhere did it state the interest rate or the residual value. And, on top of that, my understanding is that if you ask for the interest rate, you're given something called the money factor, which you have to multiply by 2400 to give you an approximate interest rate equivalent. Really???

Anyway, thanks for your help with this, I really appreciate it.

Cheers!