Today’s Quiz:

**What is my return on the following investment:**

**I bought my first investment property back in August 2008. I paid $63,500 for the house using my own cash. I spent the next two months rehabbing it with $34,000 of my own cash. It sat for about 5 months before I lease-optioned it for $1000/month in rent, starting May 2009. In August 2010, I refinanced and pulled out $66,320 in cash. It cost me about $2300 to do that refinance. I spent $1400 on property taxes in August 2009 and another $1200 in property taxes in August 2010. I’m expecting the tenants will be able to purchase the property for $120,000 in July 2011, and I’ll end up netting about $50,000 after all fees, commissions and loan payoff.**

If you don’t know how to calculate the correct answer to that question – and as a real estate investor you SHOULD know – I highly recommend you keep reading…

Anyone who reads my BP blog posts or my blog probably knows that I’m a hard-core numbers guy. While I don’t discount “gut feel” when it comes to investing, if the numbers don’t work, it doesn’t matter how excited my gut might be. I like to examine the financial aspects of a deal before I buy, while I’m holding and then after it’s done. That way, I can mitigate the risk of financial surprises as much as possible.

Seeing as how I’m such a numbers guy, I find it very surprising when I speak to investors who don’t seem to have clue how to analyze a deal or how to determine how profitable a deal was after it’s completed. It’s not that most investors are stupid (far from it!), but many investors have never spent any real time learning the basics of analyzing investment numbers.

I’ve spent many of previous BP blog posts discussing how to analyze a deal upfront to determine if – in theory – the deal is a good one; today I want to tackle the other end of the deal and discuss how to determine whether a specific deal was profitable after all is said and done.

First, let’s clear up some common misconceptions. I’m sure most investors have heard terms like “cash-on-cash return,” “total return,” “return on investment,” etc. These are all terms that indicate in some way, shape or form how successful a particular deal is. The most common I hear people referring to is Return on Investment, or ROI. For many investors, this is the one number that summarizes the entire success or failure of a particular investment.

For those not familiar, ROI is calculated as follows:

**ROI = (V1 – V0) / (V0)**, where V1 is the ending balance and V0 is the starting balance.

A simple scenario for using ROI to calculate an investment return would be as follows: On January 1, you put $1000 into a bank account. On the following January 1, you cash out the account for $1100. Your ROI on the investment is:

**ROI = (1100 – 1000) / (1000) = .1 (or 10%)**

You start with $1000 and end up with $1100 after a year for a return of 10%. Seems pretty straightforward and even the most non-mathematical among us should be able to do that type of calculation.

Now what if I give you the following scenario: On January 1, you put $1000 into a bank account. On February 1, you put another $500 in the same account. On September 1, you removed $250 from the account. And then on October 1, you removed another $250. On the following January 1, you cash out the account for $1100. Like the first example, you started with $1000 on the first day of the year, and you finished with $1100 on the first day of the following year.

So, is your return still 10%? At first glance, you might think so. In fact, using the ROI formula above, the ROI on this investment appears exactly the same as the previous investment. But, given that you had $1500 invested for several months of the investment period (from February through September), you’d think that a 10% return should have resulted in a higher ending balance. So, in actuality, your ROI is probably a good bit less.

As you can see, the ROI formula has two big limitations:

- For any investments that involve sums of money going in and coming out through the life of the investment, ROI will pretty much ignore every in-come and out-flow other than the first and the last;
- ROI doesn’t take into account the amount of time an investment was held. For example, let’s say in that first example, the $1100 was cashed out after 5 years instead of one – according to the ROI formula, the return is still calculated at 10%.

This is where Internal Rate of Return (IRR) comes in. IRR is the much more powerful cousin to ROI, and while also more complicated than ROI, it’s an essential tool that all serious investors need to understand. I’m not going to go into the nitty-gritty of how IRR is used (and yes, there are some downsides to using IRR that I won’t go into here), but I do want to review the basics…

