Help with RATE function

[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?

 

[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.

 

[Joe]

Getting closer.

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

Thanks!

 

[Jim Cone]

It gives me 1.7525 for a monthly rate.
Multiplied by 12 = 21.03%
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


"Joe" <[email protected]>
wrote in message
Getting closer.
The negative payment produces a real result,
but the rate calculated is 1.5% and it should be about 19%
Thanks!

 

[joeu2004]

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

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.

 

[joeu2004]

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

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:

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

 

[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.

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.

 

[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.

 

[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:

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

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!