{"text_format":"1","workbook":"xlsx","n_sheets":12,"sheets":["Proposal","Interest Rate","ILO","NDI","Restriction Text","Tax Calc","MAX LTV","Variables","Messaging","FX","Mortgage Rates","Sheet1"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":1,"defined_names":121,"comments":0,"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":"Proposal","index":0,"n_rows":144,"n_cols":18,"rows_kept":144,"cut":false,"hidden":false,"merged":[[60,0,60,15],[143,0,155,15],[139,0,141,15],[134,0,134,15],[34,4,34,7],[67,1,67,12],[68,1,68,12],[77,1,77,7],[71,1,71,12],[36,4,36,7],[69,1,69,12],[70,1,70,12],[98,0,98,15],[99,0,99,15],[61,0,61,15],[12,0,12,11],[38,4,38,7],[40,4,40,7],[49,5,49,6],[40,13,40,15],[38,13,38,15],[15,4,15,7],[17,4,17,7],[23,4,23,7],[27,4,27,7],[19,4,19,7],[21,4,21,7],[25,4,25,7],[32,4,32,7],[32,10,32,15]]}
[]
[]
[null,null,null,null,"Single","Paye","Private Sector"]
[null,null,null,null,"Married","Self Employed","Public Sector"]
[null,null,null,null,"Widowed"]
[]
[]
[]
[]
[]
[]
[]
["KBC Mortgage NDI Calculator"]
[null,null,null,null,null,"Rate Chosen",null,null,"Please ensure rate is appropriate for LTV chosen.",null,null,null,null,"Stressed Rate",null,{"$f":"$G$14+2%","v":0.02}]
[]
["Date",null,null,null,{"$f":"TODAY()","v":"2018-12-14"},null,null,null,null,null,"Loan sought",{"$f":"IF(AND(Loan_Sought<50000,$E$24<>\"Top Up/Equity Release\"),\"Below Minimum Loan Amount\",IF(AND(Loan_Sought<20000,$E$24=\"Top Up/Equity Release\"),\"Below Minimum Top Up Amount\",\"\"))","v":"Below Minimum Loan Amount"}]
[]
["Agreement Ref",null,null,null,null,null,null,null,null,null,"Purchase Price"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null," "]
["Name (Applicant 1)",null,null,null,null,null,null,null,null,null,"Max LTV (Based on CBI Regulations)",null,null,null,null,{"$f":"IFERROR('MAX LTV'!C3,\"\")","v":""}]
[]
["Name (Applicant 2)",null,null,null,null,null,null,null,null,null,"Loan to Value",null,null,null,null,{"$f":"IF(P18<=0,\"\",P16/P18)","v":""}]
[]
["Lending Purpose",null,null,null,null,null,null,null,null,null,"Borrower Age"]
[]
["Property Type",null,null,null,{"$f":"IFERROR(VLOOKUP(E24,Variables!$A$96:$B$103,2,0),\"\")","v":""},null,null,null,null,null,"Max Term Permitted (Years)",null,null,null,null,{"$f":"IFERROR(IF(OR($E$24=\"RIP Purchase\",$E$24=\"RIP Remortgage\",$E$24=\"Holiday Home\"),MIN(IF(Proposal!P24<'MAX LTV'!H6,'MAX LTV'!E3,'MAX LTV'!D3),'MAX LTV'!J3-Proposal!P24),MIN(IF(Proposal!P24<'MAX LTV'!H6,'MAX LTV'!E3,'MAX LTV'!D3),'MAX LTV'!I3-Proposal!P24)),\"\")","v":""}]
[]
["Location",null,null,null,null,null,null,null,null,null,"Term Sought (Years)",{"$f":"IF(Term_Sought>$P$26,\"Exceeds Maximum Term\",IF(Term_Sought<5,\"Below Minimum Term\",IF(AND($E$24=\"Switcher\",Term_Sought>30),\"Exceeds Maximum Term\",IF(AND(OR($E$24=\"RIP Purchase\",$E$24=\"RIP Remortgage\",$E$24=\"Holiday Home\"),Term_Sought>20),\"Exceeds Maximum Term\",\"\"))))","v":"Below Minimum Term"}]
[]
["Applicant Details"]
[null,null,null,null,"Applicant 1",null,null,null,null,null,"Applicant 2"]
[]
["Status"]
[]
["Joint application"]
[]
["No of Dependants"]
[]
["Employment Type",null,null,null,null,null,null,null,null,null,"Employment Type"]
[]
["Employment Sector",null,null,null,null,null,null,null,null,null,"Employment Sector"]
[]
["Net Income Calculation"]
[null,null,null,null,"Applicant 1",null,null,null,null,null,"Applicant 2"]
[null,null,null,null,"Actual",null,null,"Allowable",null,null,null,null,null,"Actual",null,"Allowable"]
["Basic Income",null,null,null,null,null,null,{"$f":"MIN(E46*Variables!$B$21,N(E46))","v":0},null,null,"Basic Income",null,null,null,null,{"$f":"MIN(N46*Variables!C21,N(N46))","v":0}]
[]
["Guaranteed Car/Garda Rent/Shift Allowance",null,null,null,null,null,null,{"$f":"E48*Variables!B22","v":0},null,null,"Car Allowance and/or Garda Rent Allowance",null,null,null,null,{"$f":"MIN(N48*Variables!C22,N(N48))","v":0}]
[]
["Other Variable Income",null,null,null,null,null,null,{"$f":"E50*Variables!B23","v":0},null,null,"Other Variable Income",null,null,null,null,{"$f":"MIN(N50*Variables!C23,N(N50))","v":0}]
[]
[null,null,null,null,null,null,null,"----------------",null,null,null,null,null,null,null,"-----------------------"]
["Pre-tax Annual Income",null,null,null,null,null,null,{"$f":"H46+H48+H50","v":0},null,null,"Pre-tax Annual Income",null,null,null,null,{"$f":"P46+P48+P50","v":0}]
[]
["Combined Pre-tax Annual Income",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"H53+P53","v":0}]
[]
["Combined Net Annual Income*",null,null,null,null,null,null,null," ",null,null,null,null,null,null,{"$f":"IFERROR(MIN(P55,IF(OR(APPLICATION_STATUS=1,APPLICATION_STATUS=3),'Tax Calc'!D73,'Tax Calc'!E73)),\"\")","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"--------------------",null,"--------------------"]
["Combined Net Monthly Income (Divided by 12)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IFERROR(P57/12,\"\")","v":""}]
[]
[{"$f":"IF(AND(OR($E$24=\"RIP Purchase\",$E$24=\"RIP Remortgage\"),$E$35=\"No\",$H$53<50000),\"Below Gross Qualifying Income\",IF(AND(OR($E$24=\"RIP Purchase\",$E$24=\"RIP Remortgage\"),$E$35=\"Yes\",($H$53+$P$53)<75000),\"Below Gross Qualifying Income\",IF(AND($E$35=\"No\",$H$53<30000),\"Below Gross Qualifying Income\",IF(AND($E$35=\"Yes\",($H$53+$P$53)<40000),\"Below Gross Qualifying Income\",\"\"))))","v":""}]
[{"$f":"IF(AND($E$35=\"No\",$E$46<$E$50),\"Other Variable Income Cannot be Greater than Basic Income\",IF(AND($E$35=\"Yes\",OR($E$46<$E$50,$N$46<$N$50)),\"Other Variable Income Cannot be Greater than Basic Income\",\"\"))","v":""}]
[]
["Additional Commitments"]
[]
["Monthly Maintenance Payments (if applicable)"]
["Monthly Loan Repayments (not redeemed by this facility)"]
[]
[]
[]
[]
[]
[]
["Total Additional Commitments",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(P66:P73)","v":0}]
[]
["Additional Properties"]
[]
[null,"GBP RIPs - Enter all information in GBP and ensure GBP Rate below is correct"]
[null,"GBP Rate",0.87839999999999996]
[]
[null,"Insert appropriate interest rate below:"]
[null,"Euro RIP",5.1499999999999997E-2]
[null,"GBP RIP",5.1499999999999997E-2]
[null,"Homeloan",4.4999999999999998E-2]
[null,"Tracker Rate Loan - RIP",0.03]
[null,"Tracker Rate Loan - PDH",0.03]
[null,"Address","Type","Property Value","Loan Balance","LTV","Remaining Term","Current Interest Rate","Assumed Interest Rate","Stressed Interest Rate","Monthly Repayment","Monthly Rent ","Net Rent","Income Available for Repayments",null,"Euro Income Available for Repayments"]
[null,null,null,null,null,{"$f":"IF(D88=0,0,E88/D88)","v":0},null,null,{"$f":"IF(ISERROR(VLOOKUP(C88,$B$82:$C$86,2,0)),$G$14,(VLOOKUP(C88,$B$82:$C$86,2,0)))","v":0},{"$f":"H88+2%","v":0.02},{"$f":"E88*IF(ISERROR(-PMT((J88)/12,G88*12,1000,0,0)),0,-PMT((J88)/12,G88*12,1000,0,0)/1000)","v":0},null,{"$f":"L88*0.6","v":0},{"$f":"M88-K88","v":0},null,{"$f":"IF(C88=\"GBP RIP\",N88/$C$79,N88)","v":0}]
[null,null,null,null,null,{"$f":"IF(D89=0,0,E89/D89)","v":0},null,null,{"$f":"IF(ISERROR(VLOOKUP(C89,$B$82:$C$86,2,0)),$G$14,(VLOOKUP(C89,$B$82:$C$86,2,0)))","v":0},{"$f":"H89+2%","v":0.02},{"$f":"E89*IF(ISERROR(-PMT((J89)/12,G89*12,1000,0,0)),0,-PMT((J89)/12,G89*12,1000,0,0)/1000)","v":0},null,{"$f":"L89*0.6","v":0},{"$f":"M89-K89","v":0},null,{"$f":"IF(C89=\"GBP RIP\",N89/$C$79,N89)","v":0}]
[null,null,null,null,null,0,null,null,0,0.02,0,null,0,0,null,0]
[null,null,null,null,null,0,null,null,0,0.02,0,null,0,0,null,0]
[null,null,null,null,null,0,null,null,0,0.02,0,null,0,0,null,0]
[null,null,null,null,null,0,null,null,0,0.02,0,null,0,0,null,0]
[null,null,null,null,null,0,null,null,0,0.02,0,null,0,0,null,0]
[null,null,null,null,null,0]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(P88:P94)","v":0}]
[]
[{"$f":"IF($F$95>125%,\"Exception Required as Overall LTV Position is Above 125%\",IF(MAX($F$88:$F$94)>135%,\"Exception Required as Individual Property in Excess of 135% LTV\",\"\"))","v":""}]
[{"$f":"IF(AND(OR($E$24=\"RIP Purchase\",$E$24=\"RIP Remortgage\"),COUNTIFS($C$88:$C$94,\"*RIP*\")>1),\"Exception Required as Applicant is Exposed to 2 or More BTL Properties\",IF(AND(OR($E$24<>\"RIP Purchase\",$E$24<>\"RIP Remortgage\"),COUNTIFS($C$88:$C$94,\"*RIP*\")>2),\"Exception Required as Applicant is Exposed to 2 or More BTL Properties\",\"\"))","v":""}]
[]
["Step 1 - LTV and LTI Parameters"]
[]
[null,null,"Maximum LTV",null,{"$f":"P20","v":""},null,null,null,null,null,"LTI Loan Cap (3.5x Combined Income)",null,null,null,null,{"$f":"IF(OR(E24=Variables!A98,Proposal!E24=Variables!A100,Proposal!E24=Variables!A102,Proposal!E24=Variables!A103),P55*100,P55*Messaging!C4)","v":0}]
[]
[null,null,"Maximum LTV Cap",null,{"$f":"IFERROR(P18*E104,\"\")","v":""},null,null,null,null,null,"Requested Loan",null,null,null,null,{"$f":"Loan_Sought","v":0}]
[]
[null,null,"Requested LTV",null,{"$f":"IFERROR(Loan_Sought/P18,\"\")","v":""}]
[]
[null,null,"Outside LTV Limit",null,{"$f":"IF(E108>E104,\"Yes\",\"No\")","v":"No"},null,null,null,null,null,"Outside LTI Limit",null,null,null,null,{"$f":"IF(OR(E24=\"RIP Purchase\",E24=\"RIP Remortgage\",E24=\"Holiday Home\"),\"N/A\",IF(P106>P104,\"Yes\",\"No\"))","v":"No"}]
[]
[null,null,null,null,"Limits Outside Parameters",null,null,{"$f":"COUNTIF($E$110:$P$110,\"Yes\")","v":0}]
[]
[null,null,null,null,"Within KBC CBI Limit Criteria",null,null,{"$f":"IFERROR(IF(H112=0,\"Yes\",IF(H112=2,\"No\",IF(H112=1,\"Yes\",\"No\"))),\"\")","v":"Yes"}]
[]
[null,null,null,null,"Minimum Loan Amount to Qualify",null,null,{"$f":"IFERROR(IF(H112>0,VLOOKUP(H114,Messaging!$E$20:$G$21,3,FALSE),\"No minimum\"),\"N/A\")","v":"No minimum"},null,{"$f":"IF(AND(H112=1,Loan_Sought<H116),Messaging!D23,IF(H112=2,\"Outside Lending Limits\",\"\"))","v":""}]
[]
[null,null,null,null,null,null,"                   Step 2 - Does the applicant qualify for loan requested?",null,null,null,null,null,null,"Required",null,"Maximum"]
[]
["Net Disposable Income (NDI) per month",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IFERROR(P59-P74,\"\")","v":""}]
[]
["Allowable NDI % for mortgage repayment purposes",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"NDI!B15","v":0}]
[]
["Customer Allowable NDI per month ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IFERROR((P120*P122)+P97,\"\")","v":""}]
[]
["Minimum Income Left Over Test",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(E110=\"Yes\",ILO!B6,IF(Proposal!P110=\"Yes\",ILO!B7,\"-\"))","v":"-"}]
[]
["Required Minimum Income Left Over",null,{"$f":"IF(H112>0,\"Note: CBI Exception\",\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(H112>0,IF(E110=\"Yes\",ILO!B6,IF(Proposal!P110=\"Yes\",ILO!B7,ILO!B5)),ILO!B5)","v":2000}]
[]
["Maximum allowable NDI (Euro) for mortgage repayment purposes",null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IFERROR(PMT(($P$14)/12,Term_Years*12,-(N132),0,0),\"\")","v":""},null,{"$f":"IFERROR(MAX(MIN(P124,P97+P120-P128),0),\"\")","v":""}]
[]
["Maximum Potential Loan Amount Allowable**",null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"P16","v":0},null,{"$f":"IFERROR(Max_Loan_Amount,\"\")","v":""}]
[{"$f":"CONCATENATE(\"(based on a stressed interest rate of 2% above the interest rate chosen by the Borrower over \",Term_Years,\" years)\")","v":{"$error":"#N/A"}}]
[]
[{"$f":"VLOOKUP(H112,Messaging!$B$7:$C$9,2,FALSE)","v":"Max LTV Exceeded per KBC Criteria"}]
[null,null,null,{"$f":"IFERROR(IF(AND(Proposal!$E$110=\"Yes\",Proposal!$H$114=\"Yes\"),VLOOKUP(Proposal!$C$110,Messaging!$B$12:$D$13,3,FALSE),IF(AND(Proposal!$P$110=\"Yes\",Proposal!$H$114=\"Yes\"),VLOOKUP(Proposal!$K$110,Messaging!$B$12:$D$13,3,FALSE),\"Any changes to the loan amount sought or purchase price may result in the max loan amount being affected by CBI LTV and/or LTI restrictions.\")),\"\")","v":"Any changes to the loan amount sought or purchase price may result in the max loan amount being affected by CBI LTV and/or LTI restrictions."}]
["Valid for tax year ending 31st December 2018"]
["**This calculation is for illustrative purposes only and must not be construed as an offer to advance funds by KBC Bank."]
[]
["KBC Bank Ireland plc is regulated by the Central Bank of Ireland. Lending criteria Terms and Conditions apply. Security and insurance are required. The maximum mortgage is up to 90% of the property value. As a general rule, loan amounts are subject to monthly repayments of up to a maximum of 50% of disposable income and will vary according to individual circumstances. Please contact KBC Bank Ireland PLC to receive more information on charges which may apply to early repayment of fixed loans. All information, prices and rates quoted are for guidance only and do not form any part of a legal contract, agreement or understanding."]
[]
[]
[]
["Variable Rate Warning: The cost of your monthly repayments may increase - If you do not keep up your repayments you may lose your home. The payment rates on this housing loan may be adjusted by the lender from time to time\n\nWarning: Your home is at risk if you do not keep up payments on a mortgage or any other loan secured on it\n\nFixed Rate Warning: You may have to pay charges if you pay off your fixed-rate loan early\n\nInterest Only Warning: The entire amount that you have borrowed will still be outstanding at the end of the interest-only period\n\nDebt Consolidation Warning: This new loan may take longer to pay off than your previous loans. This means you may pay more than if you paid over a shorter term\n\nEndowment Loan Warning: There is no guarantee that the proceeds of the [Insurance Policy / Pension Policy] will be sufficient to repay the loan in full when it becomes due for repayment\n"]

