Daily DAX : Day 230 RECEIVED

The RECEIVED function in DAX returns the total amount received at maturity for a fully invested security, accounting for the initial investment and the discount rate. It’s commonly used in financial analysis to determine the payout of securities like zero-coupon bonds, where no periodic interest is paid, and the return is realized at maturity.

Syntax

dax

RECEIVED(<settlement>, <maturity>, <investment>, <discount>[, <basis>])

    settlement: The date the security is purchased (after the issue date). Must be a valid date.

    maturity: The date the security expires and the amount is received. Must be a valid date and later than the settlement date.

    investment: The amount invested in the security (must be greater than 0).

    discount: The security’s discount rate (must be greater than 0), expressed as a percentage or decimal (e.g., 4.5% or 0.045).

    basis (optional): The day count basis for calculating time periods. If omitted, defaults to 0 (US NASD 30/360). Valid values are:

        0: US (NASD) 30/360

        1: Actual/Actual

        2: Actual/360

        3: Actual/365

        4: European 30/360

Formula

The RECEIVED function uses the following formula to calculate the amount received at maturity:

RECEIVED = investment / (1 - (discount * DIM / B))

Where:

    DIM: Number of days from settlement to maturity (based on the specified day count basis).

    B: Number of days in a year, according to the day count basis (e.g., 360 for US NASD 30/360, 365 for Actual/365).

Return Value

A single decimal value representing the total amount received at maturity.

Remarks

    Dates are stored as sequential serial numbers in DAX, with December 30, 1899, as day 0. For example, January 1, 2008, is 39,448 days after this base date.

    The function assumes the security is held to maturity and fully invested (no partial redemption).

    Errors occur if:

        Settlement or maturity dates are invalid.

        Investment or discount is less than or equal to 0.

        Basis is not 0, 1, 2, 3, or 4.

        Settlement date is greater than or equal to the maturity date.

Use Case

Scenario: A financial analyst needs to calculate the amount received at maturity for a zero-coupon bond purchased on April 1, 2017, with a maturity date of March 31, 2018. The investment amount is $1,000, and the discount rate is 4.5%. The US (NASD) 30/360 day count basis is used.

DAX Formula:

Assume the data is in a table called Securities with columns SettlementDate, MaturityDate, Investment, and DiscountRate. You can create a calculated column or measure:

dax

ReceivedAmount = RECEIVED(

    Securities[SettlementDate],

    Securities[MaturityDate],

    Securities[Investment],

    Securities[DiscountRate],

    0

)

Example Data:

    Settlement: April 1, 2017

    Maturity: March 31, 2018

    Investment: $1,000

    Discount: 4.5% (or 0.045)

    Basis: 0 (US NASD 30/360)

Calculation:

    DIM = 360 days (using 30/360 basis for one year).

    B = 360 (for US NASD 30/360).

    Discount = 0.045.

    Formula: RECEIVED = 1000 / (1 - (0.045 * 360 / 360)) = 1000 / (1 - 0.045) = 1000 / 0.955 ≈ 1047.12.

Result: The amount received at maturity is approximately $1,047.12.

Application:

    Add this as a calculated column in the Securities table to show the maturity amount for each bond.

    Use in a Power BI report to visualize the returns on multiple securities, comparing their maturity payouts against initial investments.

    Useful for financial modeling, portfolio analysis, or reporting for investments like bonds or treasury bills.

Practical Example in Power BI

    Data Model: Ensure your table has columns for settlement date, maturity date, investment amount, and discount rate.

    Calculated Column: Add the RECEIVED formula as shown above in the Power BI Data view under the Modeling tab.

    Visualization: Create a table or chart in a Power BI report to display the ReceivedAmount alongside other metrics, such as profit (ReceivedAmount - Investment).

    Context: Use filters or slicers to analyze specific securities or time periods.

Limitations

    Not supported in DirectQuery mode for calculated columns or row-level security (RLS) rules.

    Requires valid date formats and a positive investment and discount rate.

    Assumes a single payout at maturity, so it’s not suitable for securities with periodic interest payments (use ACCRINTM for accrued interest at maturity).