{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Broker ICR","ICR v4","Tax","1 max","P Max","Holiday Let","Admin","Version"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":4,"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":"Broker ICR","index":0,"n_rows":63,"n_cols":14,"rows_kept":63,"cut":false,"hidden":false,"merged":[[17,12,18,13],[1,3,1,8],[7,7,7,8],[11,7,11,8],[13,7,13,8],[3,7,3,8],[18,3,18,4],[15,7,15,8],[16,6,16,8],[17,2,17,3],[1,12,1,13],[5,7,5,8],[18,6,18,8],[5,12,5,13],[3,12,3,13],[19,3,19,8],[62,3,62,7],[64,2,64,3],[33,5,33,8],[3,4,3,6],[27,7,27,8],[27,2,27,6],[36,7,36,8],[53,7,53,8],[45,7,45,8],[29,7,29,8],[47,7,47,8],[31,7,31,8],[9,7,9,8],[34,12,34,13],[43,3,43,6],[63,3,63,7],[54,2,54,8],[38,7,42,8],[61,3,61,7],[49,3,49,4]]}
[]
[null,null,null,"Vernon Building Society - BTL ICR Calculator\nPlease scroll to the bottom of the form to ensure all fields are completed\nThe calculator should be used in Excel 365 or >2016",null,null,null,null,null,null,null,null,{"$f":"IF(ISERROR(IF('ICR v4'!E23=0,\"\",IF(H21=\"Yes\",\"Pass - ICR 130% or higher\",\"\"))),\"\",IF('ICR v4'!E23=0,\"\",IF(H21=\"Yes\",\"Pass - ICR 130% or higher\",\"\")))","v":""}]
[]
[null,null,null,"Applicant(s) Name"]
[]
[null,null,null,"BTL Type",null,null,null,{"$f":"IF(OR(E8=\"\",H8=\"\"),\"\",IF(H12>0.75,\"LTV too high - max 75%\",\"\"))","v":""}]
[]
[null,null,null,"Mortgage Requested £",null,null,"Property Value"]
[]
[null,null,null,"Product Type",null,null,"Product Rate %",null,null,null,null,null,"Calculated ICR",{"$f":"IF(ISERROR(IF('ICR v4'!E23=0,\"\",IF(ISERROR(IF(H25=0,\"Data Required\",'ICR v4'!E53)),\"\",IF(H25=0,\"Data Required\",'ICR v4'!E53)))),\"\",IF('ICR v4'!E23=0,\"\",IF(ISERROR(IF(H25=0,\"Data Required\",'ICR v4'!E53)),\"\",IF(H25=0,\"Data Required\",'ICR v4'!E53))))","v":""}]
[]
[null,null,null,"Product Fee to be added",null,null,"Loan to Value",{"$f":"IFERROR(E8/H8,\"\")","v":""},null,null,null,null,"Portfolio ICR",{"$f":"IF(N10=\"\",\"\",\nIF(H25=0,\"Data Required\",\nIF(AND(H16>1,F58=\"\"),N10,\nIFERROR(IF(E24=\"No\",\"N/A\",'ICR v4'!Q53),\"Error\"))))","v":""}]
[]
[null,null,null,"Gross Annual Rental Income",null,null,"Agent Letting % Costs (inc VAT)",null,null,null,null,null,"Without Top-Slicing"]
[]
[null,null,null,"Transaction Type",null,null,"No. of properties in portfolio",1,null,null,null,null,"Maximum Loan",{"$f":"IF(E8=\"\",\"\",IF(H16=1,'ICR v4'!E57,MIN('ICR v4'!E57,'ICR v4'!E59)))","v":""}]
[null,null,null,null,null,null,"(Including current application)"]
[null,null,"Stressed Rate",null,{"$f":"'ICR v4'!E17","v":6.2E-2},null,null,null,null,null,null,null,{"$f":"IF(ISERROR(IF(N10=\"\",\"\",IF(H25=0,\"Data Missing\",IF(N28=0,\"Pass\",\"If top slicing Required - please contact intermediary team\")))),\"\",IF(N10=\"\",\"\",IF(H25=0,\"Data Missing\",IF(N28=0,\"Pass\",\"If top slicing Required - please contact intermediary sales team\"))))","v":""}]
[null,null,null,{"$f":"IF(H16=\"\",\"\",IF(ISERROR(IF(AND(E26=\"Yes\",E43=0),\"Please complete Holiday Let Rental Income (below)\",\"\")),\"\",IF(AND(E26=\"Yes\",E43=0),\"Please complete Holiday Let Rental Income (below)\",\"\")))","v":""},null,null,{"$f":"IF(AND(H16>1,H24<>\"Ok\"),\"Please provide details of all portfolio properties (below)\",\"\")","v":""}]
[null,null,null,"Internal Checks"]
[null,null,null,"Data Integrity",{"$f":"IF(OR(E8=\"\",H8=\"\",E12=\"\",H12=\"\",E6=\"\",H14=\"\",H16=\"\",E16=\"\",H24=\"Data Required\"),1,0)","v":1},null,"ICR above 130%",{"$f":"IF(ISERROR(IF(AND(N10>=130%,N12>=130%),\"Yes\",\"No\")),\"Yes\",IF(AND(N10>=130%,N12>=130%),\"Yes\",\"No\"))","v":"Yes"}]
[null,null,null,"Costs Required",{"$f":"VLOOKUP(E6,Admin!A:B,2,FALSE)","v":0.6},null,"Costs Integrity",{"$f":"IF(AND(E22=\"Yes\",OR(E30=\"\",H30=\"\",E32=\"\",H32=\"\")),\"Error\",\"Ok\")","v":"Ok"}]
[null,null,null,"Personal BTL",{"$f":"VLOOKUP(E6,Admin!A:C,3,FALSE)","v":"Less than 60%"},null,"Personal BTL Integrity",{"$f":"IF(AND(E23=\"Yes\",E48=\"\"),\"Error\",\"Ok\")","v":"Ok"}]
[null,null,null,"Portfolio",{"$f":"IF(OR(H16=\"\",H16>1),\"Yes\",\"No\")","v":"No"},null,"Portfolio Integrity",{"$f":"H54","v":"NOT APPLICABLE"}]
[null,null,null,"HMO",{"$f":"VLOOKUP(E6,Admin!A:D,4,FALSE)","v":0},null,"Overall Integrity",{"$f":"IF(OR(H22=\"Error\",H23=\"Error\",H24=\"Error\",E21=1),0,\"Ok\")","v":0}]
[null,null,null,"Holiday BTL",{"$f":"IF(E6=\"-\",\"\",VLOOKUP(E6,Admin!A:E,5,FALSE))","v":0},{"$f":"IF(AND(E25=\"No\",E26=\"No\"),1,0)","v":0},"Limited Company",{"$f":"VLOOKUP(E6,Admin!A:F,6,FALSE)","v":0}]
[]
[null,null,"Estimated HMO / Holiday Let Costs",null,null,null,null,{"$f":"IFERROR(IF(F26=1,\"NOT APPLICABLE\",\"\"),\"\")","v":""},null,null,null,null,"Top Slicing Requirement\n(Per Month)",{"$f":"IF(E8=\"\",\"\",IF(ISERROR(IF(MAX('ICR v4'!E55,'ICR v4'!Q55)=0,0,MAX('ICR v4'!E55,'ICR v4'!Q55))),\"\",IF(MAX('ICR v4'!E55,'ICR v4'!Q55)=0,0,MAX('ICR v4'!E55,'ICR v4'!Q55))))","v":""}]
[]
[null,null,null,"Cost of Cleaning (per week)",null,null,"Utility Bills (Per Month)\n(Gas, Elec, Water)"]
[]
[null,null,null,"Council Tax / Business Rates\n(Per Month)",null,null,"Other Costs (Per Month)\n(eg, Media/Internet Costs)"]
[]
[null,null,null,"Gardening Requirements",null,{"$f":"IF(ISERROR(IF(ISBLANK(VLOOKUP(E34,Admin!P:S,4,FALSE)),\"\",VLOOKUP(E34,Admin!P:S,4,FALSE))),\"\",IF(ISBLANK(VLOOKUP(E34,Admin!P:S,4,FALSE)),\"\",VLOOKUP(E34,Admin!P:S,4,FALSE)))","v":""}]
[]
[]
[null,null,"Holiday Let Rental Income",null,null,null,null,{"$f":"IFERROR(IF(E26=\"No\",\"NOT APPLICABLE\",\"\"),\"\")","v":""}]
[]
[null,null,null,"Rental Demand Period","Weekly Rental Price","No. of Weeks","Occupancy Rate",{"$f":"IF(M44>0,\"Please refer to the Intermediary Sales Team\",\"\")","v":""}]
[null,null,null,"High",null,16,0.9]
[null,null,null,"Medium ",null,12,0.8]
[null,null,null,"Low",null,24,0.4]
[null,null,null,"Annual Rental Income",{"$f":"'Holiday Let'!D9","v":0},{"$f":"SUM(F40:F42)","v":52}]
[null,null,null,"* Letting agents letter required to substantiate (i) the rental income levels used and (ii) the expected occupancy rates at levels at or above those detailed above. \n\nPlease refer to the Intermediary Sales Team if forecast occupancy levels are lower than the above thresholds"]
[]
[null,null,"Personal BTL Only",null,null,null,null,{"$f":"IFERROR(IF(E23=\"No\",\"NOT APPLICABLE\",\"\"),\"\")","v":""}]
[]
[null,null,null,"Applicant 1 Personal Income Tax Band",null,null,"Applicant 2 Personal Income Tax Band"]
[]
[null,null,null,"Number of applicants assessed",null,{"$f":"IF(E48=\"\",\"\",IF(H48=\"\",1,IF(H48<>\"\",2,\"\")))","v":""}]
[]
[]
[]
[null,null,"Property Portfolio \n",null,null,null,null,{"$f":"IF(H16=1,\"NOT APPLICABLE\",IF(H16>=1,\"\",IF(H16>=5,\"Please complete BTL Portfolio Form\",\"\")))","v":"NOT APPLICABLE"}]
[]
[]
[null,null,"#","Property Value","Mortgage Balance","Mortgage Payment PM","Annual Rent","HMO / Holiday Let?","Int Rate"]
[null,null,1,null,null,null,null,null,{"$f":"IF(E58=\"\",\"\",(F58/E58)*12)","v":""},null,null,null,{"$f":"IF(AND($H$16>1,OR(D58=\"\",E58=\"\",F58=\"\",G58=\"\",H58=\"\")),1,0)","v":0}]
[null,null,2,null,null,null,null,null,{"$f":"IF(E59=\"\",\"\",(F59/E59)*12)","v":""},null,null,null,{"$f":"IF(AND($H$16>2,OR(D59=\"\",E59=\"\",F59=\"\",G59=\"\",H59=\"\")),1,0)","v":0}]
[null,null,3,null,null,null,null,null,"",null,null,null,{"$f":"IF(AND($H$16>3,OR(D60=\"\",E60=\"\",F60=\"\",G60=\"\",H60=\"\")),1,0)","v":0}]
[null,null,4,null,null,null,null,null,"",null,null,null,{"$f":"IF(AND($H$16>4,OR(D61=\"\",E61=\"\",F61=\"\",G61=\"\",H61=\"\")),1,0)","v":0}]
[null,null,"*","Portfolio's with over 4 properties assessed seperately - please provide property schedule"]
[null,null,"*","Please only include properties owned in the same name as the applicant(s) or Limited Company",null,null,null,null,null,null,null,null,{"$f":"SUM(M58:M61)","v":0}]

