{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Loan Calculator","Workings","Heads of Terms","Aff. Calculator","Summary Matrix","ONS Figures"],"not_rendered":{"charts":0,"images":3,"macros":1,"external_links":4,"defined_names":70,"comments":2,"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":"Loan Calculator","index":0,"n_rows":60,"n_cols":16,"rows_kept":60,"cut":false,"hidden":false,"merged":[[56,12,56,15],[54,2,54,3],[30,7,30,10],[48,7,48,10],[62,2,62,5],[56,2,56,3],[36,2,36,5],[39,2,39,3],[42,7,45,10],[57,2,57,3],[55,2,55,3],[32,2,32,3],[38,2,38,3],[44,2,44,3],[38,12,52,15],[52,2,52,3],[18,12,18,13],[24,12,24,13],[25,2,25,3],[4,2,4,15],[10,12,10,15],[12,12,12,13],[16,3,16,5],[13,3,13,5],[14,12,14,13],[10,7,10,10],[10,2,10,5],[7,13,7,14],[14,3,14,5],[15,3,15,5],[12,3,12,5],[17,3,17,5],[22,12,22,14],[50,2,50,3],[46,2,46,3],[48,2,48,3],[28,12,28,15],[28,2,28,3],[40,2,40,3],[49,2,49,3],[27,2,27,3],[42,2,42,3],[2,6,3,11],[6,13,6,14],[6,4,6,10],[7,4,7,10],[16,12,16,13],[3,3,3,4]]}
[]
[]
[null,null,null,null,null,null,"NON-REGULATED BRIDGE CALCULATOR"]
[]
[null,null,"Effective from 12th June 2025"]
[]
[null,null,null,"Name",null,null,null,null,null,null,null,null,"Exit:"]
[null,null,null,"Security Address",null,null,null,null,null,null,null,null,"Purpose:"]
[]
[]
[null,null,"Proposed Loan (Inputs in blue)",null,null,null,null,"Loan Outputs",null,null,null,null,{"$f":"\"Rental Calculations\"&IF(Workings!$B$34=1,\" (Not Required for HOBL)\",\"\")","v":"Rental Calculations"}]
[]
[null,null,"Security","First Charge - Purchase",null,null,null,"Net Loan",null,null,{"$f":"IFERROR(IF(F21<Workings!B24,\"Below Min\",IF(F21>Workings!B23,\"Above Max\",IF(ISERROR(VLOOKUP(Workings!B15,Workings!A84:H149,5,FALSE)),\"No Plan\",F21))),\"\")","v":350000},null,"Max MT CMI on rent",null,null,{"$f":"IFERROR(IF(F33=\"Serviced\",IF(P17<=(F45/Workings!B18),((F45/Workings!B18)-F55),\"Fails Rental\"),\"Rolled\"),\"\")","v":"Rolled"}]
[]
[null,null,"Loan Plan","BTL / Holiday Let Bridge",null,null,null,{"$f":"\"Lender Fee\"&IF(F27>0,\" (inc Title Insurance)\",\"\")","v":"Lender Fee (inc Title Insurance)"},null,null,{"$f":"IF(D25=\"Added\",F25+Workings!I19,0)","v":11499},null,"MT proposed CMI",null,null,{"$f":"IF(F33=\"Serviced\",SUM(K21),\"Rolled\")","v":"Rolled"}]
[]
[null,null,"Rate Type","Fixed for Term",null,null,null,"Broker Fee",null,null,{"$f":"IF(OR(F23>F21*10%,F23>10000),\"Too High\",IF(D23=\"Added\",F23,0))","v":0},null,"Total Secured CMIs",null,null,{"$f":"IF(F33=\"Serviced\",IF(AND(Workings!B16=\"First charge\",F55>0),\"Remove RED\",SUM(F55+P15)),\"Rolled\")","v":"Rolled"}]
[]
[null,null,"Status Points",0,null,"Packaged",null,"Gross Loan",null,null,{"$f":"SUM(K17,K15,K13)","v":361499},null,"DSCR",null,null,{"$f":"IF(F33=\"Serviced\",IFERROR(SUM(F45/P17),\"\"),\"Rolled\")","v":"Rolled"}]
[]
[null,null,"Net Loan",null,null,350000,null,{"$f":"IF(F33=\"Serviced\",\"Monthly Payment\",\"Month 1 Interest (approx)\")","v":"Month 1 Interest (approx)"},null,null,{"$f":"IF(F29=\"\",\"Add Term\",IFERROR(ROUND((K19*Workings!B29),IF(F33=\"serviced\",2,0)),\"\"))","v":3651},null,"Stressed Shortfall?",null,null,{"$f":"IF(F33=\"Serviced\",IFERROR(IF((P23*DSCR)-F45<0,\"Fits Rent\", SUM((P23*DSCR)-F45)),\"\"),\"Rolled\")","v":"Rolled"}]
[]
[null,null,"Broker fee","Added",null,null,null,{"$f":"IF(F33=\"Serviced\",\"Monthly Payment (Stressed)\",\"Interest Over Term (approx)\")","v":"Interest Over Term (approx)"},null,null,{"$f":"IF(F29=\"\",\"Add Term\",IFERROR(IF(F33=\"Serviced\",IF(RIGHT(D15,5)=\"fixed\",IFERROR(ROUND(K19*Workings!B29,2),\"\"),IFERROR(ROUND(K19*(Workings!B29+(Stress/12)),2),\"\")),ROUNDUP(K19*((1+Workings!B29)^F29)-K19,0)),\"\"))","v":46332},null,"Total Secured CMIs Stressed",null,null,{"$f":"IF(F33=\"Serviced\",IFERROR(K23+F57,\"\"),\"Rolled\")","v":"Rolled"}]
[]
[null,null,"Lender Fee","Added",null,{"$f":"IFERROR(ROUND((Workings!B32*F21)+Workings!B33,2),0)","v":10500},null,{"$f":"IF(F33=\"Serviced\",\"Total Interest\",\"Total Loan (approx inc Interest)\")","v":"Total Loan (approx inc Interest)"},null,null,{"$f":"IFERROR(IF(F33=\"Serviced\",IFERROR(K21*F29,\"\"),K19+K23),\"\")","v":407831},null,"DSCR Stressed (1%)",null,null,{"$f":"IF(F33=\"Serviced\",IFERROR(SUM(F45/(F57+K23)),\"\"),\"Rolled\")","v":"Rolled"}]
[]
[null,null,{"$f":"IF(LEFT(D13,1)=\"S\",\"\",\nIF(LEFT(F41,1)=\"N\",\"\",\nIF(Workings!I23=\"NO\",\"\",\n\"Title Insurance\")))","v":"Title Insurance"},"Yes",null,{"$f":"Workings!I19","v":999},null,"LTV",null,null,{"$f":"IFERROR(IF(AND(Workings!B16=\"First charge\",F53>0),\"Remove RED\",IF(OR(Workings!B13=\"Y\",Workings!B14=\"Y\"),\"Add First charge\",ROUNDUP(SUM(IF(F33=\"Serviced\",K19,K25),F53)/F39,3))),\"\")","v":0.81599999999999995}]
[]
[null,null,"Term in Months",null,null,12,null,null,null,null,null,null,"Maximum Loan Calculator"]
[]
[null,null,{"$f":"IF(LEFT(D17,5)=\"fixed\",\"Monthly/Annual Rate (fixed)\",\"Monthly/Annual  Rate (variable)\")","v":"Monthly/Annual Rate (fixed)"},null,null,{"$f":"IFERROR(IF(Workings!B164=\"Y\",\"No Plan\",IFERROR(VLOOKUP(Workings!B15,Workings!A84:H149,IF(LEFT(D17,5)=\"Fixed\",8,7),FALSE)*100&\"% / \"&VLOOKUP(Workings!B15,Workings!A84:H149,IF(LEFT(D17,5)=\"Fixed\",8,7),FALSE)*1200&\"%\",\"No Plan\")),\"\")","v":"No Plan"},null,"Fees",null,null,null,null,"Maximum LTV Available",null,null,{"$f":"IFERROR(Workings!B37,\"\")","v":0.75}]
[]
[null,null,"Repayment Type",null,null,"Rolled",null,"Is Borrower a Limited Co?",null,null,"No",null,"Maximum Lending (Net) £",null,null,{"$f":"IFERROR(Workings!C161,\"\")","v":322716}]
[]
[null,null,null,null,null,null,null,"Is Property Leasehold?",null,null,"No"]
[]
[null,null,"Property Details (Inputs)",null,null,null,null,"Approx Legal Fee *",null,null,{"$f":"IF(AND(D13=\"first charge - other\",LEFT(F41,2)=\"No\"),VLOOKUP(K13,Workings!B55:E58,3,TRUE)+IF(VLOOKUP(K13,Workings!B55:E58,3,TRUE)=0,0,IF(K33=\"yes\",LTD,0))+IF(VLOOKUP(K13,Workings!B55:E58,3,TRUE)=0,0,IF(K35=\"yes\",Lease,0)),IF(OR(Workings!B16=\"Second Charge\",AND(Workings!B38=\"N\",F21<=100000)),0,IF(OR(K35=\"\",K33=\"\"),\"Complete Above\",VLOOKUP(K13,Workings!B55:E58,3,TRUE)+IF(VLOOKUP(K13,Workings!B55:E58,3,TRUE)=0,0,IF(K33=\"yes\",LTD,0))+IF(VLOOKUP(K13,Workings!B55:E58,3,TRUE)=0,0,IF(K35=\"yes\",Lease,0)))))","v":1800},null,"Notes"]
[]
[null,null,"Property value",null,null,500000,null,"Approx Valuation Fee **",null,null,{"$f":"IF(OR(F39=\"\",F39=0),\"Add Val\",IF(F41=\"Mainland Scotland\",IF(D19=Workings!G6,\"Refer\",VLOOKUP(F39,Workings!H61:J72,3,TRUE)),VLOOKUP(F39,Workings!B61:E80,IF(LEFT(D19,3)=\"hea\",4,3),TRUE)))","v":350},null,{"$f":"IFERROR(VLOOKUP(1,Workings!A165:C187,3,FALSE),\"\")&CHAR(10)&IFERROR(VLOOKUP(2,Workings!A165:C187,3,FALSE),\"\")&CHAR(10)&IFERROR(VLOOKUP(3,Workings!A165:C187,3,FALSE),\"\")&CHAR(10)&IFERROR(VLOOKUP(4,Workings!A165:C187,3,FALSE),\"\")&CHAR(10)&IFERROR(VLOOKUP(5,Workings!A165:C187,3,FALSE),\"\")&CHAR(10)&IFERROR(VLOOKUP(6,Workings!A165:C187,3,FALSE),\"\")&CHAR(10)&IFERROR(VLOOKUP(7,Workings!A165:C187,3,FALSE),\"\")","v":"LTV restricted to 75% due to plan\n\n\n\n\n\n"}]
[]
[null,null,"Property Location",null,null,"England",null,"Fees include VAT"]
[]
[null,null,"Property Type",null,null,"Other",null,"* Additional legal costs may apply, particularly if purchase\n** Val fee based on standard residential property"]
[]
[null,null,"Rental Income"]
[]
[null,null,"First charge Repayment Type"]
[]
[null,null,"First charge Fix Rate Remain",null,null,null,null,"Broker Income"]
[]
[null,null,"First charge Rem Term (m)",null,null,null,null,"Commission rate",null,null,0.02]
[]
[null,null,"First charge Balance",null,null,null,null,"Commission",null,null,{"$f":"F21*K51","v":7000}]
[]
[null,null,"First charge CMI",null,null,null,null,"Broker fee",null,null,{"$f":"F23","v":0},null,"Tel: 0800 032 5282"]
[]
[null,null,"First charge Stressed CMI",null,null,{"$f":"IF(F49<>\"Fixed >5yr\",IFERROR(IF(F47=\"Repayment\",PMT(RATE(F51,F55,-F53)+(Workings!B17/12),F51,-F53),F53*((F55/F53)+(Workings!B17/12))),0),F55)","v":0},null,"Total income",null,null,{"$f":"SUM(K55+K53)","v":7000},null,"Email: referrals@mercantiletrust.co.uk "]
[]
[]
[null,null,false]

{"sheet":"Workings","index":1,"n_rows":186,"n_cols":15,"rows_kept":186,"cut":false,"hidden":true,"merged":[]}
["Drop Downs"]
[]
["Fee Type","Location","Rate Type","Plan","Prop Type","Plan LTV","Status Points","Service Type","Security",null,"1st Type","1st rate","Purpose","Broker Fee Type","Term"]
["Added","England",{"$f":"IF(LEFT('Loan Calculator'!D15,7)=\"Express\",\"\",\"Variable\")","v":"Variable"},null,"Ex C'cil Flat / Mais",0.75,0,"Rolled","First Charge - Purchase","Yes","Repayment","Variable","Purchase","Percentage",1]
["Up-Front","Wales","Fixed for Term","BTL / Holiday Let Bridge","Freehold Flat",0.7,1,"Serviced","First Charge - Other","No","Interest Only","Fixed <5yr","Refurbishment","Amount",2]
[null,{"$f":"IF(LEFT('Loan Calculator'!D15,7)=\"Express\",\"\",\"Mainland Scotland\")","v":"Mainland Scotland"},null,"Equitable Charge Bridge","Other",0.6,2,null,"Second Charge",null,null,"Fixed >5yr","Consolidation",null,3]
[null,{"$f":"IF(LEFT('Loan Calculator'!D15,7)=\"Express\",\"\",\"Northern Ireland\")","v":"Northern Ireland"},null,{"$f":"IF(B14=\"First charge\",\"\",\"HO Business Loan\")","v":"HO Business Loan"},null,null,null,null,null,null,null,null,"Business",null,4]
[null,null,null,null,null,null,null,null,null,null,null,null,"Lease extension",null,5]
[null,null,null,null,null,null,null,null,null,null,null,null,"Other",null,6]
["Other calculated",null,null,null,null,null,null,null,null,null,null,null,null,null,7]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,8]
["Size",{"$f":"IF('Loan Calculator'!F21>Workings!B23,\"TOO HIGH\",\"OK\")","v":"OK"},null,null,null,null,null,null,null,null,null,null,null,null,9]
["2nd Ch bal",{"$f":"IF(AND(B16=\"Second charge\",'Loan Calculator'!F53=0),\"Y\",\"OK\")","v":"OK"},null,null,null,null,null,null,null,null,null,null,null,null,10]
["2nd Ch CMI",{"$f":"IF(AND(B16=\"Second charge\",'Loan Calculator'!F55=0),\"Y\",\"OK\")","v":"OK"},null,null,null,null,null,"Title Insurance ",null,null,null,null,null,null,11]
["Plan Code",{"$f":"B16&\" \"&'Loan Calculator'!D19 &\" SP\"","v":"First Charge 0 SP"},null,null,null,null,null,"%",2E-3,null,null,null,null,null,12]
["First / Second",{"$f":"IFERROR(LEFT('Loan Calculator'!D13,FIND(\"-\",'Loan Calculator'!D13)-2),'Loan Calculator'!D13)","v":"First Charge"},null,null,null,null,null,"Min",495,null,null,null,null,null,13]
["Stress Test pa",0.01,null,null,null,null,null,"IPT",0.12,null,null,null,null,null,14]
["Max DSCR",{"$f":"B19","v":1.25},null,null,null,null,null,"Gross for TI",{"$f":"'Loan Calculator'!F21+IF('Loan Calculator'!D25=\"Added\",'Loan Calculator'!F25,0)+IF('Loan Calculator'!D23=\"Added\",'Loan Calculator'!F23,0)","v":360500},null,null,null,null,null,15]
["DSCR BTL",1.25,null,null,null,null,null,"This Loan",{"$f":"IF('Loan Calculator'!D27=\"NO\",0,\nIF(I23=\"NO\",0,\nIF(I20=\"NORTHERN IRELAND\",0,\nIF(I24=\"SECOND CHARGE\",0,\nIF('Loan Calculator'!F21>300000,999,699)))))","v":999},null,null,null,null,null,16]
["DSCR Comm",1.4,"Max Loan (net)",null,null,null,null,"Property Location",{"$f":"'Loan Calculator'!F41","v":"England"},null,null,null,null,null,17]
[null,null,"Plan","Equitable charge","Express","NI",null,"Security",{"$f":"RIGHT('Loan Calculator'!D13,8)","v":"Purchase"},null,null,null,null,null,18]
[null,null,{"$f":"VLOOKUP(B15,A84:H149,5,FALSE)","v":500000},{"$f":"IF(LEFT('Loan Calculator'!D15,5)=\"Equit\",100000,C22)","v":500000},{"$f":"IF(AND('Loan Calculator'!D13=\"First Charge - Other\",B28=TRUE),1000000,C22)","v":500000},{"$f":"IF(LEFT('Loan Calculator'!F41,3)=\"Nor\",150000,C22)","v":500000},null,"Loan Value",{"$f":"'Loan Calculator'!F21","v":350000}]
["Max Loan all",{"$f":"MIN(C22:F22)","v":500000},{"$f":"IF(C22=MIN(C22:F22),1,\"\")","v":1},{"$f":"IF(D22<$C$22,1,\"\")","v":""},{"$f":"IF(E22<$C$22,1,\"\")","v":""},"",null,null,{"$f":"IF(AND(I21<>\"Purchase\",I22<=100000),\"NO\",\"YES\")","v":"YES"}]
["min loan",{"$f":"VLOOKUP(B15,A84:H149,4,FALSE)","v":25000},null,null,null,null,null,"Security",{"$f":"'Loan Calculator'!D13","v":"First Charge - Purchase"}]
["NI Max LTV",0.7]
["Overall Max LTV",0.75]
[]
["Express",{"$f":"'Loan Calculator'!C60","v":false}]
["Plan Rate",{"$f":"IFERROR(VLOOKUP(Workings!B15,Workings!A84:H149,IF(LEFT('Loan Calculator'!D17,5)=\"Fixed\",8,7),FALSE),\"No Plan\")","v":1.01E-2},null,null,null,null,"Fees","%","£"]
[null,null,null,null,null,null,"Normal",0.03]
[null,null,"£ Fees",null,null,null,"Equitable charge",0.04,499]
["This L Fee %",{"$f":"IF(LEFT('Loan Calculator'!D15,5)=\"Equit\",H31,H30)","v":0.03},"NI","Express Fee","Equitable charge",null,"Express",null,999]
["This L Fee £",{"$f":"SUM(C33:E33)","v":0},{"$f":"IF(AND(LEFT('Loan Calculator'!F41,2)=\"No\",'Loan Calculator'!D13=\"Second Charge\"),I33,0)","v":0},{"$f":"IF(B28=TRUE,I32,0)","v":0},{"$f":"IF(LEFT('Loan Calculator'!D15,5)=\"Equit\",I31,0)","v":0},null,"NI",null,899,"seconds only"]
["Is HO Business",{"$f":"IF(LEFT('Loan Calculator'!D15,3)=\"HO \",1,\"\")","v":""}]
["HO Business Min",null,"Max LTV"]
[null,"This quote","Property Value","Location","Plan","Property Type","Equitable charge","Property value Max LTV"]
["Plan max LTV",{"$f":"MIN(C37:G37)","v":0.75},{"$f":"IFERROR(IF('Loan Calculator'!F39=0,\"\",VLOOKUP('Loan Calculator'!F39,Workings!H37:I40,2,TRUE)),0)","v":0.75},{"$f":"IF(LEFT('Loan Calculator'!F41,3)=\"Nor\",B25,B26)","v":0.75},{"$f":"IFERROR(VLOOKUP(B15,A84:F149,6,FALSE),0)","v":0.75},{"$f":"IF('Loan Calculator'!F43=Workings!E4,IF('Loan Calculator'!F39>=150000,75%,65%),IF('Loan Calculator'!F43=Workings!E5,65%,75%))","v":0.75},{"$f":"IF(LEFT('Loan Calculator'!D15,5)=\"Equit\",0.7,0.75)","v":0.75}]
["is Purchase",{"$f":"IF(ISERROR(FIND(\"Purchase\",'Loan Calculator'!D13)),\"N\",\"Y\")","v":"Y"},{"$f":"IF(AND(C37=MIN($C$37:$G$37),C37<MAX($C$37:$G$37)),1,\"\")","v":""},{"$f":"IF(AND(D37=MIN($C$37:$G$37),D37<MAX($C$37:$G$37)),1,\"\")","v":""},{"$f":"IF(AND(E37=MIN($C$37:$G$37),E37<MAX($C$37:$G$37)),1,IF(E37=MIN(C37:G37),1,\"\"))","v":1},"","",75000,0.75]
[null,null,null,null,null,null,null,1000001,0.7]
[null,null,null,null,null,null,null,1500001,0.65]
[]
[]
["Legal Fees"]
["Limited co",{"$f":"250*1.2","v":300}]
["Leasehold",{"$f":"250*1.2","v":300}]
[]
["Legal Fees",null,null,"Residential","Commercial"]
["First charge","Loan min","Loan max","fee"]
[null,0,250000,840,1020]
[null,{"$f":"C49+1","v":250001},500000,960,1140]
[null,{"$f":"C50+1","v":500001},750000,1050,1230]
[null,{"$f":"C51+1","v":750001},1000000,1110,1290]
[null,{"$f":"C52+1","v":1000001},null,1200,1380]
[null,null,null,0,0]
["Bridging",0,0,0,0]
[null,{"$f":"C55+1","v":1},500000,{"$f":"1500*1.2","v":1800},1980]
[null,{"$f":"C56+1","v":500001},1000000,{"$f":"1750*1.2","v":2100},2280]
[null,{"$f":"C57+1","v":1000001},9999999999,2400,2580]
[]
["Valuation","Value min","Value Max","Fee","Heavy Refurb",null,"Valuation","Value min","Value Max","Fee"]
["E&W",0,200000,220,445,null,"Scot",0,100000,245]
[null,{"$f":"C61+1","v":200001},300000,250,495,null,null,{"$f":"I61+1","v":100001},200000,285]
[null,300001,350000,275,575,null,null,200001,300000,335]
[null,350001,400000,280,575,null,null,300001,{"$f":"I63+100000","v":400000},400]
[null,400001,500000,350,620,null,null,400001,{"$f":"I64+100000","v":500000},475]
[null,500001,600000,490,695,null,null,500001,{"$f":"I65+100000","v":600000},620]
[null,600001,700000,600,755,null,null,600001,{"$f":"I66+100000","v":700000},630]
[null,700001,800000,700,825,null,null,700001,750000,700]
[null,800001,900000,775,900,null,null,750001,800000,860]
[null,900001,1000000,850,1000,null,null,800001,{"$f":"I69+100000","v":900000},865]
[null,1000001,{"$f":"C70+200000","v":1200000},"On request",1200,null,null,900001,{"$f":"I70+100000","v":1000000},890]
[null,1200001,{"$f":"C71+200000","v":1400000},"On request",1350,null,null,1000001,99999999,"On request"]
[null,1400001,{"$f":"C72+200000","v":1600000},"On request",1550]
[null,1600001,{"$f":"C73+200000","v":1800000},"On request",1700]
[null,1800001,{"$f":"C74+200000","v":2000000},"On request",1850]
[null,2000001,{"$f":"C75+500000","v":2500000},"On request",2500]
[null,2500001,{"$f":"C76+500000","v":3000000},"On request",2800]
[null,3000001,{"$f":"C77+1000000","v":4000000},"On request",3300]
[null,4000001,{"$f":"C78+1000000","v":5000000},"On request",3800]
[null,5000001,9999999999,"On request","On request"]
[]
[]
["Plan Matrix",null,null,"Min Loan","Max Loan","Max LTV","V Rate","F Rate","£fee","%fee"]
["First Charge 0 SP",null,null,{"$f":"'Summary Matrix'!E4","v":25000},{"$f":"'Summary Matrix'!F4","v":500000},{"$f":"'Summary Matrix'!G4","v":0.75},{"$f":"'Summary Matrix'!H4","v":9.9000000000000008E-3},{"$f":"'Summary Matrix'!I4","v":1.01E-2}]
["First Charge 1 SP",null,null,{"$f":"'Summary Matrix'!E5","v":25000},{"$f":"'Summary Matrix'!F5","v":500000},{"$f":"'Summary Matrix'!G5","v":0.7},{"$f":"'Summary Matrix'!H5","v":1.01E-2},{"$f":"'Summary Matrix'!I5","v":1.03E-2}]
["First Charge 2 SP",null,null,{"$f":"'Summary Matrix'!E6","v":25000},{"$f":"'Summary Matrix'!F6","v":250000},{"$f":"'Summary Matrix'!G6","v":0.65},{"$f":"'Summary Matrix'!H6","v":1.03E-2},{"$f":"'Summary Matrix'!I6","v":1.0500000000000001E-2}]
["Second Charge 0 SP",null,null,{"$f":"'Summary Matrix'!E7","v":25000},{"$f":"'Summary Matrix'!F7","v":300000},{"$f":"'Summary Matrix'!G7","v":0.75},{"$f":"'Summary Matrix'!H7","v":1.01E-2},{"$f":"'Summary Matrix'!I7","v":1.03E-2}]
["Second Charge 1 SP",null,null,{"$f":"'Summary Matrix'!E8","v":25000},{"$f":"'Summary Matrix'!F8","v":250000},{"$f":"'Summary Matrix'!G8","v":0.7},{"$f":"'Summary Matrix'!H8","v":1.03E-2},{"$f":"'Summary Matrix'!I8","v":1.0500000000000001E-2}]
["Second Charge 2 SP",null,null,{"$f":"'Summary Matrix'!E9","v":25000},{"$f":"'Summary Matrix'!F9","v":100000},{"$f":"'Summary Matrix'!G9","v":0.65},{"$f":"'Summary Matrix'!H9","v":1.0500000000000001E-2},{"$f":"'Summary Matrix'!I9","v":1.0699999999999999E-2}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Maximum Lending","Small",null,null,"Rolled Loan"]
["Plan (S/L)",{"$f":"B15","v":"First Charge 0 SP"},null,null,"Gross",{"$f":"'Loan Calculator'!K19","v":361499}]
["Rate (S/L)",{"$f":"B29","v":1.01E-2},null,null,"Interest"]
["Max Sec Lend end",{"$f":"('Loan Calculator'!F39*Workings!B37)-'Loan Calculator'!F53","v":375000}]
["Max Sec Lend start",{"$f":"IF('Loan Calculator'!F33=\"Rolled\",ROUNDDOWN(B154/POWER(1+B153,'Loan Calculator'!F29),0),B154)","v":332398}]
[null,null,"max fee"]
["Br Fee %",0,0]
["Lender fee %",{"$f":"IF('Loan Calculator'!D25<>\"added\",0,B32)","v":0.03}]
["Fixed Br Fee",{"$f":"IF('Loan Calculator'!D23=\"added\",'Loan Calculator'!F23,0)","v":0},{"$f":"B159","v":0}]
["Fixed Lender Fee",{"$f":"IF('Loan Calculator'!D25<>\"added\",0,B33)","v":0}]
["Max Net",{"$f":"MIN(ROUNDDOWN((B155-B159-B160)/(1+B157+B158),0),Workings!B23)","v":322716},{"$f":"MIN(ROUNDDOWN((B155-IF(C159=\"X\",0,C159)-B160)/(1+IF(C157=\"x\",0,C157)+B158),0),Workings!B23)","v":322716}]
[]
["Warnings"]
["No Plan",{"$f":"IF(F165<>0,\"Y\",\"\")","v":"Y"}]
[0,"Applies","Warnings","Notes",null,{"$f":"SUM(F166:F180)","v":1}]
[{"$f":"A165+IF(C166=\"\",0,1)","v":0},{"$f":"IF(Workings!C37=0,1,\"\")","v":""},{"$f":"IF(B166=1,D166,\"\")","v":""},"Property value below minimim",null,{"$f":"IF(C166=\"\",\"\",1)","v":""}]
[0,{"$f":"IF(AND(LEFT('Loan Calculator'!D15,5)=\"Equit\",G38=1,'Loan Calculator'!K27>G37),1,\"\")","v":""},"",{"$f":"\"LTV restricted to \"&G37*100&\"% on equitable charge\"","v":"LTV restricted to 75% on equitable charge"},null,{"$f":"IF(C167=\"\",\"\",1)","v":""}]
[0,{"$f":"IF(AND(F38=1,'Loan Calculator'!K27>F37),1,\"\")","v":""},"",{"$f":"\"LTV restricted to \"&F37*100&\"% due to property type\"","v":"LTV restricted to 75% due to property type"},null,""]
[1,{"$f":"IF(AND(E38=1,'Loan Calculator'!K27>E37,'Loan Calculator'!K27<>\"\"),1,\"\")","v":1},"LTV restricted to 75% due to plan",{"$f":"\"LTV restricted to \"&E37*100&\"% due to plan\"","v":"LTV restricted to 75% due to plan"},null,1]
[1,{"$f":"IF(AND(D38=1,'Loan Calculator'!K27>D37),1,\"\")","v":""},"",{"$f":"\"LTV restricted to \"&D37*100&\"% due to location\"","v":"LTV restricted to 75% due to location"},null,""]
[1,{"$f":"IF(AND(C38=1,'Loan Calculator'!K27>C37,B166<>1),1,\"\")","v":""},"",{"$f":"\"LTV restricted to \"&C37*100&\"% due to property value\"","v":"LTV restricted to 75% due to property value"},null,""]
[1,{"$f":"IF(AND(LEFT('Loan Calculator'!D15,5)=\"Equit\",'Loan Calculator'!F21>100000),1,\"\")","v":""},{"$f":"IF(B172=1,D172,\"\")","v":""},"Max net loan £100,000 on equitable charge",null,""]
[{"$f":"A172+IF(C173=\"\",0,1)","v":1},{"$f":"IF(AND(LEFT('Loan Calculator'!D15,5)=\"Equit\",OR(LEFT('Loan Calculator'!F41,3)=\"Mai\",LEFT('Loan Calculator'!F41,3)=\"Nor\")),1,\"\")","v":""},"","Equitable charge only in England and Wales",null,""]
[1,null,null,null,null,""]
[1,{"$f":"IF(AND(B28=TRUE,LEFT('Loan Calculator'!F41,3)=\"Nor\"),1,\"\")","v":""},"","Packaged only in England, Wales or Scotland",null,""]
[1,null,null,null,null,""]
[1,{"$f":"IF(AND(F23=1,'Loan Calculator'!F21>F22),1,\"\")","v":""},"","Maximum net loan £150,000 in Northern Ireland",null,""]
[1,{"$f":"IF(AND(C23=1,'Loan Calculator'!F21>C22),1,\"\")","v":""},"",{"$f":"\"Maximum net loan for plan £\"&C22/1000&\",000\"","v":"Maximum net loan for plan £500,000"},null,""]
[1,{"$f":"IF(AND(LEFT('Loan Calculator'!D15,2)=\"HO\",Workings!B16=\"First charge\"),1,\"\")","v":""},"","HOBL only on second charges",null,""]
[1,{"$f":"IF(AND(LEFT('Loan Calculator'!D15,5)=\"Equit\",B16=\"First Charge\"),1,\"\")","v":""},{"$f":"IF(B180=1,D180,\"\")","v":""},"Equitable charge on second charges only",null,""]
[1,{"$f":"IF(B28=TRUE,1,\"\")","v":""},"","On Packaged there may be additional charges for NCI or Field Connect"]
[1,null,""]
[1,null,""]
[1,null,""]
[1,null,""]
[1,null,""]

{"sheet":"Heads of Terms","index":2,"n_rows":95,"n_cols":3,"rows_kept":95,"cut":false,"hidden":false,"merged":[[89,1,89,2],[94,1,94,2],[90,1,90,2],[91,1,91,2],[93,1,93,2],[92,1,92,2],[84,1,84,2],[85,1,85,2],[86,1,86,2],[87,1,87,2],[88,1,88,2],[83,1,83,2],[73,1,73,2],[74,1,74,2],[75,1,75,2],[76,1,76,2],[77,1,77,2],[78,1,78,2],[79,1,79,2],[80,1,80,2],[81,1,81,2],[82,1,82,2],[72,1,72,2],[61,1,61,2],[62,1,62,2],[63,1,63,2],[64,1,64,2],[65,1,65,2],[66,1,66,2],[68,1,68,2],[69,1,69,2],[70,1,70,2],[71,1,71,2],[67,1,67,2],[30,1,30,2],[7,1,7,2],[9,1,9,2],[50,1,50,2],[12,1,12,2],[13,1,14,1],[13,2,14,2],[16,1,17,1],[16,2,17,2],[19,1,19,2],[60,1,60,2],[52,1,52,2],[53,1,53,2],[54,1,54,2],[55,1,55,2],[56,1,56,2],[57,1,57,2],[58,1,58,2],[59,1,59,2]]}
[]
[]
[null,{"$f":"\"Date: \"&IF('Loan Calculator'!D4=\"\",\"\",DAY('Loan Calculator'!D4)&\"/\"&MONTH('Loan Calculator'!D4)&\"/\"&YEAR('Loan Calculator'!D4))","v":"Date: "}]
[null,"Our Ref:  "]
[]
[null,"Introducer Name:"]
[]
[null,"Heads of Terms"]
[]
[null,"Thank you for your enquiry.  We are pleased to confirm that based on the information provided to us, the application is acceptable in principle and this outlines the facility that we may be able to provide together with some of the terms and conditions that will apply."]
[]
[null,"Applicant Name:  ",{"$f":"IF('Loan Calculator'!E7=\"\",\"\",'Loan Calculator'!E7)","v":""}]
[null,"If the application is being made by a Limited Company, please note that all directors/shareholders in the business will need to provide a personal guarantee. Also a debenture over the property."]
[null,"Security Address:",{"$f":"IF('Loan Calculator'!E8=\"\",\"\",'Loan Calculator'!E8)","v":""}]
[]
[null,"Advised Estimated Valuation of:",{"$f":"IF('Loan Calculator'!F39=\"\",\"\",'Loan Calculator'!F39)","v":500000}]
[null,"Other Security Address: "]
[]
[null,"Advised Estimated Valuation of:","£"]
[]
[null,"Product:",{"$f":"'Loan Calculator'!D15 & \" - Bridge - \"& 'Loan Calculator'!D19&\"SP\"","v":"BTL / Holiday Let Bridge - Bridge - 0SP"}]
[null,"Loan Purpose: ",{"$f":"IF('Loan Calculator'!N8=\"\",\"Not stated\",'Loan Calculator'!N8)","v":"Not stated"}]
[null,"Loan Term:",{"$f":"'Loan Calculator'!F29&IF('Loan Calculator'!F29=1,\" month\",\" months \")","v":"12 months "}]
[null,{"$f":"IF(LEFT('Loan Calculator'!D17,5)=\"fixed\",\"Monthly / Annual Interest Rate (fixed for term):\",\"Monthly / Annual Interest Rate (variable):\")","v":"Monthly / Annual Interest Rate (fixed for term):"},{"$f":"'Loan Calculator'!F31","v":"No Plan"}]
[null,"Net Loan:",{"$f":"'Loan Calculator'!F21","v":350000}]
[null,"Gross Loan: ",{"$f":"'Loan Calculator'!K19","v":361499}]
[null,"Where detailed below, fees are included in the gross loan amount and will be deducted from the advance on completion."]
[null,"Repayment Method:  ",{"$f":"'Loan Calculator'!F33","v":"Rolled"}]
[null,{"$f":"IF('Loan Calculator'!F33=\"rolled\",\"Rolled Interest (Approximate):\", \"Monthly Interest\")","v":"Rolled Interest (Approximate):"},{"$f":"IF('Loan Calculator'!F33=\"rolled\",'Loan Calculator'!K23, 'Loan Calculator'!K21)","v":46332}]
[null,{"$f":"IF('Loan Calculator'!F33=\"rolled\",\"\", \"Monthly serviced interest payments will cover interest only, the full capital balance will not be reduced throughout the term. \")","v":""}]
[]
[null,"Exit Strategy:",{"$f":"IF('Loan Calculator'!N7=\"\",\"Not stated\",'Loan Calculator'!N7)","v":"Not stated"}]
[null,"Total Repayable at the End of the Loan Term including Discharge Fee (Approximate): ",{"$f":"IFERROR(C26+C35+IF('Loan Calculator'!F33=\"rolled\",C29, 0),\"\")","v":408076}]
[null,"Early Repayment Charge: ","None"]
[null,"Discharge Fee:",245]
[null,"Late Redemption Penalty:","5% of the opening balance"]
[]
[null,"Fees (if added to the mortgage these will be deducted from the gross loan amount on completion):"]
[]
[null,"Broker Fee:",{"$f":"'Loan Calculator'!F23","v":0}]
[null,{"$f":"IF('Loan Calculator'!D23=\"added\",\"You have asked for this fee to be added to your loan, it will therefore be interest bearing. This will be deducted from the gross loan on completion and paid to your Broker.\", \"You have chosen to pay your Broker this fee up front.\")","v":"You have asked for this fee to be added to your loan, it will therefore be interest bearing. This will be deducted from the gross loan on completion and paid to your Broker."}]
[null,"Lender Fee:",{"$f":"'Loan Calculator'!F25","v":10500}]
[null,{"$f":"IF('Loan Calculator'!D25=\"added\",\"You have asked for this fee to be added to your loan, it will therefore be interest bearing. This will be deducted from the gross loan on completion.\", \"You have chosen to pay this fee up front. This must be paid to us before the mortgage completes.\")","v":"You have asked for this fee to be added to your loan, it will therefore be interest bearing. This will be deducted from the gross loan on completion."}]
[null,"Valuation Fee, only payable if does not pass AVM: (not included in gross loan)",{"$f":"'Loan Calculator'!K39","v":350}]
[null,"Including VAT.  Paid directly to the valuer. Based on the estimated property value"]
[null,{"$f":"IF(LEFT('Loan Calculator'!D13,12)=\"First charge\",\"Legal Fee:\",\"\")","v":"Legal Fee:"},{"$f":"IF(LEFT('Loan Calculator'!D13,12)=\"First charge\",'Loan Calculator'!K37,\"\")","v":1800}]
[null,{"$f":"IF(LEFT('Loan Calculator'!D13,12)=\"First charge\",\"Including VAT.  This fee is for 1st charges Only. Prior to commencement of legal due diligence, the borrowers solicitor\"&\" will be required to provide our solicitor with an undertaking to pay its accrued legal fees in full, whether or not the loan completes. This fee may change once solicitors are instructed and work has begun.\",\"\")","v":"Including VAT.  This fee is for 1st charges Only. Prior to commencement of legal due diligence, the borrowers solicitor will be required to provide our solicitor with an undertaking to pay its accrued legal fees in full, whether or not the loan completes. This fee may change once solicitors are instructed and work has begun."}]
[null,"Commission:",{"$f":"'Loan Calculator'!K53","v":7000}]
[null,"This is the amount we will pay to your broker upon completion. This does not form part of the loan agreement"]
[]
[null,"These Heads of Terms are valid for 14 days from the date of issue"]
[]
[null,"Please note that interest on your loan will be charged on a daily rate based on the actual number of days in any month.  As the number of days in a month differ from month to month, we have assumed that there are 30.4 days (365 days/12) in each month for the purpose of this illustration.  This means the actual interest charged (and therefore your final repayment instalment) may be higher than illustrated. "]
[null,"This is not a formal loan offer.  These figures are based on un-verified information that has been provided to us.  Mercantile Trust Ltd will only be able to provide the applicant with a formal loan offer when we are in receipt of and satisfied with the following requirements:"]
[]
[null,"·         Fully completed and signed application pack."]
[null,"·         Proof of 12 months' mortgage or rent history (if not on the credit search)."]
[null,"·         The most recent bank statement showing the rent being received."]
[null,"·         Certified valid identity – Passport or Driving Licence (if the customer fails our electronic ID check)."]
[null,"·         Certified proof of address for last 3 years – Up to date Council tax bill. Bank or building society statement, utility bill, dated within the last 2 months. (if not on the electoral roll on           the credit search)."]
[null,"·         Where refurbishment forms part of the exit strategy but not included in the advance, proof of refurbishment funds will be required (typically by receipt of a bank statement)."]
[null,"·         Full Residential Property Valuation with a valuer on Mercantile Trust Ltd panel."]
[null,"·         If the property is currently rented;"]
[null,"o  A copy of the most recent Energy Performance Certificate rated E or above."]
[null,"o    Copy of current tenancy agreement(s)."]
[null,"o    Proof the tenancy deposit is in a recognised scheme."]
[null,"·         We can accept no-search indemnity insurance where the matter proceeds as a refinance and there have been no works carried out to the property in the last 12 months, otherwise full searches will be required. Full searches will always be required on purchase applications and must not be more than six months old at completion. "]
[null,"·      Confirmation of the exit strategy is required."]
[null,"·      Copy of building insurance schedule with Mercantile Trust Ltd’s interest noted for 1st charges– Reinstatement value required. "]
[null,"All documentation to be emailed to mortgagedesk@mercantiletrust.co.uk"]
[]
[null,"Please note the following will be conducted by Mercantile Trust Ltd:"]
[]
[null,"·         Pre-Completion call to be completed with all applicants."]
[null,"·         Equifax credit search to be conducted on all properties."]
[]
[]
[null,"Valuation: where the applicant is ready to proceed the valuation can be instructed via:"]
[]
[null,"Pure Panel Management: 0191 214 2459 - residential properties only"]
[null,"VAS Panel Management:  01642 262 217 - refurbishment, residential & commercial properties"]
[]
[null,"Solicitors: Mercantile Trust Ltd will instruct solicitors when our underwriting criteria has been satisfied."]
[]
[null,"Mercantile Trust Ltd is unable to lend where this mortgage would become a regulated mortgage contract under current FCA rules.  "]
[]
[null,"Property Type: Mercantile Trust Ltd will consider loans on all types of residential properties although certain properties may have LTV restrictions imposed. Please refer to our current matrix. The property types outlined below are ineligible for our product range."]
[null,"·         A property that is non-traditional and not suitable to the area "]
[null,"·         Grade I or II* (star) listed properties."]
[null,"·         Shared ownership/Housing Association properties"]
[null,"·         Properties valued at less than £75,000"]
[null,"·        Maximum LTVs apply for the following:    Freehold Flats max LTV 65%, Ex Local Authority flats/maisonettes under £150k max LTV 65%,"]
[null," Properties over £1.5m max LTV 65%, Properties over £1m max LTV 70%."]
[null,"·         Properties where potential environmental damage exists"]
[null,"·         Leasehold properties with less than 50 years remaining "]

{"sheet":"Aff. Calculator","index":3,"n_rows":138,"n_cols":34,"rows_kept":138,"cut":false,"hidden":false,"merged":[[48,23,48,24],[18,33,18,34],[34,28,34,31],[38,28,38,31],[22,14,22,15],[36,19,37,23],[36,24,37,25],[38,33,38,34],[22,33,22,34],[36,33,36,34],[34,33,34,34],[22,19,28,25],[30,24,30,25],[34,24,34,25],[32,24,32,25],[36,28,36,31],[14,19,14,25],[14,10,14,16],[14,28,14,34],[2,14,2,19],[20,33,20,34],[6,1,9,20],[6,27,6,33],[8,27,8,33],[10,27,10,29],[18,24,18,25],[2,1,2,8],[10,1,11,17],[14,1,14,7],[24,28,32,34],[46,9,46,12],[47,9,47,12],[47,13,47,15],[16,24,16,25],[16,10,18,16],[45,23,45,24],[46,23,46,24],[47,23,47,24],[24,10,26,16],[28,14,28,15],[31,9,33,17],[34,9,34,17],[16,33,16,34],[41,1,42,7],[43,9,43,12],[44,9,44,12],[44,13,44,15],[20,24,20,25],[16,1,20,7],[20,14,20,15],[37,1,39,7],[36,1,36,7]]}
[]
[null,"MERCANTILE TRUST BRIDGING AFFORDABILITY MODEL"]
[null,{"$f":"'Loan Calculator'!C5","v":"Effective from 12th June 2025"}]
[]
[null,"Step 1 - Instructions",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Step 2 - Applicant Details"]
[]
[null,"To allow accurate an Affordability Decision to be made, it is essential that ALL cells highlighted in light blue are completed in FULL, across Steps 2 to 5. Once these cells have all been accurately completed, a decision will be returned in the 'Affordability Decision' box. To proceed with the application there must be an outcome of Pass.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Applicant 1 Name"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Applicant 2 Name"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Postcode"]
[]
[]
[]
[null,"Step 3 - Household Size",null,null,null,null,null,null,null,null,"Step 4 - Monthly Income",null,null,null,null,null,null,null,null,"Step 5 - Expenditure",null,null,null,null,null,null,null,null,"AFFORDABILITY DECISION"]
[]
[null,"In this section please enter the relevant number of Adults/Dependants living in the property. Any Dependants aged 18 or over should be classed as an Adults.",null,null,null,null,null,null,null,null,"Please enter the Applicant(s) GUARANTEED income on a 'NET' monthly basis.",null,null,null,null,null,null,null,null,null,null,null,"Residential Mortgage(s) Bal","£",null,null,null,null,null,null,null,"Total resi and new","£",{"$f":"IFERROR(IF(Workings!B34=1,'Loan Calculator'!K23+'Loan Calculator'!F57,0),0)","v":0}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Residential Mortgage(s)/Rent Pmt","£",null,null,null,null,null,null,null,"Total Outgoings","£",{"$f":"IFERROR(IF(Workings!B34<>1,(Y19+Y21+Y37),Y37+AH17),\"\")","v":0}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Applicant 1 - £",5000,null,null,null,null,null,null,null,"BTL Loan shortfall","£",{"$f":"IFERROR(IF(D47>=0,\"0.00\",D48),0)","v":0},null,null,null,null,null,null,"Total Net Income","£",{"$f":"N45","v":5000}]
[]
[null,null,null,null,null,"Number of Adults   ",1,null,null,null,null,null,null,"Applicant 2 - £",null,null,null,null,null,"In the section below please provide details on the monthly outgoings that will not be cleared with the proceeds of this loan. Please note that no further details are required on expenditures other than those listed below.",null,null,null,null,null,null,null,null,null,null,null,"Outcome",null,{"$f":"X58","v":"TBC"}]
[]
[null,null,null,null,null,"Number of Dependants   ",0,null,null,null,"Please enter the gross annual income for the entire household",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(W58=99,H117,IF(W58=0,H114,IF(W58=1,H116,IF(W58=2,H115))))","v":"Applicant's Household income (gross and net) must be entered for a successful affordability decision to be made."}]
[]
[null,null,null,null,null,{"$f":"IF(OR(H83=0,H83=99),\" \",IF(OR(H83=1,H83=2,H83=3,H83=4,H83=5),\"Age in years of Dependant 1   \",\" \"))","v":" "}]
[]
[null,null,null,null,null,{"$f":"IF(OR(H83=0,H83=99),\" \",IF(OR(H83=2,H83=3,H83=4,H83=5),\"Age in years of Dependant 2   \",\" \"))","v":" "},null,null,null,null,null,null,null,"GROSS ANNUAL Income - £"]
[]
[null,null,null,null,null,{"$f":"IF(OR(H83=0,H83=99),\" \",IF(OR(H83=3,H83=4,H83=5),\"Age in years of Dependant 3   \",\" \"))","v":" "},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Credit Cards","£"]
[null,null,null,null,null,null,null,null,null,"Our mandated underwriters can be reached on: 0800 032 5282"]
[null,null,null,null,null,{"$f":"IF(OR(H83=0,H83=99),\" \",IF(OR(H83=4,H83=5),\"Age in years of Dependant 4   \",\" \"))","v":" "},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Unsecured Credit","£"]
[]
[null,null,null,null,null,{"$f":"IF(OR(H83=0,H83=99),\" \",IF(OR(H83=5),\"Age in years of Dependant 5   \",\" \"))","v":" "},null,null,null,"mortgagedesk@mercantiletrust.co.uk",null,null,null,null,null,null,null,null,null,null,null,null,"School Fees / Childcare / Other","£",null,null,null,null,"ONS Cost",null,null,null,"£",{"$f":"W56","v":843.03000000000009}]
[]
[null,{"$f":"IF(OR(H74=\"TBC\",H74=99,H83=\"TBC\",H83=99),F56,IF(H64>H65,F56,IF(H65>H64,F59,\" \")))","v":" "},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"TOTAL CREDIT COMMITMENTS NOT    . BEING CLEARED WITH THIS LOAN  £",null,null,null,null,{"$f":"SUM(Y31:Z36)","v":0},null,null,null,"Surplus",null,null,null,"£",{"$f":"N45-SUM(Y19,N48,Y21,Y37,AH35)","v":4156.97}]
[null,{"$f":"IF(J85=\"N\",H56,IF(J86=\"N\",H57,IF(H64>H65,H59,IF(H65>H64,H60,\" \"))))","v":" "}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Mortgage Buffer",null,null,null,null,{"$f":"N48","v":0}]
[]
[]
[]
[]
[null,null,null,"Rental",null,null,null,null,null,"Total Net Outputs",null,null,null,null,null,null,null,null,null,null,"ONS Calculation"]
[null,null,"PMT * DSCR",{"$f":"'Loan Calculator'!P23*DSCR","v":{"$error":"#VALUE!"}},null,null,null,null,null,"Net Income ",null,null,null,{"$f":"O21+O23","v":5000},null,null,null,null,null,null,null,null,"No","Factor",null,"Score"]
[null,null,"actual rent",{"$f":"SUM('Loan Calculator'!F45)","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"1st Adult",null,1,{"$f":"'ONS Figures'!B4","v":1},null,{"$f":"W46*X46","v":1}]
[null,null,"surplus",{"$f":"SUM(D46-D45)","v":{"$error":"#VALUE!"}},null,null,null,null,null,"Mortgage Buffer",null,null,null,null,null,null,null,null,null,null,"Other Adult",null,{"$f":"G23-W46","v":0},{"$f":"'ONS Figures'!B5","v":0.7},null,{"$f":"W47*X47","v":0}]
[null,null,"negative surplus",{"$f":"SUM(D47-D47)-D47","v":{"$error":"#VALUE!"}},null,null,null,null,null,0.01,null,null,null,{"$f":"(Y17*J48)/12","v":0},null,null,null,null,null,null,"Older",null,{"$f":"COUNTIF(G27:G36,\">16\")","v":0},{"$f":"'ONS Figures'!B6","v":0.67},null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Younger",null,{"$f":"G25-W48","v":0},{"$f":"'ONS Figures'!B7","v":0.33},null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total",null,null,null,null,{"$f":"SUM(Z46:Z49)","v":1}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Buffer ",null,null,null,null,{"$f":"'ONS Figures'!B8","v":150}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Quintile",null,{"$f":"VLOOKUP(O29,'ONS Figures'!L4:N8,3,TRUE)","v":1}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Model",null,{"$f":"IF(G25>0,\"A56\",IF(G23=1,\"A26\",\"A29\"))","v":"A26"}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Lookup code",null,{"$f":"\"Q\"&W52&W53","v":"Q1A26"}]
[null,null,"Screen Validation",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"1st adult",null,{"$f":"HLOOKUP(W54,'ONS Figures'!C12:U33,22,FALSE)","v":693.03000000000009}]
[null,null,null,null,null,"Please Complete:",null,"Please enter the Number of Household Adults.",null,null,null,null,null,null,null,null,null,null,null,null,"Family",null,{"$f":"(W55*Z50)+Z51","v":843.03000000000009}]
[null,null,null,null,null,null,null,"Please enter the Number of Household Dependants."]
[null,null,null,null,null,null,null,"Please enter the Number of Household Adults & Dependants.",null,null,null,null,null,null,null,null,null,null,null,null,"Outcome",null,{"$f":"IF(I125>0,99,IF(AH37>0,1,0))","v":99},{"$f":"IF(I125>0,\"TBC\",IF(W58=99,\"TBC\",IF(W58=0,\"FAIL\",IF(W58=1,\"PASS\",IF(W58=2,\"FAIL\")))))","v":"TBC"}]
[null,null,null,null,null,"Warning!!!",null,"Please enter the Ages of the Dependants."]
[null,null,null,null,null,null,null,"Too many Ages entered for the number of dependants selected."]
[]
[]
[]
[null,null,null,null,null,"Deps Total",null,{"$f":"G25","v":0}]
[null,null,null,null,null,"Ages Entered Count",null,{"$f":"COUNT(G27,G29,G31,G33,G35)","v":0}]
[]
[]
[null,null,null,null,null,"Number of adults",null,"TBC"]
[null,null,null,null,null,null,null,1]
[null,null,null,null,null,null,null,2]
[null,null,null,null,null,null,null,3]
[null,null,null,null,null,null,null,4]
[null,null,null,null,null,null,null,5]
[null,null,null,null,null,null,"Score",{"$f":"IF(G23=\"\",99,IF(G23=\"TBC\",99,IF(G23=1,1,IF(G23=2,2,IF(G23=3,3,IF(G23=4,4,IF(G23=5,5)))))))","v":1}]
[]
[null,null,null,null,null,"Number of dependants",null,"TBC"]
[null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,1]
[null,null,null,null,null,null,null,2]
[null,null,null,null,null,null,null,3]
[null,null,null,null,null,null,null,4]
[null,null,null,null,null,null,null,5]
[null,null,null,null,null,null,"Score",{"$f":"IF(G25=\"\",99,IF(G25=\"TBC\",99,IF(G25=0,0,IF(G25=1,1,IF(G25=2,2,IF(G25=3,3,IF(G25=4,4,IF(G25=5,5))))))))","v":0}]
[]
[null,null,null,null,null,null,null,"Adults field completed?",null,{"$f":"IF(OR(H74=\"\",H74=\"TBC\",H74=99),\"N\",\"Y\")","v":"Y"}]
[null,null,null,null,null,null,null,"Dependants field completed?",null,{"$f":"IF(OR(H83=\"\",H83=\"TBC\",H83=99),\"N\",\"Y\")","v":"Y"}]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,"Pass Msg","This application has passed the DTI decision, as it is below the maximum threshold of 55%."]
[null,null,null,null,null,"Fail Msg","This application has failed the DTI decision, as it is above the maximum threshold of 55%. "]
[null,null,null,null,null,"TBC Msg",{"$f":"IF(I97=0,K97,IF(I98=0,K98,IF(I99=0,K99,\" \")))","v":"Applicant's Household income (gross and net) must be entered for affordability decision to be made."}]
[null,null,null,null,null,"Fields Completed"]
[null,null,null,null,null,"Income",null,null,{"$f":"IF(J144>0,1,0)","v":0},null,"Applicant's Household income (gross and net) must be entered for affordability decision to be made."]
[null,null,null,null,null,"Mortgage",null,null,{"$f":"IF(Y19>0,1,0)","v":0},null,"Applicant's Mortgage Payment must be entered for affordability decision to be made."]
[null,null,null,null,null,"Loan",null,null,{"$f":"IF(Y21>0,1,0)","v":0},null,"Applicant's Proposed MTL Loan Payment must be entered for affordability decision to be made."]
[null,null,null,null,null,"Unsecured",null,null,{"$f":"IF(Y37>0,1,0)","v":0}]
[null,null,null,null,null,null,null,"Total",{"$f":"COUNTIF(I97:I99,1)","v":0}]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,"AFFORDABILITY DECISION"]
[]
[]
[]
[]
[]
[null,null,null,null,null,"Display",null,"This application has failed the affordability decision, as the available income is not sufficient to cover the outgoings detailed, and based upon national averages, still leave sufficient money to cover all other expenditures in a sustainable manner for the life of the loan. "]
[null,null,null,null,null,null,null,"This application has failed the affordability decision, as the available income is not sufficient to cover the outgoings detailed, and based upon national averages, still leave sufficient money to cover all other expenditures in a sustainable manner for the life of the loan. "]
[null,null,null,null,null,null,null,"This application has passed the affordability decision, and no further documentation is required to support the affordability on this application."]
[null,null,null,null,null,null,null,{"$f":"IF(I121=\"N\",K121,IF(I122=\"N\",K122,IF(I123=\"N\",K123,IF(I124=\"N\",K124,\" \"))))","v":"Applicant's Household income (gross and net) must be entered for a successful affordability decision to be made."}]
[]
[null,null,null,null,null,null,"Fields Completed"]
[]
[null,null,null,null,null,null,"Household Size",null,{"$f":"IF(B38=\" \",\"Y\",\"N\")","v":"Y"},null,"Applicant's Household size must be entered for a successful affordability decision to be made."]
[null,null,null,null,null,null,"Income",null,{"$f":"IF(AND(O21>0,O29>0),\"Y\",\"N\")","v":"N"},null,"Applicant's Household income (gross and net) must be entered for a successful affordability decision to be made."]
[null,null,null,null,null,null,"Mortgage",null,{"$f":"IF(LEFT('Loan Calculator'!D15,2)=\"HO\",\"Y\",IF(Y19<>\"\",\"Y\",\"N\"))","v":"N"},null,"Applicant's Mortgage Payment must be entered for a successful affordability decision to be made."]
[null,null,null,null,null,null,"Loan",null,{"$f":"IF(Y21>0,\"Y\",\"N\")","v":"N"},null,"Applicant's Proposed MT Rental shortfall amount must be entered for a successful affordability decision to be made."]
[null,null,null,null,null,null,null,"Total",{"$f":"COUNTIF(I121:I124,\"N\")","v":3}]
[]
[]
[]
[]
[]
[null,null,null,null,null,"Commitment Types"]
[null,null,null,null,null,"Please Select…"]
[null,null,null,null,null,"School Fees"]
[null,null,null,null,null,"Medical Expenses"]
[null,null,null,null,null,"Credit Card"]
[null,null,null,null,null,"Loan"]
[null,null,null,null,null,"Hire Purchase"]
[null,null,null,null,null,"Other"]

{"sheet":"Summary Matrix","index":4,"n_rows":12,"n_cols":9,"rows_kept":12,"cut":false,"hidden":true,"merged":[[1,7,1,8]]}
[]
[null,null,null,null,null,null,null,"Rate (Monthly)"]
[null,"Plan Matrix",null,null,"Min Loan","Max Loan","Max LTV","Variable","Fixed for Term"]
[null,"First Charge 0 SP",null,null,25000,500000,0.75,9.9000000000000008E-3,1.01E-2]
[null,"First Charge 1 SP",null,null,25000,500000,0.7,1.01E-2,1.03E-2]
[null,"First Charge 2 SP",null,null,25000,250000,0.65,1.03E-2,1.0500000000000001E-2]
[null,"Second Charge 0 SP",null,null,25000,300000,0.75,1.01E-2,1.03E-2]
[null,"Second Charge 1 SP",null,null,25000,250000,0.7,1.03E-2,1.0500000000000001E-2]
[null,"Second Charge 2 SP",null,null,25000,100000,0.65,1.0500000000000001E-2,1.0699999999999999E-2]
[]
[null,"For full details please see our Broker Portal"]
[null,"https://www.mercantiletrust.co.uk/intermediary-portal/Account/Login"]

{"sheet":"ONS Figures","index":5,"n_rows":33,"n_cols":21,"rows_kept":33,"cut":false,"hidden":true,"merged":[]}
["Updated 04.2025"]
[]
["Multiple","Factor",null,"Quintile",1,2,3,4,5,null,null,"from (y)","From (m)"]
["1st adult",1,null,"a26",1,1,1,1,1,null,1,0,null,1]
["Ad adult",0.7,null,"a29",1.7,1.7,1.7,1.7,1.7,null,2,19760,{"$f":"L5/12","v":1646.6666666666667},2]
["Older ch 17-18",0.67,null,"a56",1.8090702947845807,2.1695473251028807,2.3509539842873179,2.4627822944896116,2.5488372093023255,null,3,32604,{"$f":"L6/12","v":2717},3]
["young ch 0-16",0.33,null,null,null,null,null,null,null,null,4,48516,4043,4]
["Buffer",150,null,null,null,null,null,null,null,null,5,73008,6084,5]
[]
["Monthly 1st adult",null,"Pasted Values"]
[null,null,"Q1",null,null,null,"Q2",null,null,null,"Q3",null,null,null,"Q4",null,null,null,"Q5"]
[null,null,{"$f":"C11&C13","v":"Q1A26"},{"$f":"C11&D13","v":"Q1A29"},{"$f":"C11&E13","v":"Q1A56"},null,{"$f":"G11&G13","v":"Q2A26"},{"$f":"G11&H13","v":"Q2A29"},{"$f":"G11&I13","v":"Q2A56"},null,{"$f":"K11&K13","v":"Q3A26"},{"$f":"K11&L13","v":"Q3A29"},{"$f":"K11&M13","v":"Q3A56"},null,{"$f":"O11&O13","v":"Q4A26"},{"$f":"O11&P13","v":"Q4A29"},{"$f":"O11&Q13","v":"Q4A56"},null,{"$f":"S11&S13","v":"Q5A26"},{"$f":"S11&T13","v":"Q5A29"},{"$f":"S11&U13","v":"Q5A56"}]
[null,null,"A26","A29","A56",null,"A26","A29","A56",null,"A26","A29","A56",null,"A26","A29","A56",null,"A26","A29","A56"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Total first adult cost (CT AA)",null,693.03000000000009,700.51,552.52,null,908.4,794.5,629.11,null,1081.75,847.95999999999992,672.13,null,1315.5100000000002,975.25999999999976,755.64999999999986,null,1582.28,1228.77,934.80000000000007]