Dax Function: ACCRINT
Category: Financial Functions
The ACCRINT function in Power BI (DAX) is used to calculate accrued interest for securities that pay periodic interest. It helps in determining the interest earned between two dates for fixed-income investments such as bonds.
Purpose
The function is primarily used in financial modeling and investment analysis to track interest accumulation over time.
It helps investors, analysts, and financial institutions compute interest amounts due before bond maturity or coupon payments.
Type of Calculations
The function calculates simple interest accrual over a given period based on annual interest rate, principal amount, and settlement/maturity dates.
It considers the day count basis for interest calculations, making it flexible for different financial standards.
Practical Use Cases
Computing interest due on bonds between coupon payments.
Determining accrued interest for partial-period investments.
Forecasting cash flows related to bond investments.
ACCRINT(issue_date, first_interest_date, settlement_date, rate, par, frequency, [basis])
| Parameter | Type | Description |
|---|---|---|
| issue_date | Date | The date when the security was issued. |
| first_interest_date | Date | The first date when interest is paid on the security. |
| settlement_date | Date | The date the security is traded or settled. |
| rate | Decimal | The annual interest rate of the security. |
| par | Decimal | The face value (principal) of the security. |
| frequency | Integer | The number of coupon payments per year (1 = annual, 2 = semiannual, 4 = quarterly). |
| [basis] (optional) | Integer | The day count basis used for calculations (defaults to 0). Options: 0 = US (NASD) 30/360 1 = Actual/Actual 2 = Actual/360 3 = Actual/365 4 = European 30/360 |
How Does ACCRINT Dax Function Works
The formula for accrued interest follows:
- The number of accrued days is determined based on the selected day count basis.
- The function prorates the interest between issue and settlement dates.
What Does It Return?
The function returns a decimal value representing the accrued interest on a security, based on the given parameters.
When Should We Use It?
To calculate accrued interest on bonds or fixed-income investments.
When analyzing interest earned before bond maturity.
For creating financial models involving securities.
Examples
Basic Usage :
ACCRINT(DATE(2023,1,1), DATE(2023,6,1), DATE(2023,3,1), 0.05, 1000, 2, 0)
Result: Returns accrued interest for a semiannual bond with a 5% interest rate.
Column Usage:
Assuming we have a table Securities with columns:
Issue DateFirst Interest DateSettlement DateRateParFrequency
We can create a calculated column:
Accrued Interest = ACCRINT( 'Securities'[Issue Date], 'Securities'[First Interest Date], 'Securities'[Settlement Date], 'Securities'[Rate], 'Securities'[Par], 'Securities'[Frequency], 0)
This calculates accrued interest for each row dynamically.
Advanced Usage – Combining with Other DAX Functions
To calculate total accrued interest for a portfolio:
Total Accrued Interest = SUMX( Securities, ACCRINT( Securities[Issue Date], Securities[First Interest Date], Securities[Settlement Date], Securities[Rate], Securities[Par], Securities[Frequency], 0))
This sums the accrued interest across all securities in the dataset.
Tips and Tricks
Use the correct day count basis to match financial conventions.
- Ensure that dates are formatted correctly to avoid errors.
- Be mindful of leap years when using the Actual/Actual day count basis.
- Combine with
SUMXto calculate total accrued interest for a portfolio.
Potential Pitfalls
- If
settlement_dateis beforeissue_date, the function returns an error. - If
frequencyis not 1, 2, or 4, the function returns an error. - Make sure
basisvalues are within the accepted range (0-4).
Performance Impact of ACCRINT DAX Function:
The function is optimized for row-level calculations but may slow down if used extensively in a large dataset.
When applying across millions of records, consider using aggregations or precomputed interest values.
Related Functions You Might Need
ACCRINTM – Calculates accrued interest for securities that pay interest at maturity instead of periodically.
CUMIPMT – Computes cumulative interest payments over a loan period.
COUPDAYS – Finds the number of days in a coupon period.
PRICE – Returns the price of a bond based on periodic interest payments.
Want to Learn More?
For more information, check out the official Microsoft documentation for ACCRINT 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.
It calculates the accrued interest for securities that pay periodic interest, considering issue date, settlement date, and interest rate.
No, ACCRINT is specifically for bonds and securities. For loans, use functions like CUMIPMT.
The function returns an error. Valid values are 1 (annual), 2 (semiannual), and 4 (quarterly).
Use SUMX to aggregate accrued interest across a dataset.
ACCRINT calculates periodic accrued interest, while ACCRINTM applies to bonds that pay interest only at maturity.
Established in 2020, Lets Viz Technologies provides a full range of high-quality data analysis and data visualization services. We are also an authorized Zoho Partner.
Sitelinks