{"sheet":"ICR v4","index":1,"n_rows":59,"n_cols":19,"rows_kept":59,"cut":false,"hidden":true,"merged":[[1,2,1,16],[34,2,34,16],[6,3,6,8],[6,12,6,16],[4,3,4,8],[4,12,4,16]]}
[]
[null,null,"Interest Coverage Ratio Calculator - Version 4 - effective from July 2020\n(Please only enter date for WHITE cells only)"]
[]
[]
[null,null,"Customer:",{"$f":"IF('Broker ICR'!E4=\"\",\"\",'Broker ICR'!E4)","v":""},null,null,null,null,null,null,"Date:"]
[]
[null,null,"App No:",null,null,null,null,null,null,null,"Completed by:"]
[]
[null,null,null,null,"New",null,"Exisiting 1",null,"Exisiting 2",null,"Exisiting 3",null,"Exisiting 4",null,"Exisiting 5",null,"Portfolio Total"]
[null,null,"Mortgage Requested",null,{"$f":"'Broker ICR'!E8+'Broker ICR'!E12","v":0},null,{"$f":"IF('Broker ICR'!E58=\"\",\"\",'Broker ICR'!E58)","v":""},null,{"$f":"IF('Broker ICR'!E59=\"\",\"\",'Broker ICR'!E59)","v":""},null,{"$f":"IF('Broker ICR'!E60=\"\",\"\",'Broker ICR'!E60)","v":""},null,{"$f":"IF('Broker ICR'!E61=\"\",\"\",'Broker ICR'!E61)","v":""},null,{"$f":"IF('Broker ICR'!E63=\"\",\"\",'Broker ICR'!E63)","v":""},null,{"$f":"SUM(E10:O10)","v":0}]
[null,null,"Term (years)",null,20]
[null,null,"Transaction Type",null,{"$f":"'Broker ICR'!E16","v":0}]
[]
[null,null,"5 Year Fixed Remaining",null,"No",null,"No",null,"No",null,"No",null,"No",null,"No"]
[null,null,null,null,{"$f":"E10*E16","v":0},null,{"$f":"IF(G10=\"\",\"\",G10*IF(G16=\"\",3.5%,G16))","v":""},null,{"$f":"IF(I10=\"\",\"\",I10*IF(I16=\"\",3.5%,I16))","v":""},null,{"$f":"IF(K10=\"\",\"\",K10*IF(K16=\"\",3.5%,K16))","v":""},null,{"$f":"IF(M10=\"\",\"\",M10*IF(M16=\"\",3.5%,M16))","v":""},null,{"$f":"IF(O10=\"\",\"\",O10*IF(O16=\"\",3.5%,O16))","v":""},null,{"$f":"SUM(E15:O15)","v":0}]
[null,null,"Product Rate",null,null,null,{"$f":"IF('Broker ICR'!I58=\"\",\"\",'Broker ICR'!I58)","v":""},null,{"$f":"IF('Broker ICR'!I59=\"\",\"\",'Broker ICR'!I59)","v":""},null,{"$f":"IF('Broker ICR'!I60=\"\",\"\",'Broker ICR'!I60)","v":""},null,{"$f":"IF('Broker ICR'!I61=\"\",\"\",'Broker ICR'!I61)","v":""},null,{"$f":"IF('Broker ICR'!I63=\"\",\"\",'Broker ICR'!I63)","v":""},null,{"$f":"Q15/Q10","v":{"$error":"#DIV/0!"}}]
[null,null,"Stressed interest rate",null,{"$f":"Admin!J18","v":6.2E-2},null,{"$f":"IF(G16=\"\",\"\",IF(G14=\"\",\"\",IF(ISERROR(IF(G14=\"No\",6.2%,G16)),\"Select Product\",IF(G14=\"No\",6.2%,G16))))","v":""},null,{"$f":"IF(I16=\"\",\"\",IF(I14=\"\",\"\",IF(ISERROR(IF(I14=\"No\",6.2%,I16)),\"Select Product\",IF(I14=\"No\",6.2%,I16))))","v":""},null,{"$f":"IF(K16=\"\",\"\",IF(K14=\"\",\"\",IF(ISERROR(IF(K14=\"No\",6.2%,K16)),\"Select Product\",IF(K14=\"No\",6.2%,K16))))","v":""},null,{"$f":"IF(M16=\"\",\"\",IF(M14=\"\",\"\",IF(ISERROR(IF(M14=\"No\",6.2%,M16)),\"Select Product\",IF(M14=\"No\",6.2%,M16))))","v":""},null,{"$f":"IF(O16=\"\",\"\",IF(O14=\"\",\"\",IF(ISERROR(IF(O14=\"No\",6.2%,O16)),\"Select Product\",IF(O14=\"No\",6.2%,O16))))","v":""},null,6.2E-2]
[null,null,"Holiday Let",null,{"$f":"IF(ISERROR('Broker ICR'!E26),\"\",'Broker ICR'!E26)","v":0},null,{"$f":"IF('Broker ICR'!H58=\"\",\"\",IF('Broker ICR'!H58=\"Holiday Let\",\"Yes\",\"No\"))","v":""},null,{"$f":"IF('Broker ICR'!H59=\"\",\"\",IF('Broker ICR'!H59=\"Holiday Let\",\"Yes\",\"No\"))","v":""},null,{"$f":"IF('Broker ICR'!H60=\"\",\"\",IF('Broker ICR'!H60=\"Holiday Let\",\"Yes\",\"No\"))","v":""},null,{"$f":"IF('Broker ICR'!H61=\"\",\"\",IF('Broker ICR'!H61=\"Holiday Let\",\"Yes\",\"No\"))","v":""},null,{"$f":"IF('Broker ICR'!H63=\"\",\"\",IF('Broker ICR'!H63=\"Holiday Let\",\"Yes\",\"No\"))","v":""}]
[null,null,"HMO",null,{"$f":"IF(ISERROR('Broker ICR'!E25),\"\",'Broker ICR'!E25)","v":0},null,{"$f":"IF('Broker ICR'!H58=\"\",\"\",IF('Broker ICR'!H58=\"HMO\",\"Yes\",\"No\"))","v":""},null,{"$f":"IF('Broker ICR'!H59=\"\",\"\",IF('Broker ICR'!H59=\"HMO\",\"Yes\",\"No\"))","v":""},null,{"$f":"IF('Broker ICR'!H60=\"\",\"\",IF('Broker ICR'!H60=\"HMO\",\"Yes\",\"No\"))","v":""},null,{"$f":"IF('Broker ICR'!H61=\"\",\"\",IF('Broker ICR'!H61=\"HMO\",\"Yes\",\"No\"))","v":""},null,{"$f":"IF('Broker ICR'!H63=\"\",\"\",IF('Broker ICR'!H63=\"HMO\",\"Yes\",\"No\"))","v":""}]
[null,null,"HMO / Holiday Let?",null,{"$f":"IF(AND(E18=\"\",E19=\"\"),\"\",IF(OR(E18=\"Yes\",E19=\"Yes\"),\"Yes\",\"No\"))","v":"No"},null,{"$f":"IF(AND(G18=\"\",G19=\"\"),\"\",IF(OR(G18=\"Yes\",G19=\"Yes\"),\"Yes\",\"No\"))","v":""},null,{"$f":"IF(AND(I18=\"\",I19=\"\"),\"\",IF(OR(I18=\"Yes\",I19=\"Yes\"),\"Yes\",\"No\"))","v":""},null,{"$f":"IF(AND(K18=\"\",K19=\"\"),\"\",IF(OR(K18=\"Yes\",K19=\"Yes\"),\"Yes\",\"No\"))","v":""},null,{"$f":"IF(AND(M18=\"\",M19=\"\"),\"\",IF(OR(M18=\"Yes\",M19=\"Yes\"),\"Yes\",\"No\"))","v":""},null,{"$f":"IF(AND(O18=\"\",O19=\"\"),\"\",IF(OR(O18=\"Yes\",O19=\"Yes\"),\"Yes\",\"No\"))","v":""}]
[]
[null,null,"Income (A)"]
[null,null,"Gross Annual Rental Income",null,{"$f":"IF('Broker ICR'!E6=\"-\",0,IFERROR(IF('Broker ICR'!E26=\"No\",'Broker ICR'!E14,'Broker ICR'!E43),\"\"))","v":0},null,{"$f":"IF('Broker ICR'!G58=\"\",\"\",'Broker ICR'!G58)","v":""},null,{"$f":"IF('Broker ICR'!G59=\"\",\"\",'Broker ICR'!G59)","v":""},null,{"$f":"IF('Broker ICR'!G60=\"\",\"\",'Broker ICR'!G60)","v":""},null,{"$f":"IF('Broker ICR'!G61=\"\",\"\",'Broker ICR'!G61)","v":""},null,{"$f":"IF('Broker ICR'!G63=\"\",\"\",'Broker ICR'!G63)","v":""},null,{"$f":"SUM(E23:O23)","v":0}]
[]
[null,null,"Tax Credit",null,{"$f":"IF(E10=\"\",0,IF(E18=\"Yes\",E10*E17,(E10*E17)*0.2))","v":0},null,{"$f":"IF(G10=\"\",0,IF(G18=\"Yes\",G10*G17,(G10*G17)*0.2))","v":0},null,{"$f":"IF(I10=\"\",0,IF(I18=\"Yes\",I10*I17,(I10*I17)*0.2))","v":0},null,{"$f":"IF(K10=\"\",0,IF(K18=\"Yes\",K10*K17,(K10*K17)*0.2))","v":0},null,{"$f":"IF(M10=\"\",0,IF(M18=\"Yes\",M10*M17,(M10*M17)*0.2))","v":0},null,{"$f":"IF(O10=\"\",0,IF(O18=\"Yes\",O10*O17,(O10*O17)*0.2))","v":0},null,{"$f":"SUM(E25:O25)","v":0}]
[]
[null,null,"Applicant Tax Band (Excludes Limited Company BTL)"]
[null,null,"Applicant 1 Tax Band",null,{"$f":"IF('Broker ICR'!E48=\"\",\"\",'Broker ICR'!E48)","v":""}]
[null,null,"Applicant 2 Tax Band",null,{"$f":"IF('Broker ICR'!H48=\"\",\"\",'Broker ICR'!H48)","v":""}]
[null,null,"Annual Costs"]
[null,null,"Taxation Type",null,{"$f":"IF('Broker ICR'!E26=\"Yes\",\"Holiday BTL\",\nIF('Broker ICR'!E23=\"Yes\",\"Personal BTL\",\nIF('Broker ICR'!H26=\"Yes\",\"Company BTL\",\"Error\")))","v":"Error"},null,null,null,null,null,null,null,null,null,null,null,{"$f":"E31","v":"Error"}]
[null,null,"Income tax payable based on Tax Rate after deduction for 20% tax relief and 30% deductable costs",null,{"$f":"IF(E10=0,\"\",IF('Broker ICR'!H26=\"Yes\",0,IF(E28=\"\",\"Error\",IF(E31=\"Personal BTL\",Tax!C15,0))))","v":""},null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(Q31=\"Personal BTL\",Tax!F15,0)","v":0}]
[null,null,"Total Letting & Management Fees Per Annum",null,{"$f":"'Broker ICR'!E14*'Broker ICR'!H14","v":0}]
[null,null,"Letting & Management Fees in excess of 12.5%+VAT per annum\n",null,{"$f":"IF(E23=\"\",\"\",IF((E23*'Broker ICR'!$H$14)-(E23*15%)<=0,0,(E23*'Broker ICR'!$H$14)-(E23*15%)))","v":0},null,{"$f":"IF(G23=\"\",\"\",IF((G23*'Broker ICR'!$H$14)-(G23*15%)<=0,0,(G23*'Broker ICR'!$H$14)-(G23*15%)))","v":""},null,{"$f":"IF(I23=\"\",\"\",IF((I23*'Broker ICR'!$H$14)-(I23*15%)<=0,0,(I23*'Broker ICR'!$H$14)-(I23*15%)))","v":""},null,{"$f":"IF(K23=\"\",\"\",K23*IF($E$33/$E$23-15%<0,0,$E$33/$E$23-15%))","v":""},null,{"$f":"IF(M23=\"\",\"\",M23*IF($E$33/$E$23-15%<0,0,$E$33/$E$23-15%))","v":""},null,{"$f":"IF(O23=\"\",\"\",O23*IF($E$33/$E$23-15%<0,0,$E$33/$E$23-15%))","v":""},null,{"$f":"SUM(E34:O34)","v":0}]
[null,null,"HMO / Holiday Let Costs"]
[null,null,"Cost of Cleaning",null,{"$f":"IF('Broker ICR'!E25=\"Yes\",'Broker ICR'!E30*52,IF('Broker ICR'!E26=\"Yes\",'Holiday Let'!F9*'Broker ICR'!E30,0))","v":0}]
[null,null,"HMO Licence Fee",null,{"$f":"IF(E10=0,\"\",IF(E19=\"Yes\",Admin!U3/Admin!U4,\"\"))","v":""}]
[null,null,"Utilities (Gas, Elec, Water)",null,{"$f":"IF('Broker ICR'!H30=\"\",\"\",'Broker ICR'!H30*12)","v":""}]
[null,null,"Council Tax / Business Rates",null,{"$f":"IF('Broker ICR'!E32=\"\",\"\",'Broker ICR'!E32*12)","v":""}]
[null,null,"Furniture, White Goods (see admin tab)",null,{"$f":"Admin!C24","v":725}]
[null,null,"Other Costs (Media/Internet costs)",null,{"$f":"IF('Broker ICR'!H32=\"\",\"\",'Broker ICR'!H32*12)","v":""}]
[null,null,"Total Actual Direct Costs (HMO/Holiday Let Only)",null,{"$f":"SUM(E36:E41)","v":725},null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(E42:O42)","v":725}]
[null,null,"Assumed Direct Costs\n(Only used when no costs are added)",null,null,null,{"$f":"IF(G23=\"\",\"\",IF(G20=\"Yes\",G23*20%,0))","v":""},null,{"$f":"IF(I23=\"\",\"\",IF(I20=\"Yes\",I23*20%,0))","v":""},null,{"$f":"IF(K23=\"\",\"\",IF(K20=\"Yes\",K23*20%,0))","v":""},null,{"$f":"IF(M23=\"\",\"\",IF(M20=\"Yes\",M23*20%,0))","v":""},null,{"$f":"IF(O23=\"\",\"\",IF(O20=\"Yes\",O23*20%,0))","v":""},null,{"$f":"SUM(E43:O43)","v":0}]
[null,null,"Costs to be used",null,{"$f":"IF(E20=\"No\",0,E42)","v":0},null,{"$f":"IF(G42<>\"\",G42,G43)","v":""},null,{"$f":"IF(I42<>\"\",I42,I43)","v":""},null,{"$f":"IF(K42<>\"\",K42,K43)","v":""},null,{"$f":"IF(M42<>\"\",M42,M43)","v":""},null,{"$f":"IF(O42<>\"\",O42,O43)","v":""},null,{"$f":"SUM(E44,G44,I44,K44,M44,O44)","v":0}]
[null,null,"Total (B)",null,{"$f":"IF(ISERROR(E32+E34+E44),\"\",E32+E34+E44)","v":""},null,{"$f":"IF(G10=\"\",\"\",SUM(G34:G43))","v":""},null,{"$f":"IF(I10=\"\",\"\",SUM(I34:I43))","v":""},null,{"$f":"IF(K10=\"\",\"\",SUM(K34:K43))","v":""},null,{"$f":"IF(M10=\"\",\"\",SUM(M34:M43))","v":""},null,{"$f":"IF(O10=\"\",\"\",SUM(O34:O43))","v":""},null,{"$f":"Q44+Q34+Q32","v":0},null,"Portfolio Top slice"]
[]
[null,null,"Annual Net Income (C) (A-B)",null,{"$f":"IF(E45=\"\",\"\",IF(E23=\"\",\"\",+E23-E45))","v":""},null,{"$f":"IF(G23=\"\",\"\",+G23-G45)","v":""},null,{"$f":"IF(I23=\"\",\"\",+I23-I45)","v":""},null,{"$f":"IF(K23=\"\",\"\",+K23-K45)","v":""},null,{"$f":"IF(M23=\"\",\"\",+M23-M45)","v":""},null,{"$f":"IF(O23=\"\",\"\",+O23-O45)","v":""},null,{"$f":"+Q23-Q45","v":0},null,{"$f":"SUM(G47:O47)","v":0}]
[]
[null,null,"Stressed Mortgage Payment (D)",null,{"$f":"IF(ISERROR(IF(E10=\"\",\"\",E10*E17)),\"\",IF(E10=\"\",\"\",E10*E17))","v":0},null,{"$f":"IF(ISERROR(IF(G10=\"\",\"\",ROUND(+G10*G17,0))),\"\",IF(G10=\"\",\"\",ROUND(+G10*G17,0)))","v":""},null,{"$f":"IF(ISERROR(IF(I10=\"\",\"\",ROUND(+I10*I17,0))),\"\",IF(I10=\"\",\"\",ROUND(+I10*I17,0)))","v":""},null,{"$f":"IF(ISERROR(IF(K10=\"\",\"\",ROUND(+K10*K17,0))),\"\",IF(K10=\"\",\"\",ROUND(+K10*K17,0)))","v":""},null,{"$f":"IF(ISERROR(IF(M10=\"\",\"\",ROUND(+M10*M17,0))),\"\",IF(M10=\"\",\"\",ROUND(+M10*M17,0)))","v":""},null,{"$f":"IF(ISERROR(IF(O10=\"\",\"\",ROUND(+O10*O17,0))),\"\",IF(O10=\"\",\"\",ROUND(+O10*O17,0)))","v":""},null,{"$f":"Q10*Q17","v":0},null,{"$f":"SUM(G49:O49)","v":0}]
[]
[null,null,"Surplus/shortfall (E) C-D",null,{"$f":"IF(ISERROR(E47-E49),\"\",E47-E49)","v":""},null,{"$f":"IF(G10=\"\",\"\",G47-G49)","v":""},null,{"$f":"IF(I10=\"\",\"\",I47-I49)","v":""},null,{"$f":"IF(K10=\"\",\"\",K47-K49)","v":""},null,{"$f":"IF(M10=\"\",\"\",M47-M49)","v":""},null,{"$f":"IF(O10=\"\",\"\",O47-O49)","v":""},null,{"$f":"Q47-Q49","v":0}]
[]
[null,null,"Calculated ICR - minimum of 130% required",null,{"$f":"IF(ISERROR(E47/E49),\"\",E47/E49)","v":""},null,{"$f":"IF(ISERROR(G47/G49),\"\",G47/G49)","v":""},null,{"$f":"IF(ISERROR(I47/I49),\"\",I47/I49)","v":""},null,{"$f":"IF(ISERROR(K47/K49),\"\",K47/K49)","v":""},null,{"$f":"IF(ISERROR(M47/M49),\"\",M47/M49)","v":""},null,{"$f":"IF(ISERROR(O47/O49),\"\",O47/O49)","v":""},null,{"$f":"Q47/Q49","v":{"$error":"#DIV/0!"}},null,{"$f":"IF(ISERROR(S47/S49),0,S47/S49)","v":0}]
[]
[null,null,"Top Slicing - Monthly requirement",null,{"$f":"IF(ISERROR(IF((E49*1.3-E47)/12<0,0,(E49*1.3-E47)/12)),\"\",IF((E49*1.3-E47)/12<0,0,(E49*1.3-E47)/12))","v":""},null,{"$f":"IF(E55<0,-E55*12,\"\")","v":""},null,null,null,null,null,null,null,{"$f":"IF(O10=\"\",\"\",IF(O51>0,0,O51/12-(O51/12)*2))","v":""},null,{"$f":"(Q49*1.3-Q47)/12","v":0},null,{"$f":"'P Max'!H13","v":0}]
[]
[null,null,"Maximum Loan",null,{"$f":"IF(ISERROR('1 max'!H6),\"\",'1 max'!H6)","v":0}]
[]
[null,null,"Maximum Loan - restricted by portfolio",null,{"$f":"IF(ISERROR('P Max'!H13),\"\",'P Max'!H13)","v":0}]

{"sheet":"Tax","index":2,"n_rows":15,"n_cols":7,"rows_kept":15,"cut":false,"hidden":true,"merged":[[0,2,0,3],[0,5,0,6]]}
[null,null,"Individual Property",null,null,"Portfolio"]
[null,null,"App1","App2",null,"App1","App2"]
[null,"Mortgage Type",{"$f":"IF(ISERROR('ICR v4'!E31),\"\",'ICR v4'!E31)","v":"Error"}]
[null,"Taxable Rental Income",{"$f":"IF('ICR v4'!E29=\"\",'ICR v4'!E23,'ICR v4'!E23/2)","v":0},{"$f":"IF('ICR v4'!E29=\"\",\"\",'ICR v4'!E23/2)","v":""},null,{"$f":"IF('ICR v4'!E29=\"\",'ICR v4'!Q23,'ICR v4'!Q23/2)","v":0},{"$f":"IF('ICR v4'!E29=\"\",\"\",'ICR v4'!Q23/2)","v":""}]
[null,"Interest Charge",{"$f":"IF(D4<>\"\",'ICR v4'!E10*'ICR v4'!E17/2,'ICR v4'!E10*'ICR v4'!E17)","v":0},{"$f":"IF(D4<>\"\",('ICR v4'!E10*'ICR v4'!E17)/2,\"\")","v":""},null,{"$f":"IF(D4<>\"\",'ICR v4'!Q10*'ICR v4'!Q17/2,'ICR v4'!Q10*'ICR v4'!Q17)","v":0},{"$f":"IF(D4<>\"\",('ICR v4'!Q10*'ICR v4'!Q17)/2,\"\")","v":""}]
[null,"Deduction of costs",{"$f":"IF(ISERROR((C4/130)*30),0,(C4/130)*30)","v":0},{"$f":"IF(D4=\"\",\"\",C6)","v":""},null,{"$f":"(F4/130)*30","v":0},{"$f":"IF(G4=\"\",\"\",F6)","v":""}]
[]
[null,"Taxable Amount",{"$f":"IF(ISERROR(C4-C6),0,C4-C6)","v":0},{"$f":"IF(D4=\"\",\"\",D4-D6)","v":""},null,{"$f":"F4-F6","v":0},{"$f":"IF(ISERROR(G4-G6),\"\",G4-G6)","v":""}]
[null,"Tax Rate Type",{"$f":"'ICR v4'!E28","v":""},{"$f":"IF('ICR v4'!E29=\"\",\"\",'ICR v4'!E29)","v":""},null,{"$f":"C9","v":""},{"$f":"D9","v":""}]
[null,"Tax Rate",{"$f":"IF(ISERROR(VLOOKUP(C9,Admin!$M:$N,2,FALSE)),0,VLOOKUP(C9,Admin!$M:$N,2,FALSE))","v":0},{"$f":"IF(D4=\"\",\"\",VLOOKUP(D9,Admin!$M:$N,2,FALSE))","v":""},null,{"$f":"IF(ISERROR(VLOOKUP(F9,Admin!$M:$N,2,FALSE)),0,VLOOKUP(F9,Admin!$M:$N,2,FALSE))","v":0},{"$f":"IF(G4=\"\",\"\",VLOOKUP(G9,Admin!$M:$N,2,FALSE))","v":""}]
[null,"Tax Credit",{"$f":"IF(C3=\"Holiday BTL\",C5,C5*20%)","v":0},{"$f":"IF(D5=\"\",\"\",IF(C3=\"Holiday BTL\",C5,C5*20%))","v":""},null,{"$f":"IF(F3=\"Holiday BTL\",F5,F5*20%)","v":0},{"$f":"IF(G5=\"\",\"\",IF(F3=\"Holiday BTL\",F5,F5*20%))","v":""}]
[null,"Tax Due Before Tax Credit",{"$f":"C8*C10","v":0},{"$f":"IF(D4=\"\",\"\",D8*D10)","v":""},null,{"$f":"IF(ISERROR(F8*F10),0,F8*F10)","v":0},{"$f":"IF(G4=\"\",\"\",G8*G10)","v":""}]
[null,"Tax to be used in calculator",{"$f":"IF(C12-C11<0,0,C12-C11)","v":0},{"$f":"IF(D4=\"\",\"\",IF(D12-D11<0,0,D12-D11))","v":""},null,{"$f":"IF(F12-F11<0,0,F12-F11)","v":0},{"$f":"IF(G4=\"\",\"\",IF(G12-G11<0,0,G12-G11))","v":""}]
[]
[null,"Total Tax to be used",{"$f":"SUM(C13:D13)","v":0},null,null,{"$f":"SUM(F13:G13)","v":0}]

{"sheet":"1 max","index":3,"n_rows":15,"n_cols":8,"rows_kept":15,"cut":false,"hidden":true,"merged":[[0,2,0,3],[0,6,0,7]]}
[null,null,"Individual Property",null,null,null,"Single Max Loan"]
[null,null,"App1","App2"]
[null,"Mortgage Type",{"$f":"'ICR v4'!E31","v":"Error"},null,null,null,"Rental Income",{"$f":"IF('Broker ICR'!E26=\"No\",'Broker ICR'!E14,'Broker ICR'!E43)","v":0}]
[null,"Taxable Rental Income",{"$f":"IF('ICR v4'!E29=\"\",'ICR v4'!E23,'ICR v4'!E23/2)","v":0},{"$f":"IF('ICR v4'!E29=\"\",\"\",'ICR v4'!E23/2)","v":""},null,null,"Deductions",{"$f":"'ICR v4'!E34+'ICR v4'!E44+C15","v":0}]
[null,"Interest Charge",{"$f":"IF(D4<>\"\",((H6+'Broker ICR'!E12)*'ICR v4'!E17)/2,(H6+'Broker ICR'!E12)*'ICR v4'!E17)","v":0},{"$f":"IF(D4<>\"\",((H6+'Broker ICR'!E12)*'ICR v4'!E17)/2,\"\")","v":""},null,null,"Net Rental Income",{"$f":"H3-H4","v":0}]
[null,"Deduction of costs",{"$f":"(C4/130)*30","v":0},{"$f":"IF(D4=\"\",\"\",C6)","v":""},null,null,"Maximum Loan",{"$f":"IF(ROUNDDOWN((H5/'ICR v4'!E17)/130%,0)-'Broker ICR'!E12<0,0,ROUNDDOWN((H5/'ICR v4'!E17)/130%,0)-'Broker ICR'!E12)","v":0}]
[]
[null,"Taxable Amount",{"$f":"C4-C6","v":0},{"$f":"IF(D4=\"\",\"\",D4-D6)","v":""}]
[null,"Tax Rate Type",{"$f":"'ICR v4'!E28","v":""},{"$f":"IF('ICR v4'!E29=\"\",\"\",'ICR v4'!E29)","v":""}]
[null,"Tax Rate",{"$f":"IF(ISERROR(VLOOKUP(C9,Admin!$M:$N,2,FALSE)),0,VLOOKUP(C9,Admin!$M:$N,2,FALSE))","v":0},{"$f":"IF(D4=\"\",\"\",VLOOKUP(D9,Admin!$M:$N,2,FALSE))","v":""}]
[null,"Tax Credit",{"$f":"IF(C3=\"Holiday BTL\",C5,C5*20%)","v":0},{"$f":"IF(D5=\"\",\"\",IF(C3=\"Holiday BTL\",C5,C5*20%))","v":""}]
[null,"Tax Due Before Tax Credit",{"$f":"C8*C10","v":0},{"$f":"IF(D4=\"\",\"\",D8*D10)","v":""}]
[null,"Tax to be used in calculator",{"$f":"IF(C12-C11<0,0,C12-C11)","v":0},{"$f":"IF(D4=\"\",\"\",IF(D12-D11<0,0,D12-D11))","v":""}]
[]
[null,"Total Tax to be used",{"$f":"IF('Broker ICR'!H26=\"Yes\",0,SUM(C13:D13))","v":0}]

{"sheet":"P Max","index":4,"n_rows":15,"n_cols":8,"rows_kept":15,"cut":false,"hidden":true,"merged":[[0,2,0,3],[0,6,0,7]]}
[null,null,"Portfolio",null,null,null,"Portfolio Maximum Loan"]
[null,null,"App1","App2"]
[null,"Mortgage Type",null,null,null,null,"Rental Income",{"$f":"'ICR v4'!Q23","v":0}]
[null,"Taxable Rental Income",{"$f":"IF('ICR v4'!E29=\"\",'ICR v4'!Q23,'ICR v4'!Q23/2)","v":0},{"$f":"IF('ICR v4'!E29=\"\",\"\",'ICR v4'!Q23/2)","v":""},null,null,"Deductions",{"$f":"'ICR v4'!Q43+'ICR v4'!Q34","v":0}]
[null,"Interest Charge",{"$f":"IF(D4<>\"\",(H12+'Broker ICR'!E12)*'ICR v4'!Q17/2,(SUM('ICR v4'!G10:O10)+(H12+'Broker ICR'!E12))*'ICR v4'!Q17)","v":0},{"$f":"IF(D4<>\"\",((H12+'Broker ICR'!E12)*'ICR v4'!Q17)/2,\"\")","v":""},null,null,"Net Rental Income",{"$f":"H3-H4-C15","v":0}]
[null,"Deduction of costs",{"$f":"(C4/130)*30","v":0},{"$f":"IF(D4=\"\",\"\",C6)","v":""},null,null,"Stressed Payment",{"$f":"'ICR v4'!Q49","v":0}]
[null,null,null,null,null,null,"ICR%",{"$f":"H3/H6","v":{"$error":"#DIV/0!"}}]
[null,"Taxable Amount",{"$f":"C4-C6","v":0},{"$f":"D4-D6","v":{"$error":"#VALUE!"}},null,null,"Required Rental Income",{"$f":"H6*130%","v":0}]
[null,"Tax Rate Type",{"$f":"'ICR v4'!E28","v":""},{"$f":"IF('ICR v4'!E29=\"\",\"\",'ICR v4'!E29)","v":""},null,null,"Difference",{"$f":"IF((H8-H5)<0,0,H8-H5)","v":0}]
[null,"Tax Rate",{"$f":"VLOOKUP(C9,Admin!$M:$N,2,FALSE)","v":{"$error":"#N/A"}},{"$f":"IF(D4=\"\",\"\",VLOOKUP(D9,Admin!$M:$N,2,FALSE))","v":""},null,null,"Top Slice",{"$f":"H9/12","v":0}]
[null,"Tax Credit",{"$f":"IF(Tax!C3=\"Holiday BTL\",C5,C5*20%)","v":0},{"$f":"IF(D4=\"\",\"\",IF(Tax!C3=\"Holiday BTL\",C5,C5*20%))","v":""},null,null,"Portfolio Borrowing",{"$f":"SUM('ICR v4'!G10:O10)","v":0}]
[null,"Tax Due Before Tax Credit",{"$f":"C8*C10","v":{"$error":"#N/A"}},{"$f":"IF(D4=\"\",\"\",D8*D10)","v":""},null,null,"Max Borrowing",{"$f":"(H5/'ICR v4'!Q17)/130%","v":0}]
[null,"Tax to be used in calculator",{"$f":"IF(C12-C11<0,0,C12-C11)","v":{"$error":"#N/A"}},{"$f":"IF(D4=\"\",\"\",IF(D12-D11<0,0,D12-D11))","v":""},null,null,"Max New Loan",{"$f":"IF(ROUNDDOWN(H12-H11,0)-'Broker ICR'!E12<0,0,ROUNDDOWN(H12-H11,0)-'Broker ICR'!E12)","v":0}]
[]
[null,"Total Tax to be used",{"$f":"IF('Broker ICR'!H26=\"Yes\",0,IF(ISERROR(SUM(C13:D13)),0,SUM(C13:D13)))","v":0}]

{"sheet":"Holiday Let","index":5,"n_rows":16,"n_cols":7,"rows_kept":16,"cut":false,"hidden":true,"merged":[[11,2,11,5],[15,2,15,3]]}
[]
[null,null,"Holiday Let - Add additional Information in YELLOW cells where known"]
[]
[]
[null,null,"Rental Demand Period","Weekly Rental Price","No. of Weeks","Occupancy Rate"]
[null,null,"High",{"$f":"'Broker ICR'!E40","v":0},14,0.9,{"$f":"E6*F6","v":12.6}]
[null,null,"Medium ",{"$f":"'Broker ICR'!E41","v":0},18,0.75,{"$f":"E7*F7","v":13.5}]
[null,null,"Low",{"$f":"'Broker ICR'!E42","v":0},{"$f":"E9-E6-E7","v":20},0.3,6]
[null,null,"Annual Rental Income",{"$f":"(D6*G6)+(D7*G7)+(D8*G8)","v":0},52,31.4]
[]
[]
[null,null,"Credits Scores"]
[null,null,"Applicant 1","Applicant 2","Applicant 3","Applicant 4"]
[]
[]
[null,null,"Proxy Expected Loss Trigger",null,{"$f":"Admin!N31","v":{"$error":"#DIV/0!"}},{"$f":"IF(E16=\"Yes\",\"Refer to Level 3 Mandate Holder\",\"\")","v":{"$error":"#DIV/0!"}}]

{"sheet":"Admin","index":6,"n_rows":62,"n_cols":23,"rows_kept":62,"cut":false,"hidden":true,"merged":[[13,4,17,6],[18,4,18,6],[19,4,21,6],[22,4,22,6],[11,8,11,9],[42,0,42,1],[34,3,34,6],[35,3,36,6],[37,3,37,6],[38,3,38,6],[39,3,39,6]]}
["Dropdowns"]
["-","Costs Required","Personal BTL","HMO","Holiday Let","Limited Company","Product","Rate",null,null,null,null,null,null,null,"No Garden","Hours","Costs","Garden","Product Fee","HMO Licence","Product Type","Properties"]
["BTL - Limited Company","No","No","No","No","Yes","5yr Fixed",3.49E-2,"Purchase","Personal Allowance",0,"Yes","Basic Rate",0.2,"No","Small Garden",2,{"$f":"(Q3*15)*9","v":270},"Yard - Areas up to 100m sq",0,1000,"5 Year Discount",1]
["BTL - Personal","No","Yes","No","No","No","Standard",3.7400000000000003E-2,"Remortgage","Basic Rate",0.2,"No","Higher Rate",0.4,"Holiday Let","Medium Garden",4,{"$f":"(Q4*15)*9","v":540},"Average residential garden - Areas between 100m and 300m sq",999,5,"Other",2]
["BTL - Limited Company (HMO)","Yes","No","Yes","No","Yes",null,2.5000000000000001E-2,null,"Higher Rate",0.4,null,"Additional Rate",0.45,"HMO","Large Garden",6,810,"Property with more extensive grounds - Greater than 300m sq",1249,null,null,3]
["BTL - Personal (HMO)","Yes","Yes","Yes","No","No",null,2.75E-2,null,"Additional Rate",0.45,null,null,null,null,"Extensive",10,1350,"Multiple acres / Fields",1499,null,null,4]
["Holiday - Limited Company","Yes","No","No","Yes","Yes",null,null,null,null,null,null,null,null,null,null,null,null,null,1749,null,null,"5+"]
["Holiday - Personal","Yes","Yes","No","Yes","No",null,null,null,null,null,null,null,null,null,null,null,null,null,1999]
[]
[]
["Renewal of Fixtures/Fittings/Goods"]
["Property Value",{"$f":"'Broker ICR'!H8","v":0},null,null,null,null,null,null,"Stressed Rate Calculation",null,null,null,"PEL Calculation"]
["White Goods","Value","Cost per year","Renewal Period",null,null,null,null,"Product Type",{"$f":"'Broker ICR'!E10","v":0},null,null,"Credit Score 1",{"$f":"'Holiday Let'!C14","v":0}]
["Oven / Hob / cooker",{"$f":"IF($B$12>500000,300,150)","v":150},{"$f":"B14/D14","v":25},6,"White goods value is based on basic low-cost replacements found online and rounded to nearest £50.",null,null,null,"Product Rate",{"$f":"'Broker ICR'!H10","v":0},null,null,"Credit Score 2 ",{"$f":"'Holiday Let'!D14","v":0}]
["Washing Machine",{"$f":"IF($B$12>500000,400,200)","v":200},{"$f":"B15/D15","v":33.333333333333336},6,null,null,null,null,"Standard Stress Rate",6.2E-2,null,null,"Credit Score 3",{"$f":"'Holiday Let'!E14","v":0}]
["Dishwasher",{"$f":"IF($B$12>500000,400,200)","v":200},33.333333333333336,6,null,null,null,null,"Product Rate + 3%",{"$f":"J14+3%","v":0.03},null,null,"Credit Score 4",{"$f":"'Holiday Let'!F14","v":0}]
["Tumble Dryer",200,33.333333333333336,6,null,null,null,null,"Discounted Stressed Rate",{"$f":"IF(J14=0,J15,MIN(J15:J16))","v":6.2E-2},null,null,"Average Credit Score",{"$f":"AVERAGEIF(N13:N16,\">0\",N13:N16)","v":{"$error":"#DIV/0!"}}]
["Fridge Freezer",{"$f":"IF($B$12>500000,400,250)","v":250},41.666666666666664,6,null,null,null,null,"Rate to be used",{"$f":"IF(J13=\"5 Year Discount\",J17,6.2%)","v":6.2E-2},null,null,"Score PD",{"$f":"2^((500-N17)/20)/(1+2^((500-N17)/20))","v":{"$error":"#DIV/0!"}}]
["Carpets",{"$f":"IF($B$12>500000,5000,3000)","v":3000},300,10,"Under 500k property (150sqm at £20), Over 500k property (200sqm at £25)",null,null,null,null,null,null,null,"Scalar PD",{"$f":"N18*B55","v":{"$error":"#DIV/0!"}}]
["Bed",{"$f":"IF($B$12>500000,1000,500)","v":500},50,10,"Based on replacement of 3 Beds (Under £500k), 3 pc Suite, TV, DVD Player and general convenience items",null,null,null,null,null,null,null,"Floored PD",{"$f":"IF(N19<B58,B58,N19)","v":{"$error":"#DIV/0!"}}]
["Sofa",{"$f":"IF($B$12>500000,1500,500)","v":500},83.333333333333329,6,null,null,null,null,null,null,null,null,"Valuation",{"$f":"'Broker ICR'!H8","v":0}]
["Other Household Items",{"$f":"IF($B$12>500000,2000,1000)","v":1000},125,8,null,null,null,null,null,null,null,null,"Forced Sale Valuation",{"$f":"N21*(1-Admin!B60)","v":0}]
["Gardening",{"$f":"'Broker ICR'!E34","v":0},{"$f":"IF(ISERROR(VLOOKUP(B23,Admin!P3:R6,3,FALSE)),0,VLOOKUP(B23,Admin!P3:R6,3,FALSE))","v":0},"Annual",null,null,null,null,null,null,null,null,"Sales Costs",{"$f":"IF(N22*B61>B62,B62,N22*B61)","v":0}]
["Used in affordability",null,{"$f":"SUM(C14:C23)","v":725},null,null,null,null,null,null,null,null,null,"Advance plus costs",{"$f":"'Broker ICR'!E8+Admin!N23","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,"LGD",{"$f":"IF((N24>100)>50%,-MIN(B59*(N24-N22),-N24*B58),IF((B59*(N24-N22))>0,0,(B59*(N24-N22))))","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Expected Loss (£)",{"$f":"N25*N20","v":{"$error":"#DIV/0!"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Downturn Expected Loss (%)",{"$f":"N26/'Broker ICR'!E8","v":{"$error":"#DIV/0!"}}]
["Broker ICR Password","Vern0nbs",null,null,null,null,null,null,null,null,null,null,"WA EL(%)",{"$f":"N27*'Broker ICR'!E8","v":{"$error":"#DIV/0!"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,"LTV Band",{"$f":"VLOOKUP(N32,A44:C49,3)","v":{"$error":"#N/A"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,"PEL Trigger",{"$f":"C37","v":9.7000000000000003E-3}]
["PEL",null,null,null,null,null,null,null,null,null,null,null,"PEL>Trigger",{"$f":"IF(N27>N30,\"YES\",\"NO\")","v":{"$error":"#DIV/0!"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Loan To Value",{"$f":"'Broker ICR'!H12","v":""}]
["Proxy Expected Loss"]
[]
[null,"LTV","PEL"]
["BTL",null,9.7000000000000003E-3,"If a ‘level’ was set for new business, it would look like a minimum LTV of 75% and minimum credit score of 600, which would be a PEL of 0.97%.  Cases such as the first one would then flag up due to the lower credit score of one applicant."]
["Holiday BTL",null,9.7000000000000003E-3]
["Mainstream","80% to 90%",2.5600000000000001E-2,"If the 575 credit score cut off was used as higher risk and the maximum 85% LTV, the PEL would be 2.56%"]
["Mainstream",">90%",0.04,"As first time buyers, there will be some cases which have lower credit scores and for a credit score of 575, the PEL would be 3.88%, for 565 is would be 5.5%, therefore only cases with a PEL over 4% should be viewed as higher risk within this sub-segment."]
["Mainstream","<80%",1.26E-2,"There were just two loans to people with a credit score below 600, at 571 and 569.  The PEL on these loans was 1.26% and 1.39% respectively."]
[]
[]
["LTV Bands",null,"Description"]
[0,0.6,"Less than 60%"]
[0.60000010000000004,0.7,"60 % <70%"]
[0.70000010000000001,0.8,"70%  <80%"]
[0.80000009999999999,0.9,"80%  <90%"]
[0.90000009999999997,0.95,"90% <95%"]
[0.95000000100000004,1,"≥95%"]
[]
["LEL Assumptions"]
[]
["PD Scalar","Current"]
["Resi",2.5]
["BTL",2.7]
["Floors"]
["PD",5.0000000000000001E-4]
["LGD",0.05,"5% on all loans",null,"On loans >50% LTV"]
["Prob (Possession │Default)",0.7]
["Haircut",0.25]
["Sale Cost %",0.05]
["Sale Costs - Cap",20000]

{"sheet":"Version","index":7,"n_rows":7,"n_cols":4,"rows_kept":7,"cut":false,"hidden":true,"merged":[]}
["Version","Date","Reason for Version","Owner"]
[1,null,"Initial calculator"]
[2,"2018-10-01","* Update ICR to loyalty product (1% off SVR) plus sttess of 2% = 6.20%\n* Allow for multiple properties\n* Ensure ICR is after costs"]
[3,"2018-12-01","Amended to deduct costs to align to Board paper in Sept 18","IK/AJE"]
[4,"2020-05-01","New version designed to include Broker front page, tax and cost calculations","BB"]
[5,"2021-06-01","Broker ICR Calculator Agreed by Credit Committee","BB"]
[6,"2021-07-01","5 year stressed interest rate added","BB"]