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:
Based on the above as an example, InvestmentA will have a fair value/terminal value of $75,500 at December 31, 2019.
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.
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.
|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
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:
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
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.
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.
Check out new user group experience and if you are a leader please create your group
Click here to read more about the April 2021 Updates!
100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.
See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.