Help with formula to find interest rate

[ExcelRob]

Hello -

I am trying to solve for the implied interest rate.

So in this example, I have the option to pay someone $2,000 today, or $23.33 every month for 10 years (120 equal payments) for a total of $2,800.

I can't figure out which formula(s) to use to find what the implied interest payment would be in this situation so I can decide if it makes sense to pay now or over time.

What formula(s) would I use?

Thanks.

[Colin Legg]

Hi,

It looks like a homework question so I'll only give you a hint.

To calculate the implied rate on the 10 year payment plan, have a look at the RATE() function. When you tell the RATE() function the monthly payment, enter the monthly payment as a negative amount.

[shg]

The APR is =12 * RATE(120, 23.33, -2000) ~ 7.10%

The APY is =(1+RATE(120, 23.33, -2000))^12 - 1 ~ 7.34%

[ExcelRob]

The negative monthly payment! I knew it was the RATE function but didn't think to use the negative.

Thanks for the hint Colin. That was exactly what I wanted. While it wasn't homework it felt like it. I'm just getting used to using these financial formulas for work.

Here's what I came up with.

=RATE(120,-23.33,2000)

0.59%.

But does excel know that its 120 months and not some other period length?

[Colin Legg]

Yes, you've done the right thing there - you have monthly payments so you have 120 periods. The result of the formula you posted is the monthly rate, not the annual rate.

[ExcelRob]

Thanks again.

[ExcelRob]

Thanks shg.

[shg]

You're welcome, glad it worked for you.