{"text_format":"1","workbook":"xlsx","n_sheets":12,"sheets":["Summary Details","Detailed Description","Version History","Enquiry","Affordability Calculator","Mortgage Repayments (UW)","Mortgage Repayments (E)","Data","Council tax","Postcodes","E Calc","Tax Calculator"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":2,"defined_names":47,"comments":0,"pivot_tables":1},"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":"Summary Details","index":0,"n_rows":20,"n_cols":3,"rows_kept":20,"cut":false,"hidden":true,"merged":[[3,0,3,2]]}
["HRBS Affordability Calculator - Owner Occupier"]
["Summary Details"]
[]
["This model has been identified as a material model, as defined in the Model Risk Management Policy, and therefore follows the requirements of that Policy, as follows:\n\n- Summary Details, Detailed Description and Version Control tabs are included in the spreadsheet and kept up-to-date;\n- Write access to the spreadsheet is password protected;\n- A minimum of 2 staff members are trained on how to use the model and are able to make amendments;\n- All output from the model is subject to review by an independent person (cannot be the preparer); and\n- Changes to the model are subject to review by an independent person (cannot be the person making changes).\n"]
[]
["Model name:",null,"Affordability Calculator"]
[]
["Purpose of the model:",null,"This is the Society's affordability calculator used to understand how much customers are able to borrow based on set criteria and affordability.  "]
[]
["Model Owner:",null,"Director of Lending"]
[]
["Locations:",null,"F:\\Shared reports\\Calculators\\Owner Occupied"]
[]
["Staff trained on use / changes (main two):",null,"Finance Analyst - Product Pricing, Finance Manager - MI"]
[]
["Staff notified of changes:",null,"Mortgage Sales & Mortgage Underwriting Department (request changes) "]
[]
["Committee approving material changes:",null,"Credit Committee / Board"]
[]
["Changes to other policies / procedures required:",null,"Lending Policy "]

{"sheet":"Detailed Description","index":1,"n_rows":37,"n_cols":1,"rows_kept":37,"cut":false,"hidden":true,"merged":[]}
[{"$f":"'Summary Details'!A1","v":"HRBS Affordability Calculator - Owner Occupier"}]
["Detailed Description"]
[]
["Detailed description:"]
["This is the Society's affordability calculator used when assessing affordability for owner occupier loan applications, affordability is assessed using a net free pay calculation (5% minimum required to pass) using actual income and expenditure input into the model. Expenditure is compared against ONS per the applicants specific region and household occupancy and any expenditure which is less than or equal to 105% of ONS is flagged. The loan monthly payment is calculated per input criteria and stressed using the stress rate applicable to the product length. The maximum loan available to the customer is calculated depending on the individuals income, credit history."]
[]
["How to use the model (if separate procedures are in place then just link to these):"]
[]
[]
[]
[]
[]
["How the model works:"]
["ONS expenditure data is stored on the \"Data\" tab, using the postcode input into the model the region is found using the lookup table in the postcode summary tab, this region is then used to find the expenditure specific to that region on the ONS data tab, this is then multiplied by a weighting on the data tab, the weighting used is determined by the household occupancy input into the model. ONS expenditure is compared with actuals on the main sheet and flagged where actuals are less than or equal to 105%.\n\nMortgage repayments are calculated on their own tab, using inputs from the calculator, this sheet is also where stress rates are updated when changed.\n\n"]
[]
["Key points to know/check when using:"]
["The key points to ensure are correct are as follows:\n- Check \"Pass/Refer Check\" boxes, pass requires >=5% net free pay.\n- Review any flagged ONS differences.\n- Check \"Repayment Type Check\" box; displays \"Error\" if repayment type selected does not correlate with input capital/interest only values/split.\n-Ensure all fields completed\n\n"]
[]
["Key points to know/check when making changes:"]
["When SVR changes, stress rates are updated in the \"mortgage repayments\" tab. Annually when ONS is updated, the ONS table in the \"Data\" tab is updated as are the household weightings on the same tab."]
[]
["Sources of information:"]
["The main sources of information that feed into the model are:\n- ONS data\n- Stress rates\n"]
[]
["Key assumptions and judgements made:"]
[]
[]
["Key outputs of the model:"]
["The key outputs of the model are:\n- Pass/Refer - affordability outcome.\n- % net free pay.\n"]
[]
["Key controls within the model to ensure accuracy:"]
["The key controls within the model are:\n- Protected cells\n- Read only access for underwriters\n- Reset macro\n- Check cells, data validation\n- Model compliance with the Model Risk Management Policy.\n"]
[]
["Key to colour coded cells within the model:"]
["Blue shading: Formulated cells"]
["Green shading: Input cells"]
["Orange shading: Input cells (Dropdowns)"]

{"sheet":"Version History","index":2,"n_rows":54,"n_cols":4,"rows_kept":54,"cut":false,"hidden":true,"merged":[[42,0,45,0],[42,3,45,3]]}
["Date","Change History","Tab","Version Number"]
["2020-03-10","Added MPI Exclusive Criteria regarding LTI's and Max Loans","UW"]
["2020-04-16","Updated ONS/ Stress Rates","UW"]
["2020-06-12","Wording Row 19 \"Does lending criteria allow higher income multiples to be used?\"; Wales council tax aligned with grand total row","UW"]
["2021-11-16","LTI and advance limits adjusted","UW"]
["2022-01-12","Updated Stress Rates","UW"]
["2022-02-28","Updated Stress Rates","UW"]
["2022-07-12","Professionals change; Stress rates; ONS","UW"]
["2022-09-21","Updated Stress Rates","UW"]
["2022-10-14","Updated Stress Rates","UW"]
["2022-11-11","Updated Stress Rates","UW"]
["2023-01-17","Updated Stress Rates","UW"]
["2023-03-02","Changed stress from +3% to +1%","UW"]
["2023-06-30","Updated stress rates","UW"]
["2023-07-19","Updated stress rates","UW"]
["2020-04-16","Updated ONS, Stress Rates, NI Thresholds","Enquiry"]
["2020-07-22","Updated the downsizing feasibility table ","Enquiry"]
["2020-10-06","Added product range drop down boxes","Enquiry"]
["2021-04-09","Combined calculators and added additional fields","Enquiry"]
["2021-11-02","Added Pillars","Enquiry"]
["2021-11-16","LTI Updates","Enquiry"]
["2021-11-25","Added professional LTI","Enquiry"]
["2022-01-12","Updated stress rates","Enquiry"]
["2022-02-28","Updated stress rates","Enquiry"]
["2022-05-16","Updated NI","Enquiry"]
["2022-07-13","Updated ONS, Stress Rates","Enquiry"]
["2022-07-21","Monthly payment shown on OO sheet is now unstressed","Enquiry"]
["2022-09-21","Updated stress rates","Enquiry"]
["2022-10-14","Updated stress rates","Enquiry"]
["2022-11-11","Updated stress rates, reversed NI change","Enquiry"]
["2022-11-15","Updated product drop down","Enquiry"]
["2022-11-24","Updated product drop down, updated for income flex and LTI criteria, and wording per EC's email ","Enquiry"]
["2023-01-17","Updated stress rates","Enquiry"]
["2023-03-02","Changed stress from +3% to +1%, added self build fields","Enquiry"]
["2023-04-19","Removed transitional rules, basic rate tax payer now at 125% and higher of either 2% above payrate or 5.5%","Enquiry"]
["2023-06-30","Updated Stress Rates","Enquiry"]
["2023-07-19","Updated Stress Rates","Enquiry"]
["2023-07-26","Combined calculators"]
["2023-07-26","Added multiple household option; overtime now taken as input value; added LTI dropdowns; payslip deductions now made before tax calculation; self supporting on BTL changed from 1.35 to 1.25 & if self supporting now excluded from DTI; DTI changed to use monthly loan & mortgage payments, previously used balance outstanding.","Enquiry"]
["2023-07-26","Removed professional & credit history questions, replaced with LTI dropdowns - max LTI per applicant is selected by user, in the background each income is multiplied by the selected LTI multiple, the two highest products are added together, with the two remaining applicants income also added in without a multiplier applied to find the max loan. ","UW"]
["2023-07-26","Updated advance limits; added multiple households & applicants option (max 4); added DTI & LTI ratios; ONS is shown per household and then amalgamated for NFP calculation.","UW"]
["2023-08-03","Updated Stress Rates",null,1]
["2024-06-25","Updated council tax rates","UW/Enquiry",2]
[null,"Split payslip deductions into pre & post tax","Enquiry"]
[null,"Updated national insurance rates in tax calculator tabs","Enquiry"]
[null,"Added 1x income multiple","Enquiry"]
["2024-08-17","Updated Stress Rates","UW/Enquiry",3]
["2024-09-17","Adjusted the additional rate tax band from £150,000 to £125,140.","Enquiry",4]
["2024-09-27","Updated ONS","All",5]
["2024-11-18","Updated Stress Rates","UW/Enquiry",6]
["2025-03-02","Added Scotland ONS, tax rates, council tax. Updated Stress Rates.","UW/Enquiry",7]
["2025-06-20","Updated Stress Rates","UW/Enquiry",8]
["2025-06-25","Scotland tax bands updated for 25/26","Tax Calculator",9]
["2025-09-12","Updated Stress Rates","UW/Enquiry",10]

{"sheet":"Enquiry","index":3,"n_rows":128,"n_cols":29,"rows_kept":128,"cut":false,"hidden":true,"merged":[[41,28,41,34],[45,17,45,18],[45,20,45,22],[48,17,48,18],[48,20,48,22],[51,17,51,18],[51,20,51,22],[54,17,54,18],[54,20,54,22],[44,17,44,18],[47,17,47,18],[50,17,50,18],[53,17,53,18],[79,5,79,7],[79,10,79,12],[79,15,79,17],[79,20,79,22],[86,18,86,22],[85,21,85,22],[97,29,97,31],[106,18,106,20],[108,6,108,8],[108,18,108,20],[95,6,95,8],[95,18,95,20],[96,6,96,8],[96,18,96,20],[97,6,97,8],[107,18,107,20],[97,18,97,20],[102,18,102,20],[103,6,103,8],[103,18,103,20],[104,18,104,20],[98,29,98,31],[96,29,96,31],[82,10,82,12],[91,27,91,28],[84,0,86,0],[85,8,85,10],[85,14,85,16],[86,14,86,16],[89,6,89,8],[90,18,90,20],[91,6,91,8],[91,18,91,20],[86,8,86,10],[89,18,89,20],[88,18,88,20],[88,0,111,0],[106,1,111,1],[109,6,109,8],[109,18,109,20],[110,18,110,20],[90,6,90,8],[107,6,107,8],[94,18,94,20],[92,18,92,20],[0,19,0,21],[1,4,1,13],[58,20,58,22],[59,14,59,16],[49,4,49,13],[45,8,45,10],[38,10,38,22],[35,17,35,19],[100,1,105,1],[100,18,100,20],[101,6,101,8],[101,18,101,20],[102,6,102,8],[68,6,68,8],[68,10,68,12],[68,14,68,16],[48,8,48,10],[51,8,51,10],[54,8,54,10],[49,17,49,18],[50,4,50,7],[57,14,57,16],[2,19,2,22],[19,4,19,10],[21,4,21,11],[21,13,21,15],[3,0,9,22],[56,0,70,0],[58,6,58,8],[57,6,57,8],[59,6,59,8],[38,4,38,6],[32,4,32,6],[31,4,31,6],[31,12,31,14],[32,12,32,14],[29,0,33,0],[27,7,27,21],[37,4,37,6],[33,12,33,14],[31,16,31,18],[10,0,16,0],[21,0,24,0],[18,0,20,0],[23,8,23,22],[19,14,19,22],[10,4,10,5],[17,0,17,3],[17,4,17,7],[17,8,17,9],[17,10,17,22],[12,4,12,5],[13,4,13,5],[14,4,14,5],[15,4,15,5],[16,4,16,5],[18,4,18,11],[120,0,123,22],[114,0,118,0],[114,1,118,22],[119,20,119,22],[112,0,113,1],[112,2,113,3],[60,6,60,8],[60,10,60,12],[73,9,73,22],[62,20,62,22],[61,6,61,8],[60,14,60,16],[60,20,60,22],[63,20,63,22],[64,14,64,16],[64,20,64,22],[71,0,77,0],[61,14,61,16],[74,4,74,6],[74,9,74,22],[88,1,93,1],[94,1,99,1],[75,4,75,6],[98,18,98,20],[119,9,119,17],[112,17,113,17],[112,20,113,22],[4,23,4,24],[43,0,55,0],[56,6,56,22],[35,4,35,6],[35,10,35,12],[112,4,113,4],[112,5,113,6],[112,7,113,8],[112,9,113,11],[112,12,113,14],[112,18,113,19],[112,15,113,16],[67,20,67,22],[64,6,64,8],[64,10,64,12],[62,14,62,16],[18,12,18,13],[11,4,11,5],[73,4,73,6],[61,20,61,22],[22,4,22,22],[70,3,70,22],[58,24,59,26],[57,10,57,12],[65,10,65,12],[65,14,65,16],[65,20,65,22],[59,10,59,12],[58,14,58,16],[58,10,58,12],[59,20,59,22],[67,24,67,25],[61,24,61,25],[63,24,63,25],[65,24,65,25],[63,6,63,8],[37,10,37,15],[36,10,36,12],[36,4,36,6],[33,4,33,6],[63,14,63,16],[18,14,18,22],[82,15,82,17],[82,20,82,22],[43,4,43,13],[44,4,44,7],[46,4,46,13],[46,17,46,18],[43,17,43,18],[52,4,52,13],[47,4,47,7],[75,9,75,22],[76,4,76,6],[76,9,76,22],[61,10,61,12],[69,14,69,16],[63,10,63,12],[62,6,62,8],[62,10,62,12],[52,17,52,18],[53,4,53,7],[66,14,66,16],[69,6,69,8],[69,10,69,12],[67,6,67,8],[39,3,39,22],[65,6,65,8],[68,20,68,22],[66,20,66,22],[69,20,69,22],[66,6,66,8],[66,10,66,12],[67,10,67,12],[67,14,67,16],[78,0,80,0],[81,0,83,0],[78,5,78,7],[78,10,78,12],[78,15,78,17],[78,20,78,22],[25,0,27,0],[26,4,26,22],[30,4,30,6],[31,8,31,10],[32,8,32,10],[33,8,33,10],[41,0,41,22],[82,5,82,7],[35,0,39,0],[37,19,37,20],[36,15,36,20],[32,16,32,18],[33,16,33,18],[30,8,30,10],[30,12,30,14],[30,16,30,18],[29,4,29,6],[25,21,25,22]]}
[null,"Owner Occupier Referral Form"]
[null,"Product Range:"]
[null,"Reason for referral /area of non-compliance:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Date:"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Pillars"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"LTV:",{"$f":"IFERROR(E36/K36,\"\")","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"LTI:",{"$f":"F113","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Credit:",{"$f":"E24","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Afford:",{"$f":"J113","v":"Incomplete"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Prop:"]
["Self Build Only",null,null,"Loan required:",{"$f":"E36","v":0}]
[null,null,null,"PP/land value:"]
[null,null,null,"Build Costs:"]
[null,null,null,"Customer Contribution:"]
[null,null,null,"Loan to Cost:",{"$f":"IFERROR(E11/(E12+E13),\"\")","v":""}]
[null,null,null,"LTV:",{"$f":"Y6","v":""}]
[null,null,null,"Build length:"]
["Re-Referral Reason:",null,null,null,null,null,null,null,"Notes:"]
["Intro",null,null,"Broker Name:"]
[null,null,null,"Company:",null,null,null,null,null,null,null,null,null,"Case Owner:"]
[]
["Apps",null,null,"Surnames:",null,null,null,null,null,null,null,null,"Age:",null,null,null,"Term:",null,"yrs",null,"Split:"]
[null,null,null,"Connection:"]
[null,null,null,"Credit Policy:",null,null,null,"Details:"]
[]
["Property",null,null,"Storeys:",null,null,"Floor:",null,"Lease:",null,"yrs","Management company:",null,null,null,null,null,"Ground Rent RPI:"]
[null,null,null,"Property Type:"]
[null,null,null,"Consess Purchase:",null,null,"Notes:"]
[]
["Households",null,null,"Households being maintained:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"1st Household",{"$f":"IF(ISBLANK(E$32),E$33,VLOOKUP(LEFT(E$32,2),Postcodes!$A:$B,2,FALSE))","v":0},{"$f":"IFERROR((HLOOKUP($Z30,Data!$D$3:$N$16,14,FALSE)*VLOOKUP(E$34,Data!$C$46:$D$54,2,FALSE))+VLOOKUP($Z30,'Council tax'!$B$16:$F$27,5,FALSE),0)","v":0}]
[null,null,null,null,"Subject Property",null,null,null,"Property 2",null,null,null,"Property 3",null,null,null,"Property 4",null,null,null,null,null,null,null,"2nd Household",{"$f":"IF(ISBLANK(I$32),I$33,VLOOKUP(LEFT(I$32,2),Postcodes!$A:$B,2,FALSE))","v":0},{"$f":"IFERROR((HLOOKUP($Z31,Data!$D$3:$N$16,14,FALSE)*VLOOKUP(I$34,Data!$C$46:$D$54,2,FALSE))+VLOOKUP($Z31,'Council tax'!$B$16:$F$27,5,FALSE),0)","v":0}]
[null,null,null,"Postcode:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"3rd Household",{"$f":"IF(ISBLANK(M$32),M$33,VLOOKUP(LEFT(M$32,2),Postcodes!$A:$B,2,FALSE))","v":0},{"$f":"IFERROR((HLOOKUP($Z32,Data!$D$3:$N$16,14,FALSE)*VLOOKUP(M$34,Data!$C$46:$D$54,2,FALSE))+VLOOKUP($Z32,'Council tax'!$B$16:$F$27,5,FALSE),0)","v":0}]
[null,null,null,"Region if not known:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"4th Household",{"$f":"IF(ISBLANK(Q$32),Q$33,VLOOKUP(LEFT(Q$32,2),Postcodes!$A:$B,2,FALSE))","v":0},{"$f":"IFERROR((HLOOKUP($Z33,Data!$D$3:$N$16,14,FALSE)*VLOOKUP(Q$34,Data!$C$46:$D$54,2,FALSE))+VLOOKUP($Z33,'Council tax'!$B$16:$F$27,5,FALSE),0)","v":0}]
[null,null,null,"Occupants:",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total ONS",{"$f":"SUM(AA30:AA33)","v":0}]
[]
["Loan",null,null,"Loan £",null,null,null,null,null,"Value £",null,null,null,null,null,null,"Monthly Payment:",{"$f":"IFERROR('Mortgage Repayments (E)'!E4,\"\")","v":""}]
[null,null,null,"Repayment £",null,null,null,null,null,"Interest £",null,null,null,null,"MRV:"]
[null,null,null,"Type:",null,null,null,null,null,"Product:",null,null,null,null,null,null,null,null,"Pay rate:"]
[null,null,null,"Capital Raising £",null,null,null,null,null,"Reason:"]
[null,null,"Deposit:"]
[]
["Note: Overtime, Bonuses & Commission – Input 50% if standard 100% if Income Flex. Input ANNUAL amounts below:"]
[]
["Employed","App 1 ","Scotland:","Title:",null,null,null,null,null,null,null,null,null,null,null,null,"Time in role:",null,null,"Yrs",null,"Mths"]
[null,null,null,"Basic £",null,null,null,null,null,null,null,null,null,null,null,null,"100% allowances £"]
[null,null,null,null,null,null,null,"Overtime, Bonuses & Commission £",null,null,null,null,null,null,null,null,"Payslip deductions; Pre-Tax £:",null,null,", Post-Tax £:"]
[null,"App 2","Scotland:","Title:",null,null,null,null,null,null,null,null,null,null,null,null,"Time in role:",null,null,"Yrs",null,"Mths",null,{"$f":"E45+R45+(I46+E74)+U59+U60+U61-R46-U46","v":0}]
[null,null,null,"Basic £",null,null,null,null,null,null,null,null,null,null,null,null,"100% allowances £"]
[null,null,null,null,null,null,null,"Overtime, Bonuses & Commission £",null,null,null,null,null,null,null,null,"Payslip deductions; Pre-Tax £:",null,null,", Post-Tax £:",null,null,null,{"$f":"E48+R48+(I49+E75)+U62+U63+U64-R49-U49","v":0}]
[null,"App 3","Scotland:","Title:",null,null,null,null,null,null,null,null,null,null,null,null,"Time in role:",null,null,"Yrs",null,"Mths"]
[null,null,null,"Basic £",null,null,null,null,null,null,null,null,null,null,null,null,"100% allowances £",null,null,null,null,null,null,{"$f":"E51+R51+(I52+E76)+U65+U66+U67-R52-U52","v":0}]
[null,null,null,null,null,null,null,"Overtime, Bonuses & Commission £",null,null,null,null,null,null,null,null,"Payslip deductions; Pre-Tax £:",null,null,", Post-Tax £:"]
[null,"App 4","Scotland:","Title:",null,null,null,null,null,null,null,null,null,null,null,null,"Time in role:",null,null,"Yrs",null,"Mths",null,{"$f":"E54+R54+(I55+E77)+U68+U69+U70-R55-U55","v":0}]
[null,null,null,"Basic £",null,null,null,null,null,null,null,null,null,null,null,null,"100% allowances £"]
[null,null,null,null,null,null,null,"Overtime, Bonuses & Commission £",null,null,null,null,null,null,null,null,"Payslip deductions; Pre-Tax £:",null,null,", Post-Tax £:"]
[]
["Self Employed",null,null,null,null,"Type of Business(es):"]
[null,null,null,null,null,"Year ending:"]
[null,"App 1 ",null,null,null,"Salary £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £",null,null,null,null,"Use tax calculator or figure provided by broker"]
[null,null,null,null,null,"Dividends/Net Profit £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £"]
[null,null,null,null,null,"Land Prop £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £"]
[null,"App 2",null,null,null,"Salary £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £",null,null,null,null,"App 1 Net Pay £",null,{"$f":"SUM('Tax Calculator'!E38)-(U46/12)","v":0}]
[null,null,null,null,null,"Dividends/Net Profit £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £"]
[null,null,null,null,null,"Land Prop £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £",null,null,null,null,"App 2 Net Pay £",null,{"$f":"SUM('Tax Calculator'!J38)-(U49/12)","v":0}]
[null,"App 3",null,null,null,"Salary £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £"]
[null,null,null,null,null,"Dividends/Net Profit £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £",null,null,null,null,"App 3 Net Pay £",null,{"$f":"'Tax Calculator'!O38-(U52/12)","v":0}]
[null,null,null,null,null,"Land Prop £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £"]
[null,"App 4",null,null,null,"Salary £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £",null,null,null,null,"App 4 Net Pay £",null,{"$f":"'Tax Calculator'!T38-(U55/12)","v":0}]
[null,null,null,null,null,"Dividends/Net Profit £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £"]
[null,null,null,null,null,"Land Prop £",null,null,null,"£",null,null,null,"£",null,null,null,null,null,"Average £"]
[null,"Notes:"]
["Other","Other Income (i.e. Second job, benefits payments) – Input 50% if standard 100% if Income Flex"]
[]
[null,"App 1 annual £",null,null,null,null,null,null,"Details:"]
[null,"App 2 annual £",null,null,null,null,null,null,"Details:"]
[null,"App 3 annual £",null,null,null,null,null,null,"Details:"]
[null,"App 4 annual £",null,null,null,null,null,null,"Details:"]
[]
["Net",null,null,null,null,"App 1",null,null,null,null,"App 2",null,null,null,null,"App 3",null,null,null,null,"App 4"]
[null,null,"Net Pay (£):"]
[]
["LTI"]
[null,null,"Max LTI Applicable:"]
[]
["BTL"]
[null,null,null,"Number of BTLs:",null,null,null,"Total Value £",null,null,null,null,null,"Balances £",null,null,null,null,"Overall LTV:",null,null,{"$f":"IFERROR(O86/I86,\"\")","v":""}]
[null,null,null,null,null,null,null,"Monthly Payment £",null,null,null,null,null,"Total Rental £",null,null,null,null,{"$f":"IF(O87=\"\",\"\",IF(O87<(I87*1.25),\"Not Self Supporting\",\"Self Supporting\"))","v":""}]
[]
["Credit","App 1 ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Credit card & Overdraft total outstanding £"]
[null,null,null,null,null,"Maintenance, Loans & HP per month £",null,null,null,null,null,null,null,null,null,null,null,"Loans total outstanding £"]
[null,null,null,null,null,"Child Commitments £",null,null,null,null,null,null,null,null,null,null,null,"Service Charge & ground rent £"]
[null,null,null,null,null,"Other Expenditure £",null,null,null,null,null,null,null,null,null,null,null,"Other monthly mortgage Payments £"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total other mortgage balance outstanding £"]
[]
[null,"App 2",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Credit card & Overdraft total outstanding £"]
[null,null,null,null,null,"Maintenance, Loans & HP per month £",null,null,null,null,null,null,null,null,null,null,null,"Loans total outstanding £"]
[null,null,null,null,null,"Child Commitments £",null,null,null,null,null,null,null,null,null,null,null,"Service Charge & ground rent £"]
[null,null,null,null,null,"Other Expenditure £",null,null,null,null,null,null,null,null,null,null,null,"Other monthly mortgage Payments £"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total other mortgage balance outstanding £"]
[]
[null,"App 3",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Credit card & Overdraft total outstanding £"]
[null,null,null,null,null,"Maintenance, Loans & HP per month £",null,null,null,null,null,null,null,null,null,null,null,"Loans total outstanding £"]
[null,null,null,null,null,"Child Commitments £",null,null,null,null,null,null,null,null,null,null,null,"Service Charge & ground rent £"]
[null,null,null,null,null,"Other Expenditure £",null,null,null,null,null,null,null,null,null,null,null,"Other monthly mortgage Payments £"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total other mortgage balance outstanding £"]
[]
[null,"App 4",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Credit card & Overdraft total outstanding £"]
[null,null,null,null,null,"Maintenance, Loans & HP per month £",null,null,null,null,null,null,null,null,null,null,null,"Loans total outstanding £"]
[null,null,null,null,null,"Child Commitments £",null,null,null,null,null,null,null,null,null,null,null,"Service Charge & ground rent £"]
[null,null,null,null,null,"Other Expenditure £",null,null,null,null,null,null,null,null,null,null,null,"Other monthly mortgage Payments £"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total other mortgage balance outstanding £"]
[]
["Max loan £",null,{"$f":"'E Calc'!D60","v":0},null,"LTI:",{"$f":"IFERROR(E36/'E Calc'!K41,\"\")","v":""},null,"Net free pay £",null,{"$f":"IFERROR('E Calc'!D63,\"Incomplete\")","v":"Incomplete"},null,null,"Net Free pay Pass/Fail",null,null,{"$f":"IFERROR('E Calc'!D64,\"\")","v":""},null,null,"DTI:",null,{"$f":"IFERROR((AC113+AC114+AC115)/(SUM(X47:X53)),0)","v":0},null,null,null,"Credit card & Overdraft total outstanding £",null,null,null,{"$f":"S89+S95+S101+S107","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Annual loan commitments £",null,null,null,{"$f":"(G90+G96+G102+G108)*12","v":0}]
["Other info",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Annual mortgage payments £",null,null,null,{"$f":"((S92+S98+S104+S110)+IF(S87<>\"Self Supporting\",I87,0))*12","v":0}]
[]
[]
[]
[]
[null,"Comments from committee",null,null,null,null,null,"Result:",null,null,null,null,null,null,null,null,null,null,null,"Date:"]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Downsizing"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Remortgage",null,"Yes",null,"Sale of investment property"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"FTB - renting",null,"No"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"FTB - parents"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Next Time Buyer"]

{"sheet":"Affordability Calculator","index":4,"n_rows":94,"n_cols":26,"rows_kept":94,"cut":false,"hidden":false,"merged":[[92,1,92,3],[92,4,92,7],[86,25,87,25],[62,24,62,25],[91,1,91,3],[91,4,91,7],[1,1,1,8],[85,6,86,7],[35,3,35,5],[33,3,33,5],[85,3,86,5],[41,3,41,5],[37,3,37,5],[1,17,9,18],[93,1,97,6],[24,15,25,15],[47,23,48,23],[45,23,46,25],[6,2,6,6],[47,25,48,25],[47,24,48,24],[24,3,25,3],[24,7,25,7],[24,11,25,11],[48,19,48,22],[7,21,7,26],[72,24,72,25],[50,24,50,25],[86,24,87,24]]}
[]
[null,"Affordability Assessment "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Input"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Dropdown"]
[null,"Date"]
[]
[null,"Type of application","House purchase"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"LTI CALCULATION"]
[null,"Households being maintained"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Income","LTI"]
[null,"Postcode",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"APP1",{"$f":"$C$27-($C$30*12)-($C$32*12)","v":0},{"$f":"$C$23","v":0},{"$f":"Y11*X11","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"APP2",{"$f":"$G$27-($G$30*12)-($G$32*12)","v":0},{"$f":"$G$23","v":0},{"$f":"Y12*X12","v":0}]
[null,"Region",{"$f":"_xlfn.IFNA(VLOOKUP(LEFT(C11, 2), Postcodes!$A$1:$B$9486, 2, FALSE),\"\")","v":""},null,null,null,{"$f":"_xlfn.IFNA(VLOOKUP(LEFT(G11, 2), Postcodes!$A$1:$B$9486, 2, FALSE),\"\")","v":""},null,null,null,{"$f":"_xlfn.IFNA(VLOOKUP(LEFT(K11, 2), Postcodes!$A$1:$B$9486, 2, FALSE),\"\")","v":""},null,null,null,{"$f":"_xlfn.IFNA(VLOOKUP(LEFT(O11, 2), Postcodes!$A$1:$B$9486, 2, FALSE),\"\")","v":""},null,null,null,null,null,null,null,"APP3",{"$f":"$K$27-($K$30*12)-($K$32*12)","v":0},{"$f":"$K$23","v":0},0]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"APP4",{"$f":"$O$27-($O$30*12)-($O$32*12)","v":0},{"$f":"$O$23","v":0},0]
[null,"Occupancy"]
[]
[null,"ONS Weighting",{"$f":"IFERROR(VLOOKUP(C15,Data!$C$45:$D$54, 2, FALSE),0)","v":0},null,null,null,{"$f":"IFERROR(VLOOKUP(G15,Data!$C$45:$D$54, 2, FALSE),0)","v":0},null,null,null,{"$f":"IFERROR(VLOOKUP(K15,Data!$C$45:$D$54, 2, FALSE),0)","v":0},null,null,null,{"$f":"IFERROR(VLOOKUP(O15,Data!$C$45:$D$54, 2, FALSE),0)","v":0}]
[]
[null,"Number of applicants"]
[]
[null,null,"Applicant 1",null,null,null,"Applicant 2",null,null,null,"Applicant 3",null,null,null,"Applicant 4"]
[]
[null,"Max LTI Applicable"]
[]
[null,"Date of birth",null,null,{"$f":"IF((($C$5/365)+($C$40)-($C$25/365))<75, \"<75\", \">75\")","v":"<75"},null,null,null,{"$f":"IF((($C$5/365)+($C$40)-($G$25/365))<75, \"<75\", \">75\")","v":"<75"},null,null,null,{"$f":"IF((($C$5/365)+($C$40)-($K$25/365))<75, \"<75\", \">75\")","v":"<75"},null,null,null,{"$f":"IF((($C$5/365)+($C$40)-($O$25/365))<75, \"<75\", \">75\")","v":"<75"}]
[]
[null,"Verified Gross Income"]
[null,"Verified (monthly) Net Income"]
[]
[null,"Monthly Loan Payments"]
[null,"TOTAL credit card outstanding"]
[null,"Credit card at 3%",{"$f":"C31*3%","v":0},null,null,null,{"$f":"G31*3%","v":0},null,null,null,{"$f":"K31*3%","v":0},null,null,null,{"$f":"O31*3%","v":0}]
[]
[null,"Property Value",null,"LTV",null,null,{"$f":"_xlfn.NUMBERVALUE(IF(CEILING(C36/C34,0.05)<0.6,0.6,CEILING(C36/C34,0.05)))","v":{"$error":"#DIV/0!"}}]
[]
[null,"Mortgage required",null,"LTV Limit",null,null,{"$f":"VLOOKUP($G$34,Data!$C$59:$D$66,2,FALSE)","v":{"$error":"#DIV/0!"}},null,"LTI",null,{"$f":"C36/SUM($X$11:$X$14)","v":{"$error":"#DIV/0!"}}]
[]
[null,"MAXIMUM LOAN (Using LTI)",{"$f":"INDEX($X$11:$X$14,MATCH(LARGE($Z$11:$Z$14,3),$Z$11:$Z$14,0))+INDEX($X$11:$X$14,MATCH(LARGE($Z$11:$Z$14,4),$Z$11:$Z$14,0))+(LARGE($Z$11:$Z$14,1)+LARGE($Z$11:$Z$14,2))","v":0},"Max Loan Check",null,null,{"$f":"IF(C36>MAX(G36,G36),\"Mortgage Required Greater than Max Loan\",IF(C36>C38,\"BELOW INCOME REQUIRED\",\"Proceed To Affordability\"))","v":{"$error":"#DIV/0!"}},null,"DTI",null,{"$f":"((($C$30+$G$30+$K$30+$O$30)*12)+($C$31+G31+K31+O31))/(C27+G27+K27+O27)","v":{"$error":"#DIV/0!"}}]
[]
[null,"Number of years"]
[]
[null,"Repayment type",null,"Repayment Type Check",null,null,{"$f":"_xlfn.IFS(C42=\"interest only\",IF(C46=C36,\"\",\"Error\"),C42=\"capital repayment\",IF(C44=C36,\"\",\"Error\"),C42=\"Part capital repayment/Part interest only\",IF(AND(C44>0,C46>0,C36-C44-C46=0),\"\",\"Error\"))","v":{"$error":"#N/A"}}]
[]
[null,"Part capital (£)",{"$f":"$C$36-$C$46","v":0}]
[]
[null,"Part interest only (£)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"ONS"]
[]
[null,"Product rate ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Grand Total ONS Figures","Actual as % of ONS","Review if under or equal to 105% of ONS"]
[null,"Product length",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Totals",null,"ONS Figures"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"H1","H2","H3","H4"]
[null,"Child commitments",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Child Coms:",{"$f":"C51+G51+K51+O51","v":0},null,null,null,null,{"$f":"S51","v":0},"Actuals Used"]
[]
[null,"Property commitments:"]
[null,"Council tax band if known"]
[null,"Council tax discount available"]
[1,"Gas/electrics",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Property Excl. 3,7,8",{"$f":"SUM(C56:O57)+SUM(C59:O61)","v":0},{"$f":"IF(C17=0,\"\",HLOOKUP($C$13, Data!$D$3:$N$17, 10, FALSE)*C17)","v":""},{"$f":"IF(G17=0,\"\",HLOOKUP($G$13, Data!$D$3:$N$17, 10, FALSE)*G17)","v":""},{"$f":"IF(K17=0,\"\",HLOOKUP($K$13, Data!$D$3:$N$17, 10, FALSE)*K17)","v":""},{"$f":"IF(O17=0,\"\",HLOOKUP($O$13, Data!$D$3:$N$17, 10, FALSE)*O17)","v":""},{"$f":"SUM(T56:W56)","v":0},{"$f":"S56/X56","v":{"$error":"#DIV/0!"}},{"$f":"IF(Y56<=105%,\"Review.\",\"Do not Review\")","v":{"$error":"#DIV/0!"}}]
[2,"Water"]
[3,"Council tax",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Council Tax",{"$f":"C58+G58+K58+O58","v":0},{"$f":"IF(C17=0,\"\",'Council tax'!C36)","v":""},{"$f":"IF(G17=0,\"\",'Council tax'!D36)","v":""},{"$f":"IF(K17=0,\"\",'Council tax'!E36)","v":""},{"$f":"IF(O17=0,\"\",'Council tax'!F36)","v":""},{"$f":"SUM(T58:W58)","v":0},{"$f":"S58/X58","v":{"$error":"#DIV/0!"}},{"$f":"IF(Y58<=105%,\"Review.\",\"Do not Review\")","v":{"$error":"#DIV/0!"}}]
[4,"Ground rent"]
[5,"Building and contents insurance"]
[6,"Property costs (repairs and maintenance)"]
[7,"Telephone/Mobile/TV packages",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Telephone etc",{"$f":"C62+G62+K62+O62","v":0},{"$f":"IF(C17=0,\"\",HLOOKUP($C$13, Data!$D$3:$N$17, 5, FALSE)*C17)","v":""},{"$f":"IF(G17=0,\"\",HLOOKUP($G$13, Data!$D$3:$N$17, 5, FALSE)*G17)","v":""},{"$f":"IF(K17=0,\"\",HLOOKUP($K$13, Data!$D$3:$N$17, 5, FALSE)*K17)","v":""},{"$f":"IF(O17=0,\"\",HLOOKUP($O$13, Data!$D$3:$N$17, 5, FALSE)*O17)","v":""},{"$f":"SUM(T62:W62)","v":0},{"$f":"S62/X62","v":{"$error":"#DIV/0!"}},{"$f":"IF(Y62<=105%,\"Review.\",\"Do not Review\")","v":{"$error":"#DIV/0!"}}]
[8,"Other",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Other",{"$f":"C63+G63+K63+O63","v":0},null,null,null,null,null,"Not Included"]
[]
[null,"Travel"]
[1,"Petrol",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total Travel",{"$f":"SUM(C66:O69)","v":0},{"$f":"IF(C17=0,\"\",HLOOKUP($C$13, Data!$D$3:$N$17, 12, FALSE)*C17)","v":""},{"$f":"IF(G17=0,\"\",HLOOKUP($G$13, Data!$D$3:$N$17, 12, FALSE)*G17)","v":""},{"$f":"IF(K17=0,\"\",HLOOKUP($K$13, Data!$D$3:$N$17, 12, FALSE)*K17)","v":""},{"$f":"IF(O17=0,\"\",HLOOKUP($O$13, Data!$D$3:$N$17, 12, FALSE)*O17)","v":""},{"$f":"SUM(T66:W66)","v":0},{"$f":"S66/X66","v":{"$error":"#DIV/0!"}},{"$f":"IF(Y66<=105%,\"Review.\",\"Do not Review\")","v":{"$error":"#DIV/0!"}}]
[2,"Vehicle costs/MOT/servicing etc"]
[3,"Car insurance"]
[4,"Other (including public transport/season tickets and taxis)"]
[]
[null,"Food and living costs"]
[1,"Grocery shopping",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Grocery ",{"$f":"C72+G72+K72+O72","v":0},{"$f":"IF(C17=0,\"\",HLOOKUP($C$13, Data!$D$3:$N$17, 7, FALSE)*C17)","v":""},{"$f":"IF(G17=0,\"\",HLOOKUP($G$13, Data!$D$3:$N$17, 7, FALSE)*G17)","v":""},{"$f":"IF(K17=0,\"\",HLOOKUP($K$13, Data!$D$3:$N$17, 7, FALSE)*K17)","v":""},{"$f":"IF(O17=0,\"\",HLOOKUP($O$13, Data!$D$3:$N$17, 7, FALSE)*O17)","v":""},{"$f":"SUM(T72:W72)","v":0},{"$f":"S72/X72","v":{"$error":"#DIV/0!"}},{"$f":"IF(Y72<=105%,\"Review.\",\"Do not Review\")","v":{"$error":"#DIV/0!"}}]
[2,"Regular social activities",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Social activities",{"$f":"C73+G73+K73+O73","v":0},null,null,null,null,null,"Not Included"]
[3,"clothing/footwear",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Clothing/footwear",{"$f":"C74+G74+K74+O74","v":0},{"$f":"IF(C17=0,\"\",HLOOKUP($C$13, Data!$D$3:$N$17, 4, FALSE)*C17)","v":""},{"$f":"IF(G17=0,\"\",HLOOKUP($G$13, Data!$D$3:$N$17, 4, FALSE)*G17)","v":""},{"$f":"IF(K17=0,\"\",HLOOKUP($K$13, Data!$D$3:$N$17, 4, FALSE)*K17)","v":""},{"$f":"IF(O17=0,\"\",HLOOKUP($O$13, Data!$D$3:$N$17, 4, FALSE)*O17)","v":""},{"$f":"SUM(T74:W74)","v":0},{"$f":"S74/X74","v":{"$error":"#DIV/0!"}},{"$f":"IF(Y74<=105%,\"Review.\",\"Do not Review\")","v":{"$error":"#DIV/0!"}}]
[4,"Other (luxury/holidays/hobbies etc)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Other etc",{"$f":"C75+G75+K75+O75","v":0},{"$f":"IF(C17=0,\"\",((HLOOKUP($C$13,Data!$D$3:$N$17,8,FALSE)+(HLOOKUP($C$13,Data!$D$3:$N$17,11,FALSE))+(HLOOKUP($C$13,Data!$D$3:$N$17,9,FALSE)))*C17))","v":""},{"$f":"IF(G17=0,\"\",((HLOOKUP($G$13,Data!$D$3:$N$17,8,FALSE)+(HLOOKUP($G$13,Data!$D$3:$N$17,11,FALSE))+(HLOOKUP($G$13,Data!$D$3:$N$17,9,FALSE)))*G17))","v":""},{"$f":"IF(K17=0,\"\",((HLOOKUP($K$13,Data!$D$3:$N$17,8,FALSE)+(HLOOKUP($K$13,Data!$D$3:$N$17,11,FALSE))+(HLOOKUP($K$13,Data!$D$3:$N$17,9,FALSE)))*K17))","v":""},{"$f":"IF(O17=0,\"\",((HLOOKUP($O$13,Data!$D$3:$N$17,8,FALSE)+(HLOOKUP($O$13,Data!$D$3:$N$17,11,FALSE))+(HLOOKUP($O$13,Data!$D$3:$N$17,9,FALSE)))*O17))","v":""},{"$f":"SUM(T75:W75)","v":0},{"$f":"S75/X75","v":{"$error":"#DIV/0!"}},{"$f":"IF(Y75<=105%,\"Review.\",\"Do not Review\")","v":{"$error":"#DIV/0!"}}]
[]
[]
[null,"Monthly expenditure",{"$f":"C51+C57+C58+C59+C60+C61+C62+C65+C63+C66+C67+C68+C71+C72+C73+C74+C75+C56+C69+C32+C30","v":0},null,null,null,{"$f":"G51+G57+G58+G59+G60+G61+G62+G65+G63+G66+G67+G68+G71+G72+G73+G74+G75+G56+G69+G32+G30","v":0},null,null,null,{"$f":"K51+K57+K58+K59+K60+K61+K62+K65+K63+K66+K67+K68+K71+K72+K73+K74+K75+K56+K69+K32+K30","v":0},null,null,null,{"$f":"O51+O57+O58+O59+O60+O61+O62+O65+O63+O66+O67+O68+O71+O72+O73+O74+O75+O56+O69+O32+O30","v":0},null,null,null,null,null,null,null,"Totals (ONS + Loans)",{"$f":"SUM(X51:X75)+C30+C32+G30+G32+K30+K32+O30+O32","v":0}]
[]
[]
[null,"Actual interest rate",{"$f":"'Mortgage Repayments (UW)'!E15","v":0}]
[null,"Net free pay",{"$f":"$C$28+$G$28+$K$28+$O$28-$C$78-$G$78-$K$78-$O$78-'Mortgage Repayments (UW)'!$E$4","v":{"$error":"#N/A"}},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"ONS Net Free Pay",{"$f":"$C$28+$G$28+K28+O28-X78-'Mortgage Repayments (UW)'!$E$4","v":{"$error":"#N/A"}}]
[null,"% net free pay",{"$f":"C82/(C28+G28+K28+O28)","v":{"$error":"#N/A"}}]
[null,"Mortgage payment",{"$f":"VLOOKUP($C$42, 'Mortgage Repayments (UW)'!$M$5:$O$7, 2, FALSE)","v":{"$error":"#N/A"}}]
[]
[null,"Stressed interest rate",{"$f":"'Mortgage Repayments (UW)'!J15","v":{"$error":"#N/A"}},"Pass/Refer",null,null,{"$f":"IFERROR(IF(C87>=((C28+G28+K28+O28)*5%), \"Pass\", \"Refer\"),\"\")","v":""}]
[null,"Net free pay stressed",{"$f":"$C$28+$G$28+$K$28+$O$28-$C$78-$G$78-$K$78-$O$78-'Mortgage Repayments (UW)'!$J$4","v":{"$error":"#N/A"}},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"ONS Stressed Net Free Pay",{"$f":"$C$28+$G$28+K28+O28-$X$78-'Mortgage Repayments (UW)'!$J$4","v":{"$error":"#N/A"}},"Pass/Refer",{"$f":"IF(X87>=((C28+G28+K28+O28)*5%),\"Pass\",\"Refer\")","v":{"$error":"#N/A"}}]
[null,"% net stressed free pay",{"$f":"C87/(C28+G28+K28+O28)","v":{"$error":"#N/A"}},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"% Net Stressed Free Pay",{"$f":"X87/(C28+G28+K28+O28)","v":{"$error":"#N/A"}}]
[null,"Mortgage payment",{"$f":"VLOOKUP($C$42, 'Mortgage Repayments (UW)'!$M$5:$O$7, 3, FALSE)","v":{"$error":"#N/A"}}]
[]
[]
[null," Pass/Refer",null,null,{"$f":"Z87","v":{"$error":"#N/A"}}]
[]
[null,"Please ensure if a Pass the Maximum Loan states Proceed to Affordability \nIf \"Refer\" please call the Sales team on 01455 894084 if you need any further assistance. "]

{"sheet":"Mortgage Repayments (UW)","index":5,"n_rows":27,"n_cols":15,"rows_kept":27,"cut":false,"hidden":true,"merged":[[1,1,1,5],[1,6,1,10]]}
[]
[null,"STANDARD RATE",null,null,null,null,"STRESSED RATE"]
[]
[null,"Monthly repayment",null,null,{"$f":"VLOOKUP('Affordability Calculator'!$C$42, 'Mortgage Repayments (UW)'!$M$5:$N$7, 2, FALSE)","v":{"$error":"#N/A"}},null,"Monthly repayment",null,null,{"$f":"VLOOKUP('Affordability Calculator'!$C$42, 'Mortgage Repayments (UW)'!$M$5:$O$7, 3, FALSE)","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,"capital repayment",{"$f":"E18","v":{"$error":"#NUM!"}},{"$f":"J18","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,"interest only",{"$f":"E24","v":0},{"$f":"J24","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Part capital repayment/Part interest only",{"$f":"E27","v":{"$error":"#NUM!"}},{"$f":"J27","v":{"$error":"#N/A"}}]
[null,"Capital Repayment",null,null,null,null,"Capital Repayment"]
[]
[null,null,"Mortgage amount",null,{"$f":"'Affordability Calculator'!C44","v":0},null,null,"Mortgage amount",null,{"$f":"'Affordability Calculator'!C44","v":0},null,null,"Less than 5 years",7.8899999999999998E-2]
[null,null,null,null,null,null,null,null,null,null,null,null,"5 years",{"$f":"E15","v":0},"no stress required"]
[null,null,"Term",null,{"$f":"'Affordability Calculator'!$C$40","v":0},"Years",null,"Term",null,{"$f":"'Affordability Calculator'!$C$40","v":0},"Years",null,"Lifetime",{"$f":"E15+1%","v":0.01}]
[null,null,null,null,{"$f":"E12*12","v":0},"Months",null,null,null,{"$f":"J12*12","v":0},"Months"]
[]
[null,null,"Interest Rate (APR)",null,{"$f":"TALO6","v":0},"%",null,"Interest Rate (APR)",null,{"$f":"VLOOKUP('Affordability Calculator'!C49, 'Mortgage Repayments (UW)'!M10:N12, 2, FALSE)","v":{"$error":"#N/A"}},"%"]
[null,null,null,"monthly",{"$f":"E15/12","v":0},null,null,null,"monthly",{"$f":"J15/12","v":{"$error":"#N/A"}}]
[]
[null,null,"Monthly Payments",null,{"$f":"-PMT(E16,E13,E10)","v":{"$error":"#NUM!"}},null,null,"Monthly Payments",null,{"$f":"-PMT(J16,J13,J10)","v":{"$error":"#N/A"}}]
[]
[]
[]
[]
[null,"Interest only mortgage amount",null,null,{"$f":"'Affordability Calculator'!C46","v":0},null,null,null,null,{"$f":"'Affordability Calculator'!C46","v":0}]
[null,"Interest only",null,null,{"$f":"(E23*E15)/12","v":0},null,"Interest only",null,null,{"$f":"(J23*J15)/12","v":{"$error":"#N/A"}}]
[]
[]
[null,"Part and part",null,null,{"$f":"E18+E24","v":{"$error":"#NUM!"}},null,null,null,null,{"$f":"J18+J24","v":{"$error":"#N/A"}}]

{"sheet":"Mortgage Repayments (E)","index":6,"n_rows":27,"n_cols":15,"rows_kept":27,"cut":false,"hidden":true,"merged":[[1,1,1,5],[1,6,1,10]]}
[]
[null,"STANDARD RATE",null,null,null,null,"STRESSED RATE"]
[]
[null,"Monthly repayment",null,null,{"$f":"VLOOKUP('E Calc'!D9, 'Mortgage Repayments (E)'!$M$5:$O$7, 2, FALSE)","v":{"$error":"#NUM!"}},null,"Monthly repayment",null,null,{"$f":"VLOOKUP('E Calc'!D9, 'Mortgage Repayments (E)'!$M$5:$O$7, 3, FALSE)","v":{"$error":"#N/A"}},null,null,null,"Standard","Stressed"]
[null,null,null,null,null,null,null,null,null,null,null,null,"Capital Repayment",{"$f":"E18","v":{"$error":"#NUM!"}},{"$f":"J18","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Interest Only",{"$f":"E24","v":0},{"$f":"J24","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Part capital repayment/Part interest only",{"$f":"E27","v":{"$error":"#NUM!"}},{"$f":"J27","v":{"$error":"#N/A"}}]
[null,"Capital Repayment",null,null,null,null,"Capital Repayment"]
[]
[null,null,"Mortgage amount",null,{"$f":"J10","v":0},null,null,"Mortgage amount",null,{"$f":"'E Calc'!D11","v":0},null,null,"5 year Fixed",{"$f":"'E Calc'!D17","v":0},"no stress required"]
[null,null,null,null,null,null,null,null,null,null,null,null,"Lifetime Discount/Tracker",{"$f":"'E Calc'!D17+1%","v":0.01}]
[null,null,"Term",null,{"$f":"J12","v":0},"Years",null,"Term",null,{"$f":"'E Calc'!D7","v":0},"Years",null,"Other (all 2 & 3 year products)",{"$f":"'Mortgage Repayments (UW)'!$N$10","v":7.8899999999999998E-2}]
[null,null,null,null,{"$f":"E12*12","v":0},"Months",null,null,null,{"$f":"J12*12","v":0},"Months",null,"Self-Build"]
[]
[null,null,"Interest Rate (APR)",null,{"$f":"Enquiry!T38","v":0},"%",null,"Interest Rate (APR)",null,{"$f":"VLOOKUP('E Calc'!D15, 'Mortgage Repayments (E)'!M10:N13, 2, FALSE)","v":{"$error":"#N/A"}},"%"]
[null,null,null,"monthly",{"$f":"E15/12","v":0},null,null,null,"monthly",{"$f":"J15/12","v":{"$error":"#N/A"}}]
[]
[null,null,"Monthly Payments",null,{"$f":"-PMT(E16,E13,E10)","v":{"$error":"#NUM!"}},null,null,"Monthly Payments",null,{"$f":"-PMT(J16,J13,J10)","v":{"$error":"#N/A"}}]
[]
[]
[]
[]
[null,"Interest only mortgage amount",null,null,{"$f":"J23","v":0},null,null,null,null,{"$f":"'E Calc'!D13","v":0}]
[null,"Interest only",null,null,{"$f":"(E23*E15)/12","v":0},null,"Interest only",null,null,{"$f":"(J23*J15)/12","v":{"$error":"#N/A"}}]
[]
[]
[null,"Part and part",null,null,{"$f":"E18+E24","v":{"$error":"#NUM!"}},null,null,null,null,{"$f":"J18+J24","v":{"$error":"#N/A"}}]

{"sheet":"Data","index":7,"n_rows":66,"n_cols":18,"rows_kept":66,"cut":false,"hidden":true,"merged":[]}
[]
[null,null,"2023 DATA"]
[null,1,"ONS Table A33/A35 Monthly expenditure","North East","North West","Yorkshire & the Humber","East Midlands","West Midlands","East England","London","South East","South West","Wales","Scotland",null,null,null,"Standard Resi (No MRC)"]
[null,2,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Standard BTL (No MRC)"]
[null,3,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Credit Flex"]
[null,4,"Clothing & footwear",59.145190000000007,72.689890000000005,58.242210000000007,69.52946,69.077970000000022,65.917539999999988,79.010750000000002,66.820520000000016,69.52946,65.917539999999988,62.30561999999999,null,null,null,"Specialist Resi (MRC Referral)"]
[null,5,"Communication",84.428629999999998,95.715879999999999,86.686080000000004,93.909920000000014,93.458429999999993,101.13376,93.00694,97.070350000000005,96.618860000000012,91.652470000000022,93.909920000000014,null,null,null,"BTL Specialist (MRC Referral)"]
[null,6,"Education",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"JBSP"]
[null,7,"Food & non-alcoholic drinks",250.12545999999998,288.50211000000002,266.83059000000003,292.11403000000001,283.98721,306.11022000000003,305.65873000000005,320.10641000000004,296.62893000000003,281.27826999999996,274.95740999999998,null,null,null,"Income Flex"]
[null,8,"Health",20.317050000000002,35.21622,36.570690000000006,37.925159999999998,46.50347,35.21622,37.022179999999999,46.05198,35.66771,30.249830000000003,29.79834,null,null,null,"BTL JBSP"]
[null,9,"Household goods & services",145.37978000000001,139.96190000000001,158.92448000000002,142.21935000000002,134.54402000000002,153.95809,145.37978000000001,162.53640000000001,158.47299000000001,125.06272999999999,139.96190000000001,null,null,null,"BTL Non Owner"]
[null,10,"Housing (net), fuel & power",181.95047,218.06967,210.84583000000001,207.68540000000002,215.36073000000002,232.96884000000003,230.71138999999999,246.51354000000001,231.61436999999998,218.52116000000004,211.74880999999999,null,null,null,"BTL Top Slicing"]
[null,11,"Miscellaneous goods and services",92.103960000000001,128.22316000000001,114.67845999999999,117.38740000000003,125.06272999999999,130.02912000000001,138.15593999999999,140.41338999999999,123.25677000000002,112.42101000000002,110.16355999999998,null,null,null,"Ex-Pat"]
[null,12,"Transport",158.0215,186.01388,179.69302000000005,191.43176,171.11471000000003,205.42794999999998,152.15213,236.12926999999999,203.17050000000003,178.79004,181.95047,null,null,null,"LTD Company"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Green Further Advance"]
[null,null,"Total Monthy Household Expenditure",{"$f":"SUM(D5:D15)","v":991.47204000000011},{"$f":"SUM(E5:E15)","v":1164.3927100000001},1112.47136,1152.2024800000002,1139.1092699999999,1230.7617399999999,1181.0978399999999,1315.64186,1214.9595899999999,1103.8930500000001,1104.79603,null,null,null,"Self Build (Arrears)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Self Build (Advance)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Custom Build (Arrears)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Custom Build (Advance)"]
[null,null,null,{"$f":"48*52","v":2496},{"$f":"D20/12","v":208},null,null,null,null,null,null,null,null,null,null,null,null,"Self Build (Arrears - Large Loans £750k+) "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Self Build (Advance - Large Loans £750k+) "]
[]
[]
[]
[]
[]
[null,null,"Table A33"]
[null,null,"Household expenditure by countries and regions"]
[null,null,"UK, financial year ending 2014 to financial year ending 2016"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Household","Weighting","Council tax %","Children (1= yes)"]
[null,null,"One adult",0.72150112866817173,0.75,0]
[null,null,"Two Adults",1.2062641083521446,1,0]
[null,null,"One adult one child",0.89813769751693018,0.75,1]
[null,null,"One adult two or more children",0.92861173814898423,0.75,1]
[null,null,"Two Adults one child",1.2880925507900673,1,1]
[null,null,"Two adults two children",1.4252257336343117,1,1]
[null,null,"Two adults three or more children",1.5744920993227991,1,1]
[null,null,"Three or more adults without children",1.4272009029345372,1,0]
[null,null,"Three or more adults with children",1.4743227990970653,1,1]
[]
[]
[null,null,"Loan sizes"]
[null,null,"LTV limit","Advance Limit"]
[null,null,0.95,400000]
[null,null,0.9,500000]
[null,null,0.85,600000]
[null,null,0.8,800000]
[null,null,0.75,800000]
[null,null,0.7,1000000]
[null,null,0.65,1000000]
[null,null,0.6,1500000]

{"sheet":"Council tax","index":8,"n_rows":36,"n_cols":7,"rows_kept":36,"cut":false,"hidden":true,"merged":[]}
[]
[null,"24-25"]
["Annual","Region",null,null,null,"D"]
[null,"North East",null,null,null,2314.7399999999998]
[null,"South West",null,null,null,2298.98]
[null,"East Midlands",null,null,null,2254.79]
[null,"South East",null,null,null,2246.69]
[null,"North West",null,null,null,2239.2600000000002]
[null,"East England",null,null,null,2183.58]
[null,"Yorkshire & the Humber",null,null,null,2163]
[null,"West Midlands",null,null,null,2144.33]
[null,"LONDON",null,null,null,1894.41]
[null,"Wales",null,null,null,1997.55]
[null,"Scotland",null,null,null,1421]
[]
["Months","Region",null,null,null,"D",1]
[null,"North East",null,null,null,{"$f":"F4/12","v":192.89499999999998},2]
[null,"South West",null,null,null,{"$f":"F5/12","v":191.58166666666668},3]
[null,"East Midlands",null,null,null,187.89916666666667,4]
[null,"South East",null,null,null,187.22416666666666,5]
[null,"North West",null,null,null,186.60500000000002,6]
[null,"East England",null,null,null,181.965,7]
[null,"Yorkshire & the Humber",null,null,null,180.25,8]
[null,"West Midlands",null,null,null,178.69416666666666,9]
[null,"LONDON",null,null,null,157.86750000000001,10]
[null,"Wales",null,null,null,166.46250000000001,11]
[null,"Scotland",null,null,null,{"$f":"F14/12","v":118.41666666666667},12]
[]
[null,null,"House 1","House 2","House 3","House 4"]
[null,"Region selected",{"$f":"'Affordability Calculator'!C13","v":""},{"$f":"'Affordability Calculator'!G13","v":""},{"$f":"'Affordability Calculator'!K13","v":""},{"$f":"'Affordability Calculator'!O13","v":""}]
[null,null,{"$f":"VLOOKUP(C$30,$B$16:$G$27,6,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(D$30,$B$16:$G$27,6,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(E$30,$B$16:$G$27,6,FALSE)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(F$30,$B$16:$G$27,6,FALSE)","v":{"$error":"#N/A"}}]
[null,"Band selected","D","D","D","D"]
[null,null,{"$f":"HLOOKUP(C$32, $C$16:$F$27, C$31, FALSE)","v":{"$error":"#REF!"}},{"$f":"HLOOKUP(D$32, $C$16:$F$27, D$31, FALSE)","v":{"$error":"#REF!"}},{"$f":"HLOOKUP(E$32, $C$16:$F$27, E$31, FALSE)","v":{"$error":"#REF!"}},{"$f":"HLOOKUP(F$32, $C$16:$F$27, F$31, FALSE)","v":{"$error":"#REF!"}}]
[null,"Remortgage",{"$f":"IF(OR('Affordability Calculator'!$C$7:$G$7=\"remortgage\",'Affordability Calculator'!$C$7:$G$7=\"additional borrowing\"), 'Affordability Calculator'!C$58, 'Council tax'!C$33)","v":{"$error":"#REF!"}},{"$f":"IF(OR('Affordability Calculator'!$C$7:$G$7=\"remortgage\",'Affordability Calculator'!$C$7:$G$7=\"additional borrowing\"), 'Affordability Calculator'!G$58, 'Council tax'!D$33)","v":{"$error":"#REF!"}},{"$f":"IF(OR('Affordability Calculator'!$C$7:$G$7=\"remortgage\",'Affordability Calculator'!$C$7:$G$7=\"additional borrowing\"), 'Affordability Calculator'!K$58, 'Council tax'!E$33)","v":{"$error":"#REF!"}},{"$f":"IF(OR('Affordability Calculator'!$C$7:$G$7=\"remortgage\",'Affordability Calculator'!$C$7:$G$7=\"additional borrowing\"), 'Affordability Calculator'!O$58, 'Council tax'!F$33)","v":{"$error":"#REF!"}}]
[]
[null,null,{"$f":"MAX(C33,C34)","v":{"$error":"#REF!"}},{"$f":"MAX(D33,D34)","v":{"$error":"#REF!"}},{"$error":"#REF!"},{"$error":"#REF!"}]

{"sheet":"Postcodes","index":9,"n_rows":188,"n_cols":9,"rows_kept":188,"cut":false,"hidden":true,"merged":[]}
[]
["POSTCODE","REGION","Band D"]
["AB","Scotland"]
["AL","South East"]
["B1","West Midlands"]
["B1","West Midlands"]
["B2","West Midlands"]
["B3","West Midlands"]
["B4","West Midlands",null,null,null,null,null,"Count of REGION"]
["B5","West Midlands",null,null,null,null,null,"REGION","Total"]
["B6","West Midlands",null,null,null,null,null,"East Midlands",5]
["B7","West Midlands",null,null,null,null,null,"London",44]
["B8","West Midlands",null,null,null,null,null,"North East",5]
["B9","West Midlands",null,null,null,null,null,"North West",31]
["BA","South West",null,null,null,null,null,"Scotland",24]
["BB","North West",null,null,null,null,null,"South East",16]
["BD","Yorkshire & the Humber",null,null,null,null,null,"South West",12]
["BH","South West",null,null,null,null,null,"Wales",5]
["BL","North West",null,null,null,null,null,"West Midlands",19]
["BN","South East",null,null,null,null,null,"Yorkshire & the Humber",18]
["BR","London",null,null,null,null,null,"East",7]
["BS","South West",null,null,null,null,null,"Grand Total",186]
["CA","North West"]
["CB","East England"]
["CF","Wales"]
["CH","North West"]
["CM","East England"]
["CO","East England"]
["CR","London"]
["CT","South East"]
["CV","West Midlands"]
["CW","North West"]
["DA","South East"]
["DD","Scotland"]
["DE","East Midlands"]
["DG","Scotland"]
["DH","North East"]
["DL","North East"]
["DN","Yorkshire & the Humber"]
["DT","South West"]
["DY","West Midlands"]
["E1","London"]
["E2","London"]
["E3","London"]
["E4","London"]
["E5","London"]
["E6","London"]
["E7","London"]
["E8","London"]
["E9","London"]
["EC","London"]
["EH","Scotland"]
["EN","London"]
["EX","South West"]
["FK","Scotland"]
["FY","North West"]
["G1","Scotland"]
["G2","Scotland"]
["G3","Scotland"]
["G4","Scotland"]
["G5","Scotland"]
["G6","Scotland"]
["G7","Scotland"]
["G8","Scotland"]
["G9","Scotland"]
["GL","South West"]
["GU","London"]
["HA","London"]
["HD","Yorkshire & the Humber"]
["HG","Yorkshire & the Humber"]
["HP","South East"]
["HR","West Midlands"]
["HS","Scotland",null,null,null,null,null,"http://www.thegrowersshop.co.uk/delivery-terms"]
["HU","Yorkshire & the Humber"]
["HX","Yorkshire & the Humber"]
["IG","London"]
["IP","East England"]
["IV","Scotland"]
["KA","Scotland"]
["KT","London"]
["KW","Scotland"]
["KY","Scotland"]
["L1","North West"]
["L2","North West"]
["L3","North West"]
["L4","North West"]
["L5","North West"]
["L6","North West"]
["L7","North West"]
["L8","North West"]
["L9","North West"]
["LA","North West"]
["LD","Wales"]
["LE","East Midlands"]
["LL","Wales"]
["LN","East Midlands"]
["LS","Yorkshire & the Humber"]
["LU","South East"]
["M","North West"]
["M1","North West"]
["M2","North West"]
["M3","North West"]
["M4","North West"]
["M5","North West"]
["M6","North West"]
["M7","North West"]
["M8","North West"]
["M9","North West"]
["ME","South East"]
["MK","South East"]
["ML","Scotland"]
["N1","London"]
["N2","London"]
["N3","London"]
["N4","London"]
["N5","London"]
["N6","London"]
["N7","London"]
["N8","London"]
["N9","London"]
["NE","North East"]
["NG","East Midlands"]
["NN","East Midlands"]
["NP","Wales"]
["NR","East England"]
["NW","London"]
["OL","North West"]
["OX","South East"]
["PA","Scotland"]
["PE","East England"]
["PH","Scotland"]
["PL","South West"]
["PO","South East"]
["PR","North West"]
["RG","South East"]
["RH","South East"]
["RM","London"]
["S1","Yorkshire & the Humber"]
["S2","Yorkshire & the Humber"]
["S3","Yorkshire & the Humber"]
["S4","Yorkshire & the Humber"]
["S5","Yorkshire & the Humber"]
["S6","Yorkshire & the Humber"]
["S7","Yorkshire & the Humber"]
["S8","Yorkshire & the Humber"]
["S9","Yorkshire & the Humber"]
["SA","Wales"]
["SE","London"]
["SG","South East"]
["SK","North West"]
["SL","South East"]
["SM","London"]
["SN","South West"]
["SO","South East"]
["SP","South West"]
["SR","North East"]
["SS","East England"]
["ST","West Midlands"]
["SW","London"]
["SY","West Midlands"]
["TA","South West"]
["TD","Scotland"]
["TF","West Midlands"]
["TN","South East"]
["TQ","South West"]
["TR","South West"]
["TS","North East"]
["TW","London"]
["UB","London"]
["W1","London"]
["W2","London"]
["W3","London"]
["W4","London"]
["W5","London"]
["W6","London"]
["W7","London"]
["W8","London"]
["W9","London"]
["WA","North West"]
["WC","London"]
["WD","London"]
["WF","Yorkshire & the Humber"]
["WN","North West"]
["WR","West Midlands"]
["WS","West Midlands"]
["WV","West Midlands"]
["YO","Yorkshire & the Humber"]
["ZE","Scotland"]

{"sheet":"E Calc","index":10,"n_rows":84,"n_cols":13,"rows_kept":84,"cut":false,"hidden":true,"merged":[[87,14,88,14],[132,2,136,8],[1,2,1,5],[68,1,71,8],[87,13,88,13]]}
[]
[null,null,"Affordability Calculator"]
[]
[null,"Loan requirements"]
[null,null,"Mortgage required",{"$f":"Enquiry!E36","v":0}]
[]
[null,null,"Mortgage Term in Years",{"$f":"Enquiry!R22","v":0}]
[]
[null,null,"Repayment type (select from list)",{"$f":"IF(Enquiry!E36=Enquiry!E37,\"Capital Repayment\",IF(Enquiry!E36=Enquiry!K37,\"Interest Only\",IF(Enquiry!E36=Enquiry!E37+Enquiry!K37,\"Part capital repayment/Part interest only\",\"\")))","v":"Capital Repayment"}]
[]
[null,null,"Capital Repayment Amount",{"$f":"Enquiry!E37","v":0}]
[]
[null,null,"Interest Only Amount",{"$f":"Enquiry!K37","v":0}]
[]
[null,null,"Product length (select from list)",{"$f":"Enquiry!K38","v":0}]
[]
[null,null,"Product pay rate",{"$f":"Enquiry!T38","v":0}]
[]
[null,"Income",null,"Applicant 1",null,"Applicant 2","Applicant 3","Applicant 4",null,"area for calculation"]
[]
[null,"Annual","Basic Gross Salary",{"$f":"Enquiry!E45","v":0},null,{"$f":"Enquiry!E48","v":0},{"$f":"+Enquiry!E51","v":0},{"$f":"+Enquiry!E54","v":0}]
[]
[null,"Annual","Car, Shift & Large Town Allowance",{"$f":"Enquiry!R45","v":0},null,{"$f":"Enquiry!R48","v":0},{"$f":"+Enquiry!R51","v":0},{"$f":"+Enquiry!R54","v":0}]
[]
[null,"Annual","Acceptable other - Employed",{"$f":"Enquiry!$I$46","v":0},null,{"$f":"Enquiry!$I$49","v":0},{"$f":"Enquiry!$I$52","v":0},{"$f":"Enquiry!$I$55","v":0},null,null,null,"LTI"]
[]
[null,null,"Sole Trader/Partnership Net Profit",{"$f":"Enquiry!U59","v":0},null,{"$f":"Enquiry!U62","v":0},{"$f":"+Enquiry!U65","v":0},{"$f":"+Enquiry!U68","v":0},null,"APP1",{"$f":"(D21+D23+(D25)+D27+D29+D31+D33+D35)-((D41+D47+(D43*3%))*12)","v":0},{"$f":"Enquiry!$F$83","v":0},{"$f":"L27*K27","v":0}]
[]
[null,null,"LTD Company Directors Remuneration",null,null,null,null,null,null,"APP2",{"$f":"(F21+F23+(F25)+F27+F29+F31+F33+F35)-((F41+F47+(F43*3%))*12)","v":0},{"$f":"Enquiry!$K$83","v":0},{"$f":"L29*K29","v":0}]
[]
[null,null,"LTD Company Gross Dividend + land prop",{"$f":"Enquiry!U60+Enquiry!U61","v":0},null,{"$f":"Enquiry!U63+Enquiry!U64","v":0},{"$f":"+Enquiry!U66+Enquiry!U67","v":0},{"$f":"+Enquiry!U69+Enquiry!U70","v":0},null,"APP3",{"$f":"(G21+G23+(G25)+G27+G29+G31+G33+G35)-((G41+G47+(G43*3%))*12)","v":0},{"$f":"Enquiry!$P$83","v":0},{"$f":"L31*K31","v":0}]
[]
[null,null,"Retired -Annual Gross Pension",null,null,null,null,null,null,"APP4",{"$f":"(H21+H23+(H25)+H27+H29+H31+H33+H35)-((H41+H47+(H43*3%))*12)","v":0},{"$f":"Enquiry!$U$83","v":0},{"$f":"L33*K33","v":0}]
[]
[null,null,"Acceptable other income",{"$f":"Enquiry!$E$74","v":0},null,{"$f":"Enquiry!$E$75","v":0},{"$f":"Enquiry!$E$76","v":0},{"$f":"Enquiry!$E$77","v":0}]
[]
[null,null,"Verified (monthly) Net Income",{"$f":"Enquiry!F80","v":0},null,{"$f":"Enquiry!K80","v":0},{"$f":"+Enquiry!P80","v":0},{"$f":"+Enquiry!U80","v":0}]
[]
[null,"Expenditure"]
[]
[null,"done at 50%","Monthly Loan/HP/Lease Payments",{"$f":"Enquiry!G90","v":0},null,{"$f":"Enquiry!G96","v":0},{"$f":"Enquiry!G102","v":0},{"$f":"Enquiry!G108","v":0},null,"Income for multiple",{"$f":"SUM(K27:K33)","v":0}]
[]
[null,"TOTAL overdraft, store & credit card balances outstanding  ",null,{"$f":"Enquiry!S89","v":0},null,{"$f":"Enquiry!S95","v":0},{"$f":"Enquiry!S101","v":0},{"$f":"Enquiry!S107","v":0}]
[]
[null,null,"Child Costs",{"$f":"Enquiry!G91","v":0},null,{"$f":"Enquiry!G97","v":0},{"$f":"Enquiry!G103","v":0},{"$f":"Enquiry!G109","v":0}]
[]
[null,null,"Monthly payment of other mortgages",{"$f":"Enquiry!S92","v":0},null,{"$f":"Enquiry!S98","v":0},{"$f":"Enquiry!S104","v":0},{"$f":"Enquiry!S110","v":0}]
[]
[null,null,"Student Loans"]
[]
[null,null,"Pension"]
[]
[null,null,"Ground rent/Service charge",{"$f":"Enquiry!S91","v":0},null,{"$f":"Enquiry!S97","v":0},{"$f":"Enquiry!S103","v":0},{"$f":"Enquiry!S109","v":0}]
[]
[null,null,"Other Expenditure ",{"$f":"Enquiry!G92","v":0},null,{"$f":"Enquiry!G98","v":0},{"$f":"Enquiry!G104","v":0},{"$f":"Enquiry!G110","v":0}]
[]
[]
[]
[]
[null,null,"MAXIMUM LOAN",{"$f":"INDEX($K$27:$K$33,MATCH(LARGE($M$27:$M$33,3),$M$27:$M$33,0))+INDEX($K$27:$K$33,MATCH(LARGE($M$27:$M$33,4),$M$27:$M$33,0))+(LARGE($M$27:$M$33,1)+LARGE($M$27:$M$33,2))","v":0}]
[]
[null,null,"Stressed interest rate",{"$f":"'Mortgage Repayments (E)'!J15","v":{"$error":"#N/A"}}]
[null,null,"Net free pay stressed",{"$f":"($D$37+$F$37+G37+H37)-((D66+((D43+F43+G43+H43)*3%)+(D41+D45+D47+D49+D51+D53+D55+F41+F45+F47+F49+F51+F53+F55+D65+G41+G45+G47+G49+G51+G53+G55+H41+H45+H47+H49+H51+H53+H55)))","v":{"$error":"#N/A"}},null,{"$f":"IF(D63>=((D37+F37+G37+H37)*5%), \"Pass\", \"Decline\")","v":{"$error":"#N/A"}}]
[null,null,"% net stressed free pay",{"$f":"D63/(D37+F37+G37+H37)","v":{"$error":"#N/A"}}]
[null,null,"Mortgage payment",{"$f":"'Mortgage Repayments (E)'!J4","v":{"$error":"#N/A"}}]
[null,null,"ONS for area",{"$f":"Enquiry!$AA$34","v":0}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,1]
[null,2]
[null,3]

{"sheet":"Tax Calculator","index":11,"n_rows":38,"n_cols":20,"rows_kept":38,"cut":false,"hidden":true,"merged":[[1,1,1,3],[1,6,1,8],[2,11,2,13]]}
[]
[null,"Income Tax (England)",null,null,null,null,"Income Tax (Scotland)"]
[null,"Band","Upper Limit","Rate",null,null,"Band","Upper Limit","Rate",null,null,"National Insurance"]
[null,"Personal Allowance",12570,0,null,null,"Personal Allowance",12570,0,null,null,"Band","Upper Limit","Rate"]
[null,"Basic Rate",50270,0.2,null,null,"Starter rate",15397,0.19,null,null,"Primary threshold",12570,0]
[null,"Higher Rate",125140,0.4,null,null,"Basic rate",27491,0.2,null,null,"Upper earnings limit",50270,0.08]
[null,"Additional Rate",null,0.45,null,null,"Intermediate rate",43662,0.21,null,null,"Additional Rate",null,0.02]
[null,null,null,null,null,null,"Higher rate",75000,0.42]
[null,null,null,null,null,null,"Advanced rate",125140,0.45]
[null,null,null,null,null,null,"Top rate",null,0.48]
[]
[null,null,null,null,"Applicant 1",null,null,null,null,"Applicant 2",null,null,null,null,"Applicant 3",null,null,null,null,"Applicant 4"]
[null,null,null,"Gross Annual Income (After Deductions)",{"$f":"Enquiry!X47+Enquiry!U46","v":0},null,null,null,"Gross Annual Income (After Deductions)",{"$f":"Enquiry!X49+Enquiry!U49","v":0},null,null,null,"Gross Annual Income (After Deductions)",{"$f":"Enquiry!X51+Enquiry!U52","v":0},null,null,null,"Gross Annual Income (After Deductions)",{"$f":"Enquiry!X53+Enquiry!U55","v":0}]
[null,null,null,"Before Deductions",{"$f":"E13+Enquiry!R46","v":0},null,null,null,"Before Deductions",{"$f":"J13+Enquiry!R49","v":0},null,null,null,"Before Deductions",{"$f":"O13+Enquiry!R52","v":0},null,null,null,"Before Deductions",{"$f":"T13+Enquiry!R55","v":0}]
[]
[null,"Bands (England)","Upper Limit","Rate","Tax Amount",null,"Bands (England)","Upper Limit","Rate","Tax Amount",null,"Bands (England)","Upper Limit","Rate","Tax Amount",null,"Bands (England)","Upper Limit","Rate","Tax Amount"]
[null,"Personal Allowance",{"$f":"IF(E13>100000,IF(E13<$C$6,$C$4-(E13-100000)/2,0),$C$4)","v":12570},{"$f":"$D$4","v":0},{"$f":"MIN(E$13,C17)*-D17","v":0},null,"Personal Allowance",{"$f":"IF(J13>100000,IF(J13<$C$6,$C$4-(J13-100000)/2,0),$C$4)","v":12570},{"$f":"$D$4","v":0},{"$f":"MIN(J$13,H17)*-I17","v":0},null,"Personal Allowance",{"$f":"IF(O13>100000,IF(O13<$C$6,$C$4-(O13-100000)/2,0),$C$4)","v":12570},{"$f":"$D$4","v":0},{"$f":"MIN(O$13,M17)*-N17","v":0},null,"Personal Allowance",{"$f":"IF(T13>100000,IF(T13<$C$6,$C$4-(T13-100000)/2,0),$C$4)","v":12570},{"$f":"$D$4","v":0},{"$f":"MIN(T$13,R17)*-S17","v":0}]
[null,"Basic Rate",{"$f":"$C$5-$C$4+C$17","v":50270},{"$f":"$D$5","v":0.2},{"$f":"MAX(0,MIN(E$13,C18)-C17)*-D18","v":0},null,"Basic Rate",{"$f":"$C$5-$C$4+H$17","v":50270},{"$f":"$D$5","v":0.2},{"$f":"MAX(0,MIN(J$13,H18)-H17)*-I18","v":0},null,"Basic Rate",{"$f":"$C$5-$C$4+M$17","v":50270},{"$f":"$D$5","v":0.2},{"$f":"MAX(0,MIN(O$13,M18)-M17)*-N18","v":0},null,"Basic Rate",{"$f":"$C$5-$C$4+R$17","v":50270},{"$f":"$D$5","v":0.2},{"$f":"MAX(0,MIN(T$13,R18)-R17)*-S18","v":0}]
[null,"Higher Rate",{"$f":"$C$6","v":125140},{"$f":"$D$6","v":0.4},{"$f":"MAX(0,MIN(E$13,C19)-C18)*-D19","v":0},null,"Higher Rate",{"$f":"$C$6","v":125140},{"$f":"$D$6","v":0.4},{"$f":"MAX(0,MIN(J$13,H19)-H18)*-I19","v":0},null,"Higher Rate",{"$f":"$C$6","v":125140},{"$f":"$D$6","v":0.4},{"$f":"MAX(0,MIN(O$13,M19)-M18)*-N19","v":0},null,"Higher Rate",{"$f":"$C$6","v":125140},{"$f":"$D$6","v":0.4},{"$f":"MAX(0,MIN(T$13,R19)-R18)*-S19","v":0}]
[null,"Additional Rate",null,{"$f":"$D$7","v":0.45},{"$f":"MAX(0,E$13-C19)*-D20","v":0},null,"Additional Rate",null,{"$f":"$D$7","v":0.45},{"$f":"MAX(0,J$13-H19)*-I20","v":0},null,"Additional Rate",null,{"$f":"$D$7","v":0.45},{"$f":"MAX(0,O$13-M19)*-N20","v":0},null,"Additional Rate",null,{"$f":"$D$7","v":0.45},{"$f":"MAX(0,T$13-R19)*-S20","v":0}]
[null,null,null,null,{"$f":"SUM(E17:E20)","v":0},null,null,null,null,{"$f":"SUM(J17:J20)","v":0},null,null,null,null,{"$f":"SUM(O17:O20)","v":0},null,null,null,null,{"$f":"SUM(T17:T20)","v":0}]
[null,"Bands (Scotland)",null,null,null,null,"Bands (Scotland)",null,null,null,null,"Bands (Scotland)",null,null,null,null,"Bands (Scotland)"]
[null,"Personal Allowance",{"$f":"C17","v":12570},{"$f":"$I$4","v":0},{"$f":"MIN(E$13,C23)*-D23","v":0},null,"Personal Allowance",{"$f":"H17","v":12570},{"$f":"$I$4","v":0},{"$f":"MIN(J$13,H23)*-I23","v":0},null,"Personal Allowance",{"$f":"M17","v":12570},{"$f":"$I$4","v":0},{"$f":"MIN(O$13,M23)*-N23","v":0},null,"Personal Allowance",{"$f":"R17","v":12570},{"$f":"$I$4","v":0},{"$f":"MIN(T$13,R23)*-S23","v":0}]
[null,"Starter rate",{"$f":"$H5-$H$4+C$23","v":15397},{"$f":"$I$5","v":0.19},{"$f":"MAX(0,MIN(E$13,C24)-C23)*-D24","v":0},null,"Starter rate",{"$f":"$H5-$H$4+H$23","v":15397},{"$f":"$I$5","v":0.19},{"$f":"MAX(0,MIN(J$13,H24)-H23)*-I24","v":0},null,"Starter rate",{"$f":"$H5-$H$4+M$23","v":15397},{"$f":"$I$5","v":0.19},{"$f":"MAX(0,MIN(O$13,M24)-M23)*-N24","v":0},null,"Starter rate",{"$f":"$H5-$H$4+R$23","v":15397},{"$f":"$I$5","v":0.19},{"$f":"MAX(0,MIN(T$13,R24)-R23)*-S24","v":0}]
[null,"Basic rate",{"$f":"$H6-$H$4+C$23","v":27491},{"$f":"$I$6","v":0.2},{"$f":"MAX(0,MIN(E$13,C25)-C24)*-D25","v":0},null,"Basic rate",{"$f":"$H6-$H$4+H$23","v":27491},{"$f":"$I$6","v":0.2},{"$f":"MAX(0,MIN(J$13,H25)-H24)*-I25","v":0},null,"Basic rate",{"$f":"$H6-$H$4+M$23","v":27491},{"$f":"$I$6","v":0.2},{"$f":"MAX(0,MIN(O$13,M25)-M24)*-N25","v":0},null,"Basic rate",{"$f":"$H6-$H$4+R$23","v":27491},{"$f":"$I$6","v":0.2},{"$f":"MAX(0,MIN(T$13,R25)-R24)*-S25","v":0}]
[null,"Intermediate rate",43662,{"$f":"$I$7","v":0.21},{"$f":"MAX(0,MIN(E$13,C26)-C25)*-D26","v":0},null,"Intermediate rate",43662,{"$f":"$I$7","v":0.21},{"$f":"MAX(0,MIN(J$13,H26)-H25)*-I26","v":0},null,"Intermediate rate",43662,{"$f":"$I$7","v":0.21},{"$f":"MAX(0,MIN(O$13,M26)-M25)*-N26","v":0},null,"Intermediate rate",43662,{"$f":"$I$7","v":0.21},{"$f":"MAX(0,MIN(T$13,R26)-R25)*-S26","v":0}]
[null,"Higher rate",75000,{"$f":"$I$8","v":0.42},0,null,"Higher rate",75000,{"$f":"$I$8","v":0.42},{"$f":"MAX(0,MIN(J$13,H27)-H26)*-I27","v":0},null,"Higher rate",75000,{"$f":"$I$8","v":0.42},{"$f":"MAX(0,MIN(O$13,M27)-M26)*-N27","v":0},null,"Higher rate",75000,{"$f":"$I$8","v":0.42},{"$f":"MAX(0,MIN(T$13,R27)-R26)*-S27","v":0}]
[null,"Advanced rate",{"$f":"$H$9","v":125140},{"$f":"$I$9","v":0.45},{"$f":"MAX(0,MIN(E$13,C28)-C27)*-D28","v":0},null,"Advanced rate",{"$f":"$H$9","v":125140},{"$f":"$I$9","v":0.45},{"$f":"MAX(0,MIN(J$13,H28)-H27)*-I28","v":0},null,"Advanced rate",{"$f":"$H$9","v":125140},{"$f":"$I$9","v":0.45},{"$f":"MAX(0,MIN(O$13,M28)-M27)*-N28","v":0},null,"Advanced rate",{"$f":"$H$9","v":125140},{"$f":"$I$9","v":0.45},{"$f":"MAX(0,MIN(T$13,R28)-R27)*-S28","v":0}]
[null,"Top rate",null,{"$f":"$I$10","v":0.48},{"$f":"MAX(0,E$13-C28)*-D29","v":0},null,"Top rate",null,{"$f":"$I$10","v":0.48},{"$f":"MAX(0,J$13-H28)*-I29","v":0},null,"Top rate",null,{"$f":"$I$10","v":0.48},{"$f":"MAX(0,O$13-M28)*-N29","v":0},null,"Top rate",null,{"$f":"$I$10","v":0.48},{"$f":"MAX(0,T$13-R28)*-S29","v":0}]
[null,null,null,null,{"$f":"SUM(E23:E29)","v":0},null,null,null,null,{"$f":"SUM(J23:J29)","v":0},null,null,null,null,{"$f":"SUM(O23:O29)","v":0},null,null,null,null,{"$f":"SUM(T23:T29)","v":0}]
[]
[null,"Primary threshold",{"$f":"$M$5","v":12570},{"$f":"$N$5","v":0},{"$f":"MIN(E$14,C32)*-D32","v":0},null,"Primary threshold",{"$f":"$M$5","v":12570},{"$f":"$N$5","v":0},{"$f":"MIN(J$14,H32)*-I32","v":0},null,"Primary threshold",{"$f":"$M$5","v":12570},{"$f":"$N$5","v":0},{"$f":"MIN(O$14,M32)*-N32","v":0},null,"Primary threshold",{"$f":"$M$5","v":12570},{"$f":"$N$5","v":0},{"$f":"MIN(T$14,R32)*-S32","v":0}]
[null,"Upper earnings limit",{"$f":"$M$6","v":50270},{"$f":"$N$6","v":0.08},{"$f":"MAX(0,MIN(E$14,C33)-C32)*-D33","v":0},null,"Upper earnings limit",{"$f":"$M$6","v":50270},{"$f":"$N$6","v":0.08},{"$f":"MAX(0,MIN(J$14,H33)-H32)*-I33","v":0},null,"Upper earnings limit",{"$f":"$M$6","v":50270},{"$f":"$N$6","v":0.08},{"$f":"MAX(0,MIN(O$14,M33)-M32)*-N33","v":0},null,"Upper earnings limit",{"$f":"$M$6","v":50270},{"$f":"$N$6","v":0.08},{"$f":"MAX(0,MIN(T$14,R33)-R32)*-S33","v":0}]
[null,"Additional Rate",null,{"$f":"$N$7","v":0.02},{"$f":"MAX(0,E$14-C33)*-D34","v":0},null,"Additional Rate",null,{"$f":"$N$7","v":0.02},{"$f":"MAX(0,J$14-H33)*-I34","v":0},null,"Additional Rate",null,{"$f":"$N$7","v":0.02},{"$f":"MAX(0,O$14-M33)*-N34","v":0},null,"Additional Rate",null,{"$f":"$N$7","v":0.02},{"$f":"MAX(0,T$14-R33)*-S34","v":0}]
[null,null,null,null,{"$f":"SUM(E32:E34)","v":0},null,null,null,null,{"$f":"SUM(J32:J34)","v":0},null,null,null,null,{"$f":"SUM(O32:O34)","v":0},null,null,null,null,{"$f":"SUM(T32:T34)","v":0}]
[]
[null,"Location:",null,null,{"$f":"IF(Enquiry!$C$45=\"Yes\",\"Scotland\",\"England\")","v":"England"},null,"Location:",null,null,{"$f":"IF(Enquiry!$C$48=\"Yes\",\"Scotland\",\"England\")","v":"England"},null,"Location:",null,null,{"$f":"IF(Enquiry!$C$51=\"Yes\",\"Scotland\",\"England\")","v":"England"},null,"Location:",null,null,{"$f":"IF(Enquiry!$C$54=\"Yes\",\"Scotland\",\"England\")","v":"England"}]
[null,"Monthly Income:",null,null,{"$f":"IF(OR(ISBLANK(E13),ISBLANK(E14)),0,(E13+E35+IF(E37=\"England\",E21,E30))/12)","v":0},null,"Monthly Income:",null,null,{"$f":"IF(OR(ISBLANK(J13),ISBLANK(J14)),0,(J13+J35+IF(J37=\"England\",J21,J30))/12)","v":0},null,"Monthly Income:",null,null,{"$f":"IF(OR(ISBLANK(O13),ISBLANK(O14)),0,(O13+O35+IF(O37=\"England\",O21,O30))/12)","v":0},null,"Monthly Income:",null,null,{"$f":"IF(OR(ISBLANK(T13),ISBLANK(T14)),0,(T13+T35+IF(T37=\"England\",T21,T30))/12)","v":0}]