XIRR with calculated terminal value (face value)

DVA496 — Mon, 11 Sep 2023 09:09:37 GMT

Hi,

Im trying to calculate the XIRR for CFs in which a terminal value is added at the cutoff date. The original table JointforCFs contain Asset, Investor, Date and Cashflows. The cutoff date is a filter coming from a separate table VALUATIONDATE so I use a measure for it [S]. The Cashflows to consider are from inception (since first cashflow different from 0 depending on investor and asset) till cutoffdate, for this I use a measure [B&S CF] which is based on the measure [Consider B&S]. The terminal value is also a measure [Fair Value] based on CFs from the cutoffdate onwards [DDM CF]. Another measure display the B&S cashflow plus terminal value [B&S + FV] but when I tried to use this cashflows and modified date column which is a measure [BS Date] for the XIRR [IRR] it says cant display visual. Could anyone help me? Ive been trying for weeks now.

Here is how the original table looks like (the CFs have a lot of zeros at the beggining because it join several assets and investors whose starting date differs)

This is how the measures looklike (B&H CF is the original CF column from the orignal table) Cutoff dateselected as an example is 30.09.2023

Here are the mentioned measures:

IRR = CALCULATE(XIRR('Joint for CFs', 'Joint for CFs'[B&S + FV], 'Joint for CFs'[BS date]))

BS date = SWITCH(true(), [Date base] <= [Min date], [Min date],

[Date base] <= [S], [Date base],

[S])

B&S + FV = CALCULATE([FV CF]+[B&S CF])

DDM CF = [Consider DDM]*'Joint for CFs'[CFS total base]

Fair Value = CALCULATE(XNPV('Joint for CFs','Joint for CFs'[DDM CF],'Joint for CFs'[DDM Date], AVERAGE('Joint for CFs'[COE])),'Joint for CFs'[Type] = "Total")

Consider B&S = IF(DATEDIFF(SELECTEDVALUE('Joint for CFs'[Date]),[S],DAY)>=0,1,0)

B&S CF = [Consider B&S]*'Joint for CFs'[CFS total base]

S = SELECTEDVALUE(ValuationDate[Dates])

topic XIRR with calculated terminal value (face value) in DAX Commands and Tips

XIRR with calculated terminal value (face value)