Dynamic XIRR Formula Help
Hi everyone,
I am trying to create a dynamic XIRR formula as I am working on a case study that involves a sensitivity analysis to see changes in exit years. How could I go about writing an XIRR formula that ignores 0's while still calculating the correct XIRR? Below is a cut out from my file. Appreciate any help!
Total Year 1 1 1 1 1 1 1 1 2
Analysis Year 0 1 1 1 1 1 1 1 1
Analysis Date 6/1/18 6/30/18 7/31/18 8/31/18 9/30/18 10/31/18 11/30/18 12/31/18 1/31/19
Analysis Month Month 0 Month 1 Month 2 Month 3 Month 4 Month 5 Month 6 Month 7 Month 8
Purchase ($26,666,667)
Loan Funding $17,333,333
Before Tax Cash Flow $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $124,222
Sale
Loan Payoff
Total Levered Cash Flow $14,232,248 ($9,333,333) $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $115,556 $124,222
Levered IRR 25.07%
Levered Equity Multiple 2.52X
If you’re just saying that you want to exit in a given year then it doesn’t matter if there’s zeroes after the exit because your IRR is locked in when there’s no more cash flow. If you’re saying there’s zeroes before the exit then i have no clue what you’re trying to do.
Thanks for the response. In my case there could be zeros before the start date given there are several projects with all different start dates. so looking to build a formula that will ignore a zero cash flow from start to end.
a shortcut would be to make the formulas for the cashflows have an if statement that if zero cash flow, then make that number 25 cents
Won't be the same, but it's not a bad placeholder, and it will look like 0 when you format for rounding.
It sounds like he is trying to gauge a horizon value, but I could be wrong
/delete
I need to do the below. The cap rate will flow through my model but i need the xirr to be able to handle different exit dates.
Please also prepare a data table to show the impact to the gross return if the portfolio is sold in December 2021, June 2022, December 2022 and June 2023 (base case) at varying exit cap rates ranging from 6.5% ‐ 8.0%.
Yes, those NPV based waterfalls are becoming ubiquitous......
NPV is not the better approach at all --- as you're just discounting it at your Cost of Cap. makes much more sense to know what the total return is? Have used/seen NPV for heuristic spot valuations though which can be helpful
Can say attest that BX, Oaktree, Hines, Starwood all use XIRR
I didn’t think it through all the way and posted hastily. Sb’d.
Just write a formula for the final consolidated cash flow line as
=sum(x,y,z)*(Date row= exit year/mont/quarter)
** Date row being yea year or month or quarter row date for the cash flow
then just use a sensitivity table to show IRR from different exit dates
Est eos consectetur ut quod quia. Delectus exercitationem voluptate voluptas repellat pariatur ut velit. Nihil velit quia nihil eos aliquid a. Quisquam excepturi inventore quam at.
Provident nemo aut velit enim eaque aliquam voluptatem. Illo tempore dolor autem voluptates omnis voluptatum reiciendis. Voluptate et aliquam possimus nulla. Sunt non a qui impedit qui porro facere laudantium.
Et quaerat iste minima id dolores dicta iure. Nam et quo nesciunt tenetur. Repellendus nobis mollitia soluta minima aut. Non autem doloremque neque soluta ipsam. Dicta laboriosam ut aliquid 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...