First, you may hear IRR referred to by different names – on your mortgage truth-in-lending statements as annual percentage yield (APY), as the “effective interest rate” of a loan, as the discounted cash flow rate of return (DCFROR), or sometimes even as the generic rate-of-return (ROR). All of these things essentially mean the same thing, and serve to underscore how important and versatile the concept of IRR is when it comes to investing and finance.

(For the other hard-core finance geeks out there, IRR is most specifically defined as the discount rate that makes an investment’s net present value (NPV) equal to 0.)

Second, and most importantly, I want to do a quick summary of how to calculate IRR for a given investment. Unlike ROI, you can’t calculate IRR in your head. In fact, even doing it with pencil and paper is practically impossible. But, calculating IRR using Microsoft Excel (or any other financial software) is a piece of cake.

In Excel, list the monthly (or annual) dates of your investments in sequential order in one column. Next to each date (month or year), list the aggregate in-come or out-flow for that time period (in-comes are positive and out-flows are negative). Then use the XIRR function in Excel to calculate your IRR. Using the example above where we deposited $1000 into a bank account on Jan 1, deposited another $500 on Feb 1, removed $250 on Sept 1, removed another $250 on Oct 1, and then removed the remaining $1100 on following Jan 1, our Excel calculation would look as follows:

As we suspected above, our return was a good bit less than 10% (almost 25% less!), despite our ROI calculation of 10% return. As you can see, doing a quick ROI calculation in your head would left you feeling a lot better about your investment than it probably should have.

If there is enough interest, I’m happy to go into more complex uses of IRR in future posts, and am also happy to discuss some IRR nuances that sometimes affect the ability to accurately determine returns of some types of investments. In the meantime, if you’re interested in learning more about IRR and how to calculate it using Excel, there are some good online tutorials.

## 37 Comments

J, mahalo for the thorough article. For those who are not familiar with investing principles, this should be printed up and used whenever an investment opportunity comes about. Kudos.

Awesome! Thank you

Great article J.Scott!!! In the good old days of rapid appreciation the financial tools of investing were long forgotten. Today -they are absolutely essential.

Therefore IRR is really useful for investment properties when you have a purchase, cashflows over a period of time and then a sale.

In a flip scenario it would not make sense since there is no income over time. Correct?

It would be great if you know of some good spreadsheets out there that can be used to calculate this.

Hey Luis,

Even for flips, IRR is a good tool. First, even with flips, money goes in at different times. Second, the problem with using a simple ROI calculation for flips is that ROI doesn’t take the compounding effects of time aspect of the return into account.

For example, let’s say you buy a property for $40K on Jan 1, put in $60K of rehab over the next two months and then sell in the fourth month for a net of $120K. You make a $20K profit on your $100K total investment in 4 months.

If you used the simple ROI calculation you get:

$20K / $100K = 20%

Now, a lot of people would say if I made a 20% return in 4 months, that’s equivalent to a 60% return in 12 months (they “annualize” their return). But, that’s not really the case, because that doesn’t take into account that for the next 8 months of the year you have $120K to invest as opposed to just that starting $100K. In other words, annualizing the simple ROI ignores compounded returns.

If you did the IRR of this flip (with $40K going in Month 1, $30K going in Month 2, $30K going in Month 3, and then $120K coming out Month 4), you’ll see that your IRR is actually 183%! Much, much higher than your assumed 60% return from the simple calculation.

Again, there are some nuances to using IRR that probably makes your return a bit smaller than 183% (for example, it would require that you NEVER have your money sitting idle for any part of the year — which is completely unrealistic)…but in theory it’s a much better predictor of your return than a simple ROI using annualization.

All that said, the reason IRR is probably more important for rentals is that with rentals, the IRR result may impact your decision to move forward or not. With flips, calculating IRR probably won’t affect your decision. Since a typical flip will see returns above 100% (and many times above 300% or 400%), it’s rare that your IRR will be so low that you’d decide not to do a flip.

