I recently received a request to help with a salary packaging calculation. I thought I would share the solution and explain the technique to solve it. This is a case where we have a value we need to equal but don’t know the components that make it up. We are in effect working backwards to find the missing value.
The solution involves percentages, and some people struggle to get their head around percentages.
The problem
They needed to model different salary package amounts. They want to enter a total package amount and then see a breakdown into the salary, the superannuation (super) and an amount for a vehicle. The basic structure is shown below. The Vehicle amount is also an input.
The yellow cells are for input.
The missing cell is B6.
What Salary value will provide a total package of $250,000 in cell B9?
The Super $ amount cell B7 is 11.5% of the Salary in B6.
This means the Salary and the Super $ combined equal 111.5% of the “missing amount”.
The missing amount is $250,000 less $17,500 or $232,500.
We need the 100% part of the calculation – the salary. To find what 100% is (the salary) we divide the missing amount $232,500 by the total percentage 111.5%.
The formula won’t include any values, only the cell references for those values. This makes the formula flexible. The formula for cell B6 (Salary) is.
=(B2-B4)/(1+B3)
The brackets (parentheses) ensure the calculations are performed in the correct order. See image below.
The formula ensures that if you amend an input cell the calculation still works – see the result for a $300,000 package below.
Great formula Neale.
Thanks Sandeep
Great post! Salary structure is often confusing for employees. A reliable salary break up calculator really helps in understanding take-home pay, allowances, and deductions more clearly.
Glad you liked it.
Very helpful article! Understanding take-home pay can be confusing, and using a salary break up calculator really makes it easier to understand deductions and allowances clearly. Thanks for explaining it so simply.