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
...
No comments:
Post a Comment