Portfolio management: Calculaiton of weight averaged maturities

[AlejandroVazque]

Hi, I have the task to analyze the evolution of the days to maturity of my portfolio over time.

I have a table with the Cash flows for each instrument as follows 

ID	INTR ID	CF Amount	CF Date (dd/mm/yyy)	CF Disb. date dd/mm/yyyy)
1	001	100,000	01/02/2021	01/01/2021
2	001	150,000	01/03/2021	01/01/2021
3	001	50,000	15/04/2021	01/01/2021
4	001	100,000	18/04/2021	01/01/2021
5	002	30,000	30/06/2021	15/02/2021
6	002	70,000	30/10/2021	15/02/2021
7	002	100,000	01/01/2022	15/02/2021
8	002	100,000	01/01/2023	15/02/2021
9	002	50,000	01/01/2024	15/02/2021
10	003	50,000	01/02/2020	01/01/2020
11	003	100,000	01/03/2020	01/01/2020
12	003	50,000	01/04/2020	01/01/2020
13	004	100,000	01/01/2021	01/01/2019
14	004	100,000	01/06/2020	01/01/2019
15	004	100,000	01/01/2019	01/01/2019
16	005	50,000	15/02/2022	15/02/2021
17	005	50,000	15/02/2023	15/02/2021
18	005	100,000	15/02/2024	15/02/2021
19	005	100,000	15/02/2025	15/02/2021

My main objective is to prepare a Graph with dates from 2018 to 2025 showing the evolution of the maturity of the porfolio

I have created already the following measures with a date analysis table (table called Query Calendar)

Measure of Tenor days over time:

CF tenor days =

Var CFdate = selectedvalue('CF Date (dd/mm/yyy))

Var Analysisdate = selectedvalue('Query Calendar'[Analysis Date])

Var Disbdate = selectedvalue(CF Disb. date dd/mm/yyyy) )

Var AnalysisvsDisbdate = datediff(Analysisdate,Disbdate,DAY)

Var Result = if(datediff(Analysisdate,CFdate,DAY)<0,0,if(AnalysisvsDisbdate>0, 0, datediff(Analysisdate,CFdate,DAY)))

// The first condition is that I cannot have negative tenor days

// The second condition is that as loang as the Instrument dibsursement date (CF Disb. date dd/mm/yyyy) is after the Analysis date, the Tenor is zero as the instrument has not existed before

Return

Result

I then multiply the tenor days x the CF Amount.

I manage to obtain well the tenor graph (tenor changes every daby each cash flow, but now what i need is to add tenor days for each instrument and then also for the portfolio. Ideally I shoud have

Tenor for instruments (INTR ID) = Sum (Tenor of each cash flow * Amount of each cash flow) / Total amount of EUR per instrument

Tenor of Portfolio = Summ all (Tenors of all cash flows * Amounts of each cash flow) / Total Amount or EUR)

Appreicate your help

Alejandro

[AlejandroVazque · Accepted solution]

Hi Rico,

Thanks for your interest. I have managed to sort this out with the following measures : 

var Lastvisibledateanalysis = MAX('Query Calendar'[Date])

var Results =

CALCULATE(

SUMX(DISTINCT( 'DIM DS_CF'[ID]),

Var CFdate = CALCULATE( (Selectedvalue('DIM DS_CF'[0003 CF Realized Date])))

Var Analysisdate = CALCULATE( (selectedvalue('Query Calendar'[Date])))

Var Disbdate = CALCULATE( (selectedvalue('DIM DS_CF'[0001 CF Disb date])))

Var ActualEUR = CALCULATE( (selectedvalue('DIM DS_CF'[0004EU2P Principal only payments in EUR (Actual)])))

Var AnalysisvsDisbdate = datediff(Analysisdate,Disbdate,DAY)

Var AnalysisCDate = datediff(Analysisdate,CFdate,DAY)

Var ResultTenor = if(datediff(Analysisdate,CFdate,DAY)<0,0,if(AnalysisvsDisbdate>=0, 0, datediff(Analysisdate,CFdate,DAY)))

Var ResultTenorxEUR = ActualEUR * ResultTenor

Var TotalAmEUR = Calculate(sum('DIM DS_CF'[0004EU2 Principal in EUR (Actual)]),'DIM DS_CF'[0003 CF Realized Date]>Lastvisibledateanalysis,'DIM DS_CF'[0001 CF Disb date]<Lastvisibledateanalysis)

Var InstrMat = divide(ResultTenorxEUR,TotalAmEUR,0)

// Return ResultTenorxEUR),

Return ResultTenor),

CROSSFILTER('DIM DS_CF'[0003 CF Realized Date],'Realized Dates'[Date],None)

)

Return

Results

[v-rzhou-msft]

Hi @AlejandroVazque 

What does Analysis date table looks like? Is there any relationship between Analysis data table and the table as above? Your calculate logic is complex. Could you share a sample pbix without sensitive data with us by your Onedrive for Business? And you can show us the result you want by screenshot. This will make it easier for us to understand your requirement.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.