{"text_format":"1","workbook":"xlsx","n_sheets":9,"sheets":["Calc","Corp Borrower 1","Corp Borrower 2","SE","Rental","Calc Data","HEM Table","Instructions","Overview"],"not_rendered":{"charts":0,"images":6,"macros":1,"external_links":0,"defined_names":5,"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":"Calc","index":0,"n_rows":59,"n_cols":40,"rows_kept":59,"cut":false,"hidden":false,"merged":[[53,2,53,4],[54,2,54,4],[46,15,46,16],[54,18,54,19],[46,18,46,19],[49,9,49,10],[49,12,49,13],[43,2,43,4],[53,12,55,13],[55,15,55,16],[44,18,44,19],[53,15,53,16],[55,2,55,4],[53,9,55,10],[50,12,50,13],[47,2,47,4],[46,2,46,4],[45,2,45,4],[44,2,44,4],[49,2,49,4],[48,2,48,4],[53,6,53,7],[54,6,54,7],[48,6,48,7],[42,2,42,4],[41,2,41,4],[24,2,24,6],[32,2,32,4],[30,12,30,13],[30,3,30,4],[42,6,42,7],[38,32,38,34],[38,23,38,31],[30,23,30,25],[33,23,33,25],[42,31,43,34],[42,29,43,30],[39,15,39,16],[26,15,26,16],[26,2,26,4],[27,2,27,4],[26,6,26,7],[26,9,26,10],[32,15,32,16],[30,15,30,16],[40,6,40,7],[41,6,41,7],[43,12,43,13],[22,2,22,4],[22,6,22,7],[22,9,22,10],[40,12,40,13],[40,15,40,16],[39,2,39,4],[32,6,32,7],[31,2,31,4],[33,6,33,7],[33,9,33,10],[34,3,34,4],[34,9,34,10],[34,6,34,7],[31,6,31,7],[39,12,39,13],[39,9,39,10],[33,12,33,13],[32,12,32,13],[39,6,39,7],[40,2,40,4],[26,12,26,13],[25,6,25,7],[40,9,40,10],[33,3,33,4],[44,6,44,7],[44,9,44,10],[43,6,43,7],[43,9,43,10],[49,6,49,7],[45,6,45,7],[45,9,45,10],[29,18,29,19],[31,15,31,16],[32,9,32,10],[41,18,41,19],[42,18,42,19],[41,9,41,10],[41,12,41,13],[42,12,42,13],[42,15,42,16],[42,9,42,10],[34,12,34,13],[41,15,41,16],[46,9,46,10],[43,15,43,16],[34,15,34,16],[28,2,28,4],[31,12,31,13],[29,2,29,4],[30,6,30,7],[30,9,30,10],[26,18,26,19],[28,18,28,19],[27,18,27,19],[29,33,29,34],[30,33,30,34],[26,33,26,34],[32,31,32,34],[33,32,33,34],[33,29,33,31],[33,26,33,28],[27,33,27,34],[28,33,28,34],[26,26,26,28],[27,26,27,28],[28,30,28,31],[30,26,30,28],[29,30,29,31],[29,26,29,28],[28,26,28,28],[30,30,30,31],[32,23,32,30],[48,23,53,34],[47,18,47,19],[47,12,47,13],[51,15,51,16],[46,12,46,13],[44,29,46,34],[45,12,45,13],[49,15,49,16],[47,15,47,16],[44,15,44,16],[44,12,44,13],[46,23,46,28],[56,6,56,7],[56,9,56,10],[45,15,45,16],[51,18,51,19],[52,18,52,19],[52,15,52,16],[56,18,56,19],[53,18,53,19],[50,15,50,16],[50,18,50,19],[56,12,56,16],[55,6,55,7],[48,12,48,13],[46,6,46,7],[47,6,47,7],[48,9,48,10],[49,18,49,19],[48,15,48,16],[54,15,54,16],[2,23,2,34],[11,15,12,16],[13,28,13,30],[3,25,3,26],[26,30,26,31],[13,31,13,34],[58,31,58,35],[45,18,45,19],[55,18,55,19],[48,18,48,19],[43,18,43,19],[40,18,40,19],[44,23,45,28],[42,23,43,28],[41,23,41,34],[34,18,34,19],[39,18,39,19],[40,23,40,34],[34,32,34,34],[33,18,33,19],[32,18,32,19],[30,18,30,19],[31,18,31,19],[34,23,34,25],[11,10,12,11],[15,12,16,12],[13,6,14,6],[13,7,14,9],[13,10,14,11],[5,6,5,10],[5,15,5,19],[15,7,16,9],[5,12,5,13],[13,18,13,20],[11,18,11,20],[11,13,12,14],[11,31,11,34],[23,23,23,27],[24,23,24,27],[24,28,24,34],[25,23,25,34],[3,29,3,34],[27,23,27,25],[27,30,27,31],[11,23,11,26],[8,31,9,34],[11,28,11,30],[8,18,9,30],[2,4,3,19],[13,12,14,12],[15,18,15,20],[9,10,10,11],[11,3,12,5],[13,3,14,5],[13,13,14,14],[9,3,10,5],[9,6,10,6],[9,7,10,9],[11,6,12,6],[11,7,12,9],[21,15,21,16],[21,18,21,19],[22,15,22,16],[25,18,25,19],[25,12,25,13],[15,31,15,34],[15,28,15,30],[15,13,16,14],[21,12,21,13],[34,26,34,28],[34,29,34,31],[25,9,25,10],[21,6,21,7],[9,13,10,14],[9,15,10,16],[13,15,14,16],[15,15,16,16],[20,2,20,6],[9,12,10,12],[15,3,16,5],[11,12,12,12],[15,23,15,26],[15,6,16,6],[15,10,16,11],[29,23,29,25],[28,23,28,25],[13,23,13,26],[33,15,33,16],[31,9,31,10],[21,9,21,10],[22,12,22,13],[22,18,22,19],[25,15,25,16]]}
[]
[]
[null,null,null,null,"GATEWAY COMMERCIAL CAPACITY TEST CALCULATOR V2024.1",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"GATEWAY USE ONLY"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"DEAL No.",null,null,null,null,"NAME:"]
[null,null,null,null,"Released Date: 4 April 2024"]
[null,null,null,null,"Field Key",null,"Mandatory and must be completed",null,null,null,null,null,"Optional input fields",null,null,"Output fields and can't be completed"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"NO",null,null,null,null,null,null,null,"NO"]
[]
[null,null,"LOAN DETAILS",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Error: Loan term must be the same as I/O term"]
[null,null,null,"Loan",null,null,"Loan Type","Loan Amount ($)",null,null,"Term (mths)",null,"Rate % p.a.","Int Only Term",null,"Indicative Payment (mthly)"]
[]
[null,null,1,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(AND(D12<>\"\",G12<>\"\",H12<>\"\",K12<>\"\",M12<>\"\",N12<>\"\"),IF(N12>0,M12/12*H12,PMT(M12/12, K12,-H12)),\"\")","v":""},null,null,"Total New Loans",null,null,null,null,{"$f":"IF(SUM(H12:J17)=0,0,SUM(H12:J17))","v":0},null,null,null,null,"Security Value",null,null,0]
[]
[null,null,2,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(AND(H14<>\"\",K14<>\"\",M14<>\"\"),IF(N14>0,M14/12*H14,PMT(M14/12, K14,-H14)),\"\")","v":""},null,null,"Existing GBL Loans",null,null,null,null,0,null,null,null,null,"LVR",null,null,{"$f":"IF(AF12=0,0,IF(OR(AF9=\"Family Pledge\",AF9=\"FHLDS\"),80%,X16/AF12))","v":0}]
[]
[null,null,3,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(AND(H16<>\"\",K16<>\"\",M16<>\"\"),IF(N16>0,M16/12*H16,PMT(M16/12,K16,-H16)),\"\")","v":""},null,null,"Total Loans",null,null,null,null,{"$f":"SUM(X12,X14)","v":0}]
[]
[]
[]
[]
[null,null,"CORPORATE INCOME (If applicable)"]
[null,null,null,null,null,null,"CORP BORROWER 1",null,null,"CORP BORROWER 2",null,null,null,null,null,null,null,null,"ANNUAL TOTALS"]
[null,null,"Average Net Profit after Adjustments",null,null,null,{"$f":"'Corp Borrower 1'!F18","v":0},null,null,{"$f":"'Corp Borrower 2'!F18","v":0},null,null,null,null,null,null,null,null,{"$f":"G23+J23","v":0}]
[]
[null,null,"PERSONAL INCOME (Annual)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"LIVING EXPENSES",null,null,null,null,"(Individual applicants only)"]
[null,null,null,null,null,null,"INDIVIDUAL 1",null,null,"INDIVIDUAL 2",null,null,"INDIVIDUAL 3",null,null,"INDIVIDUAL 4",null,null,"ANNUAL TOTALS",null,null,null,null,{"$f":"IF(AND(AA28=\"Partnered\",AH28=\"N/A\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AA29=\"Partnered\",AH29=\"N/A\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AA30=\"Partnered\",AH30=\"N/A\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AA31=\"Parntered\",AH31=\"N/A\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH28=\"App 2\",$AH$29<>\"App 1\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH28=\"App 3\",$AH$30<>\"App 1\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH28=\"App 4\",$AH$31<>\"App 1\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH29=\"App 1\",$AH$28<>\"App 2\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH29=\"App 3\",$AH$30<>\"App 2\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH29=\"App 4\",$AH$31<>\"App 2\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH29=\"App 1\",$AH$28<>\"App 2\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH30=\"App 1\",$AH$28<>\"App 3\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH30=\"App 2\",$AH$29<>\"App 3\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH30=\"App 4\",$AH$31<>\"App 3\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH31=\"App 1\",$AH$28<>\"App 4\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH31=\"App 2\",$AH$29<>\"App 4\"),\"CHECK STATUS AND PARTNER ENTRY\",IF(AND(AH31=\"App 3\",$AH$30<>\"App 4\"),\"CHECK STATUS AND PARTNER ENTRY\",\"\")))))))))))))))))","v":""}]
[null,null,"Full Time/Part Time/Casual",null,null,null,0,null,null,0,null,null,0,null,null,0,null,null,{"$f":"G27+J27+M27+P27","v":0},null,null,null,null,null,null,null,"Status",null,null,null,"Dependents",null,null,"Partner"]
[null,null,"Contract Employment",null,null,null,0,1,null,0,1,null,0,1,null,0,1,null,{"$f":"G28*H28+J28*K28+M28*N28+P28*Q28","v":0},null,null,null,null,"Individual 1",null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,"Bonuses/Commission",null,null,null,0,0.8,null,0,0.8,null,0,0.8,null,0,0.8,null,{"$f":"G29*H29+J29*K29+M29*N29+P29*Q29","v":0},null,null,null,null,"Individual 2",null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,"Overtime/Allowances/Other",null,null,null,0,0.8,null,0,0.8,null,0,0.8,null,0,0.8,null,0,null,null,null,null,"Individual 3",null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,null,"Rental Income",null,null,{"$f":"Rental!G20","v":0},null,null,{"$f":"Rental!H20","v":0},null,null,{"$f":"Rental!I20","v":0},null,null,{"$f":"Rental!J20","v":0},null,null,{"$f":"G31+J31+M31+P31","v":0},null,null,null,null,"Individual 4",null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,"Attributed Business Cash Flow",null,null,null,{"$f":"SE!H39","v":0},null,null,{"$f":"SE!J39","v":0},null,null,{"$f":"SE!L39","v":0},null,null,{"$f":"SE!N39","v":0},null,null,0]
[null,null,"Annual Salary from Business",null,null,null,{"$f":"SE!H37","v":0},null,null,{"$f":"SE!J37","v":0},null,null,{"$f":"SE!L37","v":0},null,null,{"$f":"SE!N37","v":0},null,null,0,null,null,null,null,"Declared  Expenses (monthly)",null,null,null,null,null,null,null,0]
[null,null,null,"Plus Non-Taxable Income",null,null,0,null,null,0,null,null,0,null,null,0,null,null,0]
[null,null,null,"TOTAL INCOME",null,null,{"$f":"G27+(G28*H28)+(G29*H29)+(G30*H30)+G31+G32+G33+G34","v":0},null,null,{"$f":"J27+(J28*K28)+(J29*K29)+(J30*K30)+J31+J32+J33+J34","v":0},null,null,{"$f":"M27+(M28*N28)+(M29*N29)+(M30*N30)+M31+M32+M33+M34","v":0},null,null,{"$f":"P27+(P28*Q28)+(P29*Q29)+(P30*Q30)+P31+P32+P33+P34","v":0},null,null,{"$f":"SUM(G35:Q35)","v":0},null,null,null,null,"Annual Benchmark",null,null,{"$f":"IF(X26=\"Check status and partner entry\",0,'HEM Table'!O89)","v":0},null,null,"Annual    Declared",null,null,{"$f":"AF33*12","v":0}]
[]
[]
[]
[null,null,"CONTINUING COMMITMENTS (Remaining after Settlement)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null," Expenses Used in Assessment",null,null,null,null,null,null,null,null,{"$f":"IF(X26=\"Check status and partner entry\",0,'HEM Table'!O91)","v":0}]
[null,null,null,null,null,null,"Limit",null,null,"Remaining P&I Term (months)",null,null,"Assessment Rate",null,null,"Payment (monthly)",null,null,"Sensitised Annual Payments"]
[null,null,"Home Loan",null,null,null,0,null,null,0,null,null,{"$f":"'Calc Data'!C3","v":0.08},null,null,0,null,null,{"$f":"IFERROR((IF(OR(G41=FALSE,G41=0),0,IF(OR(J41=FALSE,J41=0,P41=FALSE,P41=0),\"Missing value\",MAX(PMT(M41/12,J41,-G41),P41)*12))),\"Missing Value\")","v":0},null,null,null,null,"ASSESSMENT RESULT",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"ROUNDUP(NPER(M41/12,P41,-G41,0,1),0)","v":{"$error":"#NUM!"}},{"$f":"IFERROR((IF(OR(G41=FALSE,G41=0),0,IF(OR(J41=FALSE,J41=0,P41=FALSE,P41=0),\"Missing value\",PMT(MAX('Calc Data'!$C$5,M41+'Calc Data'!$C$4)/12,MIN(J41,ROUNDUP(NPER(M41/12,P41,-G41,0,1),0)),-G41))))*12,\"Missing Value\")","v":0}]
[null,null,"Home Loan",null,null,null,0,null,null,0,null,null,{"$f":"'Calc Data'!C3","v":0.08},null,null,0,null,null,{"$f":"IFERROR((IF(OR(G42=FALSE,G42=0),0,IF(OR(J42=FALSE,J42=0,P42=FALSE,P42=0),\"Missing value\",MAX(PMT(M42/12,J42,-G42),P42)*12))),\"Missing Value\")","v":0},null,null,null,null,{"$f":"IF(OR(S41=\"Missing Value\",S42=\"Missing Value\",S43=\"Missing Value\",S44=\"Missing Value\",S45=\"Missing Limit\",S46=\"Missing Limit\",S49=\"Missing Limit\"),\"MISSING VALUES IN COMMITMENTS\",IF(AND(OR(ISNUMBER(SEARCH(\"Equitismart\",Calc!D12))),N12<>0),\"INT ONLY TERM MUST BE ZERO FOR EQUITISMART\",IF(AND(OR(ISNUMBER(SEARCH(\"Equitismart\",Calc!D14))),N14<>0),\"INT ONLY TERM MUST BE ZERO FOR EQUITISMART\",IF(AND(OR(ISNUMBER(SEARCH(\"Equitismart\",Calc!D16))),N16<>0),\"INT ONLY TERM MUST BE ZERO FOR EQUITISMART\",IF(P12=\"\",\"PLEASE ENTER NEW LOAN DETAILS\",IF(OR(S54=\"ERROR\",S55=\"ERROR\",S56=\"ERROR\"),\"INCORRECT LOAN INFORMATION\",IF(AND(AG39=0,S35<>0),\"ENTER LIVING EXPENSES\",\"FIELDS COMPLETED\")))))))","v":"PLEASE ENTER NEW LOAN DETAILS"}]
[null,null,"Commercial Loan",null,null,null,0,null,null,0,null,null,{"$f":"'Calc Data'!C5","v":0.08},null,null,0,null,null,{"$f":"IFERROR((IF(OR(G43=FALSE,G43=0),0,IF(OR(J43=FALSE,J43=0,P43=FALSE,P43=0),\"Missing value\",MAX(PMT(M43/12,J43,-G43),P43)*12))),\"Missing Value\")","v":0},null,null,null,null,{"$f":"IFERROR(IF(X42=\"FIELDS COMPLETED\",IF('Calc Data'!C43>='Calc Data'!F43,\"SERVICING MET\",\"SERVICING NOT MET\")),\"\")","v":false},null,null,null,null,null,"DSR",null,{"$f":"IFERROR(IF(X42<>\"FIELDS COMPLETED\",\"\",'Calc Data'!C43),\"\")","v":""}]
[null,null,"Commercial Loan",null,null,null,0,null,null,0,null,null,{"$f":"'Calc Data'!C5","v":0.08},null,null,0,null,null,{"$f":"IFERROR((IF(OR(G44=FALSE,G44=0),0,IF(OR(J44=FALSE,J44=0,P44=FALSE,P44=0),\"Missing Value\",MAX(PMT(M44/12,J44,-G44),P44)*12))),\"Missing Value\")","v":0}]
[null,null,"Personal Loan",null,null,null,0,null,null,0,null,null,{"$f":"'Calc Data'!F2+'Calc Data'!C2","v":0.1459},null,null,0,null,null,{"$f":"IFERROR(IF(AND(OR(G45=FALSE,G45=0),OR(P45=TRUE,P45<>0)),\"Missing Limit\",(MAX(P45,PMT(M45/12,J45,-G45)))*12),0)","v":0},null,null,null,null,"INDICATIVE MAXIMUM LOAN AMOUNT*",null,null,null,null,null,"*The Indicative maximum loan amount is calculated based on product type and residual P&I term of Loan 1."]
[null,null,"Car Loan",null,null,null,0,null,null,0,null,null,{"$f":"'Calc Data'!F3+'Calc Data'!C2","v":0.1004},null,null,0,null,null,{"$f":"IFERROR(IF(AND(OR(G46=FALSE,G46=0),OR(P46=TRUE,P46<>0)),\"Missing Limit\",(MAX(P46,PMT(M46/12,J46,-G46)))*12),0)","v":0}]
[null,null,"Lease/HP",null,null,null,0,null,null,null,null,null,null,null,null,0,null,null,{"$f":"IFERROR(IF(AND(OR(G47=FALSE,G47=0),OR(P47=TRUE,P47<>0)),\"Missing Limit\",P47*12),0)","v":0},null,null,null,null,{"$f":"IF(X42<>\"FIELDS COMPLETED\",\"\",('Calc Data'!C50))","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Rent",null,null,0,null,null,{"$f":"IFERROR(P48*12,0)","v":0}]
[null,null,"Other (inc. HECS/HELP)",null,null,null,0,null,null,null,null,null,null,null,null,0,null,null,{"$f":"IFERROR(IF(AND(OR(G49=FALSE,G49=0),OR(P49=TRUE,P49<>0)),\"Missing Limit\",P49*12),0)","v":0},null,null,null,null,"Important Information:\nThe assessment result doesn't constitute a loan offer, each deal must still be assessed by an Underwriter.\nWe will constructively assess applications at the margin with strong supporting mitigants. Should you have any doubt, contact your Gateway representative or email loans@gatewaybank.com.au to discuss in more detail."]
[null,null,"Total Credit Card/BNPL Limits",null,null,null,0,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(G50=\"\",0,G50*0.0382*12)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Monthly LMI Premium",null,null,0,null,null,{"$f":"IFERROR(P51*12,0)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Commercial Facilities 1",null,null,{"$f":"'Corp Borrower 1'!J32","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Commercial Facilities 2",null,null,{"$f":"'Corp Borrower 2'!J32","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"GBL New Loan 1",null,null,{"$f":"IFERROR(IF($P$12=\"\",\"\",IF(AND(OR($D$12=\"Commercial Plus\",$D$12=\"Commercial Property\"),N12=0),PMT(MAX('Calc Data'!$C$5,$M$12+'Calc Data'!$C$4)/12,$K$12,-$H$12)*12,IF(AND(OR($D$12=\"Commercial Plus\",$D$12=\"Commercial Property\"),N12>0),PMT(MAX('Calc Data'!$C$5,$M$12+'Calc Data'!$C$4)/12,240,-$H$12),PMT(MAX('Calc Data'!$C$3,$M$12+'Calc Data'!$C$2)/12,($K$12-$N$12),-$H$12))*12)),\"ERROR\")","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"GBL New Loan 2",null,null,{"$f":"IFERROR(IF($P$14=\"\",\"\",IF(AND(OR($D$14=\"Commercial Plus\",$D$14=\"Commercial Property\"),N14=0),PMT(MAX('Calc Data'!$C$5,$M$14+'Calc Data'!$C$4)/12,$K$14,-$H$14)*12,IF(AND(OR($D$14=\"Commercial Plus\",$D$14=\"Commercial Property\"),N14>0),PMT(MAX('Calc Data'!$C$5,$M$14+'Calc Data'!$C$4)/12,240,-$H$14),PMT(MAX('Calc Data'!$C$3,$M$14+'Calc Data'!$C$2)/12,($K$14-$N$14),-$H$14))*12)),\"ERROR\")","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"GBL New Loan 3",null,null,{"$f":"IFERROR(IF($P$16=\"\",\"\",IF(AND(OR($D$16=\"Commercial Plus\",$D$16=\"Commercial Property\"),N16=0),PMT(MAX('Calc Data'!$C$5,$M$16+'Calc Data'!$C$4)/12,$K$16,-$H$16)*12,IF(AND(OR($D$16=\"Commercial Plus\",$D$16=\"Commercial Property\"),N16>0),PMT(MAX('Calc Data'!$C$5,$M$16+'Calc Data'!$C$4)/12,240,-$H$16),PMT(MAX('Calc Data'!$C$3,$M$16+'Calc Data'!$C$2)/12,($K$16-$N$16),-$H$16))*12)),\"ERROR\")","v":""}]
[null,null,null,null,null,null,null,null,null,null,null,null,"Total Sensitised Annual Payments",null,null,null,null,null,{"$f":"IFERROR(SUM(S41:T56),\"ERROR\")","v":0}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"NOW()","v":"2024-04-03T15:05:08.960"}]

{"sheet":"Corp Borrower 1","index":1,"n_rows":50,"n_cols":34,"rows_kept":50,"cut":false,"hidden":false,"merged":[[10,2,10,3],[10,8,10,9],[11,8,11,9],[12,8,12,9],[13,8,13,9],[6,3,6,4],[7,2,7,3],[8,2,8,3],[8,8,8,9],[9,2,9,3],[25,11,25,12],[11,2,11,3],[12,2,12,3],[15,2,15,3],[15,8,15,9],[16,2,16,10],[17,2,17,3],[14,8,14,9],[21,2,21,4],[21,5,21,9],[22,11,22,12],[23,11,23,12],[24,11,24,12],[33,2,33,9],[26,11,26,12],[27,11,27,12],[28,11,28,12],[29,11,29,12],[30,11,30,12],[31,2,31,3],[31,7,31,8],[32,2,32,3]]}
[]
[]
[null,null,"BUSINESS BORROWER 1"]
[]
[]
[null,null,null,null,null,"Latest Year",null,"Prior Year"]
[null,null,null,"Period Ending"]
[null,null,"Profit and Loss"]
[null,null,"Net Profit before Tax/Distributions",null,null,0,null,0,{"$f":"IF(OR(H9=\"\",H9=0),\"\",(F9-H9)/H9)","v":""}]
[null,null,"Addbacks"]
[null,null,"Interest Expense",null,null,0,null,0,{"$f":"IF(OR(H11=\"\",H11=0),\"\",(F11-H11)/H11)","v":""}]
[null,null,"Financed Leasing Expense",null,null,0,null,0,{"$f":"IF(OR(H12=\"\",H12=0),\"\",(F12-H12)/H12)","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Depreciation/ Amortisation of Intangibles",null,null,0,null,0,""]
[null,null,"Other Addbacks",null,null,0,null,0,"",null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Other Addbacks",null,null,0,null,0,""]
[null,null,"Net Profit after Adjustments",null,null,{"$f":"F9+SUM(F11:F15)","v":0},null,{"$f":"H9+SUM(H11:H15)","v":0},""]
[]
[null,null,"Average Net Profit after Adjustments",null,null,{"$f":"AVERAGE(F16,H16)","v":0}]
[]
[]
[]
[null,null,"Continuing Business Financial Commitments"]
[null,null,null,"Limit","Remaining Term (months)","Assessment Rate",null,"Actual Payment (monthly)",null,"Sensitised Annual Payments"]
[null,null,"Term Loan (P&I)",0,0,{"$f":"'Calc Data'!C5","v":0.08},null,0,null,{"$f":"IFERROR(MAX($H24,PMT($F24/12,$E24,-$D24))*12,\"$0.00\")","v":"$0.00"}]
[null,null,"Term Loan (P&I)",0,0,{"$f":"'Calc Data'!C5","v":0.08},null,0,null,{"$f":"IFERROR(MAX($H25,PMT($F25/12,$E25,-$D25))*12,\"$0.00\")","v":"$0.00"}]
[null,null,"Term Loan (I/O)",0,null,{"$f":"'Calc Data'!C5","v":0.08},null,0,null,{"$f":"IFERROR(MAX($H26,PMT($F26/12,360,-$D26))*12,\"$0.00\")","v":0}]
[null,null,"Line of Credit",0,null,{"$f":"'Calc Data'!C5","v":0.08},0,null,null,{"$f":"IFERROR(D27*F27,\"$0.00\")","v":0}]
[null,null,"Overdraft",0,null,{"$f":"'Calc Data'!C5","v":0.08},0,null,null,{"$f":"IFERROR(D28*F28,\"$0.00\")","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,"Lease/HP",0,null,null,null,0,null,{"$f":"IFERROR(H29*12,\"$0.00\")","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,"Other",0,null,null,null,0,null,{"$f":"IFERROR(H30*12,\"$0.00\")","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,"Credit Cards",0,null,null,null,null,null,{"$f":"IF(D31=\"\",0,D31*0.0382*12)","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,null,null,null,null,null,"Sensitised Totals",null,{"$f":"SUM(J24:J31)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,null,3]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,0,null,null,0,null,null,0,null,null,0]
[null,null,null,null,null,null,0,null,null,0,null,null,0,null,null,0]
[null,null,null,null,null,null,0,null,null,0,null,null,0,null,null,0]
[null,null,null,null,null,null,0,null,null,0,null,null,0,null,null,0]
[null,null,null,null,null,null,0,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,0,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,0,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]

{"sheet":"Corp Borrower 2","index":2,"n_rows":34,"n_cols":12,"rows_kept":34,"cut":false,"hidden":false,"merged":[[6,3,6,4],[7,2,7,3],[8,2,8,3],[31,7,31,8],[8,8,8,9],[9,2,9,3],[11,2,11,3],[12,2,12,3],[15,2,15,3],[15,8,15,9],[10,2,10,3],[10,8,10,9],[11,8,11,9],[12,8,12,9],[13,8,13,9],[14,8,14,9],[33,2,33,9],[16,2,16,10],[21,2,21,4],[21,5,21,9],[17,2,17,3],[31,2,31,3],[32,2,32,3],[30,11,30,12],[22,11,22,12],[23,11,23,12],[24,11,24,12],[25,11,25,12],[26,11,26,12],[27,11,27,12],[28,11,28,12],[29,11,29,12]]}
[]
[]
[null,null,"BUSINESS BORROWER 2"]
[]
[]
[null,null,null,null,null,"Latest Year",null,"Prior Year"]
[null,null,null,"Period Ending"]
[null,null,"Profit and Loss"]
[null,null,"Net Profit before Tax/Distributions",null,null,0,null,0,{"$f":"IF(OR(H9=\"\",H9=0),\"\",(F9-H9)/H9)","v":""}]
[null,null,"Addbacks"]
[null,null,"Interest Expense",null,null,0,null,0,{"$f":"IF(OR(H11=\"\",H11=0),\"\",(F11-H11)/H11)","v":""}]
[null,null,"Financed Leasing Expense",null,null,0,null,0,""]
[null,null,"Depreciation/ Amortisation of Intangibles",null,null,0,null,0,""]
[null,null,"Other Addbacks",null,null,0,null,0,""]
[null,null,"Other Addbacks",null,null,0,null,0,""]
[null,null,"Net Profit after Adjustments",null,null,{"$f":"F9+SUM(F11:F15)","v":0},null,{"$f":"H9+SUM(H11:H15)","v":0},{"$f":"IF(OR(H16=\"\",H16=0),\"\",(F16-H16)/H16)","v":""}]
[]
[null,null,"Average Net Profit after Adjustments",null,null,{"$f":"AVERAGE(F16,H16)","v":0}]
[]
[]
[]
[null,null,"Continuing Business Financial Commitments"]
[null,null,null,"Limit","Remaining Term (months)","Assessment Rate",null,"Actual Payment (monthly)",null,"Sensitised Annual Payments"]
[null,null,"Term Loan (P&I)",0,0,{"$f":"'Calc Data'!C5","v":0.08},null,0,null,{"$f":"IFERROR(MAX($H24,PMT($F24/12,$E24,-$D24))*12,\"$0.00\")","v":"$0.00"}]
[null,null,"Term Loan (P&I)",0,0,{"$f":"'Calc Data'!C5","v":0.08},null,0,null,{"$f":"IFERROR(MAX($H25,PMT($F25/12,$E25,-$D25))*12,\"$0.00\")","v":"$0.00"}]
[null,null,"Term Loan (I/O)",0,null,{"$f":"'Calc Data'!C5","v":0.08},null,0,null,{"$f":"IFERROR(MAX($H26,PMT($F26/12,360,-$D26))*12,\"$0.00\")","v":0}]
[null,null,"Line of Credit",0,null,{"$f":"'Calc Data'!C5","v":0.08},0,null,null,{"$f":"IFERROR(D27*F27,\"$0.00\")","v":0}]
[null,null,"Overdraft",0,null,{"$f":"'Calc Data'!C5","v":0.08},0,null,null,{"$f":"IFERROR(D28*F28,\"$0.00\")","v":0}]
[null,null,"Lease/HP",0,null,null,null,0,null,{"$f":"IFERROR(H29*12,\"$0.00\")","v":0}]
[null,null,"Other",0,null,null,null,0,null,{"$f":"IFERROR(H30*12,\"$0.00\")","v":0}]
[null,null,"Credit Cards",0,null,null,null,null,null,{"$f":"IF(D31=\"\",0,D31*0.0382*12)","v":0}]
[null,null,null,null,null,null,null,"Sensitised Totals",null,{"$f":"SUM(J24:J31)","v":0}]
[]
[null,null,null,null,null,null,null,null,null,null,null,3]

{"sheet":"SE","index":3,"n_rows":51,"n_cols":34,"rows_kept":51,"cut":false,"hidden":false,"merged":[[6,3,6,4],[17,2,17,10],[7,2,7,3],[8,2,8,3],[8,8,8,9],[9,2,9,3],[10,2,10,3],[11,2,11,3],[12,2,12,3],[13,2,13,3],[13,8,13,9],[16,2,16,3],[10,8,10,9],[11,8,11,9],[12,8,12,9],[14,8,14,9],[15,8,15,9],[16,8,16,9],[34,2,34,3],[39,2,39,3],[38,2,38,4],[36,2,36,4],[35,2,35,4],[32,2,32,9],[21,2,21,4],[21,5,21,9],[31,7,31,8]]}
[]
[]
[null,null,"SELF EMPLOYED"]
[]
[]
[null,null,null,null,null,"Latest Year",null,"Prior Year"]
[null,null,null,"Period Ending"]
[null,null,"Profit and Loss"]
[null,null,"Net Profit before Tax/Distributions ",null,null,0,null,0]
[null,null,"Addbacks"]
[null,null,"Interest Expense",null,null,0,null,0]
[null,null,"Financed Leasing Expense",null,null,0,null,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Depreciation/ Amortisation of Intangibles",null,null,0,null,0]
[null,null,"Salaries/ Wages paid to owners",null,null,0,null,0,{"$f":"IF(OR(H14=\"\",H14=0),\"\",(F14-H14)/H14)","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Other Addbacks",null,null,0,null,0,""]
[null,null,"Other Addbacks",null,null,0,null,0,""]
[null,null,"Business - Cash from Operations",null,null,{"$f":"F9+SUM(F11:F16)","v":0},0,{"$f":"H9+SUM(H11:H16)","v":0},{"$f":"IF(OR(H17=\"\",H17=0),\"\",(F17-H17)/H17)","v":""}]
[]
[]
[]
[]
[null,null,"Continuing Business Financial Commitments",null,null,"Personal commitments should be entered on the Calc worksheet"]
[null,null,null,"Limit","Remaining Term (months)","Assessment Rate",null,"Actual Payment (monthly)",null,"Sensitised Annual Payments"]
[null,null,"Term Loan (P&I)",0,0,{"$f":"'Calc Data'!C5","v":0.08},0,0,null,{"$f":"IFERROR(MAX($H24,PMT($F24/12,$E24,-$D24))*12,\"$0.00\")","v":"$0.00"}]
[null,null,"Term Loan (P&I)",0,0,{"$f":"'Calc Data'!C5","v":0.08},null,0,null,{"$f":"IFERROR(MAX($H25,PMT($F25/12,$E25,-$D25))*12,\"$0.00\")","v":"$0.00"}]
[null,null,"Term Loan (I/O)",0,null,{"$f":"'Calc Data'!C5","v":0.08},null,0,null,{"$f":"IFERROR(MAX($H26,PMT($F26/12,360,-$D26))*12,\"$0.00\")","v":0}]
[null,null,"Line of Credit",0,null,{"$f":"'Calc Data'!C5","v":0.08},null,null,null,{"$f":"IFERROR(D27*F27,\"$0.00\")","v":0}]
[null,null,"Overdraft",0,null,{"$f":"'Calc Data'!C5","v":0.08},0,null,null,{"$f":"IFERROR(D28*F28,\"$0.00\")","v":0}]
[null,null,"Lease/HP",0,null,null,null,0,null,{"$f":"IFERROR(H29*12,\"$0.00\")","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,"Other",0,null,null,null,0,null,{"$f":"IFERROR(H30*12,\"$0.00\")","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,"Credit Cards",0,null,null,null,null,null,{"$f":"IF(D31=\"\",0,D31*0.0382*12)","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,null,null,null,null,null,"Sensitised Totals",null,{"$f":"SUM(J24:J31)","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"N/A",null,null,null,0,null,null,"N/A"]
[null,null,null,null,null,null,null,null,null,null,null,3]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,"Distribution of Business Income",null,null,null,null,"Applicant 1",null,"Applicant 2",null,"Applicant 3",null,"Applicant 4"]
[null,null,{"$f":"IF(AND((H37+J37+L37+N37)<=F37,(H39+J39+L39+N39)<=F39),\"Apportion wages/salaries and business cash flow to applicants\",\"APPORTIONED VALUES EXCEED AVAILABLE INCOME\")","v":"Apportion wages/salaries and business cash flow to applicants"},null,null,"Available Income",null,"$ Share of Income",0,"$ Share of Income",null,"$ Share of Income",null,"$ Share of Income"]
[null,null,"Adjusted Salaries/Wages paid to owners",null,null,{"$f":"IFERROR(ROUND(AVERAGE(F14,H14),0),0)","v":0}]
[null,null,null,null,null,""]
[null,null,"Residual Business Cash Flow for distribution",null,null,{"$f":"IFERROR(ROUND(AVERAGE(F17,H17)-J32-F37,0),0)","v":0}]
[null,null,null,null,null,""]
[]
[null,null,null,null,null,null,0,null,null,0,null,null,0,null,null,0]
[null,null,null,null,null,null,0,null,null,0,null,null,0,null,null,0]
[null,null,null,null,null,null,0,null,null,0,null,null,0,null,null,0]
[null,null,null,null,null,null,0,null,null,0,null,null,0,null,null,0]
[null,null,null,null,null,null,0,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,0,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,0,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,0]

{"sheet":"Rental","index":4,"n_rows":21,"n_cols":32,"rows_kept":21,"cut":false,"hidden":false,"merged":[[19,4,19,5],[19,10,19,14],[20,4,20,5],[3,3,3,9],[3,10,4,13],[3,20,3,22],[3,23,3,25],[3,26,3,28],[3,29,3,31],[18,4,18,5]]}
[]
[]
[]
[null,null,null,"RENTAL INCOME & NEGATIVE GEARING CALCULATOR ",null,null,null,null,null,null,"Ownership Percentage",null,null,null,null,null,null,null,null,null,"APP 1",null,null,"APP 2",null,null,"APP 3",null,null,"APP 4"]
[]
[null,null,null,null,"Investment Property address","Type","Rental Income"," Frequency","Loan Amount"," Interest Rate","APP 1","APP 2","APP 3","APP 4","Total OP%",null,null,null,null,"DR","ARI","Interest","RI","ARI","Interest","RI","ARI","Interest","RI","ARI","Interest","RI"]
[null,null,null,"Property 1",null,null,null,"Please Select",null,null,null,null,null,null,{"$f":"IF(SUM(K7:N7)=0,\"\",SUM(K7:N7))","v":""},null,null,null,null,{"$f":"IF(F7=\"Residential long-term\",0.8,IF(F7=\"Residential short-term\",0.7,IF(F7=\"Commercial\",0.7,0)))","v":0},{"$f":"IF(O7<>100%,0,IF(H7=\"Please Select\", 0,IF(H7=\"Weekly\",G7*52*K7*T7,IF(H7=\"Fortnightly\",G7*26*K7*T7,IF(H7=\"Monthly\",G7*12*K7*T7,G7*K7*T7)))))","v":0},{"$f":"IF(O7<>100%,0,I7*J7*K7)","v":0},{"$f":"IF(O7<>100%,0,IF(H7=\"Please Select\", 0,IF(H7=\"Weekly\",G7*52*K7,IF(H7=\"Fortnightly\",G7*26*K7,IF(H7=\"Monthly\",G7*12*K7,G7*K7)))))","v":0},{"$f":"IF(O7<>100%,0,IF(H7=\"Please Select\", 0,IF(H7=\"Weekly\",G7*52*L7*T7,IF(H7=\"Fortnightly\",G7*26*L7*T7,IF(H7=\"Monthly\",G7*12*L7*T7,G7*L7*T7)))))","v":0},{"$f":"IF(O7<>100%,0,I7*J7*L7)","v":0},{"$f":"IF(O7<>100%,0,IF(H7=\"Please Select\", 0,IF(H7=\"Weekly\",G7*52*L7,IF(H7=\"Fortnightly\",G7*26*L7,IF(H7=\"Monthly\",G7*12*L7,G7*L7)))))","v":0},{"$f":"IF(O7<>100%,0,IF(H7=\"Please Select\", 0,IF(H7=\"Weekly\",G7*52*M7*T7,IF(H7=\"Fortnightly\",G7*26*M7*T7,IF(H7=\"Monthly\",G7*12*M7*T7,G7*M7*T7)))))","v":0},{"$f":"IF(O7<>100%,0,I7*J7*M7)","v":0},{"$f":"IF(O7<>100%,0,IF(H7=\"Please Select\", 0,IF(H7=\"Weekly\",G7*52*M7,IF(H7=\"Fortnightly\",G7*26*M7,IF(H7=\"Monthly\",G7*12*M7,G7*M7)))))","v":0},{"$f":"IF(O7<>100%,0,IF(H7=\"Please Select\", 0,IF(H7=\"Weekly\",G7*52*N7*T7,IF(H7=\"Fortnightly\",G7*26*N7*T7,IF(H7=\"Monthly\",G7*12*N7*T7,G7*N7*T7)))))","v":0},{"$f":"IF(O7<>100%,0,I7*J7*N7)","v":0},{"$f":"IF(O7<>100%,0,IF(H7=\"Please Select\", 0,IF(H7=\"Weekly\",G7*52*N7,IF(H7=\"Fortnightly\",G7*26*N7,IF(H7=\"Monthly\",G7*12*N7,G7*N7)))))","v":0}]
[null,null,null,"Property 2",null,null,null,"Please Select",null,null,null,null,null,null,{"$f":"IF(SUM(K8:N8)=0,\"\",SUM(K8:N8))","v":""},null,null,null,null,{"$f":"IF(F8=\"Residential long-term\",0.8,IF(F8=\"Residential short-term\",0.7,IF(F8=\"Commercial\",0.7,0)))","v":0},{"$f":"IF(O8<>100%,0,IF(H8=\"Please Select\", 0,IF(H8=\"Weekly\",G8*52*K8*T8,IF(H8=\"Fortnightly\",G8*26*K8*T8,IF(H8=\"Monthly\",G8*12*K8*T8,G8*K8*T8)))))","v":0},{"$f":"IF(O8<>100%,0,I8*J8*K8)","v":0},{"$f":"IF(O8<>100%,0,IF(H8=\"Please Select\", 0,IF(H8=\"Weekly\",G8*52*K8,IF(H8=\"Fortnightly\",G8*26*K8,IF(H8=\"Monthly\",G8*12*K8,G8*K8)))))","v":0},{"$f":"IF(O8<>100%,0,IF(H8=\"Please Select\", 0,IF(H8=\"Weekly\",G8*52*L8*T8,IF(H8=\"Fortnightly\",G8*26*L8*T8,IF(H8=\"Monthly\",G8*12*L8*T8,G8*L8*T8)))))","v":0},0,0,{"$f":"IF(O8<>100%,0,IF(H8=\"Please Select\", 0,IF(H8=\"Weekly\",G8*52*M8*T8,IF(H8=\"Fortnightly\",G8*26*M8*T8,IF(H8=\"Monthly\",G8*12*M8*T8,G8*M8*T8)))))","v":0},0,0,{"$f":"IF(O8<>100%,0,IF(H8=\"Please Select\", 0,IF(H8=\"Weekly\",G8*52*N8*T8,IF(H8=\"Fortnightly\",G8*26*N8*T8,IF(H8=\"Monthly\",G8*12*N8*T8,G8*N8*T8)))))","v":0},0,0]
[null,null,null,"Property 3",null,null,null,"Please Select",null,null,null,null,null,null,"",null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Property 4",null,null,null,"Please Select",null,null,null,null,null,null,"",null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Property 5",null,null,null,"Please Select",null,null,null,null,null,null,"",null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Property 6",null,null,null,"Please Select",null,null,null,null,null,null,"",null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Property 7",null,null,null,"Please Select",null,null,null,null,null,null,"",null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Property 8",null,null,null,"Please Select",null,null,null,null,null,null,"",null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Property 9",null,null,null,"Please Select",null,null,null,null,null,null,"",null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,"Property 10",null,null,null,"Please Select",null,null,null,null,null,null,"",null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(U7:U16)","v":0},{"$f":"SUM(V7:V16)","v":0},{"$f":"SUM(W7:W16)","v":0},0,0,0,0,0,0,0,0,0]
[null,null,null,null,null,null,"APPLICANT 1","APPLICANT 2","APPLICANT 3","APPLICANT 4"]
[null,null,null,null,"Rental Income",null,{"$f":"W17","v":0},{"$f":"Z17","v":0},{"$f":"AC17","v":0},{"$f":"AF17","v":0}]
[null,null,null,null,"Discounted Rental Income",null,{"$f":"U17","v":0},{"$f":"X17","v":0},{"$f":"AA17","v":0},{"$f":"AD17","v":0}]
[null,null,null,null,"Investment Interest",null,{"$f":"V17","v":0},{"$f":"Y17","v":0},{"$f":"AB17","v":0},{"$f":"AE17","v":0}]

{"sheet":"Calc Data","index":5,"n_rows":50,"n_cols":12,"rows_kept":50,"cut":false,"hidden":true,"merged":[[36,15,36,18],[17,1,17,3],[25,15,25,18],[1,15,1,18],[1,9,1,12]]}
[]
[null,"Retail Margin",0.03,null,"Variable personal-loan rate",0.1159]
[null,"Retail Floor",0.08,null,"New car loan rate",7.0400000000000004E-2]
[null,"Commercial Margin",2.5000000000000001E-2]
[null,"Commercial Floor",0.08]
[]
[null,"Loan","Period",null,null,"Dependents Flags"]
[null,"Commercial Property","Please Select",null,null,"Applicant 1",{"$f":"IF(AND(Calc!AH29=\"App 1\",Calc!AE29>0),1,IF(AND(Calc!AH30=\"App 1\",Calc!AE30>0),1,IF(AND(Calc!AH31=\"App 1\",Calc!AE31>0),1,0)))","v":0}]
[null,"Commercial Plus","Weekly"]
[null,"Premium Package","Fortnightly"]
[null,"Low Rate Essentials","Monthly",null,null,"Applicant 2",{"$f":"IF(AND(Calc!AH28=\"App 2\",Calc!AE28>0),1,IF(AND(Calc!AH30=\"App 2\",Calc!AE30>0),1,IF(AND(Calc!AH31=\"App 2\",Calc!AE31>0),1,0)))","v":0}]
[null,"Standard Variable","Annually",null,null,"Applicant 3",{"$f":"IF(AND(Calc!AH28=\"App 3\",Calc!AE28>0),1,IF(AND(Calc!AH29=\"App 3\",Calc!AE29>0),1,IF(AND(Calc!AH31=\"App 3\",Calc!AE31>0),1,0)))","v":0}]
[null,"Equitismart",null,null,null,"Applicant 4",{"$f":"IF(AND(Calc!AH28=\"App 4\",Calc!AE28>0),1,IF(AND(Calc!AH29=\"App 4\",Calc!AE29>0),1,IF(AND(Calc!AH30=\"App 4\",Calc!AE30>0),1,0)))","v":0}]
[null,"Construction"]
[null,"Land"]
[]
[]
[null,"2022-2023"]
[null,"Tax Boundary","Tax Due","Excess %"]
[null,0,0,0]
[null,18200,0,0.19]
[null,45000,{"$f":"C21+(B22-B21)*D21","v":5092},0.32500000000000001]
[null,120000,{"$f":"C22+(B23-B22)*D22","v":29467},0.37]
[null,180000,{"$f":"C23+(B24-B23)*D23","v":51667},0.45]
[]
[null,"APPLICANT 1",null,null,"APPLICANT 2",null,null,"APPLICANT 3",null,null,"APPLICANT 4"]
[null,"ANNUAL INCOME:",{"$f":"Calc!G35","v":0},null,"ANNUAL INCOME:",{"$f":"Calc!J35","v":0},null,"ANNUAL INCOME:",{"$f":"Calc!M35","v":0},null,"ANNUAL INCOME:",{"$f":"Calc!P35","v":0}]
[null,"Investment Interest:",{"$f":"Rental!G21","v":0},null,"Investment Interest:",{"$f":"Rental!H21","v":0},null,"Investment Interest:",{"$f":"Rental!I21","v":0},null,"Investment Interest:",{"$f":"Rental!J21","v":0}]
[null,"Tax- free income:",{"$f":"Calc!G34","v":0},null,"Tax- free income:",{"$f":"Calc!J34","v":0},null,"Tax- free income:",{"$f":"Calc!M34","v":0},null,"Tax- free income:",{"$f":"Calc!P34","v":0}]
[null,"Taxable Income:",{"$f":"C27-C28-C29","v":0},null,"Taxable Income:",{"$f":"F27-F28-F29","v":0},null,"Taxable Income:",{"$f":"I27-I28-I29","v":0},null,"Taxable Income:",{"$f":"L27-L28-L29","v":0}]
[null,"Tax Boundary: ",{"$f":"IF(C30<0, 0, VLOOKUP(C30,TaxTable,1))","v":0},null,"Tax Boundary: ",{"$f":"IF(F30<0, 0, VLOOKUP(F30,TaxTable,1))","v":0},null,"Tax Boundary: ",{"$f":"IF(I30<0, 0, VLOOKUP(I30,TaxTable,1))","v":0},null,"Tax Boundary: ",{"$f":"IF(L30<0, 0, VLOOKUP(L30,TaxTable,1))","v":0}]
[null,"Tax Due on Boundary : ",{"$f":"IF(C30<0, 0, VLOOKUP(C30,TaxTable,2))","v":0},null,"Tax Due on Boundary : ",{"$f":"IF(F30<0, 0, VLOOKUP(F30,TaxTable,2))","v":0},null,"Tax Due on Boundary : ",{"$f":"IF(I30<0, 0, VLOOKUP(I30,TaxTable,2))","v":0},null,"Tax Due on Boundary : ",{"$f":"IF(L30<0, 0, VLOOKUP(L30,TaxTable,2))","v":0}]
[null,"% Due on Excess : ",{"$f":"IF(C30<0, 0, VLOOKUP(C30,TaxTable,3))","v":0},null,"% Due on Excess : ",{"$f":"IF(F30<0, 0, VLOOKUP(F30,TaxTable,3))","v":0},null,"% Due on Excess : ",{"$f":"IF(I30<0, 0, VLOOKUP(I30,TaxTable,3))","v":0},null,"% Due on Excess : ",{"$f":"IF(L30<0, 0, VLOOKUP(L30,TaxTable,3))","v":0}]
[]
[null,"TAX DUE ON INCOME : ",{"$f":"C32+(C30-C31)*C33","v":0},null,"TAX DUE ON INCOME : ",{"$f":"F32+(F30-F31)*F33","v":0},null,"TAX DUE ON INCOME : ",{"$f":"I32+(I30-I31)*I33","v":0},null,"TAX DUE ON INCOME : ",{"$f":"L32+(L30-L31)*L33","v":0}]
[]
[]
[]
[null,"MEDICARE",{"$f":"IF(C30<=18200,0,C30*2%)","v":0},null,"MEDICARE",{"$f":"IF(F30<=18200,0,F30*2%)","v":0},null,"MEDICARE",{"$f":"IF(I30<=18200,0,I30*2%)","v":0},null,"MEDICARE",{"$f":"IF(L30<=18200,0,L30*2%)","v":0}]
[null,"TAX DUE ON INCOME : ",{"$f":"C35+C39","v":0},null,"TAX DUE ON INCOME : ",{"$f":"F35+F39","v":0},null,"TAX DUE ON INCOME : ",{"$f":"I35+I39","v":0},null,"TAX DUE ON INCOME : ",{"$f":"L35+L39","v":0}]
[]
[null,"Net Operating Income",{"$f":"ROUND(Calc!S35+Calc!S23-(C40+F40+I40+L40)-Calc!AG39,2)","v":0}]
[null,"Debt Service Ratio (DSR)",{"$f":"ROUND(C42/(C45+C46),4)","v":{"$error":"#DIV/0!"}},null,"Minimum DSR",1.1499999999999999]
[]
[null,"Existing Service",{"$f":"ROUND(SUM(Calc!S41:T53),2)","v":0}]
[null,"New Service",{"$f":"ROUND(SUM(Calc!S54:T56),2)","v":0}]
[]
[]
[null,"Maximum Service",{"$f":"ROUND((C42-(F43*C45))/F43,2)","v":0}]
[null,"Maximum Loan Amount",{"$f":"-PV((Calc!M12+C4)/12,360,C49/12)","v":0}]

{"sheet":"HEM Table","index":6,"n_rows":91,"n_cols":22,"rows_kept":91,"cut":false,"hidden":true,"merged":[[19,0,21,2],[49,3,49,6],[56,3,56,5],[80,3,80,14]]}
[null,null,null,null,null,"\"HEM Table 1\" equivalent: by Income Level and Residency",null,null,null,null,null,null,"Version","2023 Q4"]
[null,null,null,null,null,null,null,"BASED ON PREDICTED VALUES FROM THE QUANTILE REGRESSIONS"]
[null,null,null,null,null,null,null,null,null,"Smoothed HEM"]
[]
[null,null,null,null,null,null,null,"Total current gross HH income from all sources (measured in current dollars)"]
[null,null,null,null,25000,25000,38000,50000,63000,76000,101000,126000,151000,176000,202000,252000,315000,378000]
[null,null,null,null,"or less","to","to","to","to","to","to","to","to","to","to","to","to","to"]
[null,null,null,null,null,38000,50000,63000,76000,101000,126000,151000,176000,202000,252000,315000,378000,630000]
[null,null,null,"Australia"]
[null,null,null,"Couple",0,577.37112077691938,592.80248923866702,612.87570230653466,650.86135184334967,710.17364568731784,790.57201488953683,863.72292660011283,952.40274124712857,991.24596645826273,1039.8894757354115,1169.5360345429374,1283.8028509591607,1350.4863582094147]
[null,null,null,"Couple with 1 child",0,0,684.36493423306592,704.49507774302117,742.58844228460543,802.06893590064442,882.69527023359262,956.05361424820603,1044.9849706066934,1083.9382681636118,1132.7197333019139,1262.733927778977,1377.3247678942896,1444.1973679832622]
[null,null,null,"Couple with 2 children",0,0,753.45434042300951,773.54893547448637,811.57512794538525,870.95065572696933,951.43481407598438,1024.6637477109252,1113.4381286486168,1152.3227278477937,1201.0181940692498,1330.8030324110825,1445.1916501699732,1511.9463556196945]
[null,null,null,"Couple with 3 children",0,0,0,843.73802146397747,881.72955151675797,941.05113182922094,1021.461988874392,1094.6242779389968,1183.3179262661724,1222.1672027254278,1270.8182714354075,1400.4850274199475,1514.7696450577321,1581.4635778282702]
[]
[null,null,null,"Single person",293.84495491326561,310.88818989722125,326.22551646503047,346.17642052081828,383.93057743905507,442.88147325127164,522.78991729981658,595.49507622743295,683.63454798778525,722.24100411316215,770.58813594605601,899.44473514849903,1013.0151808842029,1079.2924189644859]
[null,null,null,"Single parent with 1 child",0,422.20340605269445,437.69041745230828,457.83601723333499,495.95862019848295,555.48483943379745,636.17312668153579,709.58783044400752,798.58743646095206,837.5706610569556,886.38965991122666,1016.503741692415,1131.1825451341272,1198.1065966268554]
[null,null,null,"Single parent with 2 children",0,524.78955986487233,540.19825066159012,560.24193759165212,598.17165857552584,657.39671832785905,737.67675357677581,810.72003220067222,899.26938331703741,938.05539961550198,986.62740975381723,1116.0832086791279,1230.1818205750458,1296.767284920707]
[null,null,null,"Single parent with 3 children",0,0,642.70608387087191,662.64785794996919,700.38469695256867,759.30859722192065,839.18038047201583,911.85223395733692,999.95133017312276,1038.5401381740485,1086.8651595964079,1215.6626756658407,1329.1810960159644,1395.4279732145585]
[]
["Extrapolated HEM Table",null,null,"Income Band","A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","O","O","O"]
[null,null,null,"Income Band Lower",{"$f":"E8","v":0},{"$f":"F6","v":25000},{"$f":"G6","v":38000},50000,63000,76000,101000,126000,151000,176000,202000,252000,315000,378000,1,2,3,4]
[null,null,null,"Income Band Upper",{"$f":"E6","v":25000},{"$f":"F8","v":38000},{"$f":"G8","v":50000},63000,76000,101000,126000,151000,176000,202000,252000,315000,378000,630000,{"$f":"H83","v":0},{"$f":"H84","v":0},{"$f":"H85","v":0},{"$f":"H86","v":0}]
["Applicants","Dependents","App/Dep ID"]
[2,0,{"$f":"A24&B24","v":"20"},"Couple",{"$f":"ROUNDUP(IF(E10>0,E10,F24),0)","v":578},{"$f":"ROUNDUP(IF(F10>0,F10,G24),0)","v":578},593,613,651,711,791,864,953,992,1040,1170,1284,1351,{"$f":"ROUNDUP((S$22/AVERAGE($R$22,$R$21))*($R24-$Q24)+$Q24,0)","v":1284},{"$f":"ROUNDUP((T$22/AVERAGE($R$22,$R$21))*($R24-$Q24)+$Q24,0)","v":1284},{"$f":"ROUNDUP((U$22/AVERAGE($R$22,$R$21))*($R24-$Q24)+$Q24,0)","v":1284},{"$f":"ROUNDUP((V$22/AVERAGE($R$22,$R$21))*($R24-$Q24)+$Q24,0)","v":1284}]
[2,1,{"$f":"A25&B25","v":"21"},"Couple with 1 child",{"$f":"ROUNDUP(IF(E11>0,E11,F25),0)","v":685},685,685,705,743,803,883,957,1045,1084,1133,1263,1378,1445,{"$f":"ROUNDUP((S$22/AVERAGE($R$22,$R$21))*($R25-$Q25)+$Q25,0)","v":1378},1378,1378,1378]
[2,2,{"$f":"A26&B26","v":"22"},"Couple with 2 children",754,754,754,774,812,871,952,1025,1114,1153,1202,1331,1446,1512,1446,1446,1446,1446]
[2,3,{"$f":"A27&B27","v":"23"},"Couple with 3 children",844,844,844,844,882,942,1022,1095,1184,1223,1271,1401,1515,1582,1515,1515,1515,1515]
[2,4,{"$f":"A28&B28","v":"24"},"Couple with 4 children",{"$f":"E$27+(E$27-E$26)*($B28-$B$27)","v":934},{"$f":"F$27+(F$27-F$26)*($B28-$B$27)","v":934},934,914,952,1013,1092,1165,1254,1293,1340,1471,1584,1652,1584,1584,1584,1584]
[2,5,"25","Couple with 5 children",{"$f":"E$27+(E$27-E$26)*($B29-$B$27)","v":1024},1024,1024,984,1022,1084,1162,1235,1324,1363,1409,1541,1653,1722,1653,1653,1653,1653]
[2,6,"26","Couple with 6 children",1114,1114,1114,1054,1092,1155,1232,1305,1394,1433,1478,1611,1722,1792,1722,1722,1722,1722]
[2,7,"27","Couple with 7 children",1204,1204,1204,1124,1162,1226,1302,1375,1464,1503,1547,1681,1791,1862,1791,1791,1791,1791]
[2,8,"28","Couple with 8 children",1294,1294,1294,1194,1232,1297,1372,1445,1534,1573,1616,1751,1860,1932,1860,1860,1860,1860]
[2,9,"29","Couple with 9 children",1384,1384,1384,1264,1302,1368,1442,1515,1604,1643,1685,1821,1929,2002,1929,1929,1929,1929]
[2,10,"210","Couple with 10 children",1474,1474,1474,1334,1372,1439,1512,1585,1674,1713,1754,1891,1998,2072,1998,1998,1998,1998]
[]
[1,0,{"$f":"A36&B36","v":"10"},"Single person",{"$f":"ROUNDUP(IF(E15>0,E15,F36),0)","v":294},{"$f":"ROUNDUP(IF(F15>0,F15,G36),0)","v":311},327,347,384,443,523,596,684,723,771,900,1014,1080,{"$f":"ROUNDUP(S$22/AVERAGE($R$22,$R$21)*($R36-$Q36)+$Q36,0)","v":1014},{"$f":"ROUNDUP(T$22/AVERAGE($R$22,$R$21)*($R36-$Q36)+$Q36,0)","v":1014},{"$f":"ROUNDUP(U$22/AVERAGE($R$22,$R$21)*($R36-$Q36)+$Q36,0)","v":1014},{"$f":"ROUNDUP(V$22/AVERAGE($R$22,$R$21)*($R36-$Q36)+$Q36,0)","v":1014}]
[1,1,{"$f":"A37&B37","v":"11"},"Single parent with 1 child",{"$f":"ROUNDUP(IF(E16>0,E16,F37),0)","v":423},423,438,458,496,556,637,710,799,838,887,1017,1132,1199,{"$f":"ROUNDUP(S$22/AVERAGE($R$22,$R$21)*($R37-$Q37)+$Q37,0)","v":1132},1132,1132,1132]
[1,2,{"$f":"A38&B38","v":"12"},"Single parent with 2 children",525,525,541,561,599,658,738,811,900,939,987,1117,1231,1297,1231,1231,1231,1231]
[1,3,{"$f":"A39&B39","v":"13"},"Single parent with 3 children",643,643,643,663,701,760,840,912,1000,1039,1087,1216,1330,1396,1330,1330,1330,1330]
[1,4,{"$f":"A40&B40","v":"14"},"Single Parent with 4 children",{"$f":"E$39+(E$39-E$38)*($B40-$B$27)","v":761},{"$f":"F$39+(F$39-F$38)*($B40-$B$27)","v":761},745,765,803,862,942,1013,1100,1139,1187,1315,1429,1495,1429,1429,1429,1429]
[1,5,"15","Single Parent with 5 children",{"$f":"E$39+(E$39-E$38)*($B41-$B$27)","v":879},879,847,867,905,964,1044,1114,1200,1239,1287,1414,1528,1594,1528,1528,1528,1528]
[1,6,"16","Single Parent with 6 children",997,997,949,969,1007,1066,1146,1215,1300,1339,1387,1513,1627,1693,1627,1627,1627,1627]
[1,7,"17","Single Parent with 7 children",1115,1115,1051,1071,1109,1168,1248,1316,1400,1439,1487,1612,1726,1792,1726,1726,1726,1726]
[1,8,"18","Single Parent with 8 children",1233,1233,1153,1173,1211,1270,1350,1417,1500,1539,1587,1711,1825,1891,1825,1825,1825,1825]
[1,9,"19","Single Parent with 9 children",1351,1351,1255,1275,1313,1372,1452,1518,1600,1639,1687,1810,1924,1990,1924,1924,1924,1924]
[1,10,"110","Single Parent with 10 children",1469,1469,1357,1377,1415,1474,1554,1619,1700,1739,1787,1909,2023,2089,2023,2023,2023,2023]
[]
[]
[]
[null,null,null,"Relationship Status"]
[null,null,null,"Applicant","Status","Dependents","Partner"]
[null,null,null,"Individual 1",{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D52,Calc!$X$28:$X$31,0),MATCH(E$51,Calc!$AA$27:$AI$27,0))","v":"N/A"},{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D52,Calc!$X$28:$X$31,0),MATCH(F$51,Calc!$AA$27:$AI$27,0))","v":0},{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D52,Calc!$X$28:$X$31,0),MATCH(G$51,Calc!$AA$27:$AI$27,0))","v":"N/A"}]
[null,null,null,"Individual 2",{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D53,Calc!$X$28:$X$31,0),MATCH(E$51,Calc!$AA$27:$AI$27,0))","v":"N/A"},{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D53,Calc!$X$28:$X$31,0),MATCH(F$51,Calc!$AA$27:$AI$27,0))","v":0},{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D53,Calc!$X$28:$X$31,0),MATCH(G$51,Calc!$AA$27:$AI$27,0))","v":"N/A"}]
[null,null,null,"Individual 3",{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D54,Calc!$X$28:$X$31,0),MATCH(E$51,Calc!$AA$27:$AI$27,0))","v":"N/A"},{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D54,Calc!$X$28:$X$31,0),MATCH(F$51,Calc!$AA$27:$AI$27,0))","v":0},{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D54,Calc!$X$28:$X$31,0),MATCH(G$51,Calc!$AA$27:$AI$27,0))","v":"N/A"}]
[null,null,null,"Individual 4",{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D55,Calc!$X$28:$X$31,0),MATCH(E$51,Calc!$AA$27:$AI$27,0))","v":"N/A"},{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D55,Calc!$X$28:$X$31,0),MATCH(F$51,Calc!$AA$27:$AI$27,0))","v":0},{"$f":"INDEX(Calc!$AA$28:$AI$31,MATCH($D55,Calc!$X$28:$X$31,0),MATCH(G$51,Calc!$AA$27:$AI$27,0))","v":"N/A"}]
[]
[null,null,null,"Income Band Calculation"]
[null,null,null,"Income Band Lower","Income Band Upper","Income Band"]
[null,null,null,{"$f":"E21","v":0},{"$f":"E22","v":25000},"A"]
[null,null,null,{"$f":"E59+1","v":25001},{"$f":"OFFSET($E$22,0,ROW(2:2)-1)","v":38000},"B"]
[null,null,null,38001,{"$f":"OFFSET($E$22,0,ROW(3:3)-1)","v":50000},"C"]
[null,null,null,50001,{"$f":"OFFSET($E$22,0,ROW(4:4)-1)","v":63000},"D"]
[null,null,null,63001,{"$f":"OFFSET($E$22,0,ROW(5:5)-1)","v":76000},"E"]
[null,null,null,76001,{"$f":"OFFSET($E$22,0,ROW(6:6)-1)","v":101000},"F"]
[null,null,null,101001,{"$f":"OFFSET($E$22,0,ROW(7:7)-1)","v":126000},"G"]
[null,null,null,126001,{"$f":"OFFSET($E$22,0,ROW(8:8)-1)","v":151000},"H"]
[null,null,null,151001,{"$f":"OFFSET($E$22,0,ROW(9:9)-1)","v":176000},"I"]
[null,null,null,176001,{"$f":"OFFSET($E$22,0,ROW(10:10)-1)","v":202000},"J"]
[null,null,null,202001,{"$f":"OFFSET($E$22,0,ROW(11:11)-1)","v":252000},"K"]
[null,null,null,252001,{"$f":"OFFSET($E$22,0,ROW(12:12)-1)","v":315000},"L"]
[null,null,null,315001,{"$f":"OFFSET($E$22,0,ROW(13:13)-1)","v":378000},"M"]
[null,null,null,378001,{"$f":"OFFSET($E$22,0,ROW(14:14)-1)","v":630000},"N"]
[null,null,null,630001,null,"O"]
[]
[null,null,null,"Group ID Matrix","Individual 1","Individual 2","Individual 3","Individual 4",0,"Non Applicant"]
[null,null,null,"Individual 1",0,1,1,1,2,3]
[null,null,null,"Individual 2",1,0,4,4,5,6]
[null,null,null,"Individual 3",1,4,0,7,8,9]
[null,null,null,"Individual 4",1,4,7,0,10,11]
[]
[null,null,null,"HEM Calculation"]
[null,null,null,"Applicant Short","Applicant Long","Partner","Income","Grouped Income","Income Band","Applicants","Dependents","Grouped Dependents","App/Dep ID","Group ID","HEM"]
[null,null,null,1,"Individual 1",{"$f":"IF(OR(G52=0,G52=\"N/A\"),0,IF(G52=\"Non App\",\"Non Applicant\",\"Individual \"&RIGHT(G52,1)))","v":0},{"$f":"Calc!G35","v":0},{"$f":"SUMIFS($G$83:$G$86,$E$83:$E$86,E83)+SUMIFS($G$83:$G$86,$F$83:$F$86,E83)","v":0},{"$f":"IF(AND(H83>=$D$59,H83<=$E$59),$F$59,IF(AND(H83>=$D$60,H83<=$E$60),$F$60,IF(AND(H83>=$D$61,H83<=$E$61),$F$61,IF(AND(H83>=$D$62,H83<=$E$62),$F$62,IF(AND(H83>=$D$63,H83<=$E$63),$F$63,IF(AND(H83>=$D$64,H83<=$E$64),$F$64,IF(AND(H83>=$D$65,H83<=$E$65),$F$65,IF(AND(H83>=$D$66,H83<=$E$66),$F$66,IF(AND(H83>=$D$67,H83<=$E$67),$F$67,IF(AND(H83>=$D$68,H83<=$E$68),$F$68,IF(AND(H83>=$D$69,H83<=$E$69),$F$69,IF(AND(H83>=$D$70,H83<=$E$70),$F$70,IF(AND(H83>=$D$71,H83<=$E$71),$F$71,IF(AND(H83>=$D$72,H83<=$E$72),$F$72,IF(H83>=$D$73,$F$73)))))))))))))))","v":"A"},{"$f":"IF(OR(E52=\"N/A\",E52=0),0,IF(F83=0,1,2))","v":0},{"$f":"Calc!AE28","v":0},{"$f":"SUMIF($E$83:$E$86,E83,$K$83:$K$86)+SUMIF($F$83:$F$86,E83,$K$83:$K$86)","v":0},{"$f":"J83&L83","v":"00"},{"$f":"SUMPRODUCT(($E$76:$J$79)*($E$75:$J$75=F83)*($D$76:$D$79=E83))","v":2},{"$f":"IF(I83=\"O\",SUMPRODUCT(($S$24:$V$46)*($S$21:$V$21=D83)*($C$24:$C$46=M83))/COUNTIF($N$83:$N$86,N83),SUMPRODUCT(($E$24:$R$46)*($E$20:$R$20=I83)*($C$24:$C$46=M83))/COUNTIF($N$83:$N$86,N83))","v":0}]
[null,null,null,2,"Individual 2",{"$f":"IF(OR(G53=\"N/A\",G53=0),0,IF(G53=\"Non App\",\"Non Applicant\",\"Individual \"&RIGHT(G53,1)))","v":0},{"$f":"Calc!J35","v":0},{"$f":"SUMIFS($G$83:$G$86,$E$83:$E$86,E84)+SUMIFS($G$83:$G$86,$F$83:$F$86,E84)","v":0},{"$f":"IF(AND(H84>=$D$59,H84<=$E$59),$F$59,IF(AND(H84>=$D$60,H84<=$E$60),$F$60,IF(AND(H84>=$D$61,H84<=$E$61),$F$61,IF(AND(H84>=$D$62,H84<=$E$62),$F$62,IF(AND(H84>=$D$63,H84<=$E$63),$F$63,IF(AND(H84>=$D$64,H84<=$E$64),$F$64,IF(AND(H84>=$D$65,H84<=$E$65),$F$65,IF(AND(H84>=$D$66,H84<=$E$66),$F$66,IF(AND(H84>=$D$67,H84<=$E$67),$F$67,IF(AND(H84>=$D$68,H84<=$E$68),$F$68,IF(AND(H84>=$D$69,H84<=$E$69),$F$69,IF(AND(H84>=$D$70,H84<=$E$70),$F$70,IF(AND(H84>=$D$71,H84<=$E$71),$F$71,IF(AND(H84>=$D$72,H84<=$E$72),$F$72,IF(H84>=$D$73,$F$73)))))))))))))))","v":"A"},{"$f":"IF(OR(E53=\"N/A\",E53=0),0,IF(F84=0,1,2))","v":0},{"$f":"Calc!AE29","v":0},{"$f":"SUMIF($E$83:$E$86,E84,$K$83:$K$86)+SUMIF($F$83:$F$86,E84,$K$83:$K$86)","v":0},{"$f":"J84&L84","v":"00"},{"$f":"SUMPRODUCT(($E$76:$J$79)*($E$75:$J$75=F84)*($D$76:$D$79=E84))","v":5},{"$f":"IF(I84=\"O\",SUMPRODUCT(($S$24:$V$46)*($S$21:$V$21=D84)*($C$24:$C$46=M84))/COUNTIF($N$83:$N$86,N84),SUMPRODUCT(($E$24:$R$46)*($E$20:$R$20=I84)*($C$24:$C$46=M84))/COUNTIF($N$83:$N$86,N84))","v":0}]
[null,null,null,3,"Individual 3",{"$f":"IF(OR(G54=\"N/A\",G54=0),0,IF(G54=\"Non App\",\"Non Applicant\",\"Individual \"&RIGHT(G54,1)))","v":0},{"$f":"Calc!M35","v":0},{"$f":"SUMIFS($G$83:$G$86,$E$83:$E$86,E85)+SUMIFS($G$83:$G$86,$F$83:$F$86,E85)","v":0},{"$f":"IF(AND(H85>=$D$59,H85<=$E$59),$F$59,IF(AND(H85>=$D$60,H85<=$E$60),$F$60,IF(AND(H85>=$D$61,H85<=$E$61),$F$61,IF(AND(H85>=$D$62,H85<=$E$62),$F$62,IF(AND(H85>=$D$63,H85<=$E$63),$F$63,IF(AND(H85>=$D$64,H85<=$E$64),$F$64,IF(AND(H85>=$D$65,H85<=$E$65),$F$65,IF(AND(H85>=$D$66,H85<=$E$66),$F$66,IF(AND(H85>=$D$67,H85<=$E$67),$F$67,IF(AND(H85>=$D$68,H85<=$E$68),$F$68,IF(AND(H85>=$D$69,H85<=$E$69),$F$69,IF(AND(H85>=$D$70,H85<=$E$70),$F$70,IF(AND(H85>=$D$71,H85<=$E$71),$F$71,IF(AND(H85>=$D$72,H85<=$E$72),$F$72,IF(H85>=$D$73,$F$73)))))))))))))))","v":"A"},0,{"$f":"Calc!AE30","v":0},0,"00",{"$f":"SUMPRODUCT(($E$76:$J$79)*($E$75:$J$75=F85)*($D$76:$D$79=E85))","v":8},{"$f":"IF(I85=\"O\",SUMPRODUCT(($S$24:$V$46)*($S$21:$V$21=D85)*($C$24:$C$46=M85))/COUNTIF($N$83:$N$86,N85),SUMPRODUCT(($E$24:$R$46)*($E$20:$R$20=I85)*($C$24:$C$46=M85))/COUNTIF($N$83:$N$86,N85))","v":0}]
[null,null,null,4,"Individual 4",{"$f":"IF(OR(G55=\"N/A\",G55=0),0,IF(G55=\"Non App\",\"Non Applicant\",\"Individual \"&RIGHT(G55,1)))","v":0},{"$f":"Calc!P35","v":0},{"$f":"SUMIFS($G$83:$G$86,$E$83:$E$86,E86)+SUMIFS($G$83:$G$86,$F$83:$F$86,E86)","v":0},{"$f":"IF(AND(H86>=$D$59,H86<=$E$59),$F$59,IF(AND(H86>=$D$60,H86<=$E$60),$F$60,IF(AND(H86>=$D$61,H86<=$E$61),$F$61,IF(AND(H86>=$D$62,H86<=$E$62),$F$62,IF(AND(H86>=$D$63,H86<=$E$63),$F$63,IF(AND(H86>=$D$64,H86<=$E$64),$F$64,IF(AND(H86>=$D$65,H86<=$E$65),$F$65,IF(AND(H86>=$D$66,H86<=$E$66),$F$66,IF(AND(H86>=$D$67,H86<=$E$67),$F$67,IF(AND(H86>=$D$68,H86<=$E$68),$F$68,IF(AND(H86>=$D$69,H86<=$E$69),$F$69,IF(AND(H86>=$D$70,H86<=$E$70),$F$70,IF(AND(H86>=$D$71,H86<=$E$71),$F$71,IF(AND(H86>=$D$72,H86<=$E$72),$F$72,IF(H86>=$D$73,$F$73)))))))))))))))","v":"A"},0,{"$f":"Calc!AE31","v":0},0,"00",{"$f":"SUMPRODUCT(($E$76:$J$79)*($E$75:$J$75=F86)*($D$76:$D$79=E86))","v":10},{"$f":"IF(I86=\"O\",SUMPRODUCT(($S$24:$V$46)*($S$21:$V$21=D86)*($C$24:$C$46=M86))/COUNTIF($N$83:$N$86,N86),SUMPRODUCT(($E$24:$R$46)*($E$20:$R$20=I86)*($C$24:$C$46=M86))/COUNTIF($N$83:$N$86,N86))","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(O83:O86)","v":0}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Annual Benchmark",{"$f":"O87*52","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Annual Declared",{"$f":"Calc!AG35","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Max",{"$f":"MAX(O89:O90)","v":0}]

{"sheet":"Instructions","index":7,"n_rows":85,"n_cols":7,"rows_kept":85,"cut":false,"hidden":false,"merged":[[2,2,2,19],[41,2,41,19],[84,2,84,19],[118,4,118,13],[119,2,124,2]]}
[]
[]
[null,null,"Capacity Test Calculator User Guide"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"1. MANDATORY FIELDS: All mandatory fields must be completed; Loan details, Security Value, Base Income. Living expenses is mandatory when application has exposure to individuals.  Location Tool is mandatory when application involves purchase of residential property. An Assessment Result and Indicative Maximum Loan Amount will not display unless all mandatory fields are completed."]
[null,null,"2. CORPORATE APPLICANT 1 & 2 INCOME: Use the Corp Borrower worksheets to enter details related to business income (if applicable)"]
[]
[]
[null,null,"3. PERSONAL INCOME : Enter annual income for any individuals providing financial support for the loan (if applicable). Enter less stable forms of income after discount is applied, typically 80%. "]
[]
[null,null,"4. RENTAL INCOME : Use the Rental  worksheet to enter in the details of rental properties in individual names (if applicable)"]
[null,null," The Ownership percentage must be entered against each property listed and total 100% otherwise no rental income will be included for that property."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"The total Rental Income* and Investment Interest per applicant will display at the bottom of the worksheet. Rental Income will also be carried over to the Calculator worksheet."]
[null,null,"Interest applicable to investment loans is used in calculating taxable income and therefore tax payable. "]
[null,null,"*Rental income is discounted to 70% or 80% depending on property type."]
[]
[]
[]
[]
[]
[]
[]
[null,null,"5. SELF EMPLOYED INCOME  : Business income derived from a business which is not party to the transaction. The income is adjusted for addbacks, ongoing lending commitments and tax."]
[]
[]
[null,null,"Continuing Financial Commitments: Only applicable to contractual commitments in the name of the business. Personal commitments must be entered on the Calc worksheet. "]
[null,null,null,null,null,null," Enter interest/lease expense as addbacks. Commitments are assessed using the larger of repayments calculated using Gateway's qualifying rate or actual payments "]
[]
[null,null,"Distribution of Business Income: Available income (salaries/wages and residual cash flow) must be allocated to the relevant applicants."]
[]
[null,null,"8. EXISTING MORTGAGES AND COMMITMENTS  : Continuing mortgage commitments are assessed at the larger of repayments calculated based on Gateway's qualifying rate or actual repayments."]
[null,null,"Please enter current loan limit, remaining term and actual repayments. Other commitments are calculated based on actual repayment amount. Please enter current limit and repayment amount. "]
[]
[null,null,"7. LIVING EXPENSES : Select the relationship of borrowers relevant to the application using the drop down list. Select appropriate Partner relationship and no. of dependants if applicable."]
[]
[null,null,"Actual Living Expenses:  enter monthly declared living expenses"]
[]
[null,null,"8. ASSESSMENT RESULT : Important messages will display here along with the Assessment Result; Servicing Met, Servicing Refer or Servicing Not Met. "]

{"sheet":"Overview","index":8,"n_rows":30,"n_cols":7,"rows_kept":30,"cut":false,"hidden":true,"merged":[[1,1,1,4],[1,6,1,12],[3,1,3,4],[3,6,3,12],[5,1,5,4],[5,6,5,12],[7,1,7,4],[7,6,7,12],[9,1,9,4],[9,6,9,12],[11,1,11,4],[11,6,11,12],[13,1,13,4],[13,6,13,12],[15,1,15,4],[15,6,15,12],[17,1,17,4],[17,6,17,12],[19,1,19,4],[19,6,19,12],[21,1,21,4],[21,6,21,12],[23,1,23,4],[23,6,23,12],[25,1,25,4],[25,6,25,12],[27,1,27,4],[27,6,27,12],[29,1,29,4],[29,6,29,12]]}
[]
[null,"Spreadsheet Name",null,null,null,null,"Gateway Commercial Calculator"]
[]
[null,"Storage Location",null,null,null,null,"Authorised Risk - Capacity Calculators"]
[]
[null,"Spreadsheet Owner Name / Position",null,null,null,null,"Senior Manager, Credit and Operational Risk"]
[]
[null,"Senior Manager Name / Position",null,null,null,null,"Senior Manager, Credit and Operational Risk"]
[]
[null,"Department",null,null,null,null,"Credit and Operational Risk"]
[]
[null,"Purpose of the Spreadsheet",null,null,null,null,"Capacity calculation for commercial loans"]
[]
[null,"Major EUC",null,null,null,null,"Yes"]
[]
[null,"Level of Confidentiality",null,null,null,null,"Internal and broker use"]
[]
[null,"Security / Backup",null,null,null,null,"System backup"]
[]
[null,"Inputs ",null,null,null,null,"Credit policy, HEM, tax rates, interest rates, customer data"]
[]
[null,"Outputs",null,null,null,null,"DSR "]
[]
[null,"Controls",null,null,null,null,"Calculator definitions and management procedure\nChange register\nCalculators are reviewed by before release\nThe spreadsheets are password protected so no unauthorised changes can occur."]
[]
[null,"Version Number",null,null,null,null,2022.4]
[]
[null,"Last Annual Review"]
[]
[null,"Latest Changes",null,null,null,null,"Released 30/11/2021\n1/2/2022 HEM, location categories\n1/6/2022 updated HEM, location categories, margin reduced to 2.5%\n4/8/2022 minor changes to field names\n26/5/2023 HEM, repayment of existing liabilities, introduced comm floor rate\n29/9/2023 HEM error message that I/O term must equal loan term\n8/3/2024 HEM, PL rate"]