Solving for Rate with Unknown Payment

[rnewton]

I am attempting to bulid a formula that will determine rate on an "end of the period" loan where the only knowns are:

Starting Balance
Term and frequency of payments
Number of payments made
Current Balance

For example: On a $100,000 30yr mortgage, 12 payments have been made, and the current balance is $98,524.66. What is the interest rate?

Thank you!

 

[rnewton]

Thanks guys, Goal Seek is powerful tool, that I have completely missed in excel. I have a follow up question you. Is there a way to get this to work in batches? The list I have is in the tens of thousands...

 

[joeu2004]

Goal Seek is powerful tool, that I have completely missed in excel. I have a follow up question you. Is there a way to get this to work in batches?

I think you would have to be more specific about what you mean by "batches". Even "tens of thousands" is sufficient.

But my guess is: any "batch" solution involves using Goal Seek in VBA. (And I found Solver to be more accurate in this case).

So why not use the VBA solution that I provided in posting #9 ?

It is intuitive insofar as it is a special RATE function. And RATE is what we would use if we knew the periodic payment.

So that allows you to use Excel formulas, as you said you wanted to do at the outset.

 

[rnewton]

Yeah, I didn't have the most recent responses when I asked my last question. Thanks to all for the help