Dax Function: Financial Functions
Category: Financial functions
Financial functions in Power BI (DAX) are used to perform common financial and investment-related calculations such as loan payments, interest rates, present/future values, and depreciation. These functions are particularly useful in financial modeling, business forecasting, and investment analysis.
Purpose
The purpose of financial functions in Power BI is to:
Perform loan and interest calculations (e.g., PMT, RATE, FV).
Determine the present and future values of investments.
Calculate depreciation of assets (e.g., SLN, DB).
Support cash flow and annuity calculations.
These functions help analysts create financial reports and dashboards for budgeting, forecasting, and decision-making.
| Function | Description |
|---|---|
| ACCRINT | Computes the accrued interest for a security with periodic interest payments. |
| ACCRINTM | Calculates accrued interest for a security that pays interest upon maturity. |
| AMORDEGRC | Determines asset depreciation for each accounting period, applying a depreciation coefficient based on asset lifespan. |
| AMORLINC | Computes asset depreciation for each accounting period. |
| COUPDAYBS | Finds the number of days from the start of a coupon period to its settlement date. |
| COUPDAYS | Calculates the total days in a coupon period that includes the settlement date. |
| COUPDAYSNC | Computes the days remaining from the settlement date until the next coupon date. |
| COUPNCD | Identifies the next coupon date following the settlement date. |
| COUPNUM | Counts the number of coupon payments between the settlement and maturity dates, rounding up to the nearest whole coupon. |
| COUPPCD | Retrieves the last coupon date before the settlement date. |
| CUMIPMT | Calculates the total interest paid on a loan between two specified periods. |
| CUMPRINC | Computes the cumulative principal paid on a loan within a given timeframe. |
| DB | Determines asset depreciation using the fixed-declining balance method for a specified period. |
| DDB | Computes depreciation for an asset using the double-declining balance method or another chosen approach. |
| DISC | Calculates the discount rate of a security. |
| DOLLARDE | Converts a dollar value expressed as a whole number and fraction (e.g., 1.02) into a decimal representation. |
| DOLLARFR | Converts a decimal-based dollar price into a fraction-based dollar value. |
| DURATION | Returns the Macauley duration for a security, assuming a par value of $100. |
| EFFECT | Determines the effective annual interest rate given a nominal rate and the number of compounding periods per year. |
| FV | Computes the future value of an investment based on a fixed interest rate. |
| INTRATE | Determines the interest rate for a fully invested security. |
| IPMT | Calculates the interest portion of a periodic payment for a loan or investment with fixed payments and a constant interest rate. |
| ISPMT | Computes interest paid (or received) for a specific period of a loan or investment with equal principal payments. |
| MDURATION | Returns the modified Macauley duration for a security with a par value of $100. |
| NOMINAL | Computes the nominal annual interest rate given the effective rate and compounding periods per year. |
| NPER | Determines the number of periods required to repay an investment based on fixed payments and a constant interest rate. |
| ODDFPRICE | Computes the price per $100 face value of a security with an irregular (short or long) first period. |
| ODDFYIELD | Returns the yield of a security with an odd-length first period. |
| ODDLPRICE | Calculates the price per $100 face value of a security with an irregular last coupon period. |
| ODDLYIELD | Determines the yield of a security with an odd-length last period. |
| PDURATION | Computes the number of periods needed for an investment to reach a given value. |
| PMT | Calculates the fixed periodic payment required to repay a loan based on a constant interest rate. |
| PPMT | Determines the principal portion of a loan payment for a specific period. |
| PRICE | Returns the price per $100 face value of a security that makes periodic interest payments. |
| PRICEDISC | Computes the price per $100 face value of a discounted security. |
| PRICEMAT | Determines the price per $100 face value of a security that pays interest only at maturity. |
| PV | Calculates the present value of an investment or loan, considering a constant interest rate. |
| RATE | Determines the interest rate per period for an annuity. |
| RECEIVED | Computes the amount received at maturity for a fully invested security. |
| RRI | Returns the equivalent interest rate for the growth of an investment. |
| SLN | Calculates straight-line depreciation of an asset for a single period. |
| SYD | Computes depreciation using the sum-of-years’ digits method over a specified period. |
| TBILLEQ | Determines the bond-equivalent yield of a Treasury bill. |
| TBILLPRICE | Calculates the price per $100 face value of a Treasury bill. |
| TBILLYIELD | Computes the yield of a Treasury bill. |
| VDB | Calculates the depreciation of an asset for a specific period, including partial periods, using the double-declining balance method or another approach. |
| XIRR | Computes the internal rate of return for a schedule of cash flows that may not follow a regular pattern. |
| XNPV | Calculates the present value of a schedule of cash flows that are not necessarily periodic. |
| YIELD | Determines the yield of a security that makes periodic interest payments. |
| YIELDDISC | Computes the annual yield of a discounted security. |
| YIELDMAT | Determines the annual yield for a security that pays interest upon maturity. |
Maximize the potential of Power BI and enhance your data insights with our expert consulting services. Whether you’re looking for assistance with advanced DAX functions, help designing interactive dashboards, or support in optimizing your data models for better performance, our skilled Power BI consultants are ready to provide tailored solutions for your business. Visit our Power BI consultancy page to learn more about how we can empower your organization to make more informed, data-driven decisions.
Financial functions in Power BI (DAX) allow users to perform calculations related to investments, loans, depreciation, and interest rates.
Use the PMT function:
PMT(5%/12, 60, -20000, 0, 0)
This calculates the monthly payment for a loan with a 5% annual interest rate, 60 periods, and a principal of $20,000.
Yes! Use SLN for straight-line depreciation or DB for declining balance depreciation.
Example of SLN:
SLN(50000, 10000, 5)
This calculates the annual depreciation for an asset worth $50,000, with a salvage value of $10,000 over 5 years.
Use the FV function:
FV(0.07, 5, -500, 0, 0)
This calculates the future value of an investment with a 7% annual return, 5 periods, and a $500 payment per period.
Use the RATE function:
RATE(60, -200, 10000, 0, 0)
This returns the interest rate for a loan with 60 periods, a $200 payment per period, and a present value of $10,000.