{"text_format":"1","workbook":"xlsx","n_sheets":21,"sheets":["Input","Loan Details","Applicant 1","Applicant 2","Applicant 3","Applicant 4","Household Expenditure","Summary Calculations","Version control","Stress Rates","CostOfLiving","Rates","Rates_upd","Tax Applicant 1","Tax Applicant 2","Tax Applicant 3","Tax Applicant 4","Tax Rates","1 Net Monthly Income","2 Existing Mortgage Commitments","3 Revolving Credit Commitments"],"not_rendered":{"charts":0,"images":5,"macros":1,"external_links":4,"defined_names":67,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Input","index":0,"n_rows":93,"n_cols":18,"rows_kept":93,"cut":false,"hidden":false,"merged":[[58,1,58,9],[1,1,1,9],[3,1,3,9],[21,1,21,9],[22,6,22,9],[42,1,42,9],[43,6,43,9],[51,1,51,9],[52,6,52,9],[52,2,52,3],[43,2,43,3],[22,2,22,3],[90,1,90,9],[59,6,59,9],[65,1,65,9],[66,6,66,9],[86,1,86,9],[66,2,66,3],[59,2,59,3]]}
[]
[null,"Mortgage How Much We Will Lend Calculator",null,null,null,null,null,null,null,null,{"$f":"IF(D6=2,N8,M8)","v":9},null,{"$f":"COUNTA(D6,D8,D10,D12,D14,D18,D20,H8,IF(D10=35,1,H10),IF(D18=\"Yes\",1,IF(D12=\"Yes\",H12,I12)),IF(D12=\"Yes\",1,IF(D18=\"Yes\",H18,I18)),H16)","v":1},{"$f":"COUNTA(D6,D8,D10,D12,D14,D18,D20,H8,IF(D10=35,1,H10),IF(D18=\"Yes\",1,IF(D12=\"Yes\",H12,I12)),IF(D12=\"Yes\",1,IF(D18=\"Yes\",H18,I18)),H16,H20)","v":2},null,null,null,"Broker"]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"COUNTA(D46,D55,D62,IF(D46=\"Yes\",D48,E48),IF(D46=\"Yes\",D50,E50),IF(D55=\"Yes\",D57,E57),IF(D62=\"Yes\",D64,E64))","v":3},{"$f":"COUNTA(D46,D55,D62,IF(D46=\"Yes\",D48,E48),IF(D46=\"Yes\",D50,E50),IF(D55=\"Yes\",D57,E57),IF(D62=\"Yes\",D64,E64))+COUNTA(H46,H55,H62,IF(H46=\"Yes\",H48,I48),IF(H46=\"Yes\",H50,I50),IF(H55=\"Yes\",H57,I57),IF(H62=\"Yes\",H64,I64))","v":6}]
[null,"Customer & Loan Details",null,null,null,null,null,null,null,null,null,null,{"$f":"COUNTA(D69,D73,D77,D83,D85)","v":5},{"$f":"COUNTA(D69,D73,D77,D83,D85)+COUNTA(H69,H73,H77,H83,H85)","v":10}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"COUNTA(D89,H89)","v":2},{"$f":"COUNTA(D89,H89)","v":2}]
[null,null,"Number of Applicants",null,null,null,"Customer Name",null,null,null,null,null,{"$f":"SUM(M2:M5)","v":11},{"$f":"SUM(N2:N5)","v":20}]
[null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"10+IF(D12=\"Yes\",1,0)+IF(D18=\"Yes\",1,0)+3+SUM(IF(D46=\"Yes\",2,0),IF(D55=\"Yes\",1,0),IF(D62=\"Yes\",1,0))+5+2","v":20},{"$f":"11+IF(D12=\"Yes\",1,0)+IF(D18=\"Yes\",1,0)+6+SUM(IF(D46=\"Yes\",2,0),IF(D55=\"Yes\",1,0),IF(D62=\"Yes\",1,0),IF(H46=\"Yes\",2,0),IF(H55=\"Yes\",1,0),IF(H62=\"Yes\",1,0))+10+2","v":29}]
[null,null,"Loan Purpose",null,null,null,"Loan Amount",null,null,null,null,null,{"$f":"M7-M6","v":9},{"$f":"N7-N6","v":9}]
[]
[null,null,"Term Years",null,null,null,{"$f":"IF($D$10=35,\"\",\"Term Months\")","v":"Term Months"}]
[]
[null,null,{"$f":"IF($D$18=\"Yes\",\"\",\"Co-Ownership\")","v":"Co-Ownership"},null,null,null,{"$f":"IF(OR($D$12=\"No\",$D$18=\"Yes\"),\"\",\"New Rent / Service Charge\")","v":"New Rent / Service Charge"}]
[]
[null,null,"Property Value",null,""]
[]
[null,null,null,null,null,null,"Number of Dependants"]
[]
[null,null,{"$f":"IF(D12=\"Yes\",\"\",\"Help to Buy: Equity Loan\")","v":"Help to Buy: Equity Loan"},null,null,null,{"$f":"IF(OR(D18=\"No\",D12=\"Yes\"),\"\",\"Help to Buy: Equity Loan Percentage\")","v":"Help to Buy: Equity Loan Percentage"}]
[]
[null,null,"First Applicant's Date of Birth (DD/MM/YYYY)","1990-01-01",null,null,"Second Applicant's Date of Birth (DD/MM/YYYY)","1990-01-01"]
[]
[null,{"$f":"IF(D6=2,\"Applicants' Annual Income\",\"Applicant's Annual Income\")","v":"Applicant's Annual Income"}]
[null,null,"First Applicant",null,null,null,"Second Applicant:"]
[]
[null,null,"Gross Basic Salary",null,null,null,"Gross Basic Salary"]
[]
[null,null,"Gross Overtime",null,null,null,"Gross Overtime"]
[]
[null,null,"Gross Commission",null,null,null,"Gross Commission"]
[]
[null,null,"Gross Bonus",null,null,null,"Gross Bonus"]
[]
[null,null,"Gross Contracted Allowances",null,null,null,"Gross Contracted Allowances"]
[]
[null,null,"Gross Pension",null,null,null,"Gross Pension"]
[]
[null,null,"Other State Benefits (Net)",null,null,null,"Other State Benefits"]
[]
[null,null,"Child Benefits for those aged 12 & under (Net)",null,null,null,"Child Benefits for those aged 12 & under"]
[]
[null,null,"Other Income (Net)",null,null,null,"Other Income"]
[]
[null,"Mortgages and Secured Loans - Excluding BTL Loans"]
[null,null,"First Applicant",null,null,null,"Second Applicant"]
[]
[null,null,"Other Mortgages and Secured Loans?","No",null,null,"Other Mortgages and Secured Loans?","No"]
[]
[null,null,"Total Outstanding Balance",null,null,null,"Total Outstanding Balance"]
[]
[null,null,"Monthly Secured Loan Repayment",null,null,null,"Monthly Secured Loan Repayment"]
[]
[null,"Store and Credit Cards"]
[null,null,"First Applicant",null,null,null,"Second Applicant"]
[]
[null,null,"Does the Applicant have credit cards or store cards?","No",null,null,"Does the Applicant have credit cards or store cards?","No"]
[]
[null,null,"Total outstanding Balances",null,null,null,"Total outstanding Balances"]
[]
[null,"Loans and Hire Purchase"]
[null,null,"First Applicant",null,null,null,"Second Applicant"]
[]
[null,null,"Other Unsecured Loans Or Hire Purchases?","No",null,null,"Other Unsecured Loans Or Hire Purchases?","No"]
[]
[null,null,"Monthly Unsecured Repayment",null,null,null,"Monthly Unsecured Repayment"]
[]
[null,"Other Monthly Commitments"]
[null,null,"First Applicant",null,null,null,"Second Applicant"]
[]
[null,null,"Student Loan",0,null,null,"Student Loan",0]
[]
[null,null,"Child / Spouse Maintenance",null,null,null,"Child / Spouse Maintenance"]
[]
[null,null,"Pension Contribution",0,null,null,"Pension Contribution",0]
[]
[null,null,"Car Insurance",null,null,null,"Car Insurance"]
[]
[null,null,"Childcare Vouchers",0,null,null,"Childcare Vouchers",0]
[]
[null,null,"Child Care / Nursery Fees",null,null,null,"Child Care / Nursery Fees"]
[]
[null,null,"Education Fees",null,null,null,"Education Fees"]
[]
[null,null,"Professional Subscriptions",0,null,null,"Professional Subscriptions",0]
[]
[null,null,"Life / Health Insurance",0,null,null,"Life / Health Insurance",0]
[]
[null,"Household Monthly Expenditure "]
[]
[null,null,"Rates / Local Authority Charges",0,null,null,"Other Committed Expenditure",0]
[]
[null,"Result"]
[]
[null,null,null,null,"Loan amount",{"$f":"IF(NoBlanks<>0,\"You have not completed \"&NoBlanks&\" mandatory field\"&IF(NoBlanks>1,\"s.\",\".\"),\nMAX(MIN('Summary Calculations'!D38,'Summary Calculations'!H38),0))","v":"You have not completed 9 mandatory fields."}]

{"sheet":"Loan Details","index":1,"n_rows":14,"n_cols":12,"rows_kept":14,"cut":false,"hidden":false,"merged":[[2,1,2,13]]}
[]
[]
[null,"Loan Details"]
[]
[null,null,"Number of Applicants ",{"$f":"Input!D6","v":0},null,null,"Channel","Broker"]
[]
[null,null,"Loan Purpose",{"$f":"Input!D8","v":0},null,null,"Purchase Price",{"$f":"Input!D14","v":0},null,null,"LTV",{"$f":"IFERROR(H8/H7,0)","v":0}]
[null,null,"Term Years",{"$f":"Input!D10","v":0},null,null,"Loan Amount",{"$f":"Input!H8","v":0}]
[null,null,"Term Months",{"$f":"Input!H10","v":0}]
[null,null,null,null,null,null,"Product",{"$f":"Input!H14","v":0}]
[null,null,null,null,null,null,"Annual Interest Rate",2.9000000000000001E-2,null,null,{"$f":" IFERROR(\nIF(H10=Rates!A7,VLOOKUP(L7,Rates!B7:O14,HLOOKUP(D7,Rates!E1:O2,2,0),1),\n    IF(H10=Rates!A15,VLOOKUP(L7,Rates!B15:O22,HLOOKUP(D7,Rates!E1:O2,2,0),1),\n     IF(H10=Rates!A23,VLOOKUP(L7,Rates!B23:O30,HLOOKUP(D7,Rates!E1:O2,2,0),1),\n      IF(H10=Rates!A42,VLOOKUP(L7,Rates!B42:F47,5,1))))),\"\")","v":false},{"$f":"IFERROR(IF(ISNUMBER(SEARCH(\"fixed\",H10)),Rates_upd!$C$16,Rates_upd!$Q$16),\"\")","v":""}]
[null,null,"Co-Ownership",{"$f":"Input!D12","v":0},null,null,"Follow-On Rate",3.3000000000000002E-2]
[]
[null,null,"Help to Buy: Equity Loan",{"$f":"Input!D18","v":0},null,null,"Help to Buy: Equity Loan Percentage",{"$f":"Input!H18","v":0}]

