Calculating Loan Payoff Periods with Excel NPER Function

  • Thread starter Thread starter IrinaK.
  • Start date Start date
  • Tags Tags
    Log
IrinaK.
Messages
33
Reaction score
0

Homework Statement


Hello!

There is a function in Excel NPER which gives the number of periods/years.

Here is the data:
Present Value = 7 500
Annual rate 18%
Number of periods per year 12
Period rate 0.18/12=0.015
Monthly payment -125

Task to find number of years to pay-off loan with minimum PMT of 125

Homework Equations


When I use Excel function, I get total number of periods during all years (based on period rate of 0.015) 154.65.
Divide this by 12 and get the number of years 12.88.

But when I do this manually, I can't get the right answer. Please, see my attempt below and correct the math.

The Attempt at a Solution


N = number of all periods during all years

125 x ((1 + 18%/12)^(12*N)) = 7500
(1 + 18%/12)^N = 60

1.015^N = 60

log(60) / log(1.015) = 278 periods
number of years = 278 / 12

Thank you!
 
Physics news on Phys.org
IrinaK. said:

Homework Statement


Hello!

There is a function in Excel NPER which gives the number of periods/years.

Here is the data:
Present Value = 7 500
Annual rate 18%
Number of periods per year 12
Period rate 0.18/12=0.015
Monthly payment -125

Task to find number of years to pay-off loan with minimum PMT of 125

Homework Equations


When I use Excel function, I get total number of periods during all years (based on period rate of 0.015) 154.65.
Divide this by 12 and get the number of years 12.88.

But when I do this manually, I can't get the right answer. Please, see my attempt below and correct the math.

The Attempt at a Solution


N = number of all periods during all years

125 x ((1 + 18%/12)^(12*N)) = 7500
(1 + 18%/12)^N = 60

1.015^N = 60

log(60) / log(1.015) = 278 periods
number of years = 278 / 12

Thank you!
The two formulas aren't doing the same thing. Your first formula is a type of declining annuity formula. The second formula is for compound interest, in which $125 is invested and allowed to grow over a period of years.

BTW, the first equation you have in your attempt is wrong:
125 x ((1 + 18%/12)^(12*N)) = 7500
The exponent should be N, not 12*N. You corrected this in the equation just after this one.
 
Mark44 said:
The two formulas aren't doing the same thing. Your first formula is a type of declining annuity formula. The second formula is for compound interest, in which $125 is invested and allowed to grow over a period of years.

BTW, the first equation you have in your attempt is wrong:

The exponent should be N, not 12*N. You corrected this in the equation just after this one.

Thank you very much for your help. Could you, please, suggest which formula would be a correct one for solving this problem manually?
 
IrinaK. said:
Thank you very much for your help. Could you, please, suggest which formula would be a correct one for solving this problem manually?

You are not yet ready to apply formulas. First, you need to understand the process; only then should you look for shortcuts via formulas. That way, you will not try to use the wrong formulas, which is what you have already done.

To start: reason out the first few months: at time 0 we owe P0 = 7500. At a monthly interest rate of r = 0.015, we owe 7500*(1.015) = 7612.5 just before the first payment; after paying the monthly payment of m = 125, we owe P1 = 7612.5 - 125 = 7487.5, So at the end of the first month (after the first payment) we owe P1 = 7487.5. Now get the amount P2 owing at month 2 (after the next monthly payment) by applying the same operations on P1 instead of P0. Then do the same on P2 to get P3, etc.

You could actually keep going like that and make a table of the Pk values, up until the Pk goes from positive to negative. The answer would then be somewhere between those two time points. In fact, we would have P154 > 0 and P155 < 0, so the answer would be between 154 and 155. (In this problem, the Math and the Finance do not gibe perfectly, because the answer involves a fractional number of months---so something special must take place just before the loan is paid off.)

Anyway, if you translate the manipulations above into symbolic form, you will be able---eventually--to get a formula for Pn = amount owing after n payments. That would be what you seek.
 
Last edited:
Ray Vickson said:
You are not yet ready to apply formulas. First, you need to understand the process; only then should you look for shortcuts via formulas. That way, you will not try to use the wrong formulas, which is what you have already done.

To start: reason out the first few months: at time 0 we owe P0 = 7500. At a monthly interest rate of r = 0.015, we owe 7500*(1.015) = 7612.5 just before the first payment; after paying the monthly payment of m = 125, we owe P1 = 7612.5 - 125 = 7487.5, So at the end of the first month (after the first payment) we owe P1 = 7487.5. Now get the amount P2 owing at month 2 (after the next monthly payment) by applying the same operations on P1 instead of P0. Then do the same on P2 to get P3, etc.

You could actually keep going like that and make a table of the Pk values, up until the Pk goes from positive to negative. The answer would then be somewhere between those two time points. In fact, we would have P154 > 0 and P155 < 0, so the answer would be between 154 and 155. (In this problem, the Math and the Finance do not gibe perfectly, because the answer involves a fractional number of months---so something special must take place just before the loan is paid off.)

Anyway, if you translate the manipulations above into symbolic form, you will be able---eventually--to get a formula for Pn = amount owing after n payments. That would be what you seek.
Ray, thank you very much for your help. The only thing: I do understand the process very very well (and have tons of experience, and I good at constructing excel annuity tables), the only issue I am struggling with sometimes is math. So, I am doing my best to revive my basic math as well as math analysis, calculus, etc. Hard to do on one's own, but I do have a hope )))
 
IrinaK. said:
Ray, thank you very much for your help. The only thing: I do understand the process very very well (and have tons of experience, and I good at constructing excel annuity tables), the only issue I am struggling with sometimes is math. So, I am doing my best to revive my basic math as well as math analysis, calculus, etc. Hard to do on one's own, but I do have a hope )))

Well, I did try to guide you toward finding the correct expressions. A good start would be for you to follow the steps I outlined. However, instead of using numbers it would be better to use symbols such as P instead of 7500, r instead of 0.015, m instead of 125. So, if P1 is the amount still owed just after the first payment, we have P1 = (1+r)*P - m. Then, I suggested you find the amount P2 still owing immediately after the second payment, then P3 owing immediately after the third payment, etc. Have you done that? Please be assured that I made those suggestions because I judged that if you carried them out it would help you understand/learn the material.

BTW: if you do find P2, P3, .., I would suggest you expand them out, separating the parts that have a "P" in them from the parts containing "m"----that is why symbols, instead of numbers, are important in this exercise: they allow you to keep track of separate effects, which is important when you want to finally summarize everything using a "formula".
 
There are two things I don't understand about this problem. First, when finding the nth root of a number, there should in theory be n solutions. However, the formula produces n+1 roots. Here is how. The first root is simply ##\left(r\right)^{\left(\frac{1}{n}\right)}##. Then you multiply this first root by n additional expressions given by the formula, as you go through k=0,1,...n-1. So you end up with n+1 roots, which cannot be correct. Let me illustrate what I mean. For this...
Back
Top