Hope that helps…let me know if you have any other questions…

Btw, as for spreadsheets, just create your own. In the example above, the spreadsheet would only have 8 cells: Four in column A listing the months (Jan 2010, Feb 2010, Mar 2010, Apr 2010) and four in column B listing the outflows and income (-$40K next to Jan 2010, -$30K next to Feb 2010, -$30K next to Mar 2010 and $120K next to Apr 2010). Then, you’re IRR calc would be:

XIRR (B1:B4, A1:A4)

Try it out! Convert the result to a % and you should get 183.52%…

J, thanks for taking the time to go into this deeper. I understand what you are saying and realize that is an accurate gauge of the investment. It’s just that I am a bottom line type of person so even thought the IRR might be x% I still want to know what is my profit. But you are right the IRR is a good indicator of wether it was a good investment or not.

I will add it to my spreadsheet for my flips and see if it helps me with the “big picture”

thanks again

J,

I’d definitely be interested in hearing about some of the more specific uses of IRR.

I’m a student in Santa Barbara california – I am trying to compile a “comprehensive” list of the formulas a Real Estate Investor or a RE investment broker may use to share w/ some of my peers. These kinds of articles help me out immensely w/ that project.

Might you have any recommendations of places online where I can find recommended RE formulas for investment and brokerage?

Thanks J,

Eric

Eric, try these ones for starters:

http://www.durangoinvestments.com/realestate/investment/information_resources.html

J

I started to calculate your IRR on the investment and realized I was missing some numbers. You mention Property Tax but not Insurance, Utilities and other costs. You said you borrowed money but I don’t know at what interest rate. You will also have Property Tax the third year. You have interest deductions for your mortgage interest and income tax to pay for the rental income. Did you spend some of your own “elbow grease” on the rehab? Did you have costs in getting it rented? Depreciation on your taxes? And it goes on and on. And if we get into the ‘time value’ of money, the money you drew out is worth less than the money you put in.

If I did your numbers correctly, you put in $102,400 and will put back in your pocket $116,320. But you will have rental income, rental expenses and other expenses. But just looking at your numbers and a 3 year investment, it would appear you are getting about 4.3% on your original investment. I just did this in my head and know it is not completely accurate as you put money in and took money out at different times. Excel will calculate this for you much more accurately, but when it is all said and done, you put in X, pulled out Y, and your return (dollars) is Y minus X. Just as your example of 10% vs 7.61%, in both cases, the investor started with $1,000 and ended on day 365 with $1,100. The difference being that the investor put in another $500 mid term and pulled it out later mid term. Thus the second scenario was paying an overall lower interest rate, your 7.61%.

You are absolutely correct that investors need to understand IRR (or discounted cash flow rate of return).

The one scenario where IRR will not work is if you have different costs of capital. But that is a whole other discussion.

Great Blog J!!

Hey Mike,

I didn’t expect anyone would really take me up on the quiz, so I didn’t post all the details… 🙂

But, now that you inquired, I’m happy to share all the details. Instead of listing every penny of income and expense, here is a link to my spreadsheet where I calculate the IRR of this investment based on every income and expense, including those anticipated:

http://www.123flip.com/wp-content/uploads/Churchill_IRR.xlsx

As you can see, the anticipated return is around 14.5% on this one…

Whoops…actually it’s a 12.1% return…I left out my mortgage payments for the next year now that I’ve refinanced!

J Scott,

Would you mind explaining why your sale of $120K might only be $50K of sales revenue after fees? What kind of fees are incurred with a sale that could be this significant- Is it typical to see only 40% of sale price as income?

Thank you

-Michelle

Hi J Scott,

I liked the details on your first question about deal on this thread. I calculated this using only the info you posted on top of thread(so was missing some details) but got 15.33% IRR on my calculation and it was great to compare your analysis to mine. But i have one question what is the Credit Repair for Buyer($79) entry that shows up after your refinance line, I have not found much information about this and most people say to not include repairs on your agreement for loan because of bank weariness to do it. Most say its a red flag for a lender. Any details on what this represents in your example would be much appreciated, and why if it was a choice you made to include on your deal.

