Dax Function: PMT

Category: Financial Functions

The PMT function in Power BI calculates the payment amount for a loan or investment based on constant payments and a fixed interest rate. It is widely used in financial analysis and budgeting to compute periodic payments.

Purpose

• Loan Analysis: Determines the payment amount required to repay a loan over a specific time.

• Investment Planning: Calculates payments for annuities or similar financial instruments.

Type of Calculations

• Performs financial calculations based on the present value, future value, interest rate, and the number of periods.

• Considers both positive and negative cash flows to represent payments and inflows.

Practical Use Cases

1. Loan Repayment: Calculate monthly payments for mortgages, car loans, or personal loans.

2. Savings Contributions: Estimate periodic contributions required to achieve a financial goal.

3. Budget Forecasting: Model future cash flows for businesses or individuals.

How Does PMT Dax Works

Mathematical Principle

The PMT function calculates periodic payments using the formula:

​

Where:

• r: Interest rate per period

• PV: Present value (loan or investment amount)

• FV: Future value

• n: Total number of periods

What Does It Return?

• Scalar Value: The payment amount required per period.

• Sign Convention: Returns a negative value to represent outgoing payments.

When Should We Use It?

• Loan Planning: Determine monthly installments for loans of varying durations and rates.

• Retirement Planning: Calculate contributions needed to reach retirement savings goals.

• Debt Management: Understand payment schedules for outstanding liabilities.

Examples

Basic Usage :

Calculate the monthly payment for a loan of $10,000 at an annual interest rate of 5% over 5 years.

Result: $188.71 (Monthly payment).

Column Usage

Calculate periodic payments for multiple loans in a table:

Advanced Usage

Incorporate future value (FV) and payment type (Type) into calculations:

Result: Calculates the payment when contributions grow to $10,000.

Tips and Tricks

• Use consistent units for rate and nper (e.g., monthly interest rate with monthly periods).

• Always use a negative pv to signify outgoing payments.

• Forgetting to convert annual interest rates to monthly rates.

• Misinterpreting the sign of pv and fv.

Performance Impact of PMT DAX Function:

• Efficient for single scalar calculations.

• For large datasets, precompute static inputs to optimize performance.

Related Functions You Might Need

Want to Learn More?
For more information, check out the official Microsoft documentation for PMT You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.