XIRR at input date that includes a terminal value rolled forward to that same day

[kdrizzy92]

Hi all,

I was struggling mightily with this and was hopeful you'd be able to help!

I'm trying to calculate investment IRRs based on irregular cashflows and a terminal value at quarter-end. The way our system is set up is that there is an account called InvestmentBalance that keeps track of the incremental changes in the asset's values. I was wondering if in PBI, is it possible to basically insert a row into my dataset at each quarter-end that calculates the terminal value at that date for each investment? From there, I'd then be able to have PBI calculate the IRR at each quarter-end based on the date I input.

Below is my dataset:

CompanyName	InvestmentName	Date	Cashflow	InvestmentBalance
CompanyA	InvestmentA	1/02/2019	(50,000.00)	50,000.00
CompanyA	InvestmentA	1/06/2019	(20,000.00)	20,000.00
CompanyA	InvestmentA	2/23/2019	1,000.00	(1,000.00)
CompanyA	InvestmentA	2/27/2019	4,000.00	(4,000.00)
CompanyA	InvestmentA	3/05/2019	(2,500.00)	2,500.00
CompanyA	InvestmentA	3/23/2019	3,000.00	1,000.00
CompanyA	InvestmentA	3/31/2019	-	1,500.00
CompanyA	InvestmentA	4/15/2019	(4,000.00)	4,000.00
CompanyA	InvestmentA	5/22/2019	(3,000.00)	3,000.00
CompanyA	InvestmentA	6/04/2019	2,100.00	(2,000.00)
CompanyA	InvestmentA	6/30/2019	-	(500.00)
CompanyA	InvestmentA	8/01/2019	(2,000.00)	2,000.00
CompanyA	InvestmentA	8/11/2019	(2,000.00)	2,000.00
CompanyA	InvestmentA	9/30/2019	-	1,000.00
CompanyA	InvestmentA	10/05/2019	3,500.00	(3,500.00)
CompanyA	InvestmentA	11/24/2019	3,000.00	(3,000.00)
CompanyA	InvestmentA	11/30/2019	(1,000.00)	1,000.00
CompanyA	InvestmentA	12/02/2019	(1,500.00)	1,500.00
CompanyA	InvestmentA	12/31/2019	1,000.00	(500.00)
CompanyA	InvestmentB	1/05/2019	(15,000.00)	15,000.00
CompanyA	InvestmentB	3/25/2019	(2,000.00)	2,000.00
CompanyA	InvestmentB	3/31/2019	-	1,000.00
CompanyA	InvestmentB	4/06/2019	(1,000.00)	1,000.00
CompanyA	InvestmentB	6/30/2019	-	500.00
CompanyA	InvestmentB	7/21/2019	4,000.00	(4,000.00)
CompanyA	InvestmentB	8/14/2019	3,000.00	(3,000.00)
CompanyA	InvestmentB	9/30/2019	-	2,000.00
CompanyA	InvestmentB	11/02/2019	(1,500.00)	1,000.00
CompanyA	InvestmentB	12/31/2019	-	(1,000.00)

Based on the above as an example, InvestmentA will have a fair value/terminal value of $75,500 at December 31, 2019.

Thank you!

[Anonymous]

Hi there,

I am facing this exact problem and was wondering if you were able to resolve it? Would you be willing to share your solution? I am trying to include a terminal value and leverage an input date just as you have stated. I have been stuck on this for a while and would love any tips to help me move it forward.

Thank you!

[Anonymous]

I've been working on this type of thing for a while.  The first thing I'd do is create a separate table that will give you the quarterly terminal values (power query works well with this). Then from there you can start to build your function. But there's a little ( ok, a lot) else that will go into it. 

But first things first, try and build the table of terminal values by quarter. Also be sure to have a dedicated calendar table as well, as whatever date (presumably quarter end) will be on rows, or any other filter) will be used to gather all the cash flows up till that and then grab the terminal value on that specific date. I create two calculated tables in my function (on to grab the cash flows and one to grab the terminal value) then I used union to mash them together and feed that to xirr function. But that's a few steps ahead but I used my calendar table as filter for CALCULATETABLE for the Cash Flows and Ending Net Asset Value. 

Give it a try and see what you can come up with. It's definitely not easy, but once you get it working you can generate IRR's on the fly for basically any type of cut of data, which is amazing in itself. Well, at least I think so because I always had to run the irr manually and that was a giant pain, so definitely worth it in my view. 

[kdrizzy92]

Hi @Anonymous !

Thank you so much for your reply. I've been at this now for the past few days, and was just wondering if I could get a bit more help here.

I was able to calculate the rolling NAV with the help of an index column. It is now in my table as for the below.

CompanyNameInvestmentNameDateCashflowInvestmentBalanceIndexNAVatDate

