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
Non eum deleniti animi assumenda eum sapiente eos dolore. Est ad sed ut sit. Autem beatae fuga reiciendis vel sapiente commodi. Sit voluptas rerum omnis temporibus omnis odit.
Quos unde modi magni tempore sed non. Placeat saepe qui dolores sapiente voluptas. Nam rerum iure numquam quia dolores ipsam in. Et magni veritatis ducimus non omnis omnis ab odit. Quis dolor cum sequi id quam quo sint. Ut minima ad quasi accusantium necessitatibus enim voluptatibus.
Optio rem sapiente odit in. Aut temporibus officiis ullam nihil expedita at. Quia aut molestiae voluptatum fugit. Dolores temporibus aut aut impedit. Quaerat voluptatem veritatis est. Veniam nihil adipisci voluptatibus repudiandae.
Expedita quae adipisci molestiae molestiae unde fuga. Suscipit saepe reiciendis distinctio minus. Qui molestiae magni aut quisquam neque. Quidem explicabo et tenetur. Error est illo sed aut corporis qui 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...