Day Count Conventions related calculations in PowerBI (e.g. Actual/365, 30/360 (ISDA)...)

[MelData]

Hello,

I've been trying to calculate interest over a specific period for transaction which have specific day count conventions. 

I'm aware of the DAX Yearfrac function, however it only covers a few day count convention.

I'm having difficulties for instance with replicating the 30/360 (ISDA) notation which involves 12 30-day months subject to specific rules if for instance the last day of the accrual period falls on the 31st of a month. For the "Actual/Actual (ISDA)" notation, I'm facing difficulties with inserting a leap year check into the formula. 

The best description of the specifities for the current day count conventions can be found here. If anyone can propose a way to replicate all of these day count conventions to interest calculation of a period of time into PowerBi (DAX or powerquery) that would be really appreciated

i've enclosed  a rule example below

Many many thanks for your support in this. I've been checking the forum, but I have not found this topic. 

[MelData]

this is the formula i've been using for the accrual bases that are covered in the Yearfrac function. But now I'm trying to replicate this calculation for all other convention which are not covered through this function. 

[v-frfei-msft]

Hi @MelData,

Refering to the online document. 

The Syntax YEARFRAC(<start_date>, <end_date>, <basis>)

As the description of basis, The type of day count basis to use. All arguments are truncated to integers. So I think you should update your formula accrodingly. (change 4 to 2, and 2 to 4 in your formula)

0 - US (NASD) 30/360

1 - Actual/actual

2 - Actual/360

3 - Actual/365

4 - European 30/360

One more thing,  could you please tell me what did you mean ? " trying to replicate this calculation for all other convention which are not covered through this function. " Kindly share more sample data to me together with excepted result to me.

Regards,

Frank

[MelData]

Hi Frank,

Thank you for your response 

I have indeed interchanged the bases in the Yearfrac function in my sample data. Thanks for your remark

My issue is that this DAX function doesn't cover several other day count conventions such as the conventions below

• 30/Actual(ISDA)
• 30/365
• Actual/Actual(ISDA)

For the 30/365 convention, I've found a workaround formula "[D2-D1 + 30*(M2-M1) + 360*(Y2-Y1)]/365" to calculate the accrual factor with the following hypotheses:

• Start date: D1/M1/Y1
• End date: D2/M2/Y2

For the "Actual/Actual(ISDA)" conventions however, things are a bit more complicated. If there is a leapyear in the calculation period (difference between End date & Start date),  then this accrued period falling in a leap year needs to be divided by 366 days. For instance, for calculation period with start date = 30 June 2016, end date = 1 July 2017 and 

accrual basis "Actual/Actual(ISDA)", I would like the following calculation to be replicated into DAX:

184["31 December 2016 - 30 June 2016"]/366 + 181["1 July 2017 - 1 January 2017"]/365

I will post a sample workbook and a pretty long formula which should help calculating the accrual factor for several conventions I've encountered , but I'm not sure this is the best way to achieve the result. I'm thus open to suggestions 🙂

[MelData]

here is the DAX formula I've built

Accrual Factor = 
    SWITCH('Investments'[Accrual basis];
        "Actual/360";('Investments'[End date]-'Investments'[Start date])/360;
        "30/360";IF(
                    AND(DAY('Investments'[End date])=31;DAY('Investments'[Start date])=31);
                        (30-MIN(30;30)+30*(MONTH('Investments'[End date])
                        -MONTH('Investments'[Start date]))
                        +360*(YEAR('Investments'[End date])
                        -YEAR('Investments'[Start date])))/360;
                    IF(AND(DAY('Investments'[End date])=31;DAY('Investments'[Start date])=30);
                        (30-MIN(DAY('Investments'[Start date]);30)
                        +30*(MONTH('Investments'[End date])
                        -MONTH('Investments'[Start date]))
                        +360*(YEAR('Investments'[End date])
                        -YEAR('Investments'[Start date])))/360;
                    IF(DAY('Investments'[Start date])=31;
                        (DAY('Investments'[End date])
                        -MIN(30;30)+30*(MONTH('Investments'[End date])
                        -MONTH('Investments'[Start date]))
                        +360*(YEAR('Investments'[End date])
                        -YEAR('Investments'[Start date])))/360;
                    (DAY('Investments'[End date])
                    -MIN(DAY('Investments'[Start date]);30)
                    +30*(MONTH('Investments'[End date])
                    -MONTH('Investments'[Start date]))
                    +360*(YEAR('Investments'[End date])-YEAR('Investments'[Start date])))/360
                    )));
        "30/Actual";IF(
                        OR(
                           DATE(YEAR('Investments'[Start date]);12;31)
                           -DATE(YEAR('Investments'[Start date]);1;1)*1+1=366;
                           DATE(YEAR('Investments'[End date]);12;31)
                           -DATE(YEAR('Investments'[End date]);1;1)*1+1=366);    
                        (DAY('Investments'[End date])
                        -DAY('Investments'[Start date])
                        +30*(MONTH('Investments'[End date])
                            -MONTH('Investments'[Start date]))
                        +360*(YEAR('Investments'[End date])-
                             -YEAR('Investments'[Start date]))
                        )/366;
                        (DAY('Investments'[End date])
                        -DAY('Investments'[Start date])
                        +30*(MONTH('Investments'[End date])
                            -MONTH('Investments'[Start date]))
                        +360*(YEAR('Investments'[End date])-
                             -YEAR('Investments'[Start date]))
                        )/365);
        "30/Actual(ISDA)";(31-DAY('Investments'[Start date])+30*(12-MONTH('Investments'[Start date])))
                            /(DATE(YEAR('Investments'[Start date]);12;31)
                             -DATE(YEAR('Investments'[Start date]);1;1)
                             +1)
                          +(31-DAY('Investments'[Start date])+30*(12-MONTH('Investments'[End date])))
                            /(DATE(YEAR('Investments'[End date]);12;31)
                             -DATE(YEAR('Investments'[End date]);1;1)
                             +1)
                          +(YEAR('Investments'[End date])-YEAR('Investments'[Start date]));
        "30/365";(DAY('Investments'[End date])-DAY('Investments'[Start date])
                 +30*(MONTH('Investments'[End date])-MONTH('Investments'[Start date]))
                 +360*(YEAR('Investments'[End date])-YEAR('Investments'[Start date]))
                 )/365;
        "Actual/365";('Investments'[End date]-'Investments'[Start date])/365;
        "Actual/Actual";('Investments'[End date]-'Investments'[Start date])
                        /(DATE(YEAR('Investments'[End date]);12;31)
                         -DATE(YEAR('Investments'[End date]);1;1)
                         +1);
        "Actual/Actual (ISDA)";DATE(YEAR('Investments'[Start date]);12;1)-'Investments'[Start date]
                            /(DATE(YEAR('Investments'[Start date]);12;31)
                             -DATE(YEAR('Investments'[Start date]);1;1)
                             +1)
                          +('Investments'[End date]-DATE(YEAR('Investments'[End date]);1;1))
                            /(DATE(YEAR('Investments'[End date]);12;31)
                             -DATE(YEAR('Investments'[End date]);1;1)
                             +1)
                          +(YEAR('Investments'[End date])-YEAR('Investments'[Start date]));
        "Actual/252";('Investments'[End date]-'Investments'[Start date])/252
;0)

[MelData]

a sample workbook can be found here:

https://expirebox.com/download/5d119235f3eb7b852b19b264f9c6a794.html

thank you