cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kdrizzy92
Regular Visitor

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

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:

 

CompanyNameInvestmentNameDate Cashflow  InvestmentBalance 
CompanyAInvestmentA1/02/2019                        (50,000.00)                     50,000.00
CompanyAInvestmentA1/06/2019                        (20,000.00)                     20,000.00
CompanyAInvestmentA2/23/2019                             1,000.00                     (1,000.00)
CompanyAInvestmentA2/27/2019                             4,000.00                     (4,000.00)
CompanyAInvestmentA3/05/2019                           (2,500.00)                       2,500.00
CompanyAInvestmentA3/23/2019                             3,000.00                       1,000.00
CompanyAInvestmentA3/31/2019                                          -                         1,500.00
CompanyAInvestmentA4/15/2019                           (4,000.00)                       4,000.00
CompanyAInvestmentA5/22/2019                           (3,000.00)                       3,000.00
CompanyAInvestmentA6/04/2019                             2,100.00                     (2,000.00)
CompanyAInvestmentA6/30/2019                                          -                          (500.00)
CompanyAInvestmentA8/01/2019                           (2,000.00)                       2,000.00
CompanyAInvestmentA8/11/2019                           (2,000.00)                       2,000.00
CompanyAInvestmentA9/30/2019                                          -                         1,000.00
CompanyAInvestmentA10/05/2019                             3,500.00                     (3,500.00)
CompanyAInvestmentA11/24/2019                             3,000.00                     (3,000.00)
CompanyAInvestmentA11/30/2019                           (1,000.00)                       1,000.00
CompanyAInvestmentA12/02/2019                           (1,500.00)                       1,500.00
CompanyAInvestmentA12/31/2019                             1,000.00                        (500.00)
CompanyAInvestmentB1/05/2019                        (15,000.00)                     15,000.00
CompanyAInvestmentB3/25/2019                           (2,000.00)                       2,000.00
CompanyAInvestmentB3/31/2019                                          -                         1,000.00
CompanyAInvestmentB4/06/2019                           (1,000.00)                       1,000.00
CompanyAInvestmentB6/30/2019                                          -                             500.00
CompanyAInvestmentB7/21/2019                             4,000.00                     (4,000.00)
CompanyAInvestmentB8/14/2019                             3,000.00                     (3,000.00)
CompanyAInvestmentB9/30/2019                                          -                         2,000.00
CompanyAInvestmentB11/02/2019                           (1,500.00)                       1,000.00
CompanyAInvestmentB12/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!

 

6 REPLIES 6
EGolanowski
Regular Visitor

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
Not applicable

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. 

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

CompanyAInvestmentAJanuary 2, 2019-5000050000150000
CompanyAInvestmentBJanuary 5, 2019-15000150002015000
CompanyAInvestmentAJanuary 6, 2019-2000020000270000
CompanyAInvestmentAFebruary 23, 20191000-1000369000
CompanyAInvestmentAFebruary 27, 20194000-4000465000
CompanyAInvestmentAMarch 5, 2019-25002500567500
CompanyAInvestmentAMarch 23, 201930001000668500
CompanyAInvestmentBMarch 25, 2019-200020002117000
CompanyAInvestmentAMarch 31, 201901500770000
CompanyAInvestmentBMarch 31, 2019010002218000
CompanyAInvestmentBApril 6, 2019-100010002319000
CompanyAInvestmentAApril 15, 2019-40004000874000
CompanyAInvestmentAMay 22, 2019-30003000977000
CompanyAInvestmentAJune 4, 20192100-20001075000
CompanyAInvestmentAJune 30, 20190-5001174500
CompanyAInvestmentBJune 30, 201905002419500
CompanyAInvestmentBJuly 21, 20194000-40002515500
CompanyAInvestmentAAugust 1, 2019-200020001276500
CompanyAInvestmentAAugust 11, 2019-200020001378500
CompanyAInvestmentBAugust 14, 20193000-30002612500
CompanyAInvestmentASeptember 30, 2019010001479500
CompanyAInvestmentBSeptember 30, 2019020002714500
CompanyAInvestmentAOctober 5, 20193500-35001576000
CompanyAInvestmentBNovember 2, 2019-150010002815500
CompanyAInvestmentANovember 24, 20193000-30001673000
CompanyAInvestmentANovember 30, 2019-100010001774000
CompanyAInvestmentADecember 2, 2019-150015001875500
CompanyAInvestmentADecember 31, 20191000-5001975000
CompanyAInvestmentBDecember 31, 20190-10002914500

 

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
Not applicable

@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:

Data Model.pngThen 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

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
Not applicable

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. 

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

April Update

Check it Out!

Click here to read more about the April 2021 Updates!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

secondImage

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.