Understanding IRR Calculations in Frank Gallinelli's book
Delmar, NY · Member since 2016 · 9 posts · 7 votes
Hello Everyone,
I've learned a lot from Frank Gallinelli's "What Every Real Estate Investor Needs to Know About Cash Flow," however, the Internal Rate of Return concept is still a little confusing to me.
In this book there is a case study in chapter 6 that considers an "Apartment Building Investment" (pgs. 108-120). I have worked through all the categories (i.e. "NET OPERATING INCOME," "CASH FLOW BEFORE TAXES," "PROJECTED SELLING PRICE," and "BEFORE-TAX SALE PROCEEDS") and have the EXACT same numbers that Mr. Gallinelli has in the book. When I try to figure out the "Internal Rate of Return, Before Tax", however, my answers are WAY off.
Would someone who has read this book PLEASE help me understand how Mr. Gallinelli came to the IRR projections that he did?
Thank you in advance!
Hi Justin. Don't worry too much, it really takes a long time to really understand how these complex calculations work.
But once you do, you will finally understand how to invest in ANY market, whether or not it's in NYC or Buffalo!
What's even more is that using the IRR, you can also then make comparisons to a whole lot of other types of Investments.
To answer your question, however, please refer to this snapshot:
Rather than calculate the IRR for each and every year, I just decided to calculate the 5 year IRR for the numbers you type out in your last post.
You really should split it out as I have done in this spreadsheet.
First, make a column only for the Buy/Sell, or as I put it, Appreciation.
When you buy the Investment, you paid $323,472.
Noticed you PAID for the investment.
Most people don't really understand Cash Flow so they don't understand that Cash Flow is really just a direction of how cash is flowing.
You need to first pick a reference point and then you can figure out how to properly put a sign on the Cash Flow.
In this case, we have a Cash Flow of $323,472.
HOWEVER, we need a reference point. That reference point is your pockets.
$323,472 leaves your pockets to buy the Investment. THEREFORE, the $323,472 is a NEGATIVE Cash Flow relative to your pockets.
The way I like to put it, how do you feel when money leaves your pocket? NEGATIVE!
So year Zero, your very first Cash flow is a NEGATIVE $323,472.
If people understood that every investment that they BUY starts out NEGATIVE, then they wouldn't be so hung up about Negative Cash Flows. I am hoping that the readers of this post will just try to understand that Negative Cash Flows are just numbers you put into a Calculation and that you should put more emphasis on the Assumptions and the Results. BUT HEY... that's just me explaining it.
NEXT, we know that in year 5, we sold the investment for $541,310. Money goes into your pockets then, so it's a positive cash flow.
That's the explanation of Column B.
Next, we need to know all the cash flows for all the years you held the investment, which is the next 5 years.
During that time, you received the cash flows as according to Column C.
We then total up all the Cash Flows in Column D.
NOW, we are ready to do the IRR Formula!
Here is the snapshot of the same spreadsheet, but with the IRR formula revealed:
So you can see, Excel Calculates the IRR for that 5th Year as 19.24%... very slightly off from Gallinelli's 19.25% for year 5. We should ask Frank to correct that! haha! Just kidding.
Either case, doing the calculations is just one part of it.
What Gallinelli does is explain the long and hard work that goes into doing the calculations so that you can get a very clear vision on the future of your investment.
Once you get this vision, you will clearly know if this is a good investment or not. And even more, that you can do the same IRR to other properties or even a stock investment. It's really all about cash flows.
What you also need to get a really good understanding is what does that 19.25% IRR really mean?!
It's actually a fantastic number! It sort of means that if you put your money into a savings account for 5 years, the interest in your savings account will be 19.25% per year for 5 straight years!
Even this doesn't really tell you how powerful 19.25% Compounded IRR really is! Because it's a compounded calculation, it can turn a small investment into a very large one over many years! Well.. in my case, 21 years! haha!
It's not an exact comparison, but it's close enough. There are some discrepancies in regards to the time value of money and I'm hoping you will go over the book to get a really good feel for it.
Once you understand all of these concepts, you will really understand investing.
There is a lot that goes into the projections, however.
Keep in mind that once you start thinking of the future sale price and the future cash flows, you start to think, HEY......... I BETTER GET A GOOD UNDERSTANDING of the FUTURE.
Well... how do I do that?! Time to study Economics and pay very close attention to it.
To emphasis this point, think of a scenario where if you didn't follow the economics, things would have turned out VERY bad.
Take Investing in Detroit before it went bankrupt.
10 to 20 years before Detroit went Bankrupt, there were very clear economic signs that domestic autos were in trouble. Detroit was 90% dependent on Domestic Autos.
If you followed the Charts on Ford, GM and Chrysler, you would have seen that they were declining for an awfully long time. There were plenty of signs that said RUN! Get away from here!
So even if your numbers for all the calculations were GREAT for year ONE, by the time Year 10 came, you would have lost a lot of money.
This is the BIG problem I have with these single year calculations like Cash on Cash Return. You make one calculation based on todays number and BAM... you pull the trigger. No thoughts on the future. No Economics, no Nothing. Everything based on knowing the Rents, Expenses and your investment money. WOW.... really? That's all you need to know?! I'm always shocked that's how people do it.
To me, that's just throwing darts on a board and hoping it will stick on something that will make money.
Learn the IRR. Understand the Assumptions that go into it and then you'll understand the importance of getting out your economics crystal ball.
My Partners and I invest in NYC for the last 21 years. Our Crystal Ball energized by Economics and armed with the IRR calculations told us to buy here in NYC despite all of the bad advice given to us that it's too expensive.
We've heard that same comment for the last 21 years and we will hear it again for the next 21 years.
I don't pay attention to Gurus, Posters or anyone else.
I only pay attention to the Economics and the IRR.
You can only imagine how much we made owning NYC properties over a 21 year holding period, accumulating 8 multi-family buildings.
It has been phenomenal!
You are on the right track.
The book you have should have been on the best seller's list but it won't because most people don't want to work too hard to understand more sophisticated calculations. I wish those people good luck with that.
Hopefully I helped explain the numbers for you!
I'm not familiar with the book, but I teach finance to college students. If you can share the numbers with me, I might be able to tell you where you are going astray. :)
I haven't read his book. IRR is calculated using a spreadsheet formula or a financial calculator. Unless there is a typo my guess would be that you are possiby entering the amounts in and out backwards - using negative and positive numbers in reverse.
Another common mistake is the timing of the money in and out. For example the first money into the investment is not year 1 it is year 0.
Thank you for responding! Here are the details:
"A local agent has shown you a few properties, but none has exited your interest. However, there is a 16-unit building located near your home in the suburb of New York City that has caught your eye, and your attorney has told you that he has heard that the owner has an interest in selling. You decide to approach the owner directly.
"The owner is indeed willing to discuss a possible sale, and so you begin your due diligence by asking him for a list of the current rents and the expenses for the past year. He provides the following:
4 studio apartments @ $1,000 per month
4 one-bedroom apartments @ $1,250 per month
4 two-bedroom apartments @ $1,500 per month
4 two-bedroom apartments @ $1,800 per month
Property taxes, $40,000 per year
Insurance, $15,000 per year
Water and sewer, $12,500 per year
Electricity for common area lighting, $2,800 per year
"You confirm the rents by examining the leases and you verify independently that the expenses are accurate as quoted.
"You're able to observe that the building is brick, and the exterior appears to be in good condition. There is parking for 16 cars. You have $350,000 in cash available to invest, and your bank has said that the potential financing terms would be 80% loan-to-value, 1.20 minimum debt coverage ration, 6.75% interest (fixed for 5 years and floating for the remaining 15 years of a 20-year term), with 1 point payable at the closing. Based on your research into recent sales and your interrogation of a local appraiser, you conclude that the current market capitalization rate for properties of this type in this neighborhood is about 9%."
I determined the value to be $1,506,533:
Value = NOI/Cap Rate
Value = $135,588/0.09
Value = 1,506,533
I put down 20% ($301,306)
I pay 1 point ($15,065)
I pay for title insurance and a lawyer ($7,101)
Total: $323,472
"After your research, you decide to project a 3% annual increase for both the income and expenses."
Now for the numbers:
Year 1 Year 2 Year 3 Year 4 Year 5
INCOME
Gross Scheduled
Rent Income 266,400 274,392 282,624 291,102 299,836
TOTAL GROSS INCOME 266,400 274,392 282,624 291,102 299,836
VACANCY & CREDIT LOSS 7,992 8,232 8,479 8,733 8,995
GOI 258,408 266,160 274,145 282,369 290,840
OPERATING EXPENSES
Accounting 2,000 2,060 2,122 2,185 2,251
Advertising 1,000 1,030 1,061 1,093 1,126
Insurance 15,000 15,450 15,914 16,391 16,883
Janitorial Service 4,800 4,944 5,092 5,245 5,402
Lawn/Snow 2,400 2,472 2,546 2,623 2,701
Legal 4,000 4,120 4,244 4,371 4,502
Property Management 12,920 13,308 13,707 14,118 14,542
Repairs/Maintenance 15,000 15,450 15,914 16,391 16,883
Supplies 500 515 530 546 563
Taxes
Real Estate 40,000 41,200 42,436 43,709 45,020
Trash Removal 10,400 10,712 11,033 11,364 11,705
Utilities
Electricity 2,800 2,884 2,971 3,060 3,151
Sewer/Water 12,000 12,360 12,731 13,113 13,506
TOTAL OPERATING
EXPENSES 122,820 126,505 130,300 134,209 138,235
NET OPERATING
INCOME 135,588 139,655 143,845 148,160 152,605
Year 1 Year 2 Year 3 Year 4 Year 5
NET OPERATING
INCOME 135,588 139,655 143,845 148,160 152,605
- Debt Service 109,969 109,969 109,969 109,969 109,969
- Cap Additions 0 0 0 0 0
CASH FLOW BEFORE
TAXES 25,619 29,686 33,876 38,191 42,636
Cash-on-Cash Return 7.92% 9.18% 10.48% 11.81% 13.19%
(CFBT/Cash Inv.)
Capitalization Rate 9.00% 9.27% 9.55% 9.83% 10.13%
Debt Coverage Ratio 1.23 1.27 1.31 1.35 1.39
PROJECTED SELLING
PRICE 1,506,500 1,551,700 1,598,300 1,646,200 1,695,600
- Costs of Sale 105,455 108,619 111,881 115,234 118,692
-Mortgage Payoff 1,175,708 1,144,134 1,110,361 1,074,237 1,035,598
BEFORE-TAX SALE
PROCEEDS 225,337 298,947 376,058 456,729 541,310
IRR Before Tax -22.39% 4.85% 13.83% 17.55% 19.25%
Thank you for taking the time to analyze these numbers! I'm VERY eager to understand how to accurately calculate the IRR, but honestly don't understand how Mr. Gallinelli determined these numbers given the information in these categories.
My initial investment was $323,472 (expressed as a negative).
Cash Flow Year 1 = 25,619
Cash Flow Year 2 = 29,686
Cash Flow Year 3 = 33,876
Cash Flow Year 4 = 38,191
Cash Flow Year 5 = 42,636
Where am I going down the wrong trail?
Thank you again for taking the time to explain this to me!
Justin Greenwood
Hi Justin. Don't worry too much, it really takes a long time to really understand how these complex calculations work.
But once you do, you will finally understand how to invest in ANY market, whether or not it's in NYC or Buffalo!
What's even more is that using the IRR, you can also then make comparisons to a whole lot of other types of Investments.
To answer your question, however, please refer to this snapshot:
Rather than calculate the IRR for each and every year, I just decided to calculate the 5 year IRR for the numbers you type out in your last post.
You really should split it out as I have done in this spreadsheet.
First, make a column only for the Buy/Sell, or as I put it, Appreciation.
When you buy the Investment, you paid $323,472.
Noticed you PAID for the investment.
Most people don't really understand Cash Flow so they don't understand that Cash Flow is really just a direction of how cash is flowing.
You need to first pick a reference point and then you can figure out how to properly put a sign on the Cash Flow.
In this case, we have a Cash Flow of $323,472.
HOWEVER, we need a reference point. That reference point is your pockets.
$323,472 leaves your pockets to buy the Investment. THEREFORE, the $323,472 is a NEGATIVE Cash Flow relative to your pockets.
The way I like to put it, how do you feel when money leaves your pocket? NEGATIVE!
So year Zero, your very first Cash flow is a NEGATIVE $323,472.
If people understood that every investment that they BUY starts out NEGATIVE, then they wouldn't be so hung up about Negative Cash Flows. I am hoping that the readers of this post will just try to understand that Negative Cash Flows are just numbers you put into a Calculation and that you should put more emphasis on the Assumptions and the Results. BUT HEY... that's just me explaining it.
NEXT, we know that in year 5, we sold the investment for $541,310. Money goes into your pockets then, so it's a positive cash flow.
That's the explanation of Column B.
Next, we need to know all the cash flows for all the years you held the investment, which is the next 5 years.
During that time, you received the cash flows as according to Column C.
We then total up all the Cash Flows in Column D.
NOW, we are ready to do the IRR Formula!
Here is the snapshot of the same spreadsheet, but with the IRR formula revealed:
So you can see, Excel Calculates the IRR for that 5th Year as 19.24%... very slightly off from Gallinelli's 19.25% for year 5. We should ask Frank to correct that! haha! Just kidding.
Either case, doing the calculations is just one part of it.
What Gallinelli does is explain the long and hard work that goes into doing the calculations so that you can get a very clear vision on the future of your investment.
Once you get this vision, you will clearly know if this is a good investment or not. And even more, that you can do the same IRR to other properties or even a stock investment. It's really all about cash flows.
What you also need to get a really good understanding is what does that 19.25% IRR really mean?!
It's actually a fantastic number! It sort of means that if you put your money into a savings account for 5 years, the interest in your savings account will be 19.25% per year for 5 straight years!
Even this doesn't really tell you how powerful 19.25% Compounded IRR really is! Because it's a compounded calculation, it can turn a small investment into a very large one over many years! Well.. in my case, 21 years! haha!
It's not an exact comparison, but it's close enough. There are some discrepancies in regards to the time value of money and I'm hoping you will go over the book to get a really good feel for it.
Once you understand all of these concepts, you will really understand investing.
There is a lot that goes into the projections, however.
Keep in mind that once you start thinking of the future sale price and the future cash flows, you start to think, HEY......... I BETTER GET A GOOD UNDERSTANDING of the FUTURE.
Well... how do I do that?! Time to study Economics and pay very close attention to it.
To emphasis this point, think of a scenario where if you didn't follow the economics, things would have turned out VERY bad.
Take Investing in Detroit before it went bankrupt.
10 to 20 years before Detroit went Bankrupt, there were very clear economic signs that domestic autos were in trouble. Detroit was 90% dependent on Domestic Autos.
If you followed the Charts on Ford, GM and Chrysler, you would have seen that they were declining for an awfully long time. There were plenty of signs that said RUN! Get away from here!
So even if your numbers for all the calculations were GREAT for year ONE, by the time Year 10 came, you would have lost a lot of money.
This is the BIG problem I have with these single year calculations like Cash on Cash Return. You make one calculation based on todays number and BAM... you pull the trigger. No thoughts on the future. No Economics, no Nothing. Everything based on knowing the Rents, Expenses and your investment money. WOW.... really? That's all you need to know?! I'm always shocked that's how people do it.
To me, that's just throwing darts on a board and hoping it will stick on something that will make money.
Learn the IRR. Understand the Assumptions that go into it and then you'll understand the importance of getting out your economics crystal ball.
My Partners and I invest in NYC for the last 21 years. Our Crystal Ball energized by Economics and armed with the IRR calculations told us to buy here in NYC despite all of the bad advice given to us that it's too expensive.
We've heard that same comment for the last 21 years and we will hear it again for the next 21 years.
I don't pay attention to Gurus, Posters or anyone else.
I only pay attention to the Economics and the IRR.
You can only imagine how much we made owning NYC properties over a 21 year holding period, accumulating 8 multi-family buildings.
It has been phenomenal!
You are on the right track.
The book you have should have been on the best seller's list but it won't because most people don't want to work too hard to understand more sophisticated calculations. I wish those people good luck with that.
Hopefully I helped explain the numbers for you!
Dear Sir,
Thank you VERY much for giving such a clear explanation! I wasn't adding the "BEFORE-TAX SALE PROCEEDS" to the "CASH FLOW BEFORE TAXES" in the fifth year. Your chart with those figures hit me like a bolt of lightning.
Mr. Gallinelli's advice about considering the time value of money seemed like a very important factor in determining the overall potential for earning a profit on an investment and I really wanted to understand how to figure that out. Your additional suggestion of paying close attention to the economics in your target area is something that I hadn't fully considered, but now that I have, it will absolutely be a part of my analysis.
Again, I really appreciate you helping to explain IRR and why it is important to make such a projection.
To your continued success in real estate investing,
Justin Greenwood
"If people understood that every investment that they BUY starts out NEGATIVE, then they wouldn't be so hung up about Negative Cash Flows."
@Llewelyn A. many members consider the small monthly cash flows to be cash flows but they don't consider the large cash flows at purchase, refinance, or sale to be cash flows. All of the cash inflows and outflows matter and the large ones matter the most.
If you could help me with one more question....
Mr. Gallinelli follows up with:
"Since you're a prudent investor, however, you won't settle for an analysis with just one pass. You will ask yourself, 'Do I think there is any reason why the market in this location might get stronger or weaker in the coming years?' Then you rerun your projections with exit cap rates that are above and below the rate at which you bought the property. Are you still OK with the results?"
"If you must resell at a 9.5% cap rate:
Year 1 Year 2 Year 3 Year 4 Year 5
IRR Before Tax -45.20% -7.54% 6.68% 13.02% 16.17%
"If you can resell at an 8.5% cap rate:
Year 1 Year 2 Year 3 Year 4 Year 5
IRR Before Tax 3.09% 17.12% 20.84% 22.03% 22.33%
How do I go about performing such an analysis? This type of analysis seems to be important given the comment you made regarding the potential for the economy to change.
Thank you again for your time!
Mike: Yep, I don't understand how people can "Sweat the Small Stuff" which is the monthly cash flow versus paying attention to the major Cash Flows, which are the purchase price and the Selling Price, the 2 biggest factors in your ROI. However, there are so many Guru books that harp on the Small Monthly Cash Flow I think the general population stays away from the Golden Opportunities which are the high Appreciating Investment Properties. It's like you sacrifice the Pie for the Crumbs.
That's all good for me! Lowers my competition.
Justin:
Take a look at this Spreadsheet:
What it seems like Gallinelli is saying is that you should create 2 different scenarios, one pessimistic (selling at a 9.5% Cap Rate) and one that's optimistic (selling at 8.5% Cap Rate).
If I run the pessimistic numbers, the Selling price at the 9.5% Cap Rate in Year 5 would be Year 5 NOI / 9.5% = $152,605 / 9.5% = you would sell the building at $1.606 Million. Then you pay for Closing Costs and pay off the balance of the Mortgage. The proceeds would be left ($452,078) and you use that in your IRR Calculation which you see in Cell B23.
I get a slightly different answer than Frank. Mine is 15.92% IRR while you indicated Frank gets 16.17% IRR.
It's not that far off and I bet the numbers are a little different because Frank maybe adjusting the Closing Costs a little bit down because the Price is lower. But I don't know for sure.
Comparatively, the Optimistic scenario of an 8.5% Cap Rate by year 5 will return a 22.54% IRR over the 5 years. Again, slightly different than the 22.33% you indicated in the book, but not far off.
HOWEVER, the important thing to note is that there is a HUGE difference between 16.17% and 22.54%.
What this tells you is that yes, you better buy in places where the Cap Rate is trending DOWN!!!! Because that will give you HUGE Returns!
Case in point, NYC had Cap Rates trend down for the 21 years I have been investing here.
However, that may not hold up for the next 21 years. So one has to be conservative in their future value assumptions now. Again, all dependent on Economics.
Thanks again for the explanation. I fully understand how to make these calculations now, thanks to you! I think you got a different answer from Mr. Gallinelli because you used the original cost of sale from the example to determine the closing costs (.07 X 1,695,600 = 118,692). When I plugged in the adjusted numbers (0.07 X 1,606,368) the result was 112,445. When I finished plugging in all the cash flows into the IRR calculator the result was 16.16%. Not the same as in the book, but close.
Your points about real estate investors focusing primarily on the small cash flows and largely ignoring the large cash flows is a point well taken. I've read over forty books on real estate investing in the past two years and "What Every Real Estate Investor Needs to Know About Cash Flow" is the only one that even mentioned IRR. I wonder why so many of these published real estate "experts" are leaving out this extremely important aspect of due diligence?
Again, I can't thank you enough for taking the time out to explain these numbers to me. I look forward to putting these calculations to good use when I return to the United States this winter.
Sincerely,
Justin Greenwood
@Justin Greenwood
Hi Justin,
How are things going for you these days? I recently finished Gallinellis book and I'm hoping to hear that a year later you still feel that you got your money worth!
Jon
Hi Jon,
I still think Frank Gallinelli's book on understanding cash flow is an important text if you're aiming to learn the basics of evaluating a commercial real estate product. I use the concepts taught in the book daily. I thought the case studies were useful exercises in cementing the ideas that Gallinelli taught in the book. Definitely still worth the money I spent on it.
Take care,
Justin Greenwood