How do I account for accumulated tax receivables while calculating the payable tax

We have faced negative profits due to the covid restrictions which makes use eligible for tax receivable with the Danish state. We however budget with growing profits over the next couple of years, and would like to create a calculation for the tax where we first deplete the receivable, and then use cash to fund the rest of the tax. Is there some formula for this?

1 Like

Hey Bastian, sounds like a great use case! Here’s how I’d go about it :

  • Start by creating a variable for the Tax which has negative values for periods with tax receivable and positive values for the months with payable.
  • Create a variable that calculates the Cumulative Balance which accounts for the accumulated receivables and the receivables that have already been used in profitable months. The formula would look something like : Cumulative Balance[previous]+Tax[current]-Payable Amount[previous] (Create a variable for the Payable Amount we will get to in the next point)
  • Finally you can write the formula for the Payable Amount like max(0, Cumulative Balance) . This way you will only have to pay if the Cumulative Balance is positive i.e you have depleted the receivables.

Here’s an example model, you can clone the same by clicking on “Use this template” to the top right

1 Like