blueguitar23
New Member
Hi Chandoo community, I'd be enormously grateful for any suggestions on my tax calculation problem. The issue is how to tell Excel to account for carry forward cumulative tax credits in the cash flow statement, until they are used up and then to revert to a standard method of calculating tax in the cash flow statement. Before the eyes of non-finance people glaze over, I'll try and explain.
In the early years of a project, it makes losses. In theory this gives a tax credit, i.e. the government pays you money. But until you make a profit and start paying taxes the credits can't be used. So, you may make losses for three years, and then start making a profit, but the cumulative credits mean you don't actually start paying taxes until year six.
The next issue is that the cash flow statement reflects only what you actually pay the government that year, so in my example, we pay 50% of last year's accounting tax and 50% of this year's tax.
So, I need a formula that:
(1) sets the cash flow tax to nil until the cumulative tax due becomes negative
(2) in the first and second year that the cash flow tax becomes negative, I need to take 50% of the cumulative tax in that year
(3) in all years following, I need 50% of the preceding year accounting tax and 50% of the current year accounting tax.
I have come up with a solution in the attached file, which uses a cumulative tax line as a memo but I would like to avoid having to use this memo line if possible.
If anyone has time to look over the attached and see if there is a more elegant solution, I would be very interested to read it. I'm afraid I have to avoid using VBA.
Thanks for reading.
Stan
In the early years of a project, it makes losses. In theory this gives a tax credit, i.e. the government pays you money. But until you make a profit and start paying taxes the credits can't be used. So, you may make losses for three years, and then start making a profit, but the cumulative credits mean you don't actually start paying taxes until year six.
The next issue is that the cash flow statement reflects only what you actually pay the government that year, so in my example, we pay 50% of last year's accounting tax and 50% of this year's tax.
So, I need a formula that:
(1) sets the cash flow tax to nil until the cumulative tax due becomes negative
(2) in the first and second year that the cash flow tax becomes negative, I need to take 50% of the cumulative tax in that year
(3) in all years following, I need 50% of the preceding year accounting tax and 50% of the current year accounting tax.
I have come up with a solution in the attached file, which uses a cumulative tax line as a memo but I would like to avoid having to use this memo line if possible.
If anyone has time to look over the attached and see if there is a more elegant solution, I would be very interested to read it. I'm afraid I have to avoid using VBA.
Thanks for reading.
Stan