Careful planning is essential to help you meet your financial goals.
This is where Excel’s NPER function comes into play.
The function considers different variables like interest rates and payment amounts to give you the financial forecast you need.
What Is the NPER Function in Excel?
NPER is one ofExcel’s financial functions, designed to assist you in making precise financial calculations.
NPER stands for the number of periods, referring to the payment periods required to reach a financial goal.
The function assumes consistent payments at a steady interest rate.
The value you put in FV depends on context, which we’ll cover briefly.
To use this function correctly, you must grasp two essential concepts: currency flow and payment intervals.
The important note here is that these values aren’t all positive.
In contrast, the savings account’s future value (FV) is revenue.
You lose PMT and PV from your pockets so that you gain FV in the savings account.
Therefore, you should input PMT and PV as negative values.
The opposite is true for loans.
For a loan, the PMT is positive, the PV is negative, and the FV is zero.
Imagine a bank account with the loan amount as its present value.
Here, you owe the bank and must pay off the loan amounts with interest.
Therefore, the PMT values should be positive.
The Payment Intervals
Another essential factor to consider when using NPER is the payment interval.
There are two payment intervals to consider here: the interest period and the payment period.
This is usually each month but can be every year as well.
The NPER function’s output is relative to the payment period in PMT.
If the value you put in PMT is paid monthly, then the NPER output is in months.
If the value you put is yearly, the output is in years.
The payment period goes hand in hand with what we discussed for the interest period.
So, if the interest is applied monthly, you must input the monthly payment value as PMT.
The NPER result won’t be accurate otherwise.
Let’s take a look at two common examples.
Use NPER to Calculate Savings
Suppose you want to create a savings account for a vacation.
So far, you’ve got the FV ($1,000) and the rate (12%).
Use NPER to Calculate Loans
Excel’s NPER function is equally applicable when dealing with loans.
You want to keep your monthly payments at $300.
FV is left blank, so the formula correctly assumes it’s zero.
NPER outputs 82.94, which means you’ll take approximately 83 months to pay off the loan.
The adjacent cell divides this by 12 and states it’ll take seven years to pay off this loan.
