Downloads
DAXFinancialIntermediate

NPER

Returns the number of periods needed to pay off a loan, or to reach a target, with equal payments at a fixed rate.

Syntax
NPER(<rate>, <pmt>, <pv> [, <fv>] [, <type>])

Returns: A number of periods. A fraction means the last payment is smaller or comes at a different point.

What it does

NPER answers "how many payments will it take?" If a company borrows an amount and can pay a fixed amount each period, NPER returns the number of periods to repay the loan. It can also show how long it takes to reach a savings target with regular deposits. Finance teams use it to test payoff plans, to size debt repayment by month, and to check the term on a loan statement.

Syntax and arguments

Example

A company borrows 20,000 at 6% a year. It can pay 500 a month. The question is how many months the loan will take.

Months to Repay = NPER(0.06 / 12, -500, 20000)

The result is about 44.7 months. That means 44 full payments of 500 and a smaller final payment in month 45. The minus sign on the payment is the Excel convention for cash paid out.

Example: a second, different use

The number of months to build a reserve of 30,000 from a monthly deposit of 400, earning 0.25 percent a month. The future value is the target, and the present value is 0.

Months to Reserve Target = NPER(0.0025, -400, 0, 30000)

Things to watch

In business terms

NPER supports payoff planning for debt, the timing of savings targets, the payback period on a loan, and the term check on a financing offer. Treasurers use it to show how long a repayment plan will run.

Related functions

All DAX functions · View as Markdown · DAX fundamentals