[Solved] How to calculate the taxable amount?

[Frank1000]

Hi,
what is the formula to calculate the right taxable amount, so taxes are only imposed for positive profits ?

I have re-built this here in G-Sheet for easy access & edit.

Regards,
Frank

[robleyd]

Something like this for example in C9

Code: Select all

=if(C3 <0; 0; C3*B9)

[Frank1000]

Thx fr reply David,
as of now its still not calculating right, since the accumulated negative taxable amount of the past has not been amortized yet in Year 2 etc.

[robleyd]

Without seeing your actual spreadsheet, it is like taking a picture of your car to the mechanic and saying it isn't working properly.

[Forum] How to attach a document here Note maximum file size is 128K. If your file is larger, use a file sharing site such as Mediafire or Dropbox. The link also contains information on how to anonymise your document if it contains confidential information.

[keme]

Assuming conditions for taxable earnings:
- Accumulated cash flow must be positive (losses are carried over to following year)
- Cash flow for taxation year must be positive (profits do not carry over, only taxed the year they are earned)

Code: Select all

=IF(AND(C3>0;C4>0);$B9*MIN(C$3;C$4);0)

If the losses are not carried over to calculation, the formula is a little simpler. This matches the calculation in the sample sheet:

Code: Select all

=IF(AND(C$3>0;C$4>0);$B9*C$3;0)

This yields a seemingly unfair outcome: For year 3 you will pay 180 in taxes from accumulated earnings of 29 over 3 years. IOW, taxes exceed accumulated profit.

[Frank1000]

Hey Keme, thx again.
the calcs seem yet not taking into account previous losses correctly.
Pls see the G-Sheet link i've put the equations in the cells of row 7.

G-Sheet

Hey David, above its a public G-Sheet u can visit, pls make a tab copy inside the sheet for yourself if you like to test, thx.

[keme]

Your "manual formula" in cell I7 suggest that losses from the two previous years may be deducted. If you want a complete formula, taking all exceptions into account, you need to give us the rules. Which previous losses may be counted? Are there other relevant rules which you have not given us?

I added a tab "Deduct3Yloss" which yields results close to your manually determined taxable amounts. There still seems to be a few rules I do not know...

Helpers here are from various countries around the globe, sometimes with vastly different legislation. What may seem obvious and trivial to you, may be counter-intuitive to a person from a different cultural background.

[Frank1000]

Actually im kinda struggling with identifying what i clearly can calculate without difficulties :O

Basically historic losses are deductible from current years profits.
Current years losses increase historic losses.

I also wrote a comment per year in the respective column.
Probably there needs to be one more layer in terms of what historic deductible losses exist this year ?

[Frank1000]

hey Keme, u still chg on this issue ? Would be great to get some of your knowledge in this

[keme]

Added a row for intermediate calculation to determine when historical loss deductions are "depleted". See the sheet I added.

Edit: For reference, in case the linked spreadsheet is lost: Inserted a row of formulas for "historical loss carryover", essentially a running sum of <result current year> + <accumulated loss previous year>. Used the MIN() function to limit the calculation, threshold=0. (When running sum is positive, there is no loss left to carry over). C6: =MIN(B6+C3;0) copied across the row

Still does not agree perfectly with your manual calculations, but I suspect that may have been an oversight on your part. The accumulated total of 29 would be taxable in year 3, and should not be taxable in year 4. Or is there something else I am missing?

[Frank1000]

ok thank you. Your calc is perfect, my Year 4 manual was in fact wrong.

thanks a lot Keme !