Loss carry forward for limited years

Hello Excel Community,

Excel Version: O365 (Enterprise)

I’m running into a mental block trying to model loss carryforward with limited years using a spill formula.

Below is a simplified example of what I’m trying to build a formula for.

Rules:

• ⁠A loss from any year can be carried forward for N years, after which it expires and can no longer be used.

• ⁠In any year with a positive tax liability, if there is any remaining loss from the past N years, you deduct using a FIFO approach (oldest losses used first).

• ⁠Any unclaimed balance from year N+1 expires, even if it hasn’t been fully used.

I’ve found plenty of examples showing loss carryforward without expiration, but I’m struggling to build something that handles both FIFO and expiration in a dynamic array formula.

I feel like this must be a solved problem and I’m just missing something obvious. Any help would be greatly appreciated!

------------------------------------------------------------------------------------------