How to Use the Power BI DAX function NPER

What is the NPER function?

The NPER function is a financial function in Power BI that calculates the number of periods required to pay off a loan or reach a savings goal. NPER stands for “number of periods” and is used to determine the duration of an investment or loan based on a set of constant payments and a fixed interest rate.


The syntax of the NPER function is as follows:

NPER(rate, pmt, pv, [fv], [type])


- `rate` is the interest rate per period.

- `pmt` is the payment made each period.

- `pv` is the present value or principal amount of the loan or investment.

- `fv` is the future value or cash balance that you want to attain after the last payment is made. This parameter is optional and set to 0 by default.

- `type` is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0.


Suppose you want to save $10,000 in five years by making monthly deposits of $150. You have a savings account that pays an annual interest rate of 5%. How many monthly payments will you need to make to reach your savings goal?

To calculate the number of monthly payments required, you can use the NPER function in Power BI. Here's how:

1. Open Power BI and create a new measure.

2. Name the measure "Number of Payments".

3. Enter the following formula in the formula bar:

Number of Payments = NPER(0.05/12,-150,0,10000,0)

4. Press Enter to calculate the result.

The result will be 60, which means that you will need to make 60 monthly payments of $150 to save $10,000 in five years at an annual interest rate of 5%.

The NPER function in Power BI is a powerful tool for calculating the number of periods required to pay off a loan or reach a savings goal. By using this function, you can easily forecast the outcomes of your financial investments or loans. In this article, we have explored the syntax of the NPER function and provided an example to demonstrate how it can be used in Power BI.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

15-17 July 2024
16-18 September 2024
11-13 November 2024

Contact Us


    Your Name (required)


    Email (required)


    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)