Issue - January 2014
A rather unknown, yet highly valuable function In Excel, Solver is capable of solving tedious calculations that take hours or even days when it is performed manually. One such example involves the calculation of corporate and personal income taxes and minimizing the amount of tax payable through the various tax incentives schemes available. These tax incentives schemes, more often than not, are interlinked to each other and can never realize the maximum value (which could be in thousands of dollars) by using a calculator. In this example, I am going to show you how a small Singapore company, owned by a husband and wife team, can reduce the amount of taxes paid purely by determining the amount of director fees and dividend declared for the year.
Assuming that the company owned by this husband and wife team generated a profit of $300K and they did not pay themselves a salary. Purely by using the corporate tax incentives declared by the government, the company would have to pay $25K. The effective tax rate is 8.4%. The husband and wife can keep the rest of the profit without paying any more taxes.
In the other extreme case, they can declare a director's fees of $150K each. This effectively reduces the profit to zero and they don't have to pay corporate taxes at all. But on a personal basis, they would have to declare personal income tax. Assuming a case where there is no personal relief, they would have to pay income tax of $24.9K. The effective tax rate is 8.3%.
This tax amount is computed without personal relief. If we factor in the personal relief of $7.5K for each, the tax payable drops to $22.7K, a savings of $2.3K.
The computation assumes that the couple does not have any children. If they have one child, the rebate for the wife is pegged at 15% of her earned income. Based on the same split of $150K each for director's fees, the wife would be getting an additional relief of $26.5K. That generates into a savings of $6K. The effective tax is 6.3%.
It seems to make sense to declare all the profit made to the wife as she is able to get a relief of 15% of the director's fees. But this is not so as there is a cap of $50,000 per child. Furthermore, the tax bracket would also have increased, causing the tax to be paid to increase to more than the base case (taxed based on corporate rate).
As such, there is a need to strike a balance on how much director's fees you should declare for the husband and wife and the amount of profit you should record for the company at the right amount. Doing this manually requires a lot of trial and error and manages multiple constraints such as the relief per child, the total amount income for husband and wife combined with the company profit cannot exceed $300K, etc.
With Excel Solver, you can add in all the constraints and have the function determine the right income level for husband and wife and the optimum profit level for the company that pay the minimum tax.
Using Solver, I am able to derive quickly that the amount of Director's fees paid to the husband is about $95K, wife $155K and the company should declare a profit of $50K. This essentially means the couple needs to pay only $16K in taxes, a substantial savings of $9K. With its ability to compute potential amounts tax savings for many of you, Excel’s Solver is certainly a function worth taking a good look.
Click on this link to post your question.
With the newly introduced PIC bonus, you are now paid (not paying) 60% of the course fees for sending your staff to our courses. Check out now in this IRAS PIC Link.
Hidden Secrets in Data Analysis with Excel 2007/2010 - 27 & 28 Mar 2014
Interactive Dashboard Reporting with Excel 2007/2010 - 10 & 18 Mar 2014
Gaining Stunning Business Insights with Excel Power Pivot - 27 & 28 Feb 2013
Learning the Magic of Macros 2007 (Excel VBA) - 24 & 25 Apr 2014
Breakthrough Performance for HR in Excel 2007/2010 - 13 & 14 Feb 2014
Dynamic Real-Time Forecasting with Excel 2007/2010 - 6 & 7 Mar 2014
For the details of the courses, please refer to our Excel courses page.
If you received this newsletter from a friend and would like to be included in our mailing list, please go to our Excel Today's page.
If you have a facebook account, you can like us at facebook or linkedln. From facebook or LinkedIn, you will be notified on mini cases and ideas how we use Excel on a day to day basis. These tips may not be published in our newsletter.
everydayExcel Business Lab Pte Ltd
Author of "Hidden Secrets of Data Analysis with Excel" and
"Excel Secrets for Highly Effective Marketers"
Received this newsletter from a friend? You can request to be included in our mailing list by signing up at our Excel Today's page. If you have friends whom you think might be interested in this newsletter, feel free to send it to them.