Rental Property Depreciation Calculator in Excel: The IRS Counts It Whether You Claim It or Not

Two landlords buy the same duplex in April 2021 for $410,000 and sell it in December 2026 for $525,000. Landlord A claims depreciation every year. Landlord B was told depreciation "just gets recaptured when you sell" and leaves it off Schedule E to avoid that. By the closing table, B has paid $8,478 more federal tax than A, and $13,564 more if B cannot prove what was claimed. The reason is one sentence in IRS Publication 946: if you do not claim depreciation you are entitled to deduct, you must still reduce the basis of the property by the full amount allowable. A rental property depreciation calculator in Excel that prints one number per year hides that rule. The version below tracks what was allowable, what was claimed and what was suspended in separate columns, because the sale year settles all three.

Every figure in this article comes from one model: the duplex above, a 24 percent marginal federal rate, 15 percent on long-term capital gain, and modified adjusted gross income of $160,000, which is high enough to switch off the passive loss allowance. State tax and the 3.8 percent net investment income tax are left out for both landlords. Swap in your own rates; the formulas carry them.

Skipping Depreciation Does Not Skip the Recapture

Three rules decide what B's shortcut costs, and all three are in the Internal Revenue Code.

1. Basis drops either way. Section 1016(a)(2) reduces basis by depreciation allowed, "but not less than the amount allowable." Publication 946 (2025) spells it out: depreciation allowed is what you actually deducted, depreciation allowable is what you were entitled to deduct, and you reduce basis by whichever is greater. This is the allowed or allowable depreciation rule, and it means a landlord who never claims a dollar still has a smaller basis, and therefore a bigger gain, at sale.
2. The 25 percent bucket follows what you claimed, if you can prove it. Section 1(h)(1)(E) taxes "unrecaptured section 1250 gain" at no more than 25 percent. That gain is measured through the depreciation adjustments of section 1250(b)(3), and the last sentence of that paragraph says that if the taxpayer can establish by adequate records that the amount allowed was less than the amount allowable, the amount taken into account is the amount allowed. B's unclaimed depreciation still sits inside the gain, but with proof it is taxed as ordinary long-term gain instead of at the depreciation recapture rate.
3. Suspended losses come back at sale. Section 469(i) lets an actively participating landlord deduct up to $25,000 of rental losses against a paycheck, reduced by 50 percent of modified adjusted gross income above $100,000, so it is zero at $150,000. Above that, a rental loss is suspended and carried forward, and section 469(g) releases it when you dispose of your entire interest in a fully taxable sale.

Put together: claiming depreciation buys a deduction at your ordinary rate now (or a suspended loss released at sale) and costs at most 25 percent later. Skipping it buys nothing now and still costs 15 percent later, or 24 percent here if the records are missing. On the duplex, B's best case ends $8,478 behind A.

The Land Split Moves the Number More Than Any Formula

Land is not depreciable, so the first job of the calculator is to carve it out, and the land to building ratio swings the annual deduction by more than a third. Publication 527 (2025) gives the order of evidence: allocate cost by the ratio of each part's fair market value to the whole at the time you buy, and if you aren't certain of those values, you can use the assessed values for real estate tax purposes.

Start with cost basis, which is more than the price. Publication 527 lists the closing costs that go into basis: abstract fees, charges for installing utility services, legal fees, recording fees, surveys, transfer taxes and title insurance. On the duplex:

Then split it three ways and look at the spread:

The model uses the appraiser's site value, because it is an opinion of market value dated at the purchase, which is the evidence Publication 527 puts first. Keep the appraisal PDF with the closing statement. The flat 20 percent has no source at all, and by the sale it claims $11,964 more than the appraisal supports; at 24 percent that is $2,871 of tax an examiner can reverse, with interest. The assessor ratio errs the other way: if the appraisal is the better evidence, it leaves $6,867 of allowable depreciation unclaimed, and the allowed or allowable rule still takes that amount off the basis. That is Landlord B's mistake on a smaller scale. If you think the assessment itself is wrong, the rental property tax appeal analysis tests it against sold comps.

In Excel, put the inputs on a Basis tab:

