How to Calculate IRR on a Rental Property in Excel

Year-one cash-on-cash return tells you what a rental pays you now. It says nothing about loan paydown or the sale at the end. IRR is the metric that puts all of it on one timeline. It is the single annual rate that makes every dollar you put in equal every dollar you get back, adjusted for when each dollar moves.

This guide builds a 10-year rental IRR in Excel on one worked example. It is the same duplex used in our cap rate vs cash-on-cash guide. That deal returned 0.09% cash-on-cash in year one, so it makes a good test of what IRR does and does not tell you.

The deal

| Input | Value | |---|---| | Purchase price | $485,000 | | Down payment (25%) | $121,250 | | Closing costs (2%) | $9,700 | | Loan | $363,750 at 6.75%, 30 years | | Year-one NOI | $28,423.20 | | Rent and expense growth | 3% a year | | Appreciation | 3% a year | | Hold | 10 years | | Selling costs | 6% of sale price |

Total cash in on day one is $121,250 plus $9,700, which is $130,950.

Keep the inputs in their own block below the timeline. The formulas in this guide use B20 year-one NOI, B21 growth, B22 appreciation, B23 price, B24 down payment, B25 closing costs, B26 loan amount, and B27 interest rate.

Step 1: Lay out the timeline

Put the years across one row. Year 0 is the purchase date and years 1 to 10 are the ends of each year of ownership.

Each column is one period. IRR in Excel assumes the periods are evenly spaced, so one column per year keeps the math honest.

Step 2: Yearly NOI and debt service

NOI goes in row 3:

C3 = $B$20*(1+$B$21)^(C1-1)

Rent and every expense grow at 3%, so NOI also grows at exactly 3%. Year 10 NOI comes out to $37,085.83. If your expenses grow faster than rent, give them their own row and their own growth rate.

Debt service is flat for a fixed-rate loan:

C4 = 12*PMT($B$27/12, 360, -$B$26)

That is $2,359.28 a month, or $28,311.31 a year.

Cash flow before tax in row 5 is =C3-C4. It runs from $111.89 in year 1 to $8,774.52 in year 10. The sum over ten years is $42,727.07.

Step 3: The sale in year 10

Three lines go in the year 10 column only.

Sale price is the purchase price grown at the appreciation rate:

=$B$23*(1+$B$22)^10 returns $651,799.44

Selling costs at 6% are $39,107.97.

Loan balance after 120 payments comes from FV:

=FV($B$27/12, 120, PMT($B$27/12, 360, -$B$26), -$B$26) returns $310,282.38

The sign convention trips people up here. The loan is entered as a negative present value and the payment as a positive number, so FV returns the balance still owed as a positive figure. Check it the easy way. The balance should be a bit below the original loan, since early payments are mostly interest. Here $53,467.62 of principal was paid off in ten years.

Net sale proceeds are $651,799.44 minus $39,107.97 minus $310,282.38, which is $302,409.09.

Step 4: The cash flow row and the IRR

Row 7 is the only row IRR reads:

Then:

=IRR(B7:L7) returns 10.63%

The first value has to be negative and the rest mostly positive. If you enter the investment as a positive number, IRR returns an error or a meaningless figure.

If your cash flows land on irregular dates, use =XIRR(values, dates) instead. A mid-year purchase or a sale in month 7 of year 10 are two examples. XIRR uses the actual day count so a partial year is not treated as a full one.

Step 5: Two numbers to show next to IRR

Equity multiple is total cash back divided by cash in:

=SUM(C7:L7)/-B7 returns 2.64x

IRR is a rate and the multiple is a size. A 30% IRR on a deal you exit in six months can return less money than a 10% IRR over ten years. Show both.

MIRR fixes the one assumption IRR hides. Plain IRR assumes every cash flow you receive is reinvested at the IRR itself. MIRR lets you set a realistic reinvestment rate instead:

=MIRR(B7:L7, 6.75%, 4%) returns 10.33%

The gap is small here because most of the money arrives in year 10. On a deal with large early cash flows the gap gets much wider.

Where the return comes from

Total profit is $214,186.16. Break it into parts and the picture changes:

| Source | Amount | |---|---| | Ten years of cash flow | $42,727.07 | | Principal paydown | $53,467.62 | | Appreciation after selling costs | $127,691.47 | | Closing costs at purchase | −$9,700.00 | | Total profit | $214,186.16 |

About 60% of the profit is appreciation. That is the one input you control least.

The sensitivity table

Same deal with one assumption changed per row:

| Scenario | IRR | Equity multiple | |---|---|---| | Base case | 10.63% | 2.64x | | Appreciation 0% | 3.96% | 1.44x | | Appreciation 2% | 8.63% | 2.20x | | Appreciation 4% | 12.49% | 3.11x | | Rent and expenses flat | 8.79% | 2.32x | | No selling costs | 11.84% | 2.93x | | 40% down instead of 25% | 9.58% | 2.28x | | All cash, no loan | 8.10% | 1.90x |

Three lessons come out of it.

  1. Appreciation carries this deal. At 0% the IRR drops to 3.96%. That is below what the 6.75% loan costs, because the property never earns its keep on cash flow alone.
  2. The loan hurts year one but helps the hold. Levered IRR is 10.63% and all-cash IRR is 8.10%. Leverage was negative on a year-one basis. It turns positive over ten years only because appreciation and paydown land on the full $485,000 while you put in $130,950.
  3. More cash down lowers IRR. Going to 40% down raised year-one cash-on-cash in the earlier guide. Here it cuts IRR from 10.63% to 9.58%. The two metrics point in opposite directions, and that tension is the actual decision.

Four mistakes to avoid

  1. Leaving out selling costs. Dropping the 6% adds about 1.2 points of IRR in this example. Agent fees and transfer taxes are real cash.
  2. Using the original loan amount at sale. Subtract the balance from FV, not the starting loan. Otherwise you understate proceeds by the principal you paid down.
  3. Treating a pre-tax IRR as after-tax. This build ignores depreciation, income tax, and tax on the gain at sale. It is fine for comparing deals on the same basis. It is not your take-home return.
  4. Trusting one IRR. A single figure hides which assumption drives it. Always build the sensitivity rows. If the deal only works at 4% appreciation, write that down before you buy.

Summary

Put the years in one row. Grow NOI, subtract flat debt service, and add net sale proceeds in the last year using FV for the loan balance. Then run IRR, the equity multiple, and MIRR on that single row. The sensitivity table matters more than the headline rate because it shows what the deal depends on.

If you want this built already, the rental property analyzer has a 10-year projection with IRR, equity multiple, loan paydown, a sale year you choose, and a four-property comparison, all in open formulas.

For education and planning only, not financial advice.