NPER Function using repayments that increase over 5 years

[mabbutt]

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

$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:

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

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

=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?

 

[MickG]

This is an example of a loan for £1000 @ rate 8% over 60 Months with the Repayments increasing every Month by 2%

Principle	Loan Rate +1	Rep'nt Inc Rate	Term mths	Rep'nt
	8%	2%
	=(1+8%)^(1/12)	=(1+2%)^(1/12)
1000	1.00643403	1.001651581	60	19.23898361
Repayment =	19.23898361	=(A4*(($B$4^(D4+1))-($C$4*B4^D4))/((B4^D4)-(C4^D4)))

<COLGROUP><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3754" width=106><COL style="WIDTH: 100pt; mso-width-source: userset; mso-width-alt: 4721" width=133><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4579" width=129><COL style="WIDTH: 64pt; mso-width-source: userset; mso-width-alt: 3043" width=86><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4323" width=122><TBODY>
</TBODY>

 

[mabbutt]

Hi MickG,

Thank you for replying and taking the time to explain your suggestion. I can make my data work with basic data using your example but I am unable to make it work with my actual data. I have condensed my data down and tried to make it easier to see below:

	A	B
1	Loan Amount	300000
2	Annual Interest Rate	6%
3	Term of Loan (in Years)	30
4	Monthly Payments	1798.65
5	Extra Monthly Payments	100
6	Year 1	2142
7	Year 2	2695
8	Year 3	3374
9	Year 4	3696
10	Year 5	4026
11	Number of months to repay loan	?

<tbody>
</tbody>

With the above data my loan would be paid off in 132 months but my formula is returning 242. Now I know this is because I am not allowing it to account for the extra payments being added in years 1 to 5 (B6:B10).

I have tried to get it to work using only the extra data for year one so that it is easier to understand at first using the following data with your example:

=(B1*(($B$2^(B3+1))-($B$6*B2^B3))/((B2^B3)-(B6^B3)))

This isn't working though. Can you explain to me using the data in the table above how I increase the monthly payments using this principle:

=ROUNDUP(NPER(G6/12,[B]?????[/B],-G5),0)

Where the ????? is I want to add my calculation. So year one would be B4+B5+B6, year two would be B4+B5+B6+B7, year three would be B4+B5+B6+B7+B8 and so on. So the final result would be an accurate monthly output for the duration of the loan which would include the extra payments being made year year over five years.

I hope that makes sense and I appreciate your help already!

Thank you

 

[MickG]

I'm sorry but I don't really understand what you are doing:-
The equation I sent you was to find a single monthly repayment using a monthly Interest rate and a monthly Percentage increase in the repayment amounts over a fixed period. The monthly rates being both based on Annual rates.
In your reply you seem show this equation relating to your column "B" data.This does not show the monthly equivalent Interest rate, and you seem to be using a Fixed amounts of money, rather than a Percentage increase rate for the increasing repayments.
I'm not sure where you get the figures in lines 6 to 10, please explain

Your New requiremts:-
My understanding is that you now want to base the repayments on having an monthly increasing repayment over the first 5 year of £100, then a fixed repayment over the remaining 25 years. Using a base repayment value of 1789.65
To do this I think you need to have 2 equations, The first being a modified version of my equation to take account of the increasing repayments and the fact that they are a fixed amout, and then using the PMT Function for the Remaining Capital over the remaining years.
Please confirm !!
Regrds Mick

 

[mabbutt]

Hi MickG,

I'm sorry that I have not been clear and that I have confused you with what I am after. I really do appreciate you helping me.

I will try and explain what I am after again and using the table below.

	A	B
1	Loan Amount	300000	- Loan to pay off
2	Annual Interest Rate	6%	- Fixed interest rate
3	Term of Loan (in Years)	30	- Standard loan term
4	Monthly Payments	1798.65	- Fixed monthly payment
5	Extra Monthly Payments	100	- Extra fixed payment
6	Year 1	2142	- Extra payment for 1st year
7	Year 2	2695	- Extra payment for 2nd year
8	Year 3	3374	- Extra payment for 3rd year
9	Year 4	3696	- Extra payment for 4th year
10	Year 5	4026	- Extra payment for 5th year
11	Number of months to repay loan	?	- Result from formula

<tbody>
</tbody>

I need to find the amount of months that it will take to pay off my loan of £300,000.

To get the predicted months I use this formula -

=ROUNDUP(NPER(B2/12,B4,-B1),0)

