XIRR for a Property Portfolio: Excel Steps, Real Example

XIRR for a Property Portfolio: Excel Steps, Real Example

Use XIRR to annualise returns for property portfolios with irregular rents, expenses and sale proceeds: enter dated cash flows and run =XIRR(values, dates). The rest of this guide walks through the exact spreadsheet setup, a worked property example, tax record-keeping notes and the fixes for the errors that usually trip this formula up.

TL;DR:

  • Enter every dated inflow and outflow with negative expenses and positive receipts; add today’s market value as a positive cash flow for unsold properties.
  • Classify stamp duty and improvements as capital costs; track rates, insurance, management fees, and repairs separately, and retain records for five years after sale.
  • Use CAGR only for a lump sum without interim flows; cap rate and cash on cash provide annual snapshots, while XIRR measures full lifecycle performance.
  • For #NUM!, confirm both positive and negative cash flows, then try a different starting guess; for #VALUE!, check that dates and amounts are numeric.
  • Recalculate after new transactions or valuation updates, and compare periods spanning three, five, and ten years to see whether returns depend on one strong year.
WealthstackerTrack Property Returns With More ContextWealthStacker combines automated quarterly property valuations with tailored modeling to help you assess investment strategies and potential net worth.Explore WealthStacker

Table of Contents

What XIRR is and why it suits property portfolios

XIRR stands for the extended internal rate of return. It is a close relative of XNPV, the function that discounts a series of dated cash flows back to a present value at a given rate: XIRR simply solves for the rate that makes that present value equal to zero. Both functions share the same requirement that every cash flow carries its own date rather than assuming evenly spaced periods.

That date-by-date structure is exactly what property investing needs. A rental property rarely produces tidy, evenly timed cash flows: you might settle in March, pay council rates in July, receive an insurance payout in October and sell eighteen months later on an odd date entirely. XIRR calculates an internal rate of return for cash flows that occur at irregular intervals, weighting each one by the exact number of days it sat in the portfolio, so the result reflects the real timing of your money rather than a simplified annual average.

Before building a model, check you have the right inputs on hand:

  • A transaction date for every cash flow, from the purchase settlement to the final sale.
  • Signed amounts: negative for money leaving your account (purchase price, rates, repairs), positive for money coming in (rent, sale proceeds).
  • At least one negative and one positive value in the full list, since XIRR cannot solve a series that only flows one way.
  • A current valuation entered as a dated positive figure if you still own the property and want an interim return.

Get these four elements right and the formula does the rest. Where this gets fiddly is in deciding exactly which costs belong in the schedule, which is where XIRR starts to diverge from simpler return metrics.

XIRR versus IRR, CAGR and simple property metrics

The technical difference between XIRR and ordinary IRR comes down to timing assumptions. Standard IRR assumes cash flows land at regular, evenly spaced intervals, typically annually. XIRR drops that assumption and works directly from calendar dates, which matters once your portfolio has settlements, renovations and rent reviews scattered unevenly across the year.

Each metric still has its place:

  • XIRR: the right choice whenever cash flows are irregular, which covers most real property portfolios.
  • CAGR: useful for a single lump-sum investment with no interim cash flows, such as pure capital growth with no rent.
  • Cap rate: a quick snapshot of annual income against purchase price, ignoring financing and timing entirely.
  • Cash-on-cash return: measures a single year’s cash income against the equity you have put in, useful for checking near-term affordability.

As a rule of thumb, reach for XIRR whenever you are measuring a property portfolio’s full lifecycle return, and use cap rate or cash-on-cash as quick annual health checks alongside it rather than instead of it.

Step-by-step XIRR calculation in Excel and Google Sheets

Both Excel and Google Sheets use the same function, so the workflow below applies to either.

Set up two columns. Column A holds dates, column B holds signed cash flow amounts. A simple single-property layout lists each transaction date and corresponding signed amount: negative for purchases and expenses, positive for rent receipts and sale proceeds.

