Auto Lease Formula

[la90292]

Does anyone have an auto lease formula?

1. Cap Cost= $31,700.38
2. Residual Value= $19,585.05
3. Money Factor= 0.00215
4. Term= 36 month
5. Payments per year = 12
6. Payment= ? should be $446.80 according to the dealer without sales tax
7. Sales Tax = 8.25%

Thanks, victor

 

[Fred Smith]

You should be able to calculate an auto lease using the standard PMT
function:

=PMT(rate,36,31700.38,-19585.05)

Your problem is that "money factor" is not an interest rate. Using your
payment and the RATE function, I calculate the effective interest rate as
..42%/month or 5.1% per year. Sounds like a pretty good lease to me.

You'll only be able to verify the payment if you get a proper interest rate
from your dealer.

 

[Norman Harker]

Hi la90292!

I can't find any standard formula to get this repayment although a
RATE function reveals a nominal rate of 5.09595812042727% per annum
compounded monthly.

You might find a definition of the mysterious (to me) "Money Factor"
in the lease documents and from that be able to calculate the
payments.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[la90292]

What I've found out about the money factor rate is that you mutiply it by
2400 to obtain the interest rate. In this case it would be 5.16%. However,
in my leasing research that doesn't tell me if that is a nominal or
effective rate.

Does this info help?

Thanks, victor

 

[Norman Harker]

Hi la90292!

Closest I can get is:

=PMT(0.00215*24/12,36,31700.38,-19585,1)
Returns: -446.276285129223

That uses 5.16% as a nominal rate compounded monthly and payments made
in advance

But other alternatives of using it as an annual effective rate produce
(paid in arrears) -445.62225963951 or (in advance) -443.757795155806

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Alton B. Wilson]

Victor,

A few years ago, I went to a Carmax store to look at BMW's. The
conversation turned towards leasing, and the salesman brought out a bunch of
numbers on a paper that were obviously generated by a spreadsheet. I asked
him about it, and he said, "Yes, we have everything in Excel." I used to
have the Excel spreadsheet, but lost it due to a virus and loss of
information on my hard drive. You may want to approach Carmax or some other
dealer and ask about leasing, and then ask if they will give you the
spreadsheet so that you can do some home calculations.

Good luck,

Alton

 

[Dana DeLouis]

Microsoft has an Excel template called "Buy vs. Lease Car Calculator."
However, I don't think it has everything the op wants. I don't think
there's a place for "Money Factor", and things like that.

<Check for line-wrap>
<It's in the Template gallery, then search for "Lease.">

http://office.microsoft.com/templat...&Query=lease&Scope=TC,HP,HA,RC,FX,ES,EP,DC,XT

 

[Norman Harker]

Hi Dana!

Have you (or anyone) got an idea what this money factor is? In real
estate we have things like YP / Net income multipliers but there's a
formula behind them.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.

 

[Dana DeLouis]

Hi Norman. I am not sure. It looks to me that it is a number used to
quickly estimate one's monthly payment. It looks like it is the annual
interest rate divided by 2400. I found a web site that tried to explain it
a little better...

http://www.efunda.com/formulae/finance/moneyfactor.cfm

And that's pages subdirectory...
http://www.efunda.com/formulae/finance/proof.cfm

Another:
http://www.leaseguide.com/lease08.htm

What I am thinking is that since it is an estimate (I think), maybe that is
why it is not on that Lease / Buy template. I don't know.

 

[Norman Harker]

Hi Dana!

Thanks for those references. Interesting!

Out Truth In Lending legislation requires quotation of an Annual
Nominal compounded monthly so Money Factors would not be necessary. If
the rate quoted was calculated using the money factor it would be
above the effective rate. I think it might be below the equivalent
nominal rate and that could cause problems.

But all this estimation stuff is no longer necessary now that we have
the tools to calculate accurately and quickly.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.