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.