Internal Rate of Return (IRR): How to Calculate & Formula

In This Article

Real estate investors and financiers use several models, formulas, and metrics to calculate a rate of return—but some are more necessary than others.

If you haven’t been calculating your real estate investments’ internal rate of return (IRR), it’s time to start. This model can provide tremendous insight into your investment’s return over the total course of ownership.

By the end of this article, you’ll understand:

• What internal rate of return is
• How to calculate IRR
• Why you should use it
• The formula’s limitations
• How it compares to other key models

Ready to get an estimate of your investment property’s return based on IRR? Let’s get started.

What Is Internal Rate of Return (IRR)?

IRR is a financial metric used to determine the total profitability of a potential investment. Just like annual yield, it’s expressed as a percentage.

The IRR calculation is commonly compared to the compound annual growth rate (CAGR) because IRR projects an initial investment and its growth over time.

That’s why you should use the internal rate of return formula as a guide to making educated guesses—not as a concrete statistic. Because it’s a projection, numbers can differ from the actual output.

Formula for Calculating IRR

The internal rate of return is often used with the NPV, or the net present value. The NPV calculates the present value of an investment in terms of cash. When calculating the IRR, you would set the equation equal to a net present value of zero.

Why is the internal rate of return formula set equal to zero? For hardcore finance gurus, IRR is the discount rate that makes an investment’s net present value (NPV) equal to 0.

Take a look at the formula:

Each symbol within the formula stands for a unique variable:

• t: The number of time periods
• C_t: The net cash inflow during the period
• C₀: Total initial investment costs

The equation may look complicated, but follow our step-by-step tutorial below to calculate IRR with a spreadsheet to simplify the process.

How to Calculate Internal Rate of Return Using a Spreadsheet

I typically use Google Sheets for this, but you can use any other spreadsheet application, such as Microsoft Excel.

1. Create a new spreadsheet and fill in the 2B cell with the words “Property Cash Flow Analysis.” Then, make your spreadsheet look like this:

2. Include the initial investment—or the price you paid for the property—to ensure you’re making money. You don’t know what you gained or lost without including this value. Make sure to use a negative value to represent the purchase price. This is money you’ve spent, and you’ll need to subtract this number from your total calculation later on.
   
3. Include the selling price of the property because that ties into the amount of money gained from the initial investment.
   
   Pro tip: Type in “Cash Flow Year 1” and then drag the bottom right pointer to row 23. This will fill in all the cells with the correct number sequence.

4. Fill out each cell with the correct values. Start by inputting the money you spent to acquire the property.
   
5. Input your projected cash flow for the next 20 years of ownership. This is where the internal rate of return variability comes here: Projecting future cash flows is truly a guessing game.

Examples of Calculating IRR

We’ll stick with a simple $100,000 purchase price for both of these walkthroughs. In the first example, we’ll examine the internal rate of return of a property in a flourishing market. In the second example, we’ll take a look at the IRR of an investment if the market takes a turn for the worse.

1. Calculating IRR in a flourishing market

If you set your monthly rent to $1,000, you can expect to earn $12,000 per year. But that is contingent upon a 0% vacancy rate, no maintenance costs, and a stable housing market. And let’s be real: Where and when does that actually exist?

Therefore, when projecting your monthly cash flow, leave a margin for error. For example, if the roof is 24 years old, add a roof replacement expense in year six.

Assuming your monthly rent is $1,000, you may face net negative cash flow in that particular year—after all, roofing costs can easily top $12,000.

2. Calculating IRR in a declining market

Let’s say that the housing market is great when you invest in the property, and $1,000 per month for rent is a premium due to the strong market. But if the market goes into a downturn, forcing your tenants to leave due to costs or eviction, you’re left with an unoccupied property.

Each passing month negatively affects cash flow—and even worse, you might have to lower your rent, as prospects are passing on your property due to price. As one would imagine, negative cash flows are not a great sign.

A situation such as an eviction or too-high costs can impact years of calculation, ultimately leading to a loss or an insignificant profit.

Calculating the internal rate of return requires detailed assumptions and open minds regarding potential changes.

So, let me help you understand how to use the IRR formula to your benefit using the same purchase price as above of $100,000. In this example, I list out the steps I took to set up my cash flow at $800 per month in rent.

