Solving for an Unknown Interest Rate

[George103]

Hi Forum,

This is my first posting, so please be forgiving.

I need to know the most appropriate Excel function for solving a compound interest rate calculation for an unknown interest rate.

For example, $193 = $100*(1+I)^9yrs, solve for I. By trial and error, I know the solution is about 7.6%pa. Which of the many Excel financial or mathematical functions will give this answer.

Thanks

[darkyam]

=Rate(<# of periods>,<payment amount>,<pv>,<fv>) FV is not necessary, 0 if ommitted, and usually must be the opposite sign of pv to be correct.

[George103]

Hi Darkyam,

Thanks for your prompt reply. I had a look at the RATE function that you suggested and I don't think it will be suitable for my application.

I didn't provide exactly the right situation that I am working with. It's not really an interest rate calculation, per se. It's more of a compound performance or growth calculation.

Year# 0 1 2 3 4 5 6 7 8 9
Value 100 115 135 136 130 155 153 150 161 183

What my average annual performance or growth? Ans = 7.6%pa

Sorry!

[darkyam]

I disagree. You can use the final values of 183 and 9 as FV (make 183 negative) and # periods and 100 as PV. This formula will get you an answer of about 7.6% with these inputs and 0 as the payment.

[aceofspades4]

If you want to solve the compound interest formula by the interest rate, then you would need do the following:
In cell A1 Type "Future", in Cell B1 Type "Current", in Cell C1 type "# Compounds", and in Cell D1 type in "Int Rate".
Click cell D2 and format as a PERCENTAGE
These are just headers for organizational purposes.
Now In cell D2 insert the following formula: =((A2/B2)^(1/C2))-1
Now plug in the figures, 193 in cell A2, 100 in cell B2, and 9 in cell C2.
The figure of 7.57926923788408% should automatically appear in D2.
When you change the figures, you're interest rate will change as well.

[MoneyMaker]

Hi Darkyam,

Thanks for your prompt reply. I had a look at the RATE function that you suggested and I don't think it will be suitable for my application.

I didn't provide exactly the right situation that I am working with. It's not really an interest rate calculation, per se. It's more of a compound performance or growth calculation.

Year# 0 1 2 3 4 5 6 7 8 9
Value 100 115 135 136 130 155 153 150 161 183

What my average annual performance or growth? Ans = 7.6%pa

Sorry!

DarkYam is correct in his suggestion for using the RATE function
RATE function solves for interest rate in various situations

The formula you quoted in your original question is the compound amount formula also referred to as future value formula

Here is the formula with a little amendment

FV = PV (1+i/m)^nm

where FV is the compound amount or future amount
PV is the present value
i is the interest rate
m is the number of compounding periods
n is the number of periods

Excel uses a slightly different TVM equation in solving for interest rate thus making it more useful

PV(1+RATE)^NPER + PMT (1+RATE*TYPE) [{(1+RATE)^NPER} - 1] / RATE + FV = 0

This equation holds true i.e. it equals ZERO when at least one or at most two of the three variables of PV, FV, and PMT are Negative

You can use the following values in Excel RATE function or in this online Excel RATE function calculator to find the rate of 7.58%

PV = -100
FV = 193
NPER = 9
PMT = 0
TYPE = 0 or 1 (doesn't matter here since we have a lump-sum amount)
GUESS = 10% or omitted
RATE =?

Now if you were to find the interest RATE when interest is compounded continuously rather than discretely

Then the formula you listed will change to the following

FV = PV (e)^in
where e is the Euler e constant value of 2.7182818284590452353602874713527

In this case Excel RATE function will be of no help and you would have to resort to using tadRATE function to find the interest rate

[MoneyMaker]

I am very sorry for posting my last reply as I just noticed this is a four year old thread