Log In Sign Up
How to Calculate Internal Rate of Return (IRR) Using a Spreadsheet
Pro Only

How to Calculate Internal Rate of Return (IRR) Using a Spreadsheet

6 min read
Matt Myre

Matthew Myre is the founder of PurpleCup Digital, a web design and digital marketing agency. He’s also a former real estate agent and a freelance writer specializing in real estate economics, news, industry analysis, and more.

Experience

Matthew has over ten years of experience in SEO, digital marketing, content marketing, web design, and other related subjects. In 2019, he entered the real estate industry as an agent, later to become a writer for BiggerPockets and other real estate firms throughout the US.

In 2020, just as the COVID-19 pandemic started, he started PurpleCup Digital and began working with different firms such as nonprofits and consultants. Since then, he’s been able to help clients, both new and long-time established, elevate their marketing systems and redesign their websites.

He’s also attempting to establish himself a YouTuber, where he speaks about productivity, managing workflows, and developing better lifestyles. These topics are also written about on his young personal website, matthewmyre.com.

Matthew spends most of his time reading, working on projects, and enjoying time with friends and family. He’s also an avid Monopoly player.

Press

Matthew contributes to BiggerPockets and other real estate publications. He’s been featured from time-to-time in publications such as Realtor.com for his thought leadership in real estate digital marketing.

Education

Matthew is self-educated in web design, SEO, and real estate and is currently pursuing a J.D.

Follow

Instagram

LinkedIn

Personal Website

PurpleCup Digital

As a guest reader you have 1 free Pro article left

Pro members get unlimited access to expert market analysis, property analysis calculators, exclusive events, and more.

Sign in Already a member?

Real estate investors and financiers use a number of models, formulas, and metrics—but some are more necessary than others.

If you haven’t been calculating the internal rate of return (IRR) for your real estate investments, 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 model’s limitations
  • How it compares to other key models

Let’s get started.

What is the 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

IRR calculations are most commonly compared to the compound annual growth rate (CAGR), because IRR projects an investment’s growth over time. That’s why you should use the IRR as a guide to make educated guesses—not as a concrete statistic: Because it’s a projection, numbers can differ from the actual output.

The IRR can also be used in conjunction 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 an NPV with the value of zero:

1600789882 image

  • t: The number of time periods
  • Ct: The net cash inflow during the period, t
  • C0: Total initial investment costs.

The equation may look complicated. Don’t worry. Follow our step-by-step tutorial to calculating IRR with a spreadsheet to simplify the process.

Calculating the internal rate of return

For this, I’m using Google Sheets, but you can use any other spreadsheet application such as Microsoft Excel.

Start by creating a new spreadsheet anding fill in the 2B cell with the words “Property Cash Flow Analysis”.

Then, make your spreadsheet look like this:

1600790351 image

The reason to include the initial investment—or the price you paid for the property—is to ensure you’re making money. Without including this value, you don’t know what you gained or lost.

We’re also going to include the selling price of the property, because that ties into the amount of money gained from the investment.

Pro tip: You can type in “Cash Flow Year 1” and then drag the bottom right pointer down to row 23. This will fill in all of the cells with the correct number sequence.

Now, fill out each cell with the correct values. Start by inputting the money you spent to acquire the property. For this walkthrough, we’ll stick with a simple $100,000 purchasing price.

1600790431 image

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.

Next, input your projected cash flow for the next twenty years of ownership. This is where IRR’s variability comes in: Projecting cash flow is a guessing game.

If you set your monthly rent to $1,000 per month, then you can expect to earn $12,000 per year. But that is contingent upon a zero-percent vacancy rate, no maintenance costs, and a stable housing market.

Therefore, when projecting your monthly cash flow, leave a margin for error. For example, if the roof is 24 four years old, add a roof replacement expense in year six. That means that, assuming your monthly rent is $1,000, in that particular year you may face net negative cash flow—after all, roofing costs can easily top $12,000.

Here’s another example. 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 downturns, 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 you can imagine, this type of situation can impact years of calculation and ultimately lead you to a loss or an insignificant profit. That’s why calculating an IRR requires detailed assumptions and open minds regarding potential changes.

Below, you can see how I set up my cash flow at $800 per month rent:

1600790577 image

There’s a lot to break down here. 

  • First, notice that we bought the property at $100,000. If my target rent is $800 per month—or $9,600 per year—then we can assume that renovation costs will be pretty expensive in the first couple of years. 
  • Here, I’ve calculated a total renovation cost of $16,972 divided among the first three years of ownership. To get this number, I took my yearly rent and multiplied it by three. Then, I added up the cash flow from the first three years and subtracted it from three years of rent.
  • Now that we have a newly renovated property, our rent is $800/month and it’s just a matter of collecting it consistently. From years four through nine, our income is consistent. The only expenses are maintenance and minor repairs, which run us about $1,000 to $1,500 per year for my property. (Remember, the exact numbers will be different for each investment.)
  • Finally, in year 10, there’s a new expense that cuts deep—maybe a new HVAC system. It’s best to assume you’ll be dealing with an expensive problem. This lowers our cash flow by more than $7,000 for the year.
  • Things go back to normal until year 16, where we have another major expense, likely a roof. This is the first time we’re net negative in cash flow for a single year.
  • The good news? Due to market conditions, we increased rent to $950 per month. By the time we sell the property in year 20, the market is hot and the property sells for $213,000—a whopping $113,000 more than your purchase price.

You might assume that based on these numbers, you’ve made a profit. However, this is where the IRR equation 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. This equation includes the initial investment, yearly cash flow, and selling price.

1600790671 image

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

1600790679 image

If the number you receive is positive, congratulations! The property should produce a profit. Now you know that if you receive the monthly projected cash flow and sell at the expected price, then you should walk away with cash in your pocket.

Note that it is possible to have a negative IRR value—which mean you would lose money over the long haul. As an investor, aim for a rate between 10 and 15 percent.

Benefits of calculating IRR

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

Furthermore, it considers the time value of money—which means you have the ability to anticipate adverse effects from market shifts, maintenance costs, and more by manipulating your yearly cash flow.

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.

The limitations of IRR

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

Investors with more experience have little trouble modeling their IRR and thus determining their margin of error. But for those who are 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, then you know that it’s not as simple as lining up 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 IRR is a powerful model in its own right, but how does it compare to other key models that are similar?

Compound annual growth rate

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, your selling price, and the number of years you’ll likely own the investment.

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

Return on investment (ROI)

Much like the 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.

For instance, 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:

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

A 113 percent ROI is fantastic!

But once again, depth is the key difference between ROI and IRR. ROI doesn’t consider the time value of money, nor does it anticipate expenses. This makes it an easy, but limited calculation.

The internal rate of return is a fantastic way to calculate whether you’ll profit from your investment.

The best thing? You can account for the time value of money—a metric other common models don’t calculate. But remember that the values entered into an IRR formula are mere estimates and should be considered among other models when making an investment.

But with all things considered, the IRR is a fantastic way to compare potential investments and get a glimpse of the future. 

How do you use IRR?

Tell us below!