Issue with IRR being incorrect because it's not counting initial investment?

[Anonymous]

5 years, 9 months ago #2478

I’m using Beta V0.5.9 and I’m running into what I believe to be a pretty big problem. I’m using the construction module and my IRR isn’t calculating year 0. It just lumps in the construction year into year 1 as the investment year. I believe it should have an initial investment in Year 0 then have cash flow from year 1 construction period forward. This is skewing IRR.

I’ve attached a screenshot with it highlighted. Perhaps this was fixed in a later version?

Thanks,

Rob

[Spencer Burton]

5 years, 9 months ago #2880

Robert,

This is a great observation and has been accounted for. Allow me to explain. I intentionally set the analysis begin month for acquisition deals to time 0, while I set the analysis begin month for development deals to time 1. This was to avoid confusion on the Budget tab when modeling development cash flows; although in hindsight if I were to build the model again I’d just start both in time 0.

To properly calculate monthly returns, I wrote a lengthy XIRR() formula for the monthly IRR calculation (see E67 and E88 on the Property CF tab) to essentially set the analysis start for development deals to month 1 while including a time 0 for acquisition deals.

Thankfully, calculating the annual IRR was much simpler. If the first value in a string of IRR() values is zero, Excel disregards that value and begins at the next non-zero value. As a result, the IRR() formula (E22 and E43 of the Property CF tab) is written to include time zero for both acquisition and development deals, however for development deals the time zero value is automatically disregarded. You can test this by turning on the development module, heading to the Property CF tab cell E22 and then in the cell above the formula writing the IRR() formula but without including time 0. You’ll find the resulting IRR is identical to the existing formula.

Thanks again for your comment!

Spencer

[Anonymous]

5 years, 9 months ago #2882

Spencer,

Thank you for taking the time for the thoughtful reply. I still don’t think the Annual IRR calculations are correct. I’ve attached a screenshot showing your calculations and numbers (in Orange) and the actual timing of cash flows and resulting IRR (in Yellow). For a typical development Year 0 is the capital outflow and Year 1 results in no income because of the development timeline. Your numbers show the time 0 and time 1 as combined which doesn’t take into account not receiving any income in Year 1 during the construction period.

Does that make sense?

Rob

[Spencer Burton]

5 years, 8 months ago #2884

Rob,

Sorry for the delayed response – I was traveling much of August.

I follow your logic and this is an interesting discussion. I’ve recorded a short video/written a blog post explanation of why I chose to use month 1 as the start month for development analysis in the Ai1 rather than month 0. I also address the concern around the annual IRR being incorrect. I figured talking through the question via video is easier than typing it out!

With all of this said, it’s important to remember development analysis is generally modeled across the industry on a monthly, not annual, basis. So much of this discussion is academic only! Or in the other words, the annual IRR of a development deal is not a metric most developers are paying attention to. This is because development cash flows (those negative cash flows) occur over a period of months (usually 12+). If they were modeled on an annual basis, the IRR calculation would be greatly skewed (as you’ll see in the video).

https://www.adventuresincre.com/treatment-time-0-all-in-one-model/

I’ve attached the Excel Workbook scenario presented in the video to the above post.

Thanks for the discussion – very interesting!

Spencer

[Anonymous]

5 years, 7 months ago #2886

Spencer,

Thank you for this thoughtful video reply. It makes sense to me now. I’ll make sure to use the Monthly IRR calcs instead of Annual on my development analysis. It’s a great model and I’ve enjoyed using it as a base to tweak for my business use.

Rob

[Anonymous]

3 years, 9 months ago #3815

I read articles about this topic on the site essaysservicesreviews

[Anonymous]

3 years, 9 months ago #3817

I read articles about this topic on the site [url]https://essaysservicesreviews.com/[/url]