Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Everything about Charts in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • Excel Pie Chart
  • How to calculate correlation coefficient Correlation in Excel
  • How to create running total in an Excel Table

References

  • Count rows with at least n matching values
  • How to get relative column numbers in a range in Excel
  • How to use Excel LOOKUP Function
  • How to get address of last cell in range in Excel
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Validations

  • Excel Data validation must not contain
  • Excel Data validation allow weekday only
  • Excel Data validation require unique number
  • Excel Data validation allow uppercase only
  • Excel Data validation whole percentage only

Calculate payment periods for loan in Excel

by

To calculate the number of payment periods for a loan, given the loan amount, the interest rate, and a periodic payment amount, you can use the NPER function.

Formula

=NPER(rate,payment,-loan)

Explanation

In the example shown, the formula in C10 is…

=NPER(C6/12,C7,-C5)

How this formula works

Loans have four primary components: the amount, the interest rate, the number of periodic payments (the loan term) and a payment amount per period. One use of the NPER function is to calculate the number of periodic payments for loan.

For this example, we want to calculate the number of payments for a $5000 loan, with a 4.5% interest rate, and fixed payments of $93.22. The NPER function is configured as follows:

rate – The interest rate per period. We divide the value in C6 by 12 since 4.5% represents annual interest:

=C6/12

pmt – The payment made each period. This is the known amount $93.22, which comes from cell C7.

pv – The present value, or total value of all payments now. In the case of a loan, this is input as a negative value by adding a negative sign in from of C5:

-C5

fv – the future value, or desired balance after last payment. We do not provide a value since the default is zero and makes sense for a loan.

type – When payments are due, where 0 = end of period and 1 = beginning of period. Default is 0 so we do not provide a value.

With these inputs, the NPER function returns 59.996, which is rounded to 60 in the example, representing 60 months = 5 years. The monthly payment is rounded to the nearest penny, which causes NPER to return a number slightly less than 60.

Post navigation

Previous Post:

DECIMAL function: Description, Usage, Syntax, Examples and Explanation

Next Post:

AVERAGE function: Description, Usage, Syntax, Examples and Explanation

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Invoice status with nested if in Excel
  • Return blank if in Excel
  • How to use IFS function in Excel
  • IFERROR function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel XOR Function

Date Time

  • Get last weekday in month in Excel
  • WORKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Get month name from date in Excel
  • Sum through n months in Excel
  • How to calculate percent of year complete in Excel

Grouping

  • How to randomly assign data to groups in Excel
  • Group times into 3 hour buckets in Excel
  • Categorize text with keywords in Excel
  • Group arbitrary text values in Excel
  • Group numbers at uneven intervals in Excel

General

  • Create dynamic workbook reference to another workbook in Excel
  • Subtotal invoices by age in Excel
  • Basic numeric sort formula in Excel
  • How to calculate percentage discount in Excel
  • Share Excel data with Word documents
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning