Real Estate Investment Dashboard Excel: Track Performance Across Your Portfolio

Introduction

Managing multiple investment properties without centralised tracking leads to missed rental increases, overlooked maintenance issues, inaccurate cash flow forecasting, and poor investment decisions. Australian property investors face additional complexity: negative gearing rules, capital gains tax (CGT) on disposal, state-based land tax thresholds, and depreciation schedules under ATO guidelines.

This guide shows how to build a comprehensive real estate investment dashboard in Excel that handles both the standard portfolio metrics and the Australian-specific tax considerations.

Why Property Investors Need Dashboards

Investors who track their portfolio manually across spreadsheets, bank statements, and agent reports typically miss three to five percentage points of annual return simply because they don't have the data visibility to act.

Dashboard Components

1. Property Summary Section

• Basic property details (address, type, purchase date)
• Purchase price and date
• Current estimated value (updated quarterly from comparable sales)
• Loan details: balance, rate, type (P&I or IO), lender
• Rental income and expenses
• Land tax status per state

2. Financial Performance Metrics

• Cash-on-cash return: =AnnualCashFlow / TotalCashInvested
• Capital growth: =(CurrentValue - PurchasePrice) / PurchasePrice
• Net operating income: Gross rent minus operating expenses (before debt service)
• Debt service coverage ratio: NOI / annual loan repayments
• Gross rental yield: =(WeeklyRent * 52) / CurrentValue
• After-tax cash flow: Cash flow adjusted for negative gearing benefit at your marginal tax rate
• Total ROI: =(TotalEquity - TotalInvested) / TotalInvested

For Australian investors, the after-tax cash flow metric is the real one that matters. A negatively geared property might show negative pre-tax cash flow of -$5,000/year, but at the 37% marginal tax rate (including the Medicare levy), that loss reduces your tax bill by approximately $1,850 - meaning your actual cash outflow is only -$3,150.

3. Australian Tax Tracking

Australian property has specific tax treatments that a good dashboard should model:

• Depreciation: Building write-off at 2.5% or 4% depending on construction date (Division 40 vs Division 43)
• Capital improvements vs repairs: Repairs are immediately deductible; improvements are capital and depreciated
• Borrowing costs: Deductible over five years or the loan term, whichever is shorter
• Land tax: Each state has different thresholds (e.g., NSW general threshold is $1,075,000 for 2025-26); your dashboard should flag properties approaching the threshold
• CGT discount: 50% discount for assets held longer than 12 months (for individuals)

4. Operational Tracking

• Vacancy rates (track days vacant between tenancies)
• Maintenance history with cost classification (repair vs improvement)
• Lease expiration dates with 90/60/30-day alerts
• Compliance requirements (smoke alarm certs, pool fencing, gas/electrical, tenancy bond lodgement)
• Insurance renewal dates

Building Your Real Estate Dashboard

Step 1: Property Data Table

Add columns for Australian-specific data: depreciation claimable/year, land tax payable, and estimated CGT on disposal.

Step 2: Key Metrics Formulas

• Cash-on-cash return: =AnnualCashFlow / (PurchasePrice - LoanAmount)
• Capital growth: =(CurrentValue - PurchasePrice) / PurchasePrice
• Gross rental yield: =(WeeklyRent * 52) / CurrentValue
• After-tax cash flow: =PreTaxCashFlow + (PreTaxCashFlow * -MarginalTaxRate) - works because a negative pre-tax position generates a positive tax saving
• Effective interest rate: =PMT(Rate/12, LoanTerm*12, -LoanAmount)*12/LoanAmount

Step 3: Depreciation Schedule

Australian tax law allows two depreciation methods:

Division 43 - Capital works (building structure)

• 2.5% p.a. for residential buildings constructed after 15 September 1987
• 4% p.a. for manufacturing facilities and某些 industrial buildings

Division 40 - Plant and equipment assets

• Carpet, blinds, hot water systems, kitchen appliances, air conditioning
• Can be claimed using diminishing value (generally 2x the prime cost rate) or prime cost
• Important: Since the 2017 budget changes, second-hand residential properties purchased after 9 May 2017 can only claim Division 43 deductions, not Division 40 for existing assets. New-build properties still qualify for both.

Step 4: Loan Amortisation Schedule

Excel's PMT, PPMT, and IPMT functions handle the loan calculations:

= PMT(InterestRate/12, LoanTerm*12, -LoanAmount)
= PPMT(InterestRate/12, Period, LoanTerm*12, -LoanAmount)
= IPMT(InterestRate/12, Period, LoanTerm*12, -LoanAmount)

