Calculating Loan Payoff Periods with Excel NPER Function

[IrinaK.]

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!

 

[Mark44]

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.

 

[IrinaK.]

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?

 

[Ray Vickson]

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.

 

[IrinaK.]

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

 

[Ray Vickson]

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