From: Richard Buttrey 
Newsgroups: uk.finance
Subject: Re: PAYE and NI formulas in Excel
Date: Wed, 27 Jul 2005 23:52:49 +0100

On Wed, 27 Jul 2005 23:42:23 +0100, Peter Saxton
 wrote:

>On Wed, 27 Jul 2005 22:07:15 +0100, Richard Buttrey
> wrote:
>
>>On Wed, 27 Jul 2005 18:24:02 GMT, "Marcus Fox"
>> wrote:
>>
>>>Have written a speadsheet to track my weekly wages in Excel, but having
>>>problems with formulas for deductions. NI is no problem, as it's just
>>>=(GROSS WEEK-£94)*11%
>>>
>>>But PAYE is more complicated. Thinking something along the lines of £GROSS
>>>WEEK x 52 or 53? - £4895*22 and then /52 again but don't know how to write
>>>in the lower rate of 10% for the next £2090. But as I get paid different
>>>amounts sometimes each week, this would need to be based on an average
>>>figure for GROSS WEEK. Is there a way to write a formula like this in one
>>>cell? What's the formula the HMRC use to calculate deduction/credit of PAYE
>>>each week?
>>>
>>>If anyone can figure this out, would be grateful.
>>>
>>>Marcus
>>
>>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.
>>
>>
>>=2090*0.1+(A4-4895*A1/52-2090)*0.22
>>
>>HTH
>>
>>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
__________________________