NPER Function

In last article we have discussed about PMT Function to get the amount of the payment or premium or EMI per month.

But suppose we have the premium amount and other information but not the period (number of months) and we need to calculate the number of months then we need to use another formula to get the period. Microsoft Excel provides an excel function NPER Function which will help us to get the 'period'.

 Today in this article we will discuss about NPER function.

Suppose we have the following information, like Loan Amount, Interest Rate, Installment Amount and Compounding period per year and we need to derive the number of months from these information to replay the full amount with interest. In this situation we will use NPER Function.

So, let's see how to calculate the period (number of months) to repay the amount.

For Example let suppose we have the details with us,

1.   Loan Amount (suppose $7000)

2.   Interest Rate per annum (let it be 7.50%)

3.   Installment Amount ($150)

4.   Compounding period per year (suppose every month i.e., 12)

What does Compounding Periods Per Year mean?

The compounding period is the number of times that unpaid amount interest is added to the principal amount of the loan.

 In simpler words, it is the number of times a bank or lender calculates the due amount.

If  'n' be the Compounding Periods Per Year the

if interest is compounded yearly, then n = 1;

if semi-annually, then n = 2;

quarterly, then n = 4;

monthly, then n = 12;

weekly, then n = 52;

daily, then n = 365; and so forth, regardless of the number of years involved.

I think you got a clear idea about Compounding Periods Per Year, now we will move to the NPER Function.

Syntax

The syntax for the NPER function in Microsoft Excel is very simple,

=NPER( interest_rate, payment, PV, [FV], [Type] )

Parameters or Arguments

interest_rate

The interest rate for the loan.

payments

The amount of the payment for each period. This should be entered as a negative value.

PV

The present value or principal of the loan.

FV

It is optional. It is the future value or the loan amount outstanding after all payments have been made. If this parameter is omitted, it assumes a FV value of 0.

Type

It is also optional. It indicates when the payments are due. If the Type parameter is omitted, it assumes a Type value of 0. Type can be one of the following values:

0 = Payments are due at the end of the period. (default)

1 = Payments are due at the beginning of the period.

Example :

Let us calculate the amount payable in every month based on the above information.

The formula will be =NPER(C5/C7,-C6,C4).

One question may arise in your mind why we divide the interest rate by compounding periods per year ?

This is because we have taken the interest rate as per year and the lender calculates the loan payment amount per month. We need to break down the full year interest rate by the Compounding Periods per year.

The below image will clarify NPER function.

How to use NPER Function in Excel

Hope you have enjoyed ...

Keep reading & Stay blessed ...