Daily DAX : Day 286 YIELD

The **YIELD** function in Power BI DAX (Data Analysis Expressions) is a financial function that calculates the **yield** of a security, such as a bond, that pays periodic interest. It represents the annual return an investor would receive if the security is held until maturity, expressed as a percentage. This function is particularly useful in financial analysis for evaluating fixed-income securities like bonds.

### Syntax

```dax

YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])

```

### Parameters

1. **settlement**: The date when the buyer purchases the security (must be a valid date).

2. **maturity**: The date when the security expires (must be a valid date after the settlement date).

3. **rate**: The annual coupon rate of the security (as a decimal, e.g., 5% = 0.05).

4. **pr**: The price of the security per $100 face value.

5. **redemption**: The redemption value of the security per $100 face value (typically 100 for bonds redeemed at par).

6. **frequency**: The number of coupon payments per year:

   - 1 = Annual

   - 2 = Semiannual

   - 4 = Quarterly

7. **basis** (optional): The day-count convention for interest calculation:

   - 0 or omitted: US (NASD) 30/360

   - 1: Actual/actual

   - 2: Actual/360

   - 3: Actual/365

   - 4: European 30/360

### Return Value

The function returns the **yield** as a decimal (e.g., 0.057 for 5.7%).

### How It Works

The YIELD function calculates the yield by solving for the interest rate that equates the present value of the bond’s future cash flows (coupon payments and redemption value) to its current price. It uses an iterative numerical method, as the calculation involves solving a complex equation.

### Use Case

The YIELD function is commonly used in **bond valuation** and **investment analysis** to:

- Compare the returns of different bonds.

- Assess whether a bond is a good investment based on its yield relative to its price and coupon rate.

- Analyze the impact of changes in market conditions (e.g., price fluctuations) on bond yields.

- Support portfolio management decisions by evaluating fixed-income securities.

### Example

Suppose you have a bond with the following details:

- **Settlement Date**: January 1, 2025

- **Maturity Date**: January 1, 2030

- **Coupon Rate**: 5% (0.05)

- **Price**: $95 per $100 face value

- **Redemption Value**: $100

- **Frequency**: Semiannual (2)

- **Basis**: US 30/360 (0)

In Power BI DAX, you can calculate the yield as follows:

```dax

BondYield = YIELD(

    DATE(2025, 1, 1),  // settlement

    DATE(2030, 1, 1),  // maturity

    0.05,              // rate

    95,                // pr

    100,               // redemption

    2,                 // frequency

    0                  // basis

)

```

**Result**: The function might return approximately **0.0616** (or 6.16%), indicating the bond’s yield based on the given parameters.

### Practical Example in Power BI

Assume you have a table `BondData` with columns for settlement, maturity, rate, price, redemption, frequency, and basis. You can create a **calculated column** or **measure** to compute the yield for each bond:

```dax

YieldMeasure = 

CALCULATE(

    YIELD(

        BondData[Settlement],

        BondData[Maturity],

        BondData[Rate],

        BondData[Price],

        BondData[Redemption],

        BondData[Frequency],

        BondData[Basis]

    )

)

```

This measure can be used in visuals (e.g., tables or charts) to analyze bond yields across a portfolio.

### Notes

- Ensure that the settlement and maturity dates are valid and stored as date data types in Power BI.

- The function assumes the bond pays periodic interest (coupons). It is not suitable for zero-coupon bonds (use **YIELDDISC** for those).

- If the inputs are invalid (e.g., settlement date ≥ maturity date), the function returns an error.

- The yield calculation is sensitive to the **basis** parameter, so choose the appropriate day-count convention based on the market or region.

### Use Case Example

An investment analyst might use the YIELD function to:

- Compare the yields of bonds with different coupon rates and prices to identify undervalued or overvalued securities.

- Monitor how changes in a bond’s market price affect its yield over time.

- Build a Power BI dashboard to visualize yields across a bond portfolio for reporting to stakeholders.

By leveraging the YIELD function, financial professionals can make data-driven decisions about bond investments within Power BI’s interactive environment.