Hi,
Does anyone know if it is possible to use the NPER function if your monthly payments change and do not stay at a fixed rate for the duration of the loan?
The reason I need this is because I would like to work out how long it will take to repay a loan at the same time as allowing for the client to increase the amount they pay back each month.
At the moment I can only get an accurate forecast if I keep the monthly payments fixed.
Any help is appreciated.
Are you wanting to be able to see what their payments will be if they make an extra payment in a given month?
If so make a basic payment calendar in months showing what their payments are (Based on a minimum payment) to pay it off within agreed terms.
Allow a field to add additional payments if made so that it is subtracted from the balance, this will in the end decrease your last payment(s) and/or term length depending upon how much is added.
Made a basic sheet as an example
QuickLoanCalculator for Payments.xlsx
-If you think you are done, Start over - ELeGault
If you want to change rates and make additional payments, see if this helps:
http://pistulka.com/other
Then look for "Amortization Schedule With Variable Rates" on the right hand side. Should be first one.
This appears to be a crossposting of the discussion that you initiated in mrexcel.com/forum. See http://www.mrexcel.com/forum/excel-questions/819640-nper-function-using-repayments-increase-over-5-years.html.Originally Posted by mabbutt
You are doing yourself a disservice and wasting a lot of helpful people's time by not including the details of the variable payments that you included in the crossposted discussion; or better: simply pointing to those details in the crossposted discussion.
Unless your intent is for us to ignore those details and start over with a completely different set of assumptions. In that case, your question here is woefully inadequate insofar as you provide no information about how the monthly payments change, for example.
I notice that you say here that payments "increase ... each month", whereas they changed each year in the other discussion.
Moreover, it was not obvious to me that they should always increase, 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?
Finally, you write: "monthly payments change and do not stay at a fixed rate for the duration of the loan". That seems to be ambiguous. Some people might (mis?)interpret "not stay at a fixed rate" to refer to the interest rate. But in the other discussion, the interest rate does not change. I suspect both phrases refer to payments, and "change and do not stay" is simply redundant. Right?
In any case, based on the other discussion, it appears that 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.
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.)
PS: That question/answer really belongs in the other discussion. I will not participate further in this discussion unless you explain that this really is a different question with a difference set of assumptions, and you provide the necessary details.
Hi joeu2004,
Yes I have posted this question in a different forum and I always use different forums because that way it increases my chance of help. It also allows more people to see a problem solved with the help of the people in those forums. I am sorry that you feel I have been wasting peoples time as that is the last thing I would want to do.
The reason I worded my question differently is because in the other thread on a different forum my question caused quite a bit of confusion.
From the responses above I would say that this has been successful as they seem to know exactly what I am after.
It was my fault in the other forum that there was confusion as i worded my question poorly. However I have still received a lot of good advice and a poster has helped me a considerable amount for which I am very grateful.
To try and answer your other points 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.
There is no mathematical 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.
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.
Once again I am sorry that you feel posting on different forums and the way in which I have asked the question has contributed to possibly wasting peoples time. That was and never would be my intention. I also appreciate your advice and hopefully in the future if I post on different forums I can provide a link to that post.
Hi pistulka,
Thank you for suggesting this. I have looked at it and it is very close to what I need. I simply need to work out how to use the formulas to allow the monthly payments to change instead of the interest rate.
In case people are interested in a possible way to do this a poster called MickG on the other forum I have posted this question has said that he could have a possible solution. If that is the case I will share his answer here or you can view the thread using the following link as stated by joeu2004:
http://www.mrexcel.com/forum/excel-q...r-5-years.html
Thank you also to everyone else above that has responded and I again apologise if anyone feels I acted inappropriately by posting on different forums and worded my question differently.
My bad! I have no problem with your posting on multiple forums. (Other people do, but not I.) But IMHO, you did not post sufficient information here to get a solution, based on my (mis?)understanding of the information that you provided in the other discussion. I don't think your explanations were confusing in the other thread; simply misunderstood. But if you think you are getting meaningful responses here notwithstanding the dearth of details, so be it.
I did keep my explanation to a minimum here as I didn't want to be confusing again.
I appreciate your feedback and will alter my approach next time as I think you are right that duplicate posts with different angles can be very confusing and not help get a productive answer. I had not looked at it this way.
Do you think it is possible to amend the NPER function in a similar way that MickG did for the interest rate in the other thread but for the actual payments whilst keeping the interest rate the same?
http://www.mrexcel.com/forum/excel-q...ml#post4001175
No, as I said in my first response in this discussion.
First, I do not see where MickG uses the NPER function. Instead, he provides a mathematical formula, to wit: =(A4*(($B$4^(D4+1))-($C$4*B4^D4))/((B4^D4)-(C4^D4))).
Moreover, as MickG explains later, that does not compute a number of payments, as NPER does. Instead, it computes the initial payment which, for example, when increased by 2%/12 per month, reduces a loan of 1000 at 8%/12 interest per month to zero in 60 months. (Actually, a little more.)
Second, your payments do not increase at a constant rate (like 2%/12 per month). So IMHO, there is no mathematical formula, much less NPER usage, that can account for the "random" change in payment.
Instead, as I said before, I believe you must construct an amortization schedule and deduce the number of periods (like NPER) empirically. I believe you already have an Excel model for doing exactly that. The only improvement might be to implement the model in VBA, as I said before.
I really cannot help you. So I suggest that you continue to work with people whom you think are helpful. Good luck!
OK no problem.
I will look into the possibility of a VBA solution using a amortisation schedule as you suggest.
If I successfully achieve this or find a different solution I'll post it here in the hope that it helps someone else.
This does exactly what I need and it also more importantly offers a detailed explanation:
https://app.box.com/s/xs4803xfqxmgypbzcf2l
I hope someone else also finds it useful.
Thank you for all the people that helped me!
http://www.mrexcel.com/forum/excel-q...ml#post4002434
Last edited by mabbutt; 11-23-2014 at 07:58 AM.
Not according to my understanding of the problem as stated by example in your original posting in the other forum. But if you say it is, so be it.
MickG calculates the number of monthly payments remaining after the first 60 payments based on just 5 years of specified payments, using a fixed payment after 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 rate 6.00%
3
monthly rate 0.4868%
=(1+B2)^(1/12)-1 4
monthly pmt rem bal
5
1st yr 2,142.00 291,596.58 =FV($B$3,12,B5,-B1) 6
2nd yr 2,695.00 275,872.38 =FV($B$3,12,B6,-C5) 7
3rd yr 3,374.00 250,835.01 =FV($B$3,12,B7,-C6) 8
4th yr 3,696.00 220,326.27 =FV($B$3,12,B8,-C7) 9
5th yr 4,026.00 183,919.24 =FV($B$3,12,B9,-C8) 10
rem yrs 4,026.00
11
12
rem #pmts 51.7936
=NPER(B3,B10,-C9) 13
actl #pmts 52
=ROUNDUP(B12,0) 14
last pmt 3,196.59
=FV(B3,B13-1,B10,-C9)*(1+B3)
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.
Of course, the separate formulas in C5:C9 and C12:C13 can be combined into one complicated formula, to wit:
Please Login or Register to view this content.
Last edited by joeu2004; 11-23-2014 at 04:31 PM.
Also see my previous response #13.
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 rate 6.00%
3
monthly rate 0.4868%
=(1+B2)^(1/12)-1 4
Monthly Pmt
5
1st yr 2,142.00 12
6
2nd yr 2,695.00 12
7
3rd yr 3,374.00 12
8
4th yr 3,696.00 12
9
5th yr 4,026.00 12
10
rem yrs 4,026.00 -1
11
12
nper
111.793589
=varnper(B3,B5:C10,-B1)
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 #pmt is -1, that corresponding pmt is used for the remainder of the loan term.
[EDIT] With MickG's assumptions, the range B5:C9 would have sufficed. A last-forever payment (#pmt = -1) is needed only when it is different from the previous payment.
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):
Pmt Bal
50,000.00
1 2,142.00
48,101.38 =H2*(1+$B$3)-G3 2 2,142.00 46,193.51
3 2,142.00 44,276.36
4 2,142.00 42,349.88
5 2,142.00 40,414.02
6 2,142.00 38,468.74
7 2,142.00 36,513.99
8 2,142.00 34,549.72
9 2,142.00 32,575.89
10 2,142.00 30,592.46
11 2,142.00 28,599.37
12 2,142.00 26,596.58
13 2,695.00 24,031.04
14 2,695.00 21,453.01
15 2,695.00 18,862.43
16 2,695.00 16,259.25
17 2,695.00 13,643.39
18 2,695.00 11,014.80
19 2,695.00 8,373.41
20 2,695.00 5,719.17
21 2,695.00 3,052.01
22
2,695.00 371.87
23 2,695.00 -2,321.32
actl 23 373.68
=H24*(1+B3)
Please Login or Register to view this content.
Last edited by joeu2004; 11-23-2014 at 05:42 PM. Reason: simplified varNPER
Hi joeu2004,
Wow that was an answer to beat all answers.
Thank you so much for taking the time to put this together and explaining it so simply.
I really am grateful and will work my way through this and hopefully finish with a perfect answer!
This is why I love these forums and the knowledge that I have gained is staggering!
Thank you!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks