> 2\p
Barry Pearson BCurrent scheme
Phasing inReformed scheme
Benefit rates(_1_child:+_2_children:5_2F_children_in_first:6_2F_children_in_second:2_2nd_family_option:+_3_children:+_4_children:+_5_children:0_ChB_first_child:8_ChB_subsequent_children:2_children_in_first:3_children_in_second:;_CS_Age_youngest_qualifying:5_CS_Lump_sum_children:9_CS_Minimum_in_most_cases:4_CS_NRP_ChB_received:5_CS_NRP_Children_0_11:6_CS_NRP_Children_11_16:6_CS_NRP_Children_16_18:3_CS_NRP_Council_tax:/_CS_NRP_Housing:<_CS_NRP_maintenance_received:2_CS_NRP_Net_income::_CS_NRP_partner_net_income:<_CS_NRP_partner_pension_paid:6_CS_NRP_partner_status: 7_CS_NRP_Pension_payment:9_CS_NRP_travel_crow_miles:2_CS_Number_of_NRPs:*
_CS_Period:5_CS_Period_multiplier:/_CS_PWC_Housing:2_CS_PWC_Net_income:7_CS_PWC_Pension_payment:6_CS_Qual_Children_0_11: 7_CS_Qual_Children_11_16:
7_CS_Qual_Children_16_18:;_CS_Qual_children_allowance::_CS_Qual_disabled_children:
=_CS_Total_qualifying_children:9_current_scheme_liability:._Days_per_year:3_difference_3_years:(A!_Equal_sharing_half_ChB_reduction:7_Feed_children_in_first:8_Feed_children_in_second:4_Feed_NRP_Net_income:G'_Fraction_of_pension_taken_into_account:3_Income_threshold_1:3_Income_threshold_2:3_Income_threshold_3:4_Intact_target_spend<+/_Intermediate_1:
/_Intermediate_2:/_Intermediate_3:/_Intermediate_4:/_Intermediate_5:3_IS_Adult_allowance::_IS_Adult_couple_allowance:@ _IS_Blind_disabled_child_premium:._IS_Child_0_11:/_IS_Child_11_16:/_IS_Child_16_18:2_IS_Family_Premium:6_IS_LP_16_17_allowance: 3_IS_LP_18_allowance:8_IS_LP_Special_allowance:
:_IS_Single_16_17_allowance::_IS_Single_18_24_allowance:
,_maintenance:$?_maintenance_spend_1st_children:.2_Minimum_liability:2_NRP_capped_income:0_NRP_care_nights:/_NRP_care_spend:%2_NRP_Child_Benefit:5_NRP_childcare_credit:4_NRP_childcare_spend:1_NRP_gross_income:/_NRP_net_income:0_NRP_net_income2:0_NRP_net_income3:3_NRP_other_benefits:21_NRP_target_spend:E5_NRP_total_net_income:"=_NRP_total_spend_2nd_children:&3_Proposed_liability:2_PWC_Child_Benefit:5_PWC_childcare_credit:4_PWC_childcare_spend:*
_PWC_IS_FC:"/_PWC_net_income:3_PWC_other_benefits:*7_PWC_spend_1st_children:01_PWC_target_spend::5_PWC_total_net_income:"=_PWC_total_spend_1st_children:&:_reformed_scheme_liability:9_total_spend_1st_children:+<_total_spend_intact_children</-
_transition_1:-
_transition_2:-
_transition_3:4_WFTC_30_Hour_credit:>_WFTC_Age_of_relevant_children:>_WFTC_Analysis_Child_Allowance:>_WFTC_Analysis_Hours_Allowance:5_WFTC_Basic_allowance:0_WFTC_Child_0_11: 1_WFTC_Child_11_16:!1_WFTC_Child_16_18:";_WFTC_Hours_worked_per_week:8_WP_half_ChB_first_child:@ _WP_half_ChB_subsequent_children:$sch3:= +
8X@"1Arial1Arial1Arial1Arial1Arial1Arial1Arial1QTahoma1QTahoma1Arial1
Arial1
Arial1Arial1Arial1Tahoma1Arial1Arial1Arial1Arial1$Arial""#,##0;\-""#,##0""#,##0;[Red]\-""#,##0""#,##0.00;\-""#,##0.00!""#,##0.00;[Red]\-""#,##0.003*0_-""* #,##0_-;\-""* #,##0_-;_-""* "-"_-;_-@_-*)'_-* #,##0_-;\-* #,##0_-;_-* "-"_-;_-@_-;,8_-""* #,##0.00_-;\-""* #,##0.00_-;_-""* "-"??_-;_-@_-2+/_-* #,##0.00_-;\-* #,##0.00_-;_-* "-"??_-;_-@_-""#,##0.000.0%""#,##0
""#,##0.0"Yes";"Yes";"No""True";"True";"False""On";"On";"Off"mmmm\ d\,\ yyyyd\-mmm\-yyyy + ) , * @) @ D @+ d `+ @- D- @/ D/
#P+ H+ D+ D/ @( D( H @ "P "T "T "T D H/ @, D, !X7 @7 "T7
"T7 "\7 "P7 "T7 D3 @3
" # "X) #P) #X) "\) "X+ "p AI@ d AI@ d AI@ A @ AI@ # ` AI@ ` AI@ AA AI@ d AI@ # AI@ "
" AI@ AI@
AI@ " #P H
! "\) !X/ !X, D, "X- !P- D- !P ` d D @ "x+ #P #
" "X+ d L D+ \ \) H7 "X h+ "P ` h "p D+ D @ L+ H+ `
" ! ! #X+ "\) Q Q
L+ L+ #\+ D/ L/ L L L+ L+ L+ D+ D/ L/ L/ Q Q Q "T ( L Followed Hyperlink Hyperlink83ffff̙3f3fff3f3f33333f33333.IntroductionFCurrent scheme
Phasing in,Reformed scheme_
Benefit rates
2166Y@qF
dMbP?_*+%,MFHP DeskJet 950C Series@ dvConvertW|9W|Bwra}INra
1; ;3CSA Current Formula & Reformed Formula Spreadsheet.>Author:
Barry Pearson&http://www.barrypearson.co.uk/>Version:24 (12nd May 2004)This spreadsheet is provided in good faith, but in no way can I be held responsible for any consequences of using it. Remember - this spreadsheet is free, and you tend to get what you pay for! >Guide:= 5Please read these notes before using the spreadsheet.For most cases, all you need to do is fill in the "Current scheme" worksheet. This will give the result for both the current scheme and the reformed scheme. (Phasing into the reformed scheme is given on the "Phasing in" worksheet).Only if you want to understand the consequences of shared care do you need to use the "Reformed scheme" worksheet. (The spreadsheet doesn't cater for shared care in the current scheme).%Net income & pension paymentsOne of the most common problems people have using this spreadsheet is inputting their net income. People sometimes don't cater for pension payments in the way this spreadsheet expects. Do what it says below:bZTo identify your net income for filling in this spreadsheet: start with your gross income.D<Then subtract all of your income tax and National Insurance.Then also subtract ALL of the payments you make to pension schemes, whether to stakeholder pensions, company pensions, private pensions, or AVCs.wThen use THIS number as the "net income" number. (DON'T use a number which still has 50% of the pension payment in it).If you are studying the effect of paying more into a pension scheme, remember that as pension payments increase, net income decreases.Shared careQIThe spreadsheet for the current scheme still doesn't handle shared care. The spreadsheet for the reformed scheme handles it properly. This is probably the only reason you might need to use the "Reformed scheme" worksheet.)!Timetable for the reformed scheme, |?,*2S)
fHU!"#%'(*+,-./0B!:The new scheme has started for new cases on 3rd March 2003"#Then existing cases will be converted to the reformed scheme when there is confidence that the CSA and its computers can handle this large number of people in the reformed scheme. All existing cases will be converted within weeks of one-another. This date%Where there is a significant difference (up or down) between the current & reformed scheme amounts, the change will be phased in gradually, over up to 5 years. ''This spreadsheet now estimates the phasing-in, year by year. When you have filled in the "Current scheme" worksheet, have a look at the "Phasing in" worksheet. ((This worksheet also reveals the advantage to the PWC in causing a premature transition to the new scheme. See the "Phasing-in" worksheet.*>
Copyright:&+Copyright Barry Pearson 20045,-mailto:spreadsheet@childsupportanalysis.co.uk-For more details about all aspects of child support in the UK, see the web site: "Child Support Analysis for the 21st Century":..&http://www.childsupportanalysis.co.uk/$/More about Barry Pearson at:&0http://www.barrypearson.co.uk/F
*92(= +
8X>
"
2hONwJ
dMbP?_*+%,MFHP DeskJet 950C Seriesrrypearso@ dvConverte web site: "Child Supp.(D@@@C*7@ CTotal net income
cD( Premature transition difference:
%Xe tC+h8p!Type in amounts per month0(Other parent of NRP's children is absent
$DReformed scheme calculation:%@CWOJy5@4D@@@C*7@@@>m۶mKp@(D@@@C*7@ C
Exempt income
c33333K@
D(At March 2006)
qG?C1@@@C/1@@0@@@C*a7@@+@C*7@@@"B Type in amounts per year?7Other parent of NRP's children does not provide supportDD?s7Fill in as much as possible, especially the white cellsD CAssessible income
c
D&q~
G?G?Other parent of NRP's children provides support or isn't sayingFNRPF
QualifyingFPWC
CProtected income
c33333S]@
D]FinfoFchildrenFinfo
rNRPrPWC5%(Net income (excludes tax & NI & pension)'~
H~
I-%(Must be a sensible amount; can be 0)
.+&Allowances, benefits, & basic numbers:
,,,,,,,,%Pension payment (total)~
H~
I-%(Must be a sensible amount; can be 0)
,,,,,,,,,!%Rent or mortgage payment ~
H~
I-%(Must be a sensible amount; can be 0)
#JChild aged 10 years or less
JJ%K(\"E@ C=A%L(\"E@ C=A
JM!NTotal qualifying childrenUG+?C#4@C$ 5@@C%0|5@ / %'Qualifying children up to Sept after 11~
P! (Must be sensible number)
! JChild aged 11 to 15 years
JJ% K(\"E@
C>;% L(\"E@
C>;
JJ `Other NRP's children~
G?
9
%1Qualifying children from then up to Sept after 16~
P!
(Must be sensible number)
#
JChild aged 16 years or more
JJ%
K(\"E@C?B%
L(\"E@C?B
JJJ,
6%.Qualifying children from then to day before 19~
P!(Must be sensible number)
JSingle adult allowance
JJ%K33333K@C:P@%L33333K@C:P@JJJ,
%)%!Age of youngest qualifying child ~
P!(Must be sensible number)
J100 % carers allowance
JJJL33333K@,D"J (Youngest under 11 years)JJ,
7
%/Number of disabled or blind qualifying children~
O!
(Must be sensible number)
J75% carers allowance
JJJ$
LfffffD@D?
J (Youngest 11 to 13)
JJ,
7%/Number of NRPs of this PWC (including this one)~
O?-%(Must be sensible number, at least 1)
J50% carers allowance
JJJ$L33333;@
D?J (Youngest 14 and 15)JJ,
%
J0% carers allowance
JJJ~
L$J (Youngest 16 years or more)JJ,
$%Children up to Sept after 11~
O-%(Must be a sensible amount; can be 0)
JFamily premium
JJJ%Lfffff/@*C@CJJJ,
,%$Children from 11 up to Sept after 16~
O-%(Must be a sensible amount; can be 0)
!JChild benefit first child
JJJ%L0@C J (A deduction)JJ,
)%!Children from 16 to day before 19~
O-%(Must be a sensible amount; can be 0)
"JChild benefit second child
JJJ%L&@-C
J (A deduction)JJ,
&%NRP's new partner's net income~
I-%(Must be a sensible amount; can be 0)
JTotal net income
JJL#lC1@C@C-1@C@ @@?@@@vQ#`C1@C!2@C-1@C"@@?@1J) (Net income as above plus half pension) JJ,
#%NRP's new partner's pension~
I-%(Must be a sensible amount; can be 0)
!JAdditional element factor
JJ}R?6gD@@@333333?AD@@@?@?"BS1J) (Used in maint requirement calculation) JJ,
%Child Benefit received~
I-%(Must be a sensible amount; can be 0)
TT
,%$Maintenance or other income received~
I-%(Must be a sensible amount; can be 0)
VExempt income:
,,,,,,,,
%Council tax paid~
I-%(Must be a sensible amount; can be 0)
V,,,,,,,,
3%+Travel to work "crow" miles if at least 150~
OG?(Only "as the crow flies" amounts, at least 150 miles per week)
,,,,,,,,,
/%'Lump sum settlement for children amount~
U7/(MUST be provably settlements for the children)
JPersonal allowance
JJL33333K@DL33333K@DJJJ,
JChild allowance
JJLC@D@C"@D @@@CH@D
@@Ci<
@@@@B@@@LiC#4@D@C$" 5@D @@@C%H|5@D
@@@JJJ,
J
Housing costs
JJwLaC1@C0.@C-1@C@@@@BwLaC1@C |2@C-1@C!@2@@@B)J! (Max of half net income allowed)JJ,
JTravel allowance
JJ7W!C 0@?@XJJJ,
Errors & omissions:
$,Lum sum settlement allowance
,,IC(@P@@<zC/(@ N@@(HCY(@'@@@@"@"B,,JJ,
,,,,,,JJ,
~
? Nothing about IMAs, etc.
YParents exempt income
YY!Z33333K@$%!Z33333K@$%!J (Total of above amounts)JJ,
D'l6#Y;3(5)'
(\J !"#$%&'()*+,-./0123456789:;<=>?
!~
!@/!'Minimum assessment (5.60) not handled.!
!VAssessible income:!
,,,,,,J,
!"
",,,,,,,,,
"#~
#@#No shared-care formula.#
#JTotal net income
#
JJ#[D#LD#JJJ,
#$
$M
Exempt income
$
YY$L33333K@&D$L33333K@&D$JJJ,
$%
%MYYSSJJJ,
%&
!&YParents assessable income
&
YY2&\4D#@D$@@B2&\3D#@D$@@B!&J (Total net minus exempt)&JJ,
&'
'TT
'(
"(VBasic element calculation:(
,,,,,JJ,
()
),,,,,,JJ,
)*
*JFamily premium*
JJJ2*Lfffff/@
D@C
0@*JJ,,
*+
+JChild allowance +
JJJ+LD+SJ,,
+,
,JCarers allowance,
JJJ,L33333K@C
P@@ @$}C
0P@@ @$
JC
[P@@ @$@$@"@"B@C0@,SJJ,
,-
-JChild benefit -
YYJL-L0@/6DD@@@D@@@B@-S (A deduction)-JJ,
-.
).J!Blind & disabled children premium.
YYJ=.L'C'p6@C<A@.SJJ,
./
!/YMaintenance basic element/
JJJy/L5cD@@@C@D*@D+@@D,@@D-@@D.@B!/^ (Total of above amounts)/JJ,
/0
0T
0)1V!Proposed maintenance calculation:1
,,,,,,J,
12,,,,,,,J,
213J&Parent with care assessable income (B)#
3
JJJ3LD&3]]J,
3.4J#Absent parent assessable income (A)
4
JJJ4LD&4JJ,,
45JBasic element (C)
5
JJJ5L8D/5JJ,,
5!6JAdditional element (G)
6
JJJ6R??D6JJJ,
67JJJJ]SJJ,
78_A x C =D8
JJJX8L;B@D4@D5@D3@D4@@?@B@ 8J (Revised basic element)8JJ,
8
9JA + B9
JJJJJJJ,
9:JJJJJJJJ,
:;J D x 2 = E;
JJJ&;L=D8@@.;J& (Min assessable to pay revised basic);JJ,
;<JJJJJJJJ,
<=J A - E = F=
JJJ'=LD4@D;@ =J (Remaining assessable) =JJ,
=>JJJJJJJJ,
>*?J"Maximum additional element for cap?
JJJi?LASC(6@C@C@@C&15@@?@?JJJJ
?Dl.JJ<nJG8U*ns*9***@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_@JJJJJJJJJ
@#AJMinimum of (F x G, cap) = HA
JJJ3ALE
D=@D6@@$?B%AJ (Additional element, capped)AJJ,
ABJJJJJJJJ,
BECJ=Proposed maintenance will be smaller of the two figures belowC
JJJJJJJ,
CDJJJJJJJJ,
DEMD + H = 'E
[ED8@DA@EM&E[GD&@@(EJ (50% of AP's assessable income)EJJJ,
EFJJJJJJJJ,
FGYProposed maintenanceG
YYY*G\e@$E
@$EBGJJJ,
G
H&I+Protected income calculations:I
,,,,,,,,
IJ,,,,,,,+J`#Number of children in NRP householdUJOR?C@C@@C0@
JK,Own net (total net)K
,,,KILD#K,,,,
KL,Partners total netL
,,,|LIMfC1@CX/@C-1@C@ @@@@@L,,,,
LM,Child Benefit receivedM
,,,=MIN'C$@C1@M,,,,
M+N,#Maintenance & other income receivedN
,,,=NIP'C|.@C1@N,,,,
NO,,,,,,,,,
OP+Total incomeP
,,,APIJ+DK@DL@@DM@@DN@P,,,,
PQ,,,,,,,,,
QR,
IS amountsR
,,,=RI33333U@S'C<
@@@#:(P@@#;?@BDJ@@@@#@yC@B@C@C=A@@@C@C>;@@@C@C?B@@@R,,,,
RS,HousingS
,,,SITDS,,,,
ST,Council taxT
,,,=TIU'C-@C1@T,,,,
TU,Travel costsU
,,,UIXDU,,,,
UV,Incentive to work (30)V
,,,~
VI>@V,,,,W,,,,,,,,,#XaNecessary amount to live onX
,,,NXI33333S]@Z8DR@DS@@DT@@DU@@DV@X,,,,Y,,,,,,,,,.Z,&Difference - income & necessary amountZ
,,,2ZI[DP@DX@@BZ,,,,[,15% of this difference[
,,,,[I]DZ@333333?@[,,,,\,,,,,,,,,]+Protected income]
,,,']I33333S]@_D[@DX@],,,,^,,,,,,,,,._,&Income with protected income safeguard_
,,,2_IgDP@D]@@B_,,,,D
l**q**~Rm**afStiabcdefgabMaintenance requirementa
,,,,,,,,b,,,,,,,,,[cJSMaintenance requirement will be smaller of the two figures below & protected incomec
JJSJJJJ,dJJJSJJJJ,eJProposed maintenance
e
JJeLeDGeJ,eLKD@333333?@'eJ (30% of AP's total net income)
eJ,fJJJJJJJJ,gYMaintenance requirement
g
YY.g\$e@$e@$_BgJJJJ,x=y] @t
\$\%] @
($ (%$Multiplier for months or yearsAllows amounts typed in to be for weeks, months, or years.
1 = s per 7-day week
2 = s per calendar month
3 = s per calendar year
(Internal calculations are done in weeks):4
Note the need to calculate the "net income" according to the rule:
Net income = gross income - tax - NI - pension.
It REALLY does mean subtract ALL the pension contributions!
If you are doing "what-ifs" to see what would happen if you changed your pension, you will need to change the net income as well.= +
8X>
A
" c
2*ɩ;
dMbP?_*+%,MFHP DeskJet 950C Seriesalculate @ dvConvertcome = gross income - t'(D@@@C*7@
, Income 2:~
y@
,, Change 2:~
1@
,,$Reformed scheme calculation:
%'@Cb`SJ'y5@4D@@@C*7@@@>'m۶mKp@(D@@@C*7@,,,
, Change 3:~
1$@
,,$
'',,,,,,,Liabilities:''',Income (from "Current scheme"):
,,
>1(C1@C@,,,$'',,,,,,,DMarchT@Dw@!aD@D@A@D@
@D@D@@$"#HSGBy5@>4L@@@C*7@@@m۶mKp@2(L@@@C*7@
%,Phasing-in amount (absolute):
,,
t1@^D@D
@
@$
<D@D @@$@$@"B,,,DMarchX@Dd@%ND@D@A@D@
@D@D@@$By5@%m۶mKp@
#,Phasing-in amount (signed):
,,
@1@!*D@D@
$DB,,,DMarch\@Dd@ND@D@A@D@
@D@D@@$By5@m۶mKp@,,,,,,,DMarch`@Dd@ND@D@A@D@
@D@D@@$By5@m۶mKp@
,Minimum liability:
,,
%1@CHG,,,DMarchd@Dd@ND@D@A@D@
@D@D@@$By5@m۶mKp@,,,,,,,DMarchh@D@Dy5@m۶mKp@,,,,,,,$'',,,,,,,$'',,,,,,,d\Premature transition - see "notes" for the assumptions. Figures show CS from 1st March 2003."22222222222$"""""""""""
CS to date2Intended transition2Premature transition2
Difference2
2Notes 222222$22222222222~
f@"~
"~
"'D@D@"N"F"Intended transition" is what would happen without any special action. """"""~
q@"V333333R@@D
#)D7@#@B@@@"&D@@"'333333RD@D@""""""""~
}@"V333333b@@D
#)D7@#@B@@@"?@P@)D
@Cb`S@@@"fffff&T(L@L@"W"O"Premature transition" is what happens if the PWC stops then restarts the case. """"""~
@"VLk@ @D
#)D7@#@B@@@"@`@3(
)L
@Cb`S@@@"V """"""""Dlb(|(oixY4((On(((( !"#$%&'()~
@ "V 333333r@@D
#)D7@#@B@@@ " `h@ " X "' "Treat each quarter as 13 weeks. """"""~
!`@!"?!33333Cv@!)D
@CeT@@@!"!@p@!"!X"!""""""""~
"@""?"33333Sz@")D!
@CeT@@@"""Pt@""""X#""K""CAssume the "stopped" period is the quarter starting 1st March 2003." """"""~
#@@#"?#33333c~@#)D"
@CeT@@@#"#`x@##"#X$#""""""""~
$@$"?$9@$)D#
@CeT@@@$"$p|@$$"$X%$"?$"7Assume there are no significant changes of circumstance$ """"""~
%@%"?%A@%)D$
@CfT@@@%"%@@%"%X&%""""""""~
&@&"?&I@&)D%
@CfT@@@&"&H@&&"&X'&"M&"EThe "difference" is how much the PWC has gained, from 1st March 2003.& """"""~
'@'"?'Q@')D&
@CfT@@@'"'P@''"'X('""""""""~
( @("?(Y@()D'
@CfT@@@("(X@(("(X(""""""""$)""""""""""", = +
8X>
"
2NS
dMbP?_*+%,MMAN05 Optra S 2450"""""Gw XXch 2003.@@.t March 2003.⤗ALexmark Optra S 2450(Lexmark Optra S 2450t;XN7h⤗X,,X⤗" dXX??
Benefit ratesU}ra}ra}
ra}ra}ra}ra}
ra} ra}
ra}$ ra
Nyyy
BPer weekB Per monthBPer year{{{{{{zzDCalculation:%@CFLBJy5@&4D@@@C*7@@@>m۶mKp@(D@@@C*7@{{{{{{zz{{{{{{{{{{zz *g"PWC Circumstances - input section: *o"NRP Circumstances - input section: =+5Formula parameters (in case they change the formula):,,,,,-
Second family,-Minimum ,1 child:,2 children:,3 children:,4 children:,5 children:,-option (0 1 2):,-
liability: $..@.4@.?.?.?,~
-?,~
/@ ,,,,,,,,, ,Income thresholds:,0Y@0i@0@@,,,, ~Net earned income (pw):
%4C/: w Qualifying Children 5Intermediate 6
,??
1 qualifying child
5results:
6 ~
@2 qualifying children
66 ^k,?UC-@@
@#-(%
D
@"B~
@%3 or more qualifying children89"#3P=#PJB6
7C@@C$@@@#G@@BCm@@@#@@B@C@@@#@@B@C @@@#,@@B@CW@@
@#zH@@B@@B
9 2nd Family Children88333333?"@C58>@C9!?@@@#D@BC9f?@@@#@@B@C9?@@@#@@B@C9?@@@#%@@B@C9P?@@
@#sH@@B@@@C@@ @O@C8C8@C@@@@B@6 }Ignore the section below ,?
!No second-family children=9>@E'C6t9@C2<@6 ~
?1 second-family child=9$'CIH@C6t9@6 k,
UC.,:@@
@#.(,:%
D@"B~
@ 2 second-family children:?iC@@/@C(8@C;@@#W8B6 Net earned income (pw):
~
! ~
@( 3 or more second-family children;.C8@@ @C/8@@5CZNPP@ffffff?@@F@B<CZPP@ffffff?@@i@B@"B6 ! ;:C@@ @C/@@5CNNI@ffffff?@@F@B<CNI@ffffff?@@i@B@"B6+ #Income Support and/or JSA(IB) (pw): ! w Nights of care by NRP;CJhH@@@]C*8@Cr= Z@CqRl@Cre Z@@@
B6 %C[P w ,?
$0 night care per week by NRP ! w ~
?$1 night care per week by NRP5 -WFTC and/or other benefits (not IS/JSA) (pw): ! 5-WFTC and/or other benefits (not IS/JSA) (pw):w ),%
DB~
@%2 nights care per week by NRP %"C]PQ %wQ@CSL ~
@%3 nights care per week by NRP1|)Feeds from the "Current scheme" worksheet6666 ! w ~
@"Equal nights care by "NRP"66666* "Child Benefit for 1st family (pw):
[0@EC C8@C
,@@@ *"Child Benefit for 2nd family (pw):
-sC
M@C C/@C
G@@@@B (6 NRP's current scheme net income:666|;fC@C0\;@@C4 @@@@CW1@ ! w +6#Current scheme qualifying children:666U6?C#4@C$ 5@@C%0|5@2 *Target spend per PWC-care night per child: !!2*Target spend per NRP-care night per child:w *6"NRP's current scheme 2nd children:666U6?C@C@@C0@ %Gz."@*C_R %wGz."@CT(M ! w + #Spend on applicable childcare (pw):
~
+#Spend on applicable childcare (pw):
~
Dla"v:tx_p[LvTf4'~ !"#$%&'()*+,-./0123456789:;<=>? !3!ePWC results section:!343"!dNRP results section:!"#" S!tKExample Income Support / JSA(IB) formulae (ready to clip for PWC, not NRP):6"3.Total income inc. maint. & benefits & credits:
"33"45@&oC\P@CXO@@C[0P@@C]HPQ@@CF`LB@"4-""%Total income inc. benefits & credits:
"""U"#Q@(?CPJ@CLI@@CS0L@" "~
""
None~
",?)"1%"'D"B#33344""""" #GzR@)CApCC@CC$8C=3AC\FP@CXYO@CFmLB@#FLB
@B@#\P@B@B1#
)Lone parent 16-17 & all children under 11.$)&Maintenance Liability diverted to PWC:
$))=$*@/'CFLB@CG3;C\FP@CXYO@CFmLB@#FLB
@B@#\P@B@B.&
&Lone parent 18+ & all children 11 - 15'33333""""" ~
''
Custom6(3.Total retained income after spend on children:
(33=(4%@J'C`hR@CaH2@(f6(".Total retained income after spend on children:
(""b(#P@(LCU>@CKH@@D&@@CF=LB@( "(7)3/Net earned income spent on 1st family children:
)33s)f>]C\P@@@2@C^3Q@C\FP@B)37)"/Net earned income spent on 1st family children:
)"")("vCPJ@@@KCF/LB@CKBH@@@CP_J@B) "V)tNExample Working Families Tax Credit formulae (ready to clip for PWC, not NRP):* *gChildren results section* ~
**
None~
*,?)*v%*/D*B++ #Total spend on 1st family children:
+ =+!%@,'CaH2@CKH@+ +̜U@ClWCmWC#8CY5OC\FPZ
"C\]PZ?
BB,+
$Adult credit & all children under 11,hFrom NRP's care-spend:, *,iID,Cc S%,!)CKH, /, 'Spend per child on 1st family children:
, >,!%@"(CcS@C8@, ,gffffX@+ClWChUCm$WC38CYEOC\VPZ
"C\mPZ?
BB=,
5Adult credit & 30 hour credit & all children under 11!-hFrom PWC's Child Benefit:- *-i?,D-Cc ST-!%@.>CcS@D,@@#X(O@BB- /- 'Spend per child on 2nd family children:
- -!&C@@@X@CT6(M@@CNOI@Cb@@@B- -̜U@,ClWCntXC#8CY5OC\FPZ
"C\]PZ?
BB+-
#Adult credit & all children 11 - 15#.jFrom Maintenance Liability:.k*.l-D.Cc S].m$G#FLBCcS@D,@@D-@@BB.n .gffffX@-ClWChUCn$tXC38CYEOC\VPZ
"C\mPZ?
BB<.
4Adult credit & 30 hour credit & all children 11 - 15*/h"From PWC's other benefits/credits:/ */i.D/Cc S/!0CcS@D,@@D-@@D.@C[>P@C]QPQ@@@Cn8@C8@@@@@BB/ ~
//
Custom!0hFrom PWC's earned income:0 *0i/D0Cc Sd0!0N@CcS@D,@@D-@@D.@@D/@B0 01 V1tNExample Working Families Tax Credit formulae (ready to clip for NRP, not PWC):2 ~
22
None~
2,@)2vQ@%27D2B3 3N@%ClWCmWC#CM5pICPFJZ
"CP]JZ?
BB,3
$Adult credit & all children under 114Q@2ClWChUCm$WC3CMEpICPVJZ
"CPmJZ?
BB=4
5Adult credit & 30 hour credit & all children under 115N@3ClWCntXC#CM5pICPFJZ
"CP]JZ?
BB+5
#Adult credit & all children 11 - 156Q@5ClWChUCn$tXC3CMEpICPVJZ
"CPmJZ?
BB<6
4Adult credit & 30 hour credit & all children 11 - 15~
77
Custom8X9tPExample PWC spends per night per child (clip for PWC or intact family, not NRP):~
::
None~
:,@):uGz."@%:BD:B~
;p@-;
%JRF / "Small Fortunes " 1995 average~
<h@5<
-JRF / "Small Fortunes " inflated to '99 rate?=ףp=
@)(C\P?A-=
%40 + 10% of PWC net income, no limitL>)\(@=6C\P333333?A(\,@B/>
'30 + 15% of PWC net income, limit 100f?LPC\P@CPJ@@333333?@@@@A,?
$15% of PWC+NRP net incomes, no limitDo$l0>Ul~ddc
\Y?Gt@ABCDEFGHIJKLMf@-PC\P@CPJ@@?@@@@A,@
$20% of PWC+NRP net incomes, no limitfA@PC\P@CPJ@@?@@@@A,A
$25% of PWC+NRP net incomes, no limit~
BB
CustomCXDtPExample NRP spends per night per child (clip for NRP or intact family, not PWC):~
EE
None~
E,@)EuGz."@%EMDEB~
Fp@-F
%JRF / "Small Fortunes " 1995 average~
Gh@5G
-JRF / "Small Fortunes " inflated to '99 rate?Hףp=
@mal)(CPJ?A)H
!40 + 10% of NRP income, no limitLI)\(@H6CPJ333333?A(\,@B+I
#30 + 15% of NRP income, limit 100MJ?7CPJ@333333?@@@@A'J
15% of NRP net income, no limitMKA7CPJ@?@@@@A'K
20% of NRP net income, no limitMLK7CPJ@?@@@@A'L
25% of NRP net income, no limit~
MM
Custom 4
\Y?Gp|||] @t;;
pڒ$"pڒ%"'
]%- @; ;
ڒ$:ڒ%:B
]%- @; ;
@ے$E@ے%EM
]< @<(<
ے$
ے%
]<x @D<\<
ܒ$ܒ%] @x<<
xܒ$xܒ%]- @<<
ܒ$*ܒ%*/
]- @<<
Hݒ$2Hݒ%27
3-No PWC circumstances are used in the formula.}wOnly type in to the white cells.
The right hand side of this worksheet has some values & formulae which may be useful.This must be 0, 1 or 2.
0: this switches off any 2nd family modification; it avoids reserving money for the 2nd family, and treats the number of children as 1st family children
1: the main option used in the text, where first the formula is applied to the NRP's net income for the 2nd family children to leave a residual amount, then applied to that amount for the 1st family.
2: an alternative mentioned, where the formula is applied for all children, then the money is shared equally between them.ICThis is paid if the net income is no more than "income threshold 1"NHIf the net income is no more than this, the "minimum liability" is paid.YSIf the net income is at least this, the simple "percent of income" formula applies.\VThis is the weekly income at which the cap applies.
Net income above this is ignored.Note the need to calculate the "net income" according to the rule:
Net income = gross income - tax - NI - pension.
If you are doing "what-ifs" to see what would happen if you changed your pension, you will need to change the net income as well.M GDon't mess with these.
They just make some of the cell-formula easier.YSThis refers just to children of this NRP.
Perhaps this needs to be changed later.
This is the net income, taking into account the cap. So this amount rather than the actual amount is used everywhere in the formula.1
+This is the percentage allowance for the 2nd family, if the green paper's currently prefered "option 1" is being used.
It is the standard percentage value according to the number of 2nd family children.
If "option 2" is being used, then this is 0%. Ditto if the 2nd family feature is switched off.\VThis is the resultant maintenance percentage.
2nd family option 1: first it subtracts the previously calculated 2nd family percentage to safeguard income for them, then it uses the standard percentages according to the number of 1st family children.
(The same sum is done if 2nd family option is switched off, because the "previously calculated 2nd family percentage" will be 0).
2nd family option 2: (the 2nd family percentage has been pre-calculated as 0% for convenience) - it uses the standard percentages across ALL the children, then multiplies by the proportion of them in the 1st family.This is an intermediate result used to adjust for net incomes less than 200.
This is what the maintenance is at a net income of 200.
The proposal is based on 5 up to 100 then modified %s up to 200 - so this helps calculate according to the position between 100 & 200.
This is an intermediate result, used because of the complication for net incomes less than 200.
This is what the maintenance would be based on pure %s.
In fact the proposal is based on 5 up to 100 then modified %s up to 200.
gaThe children of the NRP which are taken into account by the formula.
So not step children, etc.
The number of children to count when applying the formula.
2nd family option 1: 1st family chidren.
Ditto if the 2nd family feature is switched off.
2nd family option 2: both sets of children.Income other than benefits & maintenance.
Eg. Earned income. (It will be taken into account by the supplied Income Support & Working Families Tax Credit formulae).
PWC's Childcare Tax Credit.
This is amount that WFTC will recognise of the amount actually spent.
(It is then subject to the WFTC taper).
NRP's Childcare Tax Credit.
This is amount that WFTC will recognise of the amount actually spent.
(It is then subject to the WFTC taper).This is for the full Income Support (or Job Seeker's Allowance - Income Based) received (allowing for the maintenance premium and deductions for other income).
It does NOT include WFTC, see below.
Supply a suitable value or formula - the spreadsheet cannot deduce enough to generate this automatically.
Some useful formulae are available on the RHS of this sheet - if one of them matches the circumstances, clip it here and the spreadsheet will handle it reasonably.
This is added in the PWC section below.This gives the peculiar deduction mentioned in the White Paper at the equal sharing point. It is approximately half the Child Benefit.Anything that supplies cash.
WFTC; DPTC; AA; GA; ICA; DLA; etc.
But NOT means-tested benefits such as Income Support or Job Seeker's Allowance - Income Based, which are dealt with above.
(HB & CTB presumably go for housing and are not available income).lfAnything that supplies cash.
However, the spreadsheet doesn't cater for means-tested benefits (other than tax credits) here. The reason is that for NRPs on such benefits, the liability becomes a nominal amount and it isn't useful to analyse the effects.
WFTC; DPTC; AA; GA; ICA; DLA; etc.
(HB & CTB presumably go for housing and are not available income).QKAs defined by the formula.
Average nights per week, presumably truncated.
This will be calculated automatically unless the PWC is entitled to the lone parent premium, in which case type in the proper value.
This is kept separate even where it is taken into account in Income Support calculations.This will be calculated automatically unless the NRP is entitled to the lone parent premium, in which case type in the proper value.E?This is some amount or formula which is used to calculate the cost of a child.
It should exclude the costs of WFTC-applicable childcare, because that is added separately, above.
Some example amounts and formulae can be copied and modified from the RHS of this sheet. Use only amounts which are not NRP-only - it doesn't make sense for the PWC to aim to spend an amount based purely on the NRP's income (does it?)
It is multiplied by the number of nights that the PWC cares for the children, and the number of children, to arrive at the total spend on children by the PWC.
This is some amount or formula which is used to calculate the cost of a child.
It should exclude the costs of WFTC-applicable childcare, because that is added separately, above.
Some example amounts and formulae can be copied and modified from the RHS of this sheet. Use only formulae which don't mention the PWC - it doesn't make sense to use a target here which is based on the PWC's income (does it?)
It is multiplied by the number of nights that the NRP cares for the children, and the number of children, to arrive at the total spend on children by the NRP.
The same value is used for 1st family children where the NRP shares care, and for 2nd family children.
This is used for WFTC purposes, and is included in the total spend on children.
It is the actual amount of money spent on childcare which will be recognised when calculating WFTC.
This is used for WFTC purposes, and is used in the spend on 2nd family children.
It is the actual amount of money spent on childcare which will be recognised when calculating WFTC.
0!*Income Support is really too complicated to fit into this spreadsheet. Eg. you have to know ages of children, etc. So this permits a simple choice of formula.
Can clip one of the cells below into the "PWC's Income Support or Family Credit" cell.
These formulae use cell names and can be clipped.
o"iThe total income - earnings, maintenance, benefits.
If WFTC is claimed, this includes Childcare Credit.
"|This is the net income plus the Child Benefit for 2nd family children.
If WFTC is claimed, this includes Childcare Credit.
#Income Suport:
Lone parent 16-17 & all children under 11.
The spreadsheet will supply the number of children.
The formula takes Child Benefit into account. It has a maintenance premium of 10 & income disregard of 5.Q$KThis shows if there is any maintenance over once the target spend on the children is complete (in other words, if the PWC is not making a contribution, and the NRP is making a more than sufficient contribution).
In effect, this is where the maintenance is contributing to PWC's retained income.
It turns red if it goes positive.
$This is it!$Income Support:
Lone parent 18+ & all children under 11.
The spreadsheet will supply the number of children.
The formula takes Child Benefit into account. It has a maintenance premium of 10 & income disregard of 5.%This is the total spend for periods of shared care.
It doesn't include any of the applicable childcare, because that is assumed to be spent on the 2nd family children only.%Income Support:
Lone parent 18+ & all children under 11 including 1 disabled.
The spreadsheet will supply the number of children.
The formula takes Child Benefit into account. It has a maintenance premium of 10 & income disregard of 5.&This starts as the amount that the PWC "wants" to spend on the children, but is constrained by the PWC's resources if necessary.
This takes into account the target spend per child per night times number of children & number of nights.
It adds in the applicable childcare.
This is not allowed to leave the PWC with less than is spent on any child, except that it must be at least the amount actually spent on applicable childcare.&This is the total spend on the 2nd family children.
It includes all the NRP's childcare spend, because it is assumed for simplicity that childcare is not spent in 1st family children during shared-care.&Income Support:
Lone parent 18+ & all children 11 - 15.
The spreadsheet will supply the number of children.
The formula takes Child Benefit into account. It has a maintenance premium of 10 & income disregard of 5.F(@This is simply the total net income which not spent on children.(This is simply the net income not spent on children.
It takes into account maintenance payment plus direct spend on 2nd family and on 1st family during care periods.)This is the percentage of the PWC's non-benefit income which is spent on the 1st family children.
(It relates to "% which is PWC's other income").
It is useful to compare this with the NRP's equivalent.)This is the total percent of the NRP's income which is spent on the 1st family children.
(It is related to the maintenance paid + the amount spent during shared-care).
It is useful to compare this with the PWC's equivalent.8)2Working Families Tax Credit is really too complicated to fit into this spreadsheet. Eg. you have to know ages of children, etc. So this permits a simple choice of formula.
Can clip one of the cells below into the "PWC's WFTC and/or other benefits" cell.
These formulae use cell names and can be clipped.1++This includes time with both the PWC & NRP.+Working Families Tax Credit:
Adult credit & all children under 11.
The spreadsheet will supply the number of children. It will also add in Childcare Credits.
i,cSimply the amount spent while these children are in the NRP's care, at the NRP's target spend rate.I,CPer week:
PWC spend for PWC nights, plus NRP spend for NRP nights.
,Working Families Tax Credit:
Adult credit & 30 hour credit & all children under 11.
The spreadsheet will supply the number of children. It will also add in Childcare Credits.
-Obvious.u-oPer week:
NRP spend for 7 nights
+
NRP spend on applicable childcare divided by number of 2nd family children.
-Working Families Tax Credit:
Adult credit & all children 11 - 15.
The spreadsheet will supply the number of children. It will also add in Childcare Credits.
.This assumes that maintenance is intended for the child, but may overflow to the PWC.
So it can't be greater than the amount left to be spent on the children..Working Families Tax Credit:
Adult credit & 30 hour credit & all children 11 - 15.
The spreadsheet will supply the number of children. It will also add in Childcare Credits.
/It is not clear how to decide who is paying the rest of the money for the child, if Child Benefit & maintenance are insufficient.
This makes the crude assumption that other benefits are spread over all the people, PWC and children, but with twice as much going to the PWC.
Perhaps in future it ought to refer to the child premium components of benefits, but this is actually dubious.0{If benefits and maintenance are not sufficient to pay for the child, then the PWC must be getting the rest from own income.812Working Families Tax Credit is really too complicated to fit into this spreadsheet. Eg. you have to know ages of children, etc. So this permits a simple choice of formula.
Can clip one of the cells below into the "NRP's WFTC and/or other benefits" cell.
These formulae use cell names and can be clipped.3Working Families Tax Credit:
Adult credit & all children under 11.
The spreadsheet will supply the number of children. It will also add in Childcare Credits.
4Working Families Tax Credit:
Adult credit & 30 hour credit & all children under 11.
The spreadsheet will supply the number of children. It will also add in Childcare Credits.
5Working Families Tax Credit:
Adult credit & all children 11 - 15.
The spreadsheet will supply the number of children. It will also add in Childcare Credits.
6Working Families Tax Credit:
Adult credit & 30 hour credit & all children 11 - 15.
The spreadsheet will supply the number of children. It will also add in Childcare Credits.
9Can clip one of the cells below into the PWC spend per night cell. They are just a starter kit.
These formulae use cell names and can be clipped.
Note: these are amounts per night, so they divide the per week cost by 7.
;Joseph Rowntree Foundation 1995 average. In fact, "Small Fortunes " (Middleton, et al) quoted in the CSA Green Paper.
57.01 per week.
<Joseph Rowntree Foundation inflated by 10%. In fact, "Small Fortunes " (Middleton, et al) quoted in the CSA Green Paper.
57.01 per week, inflated to allow for inflation since 1995.=40 + 10% of PWC total income, no limit.
Takes into account the fact that children benefit from their parents' income indefinitly.
Divided by 7 for nights.
>30 + 15% of PWC total income, limit 100 per week.
Takes into account the fact that children benefit from their parents income, but puts an upper limit on this.
Divided by 7 for nights. (14.28 = 100/7!)
z?t15% of PWC+NRP total income, no limit.
Takes into account the fact that children benefit from their parents income.z@t20% of PWC+NRP total income, no limit.
Takes into account the fact that children benefit from their parents income.zAt25% of PWC+NRP total income, no limit.
Takes into account the fact that children benefit from their parents income.DCan clip one of the cells below into the NRP spend per night cell. They are just a starter kit.
These formulae use cell names and can be clipped.
Note: these are amounts per night, so they divide the per week by 7.FJoseph Rowntree Foundation 1995 average. In fact, "Small Fortunes " (Middleton, et al) quoted in the CSA Green Paper.
57.01 per week.
GJoseph Rowntree Foundation inflated by 10%. In fact, "Small Fortunes " (Middleton, et al) quoted in the CSA Green Paper.
57.01 per week, inflated to allow for inflation since 1995.H40 + 10% of NRP income, no limit.
Takes into account the fact that children benefit from their parents income indefinitly.
Divided by 7 for nights.
I30 + 15% of NRP total income, limit 100 per week.
Takes into account the fact that children benefit from their parents income, but puts an upper limit on this.
Divided by 7 for nights. (14.28 = 100/7!)
wJq15% of NRP total income, no limit.
Takes into account the fact that children benefit from their parents income.wKq20% of NRP total income, no limit.
Takes into account the fact that children benefit from their parents income.wLq25% of NRP total income, no limit.
Takes into account the fact that children benefit from their parents income.= +
8X>
A
"
2^+gq$u
dMbP?_*+%,MFHP DeskJet 950C Seriesncome, no@ dvConvertren benefit from their $6White Paper equal-sharing pseudo-half of Child BenefitFirst child~
@Subsequent children~
@x4$,Working Families Tax Credit - now superceded#Basic tax credit per family~
N@30-hour tax credit~
4@<
t0Vn[>QTX<VfL73F;B%7
B? !"#$%'()*. &Child allowance birth to Sept after 11~
@ 2!*Child allowance from then to Sept after 16~
!@3"+Child allowance from then to last day of 18~
"@@"#Disabled child credit:~
#A@$$ ,%$Limit, beyond which 55p in reduced~
%W@% +'#Max childcare recognition, 1 child:~
'`@-(%Max childcare recognition, > 1 child:~
(i@+)#(70% of recognised amount is paid).*JDO0N=?/]
@z{'Configure spreadsheet for days per year]
@{P{"@=,
$year = 364 days (52 week year)]
@`{{"year = 365 days (normal year) ]
@{{"year = 366 days (leap year)r ]
@{D|"!year = 365.25 days (average year)= +
8X>
Oh+'0@H\t
John WardaBarry PearsonsMicrosoft Excel@rm@78՜.+,08@LT
\icr
IntroductionCurrent schemePhasing inReformed schemeBenefit ratesWorksheets
!"#$%&'()*+,-./0123456789:;<=>?@ABCDEFGHIJKLMNOPQRSTUVWXYZ[\]^_`abcdefghijklmnopqrstuvwxyz{|}~Root EntryF78Book
?xSummaryInformation(DocumentSummaryInformation8