1. Acknowledge that I bought the property at $100,000. If my target rent is $800 per month—or $9,600 per year—I can assume that renovation costs will be pretty expensive in the first couple of years. 
2. Use the calculated total renovation cost of $16,972 and divide among the first three years of ownership. To get this number, I multiplied my yearly rent by three. Then I added the cash flows from the first three years and subtracted it from three years of rent.
3. Celebrate that I have a beautifully renovated property and rent is consistent at $800/month.
4. From years four through nine, income is consistent. However, we want to factor in expenses. The only expenses are maintenance and minor repairs, which run about $1,000 to $1,500 per year for my property. (Remember, the exact numbers will be different for each investment.)
5. Don’t forget about large expenses. In year 10, maybe I need a new HVAC system. At some point, it’s best to assume I’ll have a large expense at some point. This lowers my cash flow by more than $7,000 for the year.
6. In year 16, I consider another expense; this time, it’s the roof. This is my first net negative cash flow for a year.
7. Due to market conditions, I increase rent to $950 per month. By the time I sell the property in year 20, the market is hot, and the property sells for $213,000—a whopping $113,000 more than my purchase price.

You might assume that you’ve made a profit based on these numbers. However, this is where the internal rate of return formula comes into play.

On the side of the box where you entered your values, make a new cell that says “IRR=.” In the following box, type “=IRR(C3:C24)” into the function bar. The equation below includes the initial investment, yearly cash flow, and selling price.

Once entered, it should input a percentage value like this:

You’ll see here that the percentage value is 9%, which is not the absolute greatest internal rate of return.

What Is a Good IRR?

If the number you receive using the IRR formula is positive, congratulations! Positive cash flows represent a property that should produce a profit. However, the keyword here is “should.” The IRR formula is not the end all, be all answer, since there are so many factors to consider.

Now you know that if you receive the monthly projected cash flow and sell at the expected price, you should walk away with cash in your pocket.

It is possible to have a negative internal rate of return value—which means you would lose money over the long haul.

I’ll tell you that a 10% to 12% internal rate of return might be quite respectable for an individual investor buying a long-term hold. Regarding syndication, shoot for mid-teens to make opportunities attractive to partners. Overall, aim for a rate between 10% and 15%.

Understanding How to Use IRR

The purpose of using the IRR formula is to identify the rate of discount, which makes the present value of the sum of annual nominal cash inflows equal to the initial net cash outlay.

If you’re interested in calculating the rate of growth for an investment property, this formula is perfect for those who want an annual estimation.

Again, the keyword here is “estimation.” The actual rate of return can totally differ from the IRR, depending on the circumstances.

Benefits of Calculating IRR

The IRR formula is a fantastic, simple way to analyze whether you’ll potentially profit from your initial investment. 

Furthermore, it considers the time value of money, which means you can anticipate adverse effects from market shifts, maintenance costs, and more by manipulating your yearly, hopefully positive, cash flows.

Since it’s as easy as filling in a few cells on a spreadsheet, it’s worth the time. I recommend running this calculation for every investment.

Limitations of the IRR Formula

However, IRR isn’t a foolproof way to determine whether your property will pay off. Since all the numbers, besides the purchase price, are projections, you can never be positive that they’ll be accurate in the long term.

Investors with more experience don’t need help much help calculating their IRR and determining their margin of error. But for those new to the art—or completely foreign to it—the IRR may seem more like an abstract prediction than an educated guess.

The IRR’s simplicity is another crutch. If you’ve invested in real estate before, you know it’s not as simple as lining up the purchase price, cash flows, and selling price on a spreadsheet and calling it a day. Smart investors consider reinvestment rates, mortgage considerations, and other metrics—most of which are not included in the IRR model.

IRR Versus Other Key Models

The internal rate of return is a fantastic way to calculate whether you’ll profit from your investment, but how does it compare to similar key models?

With the IRR formula, you account for the time value of money—a metric other common models don’t calculate. However, remember that the values entered into an IRR formula are mere estimates and should be considered among other models when investing.

Let’s take a look at some of the top investment property formulas.

Compound annual growth rate (CAGR)

The most important distinction between the CAGR and the IRR is that the CAGR is simpler and can easily be calculated by hand. You only need your starting investment, selling price, and the years you’ll likely own the investment.

