A cost segregation spreadsheet is where most owners start when they want to see what a study might do for a specific property before paying for one. You enter the purchase price, carve out the land, split the building into shorter-life asset classes, and apply the IRS depreciation rates to see how much of the deduction moves into year one. The math is easy to set up; the inputs are where these models usually go wrong.
As co-founder of Seneca Cost Segregation and a real estate investor myself, with multifamily property in the Pacific Northwest, I look at these models from both sides of the table. Every preliminary estimate we provide at Seneca is built by our engineering team from the details of the specific property, and every study we deliver ends in a full component breakdown that a CPA works from line by line.
So I want to walk through the spreadsheet the way we would structure one: the five tabs, the formulas, and the exact IRS rates to load. Then I run a worked example on the allocation from one of our published case studies and show where the IRS’s own audit guide says a spreadsheet stops counting as support.
TL;DR — What a Cost Segregation Spreadsheet Can and Cannot Do
Six things to know before you open a blank workbook.
- ●Five tabs cover it: property inputs, land and basis, asset allocation, a MACRS rate lookup, and a year-one summary give you a working estimate in an afternoon.
- ●Take the land out first: land never depreciates, and the IRS lets you split cost by the assessed values on your property tax bill when you have no appraisal.
- ●Use the published IRS rates: 5-year property takes 20% in year one under the half-year table, and a building placed in service in July takes 1.177% of a 39-year basis.
- ●Match bonus to the date: property acquired and placed in service after January 19, 2025 gets 100% bonus depreciation, while 2024 placements get 60%.
- ●See the size of the swing: on the allocation from our published self-storage study, $872,550 of basis goes from $10,270 of first-year depreciation to $283,267 at 100% bonus.
- ●Treat the output as an estimate: the IRS audit guide expects classified, reconciled, documented costs, so the spreadsheet tells you whether to order a study and the study is what goes on the return.
What a Cost Segregation Spreadsheet Is
A cost segregation spreadsheet splits one building cost into asset classes and shows the depreciation each class produces, year by year.
Without cost segregation, a residential rental is depreciated over 27.5 years and a commercial building over 39 years, as IRS Publication 946 sets out. The spreadsheet’s job is to show how much of that cost belongs in the 5, 7, and 15-year classes instead.
The Estimate Model You Build Yourself
The estimate model is a planning tool you build before any study exists.
The model answers one question: is the first-year deduction big enough, at your tax rate, to justify paying for an engineered study? The allocation percentages in it are assumptions, so the output is a range to test against a professional estimate.
Owners also use the model to compare scenarios, such as a December closing against a January one, or a five-year hold against a fifteen-year hold.
The Fixed Asset Schedule a Study Delivers
The second form is the schedule an engineered study produces, listing every component with its cost, class, and placed-in-service date.
Your CPA loads that schedule into tax software and claims the deductions from it. If you are weighing doing your own study instead of hiring one out, the gap between these two forms is the heart of that decision.
Build the Spreadsheet in Five Tabs
Each tab feeds the next, so build them in this order and keep every assumption on the first tab.
Excel and Google Sheets both handle the whole model with plain formulas and one lookup function.
1. Property Inputs
The inputs tab holds every number you might change, so the rest of the workbook never contains a typed value.
- ●Purchase price plus capitalized closing costs, and any improvements added after purchase
- ●Placed-in-service date, which drives both the month in the rate table and the bonus rate
- ●Property type, residential rental at 27.5 years or nonresidential at 39 years
- ●Your marginal federal tax rate, plus a discount rate if you want present-value columns
Add a bonus rate cell that reads the dates. Property acquired and placed in service after January 19, 2025 qualifies for 100% under the One Big Beautiful Bill, with “acquired” meaning the date of the binding written contract.
Property placed in service in 2024 takes 60%, and property acquired before January 20, 2025 but placed in service during 2025 takes 40%. The contract-date details behind those cutoffs are covered in our breakdown of the 2025 bonus depreciation changes.
2. Land and Depreciable Basis
Land is never depreciable, so the second tab removes it before anything else happens.
If you have an appraisal with a land value, use it. If you do not, IRS Publication 527 allows you to divide the cost between land and building using their assessed values for real estate tax purposes.
In the IRS example, a $200,000 purchase with a $160,000 assessment, $136,000 of it on the house, puts 85% of the price in the building. The formula is total cost multiplied by the building’s share of assessed value, and our guide to determining land value covers the other methods for when an assessment looks out of date.
3. Asset Allocation
The allocation tab is where cost moves out of the 27.5 or 39-year class, and it decides whether your estimate means anything.
Give each row four columns:
- ●Component description
- ●Cost assigned to the component
- ●Recovery class (5, 7, or 15-year, or building)
- ●Source of the cost, such as an invoice, a contractor estimate, or an appraisal line
Group rows the way the tax law groups them. Appliances, carpets, and furniture in a rental are 5-year property under Publication 527, while improvements made directly to land, such as fences, roads, sidewalks, and shrubbery, are 15-year property; our overview of the main asset categories goes further.
End the tab with a check cell where the sum of every row minus the depreciable basis from tab 2 equals zero. The IRS Cost Segregation Audit Techniques Guide describes a residual approach that prices only the short-life items and assigns the remainder to the building, and notes that it generally does not reconcile project costs. Price the building rows too, then let the check cell confirm the total.
4. MACRS Rate Lookup
The rate tab stores the IRS percentage tables so the summary can pull the right rate for each class and year.
Personal property and land improvements use the half-year convention, which treats them as placed in service at mid-year whatever the actual date. Residential rental and nonresidential buildings use the mid-month convention, so the month of placement sets the first-year rate.
Lay the grid out with years down the side and classes across the top, then pull each rate with INDEX and MATCH or XLOOKUP. The gap between straight-line and accelerated depreciation is visible right in that grid, with the 5-year column front-loaded and the building columns flat at roughly 2.6% to 3.6% a year.
5. Year-One Savings Summary
The summary tab applies bonus and MACRS rates to each class and compares the result with a no-study baseline.
Each short-life class gets bonus depreciation on its basis plus the first-year MACRS rate on whatever bonus did not cover. Buildings get only their mid-month rate, since bonus depreciation is limited to property with a recovery period of 20 years or less.
The baseline is the whole depreciable basis at the building rate, and the difference multiplied by your tax rate is the estimated first-year tax effect. For a longer view, extend the columns across the full recovery period and add a present-value row, since total depreciation over the life of the property stays the same and only the timing changes.
Load These IRS Depreciation Rates Into the Rate Tab
These percentages come from Appendix A of the 2025 edition of Publication 946.
| Recovery year | 5-year (half-year) | 15-year (half-year) | 27.5-year residential (mid-month) | 39-year nonresidential (mid-month) |
|---|---|---|---|---|
| Year 1 | 20.00% | 5.00% | 3.485% (January) to 0.152% (December) | 2.461% (January) to 0.107% (December) |
| Year 2 | 32.00% | 9.50% | 3.636% | 2.564% |
| Year 3 | 19.20% | 8.55% | 3.636% | 2.564% |
| Year 4 | 11.52% | 7.70% | 3.636% | 2.564% |
| Year 5 | 11.52% | 6.93% | 3.636% | 2.564% |
| Year 6 | 5.76% | 6.23% | 3.636% | 2.564% |
| Years 7 to 16 | Fully recovered | 5.90% to 5.91%, then 2.95% in year 16 | 3.636% to 3.637% | 2.564% |
For the building columns, the month matters in year one: a July placement takes 1.177% on a 39-year building and 1.667% on a 27.5-year rental. Add a 7-year column, starting at 14.29% in year one, if your property carries equipment in that class.
Run a Worked Example Through the Spreadsheet
The example below runs the allocation from Seneca’s published self-storage study through all five tabs.
That study found 18.36% of a $872,550 depreciable basis in 5-year property and 13.30% in 15-year property, with the rest in the 39-year building. I have re-run that allocation under current rules, assuming a July placement and the 37% tax rate the case study used, so the figures below are a modeling exercise rather than the study’s reported results.
Here is what the summary tab produces next to the no-study baseline, at the 60% rate for a 2024 placement and the 100% rate for property acquired and placed in service after January 19, 2025.
| Line | No study | 60% bonus | 100% bonus |
|---|---|---|---|
| 5-year basis | $0 | $160,200 | $160,200 |
| 15-year basis | $0 | $116,049 | $116,049 |
| 39-year basis | $872,550 | $596,301 | $596,301 |
| Bonus depreciation | $0 | $165,749 | $276,249 |
| Year-one MACRS on remaining basis | $10,270 | $22,155 | $7,018 |
| Total year-one depreciation | $10,270 | $187,904 | $283,267 |
| Estimated year-one tax effect at 37% | $3,800 | $69,524 | $104,809 |
Figures are illustrative estimates. Your own result will turn on your cost basis, the asset mix an engineer actually finds, and your effective tax rate. Confirm all projections with your CPA before making financial decisions.
The 100% column moves roughly $273,000 of extra depreciation into year one compared with the baseline. Whether you can use all of it this year depends on the rest of your tax picture, which is a conversation for your CPA before the result drives a purchase or a filing decision.
Add a Look-Back Tab for Property You Already Own
If the property went into service in an earlier year, a sixth tab compares the depreciation you took with what the study allocation would have allowed.
Build two schedules side by side, one straight-line from the original placed-in-service date and one using the study’s classes, each summed through the last year you filed. The difference is the catch-up, claimed as a Section 481(a) adjustment on Form 3115 in the current year with no amended returns.
The IRS allows look-back studies on property placed in service as far back as 1987. Confirm with your CPA which bonus rate, if any, applies to each prior year, because the answer turns on the placed-in-service date and any elections made on the original return.
Avoid These Cost Segregation Spreadsheet Mistakes
Most spreadsheet errors start in the inputs, and each one below has a clean fix.
- ●Plugging a rule-of-thumb percentage: a flat industry-average allocation makes the output look precise when it is a guess, and the IRS audit guide tells examiners to view rule-of-thumb studies with caution because they lack documentation. Treat any percentage you did not derive from the property as a scenario input and test a low and a high case.
- ●Forcing the total with a step-up factor: scaling every component by the same ratio until the sum matches the purchase price hides a pricing gap, and the audit guide says a pro-rata step-up used merely to reconcile is not an appropriate estimation method. Find the missing or underpriced rows instead.
- ●Leaving land in the basis: depreciating land inflates every deduction in the model. Remove it on tab 2 before any allocation.
- ●Using the wrong bonus rate for the date: a property under binding contract before January 20, 2025 does not get 100% bonus, even if it closes later in 2025. Tie the rate cell to both the contract date and the placed-in-service date.
- ●Skipping the mid-quarter test: a fourth-quarter placement made up mostly of short-life property can move you onto the mid-quarter tables. Add a check cell that flags when fourth-quarter non-building basis passes 40% of the year’s total.
- ●Ignoring the eventual sale: gain on reclassified personal property is taxed as ordinary income to the extent of the depreciation taken, under Section 1245. If a sale within a few years is possible, add a sale-year row so the model shows the net benefit.
- ●Filing from the estimate: a model built on assumed percentages has no component-level support behind it. Use it to decide whether to order a study, then file from the study.
Know Where the Spreadsheet Stops and a Study Starts
The math in a cost segregation spreadsheet is the easy part; the support behind each row is what the IRS reviews.
The IRS has not set required preparation standards for cost segregation studies, yet its audit techniques guide says taxpayers still must substantiate their depreciation and classifications. The guide expects every study to classify assets into property classes, explain the legal rationale for each classification, and substantiate and reconcile the cost of every asset.
Beyond those three points, the guide lists 13 principal elements of a quality study, including a detailed description of the methodology, unit costs and engineering take-offs, a standard numbering system, and an explanation of how indirect costs were treated. A homemade spreadsheet can mirror the layout of that work, but the documentation behind each line has to come from the building.
We see this framing consistently from owners and CPAs: if the spreadsheet math is right, the deduction is safe. The reality of how an examination works is that the review starts from the evidence behind each row, which an estimate model does not have.
At Seneca, our engineers price each component from the property itself, through an onsite or virtual tour and the documents you provide. Every study then goes through the same internal review, and our Head of Engineering signs off before it reaches your CPA.
Frequently Asked Questions
Below are the questions we hear most from owners and CPAs who are building or reviewing a cost segregation spreadsheet.
Is There a Free Cost Segregation Spreadsheet Template?+
Free and paid templates circulate online, and most follow the tab structure described above. You can also build your own in an afternoon with the IRS rates in this guide, which has the advantage that you know where every number came from.
Can I File My Tax Return From a Cost Segregation Spreadsheet?+
You should not file from an estimate built on assumed percentages. The IRS audit guide expects each asset to be classified with a legal rationale and its cost substantiated and reconciled to the actual total, and a study’s component schedule is what your CPA files from.
What Reclassification Percentages Should My Spreadsheet Use?+
Use percentages from a study of your own property whenever you can, and treat anything else as a scenario. Allocations vary by property: Seneca’s published case studies range from about 24% of basis in 5 and 15-year property on a newly built townhome community to about 32% on a self-storage facility and 33% on a remodeled single-family rental.
Run a low and a high case, then ask for a property-specific estimate before deciding.
How Is a Spreadsheet Different From a Cost Segregation Calculator?+
A calculator runs preset assumptions behind a short form and returns a ballpark in seconds. A spreadsheet exposes every assumption, so you can change the allocation, the month, the bonus rate, and the hold period yourself, which makes the two work well together.
What Does My CPA Need From the Study Schedule?+
Your CPA needs each asset’s description, cost, recovery class, method, convention, and placed-in-service date, plus the Section 481(a) figure for a look-back. Our guide to CPA integration of study results walks through how that schedule moves onto the return.
Can a Spreadsheet Show What Happens When I Sell?+
Yes, and it should whenever a sale is possible. Add a sale-year row that treats the gain on reclassified personal property as ordinary income up to the depreciation taken, then model the building separately; our comparison of Section 1245 vs 1250 property explains how each is taxed on a sale.
How Seneca Replaces the Estimate With an Engineered Schedule
Seneca’s in-house engineering team replaces assumed percentages with a component-by-component schedule built from your property, toured onsite or virtually as you prefer, and our Head of Engineering signs off on every study before it reaches your CPA. You work with a dedicated Project Manager, Engineering team, and Advisor, studies are typically delivered in 5 to 10 business days once we have what we need from you, and AuditDefense support comes with every study. We have assessed 10,200+ properties with zero IRS audits triggered or lost, and you can test your own numbers first with our free cost segregation calculator.
Conclusion
A well-built spreadsheet earns its place before the study, when you are deciding whether accelerated depreciation is worth pursuing on a specific property. Five tabs, the IRS rate tables, and an honest allocation range will tell you whether the numbers deserve a closer look.
Once the answer is yes, the spreadsheet has done its job, and the allocation needs to come from the building itself. If you want a property-specific number to drop into your model, ask us for a free preliminary estimate; you pay only if you decide to move forward with the study.