• B3 contract price, B4 closing costs that belong in basis, B5 =B3+B4 for total cost basis.
• B6 appraised value, B7 appraiser's site value, B8 total assessed value, B9 assessed land value.
• B10 land share: =IF(B7>0,B7/B6,B9/B8). It uses the appraisal when you have one and falls back to the assessor, which is the order Publication 527 gives.
• B11 building basis: =ROUND(B5*(1-B10),0), which returns $274,245.
• B12 placed-in-service date, B13 sale date (blank while you hold), B14 recovery period, 27.5.
• B16 modified adjusted gross income, B17 passive loss allowance: =MAX(0,25000-0.5*MAX(0,B16-100000)), which is $0 at $160,000.

Improvements made later, such as a roof or a kitchen, are separate assets with their own placed-in-service dates. Give each one its own copy of the schedule below; the repair vs improvement classification article covers which invoices qualify. Appliances, carpet and site work can carry shorter lives than 27.5 years, which is the subject of the cost segregation calculator.

The Schedule: One Row Per Tax Year, Mid-Month at Both Ends

Residential rental buildings use straight-line depreciation over 27.5 years with the mid-month convention: the property is treated as placed in service, and later sold, in the middle of the month. Publication 946 works the sale side in its own example: a $100,000 building sold on March 2, 2025 gets $3,636 for a full year, times 2.5 over 12, or $757.50.

The duplex was ready for rent on April 15, 2021, so 2021 counts 8.5 months. The sale closes December 15, 2026, so 2026 counts 11.5 months. On a Schedule tab with one tax year per row, starting at row 5:

• A5 the tax year.
• B5 months in service: =IF(AND(Basis!$B$13<>"",A5>YEAR(Basis!$B$13)),0,IF(A5=YEAR(Basis!$B$12),12.5-MONTH(Basis!$B$12),IF(AND(Basis!$B$13<>"",A5=YEAR(Basis!$B$13)),MONTH(Basis!$B$13)-0.5,12)))
• C5 allowable depreciation: =MIN(ROUND(Basis!$B$11/Basis!$B$14*B5/12,0),Basis!$B$11-SUM(C$4:C4)). The MIN stops the schedule once the building is fully recovered, which matters in year 28 and in any copy you build for a short-lived component.
• D5 depreciation actually claimed, typed from the Schedule E you filed. Do not link it to C: it records what the return says.
• E5 missed amount: =C5-D5, with =IF(E5<>0,"CHECK RETURN","OK") next to it.

Row 4 holds zeros as the opening balance so the running formulas have something to start from. The first rows of the schedule:

The check for 2021: $274,245 divided by 27.5 is $9,972.55 a year, times 8.5 over 12 is $7,063.89, rounded to $7,064. That matches the 2.576 percent first-year rate for a property placed in service in April in Table A-6 of Publication 946.

Allowable, Claimed and Suspended Are Three Different Columns

A one-column schedule assumes the deduction you computed is the deduction you used. With $160,000 of income, A's early losses could not touch the paycheck, and that is the moment a landlord is tempted to stop claiming. The losses are carried forward instead. Add three columns to the Schedule tab:

• F5 net rental income before depreciation: rent minus operating expenses minus mortgage interest, linked from your ledger. The rental property cash flow spreadsheet builds that ledger and ties it to Schedule E.
• G5 rental result on the return: =F5-D5.
• H5 suspended loss carried forward at year end: =MAX(0,H4-G5-Basis!$B$17). A loss year adds to the balance, a profit year uses it up first, and the allowance from B17 drains it when your income is below $150,000.
• I5 rental income that is actually taxed: =MAX(0,G5-H4). The allowance only offsets losses, so it does not enter this column.

Here is the duplex, year by year. Rent started in June 2021, when both units were leased, at $1,350 a unit, rising to $1,595 by 2025; the loan is $328,000 at 3.625 percent over 30 years.

A's first two years produce losses that do nothing on the 2021 and 2022 returns. They come back: in 2023, 2024 and 2025 the property turns a small profit after depreciation, and the parked $5,139 absorbs it, so A pays no tax on the duplex for five years and still carries $1,580 into the sale year. B reports every dollar of the $45,376 and pays $10,891.

The Sale Year Settles All Three Columns

In December 2026 both landlords sell for $525,000 and pay $34,125 in selling costs, for an amount realized of $490,875. Because basis drops by the allowable amount for both of them, the gain is identical: $415,400 minus $56,513 leaves an adjusted basis of $358,887, and the gain is $131,988. Put the sale on its own tab:

