This webpage illustrates how to deal with the tricky problem of computing net operating loss NOL for tax purposes when there is an expiration after a defined time period. Computation of the NOL with an expiration can be painful because you cannot simply go backwards using the OFFSET function. I address the problem by accumulating the amount of NOL from a vintage that has expired in a separate account. If the NOL can be used for an indefinite period there is no big deal. But when there is an expiration after a certain period of time, the problem becomes difficult. You have to remember when the NOL occurred and make an adjustment. The process for working through this issue is demonstrated in the file attached to the button below.
The first step in creating the NOL with expiration is to add a subtotal to the classic account balance below. The subtotal is necessary because you cannot use NOL that has already expired. This is illustrated by the screenshot below where I set-up a hypothetical scenario where there is fluctuating earnings before tax and a short expiration period. In the example, there is negative 200 of earnings in the first period and the expiration occurs after 3 years. But the expiration in year 4 (three years after the NOL is taken) of the NOL is not 200, but instead 120. This is because 80 of the 200 NOL has already been taken in year 2. This leaves 200-80 or 12o left for expiration. Further, the expiration of the of the NOL is not affected by the added 20 that is taken in year 3.
Use of the NOL is shown in the screenshot below. Note that the amount of NOL used is compared to the sub-total after the NOL expiration rather than regular old opening balance. The screenshot shows that the MAX and MIN combination is used as you can only use the NOL when there is a positive earnings before tax.
Now lets see how we can compute the 120 which seems that it should not be so complicated. My method, which may not be as efficient as possible, is to work through a balance of how much is “available for expiration.” First you compute the expiration of the NOL by going backwards. I just use the OFFSET function but I understand that others use a painful SUMIF. This equation and others are shown in the screenshot below. When you use the OFFSET like this you must make some kind of test that you do not look before the calculations start and you should put a negative amount for the expiration date.
Now we can move to the adjusted expiration of the NOL which accounts for NOL already used and that is not available for NOL any more. The key is to use the MIN function and compare the amount that would be expired with the opening balance of the NOL account. This is illustrated in the screenshot below. (Note that I ran the generic macro colouring function in the meantime). The key is to compute the reduction in the NOL using the MIN function. You compare the expiration that would happen with the opening balance of the account.
The final thing to do is to compute the expired NOL and to put it back into the NOL balance. The final expired NOL is row 21 — the unadjusted NOL compared to the NOL that is reduced by line 26, the amount that was taken before that is accumulated.
To illustrate this process is illustrated by different scenarios below. In the first scenario there is a negative EBT followed by positive EBT and there is no adjustment in the NOL expiration from the initial OFFSET function.
The remaining screenshots show different scenarios with various patterns of EBT. In the first scenario, there is one negative EBT followed by positive EBT. In this scenario, there is less expiration adjustment in the NOL because there is more NOL already used. The second scenario has a whole bunch of negative EBT’s which are not followed by positive EBT’s. In this case the expiration is not adjusted and the NOL just expires without future benefit. In the third case there are a few negative EBT’s followed by positive EBT’s. In this case case there is some expiration that is adjusted and also some NOL that is used.