Thanks

Rick

Hi Rick,

This was literally a payment for “credit repair” for the buyer. He had bad credit, and we were paying for credit counseling to improve his credit so he could get a loan.

Thanks,

J

I like the creativity in helping the borrower to get the loan so you could get a deal done. I’m a beginner and learning as much as I can at the moment and I hear you use that term a lot in podcasts, nice to see an example of creativity that worked for you. I will add this to my future repertoire of ways to help get a deal done.

J,

Amazing! Perfect timing! I am looking for my first investment property and this stopped me in my tracks. Keep it coming! I love it. I am leaning so much from you guys here at Bigger Pockets! Thank you, Thank you, Thank you!

I realize this is rather late to post comments for the topic but I wanted to mention that came across a book that is very helpful for analysis.

“What every Real Estate Investor Needs to Know About Cash Flow” by Frank Gallinelli”.

Explains everything in plain english.

Curtis Olson

Hey all-I second that it’s probably late and maybe not best to post on this forum, but I have been all over these boards and other websites and can’t seem to find the right answer to this question (or finding differing answers). I am doing real estate valuations with the standard 10 year forecasting model. My question arises in trying to figure out the IRR in excel and whether I combine the first year’s revenue stream (the negative purchase cost with the positive first year’s NOI) or separate the negative purchase cost into “Year 0”? So let’s assume I bought the property in February 2011 for 1,000,000. I will sell it in year 10 for $1,200,000. That first year (2011) after I bought it, i had an NOI of $40k. Then in year 2 (2012), I earned $51k, in year 3 (2013), I earned $52k, and so on as follows:

Purchase Price: $1,000,000 and Sales Price (Yr 10): $1,200,000

Year 1 NOI: $40k

Year 2 NOI: $51k

Year 3 NOI: $52k

Year 4 NOI: $53k

….

Year 10 NOI: $59k + $1,200,000

so does my IRR equation in excel look like this:

1) ($1,000,000.00)

2) $40,000.00

3) $51,000.00

4) $52,000.00

5) $53,000.00

6) $54,000.00

7) $55,000.00

8) $56,000.00

9) $57,000.00

10) $58,000.00

11) $1,259,000.00

And therefore run: IRR(1:11) (where the first input is technically “year 0”)

Or does it look like this:

1) $(960,000.00)

2) $51,000.00

3) $52,000.00

4) $53,000.00

5) $54,000.00

6) $55,000.00

7) $56,000.00

8) $57,000.00

9) $58,000.00

10) $1,259,000.00

And therefore run: IRR(1:10) (where I’ve combined the purchase price and the NOI for the first year)

