[Solved] 20% tax percent formula needed!

[Joannelouise]

Resolved now. Thanks guys ...

I am using a spreadsheet for my partners bookkeeping. In column f27 I have the amount before tax. I need a formula that will in the next box take off 20%. I have spent around two hours today googling this and the suggestions don't work. I'm off laptop now, but the popular one seemed to be *f27/20% . that formula does calculate something, but not what I want.
If column f27 has £100 in how do I calculate 20% as that is his tax owed. And then in the next column I will minus g27 from f27 to have the amount.

[FJCC]

To get 20% of F27 use the formula

Code: Select all

=F27*0.20

[keme]

Please note: this is not intended as a derogatory comment, only a friendly advice.

If you are uncertain about this kind of calculation, you should not offer to help with anyone's bookkeeping. The responsibility involved, coupled with your own insecurity, will become a constant burden to you. If you make a mistake (which may easily happen), your partner's financial situation may be seriously damaged. Such an event will also backfire on you.

Percentages occur in a lot of situations, like additional fees for working weekends/off hours, deduction for delivering late, taxes and tax refunds (e.g. VAT accounting), interest rates, etc. I do not know which (if any) of those apply to your partner's situation, but some (and perhaps some I haven't mentioned) will most likely be relevant. You need to understand what the figures mean and how they impact the cash flow. If you need to ask every time, you will be working a lot more than you need to.

If you must be the one doing the books, try to get help to help yourself. Many school teachers will tutor adults in their spare time. Contact the local educational administration or the nearest school and state your need.

We are all different in how well we relate to magnitudes, numbers and such. For some of us it is really tough while others juggle numbers rather easily. If you need extra tutoring, there is no need to feel ashamed about it. If you do anyway, remember that the shame will go away but the knowledge you acquire will not.

[Joannelouise]

I'm only doing it to keep a track of invoices issued and thought it would be helpful to calculate tax owed on each invoice we have raised. I'm only doing it for our records and all paperwork will be sent to an accountant. I've only ever used excel and could do percentages on there. The formula above is one I've tried today but for some treason won't work.

[Joannelouise]

I have already issued invoices so tax has already been calculated. For example an invoice for £920 minus 20% tax (£184) leaves £736, plus mileage at £40 the total owed is £776. That has all been worked on a calculator. I just wanted a formula to calculate it instead of inputting each one individually

[RusselB]

it would be helpful if you told us what results you are getting when you try FJCC's formula, rather than just saying it's not working.
One option that I can think of quickly, is that the entries in column F are not actually numbers, but text that appears as numbers.
With the spreadsheet open, press Ctrl+F8 (Cmd+F8 on Mac), then see what colour the entry changes to. If black (might appear to just stay black), then you have a text entry that appears to be a number, not a numeric entry.
If blue, then you have a numeric entry, in which case the formula should be working.
If green, then you'll have to figure out where the information for the formula is coming from, and determine if one or more of those entries are black, causing the same problem as if the actual entry was black.

[Lupp]

I have already issued invoices so tax has already been calculated. For example an invoice for £920 minus 20% tax (£184) leaves £736, ...

If the final (gross) amount was £920 and therein was contained 20% tax (value added tax?), that percentage once was added to a net amount we do no longer know and may want to calculate.

£920 must thus be 120% of the unknown amount which we can get back dividing £920 by 1.20 .
This leads to the result of £766.67 .

Test backwards:
20% of £766.67 = £766.67 * 0,20 = £153.33
£766.67 + £153.33 = £920.00

I think you should post £153.33 on the account for tax owed.

Now to the spreadsheet: Having the gross amount in B2 and the VAT rate of 20% (=0.20) in C1, we may put the formula =(B2 / (1 + C$1)) * C$1 in, say, C2 to get the tax contained there.
The C$1 instead of C1 will help to get the correct reference also if we fill the formula down for the calculations to come.

[Joannelouise]

Thanks. I wasn't clear. He has a day rate which I multiplied by 3 to get the £920. Then I minus tax and then add mileage.

[RoryOF]

If you add % to your figures, you turn them into text strings, which do not calculate. If you have individual cells or a column which will always contain a percentage, select those cells and format them as /Format /Cells : Numbers : Percent. Now you need only enter your percentage amount as a decimal (say 0.20) and it will display as 20% and calculate correctly.