Spencer Burton
,

Fix the 0% XIRR Issue in Excel Using XIRR + FILTER

Over the course of my career, I’ve modeled hundreds of real estate investments, from large portfolio roll-ups to complex mixed-use developments. And one issue I’ve consistently run into, especially when modeling portfolio or mixed-use cash flows, is this: the first cash flow in an IRR range is often not negative.

The problem? Excel’s built-in XIRR function expects the first value in its range to be negative. So, if the first cell (or the first several cells) are zero, XIRR will always return 0.00%, even if cash flows materialize later.

I saw someone mention this conundrum on LinkedIn recently, but they didn’t share the fix. So, I thought I’d share the formula I use: a simple combination of XIRR and FILTER that dynamically starts the IRR calculation from the first month of actual cash flow. It’s a relatively simple formula, but it makes a big difference in ensuring clean, accurate return outputs.

A More Robust Way to Calculate IRR with Non-Annual Periods

A few years back, I noticed a curious variation on how I’d always calculated XIRR in portfolio models. In one of our LP’s models, they were using a formula that combined XIRR() with FILTER(). A pairing I hadn’t seen before.

Although today I would likely use AI to help write the optimal formula in Excel for this scenario, up to that point, I’d been handling zero-cash-flow periods the hard way. My method relied on IF/THEN logic to identify when cash flows actually started, then shift the XIRR range accordingly. It worked, but it was clunky, over-engineered, and easily broke when the logic wasn’t perfect.

Their version solved the same problem far more elegantly. Instead of trying to reframe the cash flow range with conditional logic, they simply filtered out the zeros. The FILTER() function dynamically removed all non-active periods, leaving XIRR() to calculate returns starting with the first actual cash movement.

It was clean. It was simple. And it made my old workaround instantly obsolete!

Since then, that formula has become a staple in my toolkit. Below, I’ll walk through exactly how the formula works and how to make it reusable across any model.

What the XIRR + FILTER Formula Does

At its core, this formula solves one simple problem: XIRR doesn’t know what to do when the first few periods in your cash flow series are blank or zero, XIRR assumes there’s no valid investment yet and returns 0.00%.

The trick is to make XIRR ignore those zero cash flows altogether, so it starts calculating from the first actual outflow of capital. That’s exactly what the FILTER() function does. It tells Excel to use only the values that matter.

Here’s the formula:

=XIRR(FILTER(L17:BB17, L17:BB17<>0), FILTER($L$14:$BB$14, L17:BB17<>0))

In this example, row 17 is the unlevered or levered cash flows line whereas row 14 is the dates line.

Let’s break that down:

  • XIRR(values, dates) is Excel’s function for calculating the internal rate of return on a series of cash flows that occur on irregular dates.
  • FILTER(L17:BB17, L17:BB17<>0) takes your cash flow row (in this case, row 17) and filters out any cells equal to zero.
  • FILTER($L$14:$BB$14, L17:BB17<>0) takes the corresponding date row (row 14) and filters it using the same logic, so that each date still aligns perfectly with its matching cash flow.

The result:

XIRR now evaluates only the periods where cash actually moved.

This simple combination dynamically adapts to your model, whether the first cash flow happens in month 1, month 6, or month 12. It’s clean, transparent, and eliminates the need for the brittle IF/THEN logic that often breaks as models evolve.

Turn the XIRR + FILTER Formula into a Template

Once you’ve tested the formula and confirmed it’s working, the next step is to make it easy to reuse. There are two simple ways to do that, depending on how flexible you want it to be.

Option 1: Use Named Ranges

If you want a simple version that’s easy to read and audit: using named ranges, name the cash flow row ‘CashFlows‘ and the date row ‘FlowDates’, then use:

=XIRR(FILTER(CashFlows, CashFlows<>0), FILTER(FlowDates, CashFlows<>0))

That’s it. The formula is now dynamic, and it will work for anyone who opens the file.

Option 2: Wrap It in a LAMBDA Function

If you’re using Excel 365 and want a more scalable solution, wrap the formula inside a custom LAMBDA function.

  1. Go to Formulas → Name Manager → New
  2. Set the Name to DynamicXIRR
  3. In the Refers to box, paste this: =LAMBDA(cashflows, dates, XIRR(FILTER(cashflows, cashflows<>0), FILTER(dates, cashflows<>0)))
  4. Now, anywhere in your workbook (or any workbook you import the function into), you can calculate IRR dynamically by calling: =DynamicXIRR(L17:BB17, L14:BB14)

This version behaves just like a built-in Excel function. It’s the cleanest, most scalable way to calculate dynamic IRRs across the workbook. Note that if you plan to share the file externally, the LAMBDA definition doesn’t automatically travel with it. Anyone opening the workbook without that defined function will get an error.

For shared models, stick with the named-range version.

Conclusion

So, there you have it. A simple but powerful fix for one of Excel’s quiet frustrations. By combining XIRR() with FILTER(), you can make your IRR calculations dynamic to the first month of actual cash flow and eliminate those misleading 0.00% results once and for all.

If you haven’t already added this trick to your toolkit, I highly recommend it!

About the Author: Spencer Burton is Co-Founder and CEO of CRE Agents, an AI-powered platform training digital coworkers for commercial real estate. He has 20+ years of CRE experience and has underwritten over $30 billion in real estate across top institutional firms.

Spencer also co-founded Adventures in CRE, served as President at Stablewood, and holds a BS in International Affairs from Florida State University and a Masters in Real Estate Finance from Cornell University.

Contact Spencer
by