Add a what-if analysis table to model rate changes. With RBA cash rate movements between 4.1% and 4.35% through 2025-26, stress-test your portfolio at +2% on top of your current variable rate. If any property's after-tax cash flow becomes more than -$10,000 negative, that's a refinancing flag.

Step 5: Visual Dashboard

Create summary cards showing:

• Total portfolio value
• Total equity
• Weighted average yield (gross and after-tax)
• Annual cash flow (pre-tax and after-tax)
• Loan-to-value ratio across all properties (gearing ratio)
• Effective interest rate across the portfolio
• Weighted average land tax exposure

Worked Example: Building a $2M Portfolio - Australian Context

Consider an investor in Sydney with one property aiming to grow to a $2M portfolio. The dashboard reveals:

• Property 1 (purchased 2020 for $850,000) has grown to $1,050,000 - $200,000 in equity growth
• Cash flow is negative at -$4,200/year (negatively geared), primarily due to the 6.2% variable rate
• At a 37% marginal tax rate, the after-tax cash outflow is -$2,646/year after the negative gearing benefit
• Market rents are 8% below comparable properties - the tenant has been in place for three years without an increase
• Annual depreciation claim (Division 43 only): $21,250 (2.5% of $850,000 building value, assuming $850K construction cost - a Quantity Surveyor's report would confirm the split between building and land value)
• Land tax in NSW: nil, as the property value ($1,050,000) is below the $1,075,000 threshold

The dashboard's scenario analysis tool shows:

1. Increase rent to market: +8% = +$1,664/year net
2. Refinance from 6.2% to 5.8%: saves $1,440/year in interest
3. Combined effect: reduces negative cash flow from -$4,200 to -$1,096/year pre-tax, and near breakeven after tax

The equity of ~$690,000 ($1,050,000 minus $360,000 loan) can fund a deposit on a second property - but the investor needs to decide: refinance and draw equity, or save for another deposit? The dashboard's what-if model shows that refinancing 80% LVR on Property 1 releases $480,000, enough for a $600,000 deposit on a $1.2M property (20% + stamp duty) in Western Sydney with higher rental yields.

The projected outcome: a $2.25M portfolio within 12 months, with weighted net rental yield improving from 2.1% to 3.4%.

Best Practices

• Update monthly: Cash flow review, rent update, expense tracking, rate change monitoring
• Quarterly: Portfolio performance review, revaluation estimates (use comparable sales from CoreLogic or your agent)
• Annually: Strategic review, refinancing opportunities, depreciation schedule update, BAS and tax return reconciliation
• Keep historical data: Track actual vs projected performance to refine your models. A rolling 36-month view helps identify seasonal patterns and cost inflation trends
• Review state policies: Each state budget can change land tax thresholds, stamp duty concessions, or tenancy laws - update your dashboard annually

Frequently Asked Questions

What is the most important metric for property investors?

Cash-on-cash return and total ROI are the most commonly used metrics for comparing properties. For Australian investors, net rental yield after tax (accounting for negative gearing benefits) is equally critical.

How often should I update my property dashboard?

Update cash flow monthly and portfolio valuation quarterly at minimum. For Australian investors, also update after each ATO tax return to refine your depreciation and interest deduction assumptions.

Can this dashboard handle different property types?

Yes, set up separate sections or sheets for residential, commercial, and development properties. Each type has different expense structures and tax treatments.

How do I handle variable interest rates in my dashboard?

Add a scenario analysis section showing the impact of 1%, 2%, and 3% rate increases on your cash flow. Given the RBA cash rate volatility since 2022, stress-testing at +2% is now standard practice for Australian portfolios.

What's the best way to track tax depreciation?

Add a depreciation schedule for each property, separating building (2.5% p.a. under Australian tax law for post-1987 properties) from plant and equipment assets. Consider whether a quantity surveyor's depreciation schedule would add value, especially for properties built before 2017 that still qualify for Division 40 deductions on second-hand assets.

How do Australian investors handle capital gains tax in a portfolio dashboard?

Track cost base, capital improvements, and holding period for each property to estimate CGT liability on disposal. The 50% CGT discount for assets held >12 months substantially reduces the effective tax rate.

What LVR should I target for a sustainable portfolio?

Most Australian lenders require an LVR below 80% to avoid Lenders Mortgage Insurance (LMI). A portfolio-level dashboard helps you track which properties have built enough equity to refinance and release deposits for the next acquisition.

Conclusion

A well-structured real estate dashboard in Excel is essential for serious property investors. By centralising your data, automating key calculations, and incorporating Australian-specific tax treatments - depreciation, CGT, land tax, and negative gearing - you make faster, better-informed investment decisions and keep more of your returns.

Start with the basic property table and metrics above, then layer in the Australian tax components as your portfolio grows. The difference between a good investor and a great one is often just better data.