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
Financial Ratios Accounting Cycle Accounting Principles Financial Accounting Basics Financial Statements Reporting Bad Debts Current Assets Long-term Assets Voucher System Partnership Depreciation Work Sheet - 10 Column Work Sheet Difference Between Reserve and Fund Accounting for Leases Capital reduction and reconstruction Absorption of Company Amalgamation Accounting for Installment Sales Basis of Recording Profit and Loss Branch Accounting Construction Contracts Revenue Recognition Accounting for Groups Financial Analysis Events After the Balance Sheet Date Deferred Tax Cost Accounting Activity Based Costing (ABC) Throughput Accounting Relevant Cost Break Even Analysis Standard Costing Inventory Management Payroll Accounting Royalty Accounting Statistics Master Budget Salary Income Microsoft Excel Tutorial How to Count Number of Records between Two Dates How to sum values in excel based on a criteria Finding the first login and last logout time of employees How to Print Salary Slips Using VLookup Formula in Excel How to Calculate Net Present Value (NPV) in Excel Income tax calculation excel sheet for salaried individuals How to Calculate Pay Back Period in Excel How to Calculate Declining Balance in Excel Using Formula How to Calculate Double Declining Balance in Excel How to Calculate Number of Payments on A Loan In Excel Using NPER Count the Number of Excel Records That Fall Between Two Dates How to Count Cells between Two Numbers in Excel How to Prepare Exam Result in Excel 3D Sum Multiple Worksheets How to Count Specific Text in Excel Other Topics Share or Stock Valuation Model Financial Management Topics Kinds of Endorsement Letter of Credit, Kinds and Its Advantages/ Utilities Modern Functions of a Commercial Banks Difference Between Secured And Unsecured Loan Excel conditional formatting red if negative green if positive How to Remove Extra Spaces in Excel Result Card or Result Sheet Creation Using Excel Basic Accounting MCQS Multiple Choice Questions