• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Practical tax problem

B

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
 

Attachments

  • Tax problem.xlsx
    11.5 KB · Views: 10
@blueguitar23
Hi!

Looking at your file I understood these things (Correct me if I am wrong)
1. You want the formula in cell B9:M9.
2. Your input are B2:M2.
3. You don't want Row 3:8.

If so, why don't you use formula of cell B3 directly into the formula of B9.

If you want something else, than kindly explain further, with some manual examples including only input & outputs.

Regards!
 
Hi Somendra, thanks for taking the time to reply. Your points 1-3 are correct. Row 2 is the tax line in the accounts and rows 5 and 8 are there for information to show what the output values should be. I only need to allocate tax to the cash flow statement once the cumulative total in row 2 goes negative, which is what J3 shows. Before that the cash flow statement should show nil tax each year. The formula in row 8 is doing the job at the moment but I was wondering if there was an alternative way to achieve it without using row 3.

Regards
 
Hi ,

Can you check this file ? The formula is in row 13 , and depends on N2 and O2 having the value 0 in them , instead of remaining blank.

Narayan
 

Attachments

  • Tax problem.xlsx
    10.8 KB · Views: 10
@NARAYANK991 Sir
Excellent formula sir.
Can you share how you approach to that formula. I can see that problem is counting the first & second negative tax value. If we can get 1,2 than it simple IF can do the rest. But problem is getting only 1,2.

Kindly, share your step by step approach to reach it.

Thanks & Regards!
 
Hi Misra ,

Let us wait for the OP to confirm that the formula does what is required.

The difficulty as I see it is that there are two situations :

1. We need to see when the running total of the accounting tax in row 2 becomes negative. This calculation is simple , since :

SUM($B$2:B2)>0

gets the running total as one value.

2. The difficulty is that the tax calculation changes after two months of negative tax have elapsed ; here , we need the running total not as one value , but as an array of values , so that we can see how many months elapse after it has become negative.

The array of running totals is done by this :

=MMULT(N(TRANSPOSE(COLUMN($B$2:B2))>=COLUMN($B$2:B2)),TRANSPOSE($B$2:B2))

If you enter this in any unused cell in column B , and copy it across , it generates an array of values such as :

={100}

={100;200}

={100;200;300}

={100;200;300;500}

={100;200;300;500;850}

={100;200;300;500;850;450}

={100;200;300;500;850;450;250}

={100;200;300;500;850;450;250;50}

in the successive columns. This formula has been copied from here :

http://www.pcreview.co.uk/forums/running-total-array-t2628422.html

Now , we can check if the number of negative occurrences is less than or equal to 2 , in which case we use one calculation , else we use another calculation.

Narayan
 
Hi NARAYANK991, a beautiful solution which I don't quite understand, yet! It is the first use I have seen of MMULT so I am researching this a little so that I can explain it to my colleagues. But I wonder if you have time to offer an explanation of why you used "--" and "N"?

=IF(SUM($B$2:D2)>0,0,IF(SUM(--(MMULT(N(TRANSPOSE(COLUMN($B$2:D2))>=COLUMN($B$2:D2)),TRANSPOSE($B$2:D2))<0))<=2,SUM($B2:D2)*0.5,D2*0.5+C2*0.5))

As well as providing a solution, your answer has also taken my Excel knowledge in a new direction. Many thanks.

Stan
 
Hi Stan ,

Thank you , but I have just copied solutions from the Excel masters.

The use of the double negation ( -- ) and the N function has been explained in the following links :

http://www.k2e.com/tech-update/tips/143-using-two-minus-signs-in-excel
http://www.excelforum.com/excel-general/731580-double-dash-what-is-it.html

The N function is used here to convert boolean values of TRUE and FALSE to numeric values of 1 and 0 , so that they can be summed using the SUM function. There are other uses for this function in array formulae.

Narayan
 
@blueguitar23
Hi,

Inspired by @NARAYANK991 Sir formula, please find my solution. Put this array formula in B20 and copy across the column.
(Kindly, check the formula for all possible cases)

=IF(SUM($B$2:B2)>0,0,IF(SUM(IF(SUBTOTAL(9,OFFSET($B$2,,,,COLUMN($B$2:B2)-1))<0,1,0))<=2,0.5*SUM($B$2:B2),0.5*A2+0.5*B2))

As this is an array formula please enter with Ctrl+Shift+Enter. Thanks @NARAYANK991 Sir for a nice explanation which helped in writing this formula.



Regards!
 
Hi Misra, another beautiful formula which again stretches my understanding of Excel and arrays. Both formulas give me the outputs I need and I shall probably use the one that I can best explain to others.

Thanks for yours and Narayan's generous responses.

Stan
 
You must log in or register to reply here.
Back
Top