Help with RATE function

J

Joe

My task is to deteremine an equivalent loan interest rate to a lease payment.

ie: Lease payment term is 60 months, payment is $135.35, funded amount is $5,000

I've been trying to use the RATE function, but need some help entering the data
properly. Every manner I've tried results in a NUM error.

=RATE(60,(133.35),5000) is my last feeble attempt
(I am referrencing cells, I just enered the numbers for clarity.

I tried the payment as a negative after reading another post.
I left out the $ formatting sign, just in case.

What am I missing?
 
T

Ted-im

Try =Rate(60,-135.35,5000)

I'm sure more knowledgeable people will

jump in and give you an explanation.

Hopefully this will get you going.
 
J

Joe

Getting closer.

The negative payment produces a real result, but the rate calculated is 1.5% and
it should be about 19%

Thanks!
 
J

joeu2004

Try =Rate(60,-135.35,5000)
I'm sure more knowledgeable people will
jump in and give you an explanation.
Click to expand...

When using the financial functions, inflows and outflows must have
opposite signs. Which is which depends on your point of view (lender
or borrower). So you could also write RATE(60,135.35,-5000).

It should also be noted that RATE returns the __periodic__ interest
rate -- the rate per each of the 60 periods. If the period is not in
years, we have to annualize the rate. Typically, multiply by 12 to
convert a monthly rate to an annual interest rate, at least for US
loans.
 
T

Ted-im

Thanks for your explanation
Ted
Try =Rate(60,-135.35,5000)
I'm sure more knowledgeable people will
jump in and give you an explanation.
Click to expand...

When using the financial functions, inflows and outflows must have
opposite signs. Which is which depends on your point of view (lender
or borrower). So you could also write RATE(60,135.35,-5000).

It should also be noted that RATE returns the __periodic__ interest
rate -- the rate per each of the 60 periods. If the period is not in
years, we have to annualize the rate. Typically, multiply by 12 to
convert a monthly rate to an annual interest rate, at least for US
loans.
 
J

Joe

Ahhhh!
What a dumbass I am.
Thanks so much.

I wish the hints that Excel provides when you insert a function were more
descriptive.
 
J

Joe

If you are saying that you already know that the lease interest rate
should be about 19% and you continue to have trouble getting that
result even with 12*RATE(60,-135.35,5000), the answer might lie in the
explanation on the following web pages:
Click to expand...

I just did some manual regression to find the equivalent interest rate, but I'm
building a spreadsheet to relate money factors to interest rates and evaluate
the value to the dealers and their customers. I've found I'm far better in sales
than I am in finance, Excel can be tough when you use it sparingly as I do
http://www.leaseguide.com/lease08.htm
http://www.leaseguide.com/lease09.htm
Click to expand...

Yep, the dealers will bang you when they can. They got me once about 10 years
ago, but today I'm hip to their tricks

I need to "back out" the interest rate from a table of money factors or buy
rates that a lease company has provided. The reason is to explain and compare
lease costs to dealers we're trying setup leasing programs for. The lender has
given us the rate tables, but I prefer to have more understanding.

Thanks all!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Rate() function in Expression Builder 3
PMT function 2
APR calculation for Mortgages with a balloon payment. 6
PMT function question 2
Rate syntax 2
Financial function - calculating loans with deferred payments 2
Function for interest rate over several years 1
T Vaule function 6

Top