Dax Function: YIELD
Category: Financial Functions
The YIELD function in Power BI calculates the annual yield of a security that pays periodic interest, such as a bond. The yield is the rate of return earned on the security, considering the purchase price, redemption value, coupon interest rate, and time to maturity.
Purpose
To compute the annual rate of return on fixed-income securities like bonds.
Helps investors assess the profitability of a bond investment.
Type of Calculations
Calculates the bond yield using inputs such as settlement date, maturity date, and coupon rate.
Accounts for various day count conventions for precise financial analysis.
Practical Use Cases
Bond Valuation: Determine the yield of a bond to compare it with alternative investments.
Portfolio Analysis: Evaluate the performance of fixed-income assets.
Financial Reporting: Provide key metrics for investment reports or dashboards.
YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
| Parameter | Type | Description |
|---|---|---|
settlement | Scalar | The settlement date of the security, when the buyer takes ownership. Must be later than the issue date. |
maturity | Scalar | The maturity date of the security, when it expires, and the principal is repaid. |
rate | Scalar | The annual coupon rate of the bond (as a decimal, e.g., 5% = 0.05). |
pr | Scalar | The price of the bond per $100 of face value. |
redemption | Scalar | The redemption value of the bond per $100 of face value. |
frequency | Scalar | Number of coupon payments per year: 1 (annual), 2 (semi-annual), or 4 (quarterly). |
[basis] | Scalar | (Optional) The day count basis for calculations. Default is 0 (US 30/360). See day count basis table below. |
How Does YIELD Dax Works
Mathematical Principle
The YIELD function solves the yield-to-maturity (YTM) equation:
Where:
P: Price of the bond
C: Periodic coupon payment
F: Redemption value
n: Number of periods to maturity
f: Payment frequency
YTM: Yield to Maturity (solved iteratively)
Key Points
Iterative calculation: The function uses numerical methods to find the yield.
Frequency impacts coupon payment intervals and calculation complexity.
The basis determines how days are counted between periods.
What Does It Return?
- Decimal Value: The calculated annual yield of the bond as a decimal (e.g., 0.06 for 6%).
When Should We Use It?
Bond Pricing: Determine the return rate for bonds at different prices.
Investment Comparison: Compare bond yields to other securities or benchmark rates.
Scenario Analysis: Assess yield changes with varying coupon rates or maturity dates.
Examples
Basic Usage :
Calculate the yield for a bond with:
Settlement date: 2023-01-01
Maturity date: 2028-01-01
Annual coupon rate: 5%
Price: $95
Redemption value: $100
Semi-annual payments
US 30/360 basis
YIELD(DATE(2023, 1, 1), DATE(2028, 1, 1), 0.05, 95, 100, 2, 0)
Result: 0.0574 (5.74%)
Column Usage
For a table of bonds:
| Bond | Settlement | Maturity | Rate | Price | Redemption | Frequency |
|---|---|---|---|---|---|---|
| A | 2023-01-01 | 2028-01-01 | 0.05 | 95 | 100 | 2 |
| B | 2023-03-01 | 2030-03-01 | 0.04 | 98 | 100 | 1 |
BondYield =
YIELD(
Bonds[Settlement],
Bonds[Maturity],
Bonds[Rate],
Bonds[Price],
Bonds[Redemption],
Bonds[Frequency],
0
)
Result: Adds annual yields for each bond.
Advanced Usage
Combine with other financial metrics:
Calculate bond yield adjusted for inflation:
RealYield =
YIELD(
Bonds[Settlement],
Bonds[Maturity],
Bonds[Rate],
Bonds[Price],
Bonds[Redemption],
Bonds[Frequency],
0
) - InflationRate
Result: Computes real yield after inflation adjustments.
Tips and Tricks
Ensure dates are formatted correctly and validated for logical errors.
Use precise pricing and redemption values to improve accuracy.
Incorrect frequency parameter (e.g., using 2 for annual payments).
Mismatched settlement and maturity dates, leading to errors.
Performance Impact of YIELD DAX Function:
Computationally intensive for large datasets due to iterative methods.
Optimize by filtering relevant securities before applying the function.
Related Functions You Might Need
| Function | Description |
|---|---|
PRICE | Calculates the price of a bond given yield, maturity, and other details. |
XIRR | Determines the internal rate of return for irregular cash flows. |
DURATION | Computes the Macaulay duration of a bond. |
Want to Learn More?
For more information, check out the official Microsoft documentation for YIELD 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.
The YIELD function calculates the annual yield (rate of return) of a security that pays periodic interest.
The function requires settlement and maturity dates, coupon rate, price, redemption value, frequency, and an optional day count basis.
Yes, the function supports annual, semi-annual, and quarterly coupon payments through the frequency parameter.
The basis determines how days are counted between periods, impacting yield accuracy. For example, “US 30/360” assumes 30-day months and 360-day years.
Errors often occur due to invalid date ranges, mismatched parameters, or incorrect frequency values.