Calculating the Income Tax with Excel

Calculating the Income Tax
Accordigng to The Libyan Tax Law with Excel

ELMAWRID

Microsoft's Libyan Partner for Implementing
SharePoint
SQL Server/ Exchange
.NET Development

Important notice: A new 2010 income tax law was passed. The article below may no longer be applicable. Please consult your legal advisor

It is easy to calculate the Income Tax according to the 2004 Libyan Tax Law with the help of Microsoft Excel as follows:

Start Excel and create a blank spreadsheet.

In cell B4 type the Gross Salary
In cell C4 place the Tax Free Allowance ( this is defined as follows 100 LYD for Single, 150 for a Married , and 200 LYD for a Parent i.e. Married with one or more children)
In cell D4 insert this formula that subtracts the Free Tax Allowance from Gross Salary
= B4 -C4
Now here comes the magic formula that calculates the Income Tax
Place this formula in cell E4 as folllows:
=ROUND(MAX(MIN(D4;400)*8%;0)+MAX(MIN(D4-400;400)*10%;0)+MAX((D4-800)*15%;0);3)

This formula calculates the Income Tax according to the 2004 Libyan Tax Regulations as follows:

First Tax slot for the Initial 400 LYD is calculated at 8%
Second Tax Slot for the following 400 LYD is calculated at 10%
Third Tax Slot for amounts larger than 800LYD is calculated at 15%

Note that we avoided using compound conditional functions and simply used the Max & Min functions.
The formula could have been implemented in a different way by using traditional conditional logic operators and conditional functions...
But the beauty of this formula is that it is compact, short and concise.

To download the above Excel Spreadsheet click here

For the Arabic version of this article click here

Note: This method for calculatinon was designed and tested By Eng. Malek BADI