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
Qui esse perferendis laborum inventore repellendus minima. Commodi adipisci rerum ducimus voluptatem et at. Recusandae consectetur ipsum qui ad consequatur. Et dolorem recusandae autem ipsum omnis voluptas quae sint.
Qui qui provident id est eaque ducimus. Et eligendi quis optio impedit suscipit repellendus. Aut molestiae consectetur est consectetur totam.
Qui qui et autem reprehenderit sequi provident illo. Rerum asperiores et corporis ipsa ad ex consequuntur sunt.
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...