I don't have an Excel version, but the formula is (relatively) straightforward:
N = total negotiated price, before taxes, but including all other fees and extras.
C = capitalization cost reduction (e.g. 0 if you are putting $0 down)
P = net price = N - C
R = residual value at the end of lease (vehicle MSRP * residual percentage, regardless of the actual negotiated price, and not including any taxes, freight and PDI, or extras, so for example, on a 335i sedan with metallic paint, a 52% residual is $52,200 * .52 = $27,144)
T = lease term in months (e.g. 36)
M = money factor (APR / 24, so 3.9% = 0.039 / 24 = 0.001625)
D = monthly depreciation cost: =(P - R) / T
F = financing charge =(P + R) * M
P = monthly payment = D + F
P does not include taxes (multiply by 1 + tax rate to get total monthly payment. E.g., in Ontario, Pt = P * 1.13)
Also worth noting is your average monthly cost:
((Pt * F) + C) / F
And total cost over the lease period:
(Pt * F) + C
Not counting any lease turn-in fees or costs for excess wear and tear, replacement tires, etc.
That should be correct, though my brain is not exactly in math mode tonight.
|