PAYE and NI formulas in Excel

"Marcus Fox" wrote in message news:6jQFe.11606$ snipped-for-privacy@newsfe2-win.ntli.net...

Think it is called "damn, gotta buy Sage or some other package.

I think you will find EVEN IF you get the XL sheet right this year the changes coming (nope, I don't know either) will cost you more to work out than paying SAGE or whoever...

Rab

PAYE works on a year-to-date basis, so you really need to store the YTD gross and YTD tax on your spreadsheet.

Tax is then worked out by adding your week's gross to the YTD gross, working out what tax should be due on this given the proportion of the year gone (eg week 24 would be 24/52 or 24/53), then taking the previous YTD tax off this.

But you can cheat provided that your pay doesn't vary enough to move you into a different tax band. So if your weekly pay is always between 135 and 717, you can use:

tax = GROSS WEEK*22% - 25.53

The 25.53 is worked out using (4895*.22 + 2090*.12)/52

To be accurate substitute your tax code with a 9 on the end for the 4895 above.

you would use an IF construction, something along the lines

tax = if (gross

Bitstring , from the wonderful person Rab C Nesbitt said

Actually it isn't at all hard to do it in Excel - I've done it that way for about 20 years (well, it started in lotus 1-2-3) but =NOT= in one cell.

It takes a whole worksheet with tax thresholds, rates, exemptions, and suchlike (especially if you have investment income, may be over 65, etc. etc.) to get the right answer for several years.

You can do it with a Visual Basic user written function - but all that does is hide the 'worksheet full of tables, formulae etc.' away where you can't see it.

You can do it as a formula using lots of IF statements, but you would be much better writing it as a Visual Basic function.

Well, at one point I was earning 4X per week and paying Y tax, but suddenly I was earning 3X and then I was getting small amounts of tax refunded each week, so they obviously calculate weekly tax based on average weekly earnings, and if these change up or down, then they either take more tax to cover it or pay some back.

Marcus

They calculate the cumulative tax due on your cumulative year to date earnings, and subtract from this the tax already paid.

Assuming you put the current week number in cell A1, and your cumulative gross pay in A4, the following formula will calculate your current cumulative basic rate tax. You'd need to modify it slightly for earnings over 32400 to take account of the extra 18% higher rate tax.

It's then a simple matter to deduct the result of last week's cumulative tax from this week's to deduce the current week's tax.

You could improve on this formula by holding the variables for £2090, and £4895 in separate controlling cells, and changing the formula to reference these cells. That way when rates / bands change you only need modify these controlling cells.

90*0.1+(A4-4895*A1/52-2090)*0.22

HTH

Rgds

__ Richard Buttrey Grappenhall, Cheshire, UK __________________________

"Richard Buttrey" wrote in message news: snipped-for-privacy@4ax.com...

That's exactly what I'm looking for, thanks. I take it by "cumulative gross pay in A4" you mean "Gross Pay Year To Date", which increases every week?

Marcus

"Marcus Fox" wrote in message news:MmTFe.60$ snipped-for-privacy@newsfe1-win.ntli.net...

Hmmm, got in too quick with that, just tried to apply it, and it's trying to give me a couple of hundred back tax for the first week.

Marcus

Yes I did mean year to date gross pay.

The above formula assumes that the 2090 10% tax band is all taken immediately. Thinking a bit more about this, perhaps the tax tables are designed to apportion this element too. (can anyone else comment on this?). In which case modify the formula to:

90*0.1*A1/52+(A4-(4895+2090)*A1/52)*0.22

Let me know if this correlates with your actual results.

Rgds

__ Richard Buttrey Grappenhall, Cheshire, UK __________________________

Why are you not taking into account that the £2,090 band is yearly? For each week you should be dividing that by 52 as well.

Thanks,

That just answers a point I'd queried earlier. My original had (incorrectly) assumed that the 2090 was taken immediately rather than apportioned through the year.

Rgds

__ Richard Buttrey Grappenhall, Cheshire, UK __________________________

"Richard Buttrey" wrote in message news: snipped-for-privacy@4ax.com...

Fitting that in with the actual formula, for week one. if I do 37.5 hours per week I pay 19.84 tax which is fairly close to the 19.82 I was charged. but if I change the hours worked to 5, it tries to repay me 19.48 (a figure of -19.48).

Marcus

Ah, OK. Arithmetically that is correct. What it means is that on this relatively small amount of pay in week 1(circa £27.5), your allowances exceed your gross pay.

You can refine the formula to ignore the situation where Gordon Brown is notionally in debt to you, as follows:

=MAX(0,2090*0.1*A1/52+(A4-4895*A1/52-2090*A1/52)*0.22)

This will return Nil tax in the circumstances you describe.

Regards,

__ Richard Buttrey Grappenhall, Cheshire, UK __________________________

that's because there are no if statements so spot if your pay is less than the personal allowance or less than the 10% band. The single formula you were given applies only to a single set of circumstances - earning within the standard rate band.

has a free trial. Phil

"Richard Buttrey" wrote

That might now work correctly for annual-equivalent earnings at/below

4895pa, or within [6985-37295] (in standard-rate band) -- but it's still wrong for earnings *between* 4985 & 6985 !!!

What's the rule for earnings between 4985 & 6985? So I can modify the formula and test it, what would tax be on say £5500?

Rgds

__ Richard Buttrey Grappenhall, Cheshire, UK __________________________

You should already know the answer to this.

5500-4895 = 605. This is less than 2090, so tax is due at 10%: £60.50.

Using IF constructs is tidier, but since the tax curve is convex (or concave, depending on how you look at it, but significantly not both), you can still get away with using MAX instead. Something like the following might do the trick.

=MAX(0,MAX(0.1*(A4*52/A1-4895),209+0.22*(A4*52/A1-6985)))*A1/52

E&OE, of course, since I don't normally do spreadsheet-speak.