You have taken a loan and you know your EMI capability. So, you want to know how many months will be taken to pay off a loan completely.
It is fairly easy job to do it in Excel. You will need to use NPER function for this.
Excel defines NPER as "Returns the number of periods for an investment based on periodic, constant payments and a constant interest rate."
Syntax of NPER is NPER(rate,pmt,pv,[fv],[type]).
rate: You rate of interest
pmt: EMI (Payment per period). You will need to put -ve value of this in your formula.
Your pmt and rate should be on the same scale. i.e if you are planning to pay up monthly, the rate in your formula should be monthly only. Generally, interest rate is specified yearly i.e. 10.5% per year. This you should divide by 12 to arrive at monthly rate. Hence, if you wanted 3 years loan, it means nper would 3×12=36 months.
If it is quarterly, rate = 10.5%/4 = 2.625% and nper would be 3×4 = 12
If it is annual, rate = 10.5% and nper = 3
pv: Your loan amount. You will need to put +ve value of this in your formula.
Note – Either PMT or PV should be -ve. Both can't be +ve and -ve at the same time.
+ve / -ve requires some explanation and this can not be ignored. It depends upon your cashflow. If you are taking a loan, hence cash in, hence pv is +ve. But every month, you will have to pay up something, hence cash out. Hence, PMT is -ve. If you are investing, hence cash out. Hence pv is -ve. But every month, you will be receiving something, hence cash in. Hence, PMT is +ve.
fv: Your remaining value after you finish your installment. Generally, it is 0 as any lender will like to recover its money fill. (Default is 0)
type: 0 – At the end of the period, 1 – At the beginning of the period (Default is 0)
Also note, fv and type are optional and may not be required in your formula.
The formula used in the below picture is =NPER(B1/12,-B2,B3)
The below EMI Calculator can be download from here NPER Calculator