CompanyA	InvestmentA	January 2, 2019	-50000	50000	1	50000
CompanyA	InvestmentB	January 5, 2019	-15000	15000	20	15000
CompanyA	InvestmentA	January 6, 2019	-20000	20000	2	70000
CompanyA	InvestmentA	February 23, 2019	1000	-1000	3	69000
CompanyA	InvestmentA	February 27, 2019	4000	-4000	4	65000
CompanyA	InvestmentA	March 5, 2019	-2500	2500	5	67500
CompanyA	InvestmentA	March 23, 2019	3000	1000	6	68500
CompanyA	InvestmentB	March 25, 2019	-2000	2000	21	17000
CompanyA	InvestmentA	March 31, 2019	0	1500	7	70000
CompanyA	InvestmentB	March 31, 2019	0	1000	22	18000
CompanyA	InvestmentB	April 6, 2019	-1000	1000	23	19000
CompanyA	InvestmentA	April 15, 2019	-4000	4000	8	74000
CompanyA	InvestmentA	May 22, 2019	-3000	3000	9	77000
CompanyA	InvestmentA	June 4, 2019	2100	-2000	10	75000
CompanyA	InvestmentA	June 30, 2019	0	-500	11	74500
CompanyA	InvestmentB	June 30, 2019	0	500	24	19500
CompanyA	InvestmentB	July 21, 2019	4000	-4000	25	15500
CompanyA	InvestmentA	August 1, 2019	-2000	2000	12	76500
CompanyA	InvestmentA	August 11, 2019	-2000	2000	13	78500
CompanyA	InvestmentB	August 14, 2019	3000	-3000	26	12500
CompanyA	InvestmentA	September 30, 2019	0	1000	14	79500
CompanyA	InvestmentB	September 30, 2019	0	2000	27	14500
CompanyA	InvestmentA	October 5, 2019	3500	-3500	15	76000
CompanyA	InvestmentB	November 2, 2019	-1500	1000	28	15500
CompanyA	InvestmentA	November 24, 2019	3000	-3000	16	73000
CompanyA	InvestmentA	November 30, 2019	-1000	1000	17	74000
CompanyA	InvestmentA	December 2, 2019	-1500	1500	18	75500
CompanyA	InvestmentA	December 31, 2019	1000	-500	19	75000
CompanyA	InvestmentB	December 31, 2019	0	-1000	29	14500

I'm trying to figure out combining the two now into a function, but I've been stuck on it for awhile now and can't quite figure out what I'm doing wrong. I'm getting an error message, "Calculation error in measure: A table of multiple values was supplied where a single value was expected". Copy of pbix here

[Anonymous]

@kdrizzy92 ,

Take a look at the attached. I don't think it's 100% complete but it's a good start.  Here's a few things I did:

1. Created a Dimension table for Company Name and Investment Name. Want to use those columns as filters, never want to filter using a fact table since that would be a performance hit
2. Created a separate NAV table. Probably not 100% necessary, but I find it easier to have the ENAVs and the cashflows separted

So here's how the data model looks:

Then back in the report view, with Dates on rows from your Date Table, and Company Name and InvestmentName from our new Dimension tables, can use this IRR function:

IRR v2 = 

//Create a table that is just the ENAV value for the current date in the current filter context
VAR __EnavTable=
	CALCULATETABLE(
		NavTable,
			FILTER(
				ALL( 'Date'),
				MAX('Date'[Date]) = 'Date'[Date]
			)
		)

//Create a table that has all the cashflows (not the terminal value) from start of time till the date in the current filter context
//Need to use Select Columns for the Union Function. Union Function Requires the same amount of columns
VAR __CFTable=
SELECTCOLUMNS(
    	CALCULATETABLE(
		IRR,
			FILTER(
				ALL( 'Date'),
				MAX( 'Date'[Date]) >= 'Date'[Date]
            )
    ),
    "CompanyName", IRR[CompanyName], "InvestmentName", IRR[InvestmentName], "Date", IRR[Date],"CashFlow", IRR[Cashflow])

//Need to make one table out of the above tables since want all the cashflows preceeding the current date in the current filter context
//and only want the ENAV value for the date from the context, not all the preeceding ones
Var __Union =
    UNION(  __CFTable,__EnavTable)

//Feed the columns from our union table to the XIRR function
var __XIRR=
      IFERROR(
            XIRR( __Union,[Cashflow],[Date]),BLANK()
        
        
    )

RETURN
__XIRR

There's notes in the code itself, but basically it says look at the date I'm currently in (along with the Company, Investment, or whatever filters are applied). Then in the ENVA table, give me a table where it is only the ENAV for all those above filters. Then the second table applies the same filters, but to the IRR table and gives a table where it is all the cashflows up to, and including, the date in the current filter context.

So with those two tables, we want to use merge them into one so we use UNION. and with that merged table we can use as the columns for the IRR function. 

I dont know enough about your data, or if the data was random, but I did a quick check in excel and looked good, but in no way did I do a deep check.  There are a lot of little things that I have found to account for when doing IRRs and such. (such as what to do when the initial cash flow and the date you want the IRR is < 1 year....) but that's putting the cart before the horse. 

So take a look at the attached and let me know what you think

https://1drv.ms/u/s!Amqd8ArUSwDS3VZLLHbm6kUvXIMD?e=0Bvy6L

[joka22]

Hi Anonymous,

I have the exact same problem! Did you solve it at the end? And how is your NAV Table looking? Is there a NAV for every date and are the dates thes same as in the CF table? 

Kind regards,

Joka

[kdrizzy92]

Awesome stuff, @Anonymous !

I was arriving at the same IRR's as yours in my earlier iterations of the formulas, but the one thing behind it was that it didn't seem to pick up the terminal value at the reporting-end date in the calculation. This is why we were getting IRRs of about -97.53% when we were expecting a 10.31% with a terminal value of $89,500.

I'm looking into it still though, must be a reason why it's not getting captured in it! I'll let you know if I can figure it out. Thank you again for all the help though, Nick. 

[Anonymous]

I'm sure there is a reason it's not picking it up, could be a data type issue or something similar. not sure. Mess around with the 1st CalculateTable and see what shakes out. I used more or less the same set up in my file, and all works well, but obviously the data isnt the same.

Try and troubleshoot and see what you can find. If i get some time I'll take a look as well.