DISC 05-02-2020 13:04 PM

[Greg_Deckler]

In my recent quest to create or catalog as many DAX equivalents for Excel functions, this one (Discount Rate) or DISC took way longer than it should have. This is due to the fact that the Excel documentation on the function is pretty much garbage. So much so that the answer provided for the example is actually wrong and not what Excel returns at all. Yeah, fun figuring that out...

DISC = 
    VAR __BasisTable = 
        ADDCOLUMNS(
            GENERATESERIES(YEAR([Settlement]),YEAR([Maturity]),1),
            "Days",( DATE([Value],12,31) - DATE([Value],1,1) ) * 1. + 1
        )
    VAR __B = 
        SWITCH([Basis],
            1,AVERAGEX(__BasisTable,[Days]),
            2,360,
            3,365,
            4,360,
            360
        )
RETURN
    DIVIDE(
        ([Redemption] - [Price]),
        [Redemption],
        0
    )
    *
    DIVIDE(
        __B,
        ([Maturity] - [Settlement]) * 1.
    )

eyJrIjoiNWExODY3NGYtOTZiMy00MzI3LTgxMTktZTRjZmYyMzNjYzc3IiwidCI6IjRhMDQyNzQzLTM3M2EtNDNkMi04MjdiLTAwM2Y0YzdiYTFlNSIsImMiOjN9