IRR Calculation (Distributions vs Deleveraging)

14 Replies

Hi everyone,

This question is probably for the more financial modeling experienced people but any help would be greatly appreciated.

I would like to figure out how to determine on a 5yr hold whether cashflow distributions versus deleveraging the investment would lead to a greater IRR if I purchased a property in a high interest rate environment leading to a low interest rate environment.

I'm in the process of putting together a pitch book for a distressed debt fund as I think we're at the peak of the current market and we are in for a world of hurt when the correction comes. 

As such, I need some help figuring out how to do this. I've built my own Excel model in Google Drive but I don't know exactly how to figure this out and I'm debating just purchasing the RealData course or REFM course in order to learn how to calculate this more clearly.

Thanks in advance for your help.

@joshua It's all in here - best of luck!

The Complete Guide To Real Estate Finance For Investment Properties, 14th Edition

How To Analyze Any Single-Family, Multifamily, Or Commercial Property

By Steve Berges

John Wiley & Sons, Inc.

@Joshua Nicholas undefined

Ex financial modeler / analyst here.

Excel calculates IRR for you - but you have to setup the data properly. =IRR(data range) where t-0 is the cash outlay (as -) and disbursements are equal time intervals (years work best).

If you put your initial total capital investment in cell a1, and cells a2-a10 were annual returns (with a10 being that years return PLUS net proceeds from sale), then the formula would simply read =IRR(a1:a10) and that will give you IRR.

Most people (investors included) confuse YoY CoC with IRR. Once you start building models for REITs and Funds, they want IRR as the rest of the financial world works on those - but here in the RE world people use CAP and CoC... oddly enough. I always still include IRR in my models as you never know who you're going to be pitching to...

Good luck!

@Joshua Nicholas  I've taken two of the three REFM exams based on prior knowledge and also used one of the back of an envelope templates. I have high regard for the system and expect the courses would be good too. 

@Doug M. Great call on Steve Berges. I think he's underrated. It was another of his books that gave me the real estate strategy I use today. I think he would do better with fewer books and less repetition but the one you referred to is very good. 

Originally posted by :

@Doug M. Great call on Steve Berges. I think he's underrated. It was another of his books that gave me the real estate strategy I use today. I think he would do better with fewer books and less repetition but the one you referred to is very good. 

 I like your great call on my great call about Steve Burgess. lol?

What book do you refer to? I have only read the one. 

Originally posted by @Travis Lloyd :

Ex financial modeler / analyst here.

Excel calculates IRR for you - but you have to setup the data properly. =IRR(data range) where t-0 is the cash outlay (as -) and disbursements are equal time intervals (years work best).

If you put your initial total capital investment in cell a1, and cells a2-a10 were annual returns (with a10 being that years return PLUS net proceeds from sale), then the formula would simply read =IRR(a1:a10) and that will give you IRR.

Most people (investors included) confuse YoY CoC with IRR. Once you start building models for REITs and Funds, they want IRR as the rest of the financial world works on those - but here in the RE world people use CAP and CoC... oddly enough. I always still include IRR in my models as you never know who you're going to be pitching to...

Good luck!

 Hi Travis,

Thank you for this explanation! 

One question however is I don't know exactly how to formulate the following:

If I forego giving distributions, each time I make an extra payment on my mortgage I am accelerating the amortization so mortgage payment may be the same for 60 months but each month the amount of interest paid on the mortgage is changing.

I could technically just take cash flow after debt service and use that to calculate the ending balance but it wouldn't be correct because of paying the mortgage down more quickly will also pay off principal quickly. Do you know how to build a model where I could run both analyses and see which one leads to higher IRR?

Thanks for your help :)

Your cash flow from prepaying the mortgage will be realized at the reversion cash flow.  You'd need to build an amorization schedule into your model to account for the cash flows.  There is a model on the Files section of BP that I built and some other folks have revised that does this.  

Your cash flows from prepayment will be negative each month and a bigger positive at the end of the note. Prepayment is very likely to decrease your IRR unless your property is operating poorly.

@Doug M. The one that got me started was this one, on a recommendation from another forum I think. It was the first book I had read that presented realistic numbers and explained cap rates etc. in a way I could understand. It also explained repositioning to me as a way to force appreciation. It was refreshing to read something that made sense analytically instead of endless bogus narratives about making a fortune with no money down. 

I believe Frank Gallinelli's book is similar to the one you describe and also very good, although I haven't read it. I think he is a contributor here on BP too. 

Originally posted by @Doug M.:

@joshua It's all in here - best of luck!

The Complete Guide To Real Estate Finance For Investment Properties, 14th Edition

How To Analyze Any Single-Family, Multifamily, Or Commercial Property

By Steve Berges

John Wiley & Sons, Inc.

@Joshua Nicholas undefined

This looks like a good read to add to my reading. I found a 1st edition, is there much of a difference between that and the edition you have? Thanks

Originally posted by @Doug M.:

@joshua It's all in here - best of luck!

The Complete Guide To Real Estate Finance For Investment Properties, 14th Edition

How To Analyze Any Single-Family, Multifamily, Or Commercial Property

By Steve Berges

John Wiley & Sons, Inc.

@Joshua Nicholas undefined

this look like a good book to add to my reading. I found a 1st edition on amazon, is there much of a difference between that and the 14th? Thanks

Originally posted by @Daria B. :
Originally posted by @Doug M.:

@joshua It's all in here - best of luck!

The Complete Guide To Real Estate Finance For Investment Properties, 14th Edition

This looks like a good read to add to my reading. I found a 1st edition, is there much of a difference between that and the edition you have? Thanks

 Hi Daria - I would imagine there is a lot of difference - PM me if you're interested. 

Originally posted by :

 It was refreshing to read something that made sense analytically instead of endless bogus narratives about making a fortune with no money down. 

 Ordered for 12 bucks, can't beat it. Thank you so much - good recommendations are... good! (Strike that - GREAT!)