Variable payment rate for the NPER function

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.

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.

Originally Posted by pistulka

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.

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.

Originally Posted by mabbutt

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.

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.

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.

Originally Posted by mabbutt

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.
[....]
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.

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.

Originally Posted by joeu2004

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

Originally Posted by mabbutt

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?

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!