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. This case involves losing the ability to use the NOL after a defined time limit. The issue of NOL’s with a time limit arises when there is an expiration of the accumulated NOL after a defined time period. For example, the NOL may expire after six years. 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. I address the problem of adjusted NOL for earlier NOL use by accumulating the amount of NOL using a vintage method that keeps track of the expired NOL in a separate account with an opening balance. This means you can go back six years, but you can adjust this balance for the amount that has already been used. 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 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 in Solvicia:
Step 1: Add a Separate Account 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 and MIN(Opening NOL, MAX(EBT,0) to compute the NOL used to reduce future taxes. Note that the NOL can be reduced by a factor that is now the case in the U.S.
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.
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.
As you may not use the expired NOL very often, I have put some titles together so that you can just copy and paste these to your model. If you look at the formulas in the screenshot, you can see that the formulas are not too complex, but it is difficult to remember the process.
|Opening Balance (Last period Closing)|
|Less: NOL Expired (From bottom of schedule below)|
|NOL Sub-Total (Difference between opening and reduction)|
|Add: NOL Created from Negative Income Max(-EBT,0)|
|Less: NOL Applied to Positive Income Min(Subtotal,Max(EBT,0)|
|Closing Balance (Add and Subtract)|
|Calculation of Expired NOL|
|NOL Unadjusted Expired NOL without Adjustment (Offset, Generated)|
|Accumulated Balance of NOL Already Used and Not Expired|
|Opening Balance (Priod Closing)|
|Add: NOL Used in this Period (Reduces Expiration)|
|Less: Expired NOL from Opening Balance (Min(opening, Offset)|
|Closing Balance of Adjustment to Expiration|
|Expired NOL this Period (Offset – Used)|
NOL Balance Opening Balance (Last period Closing) Less: NOL Expired (From bottom of schedule below) NOL Sub-Total (Difference between opening and reduction) Add: NOL Created from Negative Income Max(-EBT,0) Less: NOL Applied to Positive Income Min(Subtotal,Max(EBT,0) Closing Balance (Add and Subtract) Calculation of Expired NOL NOL Expired without Adjustment (Offset with Expiration) Accumulated Balance of NOL Previously Used Opening Balance (Priod Closing) Add: NOL Used in Period, Not in NOL Expiration Less: Reduction in NOL Expired (Min(opening, Offset) Closing Balance Expired NOL this Period (Offset - Used)
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.