Rental Property IRR Spreadsheet: 10-Year Projection, Loan Paydown, Sale Proceeds and Equity Multiple (Excel & Google Sheets)
Short on time? The Rental Deal Analyzer ($15) is the finished, tested version of what this tutorial builds.
Cap rate and cash-on-cash describe one year. But nobody buys a rental for one year: rents rise, the loan gets paid down, the property (hopefully) appreciates, and at the end you sell and pay an agent. IRR, the internal rate of return, rolls all of that into one annual percentage: the return on your cash from the day you buy to the day you sell.
This tutorial builds a two-tab projection that works out yearly cash flow, loan balance, property value, net sale proceeds, IRR, equity multiple and total profit, plus a table that splits the profit into cash flow, appreciation, loan paydown, selling costs and upfront costs, so you can see how much of the return depends on appreciation. It works the same in Excel, Google Sheets and LibreOffice. We tested every formula below by building a workbook with these exact formulas, recalculating it in LibreOffice, and comparing every cell to the worked example and to an independent Python model (loan balance amortized month by month, IRR solved numerically), plus 40 randomized deals.
1. The inputs
Make a tab called Inputs. Labels go in column A, your numbers in column B. The example is a duplex: two units at $1,450 a month, plus $50 a month of laundry income.
| A | B | |
|---|---|---|
| 2 | Purchase price | 285000 |
| 3 | Closing costs | 7500 |
| 4 | Upfront repairs | 12000 |
| 5 | Down payment % | 25% |
| 6 | Interest rate (annual) | 6.75% |
| 7 | Loan term (years) | 30 |
| 8 | Loan points % | 1% |
| 9 | Monthly rent (all units) | 2900 |
| 10 | Other monthly income | 50 |
| 11 | Vacancy & credit loss % | 6% |
| 12 | Fixed costs, year 1 | 6340 |
| 13 | Repairs + CapEx reserve % of rent | 12% |
| 14 | Management % of collected income | 8% |
| 15 | Rent growth % / year | 3% |
| 16 | Expense growth % / year | 3% |
| 17 | Appreciation % / year | 3% |
| 18 | Selling costs % of sale price | 7% |
| 19 | Hold period (years, 1–10) | 7 |
A few notes:
- Fixed costs are the yearly bills that don't depend on rent: property tax, insurance, HOA, owner-paid utilities and anything else. Here that's $3,100 tax + $1,500 insurance + $145 a month of utilities and other costs × 12 = $6,340.
- Repairs + CapEx is a reserve, a share of scheduled rent you set aside every month for repairs and big replacements (roof, HVAC, water heater). It grows with rent.
- Selling costs are agent commission, transfer taxes and seller closing costs, as a share of the sale price. 6–8% is a common planning range; check what's normal where you are.
- Hold period is the year you sell. The projection runs 10 years, so use a whole number from 1 to 10.
Below the inputs, add three calculated cells:
B21: =B2*(1-B5)
B22: =B2*B5+B3+B4+B21*B8
B23: =IF(OR(B21=0,B7=0),0,IF(B6=0,B21/(B7*12),PMT(B6/12,B7*12,-B21)))
Label them Loan amount, Cash invested, Monthly P&I. Cash invested is everything you put in on day one: the down payment, closing costs, upfront repairs and the loan points. The IFs in B23 handle a cash purchase (no loan) and a 0% loan, where PMT isn't needed. In the example the loan is $213,750, cash invested is $92,888 and the payment is $1,386.38 a month.
2. The projection
Make a second tab called Projection. Put these headers in row 1: Year, Effective gross income, Operating expenses, NOI, Debt service, Cash flow, Loan balance, Value, Net sale proceeds, IRR cash flow (columns A to J). Type the years 0 to 10 in A2:A12.
Year 0 is the purchase. It only needs three cells:
Projection!G2: =Inputs!$B$21
Projection!H2: =Inputs!$B$2
Projection!J2: =-Inputs!$B$22
Row 3 is year 1. Enter these formulas, then fill B3:J3 down to row 12:
Projection!B3: =(Inputs!$B$9+Inputs!$B$10)*12*(1+Inputs!$B$15)^(A3-1)*(1-Inputs!$B$11)
Projection!C3: =Inputs!$B$12*(1+Inputs!$B$16)^(A3-1)+Inputs!$B$13*Inputs!$B$9*12*(1+Inputs!$B$15)^(A3-1)+Inputs!$B$14*B3
Projection!D3: =B3-C3
Projection!E3: =IF(A3<=Inputs!$B$7,12*Inputs!$B$23,0)
Projection!F3: =D3-E3
Projection!G3: =IF(OR(Inputs!$B$21=0,A3>=Inputs!$B$7),0,IF(Inputs!$B$6=0,Inputs!$B$21-12*A3*Inputs!$B$23,MAX(0,-FV(Inputs!$B$6/12,12*A3,-Inputs!$B$23,Inputs!$B$21))))
Projection!H3: =Inputs!$B$2*(1+Inputs!$B$17)^A3
Projection!I3: =H3*(1-Inputs!$B$18)-G3
Projection!J3: =IF(A3<Inputs!$B$19,F3,IF(A3=Inputs!$B$19,F3+I3,0))
What each column does:
- B, Effective gross income: a year of rent and other income, grown by the rent growth rate from year 2 on, minus the vacancy allowance.
- C, Operating expenses: fixed costs grown by expense growth, plus the repairs/CapEx reserve on scheduled rent, plus management on collected income. The mortgage is not an operating expense.
- D, NOI is income minus operating expenses. E, Debt service is twelve payments a year until the loan term ends. F, Cash flow is NOI minus debt service, before income tax.
- G, Loan balance at the end of the year.
FVreturns what's left of the loan after12*A3payments. It comes back negative because it's money you owe, hence the minus sign (Microsoft: FV function). For a 0% loan it's the loan minus the payments made, and once the term is over it's 0. - H, Value is the price grown by appreciation each year. I, Net sale proceeds is what you'd walk away with if you sold that year: value minus selling costs minus the loan payoff.
- J, IRR cash flow is the column IRR reads: the year's cash flow, plus the sale proceeds in the sale year, and 0 after you've sold. Year 0 is your cash in, as a negative number.
3. The results
Back on the Inputs tab:
B25: =IFERROR(IRR(Projection!J2:J12),IFERROR(IRR(Projection!J2:J12,-0.5),"n/a"))
B26: =IF(B22=0,"n/a",SUM(Projection!J3:J12)/B22)
B27: =SUM(Projection!J2:J12)
B28: =SUMIF(Projection!A3:A12,"<="&B19,Projection!F3:F12)
B29: =INDEX(Projection!H2:H12,B19+1)-B2
B30: =B21-INDEX(Projection!G2:G12,B19+1)
B31: =-INDEX(Projection!H2:H12,B19+1)*B18
B32: =-(B3+B4+B21*B8)
B33: =B28+B29+B30+B31+B32-B27
Label them IRR, Equity multiple, Total profit, Cash flow during hold, Appreciation, Loan paydown, Selling costs, Upfront costs, Check (should be 0). Format B25 as a percentage and B26 as a number with two decimals.
- IRR is the annual rate that makes the present value of every cash flow in column J add up to zero. Excel's
IRRneeds at least one negative and one positive value and assumes the cash flows are evenly spaced, which yearly rows are; it starts from a guess of 10% and returns#NUM!if it can't find an answer in 20 tries; Microsoft's advice is to try again with a different guess (Microsoft: IRR function; Google Sheets' IRR also takes an optional guess). That's what the secondIRRdoes: in our LibreOffice tests, losing deals with IRRs of −8% to −58% returned an error from the default guess but solved from −50%. If both fail, the cell shows "n/a". - Equity multiple is everything you get back (all the cash flows plus the sale) divided by the cash you put in. 2.0 means you doubled your money, but it says nothing about how long that took. That's what IRR adds.
- Total profit is everything you get back minus the cash you put in.
- The breakdown, B28–B32, splits total profit into five pieces that always add up to it: cash flow while you hold, appreciation (sale-year value minus price), loan paydown (the principal your tenants paid off), selling costs and the upfront costs you never get back (closing, repairs and points). The down payment isn't a cost: you get it back inside the sale proceeds. B33 should show 0. If it doesn't, a formula was mistyped.
Worked example
With the inputs above, the first seven years look like this (cash flow is before income tax):
| Year | Income | OpEx | NOI | Cash flow | Loan balance | Value | IRR cash flow |
|---|---|---|---|---|---|---|---|
| 0 | $213,750 | $285,000 | −$92,888 | ||||
| 1 | $33,276 | $13,178 | $20,098 | $3,461 | $211,472 | $293,550 | $3,461 |
| 2 | $34,274 | $13,573 | $20,701 | $4,064 | $209,035 | $302,357 | $4,064 |
| 3 | $35,303 | $13,981 | $21,322 | $4,685 | $206,429 | $311,427 | $4,685 |
| 4 | $36,362 | $14,400 | $21,962 | $5,325 | $203,641 | $320,770 | $5,325 |
| 5 | $37,452 | $14,832 | $22,620 | $5,984 | $200,659 | $330,393 | $5,984 |
| 6 | $38,576 | $15,277 | $23,299 | $6,662 | $197,470 | $340,305 | $6,662 |
| 7 | $39,733 | $15,735 | $23,998 | $7,361 | $194,058 | $350,514 | $139,281 |
In year 7 the IRR cash flow is that year's $7,361 plus $131,920 of net sale proceeds ($350,514 minus 7% selling costs minus the $194,058 payoff).
| Result | Value |
|---|---|
| IRR | 9.9% |
| Equity multiple | 1.82 |
| Total profit | $76,576 |
Where the profit comes from:
| Piece | Amount |
|---|---|
| Cash flow during hold | $37,544 |
| Appreciation | $65,514 |
| Loan paydown | $19,692 |
| Selling costs | −$24,536 |
| Upfront costs | −$21,638 |
| Total profit | $76,576 |
What the example shows:
- The first year undersells the deal. Year-1 cash flow is $3,461 on $92,888 of cash, a 3.7% cash-on-cash return. Over seven years, with rising rents, loan paydown and the sale, the IRR is 9.9%.
- Most of the profit is appreciation. Appreciation is 86% of the total profit. Set appreciation to 0% and the IRR falls to 2.6%, with a total profit of $15,648. Before trusting an IRR, run it at 0% appreciation too and decide whether you'd still buy.
- Short holds rarely work. Sell in year 3 instead and the IRR is 0.9%: the selling costs and the upfront costs land on just three years of cash flow and paydown. Hold for 10 years and the IRR is 11.4%, with an equity multiple of 2.56.
Common mistakes
- Leaving selling costs out. Selling at the projected value with no commission makes every deal look better. Here, 7% of the sale price is $24,536 of profit.
- Treating the down payment as profit or as a cost. It's neither. Count it as cash in on day 0 and let it come back through the sale proceeds, as the breakdown does.
- Comparing IRR with year-1 cash-on-cash. They answer different questions. Cash-on-cash is this year's income on your cash; IRR is the whole hold, including the sale.
- Uneven dates.
IRRassumes evenly spaced cash flows. If you have real dates (a refinance in month 17, a sale in March), useXIRRwith the actual dates. - Negative years. If a year's cash flow goes negative (a big repair, a vacancy), the cash flows can change sign more than once and IRR can have more than one answer. Check the result against the equity multiple and the profit breakdown. The equity multiple here nets those years against what you get back.
- Taxes. This projection is before income tax. It doesn't model depreciation (under the general system, residential rental buildings are depreciated over 27.5 years; see IRS Publication 527) or tax on the sale. Both can change the after-tax return a lot, so ask a tax professional.
Related tutorials
- Cap rate vs. cash-on-cash vs. DSCR: a worked rental example.
- Rental property cash flow spreadsheet: monthly NOI and cash flow per property.
- Short-term rental calculator spreadsheet: Airbnb cash flow and break-even occupancy.
Want it done for you?
The Rental Property Deal Analyzer is a finished workbook built on the same projection math: three deals side by side from 25 itemized inputs (units, taxes, insurance, HOA, utilities and separate repair and CapEx reserves), a 10-year projection with IRR and equity multiple for any hold period from 1 to 10 years, cap rate, cash-on-cash, DSCR, the 1% rule, break-even occupancy, and sensitivity tables for rent × price and interest rate × down payment. It comes with a SAMPLE file containing this duplex and two other deals, plus a PDF guide, and works in Excel, Google Sheets and LibreOffice with no macros.
Not tax, legal or investment advice. Projections depend on assumptions about rent, expenses and appreciation that may not hold.
Free tool: Free Rental Property Calculator: Cash Flow, Cap Rate, Cash-on-Cash, DSCR
Written with AI assistance. Every formula, command and number on this page was tested before publishing (see each section). Product links go to our store.