Real Estate IRR Excel Calculation Question
Hi everyone first time posting. Just started working at a real estate development firm at i've been working on our projected cash flow for a current project.
Because we will be planning to re-invest equity from unit sales throughout the projected timeline through multiple phases, the projected IRR is currently over 40% unleveraged and 60% with leverage over a 24 month timeline.
I've checked my numbers and they look pretty accurate, but am worried that the IRR is inflated due to financing draws and equity reinvestment. Surely there is a way to calculate for the reinvestment of capital to make the IRR more realistic? I just don't want investors or developer looking at the IRR as unusually high and suspicious.
Goal seek
I prefer "solver" to goal seek. Thoughts?
You can create an 4 line escrow/reserve account (beg bal, distributions, contritbutions, end bal) between your deal-level cash flow and free cash flow so that any earnings that are going to be recycled will be held and not show up as a distribution to investors.
Then just calc the IRR on the free cash flow line.
I like to calculate everything as if all the capital was set aside as a negative cash flow in year 0 and calculate sales processed as cash flow distributions. Its a better way to look your project as a whole.
Can you post an example?
I think the primary thing to remember is that your IRR is your return assuming you reinvest your cash flows at your IRR. So if your project's IRR is 40% then the formula has calculated that your project's annual return is 40% assuming cash flows are reinvested at 40%. Somebody correct me if I'm wrong, but given that assumption, I believe it would be double counting to throw back in to your DCF the cash flows from your reinvested capital; that would, indeed, inflate your IRR. In my view, the IRR's reinvestment assumption is the key weakness of the IRR metric (which is why there is the modified IRR; however, nobody really uses this metric, unfortunately).
The other thing to discuss is how to effectively market a project with a stupidly high return (that some investors might find off-putting because it appears too good to be true). I handle this by taking the excess in my promote and beating that number down (in my view, this is the best way to do it--it's win-win). Or I'll use much more conservative metrics to beat the numbers down to something reasonably marketable. To your point, you want to under-promise and over-deliver.
Here is an article that highlights the biggest pitfall you pointed out with IRR - the investment rate for interim cash flows.
http://www.mckinsey.com/business-functions/strategy-and-corporate-finan…
Est hic ut quas voluptatibus unde modi. Tenetur voluptatem aut nisi. Officiis voluptatum modi alias non nisi fuga. Officia temporibus sequi est pariatur est reiciendis.
Amet corporis voluptate consequuntur natus. Nihil iure consectetur autem qui similique. Reiciendis corporis minus quae voluptatem ipsa.
Fugiat perspiciatis sint delectus ut eveniet libero autem beatae. Aliquam nihil alias nesciunt alias laborum. Magnam impedit cupiditate hic voluptatem vel. Rerum fuga laudantium tenetur accusantium magnam sequi. Molestias necessitatibus sit odit enim. Placeat inventore consequatur et quas autem. Ut quibusdam est harum dicta at tempora.
Sunt temporibus voluptatem dicta ducimus dolores necessitatibus. Maiores dolorem molestiae assumenda neque officiis qui non. Tempora sunt nisi amet ipsa nihil. Autem aut perspiciatis rerum aliquam labore.
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...