{"sheet":"Applicant 1","index":2,"n_rows":131,"n_cols":9,"rows_kept":131,"cut":false,"hidden":false,"merged":[[125,1,125,9],[57,1,57,9],[2,1,2,9],[8,1,8,9],[35,1,35,9],[94,1,94,9],[58,2,58,8],[64,2,64,8],[70,2,70,8],[75,2,75,8],[81,2,81,8]]}
[{"$f":"'Loan Details'!H5","v":"Broker"}]
[]
[null,"Loan Details"]
[]
[null,null,"Date of Birth (YYYY-MM-DD)",{"$f":"Input!D20","v":"1990-01-01"},null,null,"Age",{"$f":"IF(D5=\"\",\"Input DOB\",DATEDIF(D5,TODAY(),\"Y\"))","v":31}]
[null,null,"Estimate Retirement Age",68]
[]
[]
[null,"Employment Income"]
[]
[null,null,"Employment Status","Employed"]
[null,null,"Type of Fixed Term"]
[]
[null,null,null,"Gross Annual","Net Monthly","Assessable",null,"Gross Annual"]
[null,null,"Basic Salary",{"$f":"Input!D25","v":0},null,{"$f":"'1 Net Monthly Income'!B6","v":1},"Pay from all employment(s) (Wage / Salary) Year 1"]
[null,null,"Overtime",{"$f":"Input!D27","v":0},null,{"$f":"'1 Net Monthly Income'!B7","v":0.5},"Pay from all employment(s) (Wage / Salary) Year 2"]
[null,null,"Commission",{"$f":"Input!D29","v":0},null,{"$f":"'1 Net Monthly Income'!B8","v":0.5},"Pay from all employment(s) (Wage / Salary) Year 3"]
[null,null,"Bonus",{"$f":"Input!D31","v":0},null,{"$f":"'1 Net Monthly Income'!B9","v":0.5}]
[null,null,"Allowance",{"$f":"Input!D33","v":0},null,{"$f":"'1 Net Monthly Income'!B10","v":1}]
[null,null,"Sub Total",{"$f":"IF(D12=\"Zero Hours\",0,SUM(D15:D19))","v":0},{"$f":"IF(D12=\"Zero Hours\",0,SUM(E15:E19))","v":0},null,"Assessable Income",{"$f":"H15","v":0}]
[]
[null,null,"Does the applicant have \nSecondary Employment Income?","No"]
[null,null,"Secondary Employment Status"]
[null,null,"Type of Fixed Term"]
[]
[null,null,null,"Gross Annual","Net Monthly","Assessable"]
[null,null,"Basic Salary",null,null,{"$f":"'1 Net Monthly Income'!B6","v":1}]
[null,null,"Overtime",null,null,{"$f":"'1 Net Monthly Income'!B7","v":0.5}]
[null,null,"Commission",null,null,{"$f":"'1 Net Monthly Income'!B8","v":0.5}]
[null,null,"Bonus",null,null,{"$f":"'1 Net Monthly Income'!B9","v":0.5}]
[null,null,"Allowance",null,null,{"$f":"'1 Net Monthly Income'!B10","v":1}]
[null,null,"Sub Total",{"$f":"IF(D24=\"Zero Hours\",0,SUM(D27:D31))","v":0},{"$f":"IF(D24=\"Zero Hours\",0,SUM(E27:E31))","v":0}]
[]
[null,null,"Total Income (Gross Annual)",{"$f":"D20+H20+D32","v":0},{"$f":"E20+I20+E32+I32","v":0},null,"Total Assessible Income (Gross Annual)",{"$f":"IF(D12=\"Zero Hours\",0,SUMPRODUCT(D15:D19,F15:F19))+IF(D24=\"Zero Hours\",0,SUMPRODUCT(D27:D31,F27:F31))+H20","v":0},{"$f":"IF(D12=\"Zero Hours\",0,SUMPRODUCT(E15:E19,F15:F19))+IF(D24=\"Zero Hours\",0,SUMPRODUCT(E27:E31,F27:F31))","v":0}]
[]
[null,"Other Sources of Income"]
[]
[null,null,"Does the applicants have \nany other sources of income?","Yes"]
[]
[null,null,null,"Annual","Net Monthly","Assessable"]
[null,null,"Rental Income excluding BTL properties",null,null,{"$f":"'1 Net Monthly Income'!C22","v":0.75}]
[null,null,"Profit from Self Employment",null,null,{"$f":"'1 Net Monthly Income'!C24","v":1}]
[null,null,"Income from Company pension",null,null,{"$f":"'1 Net Monthly Income'!C25","v":1}]
[null,null,"Income from State pension",{"$f":"Input!D35","v":0},null,{"$f":"'1 Net Monthly Income'!C26","v":1}]
[null,null,"Income from Private pension",null,null,{"$f":"'1 Net Monthly Income'!C27","v":1}]
[null,null,"Other state benefits (Net)",{"$f":"Input!D37","v":0},null,{"$f":"'1 Net Monthly Income'!C28","v":0.25}]
[null,null,"Investment income ",null,null,{"$f":"'1 Net Monthly Income'!C29","v":1}]
[null,null,"Dividends",null,null,{"$f":"'1 Net Monthly Income'!C30","v":1}]
[null,null,"Deposit Interest",null,null,{"$f":"'1 Net Monthly Income'!C31","v":1}]
[null,null,"Trust Income",null,null,{"$f":"'1 Net Monthly Income'!C32","v":1}]
[null,null,"Annuities",null,null,{"$f":"'1 Net Monthly Income'!C33","v":1}]
[null,null,"Child Benefit for children aged 12 or below (Net)",{"$f":"Input!D39","v":0},null,{"$f":"'1 Net Monthly Income'!C34","v":1},{"$f":"IF('Applicant 2'!D7=\"Yes\",IF(AND(SUM(D34,D41:D51,D54,D123*12)<=50000,SUM('Applicant 2'!D34,'Applicant 2'!D41:D51,'Applicant 2'!D123*12)<=50000),D52,0),IF(SUM(D34,D41:D51,D54,D123*12)<=50000,D52,0))","v":0},{"$f":"IF('Applicant 2'!D7=\"Yes\",IF(AND(SUM(D34,D41:D51,D54,D123*12)<=50000,SUM('Applicant 2'!D34,'Applicant 2'!D41:D51,'Applicant 2'!D123*12)<=50000),E52,0),IF(SUM(D34,D41:D51,D54,D123*12)<=50000,E52,0))","v":0}]
[]
[null,null,"Other Income (Net)",{"$f":"Input!D41","v":0},null,{"$f":"'1 Net Monthly Income'!C35","v":1}]
[]
[null,null,"Total Other Income",{"$f":"SUM(D41:D51,G52,D54)","v":0},{"$f":"SUM(E41:E51,E54,H52)","v":0},null,"Total Assessable Other Income",{"$f":"SUMPRODUCT(D41:D51,F41:F51)+G52*F52+D54*F54","v":0},{"$f":"SUMPRODUCT(E41:E51,F41:F51)+H52*F52+E54*F54","v":0}]
[]
[null,"Credit Commitments "]
[null,null,"Primary Residential Mortgage"]
[]
[null,null,"Does the applicant have existing Residential Mortage Commitment?","No",null,null,"To be repaid?"]
[null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Assessable Payment",{"$f":"(D62-H62)*'2 Existing Mortgage Commitments'!$E$20/12+D63","v":0}]
[]
[null,null,"Other Mortgages and Secured Loans"]
[]
[null,null,"Does the applicant have any other Mortgages or Secured Loans?","Yes",null,null,"To be repaid?"]
[null,null,"Total Outstanding Balance",{"$f":"Input!D48","v":0},null,null,"Balance to be repaid"]
[null,null,"Monthly Payment to remain",{"$f":"Input!D50","v":0},null,null,"Assessable Payment",{"$f":"(D68-H68)*'2 Existing Mortgage Commitments'!$E$20/12+D69","v":0}]
[]
[null,null,"Store and Credit Cards"]
[]
[null,null,"Does the applicant have Credit or Store Card?","Yes",null,null,"To be repaid?"]
[null,null,"Balance",{"$f":"Input!D57","v":0},null,null,"Balance to be repaid"]
[null,null,null,null,null,null,"Assessable Payment",{"$f":"(D74-H74)*'3 Revolving Credit Commitments'!D14","v":0}]
[null,null,"Loans and Hire Purchase"]
[]
[null,null,"Does the applicant have any \nUnsecured Loans or Hire Purchase?","Yes",null,null,"To be repaid?"]
[null,null,"Monthly Payment",{"$f":"Input!D64","v":0},null,null,"Payment to be repaid"]
[null,null,null,null,null,null,"Assessable Payment",{"$f":"(D79-H79)","v":0}]
[]
[null,null,"Other Commitments"]
[]
[null,null,"Does the applicant make any monthly\npension contributions or have any other\nfinancial commitments?","Yes"]
[]
[null,null,"Child / Spouse Maintenance ",{"$f":"Input!D71","v":0},null,null,"Pension Contribution",{"$f":"Input!D73","v":0}]
[null,null,"Childcare Vouchers",{"$f":"Input!D77","v":0},null,null,"Health Insurance",{"$f":"Input!D85","v":0}]
[null,null,"Child Care / Nursery Fees",{"$f":"Input!D79","v":0},null,null,"Insurance"]
[null,null,"Education Fees",{"$f":"Input!D81","v":0}]
[null,null,"Professional Subscriptions",{"$f":"Input!D83","v":0},null,null,"Other "]
[null,null,"Student Loan",{"$f":"Input!D69","v":0}]
[null,null,null,null,null,null,"Commitments to be paid off"]
[null,null,"Overall Commitments",{"$f":"IF(D84 = \"Yes\",SUM(D86:D91,H86:H90),0)-H92","v":0}]
[]
[null,"BTL Portfolio "]
[]
[null,null,"Does the applicant own any Investment / BTL properties","No",null,null,"How many Investment/BTL properties does the applicant have?"]
[]
[null,null,"Property 1:",null,null,null,"Property 4:"]
[null,null,"To be repaid?",null,null,null,"To be repaid?"]
[null,null,"Outstanding Balance",null,null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs",null,null,null,"Monthly Additional Costs"]
[null,null,"Rental Income",null,null,null,"Rental Income"]
[]
[null,null,"Property 2:",null,null,null,"Property 5:"]
[null,null,"To be repaid?",null,null,null,"To be repaid?"]
[null,null,"Outstanding Balance",null,null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs",null,null,null,"Monthly Additional Costs"]
[null,null,"Rental Income",null,null,null,"Rental Income"]
[]
[null,null,"Property 3:"]
[null,null,"To be repaid?"]
[null,null,"Outstanding Balance"]
[null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs"]
[null,null,"Rental Income"]
[]
[null,null,"Overall Outstanding Balance",{"$f":"D101+D108+D115+H108+H101","v":0}]
[null,null,"Overall Monthly Payment",{"$f":"D102+D109+D116+H109+H102","v":0},null,null,"Assessable BTL Repayment",{"$f":"MAX(D102+D101*'2 Existing Mortgage Commitments'!$E$10/12,D101*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(D109+D108*'2 Existing Mortgage Commitments'!$E$10/12,D108*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(D116+D115*'2 Existing Mortgage Commitments'!$E$10/12,D115*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(H102+H101*'2 Existing Mortgage Commitments'!$E$10/12,H101*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(H109+H108*'2 Existing Mortgage Commitments'!$E$10/12,H108*'2 Existing Mortgage Commitments'!$F$10/12)","v":0}]
[null,null,"Overall Montly additional costs",{"$f":"D103+D110+D117+H110+H103","v":0}]
[null,null,"Montly Portfolio Rental Income",{"$f":"D104+D111+D118+H111+H104","v":0},null,null,"Assessable Rental Income",{"$f":"D123*'1 Net Monthly Income'!$C$22","v":0}]
[]
[]
[null,"Gross Income"]
[]
[null,null,"Total Gross Annual Income",{"$f":"SUM(D34 + D56 +D123*12)","v":0}]
[null,null,"Total Gross Assessible Income",{"$f":"SUM(H34,H56)-SUM(D41*F41,D46*F46,D47*F47,D49*F49,D50*F50,D51*F51,G52,D54*F54)","v":0}]
[]
[null,null,"Total Net Monthly Income",{"$f":"IF('Loan Details'!H5=\"Broker\",'Tax Applicant 1'!C44+(D41*F41+D46*F46+G52*F52+D54*F54)/12+H123,\n                            IF(OR(D11=\"Self Employed\",D11=\"Employed and Self Employed\",D23=\"Self Employed\",D23=\"Employed and Self Employed\"),'Tax Applicant 1'!C44+(D41*F41+D46*F46+G52*F52+D54*F54)/12+H123,\n                                             I34+I56+H123))","v":0},null,null,"Non-Credit Monthly Commitments",{"$f":"IF(OR('Loan Details'!H5=\"Broker\",OR(D11=\"Self Employed\",D23=\"Self Employed\")),D93-H86-D91-D87,D93)","v":0}]

{"sheet":"Applicant 2","index":3,"n_rows":131,"n_cols":9,"rows_kept":131,"cut":false,"hidden":true,"merged":[[2,1,2,9],[75,2,75,8],[81,2,81,8],[94,1,94,9],[125,1,125,9],[8,1,8,9],[35,1,35,9],[58,2,58,8],[64,2,64,8],[70,2,70,8],[57,1,57,9],[10,3,10,4]]}
[{"$f":"'Loan Details'!H5","v":"Broker"}]
[]
[null,"Loan Details"]
[]
[null,null,"Date of Birth (YYYY-MM-DD)",null,null,null,"Age",{"$f":"IF(D5=\"\",\"Input DOB\",DATEDIF(D5,TODAY(),\"Y\"))","v":"Input DOB"}]
[null,null,"Estimate Retirement Age"]
[null,null,"Will the applicant share household with Applicant 1?"]
[]
[null,"Employment Income"]
[]
[null,null,"Employment Status"]
[null,null,"Type of Fixed Term"]
[]
[null,null,null,"Gross Annual","Net Monthly","Assessable",null,"Gross Annual"]
[null,null,"Basic Salary",null,null,{"$f":"'1 Net Monthly Income'!B6","v":1},"Pay from all employment(s) (Wage / Salary) Year 1"]
[null,null,"Overtime",null,null,{"$f":"'1 Net Monthly Income'!B7","v":0.5},"Pay from all employment(s) (Wage / Salary) Year 2"]
[null,null,"Commission",null,null,{"$f":"'1 Net Monthly Income'!B8","v":0.5},"Pay from all employment(s) (Wage / Salary) Year 3"]
[null,null,"Bonus",null,null,{"$f":"'1 Net Monthly Income'!B9","v":0.5}]
[null,null,"Allowance",null,null,{"$f":"'1 Net Monthly Income'!B10","v":1}]
[null,null,"Sub Total",{"$f":"IF(D12=\"Zero Hours\",0,SUM(D15:D19))","v":0},{"$f":"IF(D12=\"Zero Hours\",0,SUM(E15:E19))","v":0},null,"Assessable Income",{"$f":"H15","v":0}]
[]
[null,null,"Does the applicant have \nSecondary Employment Income?"]
[null,null,"Secondary Employment Status"]
[null,null,"Type of Fixed Term"]
[]
[null,null,null,"Gross Annual","Net Monthly","Assessable"]
[null,null,"Basic Salary",null,null,{"$f":"'1 Net Monthly Income'!B6","v":1}]
[null,null,"Overtime",null,null,{"$f":"'1 Net Monthly Income'!B7","v":0.5}]
[null,null,"Commission",null,null,{"$f":"'1 Net Monthly Income'!B8","v":0.5}]
[null,null,"Bonus",null,null,{"$f":"'1 Net Monthly Income'!B9","v":0.5}]
[null,null,"Allowance",null,null,{"$f":"'1 Net Monthly Income'!B10","v":1}]
[null,null,"Sub Total",{"$f":"IF(D24=\"Zero Hours\",0,SUM(D27:D31))","v":0},{"$f":"IF(D24=\"Zero Hours\",0,SUM(E27:E31))","v":0}]
[]
[null,null,"Total Income (Gross Annual)",{"$f":"D20+H20+D32","v":0},{"$f":"E20+I20+E32+I32","v":0},null,"Total Assessible Income (Gross Annual)",{"$f":"IF(D12=\"Zero Hours\",0,SUMPRODUCT(D15:D19,F15:F19))+IF(D24=\"Zero Hours\",0,SUMPRODUCT(D27:D31,F27:F31))+H20","v":0},{"$f":"IF(D12=\"Zero Hours\",0,SUMPRODUCT(E15:E19,F15:F19))+IF(D24=\"Zero Hours\",0,SUMPRODUCT(E27:E31,F27:F31))","v":0}]
[]
[null,"Other Sources of Income"]
[]
[null,null,"Does the applicants have \nany other sources of income?"]
[]
[null,null,null,"Annual","Net Monthly","Assessable"]
[null,null,"Rental Income excluding BTL properties",null,null,{"$f":"'1 Net Monthly Income'!C22","v":0.75}]
[null,null,"Profit from Self Employment",null,null,{"$f":"'1 Net Monthly Income'!C24","v":1}]
[null,null,"Income from Company pension",null,null,{"$f":"'1 Net Monthly Income'!C25","v":1}]
[null,null,"Income from State pension",null,null,{"$f":"'1 Net Monthly Income'!C26","v":1}]
[null,null,"Income from Private pension",null,null,{"$f":"'1 Net Monthly Income'!C27","v":1}]
[null,null,"Other state benefits (Net)",null,null,{"$f":"'1 Net Monthly Income'!C28","v":0.25}]
[null,null,"Investment income ",null,null,{"$f":"'1 Net Monthly Income'!C29","v":1}]
[null,null,"Dividends",null,null,{"$f":"'1 Net Monthly Income'!C30","v":1}]
[null,null,"Deposit Interest",null,null,{"$f":"'1 Net Monthly Income'!C31","v":1}]
[null,null,"Trust Income",null,null,{"$f":"'1 Net Monthly Income'!C32","v":1}]
[null,null,"Annuities",null,null,{"$f":"'1 Net Monthly Income'!C33","v":1}]
[null,null,"Child Benefit for children aged 12 or below (Net)",null,null,{"$f":"'1 Net Monthly Income'!C34","v":1},{"$f":"IF('Applicant 2'!D7=\"Yes\",IF(AND(SUM(D34,D41:D51,D54,D123*12)<=50000,SUM('Applicant 1'!D34,'Applicant 1'!D41:D51,'Applicant 1'!D123*12)<=50000),D52,0),IF(SUM(D34,D41:D51,D54,D123*12)<=50000,D52,0))","v":0},{"$f":"IF('Applicant 2'!D7=\"Yes\",IF(AND(SUM(D34,D41:D51,D54,D123*12)<=50000,SUM('Applicant 1'!D34,'Applicant 1'!D41:D51,'Applicant 1'!D123*12)<=50000),E52,0),IF(SUM(D34,D41:D51,D54,D123*12)<=50000,E52,0))","v":0}]
[]
[null,null,"Other Income (Net)",null,null,{"$f":"'1 Net Monthly Income'!C35","v":1}]
[]
[null,null,"Total Other Income",{"$f":"SUM(D41:D51,G52,D54)","v":0},{"$f":"SUM(E41:E51,E54,H52)","v":0},null,"Total Assessable Other Income",{"$f":"SUMPRODUCT(D41:D51,F41:F51)+G52*F52+D54*F54","v":0},{"$f":"SUMPRODUCT(E41:E51,F41:F51)+H52*F52+E54*F54","v":0}]
[]
[null,"Credit Commitments "]
[null,null,"Primary Residential Mortgage"]
[]
[null,null,"Does the applicant have existing Residential Mortage Commitment?",null,null,null,"To be repaid?"]
[null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Assessable Payment",{"$f":"(D62-H62)*'2 Existing Mortgage Commitments'!$E$20/12+D63","v":0}]
[]
[null,null,"Other Mortgages and Secured Loans"]
[]
[null,null,"Does the applicant have any other Mortgages or Secured Loans?",null,null,null,"To be repaid?"]
[null,null,"Total Outstanding Balance",null,null,null,"Balance to be repaid"]
[null,null,"Monthly Payment to remain",null,null,null,"Assessable Payment",{"$f":"(D68-H68)*'2 Existing Mortgage Commitments'!$E$20/12+D69","v":0}]
[]
[null,null,"Store and Credit Cards"]
[]
[null,null,"Does the applicant have Credit or Store Card?",null,null,null,"To be repaid?"]
[null,null,"Balance",null,null,null,"Balance to be repaid"]
[null,null,null,null,null,null,"Assessable Payment",{"$f":"(D74-H74)*'3 Revolving Credit Commitments'!D14","v":0}]
[null,null,"Loans and Hire Purchase"]
[]
[null,null,"Does the applicant have any \nUnsecured Loans or Hire Purchase?",null,null,null,"To be repaid?"]
[null,null,"Monthly Payment",null,null,null,"Payment to be repaid"]
[null,null,null,null,null,null,"Assessable Payment",{"$f":"(D79-H79)","v":0}]
[]
[null,null,"Other Commitments"]
[]
[null,null,"Does the applicant make any monthly\npension contributions or have any other\nfinancial commitments?"]
[]
[null,null,"Child / Spouse Maintenance ",null,null,null,"Pension Contribution"]
[null,null,"Childcare Vouchers",null,null,null,"Health Insurance"]
[null,null,"Child Care / Nursery Fees",null,null,null,"Insurance"]
[null,null,"Education Fees"]
[null,null,"Professional Subscriptions",null,null,null,"Other "]
[null,null,"Student Loan"]
[null,null,null,null,null,null,"Commitments to be paid off"]
[null,null,"Overall Commitments",{"$f":"IF(D84 = \"Yes\",SUM(D86:D91,H86:H90),0)-H92","v":0}]
[]
[null,"BTL Portfolio "]
[]
[null,null,"Does the applicant own any Investment / BTL properties",null,null,null,"How many Investment/BTL properties does the applicant have?"]
[]
[null,null,"Property 1:",null,null,null,"Property 4:"]
[null,null,"To be repaid?",null,null,null,"To be repaid?"]
[null,null,"Outstanding Balance",null,null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs",null,null,null,"Monthly Additional Costs"]
[null,null,"Rental Income",null,null,null,"Rental Income"]
[]
[null,null,"Property 2:",null,null,null,"Property 5:"]
[null,null,"To be repaid?",null,null,null,"To be repaid?"]
[null,null,"Outstanding Balance",null,null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs",null,null,null,"Monthly Additional Costs"]
[null,null,"Rental Income",null,null,null,"Rental Income"]
[]
[null,null,"Property 3:"]
[null,null,"To be repaid?"]
[null,null,"Outstanding Balance"]
[null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs"]
[null,null,"Rental Income"]
[]
[null,null,"Overall Outstanding Balance",{"$f":"D101+D108+D115+H108+H101","v":0}]
[null,null,"Overall Monthly Payment",{"$f":"D102+D109+D116+H109+H102","v":0},null,null,"Assessable BTL Repayment",{"$f":"MAX(D102+D101*'2 Existing Mortgage Commitments'!$E$10/12,D101*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(D109+D108*'2 Existing Mortgage Commitments'!$E$10/12,D108*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(D116+D115*'2 Existing Mortgage Commitments'!$E$10/12,D115*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(H102+H101*'2 Existing Mortgage Commitments'!$E$10/12,H101*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(H109+H108*'2 Existing Mortgage Commitments'!$E$10/12,H108*'2 Existing Mortgage Commitments'!$F$10/12)","v":0}]
[null,null,"Overall Montly additional costs",{"$f":"D103+D110+D117+H110+H103","v":0}]
[null,null,"Montly Portfolio Rental Income",{"$f":"D104+D111+D118+H111+H104","v":0},null,null,"Assessable Rental Income",{"$f":"D123*'1 Net Monthly Income'!$C$22","v":0}]
[]
[]
[null,"Gross Income"]
[]
[null,null,"Total Gross Annual Income",{"$f":"SUM(D34 + D56 +D123*12)","v":0}]
[null,null,"Total Gross Assessible Income",{"$f":"SUM(H34,H56)-SUM(D41*F41,D46*F46,D47*F47,D49*F49,D50*F50,D51*F51,G52,D54*F54)","v":0}]
[]
[null,null,"Total Net Monthly Income",{"$f":"IF('Loan Details'!H5=\"Broker\",'Tax Applicant 2'!C44+(D41*F41+D46*F46+D52*F52+D54*F54)/12+H123,\n                            IF(OR(D11=\"Self Employed\",D11=\"Employed and Self Employed\",D23=\"Self Employed\",D23=\"Employed and Self Employed\"),'Tax Applicant 2'!C44+(D41*F41+D46*F46+D52*F52+D54*F54)/12+H123,\n                                             I34+I56+H123))","v":0},null,null,"Non-Credit Monthly Commitments",{"$f":"IF(OR('Loan Details'!H5=\"Broker\",OR(D11=\"Self Employed\",D23=\"Self Employed\")),D93-H86-D91-D87,D93)","v":0}]

{"sheet":"Applicant 3","index":4,"n_rows":131,"n_cols":9,"rows_kept":131,"cut":false,"hidden":true,"merged":[[2,1,2,9],[75,2,75,8],[81,2,81,8],[94,1,94,9],[125,1,125,9],[8,1,8,9],[35,1,35,9],[58,2,58,8],[64,2,64,8],[70,2,70,8],[57,1,57,9],[10,3,10,4]]}
[{"$f":"'Loan Details'!H5","v":"Broker"}]
[]
[null,"Loan Details"]
[]
[null,null,"Date of Birth (YYYY-MM-DD)",null,null,null,"Age",{"$f":"IF(D5=\"\",\"Input DOB\",DATEDIF(D5,TODAY(),\"Y\"))","v":"Input DOB"}]
[null,null,"Estimate Retirement Age"]
[null,null,"Will the applicant share household with Applicant 1?"]
[]
[null,"Employment Income"]
[]
[null,null,"Employment Status"]
[null,null,"Type of Fixed Term"]
[]
[null,null,null,"Gross Annual","Net Monthly","Assessable",null,"Gross Annual"]
[null,null,"Basic Salary",null,null,{"$f":"'1 Net Monthly Income'!B6","v":1},"Pay from all employment(s) (Wage / Salary) Year 1"]
[null,null,"Overtime",null,null,{"$f":"'1 Net Monthly Income'!B7","v":0.5},"Pay from all employment(s) (Wage / Salary) Year 2"]
[null,null,"Commission",null,null,{"$f":"'1 Net Monthly Income'!B8","v":0.5},"Pay from all employment(s) (Wage / Salary) Year 3"]
[null,null,"Bonus",null,null,{"$f":"'1 Net Monthly Income'!B9","v":0.5}]
[null,null,"Allowance",null,null,{"$f":"'1 Net Monthly Income'!B10","v":1}]
[null,null,"Sub Total",{"$f":"IF(D12=\"Zero Hours\",0,SUM(D15:D19))","v":0},{"$f":"IF(D12=\"Zero Hours\",0,SUM(E15:E19))","v":0},null,"Assessable Income",{"$f":"H15","v":0}]
[]
[null,null,"Does the applicant have \nSecondary Employment Income?"]
[null,null,"Secondary Employment Status"]
[null,null,"Type of Fixed Term"]
[]
[null,null,null,"Gross Annual","Net Monthly","Assessable"]
[null,null,"Basic Salary",null,null,{"$f":"'1 Net Monthly Income'!B6","v":1}]
[null,null,"Overtime",null,null,{"$f":"'1 Net Monthly Income'!B7","v":0.5}]
[null,null,"Commission",null,null,{"$f":"'1 Net Monthly Income'!B8","v":0.5}]
[null,null,"Bonus",null,null,{"$f":"'1 Net Monthly Income'!B9","v":0.5}]
[null,null,"Allowance",null,null,{"$f":"'1 Net Monthly Income'!B10","v":1}]
[null,null,"Sub Total",{"$f":"IF(D24=\"Zero Hours\",0,SUM(D27:D31))","v":0},{"$f":"IF(D24=\"Zero Hours\",0,SUM(E27:E31))","v":0}]
[]
[null,null,"Total Income (Gross Annual)",{"$f":"D20+H20+D32","v":0},{"$f":"E20+I20+E32+I32","v":0},null,"Total Assessible Income (Gross Annual)",{"$f":"IF(D12=\"Zero Hours\",0,SUMPRODUCT(D15:D19,F15:F19))+IF(D24=\"Zero Hours\",0,SUMPRODUCT(D27:D31,F27:F31))+H20","v":0},{"$f":"IF(D12=\"Zero Hours\",0,SUMPRODUCT(E15:E19,F15:F19))+IF(D24=\"Zero Hours\",0,SUMPRODUCT(E27:E31,F27:F31))","v":0}]
[]
[null,"Other Sources of Income"]
[]
[null,null,"Does the applicants have \nany other sources of income?"]
[]
[null,null,null,"Annual","Net Monthly","Assessable"]
[null,null,"Rental Income excluding BTL properties",null,null,{"$f":"'1 Net Monthly Income'!C22","v":0.75}]
[null,null,"Profit from Self Employment",null,null,{"$f":"'1 Net Monthly Income'!C24","v":1}]
[null,null,"Income from Company pension",null,null,{"$f":"'1 Net Monthly Income'!C25","v":1}]
[null,null,"Income from State pension",null,null,{"$f":"'1 Net Monthly Income'!C26","v":1}]
[null,null,"Income from Private pension",null,null,{"$f":"'1 Net Monthly Income'!C27","v":1}]
[null,null,"Other state benefits (Net)",null,null,{"$f":"'1 Net Monthly Income'!C28","v":0.25}]
[null,null,"Investment income ",null,null,{"$f":"'1 Net Monthly Income'!C29","v":1}]
[null,null,"Dividends",null,null,{"$f":"'1 Net Monthly Income'!C30","v":1}]
[null,null,"Deposit Interest",null,null,{"$f":"'1 Net Monthly Income'!C31","v":1}]
[null,null,"Trust Income",null,null,{"$f":"'1 Net Monthly Income'!C32","v":1}]
[null,null,"Annuities",null,null,{"$f":"'1 Net Monthly Income'!C33","v":1}]
[null,null,"Child Benefit for children aged 12 or below (Net)",null,null,{"$f":"'1 Net Monthly Income'!C34","v":1},{"$f":"IF(SUM(D34,D41:D51,D54,D123*12)<=50000,D52,0)","v":0},{"$f":"IF(SUM(E34,E41:E51,E54,E123*12)<=50000,E52,0)","v":0}]
[]
[null,null,"Other Income (Net)",null,null,{"$f":"'1 Net Monthly Income'!C35","v":1}]
[]
[null,null,"Total Other Income",{"$f":"SUM(D41:D51,G52,D54)","v":0},{"$f":"SUM(E41:E51,E54,H52)","v":0},null,"Total Assessable Other Income",{"$f":"SUMPRODUCT(D41:D51,F41:F51)+G52*F52+D54*F54","v":0},{"$f":"SUMPRODUCT(E41:E51,F41:F51)+H52*F52+E54*F54","v":0}]
[]
[null,"Credit Commitments "]
[null,null,"Primary Residential Mortgage"]
[]
[null,null,"Does the applicant have existing Residential Mortage Commitment?",null,null,null,"To be repaid?"]
[null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Assessable Payment",{"$f":"(D62-H62)*'2 Existing Mortgage Commitments'!$E$20/12+D63","v":0}]
[]
[null,null,"Other Mortgages and Secured Loans"]
[]
[null,null,"Does the applicant have any other Mortgages or Secured Loans?",null,null,null,"To be repaid?"]
[null,null,"Total Outstanding Balance",null,null,null,"Balance to be repaid"]
[null,null,"Monthly Payment to remain",null,null,null,"Assessable Payment",{"$f":"(D68-H68)*'2 Existing Mortgage Commitments'!$E$20/12+D69","v":0}]
[]
[null,null,"Store and Credit Cards"]
[]
[null,null,"Does the applicant have Credit or Store Card?",null,null,null,"To be repaid?"]
[null,null,"Balance",null,null,null,"Balance to be repaid"]
[null,null,null,null,null,null,"Assessable Payment",{"$f":"(D74-H74)*'3 Revolving Credit Commitments'!D14","v":0}]
[null,null,"Loans and Hire Purchase"]
[]
[null,null,"Does the applicant have any \nUnsecured Loans or Hire Purchase?",null,null,null,"To be repaid?"]
[null,null,"Monthly Payment",null,null,null,"Payment to be repaid"]
[null,null,null,null,null,null,"Assessable Payment",{"$f":"(D79-H79)","v":0}]
[]
[null,null,"Other Commitments"]
[]
[null,null,"Does the applicant make any monthly\npension contributions or have any other\nfinancial commitments?"]
[]
[null,null,"Child / Spouse Maintenance ",null,null,null,"Pension Contribution"]
[null,null,"Childcare Vouchers",null,null,null,"Health Insurance"]
[null,null,"Child Care / Nursery Fees",null,null,null,"Insurance"]
[null,null,"Education Fees"]
[null,null,"Professional Subscriptions",null,null,null,"Other "]
[null,null,"Student Loan"]
[null,null,null,null,null,null,"Commitments to be paid off"]
[null,null,"Overall Commitments",{"$f":"IF(D84 = \"Yes\",SUM(D86:D91,H86:H90),0)-H92","v":0}]
[]
[null,"BTL Portfolio "]
[]
[null,null,"Does the applicant own any Investment / BTL properties",null,null,null,"How many Investment/BTL properties does the applicant have?"]
[]
[null,null,"Property 1:",null,null,null,"Property 4:"]
[null,null,"To be repaid?",null,null,null,"To be repaid?"]
[null,null,"Outstanding Balance",null,null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs",null,null,null,"Monthly Additional Costs"]
[null,null,"Rental Income",null,null,null,"Rental Income"]
[]
[null,null,"Property 2:",null,null,null,"Property 5:"]
[null,null,"To be repaid?",null,null,null,"To be repaid?"]
[null,null,"Outstanding Balance",null,null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs",null,null,null,"Monthly Additional Costs"]
[null,null,"Rental Income",null,null,null,"Rental Income"]
[]
[null,null,"Property 3:"]
[null,null,"To be repaid?"]
[null,null,"Outstanding Balance"]
[null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs"]
[null,null,"Rental Income"]
[]
[null,null,"Overall Outstanding Balance",{"$f":"D101+D108+D115+H108+H101","v":0}]
[null,null,"Overall Monthly Payment",{"$f":"D102+D109+D116+H109+H102","v":0},null,null,"Assessable BTL Repayment",{"$f":"MAX(D102+D101*'2 Existing Mortgage Commitments'!$E$10/12,D101*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(D109+D108*'2 Existing Mortgage Commitments'!$E$10/12,D108*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(D116+D115*'2 Existing Mortgage Commitments'!$E$10/12,D115*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(H102+H101*'2 Existing Mortgage Commitments'!$E$10/12,H101*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(H109+H108*'2 Existing Mortgage Commitments'!$E$10/12,H108*'2 Existing Mortgage Commitments'!$F$10/12)","v":0}]
[null,null,"Overall Montly additional costs",{"$f":"D103+D110+D117+H110+H103","v":0}]
[null,null,"Montly Portfolio Rental Income",{"$f":"D104+D111+D118+H111+H104","v":0},null,null,"Assessable Rental Income",{"$f":"D123*'1 Net Monthly Income'!$C$22","v":0}]
[]
[]
[null,"Gross Income"]
[]
[null,null,"Total Gross Annual Income",{"$f":"SUM(D34 + D56 +D123*12)","v":0}]
[null,null,"Total Gross Assessible Income",{"$f":"SUM(H34,H56)-SUM(D41*F41,D46*F46,D47*F47,D49*F49,D50*F50,D51*F51,G52,D54*F54)","v":0}]
[]
[null,null,"Total Net Monthly Income",{"$f":"IF('Loan Details'!H5=\"Broker\",'Tax Applicant 3'!C44+(D41*F41+D46*F46+D52*F52+D54*F54)/12+H123,\n                            IF(OR(D11=\"Self Employed\",D11=\"Employed and Self Employed\",D23=\"Self Employed\",D23=\"Employed and Self Employed\"),'Tax Applicant 3'!C44+(D41*F41+D46*F46+D52*F52+D54*F54)/12+H123,\n                                             I34+I56+H123))","v":0},null,null,"Non-Credit Monthly Commitments",{"$f":"IF(OR('Loan Details'!H5=\"Broker\",OR(D11=\"Self Employed\",D23=\"Self Employed\")),D93-H86-D91-D87,D93)","v":0}]

{"sheet":"Applicant 4","index":5,"n_rows":131,"n_cols":9,"rows_kept":131,"cut":false,"hidden":true,"merged":[[2,1,2,9],[75,2,75,8],[81,2,81,8],[94,1,94,9],[125,1,125,9],[8,1,8,9],[35,1,35,9],[58,2,58,8],[64,2,64,8],[70,2,70,8],[57,1,57,9],[10,3,10,4]]}
[{"$f":"'Loan Details'!H5","v":"Broker"}]
[]
[null,"Loan Details"]
[]
[null,null,"Date of Birth (YYYY-MM-DD)",null,null,null,"Age",{"$f":"IF(D5=\"\",\"Input DOB\",DATEDIF(D5,TODAY(),\"Y\"))","v":"Input DOB"}]
[null,null,"Estimate Retirement Age"]
[null,null,"Will the applicant share household with Applicant 1?"]
[]
[null,"Employment Income"]
[]
[null,null,"Employment Status"]
[null,null,"Type of Fixed Term"]
[]
[null,null,null,"Gross Annual","Net Monthly","Assessable",null,"Gross Annual"]
[null,null,"Basic Salary",null,null,{"$f":"'1 Net Monthly Income'!B6","v":1},"Pay from all employment(s) (Wage / Salary) Year 1"]
[null,null,"Overtime",null,null,{"$f":"'1 Net Monthly Income'!B7","v":0.5},"Pay from all employment(s) (Wage / Salary) Year 2"]
[null,null,"Commission",null,null,{"$f":"'1 Net Monthly Income'!B8","v":0.5},"Pay from all employment(s) (Wage / Salary) Year 3"]
[null,null,"Bonus",null,null,{"$f":"'1 Net Monthly Income'!B9","v":0.5}]
[null,null,"Allowance",null,null,{"$f":"'1 Net Monthly Income'!B10","v":1}]
[null,null,"Sub Total",{"$f":"IF(D12=\"Zero Hours\",0,SUM(D15:D19))","v":0},{"$f":"IF(D12=\"Zero Hours\",0,SUM(E15:E19))","v":0},null,"Assessable Income",{"$f":"H15","v":0}]
[]
[null,null,"Does the applicant have \nSecondary Employment Income?"]
[null,null,"Secondary Employment Status"]
[null,null,"Type of Fixed Term"]
[]
[null,null,null,"Gross Annual","Net Monthly","Assessable"]
[null,null,"Basic Salary",null,null,{"$f":"'1 Net Monthly Income'!B6","v":1}]
[null,null,"Overtime",null,null,{"$f":"'1 Net Monthly Income'!B7","v":0.5}]
[null,null,"Commission",null,null,{"$f":"'1 Net Monthly Income'!B8","v":0.5}]
[null,null,"Bonus",null,null,{"$f":"'1 Net Monthly Income'!B9","v":0.5}]
[null,null,"Allowance",null,null,{"$f":"'1 Net Monthly Income'!B10","v":1}]
[null,null,"Sub Total",{"$f":"IF(D24=\"Zero Hours\",0,SUM(D27:D31))","v":0},{"$f":"IF(D24=\"Zero Hours\",0,SUM(E27:E31))","v":0}]
[]
[null,null,"Total Income (Gross Annual)",{"$f":"D20+H20+D32","v":0},{"$f":"E20+I20+E32+I32","v":0},null,"Total Assessible Income (Gross Annual)",{"$f":"IF(D12=\"Zero Hours\",0,SUMPRODUCT(D15:D19,F15:F19))+IF(D24=\"Zero Hours\",0,SUMPRODUCT(D27:D31,F27:F31))+H20","v":0},{"$f":"IF(D12=\"Zero Hours\",0,SUMPRODUCT(E15:E19,F15:F19))+IF(D24=\"Zero Hours\",0,SUMPRODUCT(E27:E31,F27:F31))","v":0}]
[]
[null,"Other Sources of Income"]
[]
[null,null,"Does the applicants have \nany other sources of income?"]
[]
[null,null,null,"Annual","Net Monthly","Assessable"]
[null,null,"Rental Income excluding BTL properties",null,null,{"$f":"'1 Net Monthly Income'!C22","v":0.75}]
[null,null,"Profit from Self Employment",null,null,{"$f":"'1 Net Monthly Income'!C24","v":1}]
[null,null,"Income from Company pension",null,null,{"$f":"'1 Net Monthly Income'!C25","v":1}]
[null,null,"Income from State pension",null,null,{"$f":"'1 Net Monthly Income'!C26","v":1}]
[null,null,"Income from Private pension",null,null,{"$f":"'1 Net Monthly Income'!C27","v":1}]
[null,null,"Other state benefits (Net)",null,null,{"$f":"'1 Net Monthly Income'!C28","v":0.25}]
[null,null,"Investment income ",null,null,{"$f":"'1 Net Monthly Income'!C29","v":1}]
[null,null,"Dividends",null,null,{"$f":"'1 Net Monthly Income'!C30","v":1}]
[null,null,"Deposit Interest",null,null,{"$f":"'1 Net Monthly Income'!C31","v":1}]
[null,null,"Trust Income",null,null,{"$f":"'1 Net Monthly Income'!C32","v":1}]
[null,null,"Annuities",null,null,{"$f":"'1 Net Monthly Income'!C33","v":1}]
[null,null,"Child Benefit for children aged 12 or below (Net)",null,null,{"$f":"'1 Net Monthly Income'!C34","v":1},{"$f":"IF(SUM(D34,D41:D51,D54,D123*12)<=50000,D52,0)","v":0},{"$f":"IF(SUM(E34,E41:E51,E54,E123*12)<=50000,E52,0)","v":0}]
[]
[null,null,"Other Income (Net)",null,null,{"$f":"'1 Net Monthly Income'!C35","v":1}]
[]
[null,null,"Total Other Income",{"$f":"SUM(D41:D51,G52,D54)","v":0},{"$f":"SUM(E41:E51,E54,H52)","v":0},null,"Total Assessable Other Income",{"$f":"SUMPRODUCT(D41:D51,F41:F51)+G52*F52+D54*F54","v":0},{"$f":"SUMPRODUCT(E41:E51,F41:F51)+H52*F52+E54*F54","v":0}]
[]
[null,"Credit Commitments "]
[null,null,"Primary Residential Mortgage"]
[]
[null,null,"Does the applicant have existing Residential Mortage Commitment?",null,null,null,"To be repaid?"]
[null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Assessable Payment",{"$f":"(D62-H62)*'2 Existing Mortgage Commitments'!$E$20/12+D63","v":0}]
[]
[null,null,"Other Mortgages and Secured Loans"]
[]
[null,null,"Does the applicant have any other Mortgages or Secured Loans?",null,null,null,"To be repaid?"]
[null,null,"Total Outstanding Balance",null,null,null,"Balance to be repaid"]
[null,null,"Monthly Payment to remain",null,null,null,"Assessable Payment",{"$f":"(D68-H68)*'2 Existing Mortgage Commitments'!$E$20/12+D69","v":0}]
[]
[null,null,"Store and Credit Cards"]
[]
[null,null,"Does the applicant have Credit or Store Card?",null,null,null,"To be repaid?"]
[null,null,"Balance",null,null,null,"Balance to be repaid"]
[null,null,null,null,null,null,"Assessable Payment",{"$f":"(D74-H74)*'3 Revolving Credit Commitments'!D14","v":0}]
[null,null,"Loans and Hire Purchase"]
[]
[null,null,"Does the applicant have any \nUnsecured Loans or Hire Purchase?",null,null,null,"To be repaid?"]
[null,null,"Monthly Payment",null,null,null,"Payment to be repaid"]
[null,null,null,null,null,null,"Assessable Payment",{"$f":"(D79-H79)","v":0}]
[]
[null,null,"Other Commitments"]
[]
[null,null,"Does the applicant make any monthly\npension contributions or have any other\nfinancial commitments?"]
[]
[null,null,"Child / Spouse Maintenance ",null,null,null,"Pension Contribution"]
[null,null,"Childcare Vouchers",null,null,null,"Health Insurance"]
[null,null,"Child Care / Nursery Fees",null,null,null,"Insurance"]
[null,null,"Education Fees"]
[null,null,"Professional Subscriptions",null,null,null,"Other "]
[null,null,"Student Loan"]
[null,null,null,null,null,null,"Commitments to be paid off"]
[null,null,"Overall Commitments",{"$f":"IF(D84 = \"Yes\",SUM(D86:D91,H86:H90),0)-H92","v":0}]
[]
[null,"BTL Portfolio "]
[]
[null,null,"Does the applicant own any Investment / BTL properties",null,null,null,"How many Investment/BTL properties does the applicant have?"]
[]
[null,null,"Property 1:",null,null,null,"Property 4:"]
[null,null,"To be repaid?",null,null,null,"To be repaid?"]
[null,null,"Outstanding Balance",null,null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs",null,null,null,"Monthly Additional Costs"]
[null,null,"Rental Income",null,null,null,"Rental Income"]
[]
[null,null,"Property 2:",null,null,null,"Property 5:"]
[null,null,"To be repaid?",null,null,null,"To be repaid?"]
[null,null,"Outstanding Balance",null,null,null,"Outstanding Balance"]
[null,null,"Monthly Payment",null,null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs",null,null,null,"Monthly Additional Costs"]
[null,null,"Rental Income",null,null,null,"Rental Income"]
[]
[null,null,"Property 3:"]
[null,null,"To be repaid?"]
[null,null,"Outstanding Balance"]
[null,null,"Monthly Payment"]
[null,null,"Monthly Additional Costs"]
[null,null,"Rental Income"]
[]
[null,null,"Overall Outstanding Balance",{"$f":"D101+D108+D115+H108+H101","v":0}]
[null,null,"Overall Monthly Payment",{"$f":"D102+D109+D116+H109+H102","v":0},null,null,"Assessable BTL Repayment",{"$f":"MAX(D102+D101*'2 Existing Mortgage Commitments'!$E$10/12,D101*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(D109+D108*'2 Existing Mortgage Commitments'!$E$10/12,D108*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(D116+D115*'2 Existing Mortgage Commitments'!$E$10/12,D115*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(H102+H101*'2 Existing Mortgage Commitments'!$E$10/12,H101*'2 Existing Mortgage Commitments'!$F$10/12)+MAX(H109+H108*'2 Existing Mortgage Commitments'!$E$10/12,H108*'2 Existing Mortgage Commitments'!$F$10/12)","v":0}]
[null,null,"Overall Montly additional costs",{"$f":"D103+D110+D117+H110+H103","v":0}]
[null,null,"Montly Portfolio Rental Income",{"$f":"D104+D111+D118+H111+H104","v":0},null,null,"Assessable Rental Income",{"$f":"D123*'1 Net Monthly Income'!$C$22","v":0}]
[]
[]
[null,"Gross Income"]
[]
[null,null,"Total Gross Annual Income",{"$f":"SUM(D34 + D56 +D123*12)","v":0}]
[null,null,"Total Gross Assessible Income",{"$f":"SUM(H34,H56)-SUM(D41*F41,D46*F46,D47*F47,D49*F49,D50*F50,D51*F51,G52,D54*F54)","v":0}]
[]
[null,null,"Total Net Monthly Income",{"$f":"IF('Loan Details'!H5=\"Broker\",'Tax Applicant 4'!C44+(D41*F41+D46*F46+D52*F52+D54*F54)/12+H123,\n                            IF(OR(D11=\"Self Employed\",D11=\"Employed and Self Employed\",D23=\"Self Employed\",D23=\"Employed and Self Employed\"),'Tax Applicant 4'!C44+(D41*F41+D46*F46+D52*F52+D54*F54)/12+H123,\n                                             I34+I56+H123))","v":0},null,null,"Non-Credit Monthly Commitments",{"$f":"IF(OR('Loan Details'!H5=\"Broker\",OR(D11=\"Self Employed\",D23=\"Self Employed\")),D93-H86-D91-D87,D93)","v":0}]

{"sheet":"Household Expenditure","index":6,"n_rows":17,"n_cols":8,"rows_kept":17,"cut":false,"hidden":false,"merged":[[2,1,2,9]]}
[{"$f":"'Loan Details'!D12","v":0}]
[]
[null,"Household Expenditure - One Per Application"]
[]
[null,null,"Number of Applicants  in the same household",{"$f":"1+IF('Applicant 2'!D7=\"Yes\",1,0)+IF('Applicant 3'!D7=\"Yes\",1,0)+IF('Applicant 4'!D7=\"Yes\",1,0)","v":1},null,null,"Number of Other Applicants",{"$f":"Number_of_Applicants-'Household Expenditure'!D5","v":-1}]
[null,null,"Number of Dependants",{"$f":"Input!H16","v":0}]
[null,null,"Total Household",{"$f":"D5+D6","v":1}]
[]
[null,null,"Rates / Local authority charges",{"$f":"Input!D89","v":0}]
[null,null,"Rental Service Charge",{"$f":"Input!H12","v":0}]
[null,null,"Other Household Expenditure",{"$f":"Input!H89","v":0}]
[]
[]
[null,null,"Total Cost of Living",{"$f":"CostOfLiving!I13","v":505}]
[null,null,"Total Household Expenditure",{"$f":"D9+D11+D14+D10","v":505},null,null,"Other Applicants Expenditure",{"$f":"SUM(CostOfLiving!I14:I16)","v":0}]
[]
[null,null,"Total Application Expenditure",{"$f":"D15+H15","v":505}]

{"sheet":"Summary Calculations","index":7,"n_rows":42,"n_cols":8,"rows_kept":42,"cut":false,"hidden":false,"merged":[[2,1,2,9],[28,1,28,9],[35,1,35,9]]}
[]
[]
[null,"Total Income Calculation"]
[]
[null,null,"Total Gross Annual Income",{"$f":"'Applicant 1'!D128+'Applicant 2'!D128+'Applicant 3'!D128+'Applicant 4'!D128","v":0}]
[null,null,"Total Net Assessable Monthly Income",{"$f":"'Applicant 1'!D131+'Applicant 2'!D131+'Applicant 3'!D131+'Applicant 4'!D131","v":0}]
[]
[null,null,"Stressed Existing Mortgage / BTL Commitments",{"$f":"'Applicant 1'!H63+'Applicant 1'!H69+'Applicant 1'!H121+'Applicant 2'!H63+'Applicant 2'!H69+'Applicant 2'!H121+'Applicant 3'!H63+'Applicant 3'!H69+'Applicant 3'!H121+'Applicant 4'!H63+'Applicant 4'!H69+'Applicant 4'!H121","v":0}]
[null,null,"Existing Properties Associated Costs",{"$f":"'Applicant 1'!D122+'Applicant 2'!D122+'Applicant 3'!D122+'Applicant 4'!D122","v":0}]
[]
[null,null,"Revolving Credit Commitments",{"$f":"'Applicant 1'!H75+'Applicant 2'!H75+'Applicant 3'!H75+'Applicant 4'!H75","v":0}]
[null,null,"Non-Revolving Credit Commitments",{"$f":"'Applicant 1'!H80+'Applicant 2'!H80+'Applicant 3'!H80+'Applicant 4'!H80","v":0}]
[null,null,"Non-Credit Monthly Commitments",{"$f":"'Applicant 1'!H131+'Applicant 2'!H131+'Applicant 3'!H131+'Applicant 4'!H131","v":0}]
[null,null,"Household Expenditure",{"$f":"'Household Expenditure'!D17","v":505}]
[]
[null,null,"Stressed New Mortgage payment ",{"$f":"IF(D34>0,D34+H34,D33+H34)","v":{"$error":"#NUM!"}}]
[]
[null,null,"Help to Buy: Equity Loan stress payment",{"$f":"('Stress Rates'!B22 * ('Loan Details'!H14 * 'Loan Details'!H7)) / 12","v":0}]
[]
[null,null,"Disposable Income",{"$f":"D6-SUM(D8:D18)","v":{"$error":"#NUM!"}}]
[null,null,"Disposable Income Ratio",{"$f":"D20/D6","v":{"$error":"#NUM!"}}]
[]
[null,null,"Debt Service Commitment with initial monthly Payment",{"$f":"IF('Loan Details'!H5=\"Direct\", IF(AND('Applicant 1'!D11<>\"Self Employed\",'Applicant 1'!D11<>\"Employed and Self Employed\"),'Applicant 1'!D91,0)+\n                                                                + IF(AND('Applicant 2'!D11<>\"Self Employed\",'Applicant 2'!D11<>\"Employed and Self Employed\"),'Applicant 2'!D91,0)+\n                                                                 + IF(AND('Applicant 3'!D11<>\"Self Employed\",'Applicant 3'!D11<>\"Employed and Self Employed\"),'Applicant 3'!D91,0)+\n                                                                 + IF(AND('Applicant 4'!D11<>\"Self Employed\",'Applicant 4'!D11<>\"Employed and Self Employed\"),'Applicant 4'!D91,0),0)\n+'Applicant 1'!D86+'Applicant 1'!D63+'Applicant 1'!D69+'Applicant 1'!D121+'Applicant 2'!D86+'Applicant 2'!D63+'Applicant 2'!D69+'Applicant 2'!D121+'Applicant 3'!D86+'Applicant 3'!D63+'Applicant 3'!D69+'Applicant 3'!D121+'Applicant 4'!D86+'Applicant 4'!D63+'Applicant 4'!D69+'Applicant 4'!D121+D33+D11+D12+'Household Expenditure'!D10","v":{"$error":"#NUM!"}}]
[null,null,"Debt Servicing Ratio",{"$f":"D23/D6","v":{"$error":"#NUM!"}}]
[]
[null,null,"Loan to Income Ratio",{"$f":"'Loan Details'!H8/D5","v":{"$error":"#DIV/0!"}}]
[null,null,"Loan to Value",{"$f":"'Loan Details'!L7","v":0}]
[]
[null," Loan Details"]
[]
[null,null,"Term of Mortgage (Years)",{"$f":"'Loan Details'!D8+'Loan Details'!D9/12","v":0}]
[]
[null,null,"New Mortgage Payment ",{"$f":"PMT('Loan Details'!H11/365,$D$31*365,-'Loan Details'!$H$8)*365/12","v":{"$error":"#NUM!"}}]
[null,null,"Follow-On Mortgage Payment",{"$f":"IF('Loan Details'!H12>0,PMT('Loan Details'!H12/365,$D$31*365,-'Loan Details'!$H$8)*365/12,0)","v":{"$error":"#NUM!"}},null,null,"Monthly Sensitivity after Fixed Rate Period",{"$f":"PMT((VLOOKUP('Loan Details'!$H$10,'Stress Rates'!$A$12:$B$20,2,0)+IF('Loan Details'!$H$12>0,'Loan Details'!$H$12,'Loan Details'!$H$11))/365,$D$31*365,-'Loan Details'!H8)*365/12-IF($D$34<>0,$D$34,$D$33)","v":{"$error":"#N/A"}}]
[]
[null,"Maximum Loan Amount Calculations"]
[]
[null,null,"Max LTI",{"$f":"IF($D$5<50000,IF('Loan Details'!L7>0.9,'Stress Rates'!$D$30*$D$5,'Stress Rates'!$C$30*$D$5),IF('Loan Details'!L7>0.9,'Stress Rates'!$D$31*$D$5,'Stress Rates'!$C$31*$D$5))","v":0},null,null,"Max Loan Amount",{"$f":"PV(('Stress Rates'!$B$15+'Stress Rates'!$B$26)/365,'Summary Calculations'!$D$31*365,-(D6-SUM(D8:D14,D18)-Disp_Inc_Min)/(365/12))","v":0}]
[null,null,null,null,null,null,null," "]
[null,null,"Maximum Debt Service Commitment",{"$f":"IF('Loan Details'!L7>=90%,(D6*'Stress Rates'!E12)-D23,(D6*'Stress Rates'!E14)-D23)","v":{"$error":"#NUM!"}},null,null,"Maximum Loan Amount based on Debt Servicing Ratio",{"$f":"-PV('Loan Details'!H11, D31, D40 *12)","v":{"$error":"#NUM!"}}]
[]
[null,null,"Maximum Loan Payment based on Disposable Income Ratio",{"$f":"D6 * (1 - 'Stress Rates'!#REF!) -SUM(D8:D14)","v":{"$error":"#REF!"}},null,null,"Maximum Loan Amount based on Disposable Income Ratio",{"$f":"-PV('Loan Details'!H11, D31, D42 *12)","v":{"$error":"#REF!"}}]

{"sheet":"Version control","index":8,"n_rows":32,"n_cols":4,"rows_kept":32,"cut":false,"hidden":true,"merged":[]}
[]
["Version number","Date of change","Shortname","Brief description of change"]
["v7.3","2020-07-06","BMCG ","> created version control\n> changed DI ratio check in 'Rates' to 5% instead of 2.5%\n> Changed 'Applicant 1' and 'Applicant 2' where If either Applicant 1 or 2 have total gross income > 50k then child benefit is zero for both monthly and yearly figures "]
["v7.35","2020-07-06","BMCG ","> fixed the follow on mortgage payment if there is no follow on payment pulled through "]
["v7.4","2020-07-07","YSO","> added rates for higher LTV and remortgage for existing customers\n> added product Remortgage - Internal (Loan Details)"]
["v7.45","2020-07-07","YSO","> fixed term fields for Remortgage - Internal\n> child benefit is not calculated in totals for customers with gross income >50k. If Applicant 1 and Applicant 2 will be sharing household, their total Total gross income is calculated for this, otherwise only one applicant's income"]
["v7.46","2020-07-07","YSO","> Fixed Total deductions calculation on Tax sheets"]
["v7.5","2020-07-08","YSO","> Fixed Sensitivity calculation\n> Employment: Fixed Term Contract- the fields for salary input appear only if type of fixed term chosen\n> Fixed Zero hours assessable net income calculation\n> Fixed issue with rate not pulling through for LTV>94,5\n> Added Rental service charge to Debt Service Commitment with initial monthly Payment\n> Rental service charge field is updated not only when 'Reset Household' is pressed, but also when rates inputed  (couldn't find e better solution yet)"]
["v7.51","2020-07-08","YSO","Fixed interest rate input for SVR"]
["v7.55","2020-07-09","YSO","> Rental service charge field (household Expenditure) activates/deactivates when Coownership status changes\n> Income Earned as Partner or Sole Proprietor is included in self employed income for national insurance calculation\n> Other sources of income: If Direct and Self Employed / Employed and self employed, Net monthly fields are unavailable for input"]
["v7.56","2020-07-14","YSO","> Fixed non-credit monthly commitments on Summary calculations sheet - now pulling through assessable payment "]
["v7.57","2020-07-14","YSO","> Added child/spouse maintenance and student loan to the calculation of debt service commitment"]
["v7.58","2020-07-14","YSO","> Student loan is added to the calculation of debt service commitment only for Direct channel"]
["v7.6","2020-07-16","YSO","> Self-employed income section: changed the wording for self employed income (year 1-3), set Assessable income = Year 1 income\n> Other sources of income: “Income earned as Partner / Sole Proprietor” changed to  “Profit from Self Employment”\n> Fixed Household expenditure calculation (corrected the link to Applicants 2,3,4 gross income on ONS sheet)\n> Fixed Zero hours assessable net income calculation for secondary employment"]
["v7.65","2020-07-21","BMCG ","> Changed the wording on Tax Applicant x sheets from 'Income Earned as Partner or Sole Proprieter' to 'Profit from Self Employment'\n> Changed the Self Employed national insurance to be based on just the Profit from Self Employment\n> National Insurance from Salary within 'Tax Applicant x' sheet is now based on 'Basic Salary' and  'Pay from all employment(s) (Wage/Salary) Year 1) '\n> Renamed 'Self Employed National Insurance Income*' to 'Self Employed Profit National Insurance Income*' in Tax Applicant x"]
["v7.7","2020-07-21","YSO","> Spelling corrections"]
["v7.8","2020-07-29","YSO","> Debt Cervice Commitment calculation: Student loan is included only for Direct channel and not Self Employed"]
["v7.9","2020-07-30","YSO","> Updated Tax calculation sheets in line with what Neil implemented for Employed and Self employed"]
["v8","2020-07-31","YSO","> Protected the workbook "]
["v8.1","2020-08-28","YSO","> Removed Other Income (Net) from the tax calculation"]
["v10 ","2021-03-25","BMCG ","> Updated cost of living calculation ONS(new)(2) (will remove previous ONS when testing is complete )\n> introduced the stressing of 5-year fixed mortgages\n> removes DSR calculation formatting from 'Summary Calculations' sheet\n> removes DI ratio calculation formatting from 'Summary Calculations' sheet"]
["v10.1","2021-04-09","BMCG ","> fixed issue on stressed mortgage calculation not pullin correct stress through"]
["v10.1.1","2021-04-13","BMCG ","> updated rates sheet with interest rates from \n> updated tax rates for 2021/22"]
["v10.1.5","2021-04-15","BMCG ","> Unneccessary sheets hidden"]
["v10.2","2021-05-25","BMCG ","> Updated stress rates on 5 year fixed products to 2% (2/3 year fixed at 3%)"]
["v11","2021-06-03","BMCG ","> Update the Gross Assessible income on Applicant sheets to exclude specific other incomes"]
["v12","2021-06-10","ARSTR","> Added Input sheet, created formulas upload into Loan Details, Applicant 1, Applicant 2, Household expenditure based on select value in a sheet Input"]
["v12.1","2021-06-14","BMCG ","> updated macros on Input sheet so that changing the number of applicants alters formulas in other sheets"]
["v12.2","2021-06-24","BMCG ","> updated student loan payment to take only what the customer declares in working out Net Monthly Income"]
["v13","2021-06-25","BMCG ","> created 'Rates_upd' sheet for new input of Carbon Neutral products\n> within the 'Rates_upd' sheet there is scope for new products both fixed rate and base rate tracker"]
["v13.1","2021-07-06","BMCG ","> Added in BRT - Carbon neutral product to drop down"]
["v14","2021-08-16","BMCG ","> Added new Shared Ownership roduct line to the calculator\n> Updated discounts on Bonus Overtime and Commission\n> updated Input sheet to allow 35 years\n> updated Loan details sheet to allow 35 years\n> updated mortgage payments to reflect updated payments in CDE"]

{"sheet":"Stress Rates","index":9,"n_rows":31,"n_cols":5,"rows_kept":31,"cut":false,"hidden":true,"merged":[[4,1,6,1],[4,4,6,4],[7,1,9,1],[7,4,9,4]]}
["7 New Mortgage Commitments"]
[]
[]
["New Product applied for ","Borrower Type","E1B07","Stress Rate Additional Borrowing","Stress Rate No Additional Borrowing "]
["Variable rate (BRT or SVR) Residential Mortgage, including self builds and additional variable rate RMCs.","Other than Re-Mortgage","All","Existing variable rate plus 3%","N/A"]
["2 or 3 year fixed rate residential mortgage /additional 2 and 3 year fixed rate RMCs",null,"All","Follow on (reversion) rate plus 3%"]
["5 year fixed rate residential mortgage / additional 5 year fixed RMCs",null,"All","No stress"]
["Variable rate (BRT or SVR) Residential Mortgage, including self builds and additional variable rate RMCs.","Re-Mortgage","All","Existing variable rate plus 3%","N/A (but to be built in for future use)"]
["2 or 3 year fixed rate residential mortgage /additional 2 and 3 year fixed rate RMCs",null,"All","Follow on (reversion) rate plus 3%"]
["5 year fixed rate residential mortgage / additional 5 year fixed RMCs",null,"All","No stress"]
[]
["Variable rate (BRT)",0.03,null,"Variable rate (BRT)",0.03]
["Variable rate (BRT) - Carbon Neutral",0.03,null,"Variable rate (BRT) - Carbon Neutral",0.03]
["Variable rate (SVR)",0.03,null,"2 year fixed rate",0.03]
["2 year fixed rate",0.03,null,"5 year fixed rate",0.02]
["3 year fixed rate",0.03,null,"2 year fixed rate - Carbon Neutral",0.03]
["5 year fixed rate",0.02,null,"5 year fixed rate - Carbon Neutral",0.02]
["2 year fixed rate - Carbon Neutral",0.03]
["3 year fixed rate - Carbon Neutral",0.03]
["5 year fixed rate - Carbon Neutral",0.02]
[]
["Help to Buy: Equity Loan Stress Rate",0.03]
[]
["Disposable Income value",50]
[]
["Follow-On Rate",3.3000000000000002E-2]
[]
[null,null,"LTV"]
[null,null,"<=90%",">90%"]
["Income","<£50,000",4.5,4.25]
[null,">=£50,000",5,4.25]

{"sheet":"CostOfLiving","index":10,"n_rows":20,"n_cols":11,"rows_kept":20,"cut":false,"hidden":true,"merged":[[1,9,1,10],[1,6,1,7],[1,3,1,5],[1,1,1,2]]}
[]
[null,"Model Details",null,"Polynomial Coefficients",null,null,"Power Curve Coefficients",null,"Dependant Increase Rate","Mangement Overlays"]
[null,"Model Name","Model Form","a2","a1","a0","c","m",null,"Floor","Ceiling"]
[null,"Danske 1 Adult 0 Dependant ","y = a2*x^2 + a1*a1 + a0",-6.173640628725404E-8,1.3469420101887199E-2,425.96645740292121,"N/A","N/A","N/A",505,1150]
[null,"Danske 2 Adult 0 Dependant ","y = c*x^m","N/A","N/A","N/A",8.4569786620158354,-0.32809036632335026,"N/A",850,1375]
[null,"Danske Additional Dependant cost ","y = #Dependants*(Expenditure 1 Adult 0 Dependan)*16.8% + Adult Expenditure","N/A","N/A","N/A","N/A","N/A",0.16800000000000001,"N/A","N/A"]
[]
[]
[null,"Number of Adults",{"$f":"MIN('Household Expenditure'!D5,2)","v":1}]
[null,"Number of Dependants",{"$f":"'Household Expenditure'!D6","v":0}]
[]
[null,null,"Gross Assessible Income",null,null,"1 Adult 0 Dependnt Cost ","Dependant cost ","Additional Adult ratio increase","Final Cost"]
[null,"Main Household Income",{"$f":"'Applicant 1'!D129 + IF('Applicant 2'!D7=\"Yes\",'Applicant 2'!D129,0)","v":0},null,"Main Household Expenditure",{"$f":"IF(AND($C13>=7000,$C13<=94000),$D$4*$C13^2 +$E$4*$C13 + $F$4,IF($C13<7000,$J$4,$K$4))","v":505},{"$f":"$C$10*$I$6*$F$13","v":0},{"$f":"(1+$G$5*C13^$H$5)","v":{"$error":"#DIV/0!"}},{"$f":"CEILING(IF($C$9=1,$F$13 + $G$13,IF($C$9=2,IF(AND($C$13>=16000,$C$13<=94000),$F$13*$H$13,IF($C$13<16000,$J$5,$K$5))+$G$13)),5)","v":505}]
[null,"Co-Applicant 1 Income",{"$f":"IF('Applicant 2'!D7<>\"Yes\",'Applicant 2'!D129,0)","v":0},null,"Co-Applicant 1 Expenditure",{"$f":"IF(AND($C14>=7000,$C14<=94000),$D$4*$C14^2 +$E$4*$C14 + $F$4,IF($C14<7000,$J$4,$K$4))","v":505},null,null,{"$f":"CEILING(IF(OR('Applicant 2'!D7=\"Yes\", Number_of_Applicants<2),0,$F$14),5)","v":0}]
[null,"Co-Applicant 2 Income",{"$f":"IF('Applicant 3'!D8<>\"Yes\",'Applicant 3'!D129,0)","v":0},null,"Co-Applicant 2 Expenditure",{"$f":"IF(AND($C15>=7000,$C15<=94000),$D$4*$C15^2 +$E$4*$C15 + $F$4,IF($C15<7000,$J$4,$K$4))","v":505},null,null,{"$f":"CEILING(IF(Number_of_Applicants<3,0,$F$15),5)","v":0}]
[null,"Co-Applicant 3 Income",{"$f":"IF('Applicant 4'!D9<>\"Yes\",'Applicant 4'!D129,0)","v":0},null,"Co-Applicant 3 Expenditure",505,null,null,{"$f":"CEILING(IF(Number_of_Applicants<4,0,$F$16),5)","v":0}]
[]
[]
[]
[null,null,null,null,"Total Cost of Living Costs",{"$f":"SUM($I$13:$I$16)","v":505}]

{"sheet":"Rates","index":11,"n_rows":48,"n_cols":17,"rows_kept":48,"cut":false,"hidden":true,"merged":[[2,2,2,5],[4,2,4,4],[4,5,29,5],[4,7,4,9],[4,10,29,10],[22,2,29,2],[22,7,29,7],[6,2,13,2],[6,7,13,7],[14,2,21,2],[14,7,21,7],[47,2,47,3],[35,2,35,6],[39,2,39,5],[41,2,46,2],[4,12,4,14],[6,12,13,12],[14,12,21,12],[22,12,29,12]]}
[null,null,null,null,"Purchase",null,null,null,null,"Remortgage",null,null,null,null,"Remortgage - Internal"]
[null,null,null,null,4,null,null,null,null,9,null,null,null,null,14]
[null,null,"Residential Fixed Rates"]
[]
[null,null,"Purchaser Rates \n(First Time Buyer & Home Mover)",null,null,"Min. Loan Amount (Mortgage): \n\n£30k+\n(£10k for Existing HL Customers)\n\nMin. Loan Amount (Additional Borrowing):\n\n£5k - (existing mortgage customer)\n£30k - (unencumbered property)",null,"Remortgager Rates \n(Switcher)",null,null,"Min. Loan Amount (Mortgage): \n\n£30k+\n(£10k for Existing HL Customers)\n\nMin. Loan Amount (Additional Borrowing):\n\n£5k - (existing mortgage customer)\n£30k - (unencumbered property)",null,"Existing Customers"]
[null,null,"Product","LTV Tier","Rate",null,null,"Product","LTV Tier","Rate",null,null,"Product","LTV Tier","Rate"]
["2 year fixed rate",0,"2 Yr Fixed Rates 'FeeSaver'\n","Up to 60%",1.7000000000000001E-2,null,null,"2 Yr Fixed Rates 'FeeSaver'\n","Up to 60%",1.4500000000000001E-2,null,null,"2 Yr Fixed Rates 'FeeSaver'\n","Up to 60%",1.4500000000000001E-2]
[null,0.60499999999999998,null,"61-70%",1.9599999999999999E-2,null,null,null,"61-70%",1.67E-2,null,null,null,"61-70%",1.7899999999999999E-2]
[null,0.70499999999999996,null,"71-75%",1.9599999999999999E-2,null,null,null,"71-75%",1.67E-2,null,null,null,"71-75%",1.7899999999999999E-2]
[null,0.755,null,"76-80%",2.3E-2,null,null,null,"76-80%",2.2800000000000001E-2,null,null,null,"76-80%",2.3099999999999999E-2]
[null,0.80500000000000005,null,"81-85%",2.5899999999999999E-2,null,null,null,"81-85%",2.8400000000000002E-2,null,null,null,"81-85%",2.58E-2]
[null,0.85499999999999998,null,"86-90%",2.9000000000000001E-2,null,null,null,"86-90%",3.4099999999999998E-2,null,null,null,"86-90%",3.1899999999999998E-2,null,100]
[null,0.90500000000000003,null,"91-95%",3.5400000000000001E-2,null,null,null,"91-95%",4.65E-2,null,null,null,"91-95%",3.5799999999999998E-2]
[null,0.95499999999999996,null,"Over 95%",null,null,null,null,"Over 95%",null,null,null,null,"Over 95%",3.9800000000000002E-2]
["3 year fixed rate",0,"3 Yr Fixed Rates 'FeeSaver'\n","Up to 60%","n/a",null,null,"3 Yr Fixed Rates 'FeeSaver'\n","Up to 60%","n/a",null,null,"3 Yr Fixed Rates 'FeeSaver'","Up to 60%","n/a"]
[null,0.60499999999999998,null,"61-70%","n/a",null,null,null,"61-70%","n/a",null,null,null,"61-70%","n/a"]
[null,0.70499999999999996,null,"71-75%","n/a",null,null,null,"71-75%","n/a",null,null,null,"71-75%","n/a"]
[null,0.755,null,"76-80%","n/a",null,null,null,"76-80%","n/a",null,null,null,"76-80%","n/a"]
[null,0.80500000000000005,null,"81-85%","n/a",null,null,null,"81-85%","n/a",null,null,null,"81-85%","n/a"]
[null,0.85499999999999998,null,"86-90%","n/a",null,null,null,"86-90%","n/a",null,null,null,"86-90%","n/a"]
[null,0.90500000000000003,null,"91-95%","n/a",null,null,null,"91-95%","n/a",null,null,null,"91-95%","n/a"]
[null,0.95499999999999996,null,"Over 95%",null,null,null,null,"Over 95%",null,null,null,null,"Over 95%"]
["5 year fixed rate",0,"5 Yr Fixed Rates 'FeeSaver'\n","Up to 60%",1.4500000000000001E-2,null,null,"5 Yr Fixed Rates 'FeeSaver'\n","Up to 60%",1.61E-2,null,null,"5 Yr Fixed Rates 'FeeSaver'","Up to 60%",1.5699999999999999E-2]
[null,0.60499999999999998,null,"61-70%",1.6899999999999998E-2,null,null,null,"61-70%",1.8700000000000001E-2,null,null,null,"61-70%",1.8599999999999998E-2]
[null,0.70499999999999996,null,"71-75%",1.6899999999999998E-2,null,null,null,"71-75%",1.8700000000000001E-2,null,null,null,"71-75%",1.9E-2]
[null,0.755,null,"76-80%",2.2700000000000001E-2,null,null,null,"76-80%",2.4199999999999999E-2,null,null,null,"76-80%",2.3300000000000001E-2]
[null,0.80500000000000005,null,"81-85%",2.7400000000000001E-2,null,null,null,"81-85%",2.9000000000000001E-2,null,null,null,"81-85%",2.9000000000000001E-2]
[null,0.85499999999999998,null,"86-90%",3.3000000000000002E-2,null,null,null,"86-90%",3.6299999999999999E-2,null,null,null,"86-90%",3.2099999999999997E-2]
[null,0.90500000000000003,null,"91-95%",3.7999999999999999E-2,null,null,null,"91-95%",4.65E-2,null,null,null,"91-95%",3.9E-2]
[null,0.95499999999999996,null,"Over 95%",null,null,null,null,"Over 95%",null,null,null,null,"Over 95%",4.2000000000000003E-2]
[]
[null,null,"Danske Follow-on Rate",3.3000000000000002E-2]
[]
[]
[]
[null,null,"Base Rate Tracker and Danske Re:pay Rates "]
[]
[null,null,"Danske Bank Base Rate (UK)",1E-3]
[]
[null,null,"Base Rate Tracker"]
[null,null,"Product Name","LTV Tier","Product","Rate"]
["Variable rate (BRT)",0,"Base Rate Tracker","Up to 60%","DBRR (UK) + 2.19%",2.29E-2]
[null,0.60499999999999998,null,"61-80%","DBRR (UK) + 2.69%",2.7900000000000001E-2]
[null,0.80500000000000005,null,"81-85%","DBRR (UK) + 3.39%",3.49E-2]
[null,0.85499999999999998,null,"86-90%","DBRR (UK) + 4.29%",4.3900000000000002E-2]
[null,0.90500000000000003,null,"91-95%","DBRR (UK) + 4.69%",4.7899999999999998E-2]
[null,0.95499999999999996,null,"Over 95%","DBRR (UK) + 5.01%",5.11E-2]
[null,null,"N.B. IPL BRT product rates on IPL sheet"]

{"sheet":"Rates_upd","index":12,"n_rows":204,"n_cols":21,"rows_kept":204,"cut":false,"hidden":true,"merged":[[17,18,18,18],[17,5,17,12],[17,1,18,1],[17,2,18,2],[17,3,18,3],[17,16,18,16],[17,17,18,17]]}
[]
[null,"Product Type","Loan Purchase","Loan To Value","Term",null,null,null,null,null,null,null,null,null,null,null,"Product Type","Loan Purchase","Loan To Value"]
[null,"Normal","Purchase","Up to 60%","2 year",null,null,null,null,null,null,null,null,null,null,null,"Normal","Purchase","Up to 60%"]
[null,"Carbon Neutral","Remortgage","61-70%","3 year",null,null,null,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage","61-80%"]
[null,null,"Remortgage - Internal","71-75%","5 year",null,null,null,null,null,null,null,null,null,null,null,null,"Remortgage - Internal","81-85%"]
[null,null,"Purchase - SO","76-80%",null,null,null,null,null,null,null,null,null,null,null,null,null,"Purchase - SO","86-90%"]
[null,null,"Remortgage - SO","81-85%",null,null,null,null,null,null,null,null,null,null,null,null,null,"Remortgage - SO","91-95%"]
[null,null,"Remortgage - Internal - SO","86-90%",null,null,null,null,null,null,null,null,null,null,null,null,null,"Remortgage - Internal - SO","Over 95%"]
[null,null,null,"91-95%"]
[null,null,null,"Over 95%"]
[]
[null,"Product Type","Loan Purchase","Loan To Value",null,null,null,null,null,null,null,null,null,null,null,null,"Product Type","Loan Purchase","Loan To Value"]
[null,{"$f":"IF(ISNUMBER(SEARCH(\"-\",'Loan Details'!$H$10)),MID('Loan Details'!$H$10,SEARCH(\"-\",'Loan Details'!$H$10)+2,LEN('Loan Details'!$H$10)-SEARCH(\"-\",'Loan Details'!$H$10)+2),\"Normal\")","v":"Normal"},{"$f":"IF('Loan Details'!$D$12=\"Yes\",'Loan Details'!$D$7&\" - SO\",'Loan Details'!$D$7)","v":0},{"$f":"IF('Loan Details'!$L$7<=0.605,\"Up to 60%\",\nIF('Loan Details'!$L$7<=0.705,\"61-70%\",\nIF('Loan Details'!$L$7<=0.755,\"71-75%\",\nIF('Loan Details'!$L$7<=0.805,\"76-80%\",\nIF('Loan Details'!$L$7<=0.855,\"81-85%\",\nIF('Loan Details'!$L$7<=0.905,\"86-90%\",\nIF('Loan Details'!$L$7<=0.955,\"91-95%\",\"Over 95%\")))))))","v":"Up to 60%"},{"$f":"B13&\"-\"&C13&\"-\"&D13","v":"Normal-0-Up to 60%"},null,null,null,"Danske Follow-on Rate",3.3000000000000002E-2,null,null,null,null,null,null,{"$f":"IF(ISNUMBER(SEARCH(\"-\",'Loan Details'!$H$10)),MID('Loan Details'!$H$10,SEARCH(\"-\",'Loan Details'!$H$10)+2,LEN('Loan Details'!$H$10)-SEARCH(\"-\",'Loan Details'!$H$10)+2),\"Normal\")","v":"Normal"},{"$f":"IF('Loan Details'!$D$12=\"Yes\",'Loan Details'!$D$7&\" - SO\",'Loan Details'!$D$7)","v":0},{"$f":"IF('Loan Details'!$L$7<=0.605,\"Up to 60%\",\nIF('Loan Details'!$L$7<=0.805,\"61-80%\",\nIF('Loan Details'!$L$7<=0.855,\"81-85%\",\nIF('Loan Details'!$L$7<=0.905,\"86-90%\",\nIF('Loan Details'!$L$7<=0.955,\"91-95%\",\"Over 95%\")))))","v":"Up to 60%"},{"$f":"Q13&\"-\"&R13&\"-\"&S13","v":"Normal-0-Up to 60%"}]
[null,null,null,null,null,null,null,null,"Danske Bank Base Rate (UK)",1E-3]
[null,"Fixed Rate Term","Fixed Rate Interest",null,null,null,null,null,null,null,null,null,null,null,null,null,"Fixed Rate Interest"]
[null,{"$f":"LEFT('Loan Details'!$H$10,6)","v":"0"},{"$f":"INDEX($F$20:$M$204,MATCH($E$13,$E$20:$E$204,0),MATCH($B$16,$F$19:$M$19,0))","v":{"$error":"#N/A"}},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"INDEX($U$20:$U$204,MATCH($T$13,$T$20:$T$204,0),1)","v":{"$error":"#N/A"}}]
[]
[null,"Product Type","Loan Purpose","Loan to Value",null,"Fixed Rate Term",null,null,null,null,null,null,null,null,null,null,"Product Type","Loan Purpose","Loan to Value"]
[null,null,null,null,null,"2 year","3 year","5 year",null,null,null,null,null,null,null,null,null,null,null,null,"Interest Rate"]
[null,"Normal","Purchase","Up to 60%",{"$f":"B20&\"-\"&C20&\"-\"&D20","v":"Normal-Purchase-Up to 60%"},1.7000000000000001E-2,"n/a",1.4500000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Purchase","Up to 60%",{"$f":"Q20&\"-\"&R20&\"-\"&S20","v":"Normal-Purchase-Up to 60%"},2.29E-2]
[null,"Normal","Purchase","61-70%","Normal-Purchase-61-70%",1.9599999999999999E-2,"n/a",1.6899999999999998E-2,null,null,null,null,null,null,null,null,"Normal","Purchase","61-80%",{"$f":"Q21&\"-\"&R21&\"-\"&S21","v":"Normal-Purchase-61-80%"},2.7900000000000001E-2]
[null,"Normal","Purchase","71-75%","Normal-Purchase-71-75%",1.9599999999999999E-2,"n/a",1.6899999999999998E-2,null,null,null,null,null,null,null,null,"Normal","Purchase","81-85%","Normal-Purchase-81-85%",3.49E-2]
[null,"Normal","Purchase","76-80%","Normal-Purchase-76-80%",2.3E-2,"n/a",2.2700000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Purchase","86-90%","Normal-Purchase-86-90%",4.3900000000000002E-2]
[null,"Normal","Purchase","81-85%","Normal-Purchase-81-85%",2.5899999999999999E-2,"n/a",2.7400000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Purchase","91-95%","Normal-Purchase-91-95%",4.7899999999999998E-2]
[null,"Normal","Purchase","86-90%","Normal-Purchase-86-90%",2.9000000000000001E-2,"n/a",3.3000000000000002E-2,null,null,null,null,null,null,null,null,"Normal","Purchase","Over 95%","Normal-Purchase-Over 95%",5.11E-2]
[null,"Normal","Purchase","91-95%","Normal-Purchase-91-95%",3.5400000000000001E-2,"n/a",3.7999999999999999E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage","Up to 60%","Normal-Remortgage-Up to 60%",2.29E-2]
[null,"Normal","Purchase","Over 95%","Normal-Purchase-Over 95%",null,null,null,null,null,null,null,null,null,null,null,"Normal","Remortgage","61-80%","Normal-Remortgage-61-80%",2.7900000000000001E-2]
[null,"Normal","Remortgage","Up to 60%","Normal-Remortgage-Up to 60%",1.4500000000000001E-2,"n/a",1.2200000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage","81-85%","Normal-Remortgage-81-85%",3.49E-2]
[null,"Normal","Remortgage","61-70%","Normal-Remortgage-61-70%",1.54E-2,"n/a",1.49E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage","86-90%","Normal-Remortgage-86-90%",4.3900000000000002E-2]
[null,"Normal","Remortgage","71-75%","Normal-Remortgage-71-75%",1.54E-2,"n/a",1.49E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage","91-95%","Normal-Remortgage-91-95%",4.7899999999999998E-2]
[null,"Normal","Remortgage","76-80%","Normal-Remortgage-76-80%",2.1399999999999999E-2,"n/a",2.1700000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage","Over 95%","Normal-Remortgage-Over 95%"]
[null,"Normal","Remortgage","81-85%","Normal-Remortgage-81-85%",2.2200000000000001E-2,"n/a",2.5700000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal","Up to 60%",{"$f":"Q32&\"-\"&R32&\"-\"&S32","v":"Normal-Remortgage - Internal-Up to 60%"},2.29E-2]
[null,"Normal","Remortgage","86-90%","Normal-Remortgage-86-90%",2.5999999999999999E-2,"n/a",3.0499999999999999E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal","61-80%","Normal-Remortgage - Internal-61-80%",2.7900000000000001E-2]
[null,"Normal","Remortgage","91-95%","Normal-Remortgage-91-95%",3.4099999999999998E-2,"n/a",3.6200000000000003E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal","81-85%","Normal-Remortgage - Internal-81-85%",3.49E-2]
[null,"Normal","Remortgage","Over 95%","Normal-Remortgage-Over 95%",null,null,null,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal","86-90%","Normal-Remortgage - Internal-86-90%",4.3900000000000002E-2]
[null,"Normal","Remortgage - Internal","Up to 60%","Normal-Remortgage - Internal-Up to 60%",1.29E-2,"n/a",1.15E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal","91-95%","Normal-Remortgage - Internal-91-95%",4.7899999999999998E-2]
[null,"Normal","Remortgage - Internal","61-70%","Normal-Remortgage - Internal-61-70%",1.38E-2,"n/a",1.4200000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal","Over 95%","Normal-Remortgage - Internal-Over 95%"]
[null,"Normal","Remortgage - Internal","71-75%","Normal-Remortgage - Internal-71-75%",1.38E-2,"n/a",1.4200000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Purchase - SO","Up to 60%","Normal-Purchase - SO-Up to 60%",2.4899999999999999E-2]
[null,"Normal","Remortgage - Internal","76-80%","Normal-Remortgage - Internal-76-80%",1.9800000000000002E-2,"n/a",2.1000000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Purchase - SO","61-80%",{"$f":"Q39&\"-\"&R39&\"-\"&S39","v":"Normal-Purchase - SO-61-80%"},2.9899999999999999E-2]
[null,"Normal","Remortgage - Internal","81-85%","Normal-Remortgage - Internal-81-85%",2.06E-2,"n/a",2.5000000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Purchase - SO","81-85%","Normal-Purchase - SO-81-85%",3.6900000000000002E-2]
[null,"Normal","Remortgage - Internal","86-90%","Normal-Remortgage - Internal-86-90%",2.4400000000000002E-2,"n/a",2.98E-2,null,null,null,null,null,null,null,null,"Normal","Purchase - SO","86-90%","Normal-Purchase - SO-86-90%",4.5900000000000003E-2]
[null,"Normal","Remortgage - Internal","91-95%","Normal-Remortgage - Internal-91-95%",3.2500000000000001E-2,"n/a",3.5499999999999997E-2,null,null,null,null,null,null,null,null,"Normal","Purchase - SO","91-95%","Normal-Purchase - SO-91-95%",4.99E-2]
[null,"Normal","Remortgage - Internal","Over 95%","Normal-Remortgage - Internal-Over 95%",3.4500000000000003E-2,"n/a",3.7499999999999999E-2,null,null,null,null,null,null,null,null,"Normal","Purchase - SO","Over 95%","Normal-Purchase - SO-Over 95%"]
[null,"Normal","Purchase - SO","Up to 60%",{"$f":"B44&\"-\"&C44&\"-\"&D44","v":"Normal-Purchase - SO-Up to 60%"},1.9E-2,"n/a",1.6500000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - SO","Up to 60%","Normal-Remortgage - SO-Up to 60%",2.4899999999999999E-2]
[null,"Normal","Purchase - SO","61-70%","Normal-Purchase - SO-61-70%",2.1600000000000001E-2,"n/a",1.89E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - SO","61-80%","Normal-Remortgage - SO-61-80%",2.9899999999999999E-2]
[null,"Normal","Purchase - SO","71-75%","Normal-Purchase - SO-71-75%",2.1600000000000001E-2,"n/a",1.89E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - SO","81-85%","Normal-Remortgage - SO-81-85%",3.6900000000000002E-2]
[null,"Normal","Purchase - SO","76-80%","Normal-Purchase - SO-76-80%",2.5000000000000001E-2,"n/a",2.47E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - SO","86-90%","Normal-Remortgage - SO-86-90%",4.5900000000000003E-2]
[null,"Normal","Purchase - SO","81-85%","Normal-Purchase - SO-81-85%",2.7900000000000001E-2,"n/a",2.9399999999999999E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - SO","91-95%","Normal-Remortgage - SO-91-95%",4.99E-2]
[null,"Normal","Purchase - SO","86-90%","Normal-Purchase - SO-86-90%",3.1E-2,"n/a",3.5000000000000003E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - SO","Over 95%","Normal-Remortgage - SO-Over 95%"]
[null,"Normal","Purchase - SO","91-95%","Normal-Purchase - SO-91-95%",3.7400000000000003E-2,"n/a",0.04,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal - SO","Up to 60%","Normal-Remortgage - Internal - SO-Up to 60%",2.4899999999999999E-2]
[null,"Normal","Purchase - SO","Over 95%","Normal-Purchase - SO-Over 95%",null,null,null,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal - SO","61-80%","Normal-Remortgage - Internal - SO-61-80%",2.9899999999999999E-2]
[null,"Normal","Remortgage - SO","Up to 60%","Normal-Remortgage - SO-Up to 60%",1.6500000000000001E-2,"n/a",1.4200000000000001E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal - SO","81-85%","Normal-Remortgage - Internal - SO-81-85%",3.6900000000000002E-2]
[null,"Normal","Remortgage - SO","61-70%","Normal-Remortgage - SO-61-70%",1.7399999999999999E-2,"n/a",1.6899999999999998E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal - SO","86-90%","Normal-Remortgage - Internal - SO-86-90%",4.5900000000000003E-2]
[null,"Normal","Remortgage - SO","71-75%","Normal-Remortgage - SO-71-75%",1.7399999999999999E-2,"n/a",1.6899999999999998E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal - SO","91-95%","Normal-Remortgage - Internal - SO-91-95%",4.99E-2]
[null,"Normal","Remortgage - SO","76-80%","Normal-Remortgage - SO-76-80%",2.3400000000000001E-2,"n/a",2.3699999999999999E-2,null,null,null,null,null,null,null,null,"Normal","Remortgage - Internal - SO","Over 95%","Normal-Remortgage - Internal - SO-Over 95%"]
[null,"Normal","Remortgage - SO","81-85%","Normal-Remortgage - SO-81-85%",2.4199999999999999E-2,"n/a",2.7699999999999999E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase","Up to 60%","Carbon Neutral-Purchase-Up to 60%",2.2599999999999999E-2]
[null,"Normal","Remortgage - SO","86-90%","Normal-Remortgage - SO-86-90%",2.8000000000000001E-2,"n/a",3.2500000000000001E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase","61-80%",{"$f":"Q57&\"-\"&R57&\"-\"&S57","v":"Carbon Neutral-Purchase-61-80%"},2.76E-2]
[null,"Normal","Remortgage - SO","91-95%","Normal-Remortgage - SO-91-95%",3.61E-2,"n/a",3.8100000000000002E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase","81-85%","Carbon Neutral-Purchase-81-85%",3.4599999999999999E-2]
[null,"Normal","Remortgage - SO","Over 95%","Normal-Remortgage - SO-Over 95%",null,null,null,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase","86-90%","Carbon Neutral-Purchase-86-90%",4.36E-2]
[null,"Normal","Remortgage - Internal - SO","Up to 60%",{"$f":"B60&\"-\"&C60&\"-\"&D60","v":"Normal-Remortgage - Internal - SO-Up to 60%"},1.49E-2,"n/a",1.35E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase","91-95%","Carbon Neutral-Purchase-91-95%",4.7600000000000003E-2]
[null,"Normal","Remortgage - Internal - SO","61-70%","Normal-Remortgage - Internal - SO-61-70%",1.5800000000000002E-2,"n/a",1.6199999999999999E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase","Over 95%","Carbon Neutral-Purchase-Over 95%"]
[null,"Normal","Remortgage - Internal - SO","71-75%","Normal-Remortgage - Internal - SO-71-75%",1.5800000000000002E-2,"n/a",1.6199999999999999E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage","Up to 60%","Carbon Neutral-Remortgage-Up to 60%",2.2599999999999999E-2]
[null,"Normal","Remortgage - Internal - SO","76-80%","Normal-Remortgage - Internal - SO-76-80%",2.18E-2,"n/a",2.3E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage","61-80%","Carbon Neutral-Remortgage-61-80%",2.76E-2]
[null,"Normal","Remortgage - Internal - SO","81-85%","Normal-Remortgage - Internal - SO-81-85%",2.2599999999999999E-2,"n/a",2.7E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage","81-85%","Carbon Neutral-Remortgage-81-85%",3.4599999999999999E-2]
[null,"Normal","Remortgage - Internal - SO","86-90%","Normal-Remortgage - Internal - SO-86-90%",2.64E-2,"n/a",3.1800000000000002E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage","86-90%","Carbon Neutral-Remortgage-86-90%",4.36E-2]
[null,"Normal","Remortgage - Internal - SO","91-95%","Normal-Remortgage - Internal - SO-91-95%",3.4500000000000003E-2,"n/a",3.7499999999999999E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage","91-95%","Carbon Neutral-Remortgage-91-95%",4.7600000000000003E-2]
[null,"Normal","Remortgage - Internal - SO","Over 95%","Normal-Remortgage - Internal - SO-Over 95%",null,"n/a",null,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage","Over 95%","Carbon Neutral-Remortgage-Over 95%"]
[null,"Carbon Neutral","Purchase","Up to 60%","Carbon Neutral-Purchase-Up to 60%",1.67E-2,"n/a",1.4999999999999999E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase - SO","Up to 60%","Carbon Neutral-Purchase - SO-Up to 60%",2.46E-2]
[null,"Carbon Neutral","Purchase","61-70%","Carbon Neutral-Purchase-61-70%",1.9300000000000001E-2,"n/a",1.7399999999999999E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase - SO","61-80%",{"$f":"Q69&\"-\"&R69&\"-\"&S69","v":"Carbon Neutral-Purchase - SO-61-80%"},2.9600000000000001E-2]
[null,"Carbon Neutral","Purchase","71-75%","Carbon Neutral-Purchase-71-75%",1.9300000000000001E-2,"n/a",1.7399999999999999E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase - SO","81-85%","Carbon Neutral-Purchase - SO-81-85%",3.6600000000000001E-2]
[null,"Carbon Neutral","Purchase","76-80%","Carbon Neutral-Purchase-76-80%",2.2700000000000001E-2,"n/a",2.3199999999999998E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase - SO","86-90%","Carbon Neutral-Purchase - SO-86-90%",4.5600000000000002E-2]
[null,"Carbon Neutral","Purchase","81-85%","Carbon Neutral-Purchase-81-85%",2.5600000000000001E-2,"n/a",2.7900000000000001E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase - SO","91-95%","Carbon Neutral-Purchase - SO-91-95%",4.9599999999999998E-2]
[null,"Carbon Neutral","Purchase","86-90%","Carbon Neutral-Purchase-86-90%",2.87E-2,"n/a",3.3500000000000002E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Purchase - SO","Over 95%","Carbon Neutral-Purchase - SO-Over 95%"]
[null,"Carbon Neutral","Purchase","91-95%","Carbon Neutral-Purchase-91-95%",3.5099999999999999E-2,"n/a",3.85E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage - SO","Up to 60%","Carbon Neutral-Remortgage - SO-Up to 60%",2.46E-2]
[null,"Carbon Neutral","Purchase","Over 95%","Carbon Neutral-Purchase-Over 95%",null,null,null,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage - SO","61-80%","Carbon Neutral-Remortgage - SO-61-80%",2.9600000000000001E-2]
[null,"Carbon Neutral","Remortgage","Up to 60%","Carbon Neutral-Remortgage-Up to 60%",1.4500000000000001E-2,"n/a",1.2200000000000001E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage - SO","81-85%","Carbon Neutral-Remortgage - SO-81-85%",3.6600000000000001E-2]
[null,"Carbon Neutral","Remortgage","61-70%","Carbon Neutral-Remortgage-61-70%",1.54E-2,"n/a",1.49E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage - SO","86-90%","Carbon Neutral-Remortgage - SO-86-90%",4.5600000000000002E-2]
[null,"Carbon Neutral","Remortgage","71-75%","Carbon Neutral-Remortgage-71-75%",1.54E-2,"n/a",1.49E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage - SO","91-95%","Carbon Neutral-Remortgage - SO-91-95%",4.9599999999999998E-2]
[null,"Carbon Neutral","Remortgage","76-80%","Carbon Neutral-Remortgage-76-80%",2.1399999999999999E-2,"n/a",2.1700000000000001E-2,null,null,null,null,null,null,null,null,"Carbon Neutral","Remortgage - SO","Over 95%","Carbon Neutral-Remortgage - SO-Over 95%"]
[null,"Carbon Neutral","Remortgage","81-85%","Carbon Neutral-Remortgage-81-85%",2.2200000000000001E-2,"n/a",2.5700000000000001E-2,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Q80&\"-\"&R80&\"-\"&S80","v":"--"}]
[null,"Carbon Neutral","Remortgage","86-90%","Carbon Neutral-Remortgage-86-90%",2.5999999999999999E-2,"n/a",3.0499999999999999E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Remortgage","91-95%","Carbon Neutral-Remortgage-91-95%",3.4099999999999998E-2,"n/a",3.6200000000000003E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Remortgage","Over 95%","Carbon Neutral-Remortgage-Over 95%",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Purchase - SO","Up to 60%",{"$f":"B84&\"-\"&C84&\"-\"&D84","v":"Carbon Neutral-Purchase - SO-Up to 60%"},1.8700000000000001E-2,"n/a",1.7000000000000001E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Purchase - SO","61-70%","Carbon Neutral-Purchase - SO-61-70%",2.1299999999999999E-2,"n/a",1.9400000000000001E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Purchase - SO","71-75%","Carbon Neutral-Purchase - SO-71-75%",2.1299999999999999E-2,"n/a",1.9400000000000001E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Purchase - SO","76-80%","Carbon Neutral-Purchase - SO-76-80%",2.47E-2,"n/a",2.52E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Purchase - SO","81-85%","Carbon Neutral-Purchase - SO-81-85%",2.76E-2,"n/a",2.9899999999999999E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Purchase - SO","86-90%","Carbon Neutral-Purchase - SO-86-90%",3.0700000000000002E-2,"n/a",3.5499999999999997E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Purchase - SO","91-95%","Carbon Neutral-Purchase - SO-91-95%",3.7100000000000001E-2,"n/a",4.0500000000000001E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Purchase - SO","Over 95%","Carbon Neutral-Purchase - SO-Over 95%",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Remortgage - SO","Up to 60%","Carbon Neutral-Remortgage - SO-Up to 60%",1.6500000000000001E-2,"n/a",1.4200000000000001E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Remortgage - SO","61-70%","Carbon Neutral-Remortgage - SO-61-70%",1.7399999999999999E-2,"n/a",1.6899999999999998E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Remortgage - SO","71-75%","Carbon Neutral-Remortgage - SO-71-75%",1.7399999999999999E-2,"n/a",1.6899999999999998E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Remortgage - SO","76-80%","Carbon Neutral-Remortgage - SO-76-80%",2.3400000000000001E-2,"n/a",2.3699999999999999E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Remortgage - SO","81-85%","Carbon Neutral-Remortgage - SO-81-85%",2.4199999999999999E-2,"n/a",2.7699999999999999E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Remortgage - SO","86-90%","Carbon Neutral-Remortgage - SO-86-90%",2.8000000000000001E-2,"n/a",3.2500000000000001E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Remortgage - SO","91-95%","Carbon Neutral-Remortgage - SO-91-95%",3.61E-2,"n/a",3.8199999999999998E-2,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,"Carbon Neutral","Remortgage - SO","Over 95%","Carbon Neutral-Remortgage - SO-Over 95%",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,{"$f":"B100&\"-\"&C100&\"-\"&D100","v":"--"},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Q133&\"-\"&R133&\"-\"&S133","v":"--"}]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,{"$f":"B149&\"-\"&C149&\"-\"&D149","v":"--"},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"Q197&\"-\"&R197&\"-\"&S197","v":"--"}]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]
[null,null,null,null,"--",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"--"]

{"sheet":"Tax Applicant 1","index":13,"n_rows":170,"n_cols":18,"rows_kept":170,"cut":false,"hidden":true,"merged":[]}
[null,"UK Taxation"]
[null,null," "]
[null,"Additional Deduction Fields"]
[null,"Age",{"$f":"'Applicant 1'!H5","v":31}," years"]
[null,"Student Loan Payment",{"$f":"IF('Applicant 1'!D91>0,\"Plan1\",\"No\")","v":"No"},"(Plan2/No)"]
[null,"Blind","No","(Yes/No)"]
[null,"Pension Contributions",{"$f":"'Applicant 1'!H86*12","v":0}," per year"]
[null,"Childcare Vouchers",{"$f":"'Applicant 1'!D87*12","v":0}," per year"]
[]
[null,"Income Type"]
[null,"Basic Salary",{"$f":"IF('Applicant 1'!D11<>\"Self Employed\",'Applicant 1'!H34,0)","v":0}," per year"]
[null,"All employments",{"$f":"IF('Applicant 1'!D11=\"Self Employed\",'Applicant 1'!H20,0)","v":0}," per year"]
[null,"Pension Income (incl. Private, Company and State)",{"$f":"'Applicant 1'!D44+'Applicant 1'!D45+'Applicant 1'!D43","v":0}," per year"]
[null,"Profit from Self Employment",{"$f":"'Applicant 1'!D42","v":0}," per year"]
[null,"Other",null," per year"]
[null,"Deposit Interest (Savings) Income",{"$f":"'Applicant 1'!D49","v":0}," per year"]
[null,"Annuities Income",{"$f":"'Applicant 1'!D51","v":0}," per year"]
[null,"Trust Income",{"$f":"'Applicant 1'!D50","v":0}," per year"]
[null,"Dividend Income",{"$f":"'Applicant 1'!D48","v":0}," per year"]
[null,"Investment Income",{"$f":"'Applicant 1'!D47","v":0}," per year"]
[]
[null,"Total Gross Income",{"$f":"SUM(C11:C20)","v":0}," per year"]
[]
[null,"National Insurance Income",{"$f":"C11+C12","v":0}," per year"]
[null,"Self Employed Profit National Insurance Income*",{"$f":"C14","v":0}," per year"]
[]
[null,"Non-Savings Income minus pension contributions and child care vouchers",{"$f":"SUM(C11:C15)-SUM(C7:C8)","v":0}," per year"]
[null,"Interest Income",{"$f":"SUM(C16:C18)","v":0}," per year"]
[null,"Investment Income (Top Slice)",{"$f":"SUM(C19:C20)","v":0}," per year"]
[]
[null,"Total Taxable Income",{"$f":"SUM(C27:C29)","v":0}," per year"]
[]
[null,"Total Employment PAYE tax**",{"$f":"C77","v":0}," per year"]
[null,"Total Employment National Insurance",{"$f":"C90","v":0}," per year"]
[null,"Student Loan Payment",{"$f":"IF(UPPER(LEFT(C5,1))<>\"N\",'Applicant 1'!D91*12,0)","v":0}," per year"]
[null,"Class2 NIC***",{"$f":"C96","v":0}," per year"]
[null,"Class4 NIC***",{"$f":"C110","v":0}," per year"]
[null,"Interest Income Tax ****",{"$f":"C147","v":0}," per year"]
[null,"Investment Income Tax *****",{"$f":"C170","v":0}," per year"]
[]
[null,"Total deductions",{"$f":"SUM(C33:C39)","v":0}," per year"]
[]
[null,"Annual Net Pay :",{"$f":"C31-C41","v":0}," per year"]
[null,"Monthly Net pay :",{"$f":"C43/12","v":0}," per month"]
[null,"Weekly Net pay :",{"$f":"C43/52","v":0}," per week"]
[]
[null,"* Profit from Self Employment"]
[null,"** PAYE Tax on Employment and Self Employment Incomes"]
[null,"*** Self Employed National Insurance "]
[null,"**** Tax on Deposit Interest (Savings) and Annuities Income"]
[null,"***** Tax on Dividend and Investment Income"]
[]
[null,"Personal Allowance"]
[]
[null,"Basic",{"$f":"G55","v":12570},null,null,"Basic",{"$f":"'Tax Rates'!B5","v":12570}]
[null,"Age related contribution",{"$f":"(IF(C4<65,0,IF(C4<75,G56-G55,G57-G55)))","v":0},null,null,"Age 65-74",{"$f":"G55","v":12570}]
[null,"Age related taper deduction",{"$f":"IF((C11-G60)<0,0,IF((C11-G60)/2>C56,C56,(C11-G60)/2))","v":0},null,null,"Age 75 and over",{"$f":"G55","v":12570}]
[null,"Allowance after age adjust",{"$f":"C55+C56-C57","v":12570},null,null,"Blind persons allowance",{"$f":"'Tax Rates'!B90","v":2520}]
[]
[null,"Taper deduction",{"$f":"IF(((C22-C7)-G61)<0,0,IF(((C22-C7)-G61)/2>C58,C58,((C22-C7)-G61)/2))","v":0},null,null,"Age related threshold",27700]
[null,null,null,null,null,"Taper threshold",100000]
[null,"Personal Allowance",{"$f":"C58-C60","v":12570}]
[]
[null,"Non-Savings Income Tax (Basic Salary/Self Employed/Penison(s)/IncomeEarnedAsSoleProprietorOrPartner/Trust/Other) "]
[]
[null,"Taxable Employment Income",{"$f":"C27","v":0},null,null,"Personal Allowance",{"$f":"C62","v":12570}]
[null,null,null,null,null,"Blind persons allowance",{"$f":"IF(UPPER(LEFT(C6,1))=\"Y\",G58,0)","v":0}]
[null,"Income less personal allowance",{"$f":"IF(C66>G68,C66-G68,0)","v":0},null,null,"Total Allowances",{"$f":"G67+G66","v":12570}]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate","Start of Band","End of Band","Difference"]
[null,{"$f":"TEXT(F71,\"0.0%\") & \" Rate\"","v":"20.0% Rate"},{"$f":"D71*F71","v":0},{"$f":"MIN(C68,I71)","v":0},null,{"$f":"'Tax Rates'!B42","v":0.2},0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"H71-G71","v":37700}]
[null,{"$f":"TEXT(F72,\"0.0%\") & \" Rate\"","v":"40.0% Rate"},{"$f":"D72*F72","v":0},{"$f":"IF($C$68>G72,MIN($C$68-G72,I72),0)","v":0},null,{"$f":"'Tax Rates'!B43","v":0.4},{"$f":"H71+1","v":37701},{"$f":"'Tax Rates'!B39","v":150000},{"$f":"H72-G72","v":112299}]
[null,{"$f":"TEXT(F73,\"0.0%\") & \" Rate\"","v":"45.0% Rate"},{"$f":"D73*F73","v":0},{"$f":"IF($C$68>G73,$C$68-G73,0)","v":0},null,{"$f":"'Tax Rates'!B44","v":0.45},{"$f":"H72+1","v":150001},99999999,{"$f":"H73-G73","v":99849998}]
[]
[null,"Less tax on trusts"]
[null,null,null,null,null,null,null,null,null,null,{"$f":"12500/52","v":240.38461538461539}]
[null,"Income Tax",{"$f":"SUM(C71:C73)","v":0}]
[]
[null,"National Insurance (From Basic Salary income)"]
[]
[null,"Weekly Income (Gross)",{"$f":"C24/52","v":0},null,null,"State Pension Age",65," years"]
[]
[null,null,"Tax","Carry Over",null,"Rate","Start of Band","End of Band","Difference"]
[null,{"$f":"TEXT(F84,\"0.0%\") & \" Rate\"","v":"0.0% Rate"},{"$f":"IF(C81>=G84,IF(C81>=I84,I84*F84,C81*F84),0)","v":0},{"$f":"IF((C81-I84)>0,C81-I84,0)","v":0},null,{"$f":"'Tax Rates'!B49","v":0},0,{"$f":"'Tax Rates'!B54","v":184},{"$f":"H84-G84","v":184},null,{"$f":"183*52","v":9516}]
[null,{"$f":"TEXT(F85,\"0.0%\") & \" Rate\"","v":"12.0% Rate"},{"$f":"IF(D84>0,IF(D84>=I85,I85*F85,D84*F85),0)","v":0},{"$f":"IF((D84-I85)>0,D84-I85,0)","v":0},null,{"$f":"'Tax Rates'!B50","v":0.12},{"$f":"H84+0.01","v":184.01},{"$f":"'Tax Rates'!B55","v":967},{"$f":"H85-G85+0.01","v":783}]
[null,{"$f":"TEXT(F86,\"0.0%\") & \" Rate\"","v":"2.0% Rate"},{"$f":"IF(D85>0,IF(D85>=I86,I86*F86,D85*F86),0)","v":0},{"$f":"IF((D85-I86)>0,D85-I86,0)","v":0},null,{"$f":"'Tax Rates'!B51","v":0.02},{"$f":"H85+0.01","v":967.01},99999999.989999995,{"$f":"H86-G86+0.01","v":99999032.989999995}]
[]
[null,"Total Weekly NI",{"$f":"SUM(C84:C86)","v":0}]
[null,"Age related deduction",{"$f":"IF(C4>=G81,C88*52,0)","v":0}]
[null,"Total Annual NI",{"$f":"(C88*52)-C89","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,"NIC annual maxima where employed and self employed"]
[null,"Self Employed National Insurance "]
[null,null,null,null,null,null,null,null,null,null,"STEPS"]
[null,"Class 2 Threshold",{"$f":"'Tax Rates'!B63","v":6515},null,null,null,null,null,null,null,1,{"$f":"H105","v":50270},{"$f":"H104","v":9569},{"$f":"IF(C14>0,L94-M94,0)","v":0}]
[null,"Class 2 Weekly Rate",{"$f":"'Tax Rates'!B64","v":3.05},null,null,null,null,null,null,null,2,{"$f":"N94","v":0},{"$f":"F105","v":0.09},{"$f":"M95*L95","v":0}]
[null,"Total Annual Class 2 NIC",{"$f":"IF(C25=0,0,IF(SUM(C24:C25)>C94,C95*52))","v":0},null,null,null,null,null,null,null,3,{"$f":"N95","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"53*$C$95","v":161.64999999999998},null,{"$f":"L97+L96","v":161.64999999999998}]
[null,"Class 4 Taxable Income",{"$f":"C25","v":0},null,null,null,null,null,null,null,null,null,null,null,null,"Comparative total"]
[null,null,null,null,null,null,null,null,null,null,4,null,{"$f":"N97","v":161.64999999999998},null,null,"Class 1 NIC @12%",null,{"$f":"-M100","v":0}]
[null,"Tax at 2% if employed and self employed","Tax",null,null,"Rate",null,null,null,null,null,null,{"$f":"-C85*52","v":0},null,null,"Class 2 NIC",null,{"$f":"-M101","v":0}]
[null,{"$f":"TEXT(F101,\"0.0%\")","v":"2.0%"},{"$f":"D101*F101","v":0},{"$f":"IF(C25=0,0,IF(SUM(C11:C12)>12500,C98-9500,0))","v":0},null,{"$f":"'Tax Rates'!B67","v":0.02},null,null,null,null,null,null,{"$f":"-C96","v":0},{"$f":"SUM(M99:M101)","v":161.64999999999998},null,"Class 4 NIC @9%",null,{"$f":"C25*0.09","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(R99:R101)","v":0}]
[null,null,"Tax","Carry Over",null,"Rate","Start of Band","End of Band","Difference",null,null,"Case 1",null,{"$f":"IF(N101>R102,N101,0)","v":161.64999999999998}]
[null,{"$f":"TEXT(F104,\"0.0%\") & \" Rate\"","v":"0.0% Rate"},{"$f":"IF(C101>=G104,IF(C101>=I104,I104*F104,C101*F104),0)","v":0},{"$f":"IF((C98-I104)>0,C98-I104,0)","v":0},null,{"$f":"'Tax Rates'!B70","v":0},0,{"$f":"'Tax Rates'!B75","v":9569},{"$f":"H104-G104","v":9569},null,null,"Case 2",null,{"$f":"IF(AND(N101>0,N101<R102),N101,0)","v":0},"need to add additional 2%"]
[null,{"$f":"TEXT(F105,\"0.0%\") & \" Rate\"","v":"9.0% Rate"},{"$f":"IF(D104>0,IF(D104>=I105,I105*F105,D104*F105),0)","v":0},{"$f":"IF((D104-I105)>0,D104-I105,0)","v":0},null,{"$f":"'Tax Rates'!B71","v":0.09},{"$f":"IF(H104>=1,H104+1,H104)","v":9570},{"$f":"'Tax Rates'!B76","v":50270},{"$f":"H105-G105","v":40700},null,null,"Case 3",null,{"$f":"IF(N101<0,N101,0)","v":0},"NIC4 @9% is NIL "]
[null,{"$f":"TEXT(F106,\"0.0%\") & \" Rate\"","v":"2.0% Rate"},{"$f":"IF(D105>0,IF(D105>=I106,I106*F106,D105*F106),0)","v":0},{"$f":"IF((D105-I106)>0,D105-I106,0)","v":0},null,{"$f":"'Tax Rates'!B72","v":0.02},{"$f":"H105+1","v":50271},99999999,{"$f":"H106-G106+1","v":99949729}]
[null,null,null,null,null,null,null,null,null,null,null,"Step5-9 only for Case 2 & 3"]
[null,"Total Class 4 NIC (where no Class 1 NIC)",{"$f":"IF(D101>0,C101,SUM(C104:C106))","v":0},null,null,null,null,null,null,null,5,{"$f":"IF(OR(N105>0,N104>0),N101,0)","v":0},{"$f":"100/9","v":11.111111111111111},{"$f":"L108*M108","v":0}]
[null,null,null,null,null,null,null,null,null,null,6,{"$f":"MIN(C25,H105)","v":0},{"$f":"-I104","v":-9569},{"$f":"L109+M109","v":-9569}]
[null,"Total Annual Class 4 NIC payable",{"$f":"IF(C90<0.01,C108,IF(N103>0,R101,IF(N104>0,N119,IF(N105<0,N119,0))))","v":0},null,null,null,null,null,null,null,7,null,null,{"$f":"N109-N108","v":-9569}]
[null,null,null,null,null,null,null,null,null,null,null,"If STEP 7 negative then Nil"]
[null,"Student Loan Repayment",null,null,null,null,null,null,null,null,8,{"$f":"IF(N110<0,0,N110)","v":0},{"$f":"$F$106","v":0.02},{"$f":"L112*M112","v":0}]
[null,null,null,null,null,null,null,null,null,null,9,{"$f":"C25","v":0},{"$f":"-H105","v":-50270},{"$f":"IF((L113+M113)<0,0,(L113+M113)*$F$106)","v":0}]
[null,"Threshold",{"$f":"IF(RIGHT(C5,1)=\"1\",G115/12,IF(RIGHT(C5,1)=\"2\",H115/12,0))","v":0},null,null,null,"Plan 1","Plan 2"]
[null,"Income above threshold",{"$f":"IF(SUM(C27,C14)/12-C114<0,0,SUM(C27,C14)/12-C114)","v":0},null,null,"Repayment Threshold",{"$f":"'Tax Rates'!B27*12","v":19884},{"$f":"'Tax Rates'!B28*12","v":27288},null,null,null,"Total Class 4 NIC"]
[null,"Repayment (if applicable)",{"$f":"FLOOR(C115*G116,1)*12","v":0},null,null,"Repayment Rate",{"$f":"'Tax Rates'!B31","v":0.09},{"$f":"'Tax Rates'!B32","v":0.09},null,null,null,"Step 4",null,{"$f":"IF(N101<0,0,N101)","v":161.64999999999998}]
[null,null,null,null,null,null,null,null,null,null,null,"Step 8",null,{"$f":"N112","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,"Step 9",null,{"$f":"N113","v":0}]
[null,"Interest Income Tax (Deposit Interest (Savings), Annuities, Trust)",null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(N116:N118)","v":161.64999999999998}]
[]
[null,"Interest Income",{"$f":"C28","v":0}]
[]
[null,"Non-Savings Income minus allowance",{"$f":"C68","v":0}]
[null,"Gross Income (for allowance calculation)",{"$f":"C31","v":0}]
[]
[null,null,"Calculated Allowance",null,null,"Start of Band","End of Band","Difference","Within Band"]
[null,null,{"$f":"IF(I127=1,'Tax Rates'!B9,0)","v":5000},null,null,0,{"$f":"'Tax Rates'!B15","v":12570},{"$f":"G127-F127","v":12570},{"$f":"IF(AND($C$124>=$F127,$C$124<=$G127),1,0)","v":1}]
[null,null,{"$f":"IF(I128=1,'Tax Rates'!B9-(C123-G127),0)","v":0},null,null,{"$f":"IF(G127>=1,G127+1,G127)","v":12571},{"$f":"'Tax Rates'!B16","v":17570},{"$f":"G128-F128","v":4999},{"$f":"IF(AND($C$124>=$F128,$C$124<=$G128),1,0)","v":0}]
[null,null,{"$f":"IF(I129=1,'Tax Rates'!B10,0)","v":0},null,null,{"$f":"G128+1","v":17571},{"$f":"'Tax Rates'!B17","v":50270},{"$f":"G129-F129+1","v":32700},0]
[null,null,{"$f":"IF(I130=1,'Tax Rates'!B11,0)","v":0},null,null,{"$f":"G129+1","v":50271},{"$f":"'Tax Rates'!B18","v":150000},{"$f":"G130-F130+1","v":99730},0]
[null,null,{"$f":"IF(I131=1,'Tax Rates'!B12,0)","v":0},null,null,{"$f":"G130+1","v":150001},99999999,{"$f":"G131-F131+1","v":99849999},0]
[]
[null,"Interest Income Tax Allowance",{"$f":"SUM(C127:C131)","v":5000}]
[]
[null,null,null,"Taxable Interest Income by tax band",null,"Start of Band","End of Band","Scenario check 1","Pay+Interest check","Pay+Allowance check","Scenaio Check 2","Basic ","Upper ","Additional","Allowance 2","Allowance 3"]
[null,null,"Basic Tax Band",{"$f":"IF(H136=1,L136,IF(H137=1,L137,IF(H138=1,L138,0)))","v":32700},null,0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"IF(AND($C$123>=F136,$C$123<=G136),1,0)","v":1},{"$f":"IF(AND($C$123+$C$121>F136,$C$123+$C$121<=G136),1,0)","v":0},{"$f":"IF(AND($C$123+$C$133>F136,$C$123+$C$133<=G136),1,0)","v":1},{"$f":"IF(AND(I136=1,J136=1),1,IF(AND(I136=0,J136=1),2,IF(AND(I136=0,J136=0),3,IF(AND(I136=1,J136=0),4,0))))","v":2},{"$f":"IF(K136=1,C121-C133,IF(K136=2,G136-C123-C133,IF(K136=3,0,IF(K136=4,0,0))))","v":32700},{"$f":"IF(K136=1,0,IF(K136=2,MIN(C123+C121-G136,G137-G136),IF(K136=3,MIN(C121-C133,G137-G136-O136),IF(K136=4,0,0))))","v":-37700},{"$f":"IF(C123+C121>G137,IF(K136=1,0,IF(K136=2,C123+C121-G137,IF(K136=3,C123+C121-G137,IF(K136=4,0,0)))),0)","v":0},{"$f":"$C$123+$C$133-G136","v":-32700},{"$f":"$C$133-O136","v":37700}]
[null,null,"Upper Tax Band",{"$f":"IF(H136=1,M136,IF(H137=1,M137,IF(H138=1,M138,0)))","v":-37700},null,37501,{"$f":"'Tax Rates'!B39","v":150000},{"$f":"IF(AND($C$123>=F137,$C$123<=G137),1,0)","v":0},{"$f":"IF(AND($C$123+$C$121>F137,$C$123+$C$121<=G137),1,0)","v":0},{"$f":"IF(AND($C$123+$C$133>F137,$C$123+$C$133<=G137),1,0)","v":0},{"$f":"IF(AND(I137=1,J137=1),1,IF(AND(I137=0,J137=1),2,IF(AND(I137=0,J137=0),3,IF(AND(I137=1,J137=0),4,0))))","v":3},0,{"$f":"IF(K137=1,C121-C133,IF(K137=2,G137-C123-C133,IF(K137=3,0,IF(K137=4,0,0))))","v":0},{"$f":"IF(K137=1,0,IF(K137=2,C123+C121-G137,IF(K137=3,C121-C133,IF(K137=4,0,0))))","v":-5000},{"$f":"$C$123+$C$133-G137","v":-145000},{"$f":"$C$133-O137","v":150000}]
[null,null,"Additional Tax Band",{"$f":"IF(H136=1,N136,IF(H137=1,N137,IF(H138=1,N138,0)))","v":0},null,150001,99999999,{"$f":"IF(AND($C$123>=F138,$C$123<=G138),1,0)","v":0},0,{"$f":"IF(AND($C$123+$C$133>F138,$C$123+$C$133<=G138),1,0)","v":0},{"$f":"IF(AND(I138=1,J138=1),1,IF(AND(I138=0,J138=1),2,IF(AND(I138=0,J138=0),3,IF(AND(I138=1,J138=0),4,0))))","v":3},0,0,{"$f":"C121-C133","v":-5000},{"$f":"$C$123+$C$133-G138","v":-99994999},99999999]
[]
[]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate"]
[null,{"$f":"TEXT(F143,\"0.0%\") & \" Rate\"","v":"20.0% Rate"},{"$f":"D143*F143","v":6540},{"$f":"D136","v":32700},null,{"$f":"'Tax Rates'!B42","v":0.2}]
[null,{"$f":"TEXT(F144,\"0.0%\") & \" Rate\"","v":"40.0% Rate"},{"$f":"D144*F144","v":-15080},{"$f":"D137","v":-37700},null,{"$f":"'Tax Rates'!B43","v":0.4}]
[null,{"$f":"TEXT(F145,\"0.0%\") & \" Rate\"","v":"45.0% Rate"},{"$f":"D145*F145","v":0},{"$f":"D138","v":0},null,{"$f":"'Tax Rates'!B44","v":0.45}]
[]
[null,"Interest Income Tax",{"$f":"IF(OR(C121<=C133,C121=0),0,SUM(C143:C145))","v":0}]
[]
[]
[null,"Investment Income Tax (Dividend/Investment)"]
[]
[null,"Investment Income",{"$f":"C29","v":0}]
[null,"Dividend/Investment Income Allowance ",{"$f":"'Tax Rates'!B22","v":2000}]
[]
[null,"Non-Savings Income minus personal allowance and Interest Income",{"$f":"C68+C28","v":0}]
[]
[]
[null,null,null,"Taxable Interest Income by tax band",null,"Start of Band","End of Band","Scenario check 1","Pay+Investment check","Pay+Allowance check","Scenaio Check 2","Basic ","Upper ","Additional","Allowance 2","Allowance 3"]
[null,null,"Basic Tax Band",{"$f":"IF(H159=1,L159,IF(H160=1,L160,IF(H161=1,L161,0)))","v":35700},null,0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"IF(AND($C$155>=F159,$C$155<=G159),1,0)","v":1},{"$f":"IF(AND($C$155+$C$152>F159,$C$155+$C$152<=G159),1,0)","v":0},{"$f":"IF(AND($C$155+$C$153>F159,$C$155+$C$153<=G159),1,0)","v":1},{"$f":"IF(AND(I159=1,J159=1),1,IF(AND(I159=0,J159=1),2,IF(AND(I159=0,J159=0),3,IF(AND(I159=1,J159=0),4,0))))","v":2},{"$f":"IF(K159=1,C152-C153,IF(K159=2,G159-C155-C153,IF(K159=3,0,IF(K159=4,0,0))))","v":35700},{"$f":"IF(K159=1,0,IF(K159=2,MIN(C155+C152-G159,G160-G159),IF(K159=3,MIN(C152-C153,G160-G159-O159),IF(K159=4,0,0))))","v":-37700},{"$f":"IF(C153+C152>G160,IF(K159=1,0,IF(K159=2,C153+C152-G160,IF(K159=3,C153+C152-G160,IF(K159=4,0,0)))),0)","v":0},{"$f":"$C$155+$C$153-G159","v":-35700},{"$f":"$C$153-O159","v":37700}]
[null,null,"Upper Tax Band",{"$f":"IF(H159=1,M159,IF(H160=1,M160,IF(H161=1,M161,0)))","v":-37700},null,37501,{"$f":"'Tax Rates'!B39","v":150000},{"$f":"IF(AND($C$155>=F160,$C$155<=G160),1,0)","v":0},{"$f":"IF(AND($C$155+$C$152>F160,$C$155+$C$152<=G160),1,0)","v":0},{"$f":"IF(AND($C$155+$C$153>F160,$C$155+$C$153<=G160),1,0)","v":0},{"$f":"IF(AND(I160=1,J160=1),1,IF(AND(I160=0,J160=1),2,IF(AND(I160=0,J160=0),3,IF(AND(I160=1,J160=0),4,0))))","v":3},0,{"$f":"IF(K160=1,C152-C153,IF(K160=2,G160-C155-C153,IF(K160=3,0,IF(K160=4,0,0))))","v":0},{"$f":"IF(K160=1,0,IF(K160=2,C155+C152-G160,IF(K160=3,C152-C153,IF(K160=4,0,0))))","v":-2000},{"$f":"$C$155+$C$153-G160","v":-148000},{"$f":"$C$153-O160","v":150000}]
[null,null,"Additional Tax Band",{"$f":"IF(H159=1,N159,IF(H160=1,N160,IF(H161=1,N161,0)))","v":0},null,150001,99999999,{"$f":"IF(AND($C$155>=F161,$C$155<=G161),1,0)","v":0},0,{"$f":"IF(AND($C$155+$C$153>F161,$C$155+$C$153<=G161),1,0)","v":0},3,0,0,{"$f":"C152-C153","v":-2000},{"$f":"$C$155+$C$153-G161","v":-99997999},{"$f":"$C$153-O161","v":99999999}]
[]
[]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate"]
[null,{"$f":"TEXT(F166,\"0.0%\") & \" Rate\"","v":"7.5% Rate"},{"$f":"D166*F166","v":2677.5},{"$f":"D159","v":35700},null,{"$f":"'Tax Rates'!B83","v":7.4999999999999997E-2}]
[null,{"$f":"TEXT(F167,\"0.0%\") & \" Rate\"","v":"32.5% Rate"},{"$f":"D167*F167","v":-12252.5},{"$f":"D160","v":-37700},null,{"$f":"'Tax Rates'!B84","v":0.32500000000000001}]
[null,{"$f":"TEXT(F168,\"0.0%\") & \" Rate\"","v":"38.1% Rate"},{"$f":"D168*F168","v":0},0,null,{"$f":"'Tax Rates'!B85","v":0.38100000000000001}]
[]
[null,"Investment Income Tax",{"$f":"IF(OR(C152=0,C152<=C153),0,SUM(C166:C168))","v":0}]

{"sheet":"Tax Applicant 2","index":14,"n_rows":170,"n_cols":18,"rows_kept":170,"cut":false,"hidden":true,"merged":[]}
[null,"UK Taxation"]
[null,null," "]
[null,"Additional Deduction Fields"]
[null,"Age",{"$f":"'Applicant 2'!H5","v":"Input DOB"}," years"]
[null,"Student Loan Payment",{"$f":"IF('Applicant 2'!D91>0,\"Plan2\",\"No\")","v":"No"},"(Plan2/No)"]
[null,"Blind","No","(Yes/No)"]
[null,"Pension Contributions",{"$f":"'Applicant 2'!H86*12","v":0}," per year"]
[null,"Childcare Vouchers",{"$f":"'Applicant 2'!D87*12","v":0}," per year"]
[]
[null,"Income Type"]
[null,"Basic Salary",{"$f":"IF('Applicant 2'!D11<>\"Self Employed\",'Applicant 2'!H34,0)","v":0}," per year"]
[null,"All employments",{"$f":"IF('Applicant 2'!D11=\"Self Employed\",'Applicant 2'!H20,0)","v":0}," per year"]
[null,"Pension Income (incl. Private, Company and State)",{"$f":"'Applicant 2'!D44+'Applicant 2'!D45+'Applicant 2'!D43","v":0}," per year"]
[null,"Profit from Self Employment",{"$f":"'Applicant 2'!D42","v":0}," per year"]
[null,"Other",null," per year"]
[null,"Deposit Interest (Savings) Income",{"$f":"'Applicant 2'!D49","v":0}," per year"]
[null,"Annuities Income",{"$f":"'Applicant 2'!D51","v":0}," per year"]
[null,"Trust Income",{"$f":"'Applicant 2'!D50","v":0}," per year"]
[null,"Dividend Income",{"$f":"'Applicant 2'!D48","v":0}," per year"]
[null,"Investment Income",{"$f":"'Applicant 2'!D47","v":0}," per year"]
[]
[null,"Total Gross Income",{"$f":"SUM(C11:C20)","v":0}," per year"]
[]
[null,"National Insurance Income",{"$f":"C11+C12","v":0}," per year"]
[null,"Self Employed Profit National Insurance Income*",{"$f":"C14","v":0}," per year"]
[]
[null,"Non-Savings Income minus pension contributions and child care vouchers",{"$f":"SUM(C11:C15)-SUM(C7:C8)","v":0}," per year"]
[null,"Interest Income",{"$f":"SUM(C16:C18)","v":0}," per year"]
[null,"Investment Income (Top Slice)",{"$f":"SUM(C19:C20)","v":0}," per year"]
[]
[null,"Total Taxable Income",{"$f":"SUM(C27:C29)","v":0}," per year"]
[]
[null,"Total Employment PAYE tax**",{"$f":"C77","v":0}," per year"]
[null,"Total Employment National Insurance",{"$f":"C90","v":0}," per year"]
[null,"Student Loan Payment",{"$f":"IF(UPPER(LEFT(C5,1))<>\"N\",'Applicant 2'!D91*12,0)","v":0}," per year"]
[null,"Class2 NIC***",{"$f":"C96","v":0}," per year"]
[null,"Class4 NIC***",{"$f":"C110","v":0}," per year"]
[null,"Interest Income Tax ****",{"$f":"C147","v":0}," per year"]
[null,"Investment Income Tax *****",{"$f":"C170","v":0}," per year"]
[]
[null,"Total deductions",{"$f":"SUM(C33:C39)","v":0}," per year"]
[]
[null,"Annual Net Pay :",{"$f":"C31-C41","v":0}," per year"]
[null,"Monthly Net pay :",{"$f":"C43/12","v":0}," per month"]
[null,"Weekly Net pay :",{"$f":"C43/52","v":0}," per week"]
[]
[null,"* Profit from Self Employment"]
[null,"** PAYE Tax on Employment and Self Employment Incomes"]
[null,"*** Self Employed National Insurance "]
[null,"**** Tax on Deposit Interest (Savings) and Annuities Income"]
[null,"***** Tax on Dividend and Investment Income"]
[]
[null,"Personal Allowance"]
[]
[null,"Basic",{"$f":"G55","v":12570},null,null,"Basic",{"$f":"'Tax Rates'!B5","v":12570}]
[null,"Age related contribution",{"$f":"(IF(C4<65,0,IF(C4<75,G56-G55,G57-G55)))","v":0},null,null,"Age 65-74",{"$f":"G55","v":12570}]
[null,"Age related taper deduction",{"$f":"IF((C11-G60)<0,0,IF((C11-G60)/2>C56,C56,(C11-G60)/2))","v":0},null,null,"Age 75 and over",{"$f":"G55","v":12570}]
[null,"Allowance after age adjust",{"$f":"C55+C56-C57","v":12570},null,null,"Blind persons allowance",{"$f":"'Tax Rates'!B90","v":2520}]
[]
[null,"Taper deduction",{"$f":"IF(((C22-C7)-G61)<0,0,IF(((C22-C7)-G61)/2>C58,C58,((C22-C7)-G61)/2))","v":0},null,null,"Age related threshold",27700]
[null,null,null,null,null,"Taper threshold",100000]
[null,"Personal Allowance",{"$f":"C58-C60","v":12570}]
[]
[null,"Non-Savings Income Tax (Basic Salary/Self Employed/Penison(s)/IncomeEarnedAsSoleProprietorOrPartner/Trust/Other) "]
[]
[null,"Taxable Employment Income",{"$f":"C27","v":0},null,null,"Personal Allowance",{"$f":"C62","v":12570}]
[null,null,null,null,null,"Blind persons allowance",{"$f":"IF(UPPER(LEFT(C6,1))=\"Y\",G58,0)","v":0}]
[null,"Income less personal allowance",{"$f":"IF(C66>G68,C66-G68,0)","v":0},null,null,"Total Allowances",{"$f":"G67+G66","v":12570}]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate","Start of Band","End of Band","Difference"]
[null,{"$f":"TEXT(F71,\"0.0%\") & \" Rate\"","v":"20.0% Rate"},{"$f":"D71*F71","v":0},{"$f":"MIN(C68,I71)","v":0},null,{"$f":"'Tax Rates'!B42","v":0.2},0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"H71-G71","v":37700}]
[null,{"$f":"TEXT(F72,\"0.0%\") & \" Rate\"","v":"40.0% Rate"},{"$f":"D72*F72","v":0},{"$f":"IF($C$68>G72,MIN($C$68-G72,I72),0)","v":0},null,{"$f":"'Tax Rates'!B43","v":0.4},{"$f":"H71+1","v":37701},{"$f":"'Tax Rates'!B39","v":150000},{"$f":"H72-G72","v":112299}]
[null,{"$f":"TEXT(F73,\"0.0%\") & \" Rate\"","v":"45.0% Rate"},{"$f":"D73*F73","v":0},{"$f":"IF($C$68>G73,$C$68-G73,0)","v":0},null,{"$f":"'Tax Rates'!B44","v":0.45},{"$f":"H72+1","v":150001},99999999,{"$f":"H73-G73","v":99849998}]
[]
[null,"Less tax on trusts"]
[null,null,null,null,null,null,null,null,null,null,{"$f":"12500/52","v":240.38461538461539}]
[null,"Income Tax",{"$f":"SUM(C71:C73)","v":0}]
[]
[null,"National Insurance (From Basic Salary income)"]
[]
[null,"Weekly Income (Gross)",{"$f":"C24/52","v":0},null,null,"State Pension Age",65," years"]
[]
[null,null,"Tax","Carry Over",null,"Rate","Start of Band","End of Band","Difference"]
[null,{"$f":"TEXT(F84,\"0.0%\") & \" Rate\"","v":"0.0% Rate"},{"$f":"IF(C81>=G84,IF(C81>=I84,I84*F84,C81*F84),0)","v":0},{"$f":"IF((C81-I84)>0,C81-I84,0)","v":0},null,{"$f":"'Tax Rates'!B49","v":0},0,{"$f":"'Tax Rates'!B54","v":184},{"$f":"H84-G84","v":184},null,{"$f":"183*52","v":9516}]
[null,{"$f":"TEXT(F85,\"0.0%\") & \" Rate\"","v":"12.0% Rate"},{"$f":"IF(D84>0,IF(D84>=I85,I85*F85,D84*F85),0)","v":0},{"$f":"IF((D84-I85)>0,D84-I85,0)","v":0},null,{"$f":"'Tax Rates'!B50","v":0.12},{"$f":"H84+0.01","v":184.01},{"$f":"'Tax Rates'!B55","v":967},{"$f":"H85-G85+0.01","v":783}]
[null,{"$f":"TEXT(F86,\"0.0%\") & \" Rate\"","v":"2.0% Rate"},{"$f":"IF(D85>0,IF(D85>=I86,I86*F86,D85*F86),0)","v":0},{"$f":"IF((D85-I86)>0,D85-I86,0)","v":0},null,{"$f":"'Tax Rates'!B51","v":0.02},{"$f":"H85+0.01","v":967.01},99999999.989999995,{"$f":"H86-G86+0.01","v":99999032.989999995}]
[]
[null,"Total Weekly NI",{"$f":"SUM(C84:C86)","v":0}]
[null,"Age related deduction",{"$f":"IF(C4>=G81,C88*52,0)","v":0}]
[null,"Total Annual NI",{"$f":"(C88*52)-C89","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,"NIC annual maxima where employed and self employed"]
[null,"Self Employed National Insurance "]
[null,null,null,null,null,null,null,null,null,null,"STEPS"]
[null,"Class 2 Threshold",{"$f":"'Tax Rates'!B63","v":6515},null,null,null,null,null,null,null,1,{"$f":"H105","v":50270},{"$f":"H104","v":9569},{"$f":"IF(C14>0,L94-M94,0)","v":0}]
[null,"Class 2 Weekly Rate",{"$f":"'Tax Rates'!B64","v":3.05},null,null,null,null,null,null,null,2,{"$f":"N94","v":0},{"$f":"F105","v":0.09},{"$f":"M95*L95","v":0}]
[null,"Total Annual Class 2 NIC",{"$f":"IF(C25=0,0,IF(SUM(C24:C25)>C94,C95*52))","v":0},null,null,null,null,null,null,null,3,{"$f":"N95","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"53*$C$95","v":161.64999999999998},null,{"$f":"L97+L96","v":161.64999999999998}]
[null,"Class 4 Taxable Income",{"$f":"C25","v":0},null,null,null,null,null,null,null,null,null,null,null,null,"Comparative total"]
[null,null,null,null,null,null,null,null,null,null,4,null,{"$f":"N97","v":161.64999999999998},null,null,"Class 1 NIC @12%",null,{"$f":"-M100","v":0}]
[null,"Tax at 2% if employed and self employed","Tax",null,null,"Rate",null,null,null,null,null,null,{"$f":"-C85*52","v":0},null,null,"Class 2 NIC",null,{"$f":"-M101","v":0}]
[null,{"$f":"TEXT(F101,\"0.0%\")","v":"2.0%"},{"$f":"D101*F101","v":0},{"$f":"IF(C25=0,0,IF(SUM(C11:C12)>12500,C98-9500,0))","v":0},null,{"$f":"'Tax Rates'!B67","v":0.02},null,null,null,null,null,null,{"$f":"-C96","v":0},{"$f":"SUM(M99:M101)","v":161.64999999999998},null,"Class 4 NIC @9%",null,{"$f":"C25*0.09","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(R99:R101)","v":0}]
[null,null,"Tax","Carry Over",null,"Rate","Start of Band","End of Band","Difference",null,null,"Case 1",null,{"$f":"IF(N101>R102,N101,0)","v":161.64999999999998}]
[null,{"$f":"TEXT(F104,\"0.0%\") & \" Rate\"","v":"0.0% Rate"},{"$f":"IF(C101>=G104,IF(C101>=I104,I104*F104,C101*F104),0)","v":0},{"$f":"IF((C98-I104)>0,C98-I104,0)","v":0},null,{"$f":"'Tax Rates'!B70","v":0},0,{"$f":"'Tax Rates'!B75","v":9569},{"$f":"H104-G104","v":9569},null,null,"Case 2",null,{"$f":"IF(AND(N101>0,N101<R102),N101,0)","v":0},"need to add additional 2%"]
[null,{"$f":"TEXT(F105,\"0.0%\") & \" Rate\"","v":"9.0% Rate"},{"$f":"IF(D104>0,IF(D104>=I105,I105*F105,D104*F105),0)","v":0},{"$f":"IF((D104-I105)>0,D104-I105,0)","v":0},null,{"$f":"'Tax Rates'!B71","v":0.09},{"$f":"IF(H104>=1,H104+1,H104)","v":9570},{"$f":"'Tax Rates'!B76","v":50270},{"$f":"H105-G105","v":40700},null,null,"Case 3",null,{"$f":"IF(N101<0,N101,0)","v":0},"NIC4 @9% is NIL "]
[null,{"$f":"TEXT(F106,\"0.0%\") & \" Rate\"","v":"2.0% Rate"},{"$f":"IF(D105>0,IF(D105>=I106,I106*F106,D105*F106),0)","v":0},{"$f":"IF((D105-I106)>0,D105-I106,0)","v":0},null,{"$f":"'Tax Rates'!B72","v":0.02},{"$f":"H105+1","v":50271},99999999,{"$f":"H106-G106+1","v":99949729}]
[null,null,null,null,null,null,null,null,null,null,null,"Step5-9 only for Case 2 & 3"]
[null,"Total Class 4 NIC (where no Class 1 NIC)",{"$f":"IF(D101>0,C101,SUM(C104:C106))","v":0},null,null,null,null,null,null,null,5,{"$f":"IF(OR(N105>0,N104>0),N101,0)","v":0},{"$f":"100/9","v":11.111111111111111},{"$f":"L108*M108","v":0}]
[null,null,null,null,null,null,null,null,null,null,6,{"$f":"MIN(C25,H105)","v":0},{"$f":"-I104","v":-9569},{"$f":"L109+M109","v":-9569}]
[null,"Total Annual Class 4 NIC payable",{"$f":"IF(C90<0.01,C108,IF(N103>0,R101,IF(N104>0,N119,IF(N105<0,N119,0))))","v":0},null,null,null,null,null,null,null,7,null,null,{"$f":"N109-N108","v":-9569}]
[null,null,null,null,null,null,null,null,null,null,null,"If STEP 7 negative then Nil"]
[null,"Student Loan Repayment",null,null,null,null,null,null,null,null,8,{"$f":"IF(N110<0,0,N110)","v":0},{"$f":"$F$106","v":0.02},{"$f":"L112*M112","v":0}]
[null,null,null,null,null,null,null,null,null,null,9,{"$f":"C25","v":0},{"$f":"-H105","v":-50270},{"$f":"IF((L113+M113)<0,0,(L113+M113)*$F$106)","v":0}]
[null,"Threshold",{"$f":"IF(RIGHT(C5,1)=\"1\",G115/12,IF(RIGHT(C5,1)=\"2\",H115/12,0))","v":0},null,null,null,"Plan 1","Plan 2"]
[null,"Income above threshold",{"$f":"IF(SUM(C11:C12,C14)/12-C114<0,0,SUM(C11:C12,C14)/12-C114)","v":0},null,null,"Repayment Threshold",{"$f":"'Tax Rates'!B27*12","v":19884},{"$f":"'Tax Rates'!B28*12","v":27288},null,null,null,"Total Class 4 NIC"]
[null,"Repayment (if applicable)",{"$f":"FLOOR(C115*G116,1)*12","v":0},null,null,"Repayment Rate",{"$f":"'Tax Rates'!B31","v":0.09},{"$f":"'Tax Rates'!B32","v":0.09},null,null,null,"Step 4",null,{"$f":"IF(N101<0,0,N101)","v":161.64999999999998}]
[null,null,null,null,null,null,null,null,null,null,null,"Step 8",null,{"$f":"N112","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,"Step 9",null,{"$f":"N113","v":0}]
[null,"Interest Income Tax (Deposit Interest (Savings), Annuities, Trust)",null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(N116:N118)","v":161.64999999999998}]
[]
[null,"Interest Income",{"$f":"C28","v":0}]
[]
[null,"Non-Savings Income minus allowance",{"$f":"C68","v":0}]
[null,"Gross Income (for allowance calculation)",{"$f":"C31","v":0}]
[]
[null,null,"Calculated Allowance",null,null,"Start of Band","End of Band","Difference","Within Band"]
[null,null,{"$f":"IF(I127=1,'Tax Rates'!B9,0)","v":5000},null,null,0,{"$f":"'Tax Rates'!B15","v":12570},{"$f":"G127-F127","v":12570},{"$f":"IF(AND($C$124>=$F127,$C$124<=$G127),1,0)","v":1}]
[null,null,{"$f":"IF(I128=1,'Tax Rates'!B9-(C123-G127),0)","v":0},null,null,{"$f":"IF(G127>=1,G127+1,G127)","v":12571},{"$f":"'Tax Rates'!B16","v":17570},{"$f":"G128-F128","v":4999},{"$f":"IF(AND($C$124>=$F128,$C$124<=$G128),1,0)","v":0}]
[null,null,{"$f":"IF(I129=1,'Tax Rates'!B10,0)","v":0},null,null,{"$f":"G128+1","v":17571},{"$f":"'Tax Rates'!B17","v":50270},{"$f":"G129-F129+1","v":32700},0]
[null,null,{"$f":"IF(I130=1,'Tax Rates'!B11,0)","v":0},null,null,{"$f":"G129+1","v":50271},{"$f":"'Tax Rates'!B18","v":150000},{"$f":"G130-F130+1","v":99730},0]
[null,null,{"$f":"IF(I131=1,'Tax Rates'!B12,0)","v":0},null,null,{"$f":"G130+1","v":150001},99999999,{"$f":"G131-F131+1","v":99849999},0]
[]
[null,"Interest Income Tax Allowance",{"$f":"SUM(C127:C131)","v":5000}]
[]
[null,null,null,"Taxable Interest Income by tax band",null,"Start of Band","End of Band","Scenario check 1","Pay+Interest check","Pay+Allowance check","Scenaio Check 2","Basic ","Upper ","Additional","Allowance 2","Allowance 3"]
[null,null,"Basic Tax Band",{"$f":"IF(H136=1,L136,IF(H137=1,L137,IF(H138=1,L138,0)))","v":32700},null,0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"IF(AND($C$123>=F136,$C$123<=G136),1,0)","v":1},{"$f":"IF(AND($C$123+$C$121>F136,$C$123+$C$121<=G136),1,0)","v":0},{"$f":"IF(AND($C$123+$C$133>F136,$C$123+$C$133<=G136),1,0)","v":1},{"$f":"IF(AND(I136=1,J136=1),1,IF(AND(I136=0,J136=1),2,IF(AND(I136=0,J136=0),3,IF(AND(I136=1,J136=0),4,0))))","v":2},{"$f":"IF(K136=1,C121-C133,IF(K136=2,G136-C123-C133,IF(K136=3,0,IF(K136=4,0,0))))","v":32700},{"$f":"IF(K136=1,0,IF(K136=2,MIN(C123+C121-G136,G137-G136),IF(K136=3,MIN(C121-C133,G137-G136-O136),IF(K136=4,0,0))))","v":-37700},{"$f":"IF(C123+C121>G137,IF(K136=1,0,IF(K136=2,C123+C121-G137,IF(K136=3,C123+C121-G137,IF(K136=4,0,0)))),0)","v":0},{"$f":"$C$123+$C$133-G136","v":-32700},{"$f":"$C$133-O136","v":37700}]
[null,null,"Upper Tax Band",{"$f":"IF(H136=1,M136,IF(H137=1,M137,IF(H138=1,M138,0)))","v":-37700},null,37501,{"$f":"'Tax Rates'!B39","v":150000},{"$f":"IF(AND($C$123>=F137,$C$123<=G137),1,0)","v":0},{"$f":"IF(AND($C$123+$C$121>F137,$C$123+$C$121<=G137),1,0)","v":0},{"$f":"IF(AND($C$123+$C$133>F137,$C$123+$C$133<=G137),1,0)","v":0},{"$f":"IF(AND(I137=1,J137=1),1,IF(AND(I137=0,J137=1),2,IF(AND(I137=0,J137=0),3,IF(AND(I137=1,J137=0),4,0))))","v":3},0,{"$f":"IF(K137=1,C121-C133,IF(K137=2,G137-C123-C133,IF(K137=3,0,IF(K137=4,0,0))))","v":0},{"$f":"IF(K137=1,0,IF(K137=2,C123+C121-G137,IF(K137=3,C121-C133,IF(K137=4,0,0))))","v":-5000},{"$f":"$C$123+$C$133-G137","v":-145000},{"$f":"$C$133-O137","v":150000}]
[null,null,"Additional Tax Band",{"$f":"IF(H136=1,N136,IF(H137=1,N137,IF(H138=1,N138,0)))","v":0},null,150001,99999999,{"$f":"IF(AND($C$123>=F138,$C$123<=G138),1,0)","v":0},0,{"$f":"IF(AND($C$123+$C$133>F138,$C$123+$C$133<=G138),1,0)","v":0},{"$f":"IF(AND(I138=1,J138=1),1,IF(AND(I138=0,J138=1),2,IF(AND(I138=0,J138=0),3,IF(AND(I138=1,J138=0),4,0))))","v":3},0,0,{"$f":"C121-C133","v":-5000},{"$f":"$C$123+$C$133-G138","v":-99994999},99999999]
[]
[]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate"]
[null,{"$f":"TEXT(F143,\"0.0%\") & \" Rate\"","v":"20.0% Rate"},{"$f":"D143*F143","v":6540},{"$f":"D136","v":32700},null,{"$f":"'Tax Rates'!B42","v":0.2}]
[null,{"$f":"TEXT(F144,\"0.0%\") & \" Rate\"","v":"40.0% Rate"},{"$f":"D144*F144","v":-15080},{"$f":"D137","v":-37700},null,{"$f":"'Tax Rates'!B43","v":0.4}]
[null,{"$f":"TEXT(F145,\"0.0%\") & \" Rate\"","v":"45.0% Rate"},{"$f":"D145*F145","v":0},{"$f":"D138","v":0},null,{"$f":"'Tax Rates'!B44","v":0.45}]
[]
[null,"Interest Income Tax",{"$f":"IF(OR(C121<=C133,C121=0),0,SUM(C143:C145))","v":0}]
[]
[]
[null,"Investment Income Tax (Dividend/Investment)"]
[]
[null,"Investment Income",{"$f":"C29","v":0}]
[null,"Dividend/Investment Income Allowance ",{"$f":"'Tax Rates'!B22","v":2000}]
[]
[null,"Non-Savings Income minus personal allowance and Interest Income",{"$f":"C68+C28","v":0}]
[]
[]
[null,null,null,"Taxable Interest Income by tax band",null,"Start of Band","End of Band","Scenario check 1","Pay+Investment check","Pay+Allowance check","Scenaio Check 2","Basic ","Upper ","Additional","Allowance 2","Allowance 3"]
[null,null,"Basic Tax Band",{"$f":"IF(H159=1,L159,IF(H160=1,L160,IF(H161=1,L161,0)))","v":35700},null,0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"IF(AND($C$155>=F159,$C$155<=G159),1,0)","v":1},{"$f":"IF(AND($C$155+$C$152>F159,$C$155+$C$152<=G159),1,0)","v":0},{"$f":"IF(AND($C$155+$C$153>F159,$C$155+$C$153<=G159),1,0)","v":1},{"$f":"IF(AND(I159=1,J159=1),1,IF(AND(I159=0,J159=1),2,IF(AND(I159=0,J159=0),3,IF(AND(I159=1,J159=0),4,0))))","v":2},{"$f":"IF(K159=1,C152-C153,IF(K159=2,G159-C155-C153,IF(K159=3,0,IF(K159=4,0,0))))","v":35700},{"$f":"IF(K159=1,0,IF(K159=2,MIN(C155+C152-G159,G160-G159),IF(K159=3,MIN(C152-C153,G160-G159-O159),IF(K159=4,0,0))))","v":-37700},{"$f":"IF(C153+C152>G160,IF(K159=1,0,IF(K159=2,C153+C152-G160,IF(K159=3,C153+C152-G160,IF(K159=4,0,0)))),0)","v":0},{"$f":"$C$155+$C$153-G159","v":-35700},{"$f":"$C$153-O159","v":37700}]
[null,null,"Upper Tax Band",{"$f":"IF(H159=1,M159,IF(H160=1,M160,IF(H161=1,M161,0)))","v":-37700},null,37501,{"$f":"'Tax Rates'!B39","v":150000},{"$f":"IF(AND($C$155>=F160,$C$155<=G160),1,0)","v":0},{"$f":"IF(AND($C$155+$C$152>F160,$C$155+$C$152<=G160),1,0)","v":0},{"$f":"IF(AND($C$155+$C$153>F160,$C$155+$C$153<=G160),1,0)","v":0},{"$f":"IF(AND(I160=1,J160=1),1,IF(AND(I160=0,J160=1),2,IF(AND(I160=0,J160=0),3,IF(AND(I160=1,J160=0),4,0))))","v":3},0,{"$f":"IF(K160=1,C152-C153,IF(K160=2,G160-C155-C153,IF(K160=3,0,IF(K160=4,0,0))))","v":0},{"$f":"IF(K160=1,0,IF(K160=2,C155+C152-G160,IF(K160=3,C152-C153,IF(K160=4,0,0))))","v":-2000},{"$f":"$C$155+$C$153-G160","v":-148000},{"$f":"$C$153-O160","v":150000}]
[null,null,"Additional Tax Band",{"$f":"IF(H159=1,N159,IF(H160=1,N160,IF(H161=1,N161,0)))","v":0},null,150001,99999999,{"$f":"IF(AND($C$155>=F161,$C$155<=G161),1,0)","v":0},0,{"$f":"IF(AND($C$155+$C$153>F161,$C$155+$C$153<=G161),1,0)","v":0},3,0,0,{"$f":"C152-C153","v":-2000},{"$f":"$C$155+$C$153-G161","v":-99997999},{"$f":"$C$153-O161","v":99999999}]
[]
[]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate"]
[null,{"$f":"TEXT(F166,\"0.0%\") & \" Rate\"","v":"7.5% Rate"},{"$f":"D166*F166","v":2677.5},{"$f":"D159","v":35700},null,{"$f":"'Tax Rates'!B83","v":7.4999999999999997E-2}]
[null,{"$f":"TEXT(F167,\"0.0%\") & \" Rate\"","v":"32.5% Rate"},{"$f":"D167*F167","v":-12252.5},{"$f":"D160","v":-37700},null,{"$f":"'Tax Rates'!B84","v":0.32500000000000001}]
[null,{"$f":"TEXT(F168,\"0.0%\") & \" Rate\"","v":"38.1% Rate"},{"$f":"D168*F168","v":0},0,null,{"$f":"'Tax Rates'!B85","v":0.38100000000000001}]
[]
[null,"Investment Income Tax",{"$f":"IF(OR(C152=0,C152<=C153),0,SUM(C166:C168))","v":0}]

{"sheet":"Tax Applicant 3","index":15,"n_rows":170,"n_cols":18,"rows_kept":170,"cut":false,"hidden":true,"merged":[]}
[null,"UK Taxation"]
[null,null," "]
[null,"Additional Deduction Fields"]
[null,"Age",{"$f":"'Applicant 3'!H5","v":"Input DOB"}," years"]
[null,"Student Loan Payment",{"$f":"IF('Applicant 3'!D91>0,\"Plan2\",\"No\")","v":"No"},"(Plan2/No)"]
[null,"Blind","No","(Yes/No)"]
[null,"Pension Contributions",{"$f":"'Applicant 3'!H86*12","v":0}," per year"]
[null,"Childcare Vouchers",{"$f":"'Applicant 3'!D87*12","v":0}," per year"]
[]
[null,"Income Type"]
[null,"Basic Salary",{"$f":"IF('Applicant 3'!D11<>\"Self Employed\",'Applicant 3'!H34,0)","v":0}," per year"]
[null,"All employments",{"$f":"IF('Applicant 3'!D11=\"Self Employed\",'Applicant 3'!H20,0)","v":0}," per year"]
[null,"Pension Income (incl. Private, Company and State)",{"$f":"'Applicant 3'!D44+'Applicant 3'!D45+'Applicant 3'!D43","v":0}," per year"]
[null,"Profit from Self Employment",{"$f":"'Applicant 3'!D42","v":0}," per year"]
[null,"Other",null," per year"]
[null,"Deposit Interest (Savings) Income",{"$f":"'Applicant 3'!D49","v":0}," per year"]
[null,"Annuities Income",{"$f":"'Applicant 3'!D51","v":0}," per year"]
[null,"Trust Income",{"$f":"'Applicant 3'!D50","v":0}," per year"]
[null,"Dividend Income",{"$f":"'Applicant 3'!D48","v":0}," per year"]
[null,"Investment Income",{"$f":"'Applicant 3'!D47","v":0}," per year"]
[]
[null,"Total Gross Income",{"$f":"SUM(C11:C20)","v":0}," per year"]
[]
[null,"National Insurance Income",{"$f":"C11+C12","v":0}," per year"]
[null,"Self Employed Profit National Insurance Income*",{"$f":"C14","v":0}," per year"]
[]
[null,"Non-Savings Income minus pension contributions and child care vouchers",{"$f":"SUM(C11:C15)-SUM(C7:C8)","v":0}," per year"]
[null,"Interest Income",{"$f":"SUM(C16:C18)","v":0}," per year"]
[null,"Investment Income (Top Slice)",{"$f":"SUM(C19:C20)","v":0}," per year"]
[]
[null,"Total Taxable Income",{"$f":"SUM(C27:C29)","v":0}," per year"]
[]
[null,"Total Employment PAYE tax**",{"$f":"C77","v":0}," per year"]
[null,"Total Employment National Insurance",{"$f":"C90","v":0}," per year"]
[null,"Student Loan Payment",{"$f":"IF(UPPER(LEFT(C5,1))<>\"N\",'Applicant 3'!D91*12,0)","v":0}," per year"]
[null,"Class2 NIC***",{"$f":"C96","v":0}," per year"]
[null,"Class4 NIC***",{"$f":"C110","v":0}," per year"]
[null,"Interest Income Tax ****",{"$f":"C147","v":0}," per year"]
[null,"Investment Income Tax *****",{"$f":"C170","v":0}," per year"]
[]
[null,"Total deductions",{"$f":"SUM(C33:C39)","v":0}," per year"]
[]
[null,"Annual Net Pay :",{"$f":"C31-C41","v":0}," per year"]
[null,"Monthly Net pay :",{"$f":"C43/12","v":0}," per month"]
[null,"Weekly Net pay :",{"$f":"C43/52","v":0}," per week"]
[]
[null,"* Profit from Self Employment"]
[null,"** PAYE Tax on Employment and Self Employment Incomes"]
[null,"*** Self Employed National Insurance "]
[null,"**** Tax on Deposit Interest (Savings) and Annuities Income"]
[null,"***** Tax on Dividend and Investment Income"]
[]
[null,"Personal Allowance"]
[]
[null,"Basic",{"$f":"G55","v":12570},null,null,"Basic",{"$f":"'Tax Rates'!B5","v":12570}]
[null,"Age related contribution",{"$f":"(IF(C4<65,0,IF(C4<75,G56-G55,G57-G55)))","v":0},null,null,"Age 65-74",{"$f":"G55","v":12570}]
[null,"Age related taper deduction",{"$f":"IF((C11-G60)<0,0,IF((C11-G60)/2>C56,C56,(C11-G60)/2))","v":0},null,null,"Age 75 and over",{"$f":"G55","v":12570}]
[null,"Allowance after age adjust",{"$f":"C55+C56-C57","v":12570},null,null,"Blind persons allowance",{"$f":"'Tax Rates'!B90","v":2520}]
[]
[null,"Taper deduction",{"$f":"IF(((C22-C7)-G61)<0,0,IF(((C22-C7)-G61)/2>C58,C58,((C22-C7)-G61)/2))","v":0},null,null,"Age related threshold",27700]
[null,null,null,null,null,"Taper threshold",100000]
[null,"Personal Allowance",{"$f":"C58-C60","v":12570}]
[]
[null,"Non-Savings Income Tax (Basic Salary/Self Employed/Penison(s)/IncomeEarnedAsSoleProprietorOrPartner/Trust/Other) "]
[]
[null,"Taxable Employment Income",{"$f":"C27","v":0},null,null,"Personal Allowance",{"$f":"C62","v":12570}]
[null,null,null,null,null,"Blind persons allowance",{"$f":"IF(UPPER(LEFT(C6,1))=\"Y\",G58,0)","v":0}]
[null,"Income less personal allowance",{"$f":"IF(C66>G68,C66-G68,0)","v":0},null,null,"Total Allowances",{"$f":"G67+G66","v":12570}]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate","Start of Band","End of Band","Difference"]
[null,{"$f":"TEXT(F71,\"0.0%\") & \" Rate\"","v":"20.0% Rate"},{"$f":"D71*F71","v":0},{"$f":"MIN(C68,I71)","v":0},null,{"$f":"'Tax Rates'!B42","v":0.2},0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"H71-G71","v":37700}]
[null,{"$f":"TEXT(F72,\"0.0%\") & \" Rate\"","v":"40.0% Rate"},{"$f":"D72*F72","v":0},{"$f":"IF($C$68>G72,MIN($C$68-G72,I72),0)","v":0},null,{"$f":"'Tax Rates'!B43","v":0.4},{"$f":"H71+1","v":37701},{"$f":"'Tax Rates'!B39","v":150000},{"$f":"H72-G72","v":112299}]
[null,{"$f":"TEXT(F73,\"0.0%\") & \" Rate\"","v":"45.0% Rate"},{"$f":"D73*F73","v":0},{"$f":"IF($C$68>G73,$C$68-G73,0)","v":0},null,{"$f":"'Tax Rates'!B44","v":0.45},{"$f":"H72+1","v":150001},99999999,{"$f":"H73-G73","v":99849998}]
[]
[null,"Less tax on trusts"]
[null,null,null,null,null,null,null,null,null,null,{"$f":"12500/52","v":240.38461538461539}]
[null,"Income Tax",{"$f":"SUM(C71:C73)","v":0}]
[]
[null,"National Insurance (From Basic Salary income)"]
[]
[null,"Weekly Income (Gross)",{"$f":"C24/52","v":0},null,null,"State Pension Age",65," years"]
[]
[null,null,"Tax","Carry Over",null,"Rate","Start of Band","End of Band","Difference"]
[null,{"$f":"TEXT(F84,\"0.0%\") & \" Rate\"","v":"0.0% Rate"},{"$f":"IF(C81>=G84,IF(C81>=I84,I84*F84,C81*F84),0)","v":0},{"$f":"IF((C81-I84)>0,C81-I84,0)","v":0},null,{"$f":"'Tax Rates'!B49","v":0},0,{"$f":"'Tax Rates'!B54","v":184},{"$f":"H84-G84","v":184},null,{"$f":"183*52","v":9516}]
[null,{"$f":"TEXT(F85,\"0.0%\") & \" Rate\"","v":"12.0% Rate"},{"$f":"IF(D84>0,IF(D84>=I85,I85*F85,D84*F85),0)","v":0},{"$f":"IF((D84-I85)>0,D84-I85,0)","v":0},null,{"$f":"'Tax Rates'!B50","v":0.12},{"$f":"H84+0.01","v":184.01},{"$f":"'Tax Rates'!B55","v":967},{"$f":"H85-G85+0.01","v":783}]
[null,{"$f":"TEXT(F86,\"0.0%\") & \" Rate\"","v":"2.0% Rate"},{"$f":"IF(D85>0,IF(D85>=I86,I86*F86,D85*F86),0)","v":0},{"$f":"IF((D85-I86)>0,D85-I86,0)","v":0},null,{"$f":"'Tax Rates'!B51","v":0.02},{"$f":"H85+0.01","v":967.01},99999999.989999995,{"$f":"H86-G86+0.01","v":99999032.989999995}]
[]
[null,"Total Weekly NI",{"$f":"SUM(C84:C86)","v":0}]
[null,"Age related deduction",{"$f":"IF(C4>=G81,C88*52,0)","v":0}]
[null,"Total Annual NI",{"$f":"(C88*52)-C89","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,"NIC annual maxima where employed and self employed"]
[null,"Self Employed National Insurance "]
[null,null,null,null,null,null,null,null,null,null,"STEPS"]
[null,"Class 2 Threshold",{"$f":"'Tax Rates'!B63","v":6515},null,null,null,null,null,null,null,1,{"$f":"H105","v":50270},{"$f":"H104","v":9569},{"$f":"IF(C14>0,L94-M94,0)","v":0}]
[null,"Class 2 Weekly Rate",{"$f":"'Tax Rates'!B64","v":3.05},null,null,null,null,null,null,null,2,{"$f":"N94","v":0},{"$f":"F105","v":0.09},{"$f":"M95*L95","v":0}]
[null,"Total Annual Class 2 NIC",{"$f":"IF(C25=0,0,IF(SUM(C24:C25)>C94,C95*52))","v":0},null,null,null,null,null,null,null,3,{"$f":"N95","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"53*$C$95","v":161.64999999999998},null,{"$f":"L97+L96","v":161.64999999999998}]
[null,"Class 4 Taxable Income",{"$f":"C25","v":0},null,null,null,null,null,null,null,null,null,null,null,null,"Comparative total"]
[null,null,null,null,null,null,null,null,null,null,4,null,{"$f":"N97","v":161.64999999999998},null,null,"Class 1 NIC @12%",null,{"$f":"-M100","v":0}]
[null,"Tax at 2% if employed and self employed","Tax",null,null,"Rate",null,null,null,null,null,null,{"$f":"-C85*52","v":0},null,null,"Class 2 NIC",null,{"$f":"-M101","v":0}]
[null,{"$f":"TEXT(F101,\"0.0%\")","v":"2.0%"},{"$f":"D101*F101","v":0},{"$f":"IF(C25=0,0,IF(SUM(C11:C12)>12500,C98-9500,0))","v":0},null,{"$f":"'Tax Rates'!B67","v":0.02},null,null,null,null,null,null,{"$f":"-C96","v":0},{"$f":"SUM(M99:M101)","v":161.64999999999998},null,"Class 4 NIC @9%",null,{"$f":"C25*0.09","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(R99:R101)","v":0}]
[null,null,"Tax","Carry Over",null,"Rate","Start of Band","End of Band","Difference",null,null,"Case 1",null,{"$f":"IF(N101>R102,N101,0)","v":161.64999999999998}]
[null,{"$f":"TEXT(F104,\"0.0%\") & \" Rate\"","v":"0.0% Rate"},{"$f":"IF(C101>=G104,IF(C101>=I104,I104*F104,C101*F104),0)","v":0},{"$f":"IF((C98-I104)>0,C98-I104,0)","v":0},null,{"$f":"'Tax Rates'!B70","v":0},0,{"$f":"'Tax Rates'!B75","v":9569},{"$f":"H104-G104","v":9569},null,null,"Case 2",null,{"$f":"IF(AND(N101>0,N101<R102),N101,0)","v":0},"need to add additional 2%"]
[null,{"$f":"TEXT(F105,\"0.0%\") & \" Rate\"","v":"9.0% Rate"},{"$f":"IF(D104>0,IF(D104>=I105,I105*F105,D104*F105),0)","v":0},{"$f":"IF((D104-I105)>0,D104-I105,0)","v":0},null,{"$f":"'Tax Rates'!B71","v":0.09},{"$f":"IF(H104>=1,H104+1,H104)","v":9570},{"$f":"'Tax Rates'!B76","v":50270},{"$f":"H105-G105","v":40700},null,null,"Case 3",null,{"$f":"IF(N101<0,N101,0)","v":0},"NIC4 @9% is NIL "]
[null,{"$f":"TEXT(F106,\"0.0%\") & \" Rate\"","v":"2.0% Rate"},{"$f":"IF(D105>0,IF(D105>=I106,I106*F106,D105*F106),0)","v":0},{"$f":"IF((D105-I106)>0,D105-I106,0)","v":0},null,{"$f":"'Tax Rates'!B72","v":0.02},{"$f":"H105+1","v":50271},99999999,{"$f":"H106-G106+1","v":99949729}]
[null,null,null,null,null,null,null,null,null,null,null,"Step5-9 only for Case 2 & 3"]
[null,"Total Class 4 NIC (where no Class 1 NIC)",{"$f":"IF(D101>0,C101,SUM(C104:C106))","v":0},null,null,null,null,null,null,null,5,{"$f":"IF(OR(N105>0,N104>0),N101,0)","v":0},{"$f":"100/9","v":11.111111111111111},{"$f":"L108*M108","v":0}]
[null,null,null,null,null,null,null,null,null,null,6,{"$f":"MIN(C25,H105)","v":0},{"$f":"-I104","v":-9569},{"$f":"L109+M109","v":-9569}]
[null,"Total Annual Class 4 NIC payable",{"$f":"IF(C90<0.01,C108,IF(N103>0,R101,IF(N104>0,N119,IF(N105<0,N119,0))))","v":0},null,null,null,null,null,null,null,7,null,null,{"$f":"N109-N108","v":-9569}]
[null,null,null,null,null,null,null,null,null,null,null,"If STEP 7 negative then Nil"]
[null,"Student Loan Repayment",null,null,null,null,null,null,null,null,8,{"$f":"IF(N110<0,0,N110)","v":0},{"$f":"$F$106","v":0.02},{"$f":"L112*M112","v":0}]
[null,null,null,null,null,null,null,null,null,null,9,{"$f":"C25","v":0},{"$f":"-H105","v":-50270},{"$f":"IF((L113+M113)<0,0,(L113+M113)*$F$106)","v":0}]
[null,"Threshold",{"$f":"IF(RIGHT(C5,1)=\"1\",G115/12,IF(RIGHT(C5,1)=\"2\",H115/12,0))","v":0},null,null,null,"Plan 1","Plan 2"]
[null,"Income above threshold",{"$f":"IF(SUM(C11:C12,C14)/12-C114<0,0,SUM(C11:C12,C14)/12-C114)","v":0},null,null,"Repayment Threshold",{"$f":"'Tax Rates'!B27*12","v":19884},{"$f":"'Tax Rates'!B28*12","v":27288},null,null,null,"Total Class 4 NIC"]
[null,"Repayment (if applicable)",{"$f":"FLOOR(C115*G116,1)*12","v":0},null,null,"Repayment Rate",{"$f":"'Tax Rates'!B31","v":0.09},{"$f":"'Tax Rates'!B32","v":0.09},null,null,null,"Step 4",null,{"$f":"IF(N101<0,0,N101)","v":161.64999999999998}]
[null,null,null,null,null,null,null,null,null,null,null,"Step 8",null,{"$f":"N112","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,"Step 9",null,{"$f":"N113","v":0}]
[null,"Interest Income Tax (Deposit Interest (Savings), Annuities, Trust)",null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(N116:N118)","v":161.64999999999998}]
[]
[null,"Interest Income",{"$f":"C28","v":0}]
[]
[null,"Non-Savings Income minus allowance",{"$f":"C68","v":0}]
[null,"Gross Income (for allowance calculation)",{"$f":"C31","v":0}]
[]
[null,null,"Calculated Allowance",null,null,"Start of Band","End of Band","Difference","Within Band"]
[null,null,{"$f":"IF(I127=1,'Tax Rates'!B9,0)","v":5000},null,null,0,{"$f":"'Tax Rates'!B15","v":12570},{"$f":"G127-F127","v":12570},{"$f":"IF(AND($C$124>=$F127,$C$124<=$G127),1,0)","v":1}]
[null,null,{"$f":"IF(I128=1,'Tax Rates'!B9-(C123-G127),0)","v":0},null,null,{"$f":"IF(G127>=1,G127+1,G127)","v":12571},{"$f":"'Tax Rates'!B16","v":17570},{"$f":"G128-F128","v":4999},{"$f":"IF(AND($C$124>=$F128,$C$124<=$G128),1,0)","v":0}]
[null,null,{"$f":"IF(I129=1,'Tax Rates'!B10,0)","v":0},null,null,{"$f":"G128+1","v":17571},{"$f":"'Tax Rates'!B17","v":50270},{"$f":"G129-F129+1","v":32700},0]
[null,null,{"$f":"IF(I130=1,'Tax Rates'!B11,0)","v":0},null,null,{"$f":"G129+1","v":50271},{"$f":"'Tax Rates'!B18","v":150000},{"$f":"G130-F130+1","v":99730},0]
[null,null,{"$f":"IF(I131=1,'Tax Rates'!B12,0)","v":0},null,null,{"$f":"G130+1","v":150001},99999999,{"$f":"G131-F131+1","v":99849999},0]
[]
[null,"Interest Income Tax Allowance",{"$f":"SUM(C127:C131)","v":5000}]
[]
[null,null,null,"Taxable Interest Income by tax band",null,"Start of Band","End of Band","Scenario check 1","Pay+Interest check","Pay+Allowance check","Scenaio Check 2","Basic ","Upper ","Additional","Allowance 2","Allowance 3"]
[null,null,"Basic Tax Band",{"$f":"IF(H136=1,L136,IF(H137=1,L137,IF(H138=1,L138,0)))","v":32700},null,0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"IF(AND($C$123>=F136,$C$123<=G136),1,0)","v":1},{"$f":"IF(AND($C$123+$C$121>F136,$C$123+$C$121<=G136),1,0)","v":0},{"$f":"IF(AND($C$123+$C$133>F136,$C$123+$C$133<=G136),1,0)","v":1},{"$f":"IF(AND(I136=1,J136=1),1,IF(AND(I136=0,J136=1),2,IF(AND(I136=0,J136=0),3,IF(AND(I136=1,J136=0),4,0))))","v":2},{"$f":"IF(K136=1,C121-C133,IF(K136=2,G136-C123-C133,IF(K136=3,0,IF(K136=4,0,0))))","v":32700},{"$f":"IF(K136=1,0,IF(K136=2,MIN(C123+C121-G136,G137-G136),IF(K136=3,MIN(C121-C133,G137-G136-O136),IF(K136=4,0,0))))","v":-37700},{"$f":"IF(C123+C121>G137,IF(K136=1,0,IF(K136=2,C123+C121-G137,IF(K136=3,C123+C121-G137,IF(K136=4,0,0)))),0)","v":0},{"$f":"$C$123+$C$133-G136","v":-32700},{"$f":"$C$133-O136","v":37700}]
[null,null,"Upper Tax Band",{"$f":"IF(H136=1,M136,IF(H137=1,M137,IF(H138=1,M138,0)))","v":-37700},null,37501,{"$f":"'Tax Rates'!B39","v":150000},{"$f":"IF(AND($C$123>=F137,$C$123<=G137),1,0)","v":0},{"$f":"IF(AND($C$123+$C$121>F137,$C$123+$C$121<=G137),1,0)","v":0},{"$f":"IF(AND($C$123+$C$133>F137,$C$123+$C$133<=G137),1,0)","v":0},{"$f":"IF(AND(I137=1,J137=1),1,IF(AND(I137=0,J137=1),2,IF(AND(I137=0,J137=0),3,IF(AND(I137=1,J137=0),4,0))))","v":3},0,{"$f":"IF(K137=1,C121-C133,IF(K137=2,G137-C123-C133,IF(K137=3,0,IF(K137=4,0,0))))","v":0},{"$f":"IF(K137=1,0,IF(K137=2,C123+C121-G137,IF(K137=3,C121-C133,IF(K137=4,0,0))))","v":-5000},{"$f":"$C$123+$C$133-G137","v":-145000},{"$f":"$C$133-O137","v":150000}]
[null,null,"Additional Tax Band",{"$f":"IF(H136=1,N136,IF(H137=1,N137,IF(H138=1,N138,0)))","v":0},null,150001,99999999,{"$f":"IF(AND($C$123>=F138,$C$123<=G138),1,0)","v":0},0,{"$f":"IF(AND($C$123+$C$133>F138,$C$123+$C$133<=G138),1,0)","v":0},{"$f":"IF(AND(I138=1,J138=1),1,IF(AND(I138=0,J138=1),2,IF(AND(I138=0,J138=0),3,IF(AND(I138=1,J138=0),4,0))))","v":3},0,0,{"$f":"C121-C133","v":-5000},{"$f":"$C$123+$C$133-G138","v":-99994999},99999999]
[]
[]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate"]
[null,{"$f":"TEXT(F143,\"0.0%\") & \" Rate\"","v":"20.0% Rate"},{"$f":"D143*F143","v":6540},{"$f":"D136","v":32700},null,{"$f":"'Tax Rates'!B42","v":0.2}]
[null,{"$f":"TEXT(F144,\"0.0%\") & \" Rate\"","v":"40.0% Rate"},{"$f":"D144*F144","v":-15080},{"$f":"D137","v":-37700},null,{"$f":"'Tax Rates'!B43","v":0.4}]
[null,{"$f":"TEXT(F145,\"0.0%\") & \" Rate\"","v":"45.0% Rate"},{"$f":"D145*F145","v":0},{"$f":"D138","v":0},null,{"$f":"'Tax Rates'!B44","v":0.45}]
[]
[null,"Interest Income Tax",{"$f":"IF(OR(C121<=C133,C121=0),0,SUM(C143:C145))","v":0}]
[]
[]
[null,"Investment Income Tax (Dividend/Investment)"]
[]
[null,"Investment Income",{"$f":"C29","v":0}]
[null,"Dividend/Investment Income Allowance ",{"$f":"'Tax Rates'!B22","v":2000}]
[]
[null,"Non-Savings Income minus personal allowance and Interest Income",{"$f":"C68+C28","v":0}]
[]
[]
[null,null,null,"Taxable Interest Income by tax band",null,"Start of Band","End of Band","Scenario check 1","Pay+Investment check","Pay+Allowance check","Scenaio Check 2","Basic ","Upper ","Additional","Allowance 2","Allowance 3"]
[null,null,"Basic Tax Band",{"$f":"IF(H159=1,L159,IF(H160=1,L160,IF(H161=1,L161,0)))","v":35700},null,0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"IF(AND($C$155>=F159,$C$155<=G159),1,0)","v":1},{"$f":"IF(AND($C$155+$C$152>F159,$C$155+$C$152<=G159),1,0)","v":0},{"$f":"IF(AND($C$155+$C$153>F159,$C$155+$C$153<=G159),1,0)","v":1},{"$f":"IF(AND(I159=1,J159=1),1,IF(AND(I159=0,J159=1),2,IF(AND(I159=0,J159=0),3,IF(AND(I159=1,J159=0),4,0))))","v":2},{"$f":"IF(K159=1,C152-C153,IF(K159=2,G159-C155-C153,IF(K159=3,0,IF(K159=4,0,0))))","v":35700},{"$f":"IF(K159=1,0,IF(K159=2,MIN(C155+C152-G159,G160-G159),IF(K159=3,MIN(C152-C153,G160-G159-O159),IF(K159=4,0,0))))","v":-37700},{"$f":"IF(C153+C152>G160,IF(K159=1,0,IF(K159=2,C153+C152-G160,IF(K159=3,C153+C152-G160,IF(K159=4,0,0)))),0)","v":0},{"$f":"$C$155+$C$153-G159","v":-35700},{"$f":"$C$153-O159","v":37700}]
[null,null,"Upper Tax Band",{"$f":"IF(H159=1,M159,IF(H160=1,M160,IF(H161=1,M161,0)))","v":-37700},null,37501,{"$f":"'Tax Rates'!B39","v":150000},{"$f":"IF(AND($C$155>=F160,$C$155<=G160),1,0)","v":0},{"$f":"IF(AND($C$155+$C$152>F160,$C$155+$C$152<=G160),1,0)","v":0},{"$f":"IF(AND($C$155+$C$153>F160,$C$155+$C$153<=G160),1,0)","v":0},{"$f":"IF(AND(I160=1,J160=1),1,IF(AND(I160=0,J160=1),2,IF(AND(I160=0,J160=0),3,IF(AND(I160=1,J160=0),4,0))))","v":3},0,{"$f":"IF(K160=1,C152-C153,IF(K160=2,G160-C155-C153,IF(K160=3,0,IF(K160=4,0,0))))","v":0},{"$f":"IF(K160=1,0,IF(K160=2,C155+C152-G160,IF(K160=3,C152-C153,IF(K160=4,0,0))))","v":-2000},{"$f":"$C$155+$C$153-G160","v":-148000},{"$f":"$C$153-O160","v":150000}]
[null,null,"Additional Tax Band",{"$f":"IF(H159=1,N159,IF(H160=1,N160,IF(H161=1,N161,0)))","v":0},null,150001,99999999,{"$f":"IF(AND($C$155>=F161,$C$155<=G161),1,0)","v":0},0,{"$f":"IF(AND($C$155+$C$153>F161,$C$155+$C$153<=G161),1,0)","v":0},3,0,0,{"$f":"C152-C153","v":-2000},{"$f":"$C$155+$C$153-G161","v":-99997999},{"$f":"$C$153-O161","v":99999999}]
[]
[]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate"]
[null,{"$f":"TEXT(F166,\"0.0%\") & \" Rate\"","v":"7.5% Rate"},{"$f":"D166*F166","v":2677.5},{"$f":"D159","v":35700},null,{"$f":"'Tax Rates'!B83","v":7.4999999999999997E-2}]
[null,{"$f":"TEXT(F167,\"0.0%\") & \" Rate\"","v":"32.5% Rate"},{"$f":"D167*F167","v":-12252.5},{"$f":"D160","v":-37700},null,{"$f":"'Tax Rates'!B84","v":0.32500000000000001}]
[null,{"$f":"TEXT(F168,\"0.0%\") & \" Rate\"","v":"38.1% Rate"},{"$f":"D168*F168","v":0},0,null,{"$f":"'Tax Rates'!B85","v":0.38100000000000001}]
[]
[null,"Investment Income Tax",{"$f":"IF(OR(C152=0,C152<=C153),0,SUM(C166:C168))","v":0}]

{"sheet":"Tax Applicant 4","index":16,"n_rows":170,"n_cols":18,"rows_kept":170,"cut":false,"hidden":true,"merged":[]}
[null,"UK Taxation"]
[null,null," "]
[null,"Additional Deduction Fields"]
[null,"Age",{"$f":"'Applicant 4'!H5","v":"Input DOB"}," years"]
[null,"Student Loan Payment",{"$f":"IF('Applicant 4'!D91>0,\"Plan2\",\"No\")","v":"No"},"(Plan2/No)"]
[null,"Blind","No","(Yes/No)"]
[null,"Pension Contributions",{"$f":"'Applicant 4'!H86*12","v":0}," per year"]
[null,"Childcare Vouchers",{"$f":"'Applicant 4'!D87*12","v":0}," per year"]
[]
[null,"Income Type"]
[null,"Basic Salary",{"$f":"IF('Applicant 4'!D11<>\"Self Employed\",'Applicant 4'!H34,0)","v":0}," per year"]
[null,"All employments",{"$f":"IF('Applicant 4'!D11=\"Self Employed\",'Applicant 4'!H20,0)","v":0}," per year"]
[null,"Pension Income (incl. Private, Company and State)",{"$f":"'Applicant 4'!D44+'Applicant 4'!D45+'Applicant 4'!D43","v":0}," per year"]
[null,"Profit from Self Employment",{"$f":"'Applicant 4'!D42","v":0}," per year"]
[null,"Other",null," per year"]
[null,"Deposit Interest (Savings) Income",{"$f":"'Applicant 4'!D49","v":0}," per year"]
[null,"Annuities Income",{"$f":"'Applicant 4'!D51","v":0}," per year"]
[null,"Trust Income",{"$f":"'Applicant 4'!D50","v":0}," per year"]
[null,"Dividend Income",{"$f":"'Applicant 4'!D48","v":0}," per year"]
[null,"Investment Income",{"$f":"'Applicant 4'!D47","v":0}," per year"]
[]
[null,"Total Gross Income",{"$f":"SUM(C11:C20)","v":0}," per year"]
[]
[null,"National Insurance Income",{"$f":"C11+C12","v":0}," per year"]
[null,"Self Employed Profit National Insurance Income*",{"$f":"C14","v":0}," per year"]
[]
[null,"Non-Savings Income minus pension contributions and child care vouchers",{"$f":"SUM(C11:C15)-SUM(C7:C8)","v":0}," per year"]
[null,"Interest Income",{"$f":"SUM(C16:C18)","v":0}," per year"]
[null,"Investment Income (Top Slice)",{"$f":"SUM(C19:C20)","v":0}," per year"]
[]
[null,"Total Taxable Income",{"$f":"SUM(C27:C29)","v":0}," per year"]
[]
[null,"Total Employment PAYE tax**",{"$f":"C77","v":0}," per year"]
[null,"Total Employment National Insurance",{"$f":"C90","v":0}," per year"]
[null,"Student Loan Payment",{"$f":"IF(UPPER(LEFT(C5,1))<>\"N\",'Applicant 4'!D91*12,0)","v":0}," per year"]
[null,"Class2 NIC***",{"$f":"C96","v":0}," per year"]
[null,"Class4 NIC***",{"$f":"C110","v":0}," per year"]
[null,"Interest Income Tax ****",{"$f":"C147","v":0}," per year"]
[null,"Investment Income Tax *****",{"$f":"C170","v":0}," per year"]
[]
[null,"Total deductions",{"$f":"SUM(C33:C39)","v":0}," per year"]
[]
[null,"Annual Net Pay :",{"$f":"C31-C41","v":0}," per year"]
[null,"Monthly Net pay :",{"$f":"C43/12","v":0}," per month"]
[null,"Weekly Net pay :",{"$f":"C43/52","v":0}," per week"]
[]
[null,"* Profit from Self Employment"]
[null,"** PAYE Tax on Employment and Self Employment Incomes"]
[null,"*** Self Employed National Insurance "]
[null,"**** Tax on Deposit Interest (Savings) and Annuities Income"]
[null,"***** Tax on Dividend and Investment Income"]
[]
[null,"Personal Allowance"]
[]
[null,"Basic",{"$f":"G55","v":12570},null,null,"Basic",{"$f":"'Tax Rates'!B5","v":12570}]
[null,"Age related contribution",{"$f":"(IF(C4<65,0,IF(C4<75,G56-G55,G57-G55)))","v":0},null,null,"Age 65-74",{"$f":"G55","v":12570}]
[null,"Age related taper deduction",{"$f":"IF((C11-G60)<0,0,IF((C11-G60)/2>C56,C56,(C11-G60)/2))","v":0},null,null,"Age 75 and over",{"$f":"G55","v":12570}]
[null,"Allowance after age adjust",{"$f":"C55+C56-C57","v":12570},null,null,"Blind persons allowance",{"$f":"'Tax Rates'!B90","v":2520}]
[]
[null,"Taper deduction",{"$f":"IF(((C22-C7)-G61)<0,0,IF(((C22-C7)-G61)/2>C58,C58,((C22-C7)-G61)/2))","v":0},null,null,"Age related threshold",27700]
[null,null,null,null,null,"Taper threshold",100000]
[null,"Personal Allowance",{"$f":"C58-C60","v":12570}]
[]
[null,"Non-Savings Income Tax (Basic Salary/Self Employed/Penison(s)/IncomeEarnedAsSoleProprietorOrPartner/Trust/Other) "]
[]
[null,"Taxable Employment Income",{"$f":"C27","v":0},null,null,"Personal Allowance",{"$f":"C62","v":12570}]
[null,null,null,null,null,"Blind persons allowance",{"$f":"IF(UPPER(LEFT(C6,1))=\"Y\",G58,0)","v":0}]
[null,"Income less personal allowance",{"$f":"IF(C66>G68,C66-G68,0)","v":0},null,null,"Total Allowances",{"$f":"G67+G66","v":12570}]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate","Start of Band","End of Band","Difference"]
[null,{"$f":"TEXT(F71,\"0.0%\") & \" Rate\"","v":"20.0% Rate"},{"$f":"D71*F71","v":0},{"$f":"MIN(C68,I71)","v":0},null,{"$f":"'Tax Rates'!B42","v":0.2},0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"H71-G71","v":37700}]
[null,{"$f":"TEXT(F72,\"0.0%\") & \" Rate\"","v":"40.0% Rate"},{"$f":"D72*F72","v":0},{"$f":"IF($C$68>G72,MIN($C$68-G72,I72),0)","v":0},null,{"$f":"'Tax Rates'!B43","v":0.4},{"$f":"H71+1","v":37701},{"$f":"'Tax Rates'!B39","v":150000},{"$f":"H72-G72","v":112299}]
[null,{"$f":"TEXT(F73,\"0.0%\") & \" Rate\"","v":"45.0% Rate"},{"$f":"D73*F73","v":0},{"$f":"IF($C$68>G73,$C$68-G73,0)","v":0},null,{"$f":"'Tax Rates'!B44","v":0.45},{"$f":"H72+1","v":150001},99999999,{"$f":"H73-G73","v":99849998}]
[]
[null,"Less tax on trusts"]
[null,null,null,null,null,null,null,null,null,null,{"$f":"12500/52","v":240.38461538461539}]
[null,"Income Tax",{"$f":"SUM(C71:C73)","v":0}]
[]
[null,"National Insurance (From Basic Salary income)"]
[]
[null,"Weekly Income (Gross)",{"$f":"C24/52","v":0},null,null,"State Pension Age",65," years"]
[]
[null,null,"Tax","Carry Over",null,"Rate","Start of Band","End of Band","Difference"]
[null,{"$f":"TEXT(F84,\"0.0%\") & \" Rate\"","v":"0.0% Rate"},{"$f":"IF(C81>=G84,IF(C81>=I84,I84*F84,C81*F84),0)","v":0},{"$f":"IF((C81-I84)>0,C81-I84,0)","v":0},null,{"$f":"'Tax Rates'!B49","v":0},0,{"$f":"'Tax Rates'!B54","v":184},{"$f":"H84-G84","v":184},null,{"$f":"183*52","v":9516}]
[null,{"$f":"TEXT(F85,\"0.0%\") & \" Rate\"","v":"12.0% Rate"},{"$f":"IF(D84>0,IF(D84>=I85,I85*F85,D84*F85),0)","v":0},{"$f":"IF((D84-I85)>0,D84-I85,0)","v":0},null,{"$f":"'Tax Rates'!B50","v":0.12},{"$f":"H84+0.01","v":184.01},{"$f":"'Tax Rates'!B55","v":967},{"$f":"H85-G85+0.01","v":783}]
[null,{"$f":"TEXT(F86,\"0.0%\") & \" Rate\"","v":"2.0% Rate"},{"$f":"IF(D85>0,IF(D85>=I86,I86*F86,D85*F86),0)","v":0},{"$f":"IF((D85-I86)>0,D85-I86,0)","v":0},null,{"$f":"'Tax Rates'!B51","v":0.02},{"$f":"H85+0.01","v":967.01},99999999.989999995,{"$f":"H86-G86+0.01","v":99999032.989999995}]
[]
[null,"Total Weekly NI",{"$f":"SUM(C84:C86)","v":0}]
[null,"Age related deduction",{"$f":"IF(C4>=G81,C88*52,0)","v":0}]
[null,"Total Annual NI",{"$f":"(C88*52)-C89","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,"NIC annual maxima where employed and self employed"]
[null,"Self Employed National Insurance "]
[null,null,null,null,null,null,null,null,null,null,"STEPS"]
[null,"Class 2 Threshold",{"$f":"'Tax Rates'!B63","v":6515},null,null,null,null,null,null,null,1,{"$f":"H105","v":50270},{"$f":"H104","v":9569},{"$f":"IF(C14>0,L94-M94,0)","v":0}]
[null,"Class 2 Weekly Rate",{"$f":"'Tax Rates'!B64","v":3.05},null,null,null,null,null,null,null,2,{"$f":"N94","v":0},{"$f":"F105","v":0.09},{"$f":"M95*L95","v":0}]
[null,"Total Annual Class 2 NIC",{"$f":"IF(C25=0,0,IF(SUM(C24:C25)>C94,C95*52))","v":0},null,null,null,null,null,null,null,3,{"$f":"N95","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"53*$C$95","v":161.64999999999998},null,{"$f":"L97+L96","v":161.64999999999998}]
[null,"Class 4 Taxable Income",{"$f":"C25","v":0},null,null,null,null,null,null,null,null,null,null,null,null,"Comparative total"]
[null,null,null,null,null,null,null,null,null,null,4,null,{"$f":"N97","v":161.64999999999998},null,null,"Class 1 NIC @12%",null,{"$f":"-M100","v":0}]
[null,"Tax at 2% if employed and self employed","Tax",null,null,"Rate",null,null,null,null,null,null,{"$f":"-C85*52","v":0},null,null,"Class 2 NIC",null,{"$f":"-M101","v":0}]
[null,{"$f":"TEXT(F101,\"0.0%\")","v":"2.0%"},{"$f":"D101*F101","v":0},{"$f":"IF(C25=0,0,IF(SUM(C11:C12)>12500,C98-9500,0))","v":0},null,{"$f":"'Tax Rates'!B67","v":0.02},null,null,null,null,null,null,{"$f":"-C96","v":0},{"$f":"SUM(M99:M101)","v":161.64999999999998},null,"Class 4 NIC @9%",null,{"$f":"C25*0.09","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(R99:R101)","v":0}]
[null,null,"Tax","Carry Over",null,"Rate","Start of Band","End of Band","Difference",null,null,"Case 1",null,{"$f":"IF(N101>R102,N101,0)","v":161.64999999999998}]
[null,{"$f":"TEXT(F104,\"0.0%\") & \" Rate\"","v":"0.0% Rate"},{"$f":"IF(C101>=G104,IF(C101>=I104,I104*F104,C101*F104),0)","v":0},{"$f":"IF((C98-I104)>0,C98-I104,0)","v":0},null,{"$f":"'Tax Rates'!B70","v":0},0,{"$f":"'Tax Rates'!B75","v":9569},{"$f":"H104-G104","v":9569},null,null,"Case 2",null,{"$f":"IF(AND(N101>0,N101<R102),N101,0)","v":0},"need to add additional 2%"]
[null,{"$f":"TEXT(F105,\"0.0%\") & \" Rate\"","v":"9.0% Rate"},{"$f":"IF(D104>0,IF(D104>=I105,I105*F105,D104*F105),0)","v":0},{"$f":"IF((D104-I105)>0,D104-I105,0)","v":0},null,{"$f":"'Tax Rates'!B71","v":0.09},{"$f":"IF(H104>=1,H104+1,H104)","v":9570},{"$f":"'Tax Rates'!B76","v":50270},{"$f":"H105-G105","v":40700},null,null,"Case 3",null,{"$f":"IF(N101<0,N101,0)","v":0},"NIC4 @9% is NIL "]
[null,{"$f":"TEXT(F106,\"0.0%\") & \" Rate\"","v":"2.0% Rate"},{"$f":"IF(D105>0,IF(D105>=I106,I106*F106,D105*F106),0)","v":0},{"$f":"IF((D105-I106)>0,D105-I106,0)","v":0},null,{"$f":"'Tax Rates'!B72","v":0.02},{"$f":"H105+1","v":50271},99999999,{"$f":"H106-G106+1","v":99949729}]
[null,null,null,null,null,null,null,null,null,null,null,"Step5-9 only for Case 2 & 3"]
[null,"Total Class 4 NIC (where no Class 1 NIC)",{"$f":"IF(D101>0,C101,SUM(C104:C106))","v":0},null,null,null,null,null,null,null,5,{"$f":"IF(OR(N105>0,N104>0),N101,0)","v":0},{"$f":"100/9","v":11.111111111111111},{"$f":"L108*M108","v":0}]
[null,null,null,null,null,null,null,null,null,null,6,{"$f":"MIN(C25,H105)","v":0},{"$f":"-I104","v":-9569},{"$f":"L109+M109","v":-9569}]
[null,"Total Annual Class 4 NIC payable",{"$f":"IF(C90<0.01,C108,IF(N103>0,R101,IF(N104>0,N119,IF(N105<0,N119,0))))","v":0},null,null,null,null,null,null,null,7,null,null,{"$f":"N109-N108","v":-9569}]
[null,null,null,null,null,null,null,null,null,null,null,"If STEP 7 negative then Nil"]
[null,"Student Loan Repayment",null,null,null,null,null,null,null,null,8,{"$f":"IF(N110<0,0,N110)","v":0},{"$f":"$F$106","v":0.02},{"$f":"L112*M112","v":0}]
[null,null,null,null,null,null,null,null,null,null,9,{"$f":"C25","v":0},{"$f":"-H105","v":-50270},{"$f":"IF((L113+M113)<0,0,(L113+M113)*$F$106)","v":0}]
[null,"Threshold",{"$f":"IF(RIGHT(C5,1)=\"1\",G115/12,IF(RIGHT(C5,1)=\"2\",H115/12,0))","v":0},null,null,null,"Plan 1","Plan 2"]
[null,"Income above threshold",{"$f":"IF(SUM(C11:C12,C14)/12-C114<0,0,SUM(C11:C12,C14)/12-C114)","v":0},null,null,"Repayment Threshold",{"$f":"'Tax Rates'!B27*12","v":19884},{"$f":"'Tax Rates'!B28*12","v":27288},null,null,null,"Total Class 4 NIC"]
[null,"Repayment (if applicable)",{"$f":"FLOOR(C115*G116,1)*12","v":0},null,null,"Repayment Rate",{"$f":"'Tax Rates'!B31","v":0.09},{"$f":"'Tax Rates'!B32","v":0.09},null,null,null,"Step 4",null,{"$f":"IF(N101<0,0,N101)","v":161.64999999999998}]
[null,null,null,null,null,null,null,null,null,null,null,"Step 8",null,{"$f":"N112","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,"Step 9",null,{"$f":"N113","v":0}]
[null,"Interest Income Tax (Deposit Interest (Savings), Annuities, Trust)",null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(N116:N118)","v":161.64999999999998}]
[]
[null,"Interest Income",{"$f":"C28","v":0}]
[]
[null,"Non-Savings Income minus allowance",{"$f":"C68","v":0}]
[null,"Gross Income (for allowance calculation)",{"$f":"C31","v":0}]
[]
[null,null,"Calculated Allowance",null,null,"Start of Band","End of Band","Difference","Within Band"]
[null,null,{"$f":"IF(I127=1,'Tax Rates'!B9,0)","v":5000},null,null,0,{"$f":"'Tax Rates'!B15","v":12570},{"$f":"G127-F127","v":12570},{"$f":"IF(AND($C$124>=$F127,$C$124<=$G127),1,0)","v":1}]
[null,null,{"$f":"IF(I128=1,'Tax Rates'!B9-(C123-G127),0)","v":0},null,null,{"$f":"IF(G127>=1,G127+1,G127)","v":12571},{"$f":"'Tax Rates'!B16","v":17570},{"$f":"G128-F128","v":4999},{"$f":"IF(AND($C$124>=$F128,$C$124<=$G128),1,0)","v":0}]
[null,null,{"$f":"IF(I129=1,'Tax Rates'!B10,0)","v":0},null,null,{"$f":"G128+1","v":17571},{"$f":"'Tax Rates'!B17","v":50270},{"$f":"G129-F129+1","v":32700},0]
[null,null,{"$f":"IF(I130=1,'Tax Rates'!B11,0)","v":0},null,null,{"$f":"G129+1","v":50271},{"$f":"'Tax Rates'!B18","v":150000},{"$f":"G130-F130+1","v":99730},0]
[null,null,{"$f":"IF(I131=1,'Tax Rates'!B12,0)","v":0},null,null,{"$f":"G130+1","v":150001},99999999,{"$f":"G131-F131+1","v":99849999},0]
[]
[null,"Interest Income Tax Allowance",{"$f":"SUM(C127:C131)","v":5000}]
[]
[null,null,null,"Taxable Interest Income by tax band",null,"Start of Band","End of Band","Scenario check 1","Pay+Interest check","Pay+Allowance check","Scenaio Check 2","Basic ","Upper ","Additional","Allowance 2","Allowance 3"]
[null,null,"Basic Tax Band",{"$f":"IF(H136=1,L136,IF(H137=1,L137,IF(H138=1,L138,0)))","v":32700},null,0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"IF(AND($C$123>=F136,$C$123<=G136),1,0)","v":1},{"$f":"IF(AND($C$123+$C$121>F136,$C$123+$C$121<=G136),1,0)","v":0},{"$f":"IF(AND($C$123+$C$133>F136,$C$123+$C$133<=G136),1,0)","v":1},{"$f":"IF(AND(I136=1,J136=1),1,IF(AND(I136=0,J136=1),2,IF(AND(I136=0,J136=0),3,IF(AND(I136=1,J136=0),4,0))))","v":2},{"$f":"IF(K136=1,C121-C133,IF(K136=2,G136-C123-C133,IF(K136=3,0,IF(K136=4,0,0))))","v":32700},{"$f":"IF(K136=1,0,IF(K136=2,MIN(C123+C121-G136,G137-G136),IF(K136=3,MIN(C121-C133,G137-G136-O136),IF(K136=4,0,0))))","v":-37700},{"$f":"IF(C123+C121>G137,IF(K136=1,0,IF(K136=2,C123+C121-G137,IF(K136=3,C123+C121-G137,IF(K136=4,0,0)))),0)","v":0},{"$f":"$C$123+$C$133-G136","v":-32700},{"$f":"$C$133-O136","v":37700}]
[null,null,"Upper Tax Band",{"$f":"IF(H136=1,M136,IF(H137=1,M137,IF(H138=1,M138,0)))","v":-37700},null,37501,{"$f":"'Tax Rates'!B39","v":150000},{"$f":"IF(AND($C$123>=F137,$C$123<=G137),1,0)","v":0},{"$f":"IF(AND($C$123+$C$121>F137,$C$123+$C$121<=G137),1,0)","v":0},{"$f":"IF(AND($C$123+$C$133>F137,$C$123+$C$133<=G137),1,0)","v":0},{"$f":"IF(AND(I137=1,J137=1),1,IF(AND(I137=0,J137=1),2,IF(AND(I137=0,J137=0),3,IF(AND(I137=1,J137=0),4,0))))","v":3},0,{"$f":"IF(K137=1,C121-C133,IF(K137=2,G137-C123-C133,IF(K137=3,0,IF(K137=4,0,0))))","v":0},{"$f":"IF(K137=1,0,IF(K137=2,C123+C121-G137,IF(K137=3,C121-C133,IF(K137=4,0,0))))","v":-5000},{"$f":"$C$123+$C$133-G137","v":-145000},{"$f":"$C$133-O137","v":150000}]
[null,null,"Additional Tax Band",{"$f":"IF(H136=1,N136,IF(H137=1,N137,IF(H138=1,N138,0)))","v":0},null,150001,99999999,{"$f":"IF(AND($C$123>=F138,$C$123<=G138),1,0)","v":0},0,{"$f":"IF(AND($C$123+$C$133>F138,$C$123+$C$133<=G138),1,0)","v":0},{"$f":"IF(AND(I138=1,J138=1),1,IF(AND(I138=0,J138=1),2,IF(AND(I138=0,J138=0),3,IF(AND(I138=1,J138=0),4,0))))","v":3},0,0,{"$f":"C121-C133","v":-5000},{"$f":"$C$123+$C$133-G138","v":-99994999},99999999]
[]
[]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate"]
[null,{"$f":"TEXT(F143,\"0.0%\") & \" Rate\"","v":"20.0% Rate"},{"$f":"D143*F143","v":6540},{"$f":"D136","v":32700},null,{"$f":"'Tax Rates'!B42","v":0.2}]
[null,{"$f":"TEXT(F144,\"0.0%\") & \" Rate\"","v":"40.0% Rate"},{"$f":"D144*F144","v":-15080},{"$f":"D137","v":-37700},null,{"$f":"'Tax Rates'!B43","v":0.4}]
[null,{"$f":"TEXT(F145,\"0.0%\") & \" Rate\"","v":"45.0% Rate"},{"$f":"D145*F145","v":0},{"$f":"D138","v":0},null,{"$f":"'Tax Rates'!B44","v":0.45}]
[]
[null,"Interest Income Tax",{"$f":"IF(OR(C121<=C133,C121=0),0,SUM(C143:C145))","v":0}]
[]
[]
[null,"Investment Income Tax (Dividend/Investment)"]
[]
[null,"Investment Income",{"$f":"C29","v":0}]
[null,"Dividend/Investment Income Allowance ",{"$f":"'Tax Rates'!B22","v":2000}]
[]
[null,"Non-Savings Income minus personal allowance and Interest Income",{"$f":"C68+C28","v":0}]
[]
[]
[null,null,null,"Taxable Interest Income by tax band",null,"Start of Band","End of Band","Scenario check 1","Pay+Investment check","Pay+Allowance check","Scenaio Check 2","Basic ","Upper ","Additional","Allowance 2","Allowance 3"]
[null,null,"Basic Tax Band",{"$f":"IF(H159=1,L159,IF(H160=1,L160,IF(H161=1,L161,0)))","v":35700},null,0,{"$f":"'Tax Rates'!B38","v":37700},{"$f":"IF(AND($C$155>=F159,$C$155<=G159),1,0)","v":1},{"$f":"IF(AND($C$155+$C$152>F159,$C$155+$C$152<=G159),1,0)","v":0},{"$f":"IF(AND($C$155+$C$153>F159,$C$155+$C$153<=G159),1,0)","v":1},{"$f":"IF(AND(I159=1,J159=1),1,IF(AND(I159=0,J159=1),2,IF(AND(I159=0,J159=0),3,IF(AND(I159=1,J159=0),4,0))))","v":2},{"$f":"IF(K159=1,C152-C153,IF(K159=2,G159-C155-C153,IF(K159=3,0,IF(K159=4,0,0))))","v":35700},{"$f":"IF(K159=1,0,IF(K159=2,MIN(C155+C152-G159,G160-G159),IF(K159=3,MIN(C152-C153,G160-G159-O159),IF(K159=4,0,0))))","v":-37700},{"$f":"IF(C153+C152>G160,IF(K159=1,0,IF(K159=2,C153+C152-G160,IF(K159=3,C153+C152-G160,IF(K159=4,0,0)))),0)","v":0},{"$f":"$C$155+$C$153-G159","v":-35700},{"$f":"$C$153-O159","v":37700}]
[null,null,"Upper Tax Band",{"$f":"IF(H159=1,M159,IF(H160=1,M160,IF(H161=1,M161,0)))","v":-37700},null,37501,{"$f":"'Tax Rates'!B39","v":150000},{"$f":"IF(AND($C$155>=F160,$C$155<=G160),1,0)","v":0},{"$f":"IF(AND($C$155+$C$152>F160,$C$155+$C$152<=G160),1,0)","v":0},{"$f":"IF(AND($C$155+$C$153>F160,$C$155+$C$153<=G160),1,0)","v":0},{"$f":"IF(AND(I160=1,J160=1),1,IF(AND(I160=0,J160=1),2,IF(AND(I160=0,J160=0),3,IF(AND(I160=1,J160=0),4,0))))","v":3},0,{"$f":"IF(K160=1,C152-C153,IF(K160=2,G160-C155-C153,IF(K160=3,0,IF(K160=4,0,0))))","v":0},{"$f":"IF(K160=1,0,IF(K160=2,C155+C152-G160,IF(K160=3,C152-C153,IF(K160=4,0,0))))","v":-2000},{"$f":"$C$155+$C$153-G160","v":-148000},{"$f":"$C$153-O160","v":150000}]
[null,null,"Additional Tax Band",{"$f":"IF(H159=1,N159,IF(H160=1,N160,IF(H161=1,N161,0)))","v":0},null,150001,99999999,{"$f":"IF(AND($C$155>=F161,$C$155<=G161),1,0)","v":0},0,{"$f":"IF(AND($C$155+$C$153>F161,$C$155+$C$153<=G161),1,0)","v":0},3,0,0,{"$f":"C152-C153","v":-2000},{"$f":"$C$155+$C$153-G161","v":-99997999},{"$f":"$C$153-O161","v":99999999}]
[]
[]
[]
[null,null,"Tax","Value Being Taxed",null,"Rate"]
[null,{"$f":"TEXT(F166,\"0.0%\") & \" Rate\"","v":"7.5% Rate"},{"$f":"D166*F166","v":2677.5},{"$f":"D159","v":35700},null,{"$f":"'Tax Rates'!B83","v":7.4999999999999997E-2}]
[null,{"$f":"TEXT(F167,\"0.0%\") & \" Rate\"","v":"32.5% Rate"},{"$f":"D167*F167","v":-12252.5},{"$f":"D160","v":-37700},null,{"$f":"'Tax Rates'!B84","v":0.32500000000000001}]
[null,{"$f":"TEXT(F168,\"0.0%\") & \" Rate\"","v":"38.1% Rate"},{"$f":"D168*F168","v":0},0,null,{"$f":"'Tax Rates'!B85","v":0.38100000000000001}]
[]
[null,"Investment Income Tax",{"$f":"IF(OR(C152=0,C152<=C153),0,SUM(C166:C168))","v":0}]

{"sheet":"Tax Rates","index":17,"n_rows":90,"n_cols":5,"rows_kept":90,"cut":false,"hidden":true,"merged":[]}
[]
[]
["Personal Allowance"]
[null,"2021/22 Allowance","2020/21 Allowance"]
["Allowance",12570,12500,"per year"]
[]
["Interest Income "]
["Allowances ","2021/22 Allowance","2020/21 Allowance"]
["Starting Rate",5000,5000,"per year"]
["Basic Rate",1000,1000,"per year"]
["Upper Rate",500,500,"per year"]
["Additional Rate",0,0,"per year"]
[]
["Thresholds","2021/22 Thresholds","2020/21 Thresholds"]
["Starting ",12570,12500,"per year"]
["Basic",17570,17500,"per year"]
["Upper",50270,50000,"per year"]
["Additional",150000,150000,"per year"]
[]
["Investment Income Allowance"]
[null,"2021/22 Allowance","2020/21 Allowance"]
["Allowance",2000,2000,"per year"]
[]
[]
["Student Loan "]
["Thresholds","2021/22 Allowance","2020/21 Allowance"]
["Plan 1",1657,1615,"per month"]
["Plan 2",2274,2214,"per month"]
[]
["Rates","2021/22 Rates","2020/21 Rates"]
["Plan 1",0.09,0.09]
["Plan 2",0.09,0.09]
[]
[]
["PAYE tax "]
["Thresholds","2021/22 Thresholds","2020/21 Thresholds"]
["Basic",0,0,"per year"]
["Higher",37700,37500,"per year"]
["Additional",150000,150000,"per year"]
[]
["Rates","2021/22 Rates","2020/21 Rates"]
["Basic",0.2,0.2]
["Higher",0.4,0.4]
["Additional",0.45,0.45]
[]
[]
["Employment National Insurance"]
[null,"2021/22 Rates","2020/21 Rates"]
["Zero",0,0]
["Lower ",0.12,0.12]
["Upper",0.02,0.02]
[]
[null,"2021/22 Thresholds","2020/21 Thresholds"]
["Zero",184,183,"per week"]
["Lower ",967,962,"per week"]
["Upper"]
[]
[]
[]
["Self Employment National Insurance"]
[]
["Class 2","2021/22 Thresholds","2020/21 Thresholds"]
["Threshold ",6515,6475,"per year"]
["Weekly Rate",3.05,3.05,"per week"]
[]
["Class 4 Both Employed and Self Employed","2021/22 Rates","2020/21 Rates"]
["Flat Rate",0.02,0.02]
[]
["Class 4 ","2021/22 Rates","2020/21 Rates"]
["Zero",0,0]
["Lower ",0.09,0.09]
["Upper",0.02,0.02]
[]
["Class 4 ","2021/22 Thresholds","2020/21 Thresholds"]
["Zero",9569,9500,null,"per year"]
["Lower ",50270,50000,null,"per year"]
["Upper"]
[]
[]
[]
["Investment Income Tax Rates"]
[null,"2021/22 Rates","2020/21 Rates"]
["Basic",7.4999999999999997E-2,7.4999999999999997E-2]
["Higher",0.32500000000000001,0.32500000000000001]
["Additional",0.38100000000000001,0.38100000000000001]
[]
[]
["Blind Persons Allowance"]
[null,"2021/22 Allowance","2020/21 Allowance"]
["Allowance",2520,2500,"per year"]

{"sheet":"1 Net Monthly Income","index":18,"n_rows":39,"n_cols":4,"rows_kept":39,"cut":false,"hidden":true,"merged":[[37,0,37,2],[3,0,3,3],[5,2,9,2],[6,3,8,3],[12,0,12,2],[14,2,16,2],[19,0,19,2]]}
["1 Net Monthly Income"]
[]
[]
["Employed Income"]
["Gross Income Type","Assessable Percentage","Recognised Income","Notes"]
["Basic Salary",1,"100% of Basic Salary \n+ \n50% of (Overtime + Commission + Bonus) \n+ \n100% of Permanent Contracted Allowances"]
["Overtime ",0.5,null,"discount may be subject to changes"]
["Commission",0.5]
["Bonus",0.5]
["Permant Contracted Allowances  ",1]
[]
[]
["Self Employed Income"]
["Earned Income Year (EIY)","Declared Gross Income","Recognised Income"]
["EIY1 (Most Recent Year)","£x","Keep current logic for assessing the income"]
["EIY2 ","£y"]
["EIY3 ","£z"]
[]
[]
["Other Income Sources"]
["Income Source","Discounts","Assessable Percentage","Any change needed"]
["Rental Income excluding BTL and Investment properties;",0.25,0.75,"Use only 80% of this "]
["Income from second job or part time employment (Should only appear in DIP06B Retired Drop down)",0,{"$f":"100%-B23","v":1},"no"]
["Income earned as Partner/ Sole Proprietor;",0,1,"no"]
["Income from Company pension;",0,1,"no"]
["Income from State pension;",0,1,"no"]
["Income from Private pension;",0,1,"no"]
["Other state benefits;",0.75,0.25]
["Investment income; ",0,1,"no"]
["Dividends;",0,1,"no"]
["Deposit Interest;",0,1,"no"]
["Trust Income;",0,1,"no"]
["Annuities;",0,1,"no"]
["Child Benefit for children aged 12 or below;",0,1,"IF any of the first two applicants Gross Income, before discounts, is > £50,000 then discount by 100% (i.e. use 0% of Child Benefit for children aged 12 or below) "]
["Other Income",0,1,"no"]
[]
[]
["Final Calculation "]
["Net Monthly Income Calculation ","Current Net Monthly Income Calculation applied to: \nEmployed Income \n+ \nSelf Employed Income \n+ \nOther Income Sources","The new discounts mentioned above have to applied to the gross income. After which the current calculation of Net Monthly Income is sufficient."]

{"sheet":"2 Existing Mortgage Commitments","index":19,"n_rows":20,"n_cols":7,"rows_kept":20,"cut":false,"hidden":true,"merged":[[8,0,9,0],[18,0,19,0]]}
["2 Existing Mortgage Commitments"]
[]
[]
["BTL Properties (BTLPortfolioMortgage)"]
["Should now be taken from the commitments screen not from the total BTL tab"]
[]
[]
[null,"To be repaid","Include within monthly commitments","Mortgage Commitment to be used ","Percent 1","Percent 2","Other Commitments"]
["Customer has BTL properties","Yes","No","Not needed",null,null,"Not Needed"]
[null,"No","Yes","MAXIMUM(Total Exisiting Mortgage Payment + (2% Of Total Outstanding Balance)/12, (5.50% Of Total Outstanding Balance)/12) ",0.02,5.5E-2,"Total Net Rent Received After Agent Fees (Total Monthly Additional Costs detailed on application form) of properties that are not to be repaid"]
[]
[]
[]
["Other Mortgage Commitments (Current mortgage / Secondary mortgage)"]
["Should still be taken from commitment screen"]
[]
[]
[null,"To be repaid","Include within monthly commitments","Mortgage Commitment to be used ","Percent 1",null,"Other Commitments"]
["Customer has exisitng properties","Yes","No","Not needed",null,null,"Not Needed"]
[null,"No","Yes","Total Exisiting Mortgage Payment + (3% Of Total Outstanding Balance)/12",0.03,null,"Total Additional costs of properties that are not to be repaid"]

{"sheet":"3 Revolving Credit Commitments","index":20,"n_rows":35,"n_cols":4,"rows_kept":35,"cut":false,"hidden":true,"merged":[]}
["3 Revolving Credit Commitments"]
[]
[]
["Inputs","Variable"]
["Declared Total outstanding balance on Overdrafts/Store Cards/Credit Cards even if declared to be repaid","Declared Revolving Balances"]
["Declared Total outstanding balance on Overdrafts/Store Cards/Credit Cards that customer declares are to be repaid","Repaid Declared Revolving Balances"]
["Experian variable - SP-A-04 (Balance of active revolving CAIS) from both soft and hard search","SP-A-04"]
[]
["Rule","Logic"]
["Threshold check","IF ABS((Declared Revolving Balances/SP-A-04) -1) <= 0.05 THEN PASS\nELSE FAIL"]
[]
["Outcome of Rule","Revolving Commitment to be used","Policy Rule","Percent"]
["Threshold PASS","3% of (MAX(Declared Revolving Balances, SP-A-04) - Repaid Declared Revolving Balances)"]
["Threshold FAIL","3% of (Declared Revolving Balances - Repaid Declared Revolving Balances)","Refer - Check declared Revolving balances against Experian report ",0.03]
[]
[]
[]
["Exisitng Commitment Type","Revolving Commitment"]
["BTLPortfolioMortgage","No"]
["Card","Yes"]
["ChildMinding","No"]
["CurrentMortgage","No"]
["Expense","No"]
["HirePurchase","No"]
["Loan","No"]
["MailOrder","Yes"]
["Maintenance","No"]
["Nursery","No"]
["OtherCommitment","No"]
["OtherProperty","No"]
["Overdraft","Yes"]
["SchoolFees","No"]
["SecondaryMortgage","No"]
["SecondCharge","No"]
["Tenancy","No"]