Calculating Tax Losses with an Expiration Period


It is often the case that certain countries limit the length of time companies can carry their tax losses on the balance sheets. This is to restrict the company’s ability to minimise their tax liabilities by carrying tax losses on the balance sheet indefinitely.

This tutorial shows how to upgrade a standard tax loss calculation to account for an expiration period.

In this tutorial we will cover the following

  1. How to calculate a standard tax loss amount
  2. Including an expiration period

How to calculate a standard tax loss amount without expiry

In the attached case study we have a project that is making a loss for the first two quarters. In project finance, this may occur at the start of a project during the ramp up phase.

The calculation is separated into three sections

  1. Tax Losses
  2. Net Taxable Income
  3. Tax Payable

Tax Losses

Carried forward tax losses are used to reduce the current period’s pre-tax profit. Companies want to maximise tax losses in order to offset future tax liabilities. Subsequent upgrade of this section of the model is the focus of this tutorial.

Screenshot 1: Modelling a standard tax loss account

In Screenshot 1, an account structure is set up to show a typical tax loss calculation with opening balance (Balance B/f in row 22) and closing balance (Balance C/f in row 25). In between we

  • Add tax loss generated
  • Deduct tax losses utilised

Tax Loss Generated (row 23) adds to the account when there is a pre-tax loss.

Tax Loss Utilised (row 24), as the title suggests, is depleted from the account when the project makes a Pre Tax Profit.

In our popular Project Finance Modelling (B) course we cover an interactive demonstration of how to model tax losses.

Net Taxable Income
Net Taxable Income is the amount taxed after other income and deductions have been accounted for. Examples of deductions include interest expense, royalties and depreciation.

We have kept it simple in our case study where

Net Taxable Income = Pre-Tax Profit (Loss) – Tax Losses

Screenshot 2 illustrates the calculation.

Screenshot 2: Modelling Net Taxable Income

Since no tax is payable when Net Taxable Income (row 14) is negative, a MAX(0,) function is used to ensure only positive values appear.

Tax Payable

Tax Payable = Net Taxable Income x Corporate Tax Rate

Screenshot 3: Modelling Tax Payable

In Screenshot 3 we make a simple assumption that the corporate tax rate (cell I18) is applicable during the operations period only by multiplying by the operations flag (row 5), but it is common to further add a tax holiday function.

Some countries have tax holidays or the tax rate is effectively 0% during the beginning stages of the project. This is used by governments to attract capital investment.

Including an expiration period

In the following section we replicate the standard sections already covered with one additional line to the Tax Loss Account.

In the Tax Loss Account as laid out in Screenshot 4, we insert an extra line (row 44) “Expired Tax Losses (Net)” where we assume tax losses expire after six months. Conceptually, this line reduces the balance in the tax loss account by tax losses generated more than six months ago which have not yet been utilised.
There are two types of Expired Tax Losses, net (row 44) and gross (row 47). Expired Tax Losses (Gross) captures the tax losses generated six months prior by using the OFFSET function that refers to

  • Tax Loss Generated (row 42)
  • Counter (row 39) that counts back six months to locate the applicable tax loss expiring

Screenshot 4 demonstrates how $150 USD m (cell L44) corresponds to a tax loss of that amount generated two quarters ago (cell J42).
For a demonstration of how to use the OFFSET function refer to our OFFSET Function in Excel tutorial.
Expired Tax Losses (Net), as the title suggests, is the gross equivalent net of the Amount Utilised (row 48). Conceptually, how this works is Expired Tax Losses (Net) in cell M44 is 0 because the tax losses supposedly expired in that period (cell M47) has already been fully been utilised (cell M48 & M43).

Screenshot 4: Modelling Tax Losses

Row 48 signifies the amount of tax losses utilised before they expired. The formula tries to do a few things

  • Be less than the Expired Tax Losses (Gross), and return 0 when there are no expired tax losses
  • Take the cumulative tax losses utilised (row 43) from previous periods

Lastly we need to make sure Expired Tax Losses (Net) does not reduce the account to a negative closing balance.

This is achieved by applying the MIN Function to Expired Tax Losses (Net) for a particular period to derive the lesser of

  • Expired Tax Losses (Gross) less Amount Utilised
  • Balance B/f + Tax Loss Generated – Tax Loss Utilised

Comparing screenshots 1 & 4 we can see the impact an expiration period has on the tax losses account. Screenshot 4 illustrates that only $220 USD M (cell I43) of tax loss is utilised versus the full $370 USD M in the standard treatment in Screenshot 1.

Screenshot 5: Tax Payable (With Expiry Applied to Tax Loss)

The most important difference from the company’s point of view is that total tax payable has increased from $294 USD M in Screenshot 3 (cell 19) versus $339 USD M in Screenshot 5 (cell I36).

Final Words

Tax is an area where the various nuances of rules and regulations can add layers of complexity to any financial model. As a result, it is particularly important to keep your model as transparent as possible by focusing on the three pillars of Best Practice Modelling

  1. Model Infrastructure
  2. Formatting and Presentation
  3. Formula Structure

In the attached model we have demonstrated our best practice approach to this issue of calculating tax losses with an expiration period. To find out more about our methodology visit our other tutorials or check out our Project Finance Modelling (A) course.

Share this post


You must be logged in to view the Tutorial