Rate of Return calculation with series of equal payments

In engineering economy problems we want to know the rate of return on an investment that returns a series of equal annual palyments for n years. This is a simpler problem than the IRR function is designed for, since it deals with varying payments which must be spelled out in an array.

I don't want to put the same annual payment in 20 cells to use IRR, and I want the number of years to be a variable, which IRR would not lend itself to. Rate should be able to do this task in a single cell, but the results I am getting are not accurate.

Basically I want to find the interest rate that will make the present value of the annual payments equal to the initial investment.

I would use Goal Seek, because it requires iteration to determine the answer.

I can never remember the formula, so instead I remember how to derive it:

The NPV formula is:
NPV = A*(1+i)^-1 + A*(1+i)^-2 + ... A*(1+i)^-n

If you multiply both sides by 1 + i:
NPV * (i+i) = A + A*(1+i)^-1 + ... + A*(1+i)^-(n-1)

If you subtract the first equation from the first:
NPV (1-i-1) = A*(1+i)^-n - A
i * NPV = A ( 1 - (1+i)^n)
NPV = A * (1 - (1+i)^n)/i

So, you stuff that formula in a cell, and let goal seek vary i to make the cell value equal to the initial investment.

They say this is how Warren Buffet picks stocks. So, if you find a good stock, let us know!

Yes I tried that approach and it definitely works, but there is, of course, a catch. Goal seeker will not let you point to the cell you want to match; you have to type in the value in the window. For some reason it won't accept a cell reference.

I suppose I could set up my own goal seeker using IF statements. Pesky little problem isn't it? Thanks for the help!

DJ