Dynamic XIRR
Hello - is there a way to have XIRR dynamically start from input date?
I.e. not having to adjust columns captures each time, if/when dates in cashflow change.
Modeling a 20 asset portfolio acquisition. This would save meaningful time.
Yes. I built one a year or so ago using address indirect. Set up an input box to select how many months I wanted the irr to be calculated on from the dcf and then it was just plug and play
Why in the world are you using XIRR?
=(1+IRR(net cash flows))^(1/12)-1
If your using quarterly then ^ (1/4), so on and so forth.
Not all months are 1/12 of a year
I've worked at a few blue chip firms and have plenty of friends at blue chip firms in the industry and I've NEVER seen anyone use XIRR outside of a college assignment. The difference in what you are describing is i) immeasurably small and ii) it would never change a real world decision.
Financial models are one - of many - tools to assist decision making and risk management. The only thing I can promise will NEVER happen is what is in your model.
Unless you are in university trying to answer some professor's ridiculous question and standards, stick with IRR and adjust accordingly.
My JVA docs say otherwise
If blue chip means less than $5mm equity checks I believe it. Funny I've only seen college kids use the way you describe it.
Institutional firms only use XIRR. It's almost always called out in the JV docs to use the XIRR excel formula.
And as another person pointed out you're way of doing it is never accurate.
Yes, the LPA will use XIRR and use actual contribution and distribution dates to determine exactly when (or if) the preferred return was met and what the total nominal compounded preferred return due to LPs was / is. This is something your accountant can easily calculate on their own when making capital calls or distributions.
But if you're using financial modeling, this is just bad practice. In the below I used an EXTREME example of how XIRR and IRR could differ, where net cash flow was made (contributed or distributed) on cycling first and last days of each financial period. It results in a 81 bps delta. No one is making a different management decision off a 7.00% IRR versus a 7.75% IRR.
And yes, blue chip - over $100 Bn.
Why are you trying to die on this hill lol. It's literally easier to do it the correct way than your way.
So your talking about 5-10 companies, (blackrock,blackstone,EQT, Appollo, cvc, carlye, tpg, kkr) as someone who used to work at one of those companies i can confirm xirr is used, not your wrong way lol.
Also youre out of your mind if you think 75 bps is not a big deal.
3/10 rage bait. We have had, and currently have, multiple JV docs with "blue chip firms" and there are XIRR clauses . . .
If the difference is immeasurable small why argue with someone? I also work at a “blue chip” firm and we use xirr. Even the loan docs I’m currently working on reference that the XIRR function must be used in the cash flow waterfall.
Also, the last “blue chip” firm I worked at exclusively used annual IRR.
The whole point to this - there is no right way. There is no difference whether you choose IRR, XIRR, or your mathematical method.
Use OFFSET to make a dynamically sized range. Set width dynamically and keep height constant at 1.
Thanks for response
OFFSET Shit makes no sense
Can you explain logic lke im 5 year old plz
offset spits out a range, so use it inside another formula like SUM or IRR. 1) param 1 is the reference, so think about it as the top left corner of your range 2) param 2 is how many rows to go down to the start cell of the range 3) param 3 is how many columns go right to the start of your range 4) param 4 is how tall you want your range to extend (how many rows will be part of your range) 5) param 5 is how wide you want your range to extend (how many columns will be part of your range)
Let’s say I want add all the numbers from B2 to D8. =OFFSET(B2, 0, 0, 6, 2). The 6 comes from 8-2, and the 2 comes from D-B
Let’s say I want a range the same size as that h=6,w=2, but I want it to start in C5 instead. =OFFSET(B2, 3, 1, 6, 2)
In your use case I’m assuming you have a net cash flow line starting in let’s say F60, and the exit month for the deal is in cell B2. Let’s also say your dates start in F2.
For the dates range: OFFSET(F2, 0, 0, 1, B2)
For the cash flows range: OFFSET(F60, 0, 0, 1, B2)
Put it all together and you get XIRR(OFFSET(F60, 0, 0, 1, B2), OFFSET(F2, 0, 0, 1, B2)) ——————- The reason for there being the rows and cols offsets (params 2 and 3) is so that you can have a table with bunch of numbers and then you can mimic the behavior of index(match,match) by instead starting in the top left and then offsetting a 1x1 range to get a specific cell.
It's already dynamic on the backend. On the front end, can just index(match) for the first date where CF is negative.
See my earlier comments above. Below is a way you could achieve what I believe you are asking, however
DO NOT USE THIS. THIS IS BAD. YOUR MANAGING DIRECTOR WILL HATE YOU.
Once again, IRR versus XIRR function results are nearly de minimus. Stick with IRR.
Borat appreciate and offer hospitaliy
You can use =FILTER too
I second this. Using FILTER is a great way to achieve what you are looking for.
The formula should be something like:
=XIRR (
FILTER ( CFs, (Dates>=Start_Date)*(DatesFILTER ( Dates, (Dates>=Start_Date)*(Dates)
You can remove the "*(Dates
What FILTER does is it uses your conditions (second input in the FILTER formula) as a TRUE / FALSE matrix, then outputs only the results from your first input (your CFs, or Dates, or whatever else you want) that have a TRUE value in the matrix. For this to work properly, your reference range size (# of columns or rows) for the first input (CFs, Dates, Etc.) needs to match the reference range size of your second input (the conditions you build).
Not to date myself, but when my Excel skills peaked I was still using 2007 and rolling over into 2010. So while I am not familiar with this function, it looks like a much better solution if it does what is intended as per above.
Use ChatGPT for Excel formula help
Here are two more ways to do this:
1) Simpler: In your levered cash flow line, add -0.00001 for the first month.
2) More Complex: Add a helper row below each month of levered cash flow, and keep the left side of the values/dates unlocked. Then, you can index/match to find the first negative cash flow input and pull the IRR from that date.
Helper Row: =IFERROR(XIRR(B9:$F9,B8:$F8,0),0)
Calculate First Negative Value: =INDEX(B10:F10,MATCH(TRUE,INDEX(B9:F90,0),0))
#1 is the way, forget the rest of the brain damage. More calcs will bog down your spreadsheet as well.
Can you explain #1? I’ve seen it used in most models, but never really got the point / how it works.
Non sunt dolorem aut veritatis. Unde distinctio nisi nam qui. Ex est aut vitae facere repudiandae neque. Dolor aperiam labore tempore. Eligendi velit qui minima architecto mollitia dicta architecto. Non eum voluptatem dolorem corrupti maxime voluptatem.
Alias eveniet eos atque voluptate. Ut ad modi illum.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...
Earum ratione ut in eaque officia quam sint. Nemo amet cumque vel quod autem. Ducimus ut aut voluptatem. Ducimus autem aperiam voluptas rerum voluptates minus sunt saepe.