NPER Function using repayments that increase over 5 years

mabbutt

Board Regular
Joined
Oct 4, 2014
Messages
106
Hi,

I am trying to create a mortgage calculator that forecasts the number of months it will take to pay off the loan.

I have successfully done this for a basic calculation where the monthly payment doesn't change but what I need is a calculation that works for payments increasing over 5 years.

I have uploaded my spreadsheet to Dropbox at the following link as it might be easier to understand if you can see what I am doing:

https://www.dropbox.com/s/1fg47837cymjv0k/Payment Mortgage Calculator.xlsx?dl=0

The value in V6 is the one that I want to display the months it will take to pay off the loan.

I use the basic loan details in cells G5,G6 and G7 and the monthly repayment in V5.

I then need to run this calculation to determine the monthly payment for year one (B12):

Code:
$G$8+(($B$16*52/12)*$B$12)+$G$9

This gives me the actual monthly payment. I then need to repeat these steps for years 2,3,4 and 5 in cells D12, F12, H12 and J12. So for example year five I would use the following formula:

Code:
$G$8+(($J$16*52/12)*$J$12)+$G$9

The formula I am using to actually get the monthly payments forecast is:

Code:
=ROUNDUP(NPER(G6/12,V5,-G5),0)

Now I understand that to calculate a basic loan works but I need the repayments to increase year on year and for the total months for the repayment to reflect this.

Please can someone suggest a way around this problem or point me in the right direction?
 
Hi joeu2004,

It is not obvious to me that payments should always increase (annually?), since the cash flow drops significantly in year 6. For some reason, you treat the annual cash flows as if they occur weekly (monthly) throughout all the previous years as well. Do they?(!)

The payments do not have to increase and they depend on if the person paying the loan has got additional money coming in. The reason I have used the example of additional payments increasing is because I can get my formula to work if they do not increase. The money that someone receives via rent is paid weekly which is why I first start at a weekly amount and then convert it to monthly.

In any case, it appears there is no mathematical relationship that describes how payments change periodically. In particular, they do not change by a constant percentage or even by a constant amount. Right?

There is no mathimatical relationship for how the payments increase. They are simply rent received by the person paying the loan from other investment properties. Therefore these additional payments will always be different and have no relation to each other.

If so, then I doubt that you can use NPER to calculate the number of payments. [1]

Instead, I would write a VBA function -- myNPER -- which takes a list of the periodic payments (et al) and effectively works through an amortization schedule to determine when the loan is paid off.

I don't know if I have time to develop such a function. But are you amenable to a VBA solution? (Someone else might provide it.)

I am open to using a VBA solution but I wanted to use the existing NPER function as this already does exactly what I need except allowing the possibility of the payment amount to increase whilst everything else stays the same.

MickG has done the calculation which allows the interest rate to increase over the lifetime of the loan using the NPER function and that is exactly what I need except for the monthly payments so I am hoping it is possible.

I appreciate all your help and advice. I have posted the same question on another Excel forum and I have tried to reword my question because of the confusion I caused here. I always like to use different forums and hopefully if I can solve this with the help of people from those forums I can also help more people that might need something similar.
 
Upvote 0

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Note the formula shown in "C8" and "A20" do not show the right cells (My mistake) the correct cells an shown in the actual formula in "B8" and "A21".

