Modelling tax loss carry forward

In most jurisdictions, tax losses don't result in the government giving us money.

However, most tax authorities will allow you to carry losses forward and offset them against taxable profit in the future.

Sometimes companies can carry these losses forward indefinitely. Sometimes they expire after a set number of years.

Our imaginary tax advisor has informed us that there is no limitation on the carry forward of tax losses in this jurisdiction.

So let's see how we can action this in the model.

Our steps will be as follows:

  • Split out our Taxable Profit / (Tax losses arising) line into two separate lines. Each of these lines is going to drive further calculations.
  • Tax losses arising will be the upward flow in a corkscrew we'll need to set up. The downward flow will be the utilisation of those tax losses.
  • In any period, we'll compare Taxable profit and available tax losses brought forward from earlier periods. The tax losses utilised will be the lower of those two numbers.
  • We'll deduct the utilised tax losses from the Taxable profit to give the "Taxable profit after tax losses utilised". Talk about a snappy label.
  • Finally, we'll calculate the Tax paid by multiplying this line by the tax rate. We've been advised that taxes are paid quarterly. What do you mean by "isn't that a convenient simplification to make the modelling easy"? I mean, how long do you want me to drag out these tax chapters?

Let's go.

Download the start file for this section

Step 1 - Separate Taxable Profit from Tax losses arising.

We're going to use two blocks for this:

In the first block, we only want the positive values, so take the MAX of the value and zero. This only returns the positive values.

In the second block, we take a similar approach to isolate the negative values by taking the MIN of the value and zero. However, we want to process this line item as a positive item in our model to stick to our sign convention rules. We, therefore, sign switch this to give us positive values.

Step 2: Set up our corkscrew

We don't need an initial value injection, so we're using a four-line corkscrew structure. We immediately link tax losses into the corkscrew.

Step 3: Calculate tax losses utilised.

We first link to taxable income. Then to the beginning balance of tax losses. The tax losses utilised is the minimum of those two. This then feeds back down into the corkscrew.

Step 4: Calculate tax losses after Tax-loss utilisation

We now deduct the tax losses utilised from the taxable income to give the Taxable income after utilisation of tax losses.

Step 5: Calculate Tax paid

We now have the final Taxable Profit after tax losses that we can use to calculate the Tax payable line. To calculate the Tax paid, we multiple Taxable Profit by the tax rate,  in this case, 15%.

This line will go to both the cash flow and income statements.

In our case, given the tax losses, tax payments do not begin until 2027.

Step 6: Wire into financial statements

Now that we have out Tax paid line, we can connect them to our financial statements. The Tax paid line will link to both the income statement and the cash flow statement.

Download the financial model completed to this point:

Circularity in our tax calculation

When we do this, we immediately get a warning to tell us that the model is circular.

Why is the tax calculation circular?

Please take a moment to think through what might be causing us to get a circularity warning. This will help you when you find circularities in your own model later.


Sign in or become a Financial Modelling Handbook member to join the conversation.
Just enter your email below to get a log in link.

Subscribe to Financial Modelling Handbook

Don’t miss out on the latest financial modelling guides. Sign up now to get access to the library of members-only guides.