Rental Property Depreciation Spreadsheet: 27.5-Year Schedule with the Mid-Month Convention (Excel & Google Sheets)

Short on time? The Rental Property Tracker ($19) is the finished, tested version of what this tutorial builds.

Depreciation is often the biggest number on a landlord's Schedule E (line 18), and it's the one people most often get wrong or leave to the last minute. The rules for a residential rental building are short: the land isn't depreciable, the building is recovered over 27.5 years in equal amounts, and the first and last years are prorated by the month, counting the month you start (or stop) as half a month.

This tutorial turns those rules into one tab you can keep year after year: one row per building or improvement, with this year's depreciation, the total taken before this year and the basis left. It works the same in Excel, Google Sheets and LibreOffice, using only ROUND, IF, MIN, MAX, MONTH, YEAR and SUM. To test it, we built a workbook with these exact formulas, recalculated it in LibreOffice, and compared every result with the worked example below, with the two worked examples in IRS publications 527 and 946, with an independent Python model that steps through every year on 40 random properties, and with the roof entry and Schedule E line 18 of our Rental Property Tracker's sample file.

Not tax advice. The rules below are quoted from IRS Publication 527 (Residential Rental Property) and IRS Publication 946 (How To Depreciate Property). This sheet covers the general 27.5-year method only: no bonus depreciation, no cost segregation, no 5-year items. Have your preparer check the first year you set it up.

1. What goes in the sheet

Put the tax year in B1. Then one row per item, starting in row 5 (row 4 holds the headers):

Placed in service isn't the closing date. Publication 527: "You place property in service in a rental activity when it is ready and available for a specific use in that activity. Even if you aren't using the property, it is in service when it is ready and available for its specific use." A house you buy in March and fix up until May goes in as May.

Cost includes some closing costs and not others. Publication 527 lists ones that are part of your basis, among them "Abstract fees", "Legal fees", "Recording fees", "Surveys", "Transfer taxes" and "Title insurance". It says you can't include "Fire insurance premiums" or charges connected with getting or refinancing a loan, such as points.

Land comes out because "You can't depreciate the cost of land because land generally doesn't wear out, become obsolete, or get used up." If your purchase contract doesn't split the price, Publication 527 says "you can divide the cost between them based on their assessed values for real estate tax purposes". That's what columns D and E do.

Improvements like a new roof get their own row. Publication 527's Table 2-1 gives "Additions and improvements, such as a new roof" the same recovery period as the property they're added to, so a roof on a rental house is 27.5-year property, starting from the month it was finished. Appliances, carpeting and furniture are different: Publication 527 puts them in the 5-year class, which uses other rates, so keep them out of this sheet.

2. The formulas (row 5; fill down to row 14)

F5 (Depreciable basis):     =IF(C5="","",ROUND(C5*(1-IF(E5>0,D5/E5,0)),2))
G5 (Year number):           =IF(B5="","",$B$1-YEAR(B5)+1)
H5 (First-year rate):       =IF(B5="","",ROUND((12.5-MONTH(B5))/12/27.5,5))
I5 (Taken before this year): =IF(B5="","",IF(G5<=1,0,MIN(F5,ROUND(F5*H5,2)+(G5-2)*ROUND(F5*0.03636,2))))
J5 (This year):             =IF(B5="","",IF(G5<1,0,IF(G5=1,ROUND(F5*H5,2),MAX(0,MIN(ROUND(F5*0.03636,2),F5-I5)))))
K5 (Left to depreciate):    =IF(B5="","",F5-I5-J5)
J15 (Total for Schedule E line 18): =SUM(J5:J14)

Format H as a percentage with three decimals. Depreciable basis is the cost times the building's share of the assessment. Year number is 1 in the year the item was placed in service, 2 the next year, and so on. It's zero or negative for something placed in service after the tax year in B1, and This year shows 0 for it.

First-year rate is the mid-month convention: an item placed in service in month m gets 12 − m + ½ months in its first year, out of 12 months × 27.5 years. Rounded to three decimals of a percent, that gives:

