Outgoing payments should be represented by negative numbers.
NPER function is an Excel financial function, used to calculate the number of periods needed to pay off a debt, or reach an investment goal.
Formula:
log(
| ||
| log( 1 + R ) |
Where:
R = Annual Interest Rate
FV = Future Value
PV = Present Value
PMT = The payment made each period
Type = Payments due, if the payment is due at the end of each period, the type is equal to zero, if payment the due at the start of each period, the type will be one.
NPER Default type in excel is 0
For more about PMT function, formula, and PMT calculator click here.
How to use NPER Function
Excel NPER function formula is NPER(rate,pmt,pv,[fv],[type]).
To calculate the NPER function online, simply enter the interest rate (rate), payment per period (PMT), present value (PV), future value (FV) is optional, and the payments due will be at the end of each period by default, you can change it to the beginning of each period (type).
Make attention to the outgoing payments should be represented by negative numbers, same as the excel function.
Example:
When the present value (PV) should be as a negative number.
Assuming we have a loan balance of $50,000 at a 5% interest rate, and with a monthly payment of $500, calculate the period required to repay the loan.
We should enter the Loan amount (-50000) in the input of present value (PV) as a negative number as it represents an outgoing payment, then the interest rate (5), and the monthly payment as 500, the calculator will automatically divide the percentage by 12 (because monthly payment) you don't need to do that like on Excel. finally, the result will appear that we need 129.63 months to repay the loan.
Another Example:
When the monthly payment (pmt) should be as a negative number.
An investor invests $10,000 with an interest rate of 5% and additional monthly contributions of $5,000 with a goal to reach $500,000. calculate how long it will take to achieve his goal.
Enter the investment amount (10000) in the present value input, the goal to reach (500000) in the future value input, the interest rate (5), and finally the additional monthly contributions in the PMT input as a negative number (-5000).
NPER = 85.48
The investor needs 85.48 months to achieve his goal.