IRR Formula for Rental Property


Are you curious about the IRR formula because you want to model expected returns from a potential real estate investment? Maybe you just sold a rental property and are wondering how to calculate your all-in return over the life of the deal?

Well, unless you have a master’s degree in mathematics or finance, the actual IRR formula for real estate won’t get you very far. You can’t actually calculate an internal rate of return (IRR) by hand without running through a lot of painful iterative calculations. That’s why real estate investors have been using Excel to do their dirty work for decades. Google Sheets will now do the trick as well.

IRR Formula Options in Excel

What’s the IRR formula in Excel? Well, there are a couple of options…

For regular annual cash flows, you can just use:

=IRR(values)

Each value in the range is assumed to represent a distinct annual cash flow. You’ll of course need a negative value to appear at the start of the range, representing your initial investment.

That said, the preferable way to calculate IRR in Excel is actually with:

= XIRR(values, dates)

With this alternative IRR formula, each value in the range can be tied to a specific date, rather than having Excel assume that each cash flow occurred in regular one year intervals. This more accurately represents actual cash flows, which tend to be irregular and are often distributed monthly or at random dates throughout the year.

Why is the IRR Formula Important in Real Estate?

Calculating the internal rate of return is highly valued in real estate analysis because it’s the most reliable way to compare all-in deal performance to all sorts of other investments. “Other investments” here can mean anything from another real estate deal to stocks, bonds, commodities, or even a partnership interest in a business.

Since IRR is calculated as a percentage, rather than aggregate dollars, it’s an easy way to compare your returns even when the actual dollars invested in one project are much larger than another project.

The other big selling point for using the IRR metric for real estate is that it can handle capital expenses, leverage, interest and principal payments, and the generally lumpy cash flows that come with fix and flips and significant value appreciation.

Since (X)IRR essentially links each dollar in and each dollar out with a precise date, the IRR is a highly accurate assessment of performance that takes into account speed of execution and the time value of money.

How Might IRR Calculations Lead Us Astray?

The simple fact that the IRR calculation results in a percentage (instead of actual dollars) is both it’s strength and it’s achilles heel. While this aspect makes IRR useful when comparing a real estate deal to a stock or bond investment, it makes IRR somewhat problematic when you’re trying to decide what to do going forward.

Let’s say you’re presented with a real estate opportunity that promises a killer 35% IRR. Sounds great, right? The danger is that if it’s a relatively small deal that requires a lot of your time and attention, it could still end up being a real loser. A much larger deal with a 22% projected IRR might be the smarter choice when you consider the profit potential in terms of actual dollars.

After all, I haven’t yet found a bank that will let me deposit an IRR percentage into a checking account!