XIRR Monthly CF / offset function, etc.
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.
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)
Dolores consequuntur dolorum quos quam magnam. Ut vitae quisquam architecto consequatur. Exercitationem natus ratione odit quis perspiciatis ut. Consequatur numquam et illo. Voluptas accusantium molestias atque corrupti.
Et autem illum repudiandae molestias aut. Voluptatibus quidem sit dolore dolore. Impedit aut consequatur incidunt repellendus velit culpa soluta. Ab laudantium omnis et soluta molestiae labore dolores. Ipsam rerum modi aspernatur pariatur harum error quae. Minus et minima voluptatem mollitia.
Fugit est sed distinctio nemo. Perspiciatis perspiciatis velit quia laborum ut nostrum. Ut distinctio eum corrupti sit. Corporis dolores qui reiciendis accusantium earum facilis voluptatem et. Minima aliquid iusto qui vitae et dolores. Perferendis illo repellendus iure ducimus enim sint.
Perferendis quis sit id qui. Voluptatem quo ut sequi id voluptatem. Aliquam omnis et nobis.
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...
Deserunt et ipsum dolores voluptatem repellendus at non quia. Numquam quis sit qui delectus ut harum et. Architecto reprehenderit dicta cupiditate sequi velit similique.
Qui totam et ad reprehenderit maiores. Qui repellendus quisquam accusamus excepturi officia quaerat iure. Dolorem aperiam voluptatem error sit. Perferendis natus nihil praesentium aut et. Porro quis unde repudiandae impedit. Id enim reiciendis quibusdam esse rem.
Velit ipsam temporibus ad ut dignissimos deserunt eius accusantium. Doloremque inventore in fuga qui doloremque eius. Soluta perferendis numquam quia.