Mark asked on # Need formula to calculate number of payments given amout, interest and payment amount

I need to calculate number of payments given interest, beginning principle and payment amount. I've been looking all over the web and I can find plenty of formulas for calculting payment amount give principle, interest and number of payments, I can't find anything solving for number of payments given the other two variables. Help!

Algorithms

Log in or sign up to see answer

Become an EE member today7-DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform

or

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.

Not exactly the question you had in mind?

Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.

ask a questionExperts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!

Walt Forbes

Here is my vb solution:

Dim i As Double

Dim n As Double

' Calculate new number of payments: n

' i*B

' Note, we really want: n = -log (1 - --- )

' 1+i P

'

' where B is loan amount, P is desired payment and i is period interest rate. But VBA

' does not have a Log base X of Y function. To synthesize this we do: log(Y) / log(X)

i = Me.interestRate / 24#

If (1 - ((i * Me.purchaseCost) / Me.paymentAmount) < 0) Then

MsgBox "Requested Payment is less than minimum interest accrued per payment period: " & _

round(Me.purchaseCost * i, 2) & ". Pick a larger payment amount."

Me.paymentAmount = Null

Exit Sub

End If

n = -1 * (Log(1 - ((i * Me.purchaseCost) / Me.paymentAmount)) / Log(1 + i))

Me.payments = Int(n)