CAGR and IRR provide a percentage return, but the CAGR only looks at what you start with and end with. The IRR zooms into what’s happening over the years and outputs more in-depth information.

Return on investment (ROI)

Like CAGR, ROI calculates the growth between your starting and ending values. However, ROI doesn’t consider the time period. Instead, it simply calculates the amount of money made.

Here is the ROI formula. Keep in mind that V1 is the ending balance, and V0 is the starting balance.

ROI = (V1 – V0) / (V0)

Now, let’s look back at our model from earlier. The starting price was $100,000, and we sold the property for $213,000 during a hot market. The equation would look like this:

($213,000 – $100,000) / $100,000 x 100 = 113%

A 113% ROI is fantastic, right? Sure, but it’s not the end all, be all number.

ROI doesn’t consider the time value of money or anticipate expenses. For any investments that involve sums of money going in and coming out through the life of the investment, ROI will pretty much ignore cash inflows and outflows, other than the first and the last. This makes it an easy but extremely limited calculation.

Cash-on-cash return (CCR)

The most easily understood real estate investment return metric is cash-on-cash return, usually abbreviated as CCR or COC. The concept is relatively simple, as CCR juxtaposes the cash investment to the cash flow (income minus expenses) received.

For example, let’s say you invest $100,000 cash to buy a fourplex, which generates $2,000/month of gross income, resulting in $1,200/month of cash flow.

Since CCR is usually considered an annual return, we must multiply all monthly numbers by 12. Thus, this $100,000 fourplex generates $14,400 of annual cash flow.

If I invest $100,000 in this fourplex, how quickly would I recover my cash? You can find out with the CCR model.

All we aim to find out is what percentage of $100,000 does $14,400 represent. In mathematical terms, if $100,000 is 100%, $14,400 is x—at which point we solve for x:

X (CCR) = $14,400 / $100,000 = 14.4%

Thus, having paid $100,000 and received $1,200/month of cash flow, our CCR is 14.4%.

Common Misconceptions of the IRR Formula

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 ROI. For many investors, this number summarizes a particular investment’s success or failure.

This is where the 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.

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.

Second, and most importantly, I want to point out that calculating IRR using Microsoft Excel, Google Sheets, or any other financial software is a piece of cake once you jump through the initial hoops of setting everything up.

Related: Cash Flow For Rental Properties: What Is Average or Good?

What Does IRR Take Into Account?

When very sophisticated investors evaluate investment options, they must assign value to things like the time value of money or the cash movement in or out of the transaction. Although the IRR may consider these, it’s important to understand how and to what extent. Let’s talk about these one at a time.

Time value of money

Time value of money is simply the reality that money is more valuable today than at any time in the future.

There are many economic reasons why this is true. Still, it all comes down to buying power and erosion over time due to inflation of the currency supply and the resulting price inflation.

Currency held today does indeed store more buying power than it will eventually—this statement is almost always true.

With this in mind, sophisticated investors must price this value erosion into their return. You have to be careful about how IRR considers the time value of money.

The internal rate of return assumes that future cash flows from some investment projects are reinvested at the IRR, not at the company’s cost of capital, so it’s not super accurate regarding the cost of capital and time value of money like net present value.

Movement of money

Finally, having weighted the opportunity for the time value of money, the internal rate of return also tracks the movement of cash in and out of investment by tagging each movement with a date.

For instance, having purchased an asset, you might receive cash flow for two years, and then you might choose to refinance the building, which would constitute a large waterfall event.

Later, however, you realize that you need to be more aggressive on your refinance, leaving your DSCR too low, which results in your cash flow being unable to support the CapEx. Now you must dip into your pocket to pay for the repairs, which naturally adversely impacts your internal rate of return.

Once this happens, you decide to sell the building. And since there are so many people chasing yield in the marketplace, you manage to sell a money-losing asset at a profit—so you add that profit into the IRR.

Ready to Use the IRR Formula?

At first glance, the formula for IRR looks rather complex. However, once you get the gist of it, you’ll be calculating your internal rate of return in no time.

Keep in mind that this formula isn’t perfect. You’ll need to consider a few other factors to determine the cash flows on various properties and projects. For estimations, the IRR formula is a perfect go-to for predicting a potential return.

Note By BiggerPockets: These are opinions written by the author and do not necessarily represent the opinions of BiggerPockets.