When working with financial data in Power BI, especially for investments like bonds, treasury bills, or securities that pay interest, analysts often need to calculate accrued interest. Power BI’s DAX (Data Analysis Expressions) language provides powerful functions for financial analysis, including ACCRINT and ACCRINTM.
Although both functions deal with accrued interest, they are used in different contexts. Many users confuse them, so in this article we’ll break down the difference, explore their syntax, and look at practical examples you can use in your Power BI reports.
What is Accrued Interest?
Before we dive into DAX functions, let’s recap what accrued interest means.
Accrued interest is the amount of interest that has accumulated on a bond or financial instrument but has not yet been paid to the investor.
For example:
- If you buy a bond halfway through its interest period, you will still owe the seller the interest they earned up to that point.
- Accrued interest ensures fair compensation when bonds are bought, sold, or redeemed between payment dates.
Power BI’s ACCRINT and ACCRINTM functions help calculate this amount, depending on whether the security pays interest periodically or only at maturity.
ACCRINT Function in Power BI DAX
Definition
The ACCRINT function returns the accrued interest for a security that pays interest periodically (e.g., every 6 months or annually).
Syntax
ACCRINT(issue, first_interest, settlement, rate, par, frequency, basis, [calc_method])
Parameters
- issue – The issue date of the security.
- first_interest – The first interest payment date.
- settlement – The settlement date (when the buyer purchases the security).
- rate – The annual coupon rate (interest rate).
- par – The face value of the security (default is 1000).
- frequency – Number of interest payments per year:
- 1 = annual, 2 = semiannual, 4 = quarterly.
- basis – Day count basis (0 = US 30/360, 1 = actual/actual, 2 = actual/360, 3 = actual/365, 4 = European 30/360).
- calc_method (optional) – Boolean. TRUE (default) includes issue date; FALSE excludes it.
Example
Suppose you have a bond with the following details:
- Issue date: 01-Jan-2024
- First interest payment: 01-Jul-2024
- Settlement date: 01-Mar-2024
- Annual interest rate: 10% (0.1)
- Face value: 1000
- Payments: Semiannual (2)
- Basis: US 30/360
The formula would be:
ACCRINT(DATE(2024,1,1), DATE(2024,7,1), DATE(2024,3,1), 0.1, 1000, 2, 0)
Result: 16.67 (approx.)
This means that by March 1, 2024, the bond has accumulated $16.67 in interest since it was issued.
ACCRINTM Function in Power BI DAX
Definition
The ACCRINTM function returns the accrued interest for a security that pays interest only at maturity (no periodic interest payments).
Syntax
ACCRINTM(issue, settlement, rate, par, basis)
Parameters
- issue – The issue date of the security.
- settlement – The settlement date (when the buyer purchases the security).
- rate – The annual coupon rate.
- par – The face value of the security (default 1000).
- basis – Day count basis (0–4 as in ACCRINT).
Example
Consider a zero-coupon bond (no periodic payments):
- Issue date: 01-Jan-2024
- Settlement date: 01-Jul-2024
- Annual interest rate: 10% (0.1)
- Face value: 1000
- Basis: US 30/360
The formula would be:
ACCRINTM(DATE(2024,1,1), DATE(2024,7,1), 0.1, 1000, 0)
Result: 50.00 (approx.)
This means that by July 1, 2024, the bond has accumulated $50.00 in interest since issuance.
Key Differences Between ACCRINT and ACCRINTM
| Feature | ACCRINT | ACCRINTM |
|---|---|---|
| Interest Type | For securities that pay periodically | For securities that pay only at maturity |
| Parameters | Requires first_interest and frequency | Does not need these, simpler syntax |
| Use Case | Bonds with semiannual or quarterly payments | Zero-coupon or bullet bonds |
| Output | Accrued interest since issue date up to settlement | Accrued interest from issue date to maturity |
Practical Summary
- Use ACCRINT when the bond has regular coupon payments.
- Use ACCRINTM when the bond pays interest only once at maturity.
Real-Life Use Cases in Power BI
- Portfolio Management Dashboard
- Use ACCRINT to show investors how much interest they have earned on bonds with regular coupon payments.
- Use ACCRINTM for zero-coupon bonds in the same portfolio.
- Treasury Reporting
- Finance teams can track accrued interest across different securities to calculate returns and liabilities accurately.
- Investment Comparison
- Analysts can compare bonds with periodic coupons vs. zero-coupon bonds side by side.
- Cash Flow Forecasting
- Knowing accrued interest helps forecast upcoming payments and revenue recognition.
Example in Power BI Table
Imagine you have the following dataset of bonds:
| Bond | Type | Issue Date | First Interest | Settlement Date | Rate | Face Value | Frequency | Basis |
|---|---|---|---|---|---|---|---|---|
| A | Coupon Bond | 01-Jan-2024 | 01-Jul-2024 | 01-Mar-2024 | 10% | 1000 | 2 | 0 |
| B | Zero-Coupon | 01-Jan-2024 | – | 01-Jul-2024 | 10% | 1000 | – | 0 |
DAX formulas:
For Bond A:
AccruedInterest_A = ACCRINT(DATE(2024,1,1), DATE(2024,7,1), DATE(2024,3,1), 0.1, 1000, 2, 0)
For Bond B:
AccruedInterest_B = ACCRINTM(DATE(2024,1,1), DATE(2024,7,1), 0.1, 1000, 0)
Output Table:
| Bond | Accrued Interest |
|---|---|
| A | 16.67 |
| B | 50.00 |
Common Mistakes to Avoid
- Confusing ACCRINT with ACCRINTM
- Using ACCRINT for a zero-coupon bond will give incorrect results.
- Ignoring the Basis Parameter
- The day count convention can significantly impact interest calculations. Always confirm whether your market uses 30/360, actual/365, etc.
- Wrong Dates
- If
settlementis earlier thanissue, the formula will throw an error.
- If
- Not Matching Frequency
- For ACCRINT, make sure frequency (1, 2, 4) matches the actual bond payment schedule.
Final Thoughts
Both ACCRINT and ACCRINTM are essential DAX functions for financial analysis in Power BI. While they share similarities, their use cases differ:
- ACCRINT is for bonds or securities that pay regular coupons.
- ACCRINTM is for securities that pay interest only at maturity.
By mastering these functions, financial analysts, accountants, and data professionals can build accurate investment dashboards, forecast cash flows, and provide deeper insights into portfolio performance.
Want to Master Financial DAX Functions?
If you want to go beyond the basics and learn how to apply financial modeling and analytics in Power BI, then this is your chance.
👉 Learn Data Analytics at Slidescope
At Slidescope, we offer hands-on training in Data Analytics, Business Intelligence, and Power BI with real-world financial datasets. You’ll gain practical skills in DAX, KPIs, and advanced reporting that employers value.
Start your journey toward becoming a data analytics professional today!
