Can someone help with a dynamic XIRR calculation? The GP in the deal puts in all equity through a particular month and the LP starts to contribute capital after that. I am looking for an off-set type equation that will calculate XIRR if the date ranges change. Would appreciate if someone can share an equation for this. Pain in the ass to keep dragging between month 8 or 15 due to timing of first negative CF. Thanks in advance.
| +52 | Major Decisions | 9 | 3h | |
| +35 | LP coming into deal at higher land basis. How to model returns to GP? | 12 | 1m | |
| +32 | Yes Another Comp Thread | 29 | 9h | |
| +28 | Niche down to an operator or stay an allocator | 9 | 14h | |
| +24 | Target Return for Spec SFR Development? | 8 | 2d | |
| +24 | Breaking into CRE late 20s | 7 | 1d | |
| +20 | Doom Loop or Creative Urban Reemergence? | 3 | 3d | |
| +19 | Self Storage ECRI | 9 | 5h | |
| +18 | 3 Hour Condo/Rental Excel Test - What Can Be Expected | 2 | 1d | |
| +17 | What do you RE folks think? | 7 | 5d |
Career Resources
Get instant access to lessons taught by experienced private equity pros and bulge bracket investment bankers including financial statement modeling, DCF, M&A, LBO, Comps and Excel Modeling.
If you already have a capital deployment date you will need to combine XIRR with OFFSET(MATCH) to tell your IRR calculation where to start.
If your input is not driven by a date then use an INDEX(MATCH(INDEX)) to find the first negative value of the cashflow and then combine that with the above (google "find first non-zero value excel")
I think offset over complicates it, if another person using the model isn't familiar with it as a formula they wont understand what's going on. You can achieve the same result with a combination of XIRR, ROUND, IFERROR and SUM. Assuming you've a 36 month cashflow over cells B2:AK2, dates in B1:AK1 and the formula in row 3. The formula is: =IFERROR(ROUND(XIRR(B2:$AK2,B$1:$AK$1),4),0)*(SUM($A3:A3)=0). Sum cells B3:AK3 which will give you the IRR output as the formula will return the IRR on the month of the first cashflow.
thank you! what does A3:3 impact the calc? This is another equation that works someone shared with me and it worked for my analysis. my knuckle head can't grasp your simple logic haha
=XIRR(INDEX(4:4,AGGREGATE(15,6,COLUMN(F4:W4)/(F4:W4<>0),1)):W4,INDEX(3:3,AGGREGATE(15,6,COLUMN(F4:W4)/(F4:W4<>0),1)):W3)
Dolorum ducimus dolores ipsam. Ullam et id ut impedit quis non nihil. Totam unde placeat repudiandae quo asperiores dolorem soluta fugiat.
Recusandae autem harum architecto excepturi tempore. Natus labore porro aspernatur voluptatibus rerum soluta.
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...
Ea a accusamus accusantium id ut deserunt. Saepe quisquam sunt enim non. Odit culpa sequi voluptates voluptatem aut nam. Eos vel nisi fugiat vero voluptate aperiam nemo. Voluptatibus quibusdam repellendus ex quae neque quibusdam sapiente. Est quisquam optio quasi alias.
Adipisci soluta magnam eius optio qui tenetur. Architecto eaque quod eius. Dolorum expedita incidunt qui quos accusantium. In dolorum architecto fuga ab eaque ullam et.
Magni sunt dignissimos magni ut enim sit. Unde rerum ut eum itaque. Debitis et asperiores est eos. Quis fugiat quae fugiat aut rerum. Velit ducimus ut aut nobis nihil sit autem. Id aut consequatur nobis provident beatae magni hic aliquid.