PPMT - Daxplained

PPMT is a DAX function that returns the payment on the principal for a given period of an investment based on periodic, constant payments and a constant interest rate.

Syntax

PPMT(
   Rate,
   Per,
   Nper,
   Pv,
   Fv,
   Type
)

Argument	Properties	Description
Rate		The interest rate for the loan.
Per		Specifies the period and must be in the range 1 to nper.
Nper		The total number of payments for the loan.
Pv		The present value, or the total amount that a series of future payments is worth now; also known as the principal.
Fv	Optional	The future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be BLANK.
Type	Optional	The number 0 (zero) or 1 and indicates when payments are due.

Return Values

The payment on the principal for a given period.

Remarks

Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 0.12/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 0.12 for rate and 4 for nper.
type is rounded to the nearest integer.
An error is returned if:
- per < 1 or per > nper
- nper < 1
This function is not supported for use in DirectQuery mode when used in calculated columns or row-level security (RLS) rules.

Example 1

Data	Argument description
10%	Annual interest rate
2	Number of years for the loan
$2,000.00	Amount of loan

The following DAX query:

EVALUATE
{
  PPMT(0.1/12, 1, 2*12, 2000.00)
}

Returns the principal payment made in the first month for a loan with the terms specified above.

[Value]
-75.6231860083663

Example 2

Data	Argument description
8%	Annual interest rate
10	Number of years for the loan
$200,000.00	Amount of loan

The following DAX query:

EVALUATE
{
  PPMT(0.08, 10, 10, 200000.00)
}

Returns the principal payment made in the 10th year for a loan with the terms specified above.

[Value]
-27598.0534624214

Contribute » | Contributors: Rick de Groot
Microsoft documentation: https://learn.microsoft.com/en-us/dax/ppmt-function-dax