< Go Back

Tax rate calculation with fixed base

Tax rate calculation with the fixed base

Many times, we have to calculate the tax amount of a client. It may seem an easy task but when we are given a set of both variable and fixed values, the operation becomes a little complex. In such a situation, we can make use of simple formulas in excel to calculate the tax amount. We can also use IF clause in EXCEL to calculate tax amount while checking for some conditions.     

IF clause in EXCEL

This in-built function of excel returns one value of any condition. In this, there are two possible answers, True or False. This function is categorized as a logical function in EXCEL. It can also be used with other formulas so as to solve complex statements.

Generic Formula

=IF(amt < limit, amt * tax_rate, (amt-limit) * tax_rate + base_tax)

Illustration

Given below is an example which illustrates how to set up a simple formula using IF function.

In the example below, it is shown, how to calculate the tax amount with both variable and fixed components.

Tax rules

If an amount is less than 2000, the tax is amount * 15%. If the amount is greater than or equal to 2000, the tax is 400 + 15% of amount minus 2000. We convert this condition into an excel formula as shown:

The example makes use of the IF clause and checks whether the amount is less than the base limit. If the amount is less than the base limit, the first operation just after the ‘,’ is executed else the 2nd operation is executed.

In the first case, i.e. in C7 we use the formula:

=IF(B7<F8,B7*F6/100,((B7-F8)*F6+F7)/100)

Since 650 is less than 2000 so the 1st operation is executed.

Similarly, this function can be applied to calculate the tax rate of other amounts as shown.

 We hope that the tutorial was helpful to you and you won’t be facing any problem regarding the above calculation in the future.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar