How to Use the Bonus Depreciation Tax Spreadsheet

Overview

This spreadsheet estimates short-term rental investment performance. It models purchase costs, financing, income, expenses, and tax benefits from bonus depreciation. It’s designed for real estate investors to assess potential ROI and after-tax cash flow.

1. Property Purchase Details

Purpose: Define property cost and purchase parameters.

• Property Address: Enter the property’s address or nickname.

• Price: Enter the total purchase price.

• Down Payment %: Input your intended down payment percentage (default 20%).

• Mortgage Interest Rate %: Enter current loan rate.

• Number of Monthly Payments: Keep at 360 for a 30-year mortgage.

• Closing Cost Est %: Estimate closing costs (3–5% typical).

• Outputs:

  • Monthly Mortgage Payment auto-calculates.

  • Estimated Money Invested adds down payment and closing costs.

2. Furnishing Expenses

Purpose: Estimate setup costs for rental readiness.

• Bedrooms: Input number of bedrooms.

• Costs to Fill Per Bedroom: Default $5,000 per bedroom.

• Common Areas: Input count (living room, dining, patio, etc.).

• Costs to Fill Per Common Area: Default $2,500.

• Output: Cost to Furnish auto-calculates.

3. Monthly Operating Expenses

Purpose: Project monthly recurring costs.

• Enter estimates for:

  • HOA

  • Taxes

  • Insurance

  • Utilities: water/sewer, electric, gas, wifi

  • Repairs, Pool, Lawn, etc.

• Monthly Operating Expenses totals automatically.

4. Rental Income

Purpose: Estimate revenue.

• Est. Monthly Revenue: Input projected monthly rental income.

• Est. Yearly Revenue multiplies automatically.

5. Management and Listing Costs

Purpose: Account for management or software fees.

• Airbnb Cut: Defaults to 3% (Airbnb service fee).

• Airbnb Software Monthly Costs: Optional, if using software.

• Management Company %: Input only if using a property manager (10–25% typical).

• Monthly Management Costs and Total Operating Costs adjust automatically.

6. Net Operating Income (NOI)

Purpose: Measures property profitability before debt.

• NOI (Monthly/Yearly):
  NOI = Rental Income – Operating & Management Costs

Used for evaluating investment quality separate from financing.

7. Cash Flow

Purpose: Measure true income after loan payment.

• Cash Flow (Monthly/Yearly):
  Cash Flow = NOI – Mortgage Payment

Represents actual money remaining each month after all costs.

8. Cap Rate

Purpose: Evaluate property efficiency independent of financing.

• Formula: Cap Rate = NOI / Property Price

  • 4–7% = typical range

  • 7–10%+ = strong performance

9. Estimated Mortgage Paydown

Purpose: Reflect annual principal reduction.

• Monthly and Yearly values show how much principal is paid down.

• Included in total ROI calculation.

10. Year-End Return on Investment (ROI)

Purpose: Show combined annual return including appreciation and paydown.

• Property Value: Enter future estimated value.

• Est. Yearly Appreciation %: Default 5%.

• Est. Improvement: Optional manual entry for upgrades.

• Total ROI includes:

  • Appreciation

  • Mortgage principal reduction

  • Improvements

11. Bonus Depreciation (Tax Benefit)

Purpose: Estimate tax savings from accelerated depreciation.

Year 1

• Land Value %: Non-depreciable (default 20%).

• Land Value (Non-Depreciable): Auto-calculates.

• Tax Rate: Input your marginal tax rate (35% default).

• Yr. 1 Bonus Depreciation: Applies to depreciable basis (80% of property).

• Yr. 1 Est. Tax Savings: Bonus Dep × Tax Rate

Year 2

• Residual depreciation continues on remaining depreciable balance.

12. Interpreting Results

Key indicators:

• NOI: Operating profitability.

• Cash Flow: Real take-home income.

• Cap Rate: Investment quality benchmark.

• ROI: Overall performance with appreciation and loan paydown.

• Tax Savings: Immediate reduction in taxable income from bonus depreciation.

13. Notes

• All yellow-highlighted cells require user input.

• All white or gray cells auto-calculate.

• Do not overwrite formulas.

• For accurate results, use conservative estimates for income and appreciation.

• Bonus depreciation assumes short-term rental qualifies as a business and meets IRS “material participation” requirements. Consult a CPA.

Summary Flow

Enter purchase price, financing, expenses, income.

Adjust management, utilities, appreciation, tax rate.

Review NOI, Cash Flow, Cap Rate, ROI, Bonus Depreciation Savings.

Compare against other properties for decision-making.