Daily DAX : Day 214 YIELDMAT

 The YIELDMAT function in Power BI DAX (Data Analysis Expressions) calculates the annual yield of a security that pays interest at maturity, such as a zero-coupon bond or a Treasury bill. It is part of the financial functions in DAX, designed to assist with financial calculations.

Syntax

YIELDMAT(settlement, maturity, issue, rate, pr, [basis])

Parameters

    settlement: The date when the security is purchased (must be a valid date).

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

    issue: The date when the security was issued.

    rate: The security's annual coupon rate (interest rate).

    pr: The security's price per $100 face value.

    basis (optional): The day count basis for the calculation (default is 0 if omitted). Options are:

        0: US (NASD) 30/360

        1: Actual/actual

        2: Actual/360

        3: Actual/365

        4: European 30/360

Return Value

The YIELDMAT function returns the annual yield as a decimal (e.g., 0.05 for 5%).

How It Works

The function calculates the yield based on the difference between the purchase price and the face value (or redemption value) of the security, factoring in the time between settlement and maturity, the coupon rate, and the day count basis. It is particularly useful for securities that do not pay periodic interest but instead pay a lump sum at maturity.

Use Case

Scenario: A financial analyst is evaluating the yield of a Treasury bill or zero-coupon bond purchased on a specific date. They need to determine the annual yield to compare it with other investment options.

Example:

Suppose you have a bond with the following details:

    Issue Date: January 1, 2024

    Settlement Date: July 1, 2024

    Maturity Date: December 31, 2024

    Coupon Rate: 0% (no periodic interest)

    Price: $98 per $100 face value

    Day Count Basis: Actual/actual (1)

In Power BI, you can use the YIELDMAT function to calculate the yield:

dax

BondYield = YIELDMAT(

    DATE(2024, 7, 1),   // Settlement

    DATE(2024, 12, 31), // Maturity

    DATE(2024, 1, 1),   // Issue

    0,                  // Rate (0% for zero-coupon)

    98,                 // Price per $100 face value

    1                   // Basis (Actual/actual)

)

Result: The function might return a yield of approximately 0.0408 (or 4.08%), indicating the annualized return on the bond if held to maturity.

Practical Applications

    Investment Analysis: Compare the yield of bonds or securities with different maturities or prices to make informed investment decisions.

    Portfolio Management: Calculate yields for a portfolio of fixed-income securities to assess overall performance.

    Financial Reporting: Generate reports in Power BI to display the yield of securities for stakeholders or regulatory purposes.

Notes

    Ensure all dates are valid and in the correct format (use DATE function in DAX for clarity).

    The settlement date must be after the issue date, and the maturity date must be after the settlement date.

    If the basis is omitted, it defaults to US 30/360, which may affect the yield calculation depending on the security type.

    This function assumes the security pays interest at maturity, so it’s not suitable for securities with periodic coupon payments (use YIELD for those).