When planning your finances, you often know exactly how much you owe on a loan and exactly how much you can afford to pay each month. The missing variable is time: exactly how many months will it take to clear the debt? Microsoft Excel features a built-in financial function specifically designed to answer this question. The NPER (Number of Periods) function instantly calculates the total number of payment periods required to pay off a loan or reach a specific investment target based on constant payments and a constant interest rate.
Understanding the NPER Syntax
The NPER function requires three mandatory arguments and allows for two optional arguments:
=NPER(rate, pmt, pv, [fv], [type])
- rate: The interest rate per period. Crucially, if you are making monthly payments on an annual interest rate, you must divide the annual rate by 12.
- pmt: The payment made each period. Because this is cash leaving your pocket, this number must be negative.
- pv: The present value, or the total current amount of the loan.
- [fv] (Optional): The future value. For a loan you intend to pay off completely, this is 0.
- [type] (Optional): Enter 0 if payments are made at the end of the period, or 1 for the beginning. (Defaults to 0).
How to Calculate Loan Repayment Time
Imagine you have a £10,000 car loan with an annual interest rate of 5%. You want to know how many months it will take to pay off if you commit to paying £250 every month.
- Open a new Excel spreadsheet.
- In cell A1, type your Annual Interest Rate: 5%.
- In cell A2, type your Monthly Payment: -250.
- In cell A3, type the Loan Amount: 10000.
- Click on the cell where you want the result to appear (e.g., A5).
- Type the following formula:
=NPER(A1/12, A2, A3)
- Press Enter.
The result will display as approximately 44.15. This means it will take you 45 months (since you cannot have a fraction of a monthly payment) to pay off the car loan in full.
Using NPER for Investment Goals
The NPER function is equally effective for calculating how long it will take to reach a savings goal. In this scenario, the Present Value (the starting amount in your bank) is negative, your monthly contributions (PMT) are negative, and your Future Value (your goal) is a positive number.
If you have £5,000 saved, contribute £300 a month, earn a 4% annual return, and want to reach £20,000, the formula is:
=NPER(4%/12, -300, -5000, 20000)
Press Enter, and Excel will calculate that it will take approximately 42.7 months to hit your investment target.