The Sub below will return the Months to complete in "B9", giving the same result as "B8"
You need to run the code in the sheet as it refers to range "E2:I2"
You could relate the values in the code to cells values if you require , or turn the whole thing into a "UDF"
Code:
[COLOR="Navy"]Sub[/COLOR] MG23Nov41
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rate [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Cap [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Ans [COLOR="Navy"]As[/COLOR] Double
Cap = 300000
Rate = 1.06 ^ (1 / 12)
[COLOR="Navy"]Set[/COLOR] Rng = Range("E2:I2")
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Ans = ((Cap * ((Rate ^ 13) - (Rate ^ 12)) - (Dn.Value * ((Rate ^ 12) - 1))) / (Rate - 1))
    Cap = Ans
[COLOR="Navy"]Next[/COLOR] Dn
Range("B9") = Application.Log10(Rng(Rng.Count) / (Rng(Rng.Count) - (Ans * (Rate - 1)))) / Application.Log10(Rate)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Have a look at this file, hopefully it may help !!!
https://app.box.com/s/xs4803xfqxmgypbzcf2l

I wasn't expecting you to provide such a detailed answer but it is amazing!

Not my understanding of the problem you wanted to solve. But if you say it is, so be it.

MickG calculates the number of monthly payments remaining after the first 60 payments based just 5 years of specified payments, using a fixed payment after the year 5, namely the 5th-year payment.

If that is what you need, that can indeed be calculated directly using NPER without all the fuss and bother that MickG goes through. To wit:


A
B
C
D
1
loan
300,000.00

2
annl int rate6.00%

3
monthly rate0.4868%
=(1+B2)^(1/12)-1
4

monthly pmtrem bal
5
1st yr2,142.00291,596.58=FV($B$3,12,B5,-B1)
6
2nd yr2,695.00275,872.38=FV($B$3,12,B6,-C5)
7
3rd yr3,374.00250,835.01=FV($B$3,12,B7,-C6)
8
4th yr3,696.00220,326.27=FV($B$3,12,B8,-C7)
9
5th yr4,026.00183,919.24=FV($B$3,12,B9,-C8)
10
rem yrs4,026.00

11




12
rem #pmts51.7936
=NPER(B3,B10,-C9)
13
actl #pmts52
=ROUNDUP(B12,0)
14
last pmt3,196.59
=FV(B3,B13-1,B10,-C9)*(1+B3)

<tbody>
</tbody>

Column D shows the formulas B3, B12:B14 and C5:C9.

Caveat.... MickG treats 6% as a compounded annual rate. That is valid in some regions for specifying the APR, which is not the same as annual interest rate. But you treated 6% as a simple annual rate when you calculated the 30-yr payment, to wit: =-PMT(G6/12,G7*12,G5). That is common in most regions, even if APR is a compounded rate.

If you want the latter, change the formula in B3 to =B2/12.

[EDIT] Of course, the separate formulas in C5:C9 and C12 can be combined into one complicated formula. Let us know if you want help with that.
 
Last edited:
Upvote 0
Also see my previous response #15.

Perhaps the VBA varNPER function below is a solution. I demonstrate it below using MickG's assumption. But you can easily adapt the inputs to the assumptions of your original problem as I understand them.


A
B
C
D
1
loan
300,000.00

2
annl int rate6.00%

3
monthly rate0.4868%
=(1+B2)^(1/12)-1
4

Monthly Pmt

5
1st yr2,142.0012
6
2nd yr2,695.0012
7
3rd yr3,374.0012
8
4th yr3,696.0012
9
5th yr4,026.0012
10
rem yrs4,026.00-1
11




12
nper
111.793589
=varnper(B3,B5:C10,-B1)

<tbody>
</tbody>

The varNPER inputs are similar to the NPER parameters. The exception: the second parameter (pmt) is a range or array of pmt specifiers (pmt and #pmt pair). If the #pmt is -1, that corresponding pmt is used for the remainder of the loan term.

Note that varNPER returns the total #pmts, not just the #pmts after some assumed number (MickG used 60). For example, if the loan is 50,000 in the example above, varNPER returns about 22.1383657208546. To demonstrate correctness (Pmt is column G; Bal is column H):


PmtBal


50,000.00
12,142.00
48,101.38=H2*(1+$B$3)-G3
22,142.0046,193.51
32,142.0044,276.36
42,142.0042,349.88
52,142.0040,414.02
62,142.0038,468.74
72,142.0036,513.99
82,142.0034,549.72
92,142.0032,575.89
102,142.0030,592.46
112,142.0028,599.37
122,142.0026,596.58
132,695.0024,031.04
142,695.0021,453.01
152,695.0018,862.43
162,695.0016,259.25
172,695.0013,643.39
182,695.0011,014.80
192,695.008,373.41
202,695.005,719.17
212,695.003,052.01
22
2,695.00371.87
232,695.00-2,321.32
actl 23373.68
=H24*(1+B3)

<tbody>
</tbody>

Rich (BB code):
Function varNPER(myRate As Double, myPmts As Variant, myPv As Double, _
    Optional myFv As Double = 0, Optional myType As Long = 0) As Variant
    
    Dim i As Long, j As Long, n As Long, p As Variant
    Dim bal As Double, remBal As Double, myNPer As Double
    
    If TypeName(myPmts) = "Range" Then
        ' myPmts must be range with n rows and 2 cols.
        ' row = signed pmt; col = #pmts (-1 = forever)
        p = myPmts
        n = UBound(p, 1)
    Else
        ' myPmts must be an array constant with n rows
        ' and 2 columns and with base index of 1.
        ' row = signed pmt; col = #pmts (-1 = forever)
        n = UBound(myPmts, 1)
        ReDim p(1 To n, 1 To 2) As Variant
        For i = 1 To n: For j = 1 To 2
            p(i, j) = myPmts(i, j)
        Next j, i
    End If
    
    ' I do not trust VBA FV and NPer functions.
    ' Empirically discovered some significant differences
    ' with WorksheetFunction results.  Latter seems to
    ' match Excel function results exactly
    bal = myPv
    myNPer = 0
    For i = 1 To n - 1
        If p(i, 2) < 0 Then Exit For
        remBal = WorksheetFunction.fv(myRate, p(i, 2), p(i, 1), bal, myType)
        If remBal < myFv Then Exit For
        bal = -remBal
        myNPer = myNPer + p(i, 2)
    Next
    varNPER = myNPer + WorksheetFunction.NPer(myRate, p(i, 1), bal, myFv, myType)
End Function
 
Last edited:
Upvote 0
Hi MickG
This formula works great if the same payment interval is used for the investment growth rate AND for the payment increase rate. But what if the investment growth rate is calculated MONTHLY and the payments increase ANNUALLY . Can your formula be adapted to calculate the starting payment where the payment increases are ANNUAL but the growth rate is calculated MONTHLY.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top