Follow these steps to build and solve the model:

  1. List every transaction in date order down column A, using actual dates rather than text.
  2. Enter the matching signed amount in column B for each row, negative for outflows and positive for inflows.
  3. If the property is unsold, add a final row dated today (or the valuation date) with the current market value entered as a positive figure.
  4. In an empty cell, enter =XIRR(B2:B20, A2:A20), adjusting the range to match your data.
  5. Format the result cell as a percentage to display an annualised rate rather than a decimal.
  6. If the formula returns an error, add a third argument as a guess, for example =XIRR(B2:B20, A2:A20, 0.1), to help the calculation converge.

The guess argument is optional but worth including by default. XIRR’s underlying calculation searches iteratively for the rate that zeroes out the net present value of your cash flows, and an unusual mix of large early outflows and a single late payoff can occasionally cause that search to fail without a starting point to work from.

Pro Tip: Keep a running portfolio-level sheet alongside individual property tabs, so you can calculate a consolidated XIRR for the whole portfolio and a separate XIRR for each property without rebuilding the data layout each time.

A consolidated portfolio sheet also makes it easier to add a new row whenever a cash flow occurs, which keeps the model current without starting from scratch. For readers setting up a full forecast rather than a one-off calculation, a property cash flow forecast built around the same date-and-amount structure extends naturally into an XIRR model once the actual flows replace the forecast ones.

Worked property-portfolio example: build the cash-flow schedule and calculate XIRR

Here is a simplified two-property portfolio, with figures chosen purely to illustrate the mechanics rather than as market data.

Two property cash flows joining one portfolio timeline

Entering these dates and amounts into the two-column layout and running =XIRR(B2:B11, A2:A11) on this sample set produces an annualised return across the whole holding period, blending the sold property’s realised gain with the still-held property’s current valuation. The result reads as a single annualised percentage representing compound annual growth over the period, not a simple average of each year’s performance.

A few adjustments make this more useful in practice; for example, refer to a practical pest inspection checklist for Australian buyers to time maintenance and large-cost items that affect cash flows.

  • Filter the schedule down to a single property’s rows to calculate that property’s standalone XIRR and compare it against the portfolio figure.
  • Swap the final valuation row for a hypothetical earlier or later sale date to test how holding period length changes the annualised result.
  • Re-run the calculation after excluding one-off capital items, like the roof replacement, to separate operating performance from lumpy capital spending.

This last point matters more than it first appears, since a single large renovation dated close to purchase can pull the headline XIRR down even when the underlying rental performance is solid.

Tax, CGT and record-keeping: reflect tax items correctly

Getting the cash-flow schedule tax-accurate is where XIRR for property portfolios differs most from XIRR for shares or managed funds. The ATO’s guidance on property and capital gains tax draws a clear line between two categories of spend, and getting that line right changes what belongs in your cash-flow rows.

  • Capital expenses such as stamp duty, legal fees on purchase, and capital improvements like a new roof or extension are added to the CGT cost base and are not claimed as annual deductions: enter these as a single dated outflow at the time they were paid.
  • Deductible expenses such as council rates, property management fees, insurance and repairs (as distinct from improvements) reduce taxable income in the year they are incurred: enter these as the net rent figure after deducting them, or as a separate outflow row if you prefer to track gross rent and costs separately.
  • Settlement timing and any GST adjustments on a purchase should be dated to the actual settlement date, not the contract date, since that is when the cash actually moves.

Record-keeping discipline feeds directly into the accuracy of this model. The ATO recommends keeping property records for at least five years after you dispose of the asset, and maintaining a dedicated folder per property with settlement statements, receipts, depreciation schedules and capital works documentation makes it far easier to rebuild an accurate cash-flow schedule years later. Where a depreciation schedule is involved, a separate note on depreciation and capital works costs covers how to classify those figures correctly before they go into your XIRR model.

