DAX

Dax Function: INTRATE

Q: Can the INTRATE function handle varying day count conventions?

Yes, you can specify the 'basis' parameter to adjust for day count conventions such as 30/360 or actual/actual.

Category: Financial Functions

The INTRATE function in Power BI is a DAX function used to calculate the interest rate for a fully invested security. It computes the annualized interest rate based on the security’s purchase price, redemption value, issue date, maturity date, and the day count basis.

Purpose

To determine the effective interest rate for investments or bonds.
Facilitates the analysis of investment returns, ensuring better decision-making in financial models.

Type of Calculations

Performs financial computations to derive the interest rate using time-value-of-money principles.
Converts the relationship between a security’s price and its maturity value into an annualized rate.

Practical Use Cases

Bond Analysis: Calculate the annualized return on a bond investment.
Financial Reporting: Estimate the cost or yield of short-term securities.
Investment Valuation: Assess profitability by comparing interest rates.


INTRATE(settlement, maturity, investment, redemption, [basis])

Parameter	Type	Description
`settlement`	Scalar	The settlement date of the security (the date the security is purchased). Must be a valid date.
`maturity`	Scalar	The maturity date of the security (when it is redeemed). Must be a valid date after `settlement`.
`investment`	Scalar	The amount invested in the security.
`redemption`	Scalar	The amount received upon maturity of the security.
`basis`	Scalar	(Optional) The day count basis to use (e.g., 0 = 30/360, 1 = actual/actual). Defaults to 0.

How Does INTRATE Dax Function Works

Formula

The INTRATE function uses this formula:

Where:

Redemption: Total amount received at maturity.
Investment: Initial purchase price.
Days Between Settlement and Maturity: The duration from settlement to maturity.
Year Length: Depends on the selected basis.

Logical Steps

Calculates the difference between redemption and investment.
Divides by the investment multiplied by the fraction of the year (based on basis).
Converts this into an annualized rate.

What Does It Return?

Returns the annualized interest rate for the security as a decimal value.
The output represents a percentage, typically formatted as a number.

When Should We Use It?

When assessing the yield of bonds or investments.
For comparing returns on securities with varying maturities or day count conventions.
In financial modeling, to evaluate profitability over time.

Examples

Basic Usage :

Calculate the interest rate for a bond with:

Settlement Date: 2023-01-01
Maturity Date: 2023-12-31
Investment: $1,000
Redemption: $1,100


INTRATE(DATE(2023,1,1), DATE(2023,12,31), 1000, 1100)

Result: 0.10 (or 10%)

Column Usage

Assume a table of securities with columns for settlement, maturity, investment, and redemption. Calculate the interest rate for each security:


InterestRate = INTRATE(Securities[Settlement], Securities[Maturity], Securities[Investment], Securities[Redemption])

Calculates interest rates dynamically for each row.

Advanced Usage

Combine INTRATE with other DAX functions to filter securities:


FilteredRate =
CALCULATE(
AVERAGEX(
FILTER(Securities, Securities[Investment] &gt; 1000),
INTRATE(Securities[Settlement], Securities[Maturity], Securities[Investment], Securities[Redemption])
)
)

Computes the average interest rate for securities with investments over $1,000.

Tips and Tricks

Ensure that settlement and maturity are valid and in chronological order.
Use appropriate basis values to reflect the correct day count convention.
Providing an invalid date or mismatched settlement and maturity values.
Forgetting to adjust the basis parameter for non-standard financial instruments.

Performance Impact of INTRATE DAX Function:

Efficient for scalar values and small datasets.
May require optimization for datasets with large volumes of securities.

Related Functions You Might Need

Function	Description
`YIELD`	Calculates the yield of a security with periodic interest payments.
`MDURATION`	Computes the modified duration of a security for sensitivity analysis.
`PRICE`	Returns the price of a security based on yield.

Want to Learn More?
For more information, check out the official Microsoft documentation for INTRATE 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.

1. What does the INTRATE function do in Power BI?

It calculates the annualized interest rate for a fully invested security based on settlement, maturity, investment, and redemption values.

2. Can the INTRATE function handle varying day count conventions?

Yes, you can specify the basis parameter to adjust for day count conventions such as 30/360 or actual/actual.

3. Is the INTRATE function suitable for large datasets?

Yes, but for large datasets, ensure optimal calculations by minimizing recalculations and using aggregate functions.

4. What happens if the settlement date is after the maturity date?

The function will return an error, as the maturity date must logically occur after the settlement date.

5. How do you interpret the result of the INTRATE function?

The result is a decimal representing the annualized interest rate, which can be formatted as a percentage.

Dax Function: FV

Dax Function: IPMT