Formula required

Formula required

Didn't find your answer?

I am after an excel formula which will give tax payable at various income level.

I would like to be able to manually change personal allowances and tax bands (ie so that future net income can be predicted at various income levels and with various personal allowances and tax bands)

Much appreciate any help.

Replies (7)

Please login or register to join the discussion.

By George Attazder
13th Feb 2013 15:16

Try this...

=CRYSTALBALL(A1)

Thanks (1)
Replying to ireallyshouldknowthisbut:
avatar
By adam.arca
13th Feb 2013 16:26

Why?

2020 produce some good stuff but a lot of it's straightforward for those who prefer to rely on their own calcs or are cost conscious.

I agree with Ardeninian that breaking everything down into logical small steps should be within the scope of anyone with low to moderate excel skills.

If you haven't got those, then by all means fork out.

Thanks (0)
avatar
By ChrisMcEnaney
13th Feb 2013 16:53

Here you go... you will need to tweek

=SUMPRODUCT(--(A1>{8105;35000}), (A1-{8105;35000}), {0.2;0.2})

A1 cell contains Salary figure.

This formula doesn't work if your client is an additional rate tax payer.

Figures in { } are the different bands.

 

 

Thanks (0)
avatar
By ACDWebb
13th Feb 2013 17:49

It's going to have to be pretty basic source details

otherwise you are trying to pick up to many variables as to rates of tax relevant to source - Divs, Interest, PAYE, S/E (with the added faff of NIC) etc - with the associated problem of rate bands and BR extensions for gift aid & pension contributions.

You could look HERE for something to get you to the allowance reducer.

Tax and National Insurance contributions calculation version E4 is HMRC's full calculation for Software Developers. If you felt up to it you could copy it into Excel and then write all the formulae to follow it, but it'll be a bit of a fag.

Thanks (0)
avatar
By ACDWebb
13th Feb 2013 19:52

But ever one for a challenge

Lets assume

Income is earned but not self employed or partnership,no divs.just basic PA no other gross allowances (ie allowable interest)no Age or MCAno extension to BR band for gift aid or pensionsno other reliefs at other rates (EIS, VCT etc)

Lets also assume that you have a Cell named Income along with cells to hold allowances and rate bands (subject to the restrictions above) as follows:

Tax rates Range name

Tax year

2011/12

Non Savings Basic rate/Savings Basic rateBR_rate0.2Higher rate / Higher SavingsHR_rate0.4Additional Higher Rate AHR_Rate 0.5Rate bands  Higher rate thresholdHR_threshold 35000Higher_rate band HR_band 115000Additional rate threshold AHR _threshold  150000Allowances  Personal AllowanceP_A7475Tapered PA limit PA_taper_limit100,000Personal Allowance Income limit excessAA_excess0.5

Assuming all of that, the following should calculate your tax:

=SUM(MAX(0,MIN(MAX(0,Income-(P_A-MAX(0,MIN(P_A,INT((Income-PA_taper_limit)*AA_excess))))),HR_threshold))*BR_rate,(MAX(0,MIN(MAX(0,Income-(P_A-MAX(0,MIN(P_A,INT((Income-PA_taper_limit)*AA_excess))))),AHR__threshold)-HR_threshold))*HR_rate,MAX(0,MAX(0,Income-(P_A-MAX(0,MIN(P_A,INT((Income-PA_taper_limit)*AA_excess)))))-AHR__threshold)*AA_excess)

Or to break that down

 AB1Income?2AllowancesP_A-MAX(0,MIN(P_A,INT((B1-PA_taper_limit)*AA_excess)))3TaxableB1-B24  5

Charge @

 6BRMAX(0,MIN(B3,HR_threshold))7HAMAX(0,MIN(B3,AHR__threshold)-HR_threshold)8AHRMAX(0,B3-AHR__threshold)

and typing updated amounts for a different year into the named cells will be reflected in the result.

But of course if any of the assumptions differ you will have to think again

Thanks (0)