XIRR for rental property: cash-flow dates and the exit value matter

XIRR calculates an annualized internal rate of return from dated cash flows. It can help summarize what an owner contributed and received at different times. If the last receipt is an assumed sale or valuation, the result is estimated, even when every earlier entry is actual cash.

A rental can require annual cash top-ups and still show a positive estimated XIRR if a sufficiently large net exit receipt is assumed. The operating cash shortfall and the full investment cash-flow schedule are different views.

The simple version

Record when money went from you into the investment, and when money came back. XIRR finds a rate that balances those dated amounts after adjusting for time. It is not cash paid to you each year, and it does not make an assumed buyer appear at your chosen sale price.

Build the owner's cash-flow schedule

Use negative amounts for owner contributions and positive amounts for distributions or net terminal receipts. Keep currency consistent. Record actual contribution and receipt dates; sort chronologically with the earliest date first.

Do not add every rent receipt and repair invoice if they already sit inside a net owner cash-flow entry. Internal reserve transfers are not new owner contributions unless new money actually enters from the owner. Mortgage principal repayment belongs in the property cash calculation and reduces terminal debt; it is not another distribution to add.

Microsoft's XIRR documentation specifies dated flows on a 365-day basis and requires positive and negative entries. It defines the answer as the rate at which XNPV, the dated net present value, equals zero. For a rate $r$:

$$\sum_{i=0}^{n}\frac{C_i}{(1+r)^{t_i}}=0$$

Here, $C_i$ is each signed owner flow; $t_i$ is actual elapsed days from the first date divided by 365; and $n$ is the final entry's index. In this calculation, $r$ is a decimal annual rate greater than −1. Leap days remain in the elapsed-day count.

In Excel, place signed amounts in one column and actual spreadsheet dates in another, then use XIRR(amount range, date range). Check the entries before interpreting the result.

A separate hypothetical exit scenario

This example is separate from the CAD 125,000 operating-year example elsewhere in the series. It begins with CAD 150,000 contributed on January 1, 2020. The owner supplies CAD 5,000 each year for operating shortfalls. An assumed sale occurs on January 1, 2025.

Assumed sale price is CAD 900,000, sale costs are CAD 45,000, and loan payout is CAD 350,000. After the final CAD 5,000 top-up, the last net owner receipt is CAD 500,000. Income tax, mortgage break fees and other exit charges are assumed zero for this calculation; a decision scenario must add any that apply.

The independently checked estimated pre-tax XIRR is about 25.29%. Total net cash across the table is positive CAD 330,000. Equivalently, the owner contributes CAD 175,000 including the last top-up and receives CAD 505,000 before that top-up is netted into the last entry. Do not subtract it twice.

This is not an exclusively negative lifetime series. The positive terminal assumption drives the result, while the owner still needs liquidity to fund the shortfalls.

Test the terminal receipt

Reduce the assumed sale price to CAD 650,000 and sale costs to CAD 32,500, holding the loan payout and top-ups fixed. Final cash becomes CAD 262,500, and estimated XIRR falls to about 9.59%.

Those are sensitivity cases, not likely outcomes or healthy-return bands. Neither result is realized until the assumed transaction and amounts actually occur. An unsold-property valuation needs an explicit terminal convention; gross market value cannot stand in for owner proceeds when debt and costs remain.

When a number is missing or ambiguous

Microsoft documents errors for one-sided flows, invalid dates, unequal ranges, dates before the starting date, and unsuccessful iteration. Both signs are necessary; they do not guarantee a unique answer.

Iowa State University Extension's capital-budgeting guide explains that cash flows alternating between inflows and outflows can produce multiple internal rates. As a separate arithmetic demonstration, CAD −100, +230 and −132 at exactly 365-day intervals have roots at both 10% and 20%. Each makes discounted net value zero. A solver returning one root does not establish one unambiguous investment return.

Review unusual sign patterns, verify a root's discounted residual and investigate sensitivity to the starting guess. Do not choose whichever root looks nicest. Our check script validates the two stated sale scenarios; it is not a general solver for every possible cash-flow pattern. XIRR also does not measure liquidity, dollar profit or risk by itself.

Put it to use

Keep dated records as contributions and distributions occur. Review the scenario annually and at a transaction, refreshing exit value, costs and debt. These are suggested cadences. If estimated return jumps, identify which flow or date changed before attributing the increase to operations.

Use the cash-flow guide alongside XIRR to check funding needs, and ROI to reconcile gain. The more detailed negative-cash-flow decision article tests lower and later sale receipts.

Sources

• Microsoft, XIRR function, current Excel function documentation; checked October 1, 2026. Dates, sign requirements, 365-day basis, XNPV relationship and documented errors.
• Iowa State University Extension, Capital Budgeting Basics, Ag Decision Maker C5-240, updated June 2023; checked October 1, 2026. Used for the multiple-IRR caveat, not its project-selection advice. The dated inputs above are hypothetical and were independently recalculated October 1, 2026. Exit prices and costs are assumptions, not market evidence.