{"sheet":"Interest Rate","index":1,"n_rows":5,"n_cols":3,"rows_kept":5,"cut":false,"hidden":true,"merged":[]}
[]
[]
[null,"Euro RIP",{"$f":"Proposal!C82","v":5.1499999999999997E-2}]
[null,"GBP RIP",{"$f":"Proposal!C83","v":5.1499999999999997E-2}]
[null,"Homeloan",{"$f":"Proposal!F14","v":"Rate Chosen"}]

{"sheet":"ILO","index":2,"n_rows":31,"n_cols":33,"rows_kept":31,"cut":false,"hidden":true,"merged":[]}
[]
[]
["Joint Application",{"$f":"Proposal!E35","v":0},null,null,null,"Single Borrower MILO",1500,1850,2100,250]
["No. Of Dependants",{"$f":"Proposal!E37","v":0},null,null,null,"Joint Borrower",2000,2350,2600,250]
["Min ILO",{"$f":"HLOOKUP(B4,B14:AG15,2,0)","v":2000}]
["LTV Exemption Min ILO",{"$f":"HLOOKUP(B4,$B$22:$AG$23,2,FALSE)","v":2900}]
["LTI Exemption Min ILO",{"$f":"HLOOKUP(B4,$B$30:$AG$31,2,FALSE)","v":2900}]
[]
["Standard  MILO"]
["No of Dependants",0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31]
["Single",1500,1850,2100,2350,{"$f":"E11+$J$3","v":2600},{"$f":"F11+$J$3","v":2850},{"$f":"G11+$J$3","v":3100},3350,3600,3850,4100,4350,4600,4850,5100,5350,5600,5850,6100,6350,6600,6850,7100,7350,7600,7850,8100,8350,8600,8850,9100,9350]
["Joint",{"$f":"G4","v":2000},2350,2600,2850,{"$f":"E12+$J$4","v":3100},{"$f":"F12+$J$4","v":3350},3600,3850,4100,4350,4600,4850,5100,5350,5600,5850,6100,6350,6600,6850,7100,7350,7600,7850,8100,8350,8600,8850,9100,9350,9600,9850]
[]
["No. of Dependants",0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31]
["Applicable Rate",{"$f":"IF($B$3=\"No\",B11,B12)","v":2000},{"$f":"IF($B$3=\"No\",C11,C12)","v":2350},2600,2850,3100,3350,3600,3850,4100,4350,4600,4850,5100,5350,5600,5850,6100,6350,6600,6850,7100,7350,7600,7850,8100,8350,8600,8850,9100,9350,9600,9850]
[]
["LTV Exemption MILO","Buffer =",0.45]
["No of Dependants",0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31]
["Single",{"$f":"B11*(1+$C$17)","v":2175},{"$f":"C11*(1+$C$17)","v":2682.5},3045,3407.5,3770,4132.5,4495,4857.5,5220,5582.5,5945,6307.5,6670,7032.5,7395,7757.5,8120,8482.5,8845,9207.5,9570,9932.5,10295,10657.5,11020,11382.5,11745,12107.5,12470,12832.5,13195,13557.5]
["Joint",{"$f":"B12*(1+$C$17)","v":2900},{"$f":"C12*(1+$C$17)","v":3407.5},3770,4132.5,4495,4857.5,5220,5582.5,5945,6307.5,6670,7032.5,7395,7757.5,8120,8482.5,8845,9207.5,9570,9932.5,10295,10657.5,11020,11382.5,11745,12107.5,12470,12832.5,13195,13557.5,13920,14282.5]
[]
["No. of Dependants",0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31]
["Applicable Rate",{"$f":"IF($B$3=\"No\",B19,B20)","v":2900},{"$f":"IF($B$3=\"No\",C19,C20)","v":3407.5},3770,4132.5,4495,4857.5,5220,5582.5,5945,6307.5,6670,7032.5,7395,7757.5,8120,8482.5,8845,9207.5,9570,9932.5,10295,10657.5,11020,11382.5,11745,12107.5,12470,12832.5,13195,13557.5,13920,14282.5]
[]
["LTI Exemption MILO","Buffer =",0.45]
["No of Dependants",0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31]
["Single",{"$f":"B11*(1+$C$25)","v":2175},{"$f":"C11*(1+$C$25)","v":2682.5},3045,3407.5,3770,4132.5,4495,4857.5,5220,5582.5,5945,6307.5,6670,7032.5,7395,7757.5,8120,8482.5,8845,9207.5,9570,9932.5,10295,10657.5,11020,11382.5,11745,12107.5,12470,12832.5,13195,13557.5]
["Joint",{"$f":"B12*(1+$C$25)","v":2900},3407.5,3770,4132.5,4495,4857.5,5220,5582.5,5945,6307.5,6670,7032.5,7395,7757.5,8120,8482.5,8845,9207.5,9570,9932.5,10295,10657.5,11020,11382.5,11745,12107.5,12470,12832.5,13195,13557.5,13920,14282.5]
[]
["No. of Dependants",0,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25,26,27,28,29,30,31]
["Applicable Rate",{"$f":"IF($B$3=\"No\",B27,B28)","v":2900},{"$f":"IF($B$3=\"No\",C27,C28)","v":3407.5},3770,4132.5,4495,4857.5,5220,5582.5,5945,6307.5,6670,7032.5,7395,7757.5,8120,8482.5,8845,9207.5,9570,9932.5,10295,10657.5,11020,11382.5,11745,12107.5,12470,12832.5,13195,13557.5,13920,14282.5]

{"sheet":"NDI","index":3,"n_rows":15,"n_cols":8,"rows_kept":15,"cut":false,"hidden":true,"merged":[]}
[]
[]
[null,"Joint Application",{"$f":"Proposal!E35","v":0}]
[null,"No. Of Dependants",{"$f":"Proposal!E37","v":0}]
[null,"Look up field",{"$f":"IF(C3=\"No\",1,IF(C4=0,2,3))","v":2}]
[null,"Income",{"$f":"Proposal!P55","v":0}]
[null,0,0,null,null,"Single","Joint (Dependants)","Joint No Dependants"]
[null,30000,{"$f":"IF($C$5=1,F8,IF($C$5=2,G8,IF($C$5=3,H8,0)))","v":0},null,"30,000 – 34,999 ",25,0,0]
[null,35000,25,null,"35,000 – 39,999 ",35,25,0]
[null,40000,30,null,"40,000 – 49,999",45,30,30]
[null,50000,40,null,"50,000 -59,999",45,40,35]
[null,60000,45,null,"60,000 – 74,999  ",50,45,45]
[null,75000,50,null,"75,000 + ",50,50,50]
[]
[null,{"$f":"VLOOKUP(C6,$B$7:$C$13,2,1)/100","v":0},"Applicable NDI"]

