# How to Calculate Number of Payments on A Loan In Excel Using NPER

Microsoft Excel plays a key role in our daily finance and accounting task. That is why; it is true to say that it is the best supporting tool for an accountant and finance professional. People often takes loan from banks and financial institutions, over which they have to pay certain amount of interest. However, they are required to pay a fixed amount every month. For a layman, it is difficult to understand that the fixed amount he or she pays also included an amount of interest payment. Apart from this, he or she wants to know that how many payments he or she has to make. So, in this situation, the task is not an easy and requires lot of calculation. But, thanks to the Excel built-in financial function NPER which can be used to determine the number of payments without any mistake.

Formula

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

Example

Mr. Zee has obtained a loan of \$ 100,000 from a local bank which carries an interest rate of 5 % per annum compounded monthly. Mr Zee has to pay \$ 10,000 each month to dispose of  this loan. Calculate number of payments, he has to made using NPER function in Excel.

Solution

In order to find out number of payments in Excel, we will use NPER formula as shown below:

We can cross check this NPER calculation using manual process as shown below. You can see that the same number of periods are coming from manual work.

 S. No PAYMENT INTEREST PRINCIPAL OUTSTANDING 100,000 1 10,000 417 9,583 90,417 2 10,000 377 9,623 80,793 3 10,000 337 9,663 71,130 4 10,000 296 9,704 61,426 5 10,000 256 9,744 51,682 6 10,000 215 9,785 41,898 7 10,000 175 9,825 32,072 8 10,000 134 9,866 22,206 9 10,000 93 9,907 12,298 10 10,000 51 9,949 2,350 11 10,000 10 9,990 (7,641)
Contents