Any help would be appreciated because as you can see, you get two different answers (for these particular numbers, if you run it where you separate out the Purchase cost into Year 0 (and therefeore have 11 inputs) as I did in the first example above, you get an IRR of 6.73%. If you combine the Purchase cost and first year’s NOI (and therefore have 10 inputs) like the 2nd example, you get an IRR of 7.70%.

I agree with your first calculation. The $1,000,000 is put in in Jan of year 1 and the NOI is at the end of Year 1 so there will be two entries. When the property is sold I did two seperate entries and it worked out the same as combining the $1,200,000 and the $59,000 as they both happened at the end of the year. The number of inputs is not as important as the timing of individual inputs. I came up with 6.73%

Hope this helps

Mr. Scott,

when calculating IRR, would you consider cash out at refi (disbursements) as increased cash flow for that period? i.e. return of vs return on investment

Your input is greatly appreciated.

Hi Peter,

Yes, that’s exactly how you handle it. The benefit of IRR over other rate of return functions is that you can factor in ALL inflows and outflows of cash at different periods of time. So, if you do a cash-out refi, that’s an infusion of cash that increases your overall return (because you can put that money to work while you have it).

Hello,

I was wondering how to calculate IRR in a sale Leaseback transaction on excel. also what is the difference between IRR, MIRR, and XIRR? Sorry if its a little unrelated. Thanks all

Peter

Hi J. Scott,

I’m just catching up a little bit late with your excellent post and the subsequent exchange of communication with other members. After your easy to understand explanation about the IRR I no longer have any problem understanding the concept and I already created my own Excel spreadsheet.

Actually, I’m working right now with an acquisition that will have a couple of private investors through the PPM structure. We are trying to establish an IRR hurdle at which point the sponsor can begin participating in the profits. I got some valuable information from another BP post from Brian Burke in which he mentioned he uses 8% as a hurdle point and he estimated that point will be reached at approximately year 4 (assuming the property will be sold in year 5).

The situation that I’m having is that I’m obviously getting negative IRR from years 1-5 and it is not until the principal debt is totally repaid that we get into positive IRR. In this scenario there is no way the sponsor could begin participating in the profits before the total debt is paid. I have begun questioning myself if there should be any particular way to calculate de IRR when a hurdle is established and there is going to be a balloon payment at year 5. Below is an extract of my calculations:

IRR Calculation in 5 Years

Year 0 1/1/2014 (1,000,000)

1st year 1/1/2015 115,000 -89% IRR

2nd Year 1/1/2016 115,000 -60% IRR

3rd year 1/1/2017 115,000 -39% IRR

4th year 1/1/2018 115,000 -25% IRR

5th year 1/1/2019 1,115,000 11.49% IRR

Gross Return 41%

Any insight on this will be highly appreciated

Thanks in advance.

Alex

Alex –

I hope you’ve figured this out by now, but the profile you see is typical of most b/h projects. The significant money is made at the end, when the asset is sold or refinanced and the original investors are taken out. But operating income is also profit, and it’s usually distributed as the asset operates.

Our Reg D 506(c) funds are a little different. They offer 100% gap funding to selected flippers in specifically targeted geographic areas. Over the course of the fund’s lifetime we expect to turn each dollar 6-10 times, and we are generating profit as each flip is completed and sold.

As we modeled the fund over time, we developed a range of IRRs, and chose a preferred rate (X%) that we believe is highly achievable (X is not too far from Brian’s number). We also chose to distribute all profits quarterly, rather than reinvest the profits. Our investors receive the first X% of those dollars, then we share the rest with them at a published split. As the fund begins to approach the end of its lifetime we begin classifying dollars as return of capital instead of profit, and of course we don’t share that.

We decided against the IRR-based sponsor promote you mention, because we couldn’t keep it fair to all parties with the wide range of IRRs that might occur. We quote a simple X% pref to our investors, not an IRR.

I also tried an IRR-based waterfall in my own flips (not related to this fund) but instead decided to share profits with my investors at a fixed rate (let’s say 50-50). I identify a range of expected sales prices, and if I sell in that range I maintain that 50-50 split. If I sell above that range I receive a progressively higher percentage of the split, but I’m penalized if we sell below that amount–all the way down to a zero percent share of the profit if the sale price is low enough.

However, I do use IRR when I discuss potential returns with investors because they’re familiar with it, and as I acquire properties to stabilize and hold I’ll be able to estimate IRR within a fairly narrow range (unlike flips) and will probably use IRR-based waterfalls.

In all cases, it’s part of my analysis; it just isn’t always a good way to divide profit.

P.S. This is not an offer to sell, nor a solicitation of an offer to buy any security. Capisce?

Hi, Scott

Sorry I come to this discussion a little late; I backtracked from your most recent post concerning IRR. Both the initial material and the comments were great, to the point and carefully presented.

I certainly agree that analyzing IRR is an important tool in evaluating an investment, I think it is important to keep in mind all the net returns to be tracked must be based on a carefully developed Operating Statement extended over the time in question.

And therein lies much of the problem when looking at prospective deals. In a word, the operating statement must include realistic allowances for vacancies, management fees, repairs and maintenance, replacement reserves, and the “unforseen” (contingency). Too often such line-items are understated, omitted, even finessed–in the heat of the moment or enthusiasm for the deal: the Best Case Scenario.

In a word, I’d start with a careful review (due diligence if you will) of the operating statement and its underlying assumptions. What we used to warn against was “garbage in = garbage out”, accidental or intended. That evaluation done, go to work on the IRR.

Great discussion.

Thanks

Great post, J. I just wanted to point out to everyone that IRR is also one of the key metrics of the hedge fund/private equity world, so when you start competing for capital at a high level, you absolutely MUST talk in terms of IRR, as it’s what is expected of you and is how your investment opportunity will be compared to others.

Was the answer 24% on the initial question?

Thank you! No where else on the entire WWW could I find a more clear and simple explanation that has enlightened me to the use of IRR. I mean you actually spoke English to someone who doesn’t know what it is. Every other article I found appeared to be written for financial scientists who just missed that day in class years ago where IRR was discussed.

A lot of good comments here already. I would just add that anyone doing this analysis should also incorporate an equity multiple (EM) test. This is simply: Sum of Cash Outflows / Sum of Cash Inflows, expressed as a ratio (e.g. 2.5x).

IRRs can be easily manipulated depending on the time horizon of the investment, so looking at the equity multiple can provide another tool to help you choose between investment opportunities.

You know, J…

Not for nothing, you should actually write a whole book about the numbers you love so much. I’d buy it, because I’m not a finance major, and there are a lot of investment “numbers” that I’m unaware of that could probably be a big help. In addition to the basic numbers any real estate investor needs to know. And I’m sure I’m not the only one who’d pay for that book.

Just my $.02. Hmm…I wonder what my ROI on that will be… 😉

I have seen IRR equations which take appreciation into account. Since there is no input for it through the excel method, is it safe to assume the appreciation of the asset, or inflation of money is not taken into account when using the XIRR function?

IRR simply takes inflows and outflows and applies a function to them. If you want to factor in appreciation, you need to determine how that equity will be disbursed to you and make it an inflow at the appropriate time. Generally, that means an increase of sale proceeds or a cash out refinance.

That said, I don’t like factoring in appreciation to my deals. I’m not smart enough to forecast the future.

What number do I use at the begin of the series? The total amount of money invest and after that all the total returns?

I have now this:

Internal Rate of Return

1/1/2000 -410,000

1/1/2001 35,547

1/1/2002 37,185

1/1/2003 38,875

1/1/2004 40,616

1/1/2005 42,412

1/1/2006 44,262

1/1/2007 46,171

1/1/2008 48,138

1/1/2009 50,167

1/1/2010 52,258

With a Cash on Cash ROI of 24,05% but an Internal Rate of Return of 1.05%.

Or do I have to use the: Cash Outlay of € 90,000 ? And the Total Cash Flow:

21641

22887

24172

25496

26861

28267

29717

31211

32750

34337

Hope you can help me out.

I think I got the answer, I didnt calculate the sell price and the end of the 10 years.

Hi, I am a new member and am trying to learn real estate math. This was very interesting and IRR sounds like an important concept to learn in addition to ROI. I may have missed it, but where is the calculation for the original quiz you posted, the property purchased in August 2008? Thanks!

See this comment:

https://www.biggerpockets.com/renewsblog/2010/09/02/introduction-to-internal-rate-of-return-irr/#comment-86182

J,

This was a great article to show how to IRR is calculated.. I was wondering how you calculated IRR for a while but never dive into it until today. Of course, the answer was in bigger pockets!! Surprise. Surprise 🙂

Also, I didn’t know I there was a simple excel function calculates IRR.

Awesome!! Thanks again

Kuntay Talay