How to use the NPER function in Excel to plan loans and savings

How to use the NPER function in Excel to plan loans and savings Picture 1

Careful planning is necessary to meet your financial goals. From savings to loans, it's difficult to time your goals. This is where Excel's NPER function comes in handy. This function looks at different variables like interest rate, payment amount to give you the necessary financial predictions. Now let's dive into the fundamentals and intricacies of the NPER function. They can help you make wise financial decisions.

What is the NPER function in Excel?

NPER is one of Excel's financial functions, designed to help you create accurate financial calculations. NPER is the acronym for the number of periods or payment periods required to achieve a financial goal. This function assumes consistent payments at a stable interest rate.

The NPER function calculates a few parameters and its syntax is as follows:

=NPER(RATE, PMT, PV, [FV], [type])

1. RATE : Interest rate for each period in percentage.
2. PMT : Continuous payments are made from time to time.
3. PV  (Present Value): Initial or current investment or loan.
4. FV (Future Value): Desired target value of an investment or loan. This is a selection parameter.
5. Type : Indicates payments at the beginning (1) or end (0) of each period. This is a selection parameter.

Leaving the FV and type parameters blank will set them to the default value of 0. Since most banks calculate the interest rate at the end of each period, the type parameter is always empty. The value you put in FV depends on the context.

In addition to type, each parameter in the NPER function has its own function, which can help you solve practical financial problems in Excel.

What is the NPER function in Excel?

Essentially, the NPER function helps you determine the number of payments needed to reach a specific financial goal. This could relate to any situation, from saving for a holiday, paying off a mortgage or planning for retirement.

To use this function correctly, you must understand two basic concepts: cash flow and payment period.

Cash flow

The NPER function has 3 monetary parameters: Payments you make at each period, present value, and future value. The important note here is that not all of these values ​​are positive.

How to use the NPER function in Excel to plan loans and savings Picture 2

In a savings account, the payments you make in stages (PMT) and the total initial payment are expenses. In contrast, the future value of a savings account (FV) is the revenue. You lose PMT and PV from your wallet to get FV in your savings account. Therefore, you should enter PMT and PV as negative values.

How to use the NPER function in Excel to plan loans and savings Picture 3

The opposite is true for loans. For a loan, PMT is positive, PV is negative, and FV is 0. Imagine a bank account with the loan amount as its present value. Here, you owe the bank and have to pay back the loan amount with interest. You make monthly payments into your bank account, and you stop paying when the amount you owe reaches zero.

The payment you make each month (PMT) is the amount you contribute to the negative PV until it reaches 0. Therefore, PMT values ​​need to be positive. If the loan received from the bank (PV) is not returned, it will continue to increase, resulting in a larger negative number.

Payment range

Another fundamental factor to consider when using NPER is the payment period. In a loan and savings account, one party will receive more than they gave at the expense of the other party. There are two payment periods to consider here: the interest period and the payment period.

The interest period refers to the period of time during which the bank applies interest to your loan or pays interest on your savings. This is usually every month but can also be every year. If the bank says they will give you 12% interest on your savings and they compound this interest once a year, then at the end of the year you will receive an additional 12% of your initial deposit. .

However, if they say it's 12% annual interest and they add a little more each month, they won't just divide the 12% by 12 months and give you 1% each month. When the bank adds a little interest each month, that interest also begins to earn its own interest (this is called compound interest). So, by the end of the year, you earn a little more than 12%, specifically 12.68%.

While the difference may not seem significant, it can make a big difference with a higher principal amount over a longer period of time.

How to use the NPER function in Excel to plan loans and savings Picture 4

Repayment period refers to how often you pay back the borrowed money or put more money into your savings. The result of the NPER function is related to the settlement period in PMT. If the value you place in PMT is paid monthly, the results are calculated in months. If the value you set is annual, the result will be in years.

The payment period goes hand in hand with what we discussed about the interest period. The frequency you choose for your payment term should match the frequency of the interest rate you are dealing with. Therefore, if the interest rate is applied monthly, you must enter the monthly payment value as PMT. Otherwise the results from the NPER function in Excel will be inaccurate.

Above are the things you need to know about the NPER function in Excel . Hope her writing is useful to you.

Kareem Winters

Update 03 November 2023