Forum Discussion
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!
------------------------------------------------------------------------------------------
6 Replies
I have the functions to make for an elegant FIFOλ/LIFOλ lambda though I hesitate to finalize it because the arrangement of FIFO data is not always the same.
Is there a "typical" arrangement that can be banked on?
"Typical arrangement" ... I doubt it!
I have attached a file that contains a possible layout for a single product line.
The file was used to support an on-line presentation so it is more focussed upon demonstrating solution techniques than attempting the most efficient solution for each problem. The message being that a dynamic array solution in Excel may have very little in common with the traditional spreadsheet!
I clearly am not at my brightest and best as I have struggled with this!
Now I need to try NikolinoDE's solution on the data since it appears to be more direct.
Third go
Second go at attaching a file!!!!
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.
