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) |