Forum Discussion

papa_austin's avatar
papa_austin
Copper Contributor
Jan 14, 2026

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!

 

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

 

 

Formulas and Functions

1 Reply

  • NikolinoDE's avatar
    NikolinoDE
    Platinum Contributor
    Jan 17, 2026

    Core idea: store loss buckets as an array

    Each year’s state will be a vector of remaining losses by age:

    For N = 3:

    { Year-1 losses, Year-2 losses, Year-3 losses }

    Oldest is always on the right → FIFO is easy.

    Spill formula solution (no helpers, no VBA)

    Remaining loss inventory by year

    =SCAN(

  MAKEARRAY(ROWS(B2:B6), N, LAMBDA(r,c,0)),

  B2:B6,

  LAMBDA(prev, income,

    LET(

      aged, HSTACK(0, TAKE(prev,,N-1)),

      losses,

        IF(

          income<0,

          aged + HSTACK(ABS(income), MAKEARRAY(1,N-1,LAMBDA(r,c,0))),

          LET(

            remaining, income,

            MAP(

              aged,

              LAMBDA(x,

                LET(

                  used, MIN(x, remaining),

                  remaining := remaining-used,

                  x-used))))),losses)))

    What this does:

    • Ages losses (expiration handled automatically)
    • Adds new losses
    • Applies income FIFO to oldest losses first
    • Returns a spill range showing remaining losses by age for each year

    Total loss available each year

    Once you have the spill above (say it starts in D2):

    =BYROW(D2#, SUM)

    Taxable income after loss usage

    =MAX(0, B2 - previous_year_loss_available)

    (or derived directly inside the SCAN if you want everything in one formula)

     

    Excel formulas can do this, but:

    • This is business logic
    • If this were production tax modeling, VBA or Power Query is still safer and more auditable

    That said, for financial modeling and scenario analysis, this approach is solid.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

     

    Was the answer useful? Mark as best response and like it!

    This will help all forum participants.

Resources