• B5 amount realized: =B3-B4.
• B6 total allowable: =SUM(Schedule!C5:C10). B7 total claimed: =SUM(Schedule!D5:D10).
• B8 adjusted basis: =Basis!B5-MAX(B6,B7). This one cell is the allowed or allowable rule.
• B9 gain: =B5-B8.
• B10 "Yes" if your filed returns prove what you claimed. B11 unrecaptured section 1250 gain: =MIN(B9,IF(B10="Yes",B7,B6)).
• B13 your marginal rate, B14 the long-term gain rate, and B15 the rate on B11: =MIN(B13,0.25).
• B16 tax on the gain: =B11*B15+(B9-B11)*B14.

Then add the rental result for the sale year, after any suspended loss released under section 469(g):

A's 2026 line is the year's $11,944 of income before depreciation, minus $9,557 of depreciation, minus the $1,580 still suspended. The recapture rate equals the ordinary rate here because 24 percent is below the 25 percent cap; at a 32 or 35 percent marginal rate, B15 returns 25 percent, and each claimed dollar saves 7 to 10 points more than it costs at sale.

B's best case still loses $8,478. B gave up $56,513 of deductions worth $13,563 at 24 percent, and the only thing B saved was the 9-point gap between 24 percent and 15 percent on the same $56,513, which is $5,086. The worst case, with no proof of what was claimed, taxes the missing depreciation exactly as if B had taken it, so B pays for the deduction twice. A 1031 exchange defers both buckets instead of paying either; the 1031 exchange calculator handles the timing.

Missed Years: Amended Return or Form 3115

The last column of that table is the repair for missed depreciation. Publication 946 gives two routes and picks between them on one test: whether you have "adopted" a method of accounting for the property.

B left depreciation off five consecutive returns, so B has adopted an impermissible method and the route is Form 3115. The catch-up is a section 481(a) adjustment equal to all the allowable depreciation never deducted before the year of change: $46,956 for 2021 through 2025. Publication 946 says a negative adjustment, one that lowers taxable income, is taken into account in the year of change. Filed with the 2026 return, it stacks with the year's own $9,557 of depreciation and turns B's 2026 rental result into a $44,569 loss. Because B disposes of the entire interest that year, section 469(g) frees the loss against other income, and B's total lands exactly where A's does: $25,078. The difference is that B fronted $10,891 to the Treasury for up to five years at no interest.

Two timing points decide whether this works. Change number 7 in the Form 3115 instructions applies to depreciable property owned at the beginning of the year of change, so for a property sold in 2026 the 2026 return is the last one where it fits: B owned the duplex on January 1, 2026, and owns nothing on January 1, 2027. And if you still own the property and your income is above $150,000, the catch-up is a passive deduction like any other: it lands in column H and waits for income or a sale. Publication 946 points to Revenue Procedure 2025-23 for the current list of automatic changes. Form 3115 runs several pages, so have your preparer file it, and hand them the Schedule tab, because column E is the 481(a) adjustment already computed year by year.

What to Do With Your Own Schedule This Week

Pull your closing statement, your appraisal, your last property tax bill and every Schedule E you have filed for the property. Then:

1. Rebuild cost basis from the closing statement using the items Publication 527 lists.
2. Replace any flat land percentage with the appraiser's site value, or the assessor ratio if no appraisal exists. Write the source next to B10.
3. Rebuild the allowable column from the placed-in-service date with the mid-month formula, then type what you actually claimed into column D from the filed returns.
4. If column E shows anything other than zero for two or more consecutive years, take it to your preparer this tax season, not the year after you sell.
5. If you are above $150,000 of income, check that column H matches the carryforward on your last Form 8582. That balance is money you get back at sale, so it has to survive every change of preparer.

On the duplex, those steps are the difference between $25,078 and $38,642 of federal tax on the same property, the same rent and the same sale price.

If you are still deciding whether to buy, the depreciation line belongs in the deal analysis too. The Rental Property Analyzer includes a tax benefit estimator with depreciation schedules and mortgage interest deduction, the mortgage amortization schedule that feeds the interest line in column F, and a 10-year projection that shows equity buildup alongside cash flow. It does not keep the allowable, claimed and suspended ledger for you. Add the Schedule and Sale tabs from this article to the workbook, point column F at the analyzer's income and expense lines, and the number you see at sale is the one your preparer will put on the return.