This returns the correct result but it is based on a single payment not changing over the duration of the loan.

What I need is a formula that can workout the months it will take to pay off the loan when the monthly payments change at the end of the first five years. The monthly percentage does not change and will always be 6%.

So in year one the interest rate will be 6% (this will not change for the duration of the loan) and the amount that can be paid every month for 12 months will be £4,040.65 (B4+B5+B6).

In year two starting at the 13th month for the entire loan the amount that can be paid every month for the next 12 months will be £6,735.65 (B4+B5+B6+B7).

In year three starting at the 25th month for the entire loan the amount that can be paid every month for the next 12 months will be £10,109.65 (B4+B5+B6+B7+B8).

In year four starting at the 37th month for the entire loan the amount that can be paid every month for the next 12 months will be £13,805.65 (B4+B5+B6+B7+B8+B9).

In year five starting at the 49th month for the entire loan the amount that can be paid every month for the next 12 months will be £17,831.65 (B4+B5+B6+B7+B8+B9+B10).

This is the reason that I cannot use the original formula as it only calculates correctly if the monthly payments do not change.

My monthly payments increase after 12 months for the first five years and therefore the months that it will take to pay off the loan is greatly reduced.

I hope that makes it clearer and I really am sorry for making it confusing. I simply need a formula that gives me the expected months it takes to pay off the loan with a fixed interest rate, loan amount and monthly payments that increase for the first five years. After year 5 the payments stay the same as in year five.

Thank you so much for your help!

 

[MickG]

??

 

[mabbutt]

Hi MickG,

i'm guessing that my last post didn't make it any clearer!

I just need a way to allow the NPER function to accept a monthly payment that can change instead of it being fixed for the entire length of the repayments.

The same as you did with the interest rate but with the amount being paid each month.

 

[MickG]

No !!, I actually had a answer but I realized I was using the wrong figure when finding the capital remaining from the first 5 years.
The amounts to be deducted in the first 5 years from your data, over the 60 months seem to add up to a figure greater than the loan ???
These figures to be deducted appear to be arbitrary ???
If these figures are arbitrary, then you need the first equation to be an actual calculation containing those figures.
The 2nd equation for finding the Extra loan Period, I already have.

 

[mabbutt]

Hi MickG,

I have been looking at this for so long that I have made a mistake in my reply to you, I am really sorry about that and you are right the values are more than the loan.

However the calculation that I require is still the same.

The totals that are being deducted are not arbitrary and are worked out using a different formula based on rent being paid in each month for separate investment properties. For that I use this formula

=SUM($G$8+(($B$16*52/12)*$B$12)+$G$9)

which is on my actual spreadsheet and not from the example I gave earlier in this thread.

That gives me the total that can be paid off each month for the loan in that year (this is where I made a big mistake yesterday in my reply to you).

So my first calculation finds the monthly amounts for the first five years which each are £2142, £2695, £3374, £3696 and £4026.

The remaining years will use month five which is £4026.

I then need the second equation for finding the actual term in months which will be less then 30 years as there is more being paid off. This is what I cannot do as I cannot get the equation to accept increased payments.

I really am sorry for making a mistake with my calculations and thank you for being so patient!

 

[joeu2004]

I have been looking at this for so long that I have made a mistake in my reply to you [....] The totals that are being deducted are not arbitrary and are worked out using a different formula based on rent being paid in each month for separate investment properties.

You seem to have crossposted the same(?) question to excelforum.com. See http://www.excelforum.com/excel-formulas-and-functions/1051282-variable-payment-rate-for-the-nper-function.html.

But as I note in that discussion, it is unclear whether it is intended to be the same question or merely a similar question with a different set of assumptions. The assumptions are not clearly stated in the other discussion.

I'll have to see how your follow-up above affects my (mis?)understanding of the problem as you explain it here. But the following are some edited comments that I posted in the other discussion, which might still be relevant here.

=ROUNDUP(NPER(G6/12,?????,-G5),0)
Where the ????? is I want to add my calculation. So year one would be B4+B5+B6, year two would be B4+B5+B6+B7, year three would be B4+B5+B6+B7+B8 and so on.

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?(!)

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?

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


------
[1] For my edification, I'm working on a theory that involves discounting the payment changes to present value (loan origination date). But don't hold your breath! I doubt I will have time to work through the math (but perhaps someone else will run with the idea); and I have little confidence that it will work anyway.