TBILL 05-10-2020 18:04 PM - last edited 05-10-2020 18:05 PM

[Greg_Deckler]

In my recent quest to create or catalog as many DAX equivalents for Excel functions here are the three TBILL functions.

• TBILLPRICE
• TBILLYIELD
• TBILLEQ

Pretty simple even if, again, the documentation is incorrect. The documentation for TBILLEQ states:

TBILLEQ is calculated as TBILLEQ = (365 x rate)/(360-(rate x DSM)), where DSM is the number of days between settlement and maturity computed according to the 360 days per year basis.

Except as far as I can tell DSM isn't calculated that way since the only way I can get the answer to come out correctly is just normal date math. Eh.

TBILLPRICE = 
    VAR __Settlement = [Settlement]
    VAR __MaxMaturity =
            VAR __Date = EOMONTH(__Settlement,12)
        RETURN
            DATE(YEAR(__Date),MONTH(__Date),DAY(__Settlement))
    VAR __Maturity = MINX( { [Maturity], __MaxMaturity }, [Value] )
    VAR __Discount = [Discount]
    VAR __DSM = ( __Maturity - __Settlement) * 1.
RETURN
    100 * ( 1 - (__Discount * __DSM) / 360)


TBILLYIELD = 
    VAR __Settlement = [Settlement]
    VAR __MaxMaturity =
            VAR __Date = EOMONTH(__Settlement,12)
        RETURN
            DATE(YEAR(__Date),MONTH(__Date),DAY(__Settlement))
    VAR __Maturity = MINX( { [Maturity], __MaxMaturity }, [Value] )
    VAR __Pr = [TBILLPRICE]
    VAR __DSM = ( __Maturity - __Settlement) * 1.
RETURN
    ( 100 - __Pr ) / __Pr * 360 / __DSM


TBILLEQ = 
    VAR __Settlement = [Settlement]
    VAR __MaxMaturity =
            VAR __Date = EOMONTH(__Settlement,12)
        RETURN
            DATE(YEAR(__Date),MONTH(__Date),DAY(__Settlement))
    VAR __Maturity = MINX( { [Maturity], __MaxMaturity }, [Value] )
    VAR __Discount = [TBILLYIELD]
    VAR __DSM = (__Maturity - __Settlement) * 1.
RETURN
    ( 365 * __Discount) / ( 360 - ( __Discount * __DSM) )

eyJrIjoiMDQzMWY3YWMtY2FkYS00ZjZlLWJkNTUtMzI4OTcwOWIxN2VlIiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9