CD Type Annuities

Here’s the deal. I am trying to calculate the interest rate of a series of payments (annuity like). Problem is, payments are not equal, I am tying to infer the interest rate on the stream of cash flows. I am aware it’s impossible to solve for interest rate from an annuity, and I am also aware these problems can be solved through iteration. So far, I have solved using goal seek, but as these start to get more complex and increase in number, I need a formula that will solve it without requiring to “goal seek it”.

Please refer to an example workbook I just created on http://www.chuogakuen.com/examples.xls

On example 1, I left interest rate as an input, a very basic easy to solve problem. On example 2, the exact same, but with payment as an input. It all works fine, the only problem, payments MUST BE EQUAL, so the =rate() function works to estimate the interest rate. On example 3, where I have gotten so far, leaving payments as inputs, and I can always solve it goal seeking F23 to go to zero by changing B18. I need B18 not to be an input, but a result.

Any help will be really really really appreciated.
thank you very much
Kio
Thanks for the quick answer Ron!! I’m giving you best answer right away!!

I never thought about IRR!! I mean, I understand IRR, but I didn’t think it could be solved as an NPV problem. It worked perfectly!!! Here’s what’s to do, in case someone else looks at this question in the future: Since the IRR function works only on ranges, the initial loan should be stated first (just as the investment on a typical IRR problem), then all the cash flows IN ORDER. (in the example sheet, add a column in “20″, write “100″ in E20, then run IRR from E20:E24

Thank you very much once again!