Forum Discussion

DVA496's avatar
DVA496
Frequent Visitor
2 years ago

XIRR with calculated terminal value (face value)

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])
Tips and Tricks
Tutorial Requests
No RepliesBe the first to reply