Avatar of Mark
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

Avatar of undefined
Last Comment
Mark

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
winblows98

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
Sign up - Free for 7 days
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.
See how we're fighting big data
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 question
ASKER
Mark

Yes, I did find it. My problem was in trying to calculate number of payments when the payment amount was less than the periodic principle plus interest.

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)
Experts 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