{"sheet":"Restriction Text","index":4,"n_rows":47,"n_cols":9,"rows_kept":47,"cut":false,"hidden":true,"merged":[]}
[]
[]
["Income Left over",null,null,"Combined","Married"]
["Net Monthly Income",null,null,{"$f":"Proposal!P120","v":""},{"$f":"Proposal!P120","v":""}]
["Max NDI€",null,null,{"$f":"MAX(IF('Tax Calc'!D10>39999,IF(APPLICATION_TYPE=\"Joint\",D4-2500,D4-1500),IF(APPLICATION_TYPE=\"Joint\",D4-2000,D4-1250)),0)","v":{"$error":"#N/A"}},{"$f":"MAX(IF('Tax Calc'!E10>39999,E4-2500,E4-2000),0)","v":{"$error":"#VALUE!"}},{"$f":"IF(APPLICATION_STATUS=1,E5,D5)","v":{"$error":"#N/A"}}]
[]
["Result_Text"]
["No loan sought amount has been entered"]
[{"$f":"\"Below Minimum Loan Amount by\"&\" €\"&E9","v":"Below Minimum Loan Amount by €30000"},null,null,null,{"$f":"IF(Proposal!E24=\"Top Up/Equity Release\",10000-Proposal!N132,30000-Proposal!N132)","v":30000}]
[{"$f":"\"Within KBC lending criteria for up to\"&\" €\"&ROUND(E11,0)","v":{"$error":"#VALUE!"}},null,null,null,{"$f":"Proposal!P132-Proposal!N132","v":{"$error":"#VALUE!"}}]
[{"$f":"\"The loan amount sought is outside KBC lending criteria by\"&\" €\"&-ROUND(E10,0)&\"*\"","v":{"$error":"#VALUE!"}},null,null,null,{"$f":"Proposal!P132","v":""}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Min ","NDI","LTV","Min 5-80%","80%LTV"]
["NDI_Max_Percent",{"$f":"IF(Proposal!P120<=0,0,B30/Proposal!P120)","v":{"$error":"#N/A"}},{"$f":"IF(APPLICATION_TYPE=\"Joint\",NDI_Max_Percent_Joint,NDI_Max_Percent_Single)","v":{"$error":"#N/A"}},null,null,0.8,null,null,{"$f":"(Proposal!$P$14)/12","v":1.6666666666666668E-3}]
["NDI_Max",{"$f":"PMT((Proposal!$P$14)/12,Term_Years*12,-(Max_Loan_Amount),0,0)","v":{"$error":"#N/A"}},{"$f":"Proposal!P130","v":""},null,null,null,null,null,{"$f":"Term_Years*12","v":{"$error":"#N/A"}}]
["Min_Loan_Amount",{"$f":"VLOOKUP(Proposal!E24,'MAX LTV'!$B$8:$I$22,7,FALSE)","v":{"$error":"#N/A"}},30000,null,null,null,null,null,{"$f":"-C30","v":{"$error":"#VALUE!"}}]
["Max_Loan_Amount",{"$f":"C32","v":{"$error":"#N/A"}},{"$f":"PV((Proposal!$P$14)/12,Term_Years*12,-C30,0)","v":{"$error":"#N/A"}},{"$f":"IF(Proposal!P18>0,'Tax Calc'!#REF!*Proposal!P18,\"\")","v":""},{"$f":"MIN(#REF!,F32)","v":{"$error":"#REF!"}},{"$f":"IF(Proposal!P18>0,Proposal!P18*F29,\"\")","v":""}]
["Term_Max_Years_Allowed",{"$f":"VLOOKUP('Tax Calc'!B5,Variables!A85:E92,2,0)","v":{"$error":"#N/A"}}]
["Term_Years",{"$f":"IF(Term_Sought>0,MIN(Term_Sought,Term_Max_Years_Allowed),Term_Max_Years_Allowed)","v":{"$error":"#N/A"}}]
["NDI_Max€"]
[]
[]
[]
[]
["Restriction_Text"]
[{"$f":"CONCATENATE(\"The loan amount has been restricted to \",IF(C30=F5,A43,IF(Max_Loan_Amount='Tax Calc'!#REF!,A45,IF(Max_Loan_Amount=D32,A42,A44))))","v":{"$error":"#N/A"}}]
[{"$f":"CONCATENATE(\"a loan to value of \",TEXT('Tax Calc'!#REF!,\"0%\"))","v":{"$error":"#REF!"}}]
[{"$f":"CONCATENATE(\"a minimum income left over of \",IF(APPLICATION_TYPE=\"Single\",IF('Tax Calc'!D10<40000,\"€1,250\",\"€1,500\"),IF('Tax Calc'!D10<40000,\"€2,000\",\"€2,500\")))","v":{"$error":"#N/A"}}]
[{"$f":"CONCATENATE(\"a net disposable income level of \",TEXT(C29,\"0%\"))","v":{"$error":"#N/A"}}]
[{"$f":"CONCATENATE(\"a maximum loan amount of \",TEXT('Tax Calc'!#REF!,\"#,##0\"))","v":{"$error":"#REF!"}}]
[]
[{"$f":"CONCATENATE(\"(based on a stressed interest rate increase of \", (STRESS_RATE_ADDITION*100),\"% over the \",  Variable_Rate*100,\"% variable rate over \",Term_Years,\" years)\")","v":{"$error":"#REF!"}}]

{"sheet":"Tax Calc","index":5,"n_rows":76,"n_cols":7,"rows_kept":76,"cut":false,"hidden":true,"merged":[]}
["Applicant Details",null,null,"Applicant 1","Applicant 2"]
["APPLICATION_STATUS",{"$f":"IF(ISBLANK(D2),1,MATCH(D2,Variables!$A$8:$A$10,0))","v":{"$error":"#N/A"}},1,{"$f":"Proposal!E33","v":0},{"$f":"Proposal!K33","v":0},{"$f":"ISBLANK(D2)","v":false},{"$f":"IF(ISBLANK(D2),1,2)","v":2}]
["SELF_EMPLOYED",{"$f":"IF(ISBLANK(D3),2,MATCH(D3,Variables!$A$13:$A$14,0))","v":{"$error":"#N/A"}},{"$f":"IF(ISBLANK(E3),2,MATCH(E3,Variables!$A$13:$A$14,0))","v":{"$error":"#N/A"}},{"$f":"Proposal!E39","v":0},{"$f":"Proposal!N39","v":0}]
["APPLICATION_TYPE",{"$f":"IF(Proposal!E35=\"Yes\",\"Joint\",IF(C10>0,\"Joint\",IF(APPLICATION_STATUS=2,\"Joint\",\"Single\")))","v":{"$error":"#N/A"}},null,"Two Incomes",{"$f":"IF(AND((Proposal!H53>0),(Proposal!P53>0)),\"Yes\",\"No\")","v":"No"}]
["MORTGAGE_TYPE",{"$f":"Proposal!E24","v":0}]
["EMPLOYMENT SECTOR",{"$f":"Proposal!E41","v":0},{"$f":"Proposal!N41","v":0}]
[null,"First","Second","Combined","Joint"]
[null,1,1]
["Applicant Income Details"]
["GROSS_INCOME",{"$f":"Proposal!H53","v":0},{"$f":"Proposal!P53","v":0},{"$f":"SUM(B10:C10)","v":0},{"$f":"D10","v":0}]
[]
["Single Person",{"$f":"IF(AND(B$10>0,D$2=Variables!$A$8),VLOOKUP(A12,Variables!$A$26:$B$29,2,FALSE),0)","v":0},{"$f":"IF(AND((C$10>0),(E$2=Variables!$A$8)),VLOOKUP(A12,Variables!$A$26:$B$29,2,FALSE),0)","v":0},{"$f":"SUM(B12:C12)","v":0},{"$f":"MIN(B12+(C10)+9000,B12+C12)","v":0}]
["Married - One Income",{"$f":"IF(AND((B$10>0),(D$2=Variables!A$9),(E$4=\"No\")),VLOOKUP($A13,Variables!$A$26:$B$29,2,FALSE),0)","v":0},{"$f":"IF(AND((C$10>0),(E$2=Variables!A$9),(E$4=\"No\")),VLOOKUP($A13,Variables!$A$26:$B$29,2,FALSE),0)","v":0},{"$f":"B13+C13","v":0},{"$f":"B13+C13","v":0}]
["Married - Two Income",{"$f":"IF(AND((B$10>0),(D$2=Variables!A$9),($E$4=\"Yes\"),(B$10>=C$10)),VLOOKUP($A14,Variables!$A$26:$B$29,2,FALSE),IF(AND((B$10>0),(D$2=Variables!A$9),($E$4=\"Yes\"),(B$10<C$10)),(MIN(VLOOKUP($A14,Variables!$A$26:$C$29,3,FALSE),B10-SUM(B57:B59))),0))","v":0},{"$f":"IF(AND((C$10>0),(E$2=Variables!A$9),($E$4=\"Yes\"),(C$10>B$10)),VLOOKUP($A14,Variables!$A$26:$B$29,2,FALSE),IF(AND((C$10>0),(E$2=Variables!A$9),($E$4=\"Yes\"),(C$10<=B$10)),(MIN(VLOOKUP($A14,Variables!$A$26:$C$29,3,FALSE),C10-SUM(C57:C59))),0))","v":0},{"$f":"B14+C14","v":0},{"$f":"B14+C14","v":0}]
["One Parent/Widowed Parent",{"$f":"IF(AND(B$10>0,D2=Variables!A10),VLOOKUP('Tax Calc'!A15,Variables!$A$26:$B$29,2,FALSE),0)","v":0},{"$f":"IF(AND((C$10>0),(E2=Variables!$A10)),VLOOKUP('Tax Calc'!A15,Variables!$A$26:$B$29,2,FALSE),0)","v":0},{"$f":"SUM(B15:C15)","v":0},{"$f":"B15+C15","v":0}]
["PRSI_Ceiling",0,0]
["PRSI_Floor",0,0]
["PRSI_Exemption",0,0]
[]
["Taxable_Higher_Rate",{"$f":"MAX(B10-SUM(B57:B59)-SUM(B12:B15),0)","v":0},{"$f":"MAX(C10-SUM(C57:C59)-SUM(C12:C15),0)","v":0},{"$f":"IF(D21>=(D10-SUM(D57:D59)),0,MIN(SUM(B20:C20),D10-(D21+SUM($D$57:$D$59))))","v":0},{"$f":"IF(E21>=(E10-SUM(E57:E59)),0,MIN(SUM(B20:C20),E10-(E21+SUM(E57:E59))))","v":0}]
["Taxable_Standard_Rate",{"$f":"MIN(SUM(B12:B15),B10-SUM(B57:B59))","v":0},{"$f":"MIN(SUM(C12:C15),C10-SUM(C57:C59))","v":0},{"$f":"SUM(B21:C21)","v":0},{"$f":"MIN(SUM(E12:E15),E10-SUM(E57:E59))","v":0},null,{"$f":"IF(0<B10,1,2)","v":2}]
["Taxable_PRSI - All Income",{"$f":"B10","v":0},{"$f":"C10","v":0},{"$f":"SUM(B22:C22)","v":0},{"$f":"SUM(B22:C22)","v":0}]
["Taxable_Universal_Social_Charge - all income",{"$f":"IF(0<B10,MIN(1000000,B10)-0-SUM(B57:B59),0)","v":0},{"$f":"IF(0<C10,MIN(1000000,C10)-0-SUM(C57:C59),0)","v":0},{"$f":"SUM(B23:C23)","v":0},{"$f":"SUM(B23:C23)","v":0}]
[]
["Tax_Rate_Higher",0.4,0.4]
["Tax_Rate_Standard",0.2,0.2]
["Tax_Rate_PRSI",0.04,0.04]
["Tax_Rate_Universal_Social_Charge total income less than €13,000",0,0]
["Tax_Rate_Universal_Social_Charge first €12,012",5.0000000000000001E-3,5.0000000000000001E-3]
["Tax_Rate_Universal_Social_Charge next €7,360",0.02,0.02]
["Tax_Rate_Universal_Social_Charge amts above €19,373 to €70,044",4.7500000000000001E-2,4.7500000000000001E-2]
["Tax_Rate_Universal_Social_Charge amts above €70,044 PAYE ONLY",0.08,0.08]
["Tax_Rate_Universal_Social_Charge amts €70,044 to €100,000 SELF-EMPLOYED ONLY",0.08,0.08]
["Tax_Rate_Universal_Social_Charge amts above €100,000, SELF-EMPLOYED ONLY",0.11,0.11]
[]
["USC Band A - Amt",12012,12012,20072,20072]
["USC Band B - Amt ",7360,7360,11960,11960]
["USC Band C - Amt",50672,50672]
["USC Band D - Amt",70044,70044]
["USC Band E - Amt",29956,29956]
["USC Band F - Amt",100000,100000]
["Pension Levy Band A - Amt",{"$f":"IF(B$6=\"Public Sector\",IF(B$10>Variables!B49,Variables!B49,'Tax Calc'!B$10),0)","v":0},{"$f":"IF(C$6=\"Public Sector\",IF(C$10>Variables!C49,Variables!C49,'Tax Calc'!C$10),0)","v":0}]
["Pension Levy Band B - Amt ",{"$f":"IF(B6=\"Public Sector\",IF(B10>Variables!B51,Variables!B50,IF(B10>Variables!B49,(B10-Variables!B49),0)),0)","v":0},{"$f":"IF(C6=\"Public Sector\",IF(C10>Variables!C51,Variables!C50,IF(C10>Variables!C49,(C10-Variables!C49),0)),0)","v":0}]
["Pension Levy Band C - Amt",{"$f":"IF(B6=\"Public Sector\",IF(B10>Variables!B51,(B10-Variables!B51),0),0)","v":0},{"$f":"IF(C6=\"Public Sector\",IF(C10>Variables!C51,(C10-Variables!C51),0),0)","v":0}]
[]
["Tax Chargeable"]
["Tax_Higher_Rate",{"$f":"B20*B25","v":0},{"$f":"C20*C25","v":0},{"$f":"SUM(B47:C47)","v":0},{"$f":"E20*B25","v":0},{"$f":"+B47+C47-E47","v":0}]
["Tax_Standard_Rate",{"$f":"B21*B26","v":0},{"$f":"C21*C26","v":0},0,{"$f":"E21*B26","v":0},{"$f":"+B48+C48-E48","v":0}]
["Tax_Rate_PRSI",{"$f":"IF(B10>18304,B10*B27,0)","v":0},{"$f":"IF(C10>18304,C10*C27,0)","v":0},0,{"$f":"B49+C49","v":0},0]
["Tax_Rate_Universal_Social_Charge first €12,012",{"$f":"IF(B10>=B36,B36*B29,0)","v":0},{"$f":"IF(C10>=C36,C36*C29,0)","v":0},0,0,{"$f":"+B50+C50-E50","v":0}]
["Tax_Rate_Universal_Social_Charge next €7,360 B",{"$f":"IF(B10>SUM(B36:B37),B37*B30,IF(B10>B36,(B10-B36)*B30,0))","v":0},{"$f":"IF(C10>SUM(C36:C37),C37*C30,IF(C10>C36,(C10-C36)*C30,0))","v":0},0,0,{"$f":"+B51+C51-E51","v":0}]
["Tax_Rate_Universal_Social_Charge amts €19,373 to €70,044",{"$f":"IF(B10>B39,B38*B31,IF(B10>SUM(B36:B37),(B10-SUM(B36:B37))*B31,0))","v":0},{"$f":"IF(C10>C39,C38*C31,IF(C10>SUM(C36:C37),(C10-SUM(C36:C37))*C31,0))","v":0},0,0,0]
["Tax_Rate_Universal_Social_Charge amts above €70,044 PAYE ONLY",{"$f":"IF(D3=\"PAYE\",IF(B10>B39,(B10-B39)*B32,0),0)","v":0},{"$f":"IF(E3=\"PAYE\",IF(C10>C39,(C10-C39)*C32,0),0)","v":0},0,0,0]
["Tax_Rate_Universal_Social_Charge amts €70,044 to €100,000 SELF-EMPLOYED ONLY",{"$f":"MAX(IF(D3=\"Self Employed\",IF(B10>B41,B40*B33,(B10-B39)*B33),0),0)","v":0},{"$f":"MAX(IF(E3=\"Self Employed\",IF(C10>C41,C40*C33,(C10-C39)*C33),0),0)","v":0},0,0,0]
["Tax_Rate_Universal_Social_Charge amts above €100,000, SELF-EMPLOYED ONLY",{"$f":"IF(AND((D3=\"Self Employed\"),(B10>B41)),(B10-B41)*B34,0)","v":0},{"$f":"IF(AND((E3=\"Self Employed\"),(C10>C41)),(C10-C41)*C34,0)","v":0},0,0,0]
[]
["Pension Levy Band 1, Rate 1",{"$f":"+B42*Variables!$B53","v":0},{"$f":"+C42*Variables!$B53","v":0},{"$f":"SUM(B57:C57)","v":0},{"$f":"B57+C57","v":0},{"$f":"+B57+C57-E57","v":0}]
["Pension Levy Band 2, Rate 2",{"$f":"+B43*Variables!$B54","v":0},{"$f":"+C43*Variables!$B54","v":0},{"$f":"SUM(B58:C58)","v":0},{"$f":"B58+C58","v":0},{"$f":"+B58+C58-E58","v":0}]
["Pension Levy Band 3, Rate 3",{"$f":"+B44*Variables!$B55","v":0},{"$f":"+C44*Variables!$B55","v":0},{"$f":"SUM(B59:C59)","v":0},{"$f":"B59+C59","v":0},{"$f":"+B59+C59-E59","v":0}]
["Tax_Chargeable",{"$f":"SUM(B47:B59)","v":0},{"$f":"SUM(C47:C59)","v":0},0,0,0]
[]
["Tax Credits"]
["Personal_Tax_Credit",{"$f":"IF(B10>0,IF(AND((APPLICATION_STATUS=3),(Proposal!E37=0)),OFFSET(Variables!A34,0,APPLICATION_STATUS-1),OFFSET(Variables!$A$31,APPLICATION_STATUS,'Tax Calc'!B8)),0)","v":0},{"$f":"IF(C10>0,OFFSET(Variables!$A$31,APPLICATION_STATUS,'Tax Calc'!C8),0)","v":0},{"$f":"SUM(B63:C63)","v":0},{"$f":"D63","v":0}]
["Married - One working",{"$f":"IF(AND(Proposal!$E$33=Variables!$A$32,Proposal!N46=0),Variables!$B$33,0)","v":0},{"$f":"IF(AND(Proposal!$E$33=Variables!$A$32,Proposal!E46=0),Variables!$B$33,0)","v":0},{"$f":"SUM(B64:C64)","v":0},0]
["PAYE_Credit",{"$f":"IF(B$10>0,IF(B$3=1,Variables!B37,0),0)","v":0},{"$f":"IF(C$10>0,IF(C$3=1,Variables!B37,0),0)","v":0},{"$f":"SUM(B65:C65)","v":0},0]
["Self_Employed_Credit",{"$f":"IF(B$10>0,IF(B$3=2,Variables!C45,0),0)","v":0},{"$f":"IF(C$10>0,IF(C$3=2,Variables!C45,0),0)","v":0},{"$f":"SUM(B66:C66)","v":0},{"$f":"D66","v":0}]
[]
["Limited to PAYE charge"]
[]
["Tax_Credits",{"$f":"SUM(B63:B69)","v":0},{"$f":"SUM(C63:C69)","v":0},{"$f":"SUM(B70:C70)","v":0},{"$f":"D70","v":0}]
[]
[]
["Net_Disposable_Income",{"$f":"B10-B60+B70","v":0},{"$f":"C10-C60+C70","v":0},{"$f":"D10-D60+D70","v":0},{"$f":"E10-E60+E70","v":0}]
[]
[]
["Net Monthly Income",null,null,{"$f":"D73/12","v":0},{"$f":"E73/12","v":0}]

{"sheet":"MAX LTV","index":6,"n_rows":23,"n_cols":10,"rows_kept":23,"cut":false,"hidden":true,"merged":[]}
[]
[null,"Case Paramaters",null,null,null,null,null,null,"Maximum Age","Maximum Age BTL/Holiday Home"]
[null,{"$f":"Proposal!E24&Proposal!E28","v":""},{"$f":"VLOOKUP(B3,$D$8:$E$23,2,0)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(B3,D8:G23,4,0)","v":{"$error":"#N/A"}},{"$f":"VLOOKUP(B3,D7:I23,5,FALSE)","v":{"$error":"#N/A"}},null,null,null,68,66]
[]
[null,null,null,null,null,null,null,65]
[null,null,null,null,null,null,null,31]
[null,"Segment","Location","Segment & Location","Max LTV","Property Type","Max Term","<31 Max Term","Min Loan Amt"]
[null,"First Time Buyer","Urban",{"$f":"B8&C8","v":"First Time BuyerUrban"},0.9,"PDH",{"$f":"MAX(30,$H$5-Proposal!$P$24)","v":65},35,30000]
[null,"House Mover","Urban",{"$f":"B9&C9","v":"House MoverUrban"},0.8,"PDH",{"$f":"MAX(30,$H$5-Proposal!$P$24)","v":65},35,30000]
[null,"Switcher","Urban",{"$f":"B10&C10","v":"SwitcherUrban"},{"$f":"IF(Loan_Sought<=Variables!$B$137,0.9,0.8)","v":0.9},"PDH",30,30,30000]
[null,"Switcher - Additional Lending","Urban",{"$f":"B11&C11","v":"Switcher - Additional LendingUrban"},0.8,"PDH",30,30,30000]
[null,"Top Up/Equity Release","Urban","Top Up/Equity ReleaseUrban",0.8,"PDH",30,30,10000]
[null,"RIP Purchase","Urban","RIP PurchaseUrban",0.7,"RIP",20,20,30000]
[null,"RIP Remortgage","Urban","RIP RemortgageUrban",0.5,"RIP",20,20,30000]
[null,"Holiday Home","Urban","Holiday HomeUrban",0.7,"Holiday Home",20,20,30000]
[null,"First Time Buyer","Other",{"$f":"B16&C16","v":"First Time BuyerOther"},0.9,"PDH",{"$f":"MAX(30,H5-Proposal!$P$24)","v":65},35,30000]
[null,"House Mover","Other","House MoverOther",0.8,"PDH",{"$f":"MAX(30,H5-Proposal!$P$24)","v":65},35,30000]
[null,"Switcher","Other","SwitcherOther",0.9,"PDH",30,35,30000]
[null,"Switcher - Additional Lending","Other","Switcher - Additional LendingOther",0.8,"PDH",30,35,30000]
[null,"Top Up/Equity Release","Other","Top Up/Equity ReleaseOther",0.8,"PDH",30,35,10000]
[null,"RIP Purchase","Other","RIP PurchaseOther",0.7,"RIP",20,20,30000]
[null,"RIP Remortgage","Other","RIP RemortgageOther",0.5,"RIP",20,20,30000]
[null,"Holiday Home","Other",{"$f":"B23&C23","v":"Holiday HomeOther"},0.7,"Holiday Home",20,20,30000]

{"sheet":"Variables","index":7,"n_rows":137,"n_cols":5,"rows_kept":137,"cut":false,"hidden":true,"merged":[]}
["VARIABLES"]
[]
["Version_Control"]
["Version_Number",2.1]
["Date","2017-10-27"]
[]
["Applicant_Status"]
["Single"]
["Married"]
["Widowed"]
[]
["Employment_Type"]
["Paye"]
["Self Employed"]
[]
["Employment_Sector"]
["Private Sector"]
["Public Sector"]
[]
["Allowable",1,2]
["Income_Salary",1,1]
["Car/Garda_Allowance",1,1]
["Income_Overtime Commission Bonus",0.5,0.5]
[]
["Standard_Rate_Threshold"]
["Single Person",34550,0,0]
["Married - One Income",43550,0,0]
["Married - Two Income",43550,25550]
["One Parent/Widowed Parent",38550,38550,0]
[]
["Tax_Credits"]
["Married",1650,0,1650,0]
["Single",1650,1650,0,0]
["Widowed",1650,2190,0,0]
[]
[null,"PAYE","Self Employed"]
["PAYE_Credit",1650,0]
["PRSI_Ceiling",0,0]
["PRSI_Floor",0,0]
["PRSI_Exemption",0,0]
["Health_Levy_Ceiling",0,0]
["Health_Levy_Floor",0,0]
["Health Levy Step",0,0]
["Health_Levy_Exemption",0,0]
["PRSI SELF EMPLOYED",0,1150]
[]
["PENSION LEVY"]
[]
["Band 1",28750,28750]
["Band 2",31250,31250]
["Band 3",60000,60000]
[]
["Rate 1",0]
["Rate 2",0.1]
["Rate 3",0.105]
[]
["Stress"]
["Stress_Rate_Increase"]
["Term_Max_Years",35]
["Term_Max_Years_Remortgage",35]
["Term_Max_Years_FTB",35]
[]
["NDI_Limits","<",1]
[]
["NDI_Single",10000000,0.5]
["NDI_Single",84999,0.5]
["NDI_Single",74999,0.5]
["NDI_Single",59999,0.45]
["NDI_Single",49999,0.45]
["NDI_Single",39999,0.35]
["NDI_Single",29999,0.3]
["NDI_Single",24999,0]
[]
[]
["NDI_Joint",10000000,0.5]
["NDI_Joint",84999,0.45]
["NDI_Joint",74999,0.4]
["NDI_Joint",59999,0.4]
["NDI_Joint",49999,0.3]
["NDI_Joint",39999,0.25]
["NDI_Joint",29999,0]
[]
[]
["Lending_Purpose","Term_Max_Year","LTV_Max_Urban","LTV_Max_Other","Holiday Home"]
["First Time Buyer",35,0.9,0.8,0.7]
["House Mover",35,0.9,0.8,0.7]
["Switcher",30,0.9,0.8,0.7]
["Switcher - Additional Lending",30,0.9,0.8,0.7]
["Top Up/Equity Release",35,0.9,0.8,0.7]
["RIP Purchase",35,0.7,0.7,0.7]
["RIP Remortgage",35,0.5,0.5,0.7]
["Holiday Home",35,0.7,0.7,0.7]
[]
[]
["Borrower_Category"]
["First Time Buyer","Private Residential"]
["House Mover","Private Residential"]
["Switcher","Private Residential"]
["Switcher - Additional Lending","Private Residential"]
["Top Up/Equity Release","Private Residential"]
["RIP Purchase","Residential Investment Property"]
["RIP Remortgage","Residential Investment Property"]
["Holiday Home","Private Residential"]
[]
[]
[]
[]
["B1",0,500,0.02]
[null,50000,1000,0.04]
[]
["Tax_Revenue_At_Source_Ceiling","FTB","NON_FTB"]
["TRS_MARRIED_WIDOWED"]
["TRS_SINGLE"]
["TRS_WIDOWED"]
["TRS Rate"]
[]
["INCOME LEVY"]
["Threshold",15028]
["Band 1",75036]
["Band 2",174980]
[]
["Rate 1",0.02]
["Rate 2",0.04]
["Rate 3",0.06]
[]
["PENSION LEVY"]
[]
["Band 1",28750]
["Band 2",31250]
["Band 3",60000]
[]
["Rate 1",0]
["Rate 2",0.1]
["Rate 3",0.105]
[]
["Loan Sought"]
["Remortgage 90% Max",750000]

{"sheet":"Messaging","index":8,"n_rows":24,"n_cols":8,"rows_kept":24,"cut":false,"hidden":true,"merged":[]}
[]
[]
[null,"Loan to Income"]
[null,"Multiple",3.5]
[]
[null,"Results Messaging"]
[null,0,{"$f":"IF(Proposal!P22>0.9,\"Max LTV Exceeded per KBC Criteria\",IF(Term_Sought>Proposal!P26,\"Max Term Exceeded\",IF(Proposal!$P$22>Proposal!$P$20,\"Max LTV Exceeded\",IF(Term_Sought>Proposal!$P$26,\"Maximum Term Exceeded\",IF(Proposal!$N$132<'Restriction Text'!$B$31,'Restriction Text'!$A$9,IF(Proposal!$P$132>Proposal!$N$132,'Restriction Text'!$A$10,'Restriction Text'!$A$11))))))","v":"Max LTV Exceeded per KBC Criteria"}]
[null,1,{"$f":"IF(Proposal!P22>0.9,\"Max LTV Exceeded per KBC Criteria\",IF(Term_Sought>Proposal!P26,\"Max Term Exceeded\",IF(AND(Proposal!E110=\"Yes\",Proposal!H114=\"Yes\"),VLOOKUP(Proposal!C110,$B$12:$C$13,2,FALSE),IF(AND(Proposal!P110=\"Yes\",Proposal!H114=\"Yes\"),VLOOKUP(Proposal!K110,$B$12:$C$13,2,FALSE),C14))))","v":"Max LTV Exceeded per KBC Criteria"}]
[null,2,{"$f":"IF(Proposal!P22>0.9,\"Max LTV Exceeded per KBC Criteria\",\"Loan sought is outside lending limits\")","v":"Max LTV Exceeded per KBC Criteria"}]
[null,null," "]
[]
[null,"Outside LTV Limit",{"$f":"\"Within KBC lending criteria for up to \"&\"€\"&ROUND(Proposal!P132,0)&\" - (LTV Exception)*\"","v":{"$error":"#VALUE!"}},{"$f":"\"*If applicant wishes to borrow more than 3.5 times income, the LTV must be reduced to below \"&ROUND(Proposal!P20*100,2)&\"%\"","v":{"$error":"#VALUE!"}}]
[null,"Outside LTI Limit",{"$f":"\"Within KBC lending criteria for up to \"&\"€\"&ROUND(MIN(Proposal!P132,Proposal!P18*Proposal!P20),0)&\" - (LTI Exception)*\"","v":{"$error":"#VALUE!"}},{"$f":"\"*If applicant wishes to borrow more than \"&ROUND(Proposal!P20*100,2)&\"%\"&\", the LTI must be reduced to below 3.5x income\"","v":{"$error":"#VALUE!"}}]
[null,null,"Loan amount sought is outside lending limits"]
[null,null,null,null,{"$f":"IF(AND(Proposal!$E$110=\"Yes\",Proposal!$H$114=\"Yes\"),VLOOKUP(Proposal!$C$110,$B$12:$D$13,3,FALSE),IF(AND(Proposal!$P$110=\"Yes\",Proposal!$H$114=\"Yes\"),VLOOKUP(Proposal!$K$110,$B$12:$C$13,2,FALSE),\"\"))","v":""}]
[]
[null,null," "]
[]
[null,null,null,null,"Above","Cap","20k threshold","Loan Amount"]
[null,null,null,"LTV",{"$f":"Proposal!E110","v":"No"},{"$f":"Proposal!E104*Proposal!P18","v":{"$error":"#VALUE!"}},{"$f":"F20+20000","v":{"$error":"#VALUE!"}},{"$f":"Loan_Sought","v":0}]
[null,null,null,"LTI",{"$f":"Proposal!P110","v":"No"},{"$f":"Proposal!P104","v":0},{"$f":"F21+20000","v":20000},{"$f":"Loan_Sought","v":0}]
[]
[null,null,null,{"$f":"\"Please ensure loan amount is at least \"&\"€\"&ROUND(Proposal!H116,0)&\" to qualify for an exception\"","v":{"$error":"#VALUE!"}}]
[null,null,null,"Please amend loan amount before sending for credit decision"]

{"sheet":"FX","index":9,"n_rows":10,"n_cols":8,"rows_kept":10,"cut":false,"hidden":true,"merged":[]}
[]
["Cross Rates"]
["Currency Last Trade","USD $ N/A","JPY ¥ N/A","EUR € N/A","CAD $ N/A","GBP £ N/A","AUD $ N/A","CHF N/A"]
["1 USD $","–",112.464,0.92630000000000001,1.3004,0.79890000000000005,1.3004,0.99109999999999998]
["1 JPY ¥",8.8999999999999999E-3,"–",8.2000000000000007E-3,1.1599999999999999E-2,7.1000000000000004E-3,1.1599999999999999E-2,8.8000000000000005E-3]
["1 EUR €",1.0795999999999999,121.4121,"–",1.4038999999999999,0.86250000000000004,1.4038999999999999,1.0699000000000001]
["1 CAD $",0.76900000000000002,86.481499999999997,0.71230000000000004,"–",0.61429999999999996,1,0.7621]
["1 GBP £",1.2517,140.77180000000001,1.1595,1.6277999999999999,"–",1.6276999999999999,1.2404999999999999]
["1 AUD $",0.76900000000000002,86.484200000000001,0.71230000000000004,1,0.61439999999999995,"–",0.7621]
["1 CHF",1.0089999999999999,113.4773,0.93459999999999999,1.3122,0.80610000000000004,1.3121,"–"]

{"sheet":"Mortgage Rates","index":10,"n_rows":37,"n_cols":12,"rows_kept":37,"cut":false,"hidden":true,"merged":[]}
["Variable","Rate",null,"Fixed","Rate",null,"Buy to Let","Rate",null,null,null,"Variable"]
["<50%",3.3000000000000002E-2,null,"1 Year (all LTV)",3.5000000000000003E-2,null,"<50% Variable",4.4999999999999998E-2,null,null,null,"Fixed"]
["<60%",3.3500000000000002E-2,null,"2 Year <60%",3.1900000000000005E-2,null,">50% Variable",4.7999999999999994E-2,null,null,null,"Buy to Let"]
["<70%",3.4000000000000002E-2,null,"2 Year 60%-80%",3.1900000000000005E-2,null,"3 Year Fixed",5.2499999999999998E-2]
["<80%",3.4000000000000002E-2,null,"2 Year 80%-90%",3.7999999999999999E-2,null,"5 Year Fixed",5.2499999999999998E-2]
["<90%",3.85E-2,null,"3 Year <60%",3.3000000000000002E-2]
[null,null,null,"3 Year 60%-80%",3.3000000000000002E-2]
[null,null,null,"3 Year 80%-90%",3.85E-2]
[null,null,null,"5 Year <60%",3.6499999999999998E-2]
[null,null,null,"5 Year 60%-80%",3.85E-2]
[null,null,null,"5 Year 80%-90%",4.0500000000000001E-2]
[]
[]
[]
[]
[]
["Type","Rate",null,null,"Type","Stressed Rate"]
["<50%",3.3000000000000002E-2,null,null,"<50%",3.3000000000000002E-2]
["<60%",3.3500000000000002E-2,null,null,"<60%",3.3500000000000002E-2]
["<70%",3.4000000000000002E-2,null,null,"<70%",3.4000000000000002E-2]
["<80%",3.4000000000000002E-2,null,null,"<80%",3.4000000000000002E-2]
["<90%",3.85E-2,null,null,"<90%",3.85E-2]
["1 Year (all LTV)",3.5000000000000003E-2,null,null,"1 Year (all LTV)",3.5000000000000003E-2]
["2 Year <60%",3.1900000000000005E-2,null,null,"2 Year <60%",3.1900000000000005E-2]
["2 Year 60%-80%",3.1900000000000005E-2,null,null,"2 Year 60%-80%",3.1900000000000005E-2]
["2 Year 80%-90%",3.7999999999999999E-2,null,null,"2 Year 80%-90%",3.7999999999999999E-2]
["3 Year <60%",3.3000000000000002E-2,null,null,"3 Year <60%",3.3000000000000002E-2]
["3 Year 60%-80%",3.3000000000000002E-2,null,null,"3 Year 60%-80%",3.3000000000000002E-2]
["3 Year 80%-90%",3.85E-2,null,null,"3 Year 80%-90%",3.85E-2]
["5 Year <60%",3.6499999999999998E-2,null,null,"5 Year <60%",3.3500000000000002E-2]
["5 Year 60%-80%",3.85E-2,null,null,"5 Year 60%-80%",3.4000000000000002E-2]
["5 Year 80%-90%",4.0500000000000001E-2,null,null,"5 Year 80%-90%",3.85E-2]
["<50% Variable",4.4999999999999998E-2]
[">50% Variable",4.7999999999999994E-2]
["3 Year Fixed",5.2499999999999998E-2]
["5 Year Fixed",5.2499999999999998E-2]
["SVR",4.2500000000000003E-2]

{"sheet":"Sheet1","index":11,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":true,"merged":[]}