Common XIRR problems and a troubleshooting checklist

Most XIRR errors come down to one of a handful of causes. A #NUM! error usually means the calculation could not converge or that your data is missing either a positive or a negative value entirely. A #VALUE! error almost always points to a non-date entry in the dates column or a text-formatted number in the cash flow column.

  • Check that every cell in the dates range is a genuine date value, not text that looks like a date.
  • Confirm the cash flow list includes at least one negative and one positive figure.
  • Try a different guess value, such as 0.1 or -0.1, if the formula returns #NUM! despite valid data.
  • Cross-check by running XNPV at a known rate on the same inputs to confirm the cash flows themselves are correct before blaming the formula.

Pro Tip: When a large model breaks, copy the data into a fresh sheet one row at a time and re-run XIRR after each addition. This isolates the exact row causing the failure far faster than scanning the whole table by eye.

When to run XIRR and how to interpret multi-period results

A single XIRR figure calculated once and never revisited tells you less than a series of XIRR figures calculated across rolling windows. Running the calculation over 3, 5 and 10-year horizons shows whether a strong headline number is consistent or whether it is being carried by one unusually good year.

  • Recalculate XIRR whenever a new cash flow or updated valuation becomes available, rather than only at sale.
  • Watch for leverage effects: a large loan drawdown or early extra repayment can swing a single-period XIRR sharply without reflecting a real change in property performance.
  • Pair XIRR with cash-on-cash return and an equity multiple to separate annualised pace of return from the total cash multiple earned on your invested equity.

A portfolio performance metrics checklist is a useful reference for deciding which of these complementary metrics to track alongside XIRR for your own portfolio.

How WealthStacker helps automate cash flows and produce portfolio returns

Building this schedule by hand works, but it is tedious to keep current across a growing portfolio. We built WealthStacker around automated quarterly property valuations, portfolio dashboards and scenario modelling for both rentvesting and buying strategies, so the dated valuation entries an XIRR model needs are generated for you rather than chased down manually. Our typical workflow: let valuations and rent data populate the dashboard each quarter, export the dated figures, then drop them straight into the two-column spreadsheet layout above to run XIRR against current numbers instead of stale ones.

Wealthstacker

Try WealthStacker to automate valuations and export dated cash flows for XIRR

Every spreadsheet step above depends on having accurate, current dates and figures, which is exactly what free automated quarterly valuations through WealthStacker are built to provide. Beyond valuations, our scenario modelling tools let you compare how a rentvesting path or an outright purchase would shift your projected XIRR over a 15-year horizon before you commit either way. If you are holding or building a property portfolio, take a look at the full feature set and export your dated cash flows straight from the dashboard the next time your quarterly valuation lands.

This article is general information, not a substitute for advice from a qualified financial advisor. Consult a qualified financial professional about your own circumstances before acting on anything here.

FAQ

How do I calculate the XIRR of a mutual fund?

The method is identical to property: list every contribution, withdrawal and current unit value with its date in two columns, then run =XIRR(values, dates). The main difference is that fund cash flows are often smaller and more frequent, such as monthly contributions, but the XIRR formula handles that irregularity the same way it does for property.

Is a 4% rental yield good?

Rental yield alone cannot answer that question since it ignores financing costs, capital growth and timing, all of which an annualised XIRR captures together.

Can we get a 15% return on a mutual fund?

Fund returns vary widely by asset mix, time period and market conditions, so no fixed percentage applies universally. The most reliable way to check what a specific fund or portfolio has actually returned is to calculate its XIRR directly from the dated contribution, withdrawal and current value figures rather than relying on a general benchmark.

What percentage of an investment portfolio should be in real estate?

There is no single figure that suits every investor, since the right allocation depends on your borrowing capacity, risk tolerance and other holdings. Rather than targeting a fixed percentage, many investors compare the projected XIRR of a property purchase against other asset classes to decide how much of their portfolio it makes sense to commit.

Sources

Recommended