PPMT
A
C
- CALCULATE
- CALCULATETABLE
- CALENDAR
- CALENDARAUTO
- CEILING
- CHISQ.DIST
- CHISQ.DIST.RT
- CHISQ.INV
- CHISQ.INV.RT
- CLOSINGBALANCEMONTH
- CLOSINGBALANCEQUARTER
- CLOSINGBALANCEYEAR
- COALESCE
- COLUMNSTATISTICS
- COMBIN
- COMBINA
- COMBINEVALUES
- CONCATENATE
- CONCATENATEX
- CONFIDENCE.NORM
- CONFIDENCE.T
- CONTAINS
- CONTAINSROW
- CONTAINSSTRING
- CONTAINSSTRINGEXACT
- CONVERT
- COS
- COSH
- COT
- COTH
- COUNT
- COUNTA
- COUNTAX
- COUNTBLANK
- COUNTROWS
- COUNTX
- COUPDAYBS
- COUPDAYS
- COUPDAYSNC
- COUPNCD
- COUPNUM
- COUPPCD
- CROSSFILTER
- CROSSJOIN
- CUMIPMT
- CUMPRINC
- CURRENCY
- CURRENTGROUP
- CUSTOMDATA
D
E
I
N
O
P
R
S
- SAMEPERIODLASTYEAR
- SAMPLE
- SEARCH
- SECOND
- SELECTCOLUMNS
- SELECTEDMEASURE
- SELECTEDMEASUREFORMATSTRING
- SELECTEDMEASURENAME
- SELECTEDVALUE
- SIGN
- SIN
- SINH
- SLN
- SQRT
- SQRTPI
- STARTOFMONTH
- STARTOFQUARTER
- STARTOFYEAR
- STDEVX.P
- STDEVX.S
- STDEV.P
- STDEV.S
- SUBSTITUTE
- SUBSTITUTEWITHINDEX
- SUM
- SUMMARIZE
- SUMMARIZECOLUMNS
- SUMX
- SWITCH
- SYD
T
U
Syntax of the PPMT Function
Before we get started, let’s take a look at the syntax of the PPMT function. The PPMT function takes the following arguments:
– **Rate**: The interest rate per period.
– **Per**: The period for which you want to calculate the principal payment.
– **Nper**: The total number of payment periods in the loan.
– **Pv**: The present value or principal of the loan.
– **Fv**: The future value or a cash balance you want to attain after the last payment is made. If omitted, the function assumes a value of 0.
– **Type**: The number 0 or 1 and indicates when payments are due. If omitted, the function assumes a value of 0.
The general syntax for the PPMT function in Power BI is:
PPMT(rate, per, nper, pv, [fv], [type])
Using the PPMT Function in Power BI
Now that we know the syntax of the PPMT function, let’s take a look at how to use it in Power BI. For the purpose of this tutorial, we will use a sample dataset that contains information about a loan.
Step 1: Create a Table in Power BI
The first step is to create a table in Power BI that contains the necessary information for the PPMT function. In our sample dataset, we have created a table called “Loan Information” that contains the following columns:
– **Loan Amount**: The total amount of the loan.
– **Interest Rate**: The interest rate for the loan.
– **Loan Term**: The length of the loan in months.
– **Payment Frequency**: The frequency of payments (monthly, quarterly, etc.).
– **Payment Start Date**: The date of the first payment.
Step 2: Create a Measure for the PPMT Function
Next, we need to create a measure for the PPMT function. To do this, click on the “New Measure” button in the “Modeling” tab and enter the following DAX formula:
PPMT = PPMT('Loan Information'[Interest Rate]/'Loan Information'[Payment Frequency],
SELECTEDVALUE('Calendar'[Month Number]),
'Loan Information'[Loan Term]*'Loan Information'[Payment Frequency],
'Loan Information'[Loan Amount])
This DAX formula uses the PPMT function to calculate the principal payment for the selected period. The ‘Loan Information’ table is used to retrieve the loan amount, interest rate, loan term, and payment frequency, while the ‘Calendar’ table is used to select the period for which we want to calculate the principal payment.
Step 3: Create a Visual for the PPMT Measure
Finally, we can create a visual that displays the PPMT measure. To do this, drag the “PPMT” measure onto a visual and select the appropriate fields for the visual. In our sample dataset, we have created a table visual that displays the payment start date, payment frequency, and principal payment for each payment period.
In conclusion, the PPMT function is a valuable tool for calculating loan payments in Power BI. By following the steps outlined in this article, you can easily use the PPMT function in your own Power BI reports and dashboards.
