The DWP have changed the format of their (most useful) Tax Benefit Model Tables. Instead of just showing the results, they now publish the spreadsheet so that you can see the underlying formula (and bodge them to your heart's content).
My favourite is the formula for calculating Housing Benefit:
=IF(Rent30-HBtaper*MAX(MAX(F37+G37+H37-IF(Adults=1,IF(chn=0, IF(WTCamt30>0, Rates!$F$58+Rates!$F$61, Rates!$F$58), IF(WTCamt30>0, Rates!$F$60+Rates!$F$61, Rates!$F$60)), IF(WTCamt30>0, Rates!$F$59+Rates!$F$61, Rates!$F$59))-IF(Chcost>0, IF(chn>0,MIN(Chcost, IF(chn=1,175,300)),0),0),0)-HBapp30,0)
But the most important figure is the 'replacement ratio':
Replacement Ratios are the net income after housing costs if out of work and reliant upon benefits as a percentage of net income after housing costs when the head of the family is in full-time work. A replacement ratio of less than 100 per cent indicates that the family has a higher net income when the head of the family is in work than if unemployed and in receipt of benefits.
The replacement ratio for a family with Dad, Mum and two kids (assuming that one parent is working at least 30 hours a week, i.e. using the 30-hour Table) starts at 79% and doesn't drop to 50% until the working parent is earning £938 a week (nearly double the UK average wage).
At that level of gross wages, the family's net income (£482) is double what it would be were they just on benefits (£241), so it still woefully understates the average marginal tax/benefit withdrawal rate - even though the working parent earns £938 a week gross, the family is only £241 better off, which is a marginal tax/benefit withdrawal rate of 74%.
And remember that this rate does not include Employer's National Insurance or VAT (which add 'about' twenty per cent to the true tax rate), so when I generalise and say that people in the UK have an average tax rate of 50%, it's just as likely that I am understating as overstating the real horror of all this.
Thursday, 30 December 2010
Awesome spreadsheet
Subscribe to:
Post Comments (Atom)
10 comments:
As far as I can tell, this means most families in the lower income deciles would actually be significantly better off with only one working parent & a stay at home mum/dad?
O/T, old son, but ... ahem. ;)
Kerrist!
Seriously, can you graph that? Pictures are better than numbers(!)
SW, no actually, as regards couples, the tax/welfare system works as a prisoner's dilemma.
If both parents are out of work, there is little or no advantage on one of them starting work (and a big disadvantage if an out of work single mum gets hitched to somebody in a job), they are only really better off if both work.
Conversely, if one parent is already working (and committed to it), then the other partner has little to lose from getting a job as well.
This is why there are lots of two-earner households and lots of workless households, but, statistically speaking, relatively few two-parent/single earner households.
DP, I'll fire up the camera after dinner.
L, download the spreadsheet, they have charts to illustrate it all on the last two tabs. The net income line is so close to horizontal as makes no difference.
New Years resolution - get better at Excel!
Did you see the news from Bristol? Looks like the landlord did it!
One thing is for sure, the other lot would not have published the formula for all to see.
Credit where credit is due Mark?
SL, best of luck with that.
S, the 'other lot' at least had the decency to publish the tables, it wasn't too terribly difficult to reverse engineer them on a rough and ready basis.
It's the current lot who are going to stop publishing them (follow the link and read!) and who also have removed Table 2 from the back, which tells you how much people get when they are out of work (which is just as important in understanding all this).
So all in all, we were better served by the other lot.
Your extract says 2 things to me about their use of Excel.
1. All those $$ cell references are not very flexible - it must have been a nightmare to develop - and any changes would be hard to implement.
2. Once you get formulae that complicated, why not use the internal language (VBA) with the ability to use descriptive cellnames and array subscripting?
The whole thing looks rather amateurish to me. It's shoddy work.
GB, true.
When I tried reverse engineering it a couple of years ago, I used the 'hidden columns' method instead, i.e. I did the calculations one step at a time. Which is a bit amateurish as well, but at least you can see the results for all the interim calculations as you go along.
Post a Comment