Help with RATE function

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
Sat May 03 16:41:16 CDT 2008

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" <JoSchmo@yahoo.com> wrote in message
news:87so14prt1phq92dn1qr6tb081cgeelf46@4ax.com...
> 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?

Joe
Sat May 03 17:05:00 CDT 2008

Getting closer.

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

Thanks!


On Sat, 3 May 2008 14:41:16 -0700, "Ted-im" <ted-im@sbcglobal.net> wrote:

>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" <JoSchmo@yahoo.com> wrote in message
>news:87so14prt1phq92dn1qr6tb081cgeelf46@4ax.com...
>> 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?
>

Jim
Sat May 03 18:13:03 CDT 2008


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" <JoSchmo@yahoo.com>
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!


On Sat, 3 May 2008 14:41:16 -0700, "
Ted-im" <ted-im@sbcglobal.net> wrote:
>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.

joeu2004
Sat May 03 20:04:35 CDT 2008

On May 3, 1:41=A0pm, "Ted-im" <ted...@sbcglobal.net> wrote:
> Try =3DRate(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
Sat May 03 22:03:15 CDT 2008

On May 3, 2:05=A0pm, Joe <JoSc...@yahoo.com> wrote:
> 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
Sat May 03 22:32:55 CDT 2008

Thanks for your explanation
Ted
"joeu2004" <joeu2004@hotmail.com> wrote in message
news:a10b37ac-ed44-425d-96d6-60e3b8760c84@y18g2000pre.googlegroups.com...
On May 3, 1:41 pm, "Ted-im" <ted...@sbcglobal.net> wrote:
> 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
Sun May 04 14:41:10 CDT 2008

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

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

On Sat, 3 May 2008 16:13:03 -0700, "Jim Cone" <james.coneXXX@comcast.netXXX>
wrote:

>
>It gives me 1.7525 for a monthly rate.
>Multiplied by 12 = 21.03%

Joe
Sun May 04 14:50:23 CDT 2008

On Sat, 3 May 2008 20:03:15 -0700 (PDT), joeu2004 <joeu2004@hotmail.com> wrote:

>On May 3, 2:05?pm, Joe <JoSc...@yahoo.com> wrote:
>> 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:

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!