Excel IRR Question
I have a development deal where there is no cash out in time 0, but there is participation in the value reversion at exit. Interim cash flows are consistent with development costs. It looks funky, but that is the way the deal is structured.
When I run an XIRR, I’m getting a “0%” IRR. I realize this isnt typical and you normally have cash out at time 0… does anyone know to get an IRR in excel in this circumstance?
The IRR calf just has to start at the first negative cashflow month
I think all you need to do is put a very small negative amount in the first month to represent a cash outflow. Something like -.0000001. I believe that'll fix the IRR calc.
It will "fix" the calculation but the result will be meaningless.....
Maybe try taking the PV of the future cash outflows and then use that as the initial investment? Not sure if conventional IRR would apply to your model...
Nobody is asking, why there is no cash-out in period 0? Which is an important questions in to how to structure the deal and the returns. The most simple way is to have a negative 1 as the outflow in period zero. Because you can't get a calc out without a negative cash flow, and if you model the development then you have the loan likely covering the carry cost, so you won't have any negative cash flows. (It does vary).
At the end of the day the principal had to use equity at some point of this deal.
It’s a good question. It’s a JV structure where the Partner 1 buys the deal, then immediately engages Partner 2 through a leaseback agreement. Partner 2 pays non of the acquisition costs, but immediately participates in the cash flow from the acquisition. Partner 2 redevelops the asset, bears the cost to do so, then “sells” the property back to Partner 1 for the improved value.
The theory is that Partner 1 gets a significant better asset post redevelopment— and Partner 2 gets the benefit of the incremental value reversion.
I would bifurcate the periods,as 0 when you buy the deal, then 1 where you engage partner #2. Assuming if this is for an internal model, that would make it work, one month pushing back cash flows would not majorly effect the returns. You could also have two period zero's if it makes it easier to swallow 0t1 and 0t2.
So Partner 2 pays a lease rate every month + redevelops the asset, and the sum of that is still less than existing monthly income? Does Partner 2 get all of the cashflows?
If so it sounds like a steal of a deal...pay a monthly "rent" to act as GP for me, where all your costs are covered, and I'll buy whatever add'l value you deliver. Almost a real estate call option
Adipisci blanditiis illum nemo architecto dolore. Quas dolores molestiae maxime. Molestiae aut unde excepturi inventore at voluptas libero.
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...