NPER Mathmatical Formula

[Ickle]

Hi,

Can anyone tell me the actual mathematical formula that Excel uses in the NPER function. I need to develop an excel model outside of Excel and don't know how to translate this.

Any help gratefully received.

Many Thanks,
Lyn

 

[MickG]

Hi Lynn
At the moment I can't seem to get a lot of sense out of the Function NPER(),
But below is some background information that may assist, If you are after the Number of terms you could use this formula and "Excel's Solver " to Find "n" in the equations.
If this helps and you want further information let me know.
----------------------------------------------------------------

                                          Mortgage / Loan / APR 

Note;- AER Relates to Calculations for Saving Accounts. APR Relates to Loans Mortgages etc
The Method for Deducing from first principles an equation for APR (Annualised Percentage Rate) is shown Below:-
 
Example:- Apply an interest rate to a loan every Period and then deduct from that loan a set amount every Period until Loan Repaid :-

Borrow £100 for 3 year @ 10% Interest with repayments of "S" every month.

Note:-  Interest  Rate of 10% is used in the equation as  =  1.1 because we want to know the Total sum on the capital + the Interest therefore the sum of the (Loan + Interest)  Equals :-
    (100 x 10%) +100 =  100(0.1 +1) =  100 x 1.1  (Q.E.D)  

1st Year (100 x 1.1) - S.
2nd Year [( 100x1.1)- S) x (1.1 - S)]
3rd Year (100 x 1.1^2 - 1.1S - S) x (1.1- S)
This Equals 100 x 1.1^3 - S 1.1^2 -  S1.1 - S =  0   (Eqn 1)

Multiply this equation by  the common factor 1.1

This equals 100 x 1.1^4 - S1.1^3 - S1.1^2 - S1.1 = 0   (Eqn 2)

Subtract Eqn (1) from Eqn (2) by cross cancelling

This leaves:-    100 x 1.1^4  - 100 x 1.1^3   - S1.1^3  + S = 0        note:-  Minus, MinusS (- - S)  =  + S

                                     100 x 1.1^4  - 100 x 1.1^3   = S1.1^3  - S      

Therefore:-                   S  =  100(1.1^4 - 1.1 ^3)
                                              (1.1^3 - 1)

Rearrange in standard form:-
P = Principle.         r = Annual Interest Rate.          R =  (1+ r).        S = Periodic  repayment. 
n = Period (mth etc.)                              

                                             S = P ( R^(n+1) - R^n )
                                                         (R^n) - 1)

Note:- When dealing with Loans and Mortgages the normal repayment period is a month.
In these cases the 12 root of  R would be used to represent a period of one month  
                                           
In the example above 1.1 would be replaced by  1.1(1/ 12)  =   1.00797414  
The period "n" would read 36  equalling 3 x 12.
In the case (n + 1).  1 is always 1 irrespective of whether the periods are months or years.
NB:- For Payments in advance, alter the bottom line of the equation to:-    R^(n+1) - R

-----------------------------------
Regards Mick

 

[Ickle]

Hi Mick,

Thanks for your post. Unfortunately, I can't use a solver as I need to represent the NPER mathmatically so it can be included in javascript code. Thanks anyway.

Lyn

 

[Fazza]

hi Lyn,

Suggest you code a loop and solve iteratively for n - there is no formula like nper = whatever.

regards, Fazza

 

[Ickle]

Thanks, I've found the underlying formula. It is:

=Log10(Payment/(Payment+Capital+Rate))/Log10(1+Rate)

Works a treat!

 

[MickG]

Hi,
Im not sure if this is any help, but below is a bit of code to find your term "n" based on the formula I sent you.
In "A1" enter the Loan amount.
In "B1" enter the periodic repayment amount
In "C1" Enter the interest rate as a decimal ie 6% = 0.06.
In "E1" Enter "m" for monthly repayments "y" for yearly. (enter only the letter)
Ans in msgbox
It seems to work after limited testing, only trials will tell !!

Dim P As Double, r As Double, n As Double, S As Double, oRep As Double
Dim Sn As Double, np As Integer, k As Double, oPer As String
P = [a1].Value
oRep = [B1].Value
k = [C1].Value
oPer = [d1].Value
If oPer = "m" Then
    r = ((1 + k) ^ (1 / 12)) - 1
 ElseIf oPer = "y" Then
     r = (1 + k)
  End If

Do Until S <= oRep And Sn >= oRep
 On Error Resume Next
    n = n + 1
S = P * (((1 + r) ^ (n + 1)) - ((1 + r) ^ n)) / _
        (((1 + r) ^ n) - 1)
 If n > 1 Then
    np = n - 1
      Sn = P * (((1 + r) ^ (np + 1)) - ((1 + r) ^ np)) / _
         (((1 + r) ^ np) - 1)
      End If

If n > 10000 Then
   MsgBox "Solution Not Found, ""Data Maybe invalid"""
  Exit Sub
End If
Loop
MsgBox "The Match for the repayment fig was :- " & Sn
MsgBox "Number of terms equals " & n - 1

Regards Mick

 

[GlennMar]

Thanks, I've found the underlying formula. It is:

=Log10(Payment/(Payment+Capital+Rate))/Log10(1+Rate)

Works a treat!

Lyn,
I am sure you have the right formula, but it did not print correctly in your post. I think the correct formula is:

=LOG10 (A3 / (A3 – A1*A2)) / LOG10(1 + A1)

[from loans & Mortgages @ suite 101 Mortgage and Loan Overpayments Effect on Term: How Mortgages or Loans Terms are Affected by the Amount Overpaid | Suite101.com ]

Or, using your same terms:
=Log10 (Payment / (Payment - Capital * Rate)) / Log10(1 + Rate) .

Note the minus sign in front of Capital (or the principal amount).

If the principal amount is a negative number as normal in Excel interest formulas, I think you need to change the sign, ie
=Log10 (Payment / (Payment + Capital * Rate)) / Log10(1 + Rate) .

I am working on something where I have just tested this formula, so should be correct.

Cheers from Oz.

 

[tusharm]

Umm...no, not quite like a treat. {grin}

As GlennMar has pointed out the correct formula has a Payment - Capital*Rate.

Another requirement for this formula is that the future value (balloon payment, for example) is zero.

Thanks, I've found the underlying formula. It is:

=Log10(Payment/(Payment+Capital+Rate))/Log10(1+Rate)

Works a treat!