Loss carry forward for limited years

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.