This webpage discusses how to deal with the tricky problem of computing expiration of net operating loss (NOL) in the context of project finance models where the NOL can expire. This case involves losing the ability to use the NOL after a defined time limit. For example, the NOL may expire after six years and you must compute how much NOL you can use. This may seem easy by just using the OFFSET function, but it is not. Computation of the NOL with an expiration can be painful because you cannot simply go backwards using the OFFSET function to find the expired NOL (e.g. the amount of NOL that was generated six years ago). This is because in the intervening period (i.e. before the six years), some of the NOL may have been used and this should not be part of the expired NOL — if you assume that all of the NOL is expired you will have too much reduction in the NOL balance. I address the problem of adjusted NOL for earlier NOL use by accumulating the amount of NOL available for expiration using a vintage method that keeps track of the expired NOL in a separate account with an opening and closing balance. This means you can go back six years, but you can adjust this balance for the amount that has already been used and should not be used in the expiration formula. In the discussion below, I label this account as the accumulated expired NOL. If the NOL can be used for an indefinite period the issue of expired NOL is no big deal. But when there is an expiration after a certain period of time, the problem becomes difficult. You still 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 file below contains an example of the method that is discussed below. The map shows different NOL expiration from different countries taken from the website where the fastest expiration period in Solvicia:
.
.
.
Step 1: Add a Separate Account for Accumulated Available Balance for Expiration to your Classic NOL Account
In the discussion below, I assume that you are familiar with the basic use of MIN/MAX to compute the NOL where you use MAX(-EBT,0) to compute the NOL generated from negative income and MIN(Opening NOL, MAX(EBT,0) to compute the NOL used to reduce future taxes. I have dealt with this issue elsewhere. Note that the tax loss can be reduced by a factor less than 100% meaning that you should compute the tax loss and the tax loss carryforward in different rows.
The first step in creating the NOL with expiration is to add a subtotal to the classic account balance as illustrated below. The subtotal is necessary because you cannot use NOL that has already expired and this amount reduces the NOL balance. This is illustrated in the screenshot below where I set-up a hypothetical scenario with fluctuating earnings before tax and a short expiration period. In the example, there is negative 200 of earnings in the first period (column F) 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 which is the original NOL taken, but instead 120 (column I). This is because 80 of the 200 NOL has already been used up in year 2 (Column G). This leaves 200-80 or 120 left for expiration that occurs in year 4 (Column I). Further, the expiration of the of the NOL is not affected by the added 20 that is taken in year 3.
.
.
The tricky part of the above table is to compute the reduction in the NOL which is a function of both the amount that would expire had none been used already and the opening balance of the accumulated remaining amount that you can expire.
It may seem that the amount on line 25 causes a circular reference, but this is not the case. The formula in line 25 is a function of the EBT and the opening balance. You can compute this twice or use a link to the number below.
.
.
Another Example – Accumulated Amount Available for Expiration
I have struggled with this for a long time and I have tried to explain how to compute the NOL with different examples. The discussion below has the same formulas above but is explained a bit differently. As you try this a few times, you can see that if you set-up the account for accumulated amount available for expiration, and you use the MIN function without the constraint for the amount available for expiration things are not so difficult.
Calculation of the 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.
.
.
The screenshot below is an illustration of how I included the titles and made an analysis of expiring NOL. In the example, I have made an extremely short expiration period so that you can hopefully see that the NOL has been used and is not available. The key number that is affected and that drives the cash flow and the IRR is the use of the NOL.
.
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.
.
The example below demonstrates a case that may be analogous to project finance. In this case the EBT is not fluctuating, but the the expiration of the NOL could be less for example than the period of the accelerated depreciation. The example demonstrates that expiration of NOL can be a problem in the case of constant cash flow.
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.
.
.
.
.
.
Video Describing NOL Expiration Mechanics
.