Power BI – DAX Accrued Interest function

Interest that has accrued on a loan over time but has not yet been paid or collected by the borrower or lender is referred to as accrued interest. Regardless of when it will be paid, accrued interest is the total amount of interest that has been incurred or earned during a reporting period under accrual-based accounting. The lender’s interest revenue and receivable account, or the borrower’s interest expense and payable account, make up the adjusting entry for accumulated interest. Bond accrued interest is interest that has accrued since the bond’s last interest payment day but has not yet been paid.

Accrued Interest Financial Functions in Power BI

Financial functions in DAX are used in formulas that do financial calculations, such as net present value and rate of return. These capabilities are analogous to the financial capabilities of Microsoft Excel. Here, we are applying the functions on a sample library supplies dataset. A screenshot of the same can be seen below:

DAX ACCRINT Financial Function

It gives back any accrued interest on security that produces periodic interest.

Syntax: ACCRINT(<issue>, <first_interest>, <settlement>, <rate>, <par>, <frequency>[, <basis>[, <calc_method>]])

Parameters

issue	The day the security was issued.
first_interest	The first interest date for the security.
settlement	The settlement date for the security. The day after the issuance date when the security is traded to the buyer is known as the settlement date for securities.
rate	The annual coupon rate for the security.
par	The par value of the security.
frequency	The number of annual coupon payments. The frequency for annual payments is 1, for semi-annual payments is 2, and for quarterly payments is 4.
basis	The kind of day count foundation to employ (optional). The basis is taken to be 0 if it is omitted. Below this table is a list of acceptable values. Basis Day count basis 0 or omitted US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360
calc_method	When the date of settlement is later than the date of first interest, this logical value (which is optional) indicates how to calculate the total accrued interest. It is presumed that the calc method is TRUE if it is omitted. – ACCRINT returns the total interest that has accumulated from the issuance to the settlement if the calc method evaluates to TRUE or is omitted. – ACCRINT returns the interest that has accrued from the first interest to settlement if the calc method evaluates to FALSE.

Return Value

The accrued interest.

Notes to be considered:

In order to be used in calculations, dates are recorded as consecutive serial numbers. Since January 1, 2008, is 39,448 days after December 30, 1899, it is represented in the DAX as day 0, and December 30, 1899, as day 0. Calculating ACCRINT is done as follows:

Where,

• A_i = number of days that have been accumulated for the ith quasi-coupon period within an odd term.
• NC = number of quasi-coupon periods that are compatible with odd periods. If there is a fraction in this number, round it up to the nearest full number.
• NL_i = The quasi-coupon period’s typical length in days inside the odd period.

Numeric values are used for the issue, first interest, and settlement. The nearest integer is used to round the frequency and basis. A failure is indicated if, The dates issue, first interest, or settlement are invalid.

issue ≥ settlement.

rate ≤ 0.

par ≤ 0.

Frequency is any number other than 1, 2, or 4. The rate is 0. Par is 0. The basis is 0 or the Basis is more than 4. In calculated columns or with RLS rules, this function is not supported for use in Direct Query mode.

Example: accrint = EVALUATEANDLOG(ACCRINT(DATE(1990,3,1), DATE(2018,8,31), DATE(2023,5,1), 0.1, 1000, 2, 0))

Returns the interest that has accumulated from issuance to settlement on security with the aforementioned terms.

DAX ACCRINTM Financial Function

This function returns the interest that has accrued on a security that will mature with interest.

Syntax: ACCRINTM(<issue>, <maturity>, <rate>, <par>[, <basis>])

Issue	The day the security was issued.
Maturity	The maturity date of the security.
Rate	The annual coupon rate for the security.
Par	The par value of the security.
Basis	The kind of day count foundation to employ (optional). The basis is taken to be 0 if it is omitted. Below this table is a list of acceptable values. Basis Day count basis 0 or omitted US (NASD) 30/360 1 Actual/actual 2 Actual/360 3 Actual/365 4 European 30/360

Return Value

The accrued interest.

Notes to be considered:

In order to be used in calculations, dates are recorded as consecutive serial numbers. Since January 1, 2008, is 39,448 days after December 30, 1899, it is represented in the DAX as day 0, and December 30, 1899, as day 0. The formula for ACCRINTM is as follows:

Where,

• A- number of days accumulated that are counted on a monthly basis. The number of days between the issue date and the maturity date is used for interest at maturity items.
• D- Annual Year Basis.

The values for issue and maturity are reduced to integers. The base is rounded up to the next whole number. A failure is indicated if, Issue or maturity dates are invalid.

problem: maturity.

par 0. basis 0 or basis > 4. rate 0.

In calculated columns or with RLS rules, this function is not supported for use in Direct Query mode.

Example: accrintm = EVALUATEANDLOG(ACCRINTM(DATE(2003,4,1), DATE(2008,6,15), 0.1, 100, 3))

Returns the accumulated interest for security in accordance with the aforementioned terms.