Every full year after that is 1 ÷ 27.5 = 3.636% of the basis. Publication 946's example uses the same figure: "A full year of depreciation for 2025 is $3,636. This is $100,000 multiplied by 0.03636". Taken before this year adds the first year and the full years in between, and MIN stops it at the basis. This year is the first-year amount in year 1, a full year after that, and only what's left in the final year, so the total never goes over the basis.

Worked example

A duplex bought for $310,000 and ready to rent in June 2019, plus $4,200 of closing costs that go into basis, so C5 is 314200. The county assessment shows land $52,000 of a $260,000 total. In May 2026 the landlord put on a new roof for $9,800 (row 6, D and E blank). B1 is 2026.

• Land share: 52,000 ÷ 260,000 = 20%, so the duplex's depreciable basis is 314,200 × 80% = $251,360.00.
• First year (2019, June): 251,360 × 1.970% = $4,951.79.
• Each full year: 251,360 × 3.636% = $9,139.45.
• 2026 is year 8. Taken before 2026: $4,951.79 + 6 full years = $59,788.49. This year: $9,139.45. Left after 2026: $182,432.06.
• The roof, placed in service in May 2026, is in year 1: 9,800 × 2.273% = $222.75, leaving $9,577.25 for later years.
• Total for line 18 in 2026: $9,362.20.

Skipping the land split would claim $11,424.31 a year on the duplex instead of $9,139.45, which is $2,284.86 a year too much. That's the most common mistake in DIY depreciation, and it compounds every year.

Publication 527's own example checks the first-year rate. A building with a basis of $160,000 is placed in service on February 8. The publication finds 3.182% in its table and gets "$5,091 ($160,000 x 3.182% (0.03182))". The sheet gives $5,091.20, the same amount before rounding to whole dollars.

3. The year you sell

The mid-month convention applies at the end too: Publication 946 says to "Treat the month of disposition as one-half month of use." If you sell the building in a later year, its depreciation for that year is a full year's amount times (month of sale − ½) ÷ 12:

B18 (Sold on):                 2025-03-02
B19 (Depreciation in sale year): =ROUND(ROUND(F5*0.03636,2)*(MONTH(B18)-0.5)/12,2)

Publication 946's example is a $100,000 basis sold on March 2: "Multiply $3,636 by the fraction, 2.5 over 12, to get your 2025 depreciation deduction of $757.50." With 100000 in F5 the formula gives $757.50. Use this in place of This year for the year of the sale only, and talk to your preparer about the sale itself: depreciation you took (or could have taken) reduces your basis and affects the gain.

Before you rely on it

• The IRS tables are the official version of these rates. Publication 527's Table 2-2d lists the percentage for each month and year, and Publication 946 notes that figuring MACRS without the tables "will generally result in a slightly different amount". The sheet's first-year rates and the 3.636% full-year rate are the figures used in the two IRS examples above. If you use the table, keep using the same month's row each year, and read later years from it, because a table's rounded rates may not be exactly 3.636% in every year.
• The month matters, not the day. A building placed in service on June 1 or June 30 gets the same 1.970%.
• Depreciation reduces your basis whether or not you claim it. The IRS talks about depreciation "allowed or allowable", so skipping it in a low-income year doesn't save it for later. If you've missed years, ask your preparer about fixing it.
• Personal use, a home converted to a rental, and short-term rentals have extra rules in Publication 527 that this sheet doesn't cover.

Common mistakes

• Depreciating the land. Use the assessment split (or the contract or an appraisal) every time.
• Using the closing date instead of the placed-in-service date. It changes the first-year rate and can move the start into the next year.
• Putting appliances or carpet in the 27.5-year table. They're 5-year property with their own rates.
• Expensing a roof. A new roof is an improvement with its own 27.5-year schedule, not a repair on line 14.
• Starting a new row for the same building each year. One row per item, and change only B1 each January.

Going further

The number in J15 goes on Schedule E line 18. Our Rental Property Tracker (Excel and Google Sheets) sorts every rental transaction into its Schedule E line for up to 10 properties. Improvements like the sample file's $9,800 roof are typed as Capital, so they stay out of the expense totals, and a Depreciation entry lands on line 18. When we added this tutorial's 2026 total for the duplex as a Depreciation entry in the sample file, its Schedule E tab showed the same $9,362.20 on line 18. For the full year's records, see How to track rental income and expenses by Schedule E line.

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.