Determining gross income

[Kenaida]

Hi all, my brain is melting! I need to work out the gross income from a pension payment. Here's how net is generated. 25% of the gross withdrawal is tax free. The next 75% is taxed at your marginal rate of tax. So if the rate of income tax is, say, 20% up to £50k and then 40% after that. If someone had a gross salary of £40k and then received £20k net pension
, What was the gross pension withdrawal? I have tried many things to work out a formulaic relationship between the various parts but am completely stuck. Any help/clues would be greatly appreciated!

 

[Kenaida]

Sorry for wasting time, I just worked it out by very long hand! It's this:
Gross Pension Withdrawal : £100,000
Of which 25% is tax free cash : £25,000

TaxedIncome = £45k (If the entire £75k was taxed at 40%)
NetIncome = TFC (£25k) + TaxedIncome (£45k)
NetToGrossIncomeFactor = (70% of the gross withdrawal)

Easy...

But when some is taxed at 20% it was unclear how to reverse the calculation.

Gross Pension Withdrawal : £100,000
Of which 25% is tax free cash : £25,000

If the first £50k was taxed at 20% it would leave : £40k
The next £25k at 40% would leave : £15k
Add back in the TFC (£25k) and you get : £80k

So to reverse this you have to run this excel formula

=IF(Personal_Allowance_Remaining>0,IF(NetIncome*0.75<Personal_Allowance_Remaining,NetIncome,IF((NetIncome-(Personal_Allowance_Remaining/0.75))/0.85*0.75<BasicRateTaxBandRemaining,(NetIncome-(Personal_Allowance_Remaining*0.2))/0.85,(NetIncome-(B12*0.2)-(Personal_Allowance_Remaining*0.4))/NetToGrossIncomeFactor)))


I hope this helps someone, but feel free to ask any questions if you need to use the formula. I needed it for a cashflow model to maintain a steady stream of inflation linked income where the pension withdrawals had the tax free cash component and had to be accounted for. Otherwise the net income after tax would be different.

 

[Kenaida]

Slight change to formula:

=IF(Personal_Allowance_Remaining>0,NetIncome,IF((NetIncome-(Personal_Allowance_Remaining/0.75))/0.85*0.75<BasicRateTaxBandRemaining,(NetIncome-(Personal_Allowance_Remaining*0.2))/0.85,(NetIncome-(B12*0.2)-(Personal_Allowance_Remaining*0.4))/NetToGrossIncomeFactor))