{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Calculator","Non-Discretionary Expenses","Self Employed and Other Incomes","Tables"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":1,"defined_names":93,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Calculator","index":0,"n_rows":93,"n_cols":24,"rows_kept":93,"cut":false,"hidden":false,"merged":[[50,0,50,1],[49,5,50,5],[49,4,50,4],[66,0,72,10],[14,4,14,5],[44,0,44,1],[28,3,28,4],[19,7,25,7],[37,0,38,0],[42,10,43,10],[59,4,60,6],[49,6,50,6],[63,4,64,6],[4,8,4,9],[10,8,10,9],[18,3,18,4],[41,0,41,1],[42,0,42,1],[13,4,13,5],[49,20,49,22],[48,7,48,10],[11,2,11,5],[18,0,18,1],[17,20,17,22],[45,2,45,3]]}
[]
["Refinance Calculator (4AH)",null,null,null,null,null,null,null,null,"Created:",{"$f":"NOW()","v":"2023-08-02T10:14:55.030"}]
["Version 10.0  1/8/23"]
[]
["Instructions ",null,null,null,null,null,null,null,"Cell Index"]
["Insert the relevant details into blank fields in BLUE cells",null,null,null,null,null,null,null,null,"Blue shaded cells require input"]
["Show split loan amounts in correct fields",null,null,null,null,null,null,null,null,"White shaded cells derived from formulas"]
["There must be at least one borrower",null,null,null,null,null,null,null,null,"Light Grey shaded cells applies appropriate scales"]
[]
["1.   LOAN DETAILS"]
[null,null,null,null,null,null,null,null,"Security Position"]
["Application Name / Loan No. / Ref:",null,null,null,null,null,null,null,null,"Value - Owner Occupied Property"]
[null,null,null,null,null,null,null,null,null,"Value - Investment Property/ies"]
["Number of Adult Applicants",null,null,"max 2","Number of Dependents",null,null,"max 5 if more type 5",null,"Total Security Value",{"$f":"SUM(K12:K13)","v":0},null,null,"Adults ",null,null,1,2]
[null,null,null,null,null,null,null,null,null,"Loan to Valuation Ratio (LVR)",{"$f":"IF(ISERROR(C26/K14),0,C26/K14)","v":0},null,null,"Dependants",null,0,1,2,3,4,5]
["Product",null,null,null,null,"Rate Checker  ","resimac.co.nz/"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Prime"]
["Interest Rate - Actual",null,null,null,null,"Assessment Rate",{"$f":"IF(C16=\"\",0%,IF(C16=\"Prime\",Tables!B30,IF(C16=\"Prime Alt Doc\",Tables!B32,IF(C16=\"Specialist\",C18+Tables!C31,IF(C16=\"Prime >80% LVR\",Tables!B33,10%)))))","v":0},null,null,null,null,null,null,"Specialist",null,null,null,null,null,null,"current calculations"]
["Loan Term (yrs)",null,"Loan Amount","IO Term (yrs)",null,"Estimated Actual Repayment*",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"PMT","Monthly payments","Max","Total"]
["Portion A",30,null,null,null,{"$f":"-PMT(($C$18/12),((B20-D20)*12),C20,0)","v":0},null,"*If Interest Only, estimated repayment is after Interest Only period has expired (P&I remaining term)",null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IFERROR(-PMT(H22/12,G22,E22,0),\"0\")*12","v":0},{"$f":"F20*12","v":0},{"$f":"MAX(U20,V20)","v":0},{"$f":"V20*B20","v":0}]
["Portion B",30,null,null,null,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IFERROR(-PMT(H23/12,G23,E23,0),\"0\")*12","v":0},{"$f":"F21*12","v":0},{"$f":"MAX(U21,V21)","v":0},{"$f":"V21*B21","v":0}]
["Portion C",30,null,null,null,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IFERROR(-PMT(H24/12,G24,E24,0),\"0\")*12","v":0},0,0,0]
["Portion D",30,null,null,null,0,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IFERROR(-PMT(H25/12,G25,E25,0),\"0\")*12","v":0},0,0,0]
["Portion E",30,null,null,null,0,{"$f":"IF(-PMT(($G$18/12),((B24-D24)*12),C24,0),(-PMT(($G$18/12),((B24-D24)*12),C24,0)),\"$\")","v":"$"},null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IFERROR(-PMT(H26/12,G26,E26,0),\"0\")*12","v":0},0,0,{"$f":"SUM(U24*G26/12)","v":0}]
["Portion F",30,0,null,null,0,"$",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(W20:W24)","v":0},{"$f":"SUM(X20:X24)","v":0}]
["Total",null,{"$f":"SUM(C20:C25)","v":0},null,null,{"$f":"SUM(F20:F25)","v":0},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(X20:X24)","v":0}]
[]
["2.   INCOME AND EXPENSES"," (Use Self Employed and Other Incomes Tab for Other Income Types)"]
[null,null,"Base Income pa",null,null,"Self Employed Income","Total allowable Supplementary Income","Apportioned and Scaled Rental Income*","Taxable Income ","Tax Applied (Including ACC) ","Total Net Borrower Income After Tax"]
["Individual Applicant 1 ",null,null,null,null,{"$f":"'Self Employed and Other Incomes'!B55","v":0},{"$f":"'Self Employed and Other Incomes'!J9","v":0},{"$f":"'Self Employed and Other Incomes'!J$24","v":0},{"$f":"AUTO_GrossIncome-AUTO_SocialSecurityIncome+F30+G30+H30","v":0},{"$f":"IF(Tables!C$52+Tables!C$53+Tables!C54<0,0,Tables!C$52+Tables!C$53+Tables!C54)","v":0},{"$f":"IF(I30-J30<0,0,I30-J30)","v":0}]
["Individual Applicant 2",null,null,null,null,{"$f":"'Self Employed and Other Incomes'!B56","v":0},{"$f":"'Self Employed and Other Incomes'!J10","v":0},{"$f":"'Self Employed and Other Incomes'!K$24","v":0},{"$f":"AUTO_GrossIncome2-AUTO_SocialSecurityIncome2+F31+G31+H31","v":0},{"$f":"IF(Tables!D$52+Tables!D$53+Tables!D54<0,0,Tables!D$52+Tables!D$53+Tables!D54)","v":0},{"$f":"IF(I31-J31<0,0,I31-J31)","v":0}]
["Individual Applicant 3",null,null,null,null,{"$f":"'Self Employed and Other Incomes'!B57","v":0},{"$f":"'Self Employed and Other Incomes'!J11","v":0},{"$f":"'Self Employed and Other Incomes'!#REF!","v":{"$error":"#REF!"}},{"$f":"AUTO_GrossIncome3-AUTO_SocialSecurityIncome3+F32+G32+H32","v":{"$error":"#REF!"}},{"$f":"Tables!E$52+Tables!E$53+Tables!E54","v":{"$error":"#REF!"}},{"$f":"SUM(I32-J32)","v":{"$error":"#REF!"}}]
["Individual Applicant 4",null,null,null,null,{"$f":"'Self Employed and Other Incomes'!B58","v":0},{"$f":"'Self Employed and Other Incomes'!J12","v":0},{"$f":"'Self Employed and Other Incomes'!#REF!","v":{"$error":"#REF!"}},{"$f":"AUTO_GrossIncome4-AUTO_SocialSecurityIncome4+F33+G33+H33","v":{"$error":"#REF!"}},{"$f":"Tables!F$52+Tables!F$53+Tables!F54","v":{"$error":"#REF!"}},{"$f":"SUM(I33-J33)","v":{"$error":"#REF!"}}]
["Totals for Individuals",null,{"$f":"SUM(C30:C31)","v":0},null,null,{"$f":"SUM(F30:F33)","v":0},{"$f":"SUM(G30:G33)","v":0},{"$f":"SUM(H30:H31)","v":0},{"$f":"SUM(I30:I31)","v":0},{"$f":"SUM(J30:J31)","v":0},{"$f":"SUM(K30:K31)","v":0}]
[null,null,null,null,null,null,null,{"$f":"IF(H30<0,0,H30)","v":0},null,null,null,null,null,"Weekly"]
["Non Discretionary Monthly Living Expenses",null,null,null,"Annualised",null,"Annualised with Buffer",{"$f":"IF(H31<0,0,H31)","v":0},null,null,null,null,null,"Fortnightly"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"4 weekly"]
["Applicant Advised Living Expenses",null,{"$f":"SUM('Non-Discretionary Expenses'!D27)","v":0},null,{"$f":"SUM(AUTO_TotalDeclaredLivingExpenses*12)","v":0},null,{"$f":"'Non-Discretionary Expenses'!L27*12","v":0},null,null,null,null,null,null,"Monthly"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Quarterly"]
["3. Proposed / Ongoing Monthly Financial Expenses",null,null,null,"Limit / Balance","Estimated Repayment Amount pm","Actual Repayment Amount pm",null,null,null,null,null,null,"Annually"]
["Proposed Loan",null,null,null,{"$f":"C26","v":0},{"$f":"F26","v":0},{"$f":"F41","v":0},null,"Net Service Ratio (NSR)",null,{"$f":"P62","v":0}]
["Other Mortgages - Owner Occupied",null,null,null,null,null,{"$f":"AUTO_OtherMortgages","v":0}]
["Other Mortgages - Investment",null,null,null,null,null,{"$f":"AUTO_OtherInvestmentMortgages","v":0},null,null,null,{"$f":"IF(K41=0,\"Incomplete\",IF(K41>1.001,\"Pass\",\"Fail\"))","v":"Incomplete"}]
["Personal Loans",null,null,null,null,null,{"$f":"AUTO_PersonalLoans","v":0}]
["Credit Cards Limit",null,null,null,null,null,{"$f":"MAX(F45,Tables!B69)","v":0}]
["Student Loan, Other Debts",null,null,null,null,null,{"$f":"F46","v":0}]
[]
["4. Existing Monthly Financial Expenses of Loans Being Refinanced ($4$)"]
[null,null,null,null,null,null,null,"Frequency Converter"]
[null,null,null,null,"Original Loan Term (months)","Current Interest Rate","Current Monthly Payments",null,null,null,null,null,null,null,null,"Annual",null,null,null,null,"current calculations"]
[null,null,"Original Loan Amount",null,null,null,null,null,"Amount","Monthly","Annually",null,null,null,null,null,null,null,null,null,"PMT","Monthly payments","Max","Total"]
["Existing Loan 1",null,null,null,null,null,null,"Weekly",null,{"$f":"I52*52/12","v":0},{"$f":"I52*52","v":0},null,null,"Total Gross Income",null,{"$f":"C34+F34+(G34-'Self Employed and Other Incomes'!I13)+'Self Employed and Other Incomes'!B24-'Self Employed and Other Incomes'!C24-'Self Employed and Other Incomes'!D24-'Self Employed and Other Incomes'!E24-'Self Employed and Other Incomes'!F24","v":0},null,null,null,null,{"$f":"IFERROR(-PMT(F52/12,E52,C52,0),\"0\")*12","v":0},{"$f":"G52*12","v":0},{"$f":"MAX(U52,V52)","v":0},{"$f":"SUM(U52*E52/12)","v":0}]
["Existing Loan 2",null,null,null,null,null,null,"Fortnightly",null,{"$f":"I53*26/12","v":0},{"$f":"I53*26","v":0},null,null,"Tax Applicable",null,{"$f":"J34","v":0},null,null,null,null,{"$f":"IFERROR(-PMT(F53/12,E53,C53,0),\"0\")*12","v":0},{"$f":"G53*12","v":0},{"$f":"MAX(U53,V53)","v":0},{"$f":"SUM(U53*E53/12)","v":0}]
["Existing Loan 3",null,null,null,null,null,null,"4 weekly",null,{"$f":"I54*13/12","v":0},{"$f":"I54*13","v":0},null,null,"Net Cash after Tax*",null,{"$f":"P52-P53","v":0},null,null,null,null,0,0,0,0]
["Existing Loan 4",null,null,null,null,null,null,"Monthly",null,null,{"$f":"I55*12","v":0},null,null,"Government Benefits / Tax Free",null,{"$f":"'Self Employed and Other Incomes'!I13","v":0},null,null,null,null,0,0,0,0]
["Existing Loan 5",null,null,null,null,null,null,"Quarterly",null,{"$f":"I56*4/12","v":0},{"$f":"I56*4","v":0},null,null,"Boarder Income ",null,{"$f":"'Self Employed and Other Incomes'!B15*52","v":0},null,null,null,null,0,0,0,0]
["Total",null,{"$f":"SUM(C52:C56)","v":0},null,null,null,{"$f":"SUM(U52:U56)*12","v":0},"Annually",null,{"$f":"I57/12","v":0},null,null,null,"Living Expenses (as per scales)",null,{"$f":"IF(AUTO_NumberAdults=\"\",\"                                             0\",IF('Non-Discretionary Expenses'!D27=0,\"                                              0\",MAX('Non-Discretionary Expenses'!D29,'Non-Discretionary Expenses'!P34)))","v":"                                             0"},null,null,null,null,null,null,{"$f":"SUM(W52:W56)","v":0},{"$f":"SUM(X52:X56)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Net Disposable Income",null,{"$f":"SUM(P54+P55+P56)-P57","v":0},{"$f":"SUM(P58+P60)","v":0}]
["Payments",null,null,null,null,null,null,null,null,null,null,null,null,"Total Financial Expense",null,{"$f":"(G41+G42+G43+G44+G45+G46)*12","v":0}]
["Existing",null,{"$f":"W57","v":0},null,{"$f":"IF(C61=0,\"Incomplete\",IF(C61<=C60,\"Pass- new payment = or < existing\",\"Fail- not suitable for 4AH\"))","v":"Incomplete"}]
["Proposed ",null,{"$f":"G41*12","v":0},null,null,null,null,null,null,null,null,null,null,"Surplus Cash after Financial Expense",null,{"$f":"SUM(P58-P59+P60)","v":0},null,"Minimum: Surplus (+)"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,"Net Service Ratio (NSR)",null,{"$f":"IF(ISERROR(Q58/P59),0,(Q58/P59))","v":0}]
["Overall Cost Calculation"]
["Existing",null,{"$f":"X57","v":0},null,{"$f":"IF(C61=0,\"Incomplete\",IF(C65>=C64,\"Customers to confirm they are aware overall payments are higher and they accept\",\"Overall cost is less than existing\"))","v":"Incomplete"},null,null,null,null,"DTI"]
["Proposed",null,{"$f":"X26","v":0},null,null,null,null,null,null,{"$f":"Tables!H31","v":0}]
["4. COMMENTS"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,1]

{"sheet":"Non-Discretionary Expenses","index":1,"n_rows":43,"n_cols":18,"rows_kept":43,"cut":false,"hidden":false,"merged":[[2,9,4,9],[1,7,1,15]]}
["LIVING EXPENSES"]
[null,null,null,null,null,null,null,"******Hidden Cells***** for Underwriter use*****"]
[null,"MONTHLY NON DISCRETIONARY LIVING EXPENSES",null,null,null,null,null,null,null,"Additional Enquiry (enter all fields if change is made to any)"]
[null,"all catergories must be answered. Mark as n/a where not applicable"]
[null,null,null,"Customer Estimated",null,"Notes",null,"Customer Estimated",null,null,null,"Buffered",null,"Benchmark ",null,"Buffered Benchmark",null,"Adjusted why? verified how?  With what document? Must be answered where customer declares an non benchmarkable expense or if deemed immaterial"]
[null,"Utilities",null,null,null,null,null,{"$f":"D6","v":0},null,null,null,{"$f":"MAX(D6:J6)*1.1","v":0}]
[".","Rates",null,null,null,null,null,0,null,null,null,{"$f":"MAX(D7:J7)*1.1","v":0},null,null,null,null,"Rates"]
[null,"Property Maintenance ",null,null,null,null,null,0,null,null,null,0]
[null,"Food & Takeaways",null,null,null,null,null,0,null,null,null,0]
[null,"Transport",null,null,null,null,null,0,null,null,null,0]
[null,"Clothing and Footwear",null,null,null,null,null,0,null,null,null,0]
[null,"Personal Care",null,null,null,null,null,0,null,null,null,0]
[null,"Childcare",null,null,null,null,null,0,null,null,null,0]
[".","Insurances ",null,null,null,"Supporting document:",null,0,null,null,null,0,null,null,null,null,"Insurance"]
[null,"Communications",null,null,null,null,null,0,null,null,null,0]
[null,"Medical and Health",null,null,null,null,null,0,null,null,null,0]
[null,"Education and Schooling",null,null,null,"Supporting document:",null,0,null,null,null,0,null,null,null,null,"Edu & Sch"]
[null,"Recreation and Entertainment",null,null,null,null,null,0,null,null,null,0]
[null,"Regular Savings ",null,null,null,null,null,0,null,null,null,0]
[".","Body Corp Fees",null,null,null,"Supporting document:",null,0,null,null,null,0,null,null,null,null,"BC fees"]
[null,"Tithings",null,null,null,"Supporting document:",null,0,null,null,null,0,null,null,null,null,"Tithing"]
[".","Child Maintenance",null,null,null,"Supporting document:",null,0,null,null,null,0,null,null,null,null,"Child Maint"]
[".","All Other Regular Expenses",null,null,null,"Supporting document:",null,0,null,null,null,0,null,null,null,null,"Other"]
[]
[".","Rent or Board Paid",null,null,null,"Supporting document:",null,{"$f":"D25","v":0},null,null,null,{"$f":"MAX(D25:J25)","v":0},null,null,null,null,"Rent/Board"]
[]
[null,"Total Monthly Expenses",null,{"$f":"SUM(D6:D25)","v":0},null,null,null,{"$f":"SUM(H6:H25)","v":0},null,{"$f":"SUM(J6:J25)","v":0},null,{"$f":"SUM(L6:L25)","v":0}]
[]
[null,"Annualised",null,{"$f":"SUM(D27*12)","v":0},null,null,"Annualised"]
[null,"Benckmark Items Total",null,{"$f":"SUM(D6+D8+D9+D10+D11+D12+D13+D15+D17)*12","v":0},null,null,"Benchmarked expense type",null,null,{"$f":"SUM(J6+J8+J9+J10+J11+J12+J13+J15+J16+J18)*12","v":0},null,{"$f":"SUM(L6+L8+L9+L10+L11+L12+L13+L15+L16+L18)*12","v":0},null,{"$f":"Tables!B25","v":11984.385570298704},null,{"$f":"Tables!B25","v":11984.385570298704}]
[null,"Non Benchmark Total",null,null,null,null,"Not Benchmarked expense type",null,null,{"$f":"SUM(J7+J14+J17+J19+J20+J21+J22+J23+J25)*12","v":0},null,{"$f":"SUM(L7+L14+L17+L19+L20+L21+L22+L23+L25)*12","v":0},null,null,null,{"$f":"SUM(L31)","v":0}]
[null,null,null,{"$f":"SUM(D7+D14+D16+D18+D19+D20+D21+D22+D23+D25)*12","v":0}]
[]
[null,null,null,null,null,null,"Total",{"$f":"SUM(H27*12)","v":0},null,{"$f":"SUM(J30+J31)","v":0},null,{"$f":"SUM(L30+L31)","v":0},null,null,null,{"$f":"P30+P31","v":11984.385570298704}]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,{"$f":"MAX(L37,L34)","v":0}]

{"sheet":"Self Employed and Other Incomes","index":2,"n_rows":61,"n_cols":31,"rows_kept":61,"cut":false,"hidden":false,"merged":[[0,0,0,6],[2,0,2,6],[60,1,60,3],[39,8,39,9],[54,3,57,4],[8,9,8,10],[9,9,9,10],[10,9,10,10],[11,9,11,10],[12,9,12,10],[47,9,47,10],[48,9,48,10],[46,9,46,10],[33,9,33,10],[45,9,45,10],[26,3,26,4],[16,7,16,8],[26,5,26,6],[49,11,49,12],[37,11,37,12],[49,9,49,10],[48,11,48,12],[16,9,16,10],[7,9,7,10],[34,9,34,10],[36,9,36,10],[35,9,35,10],[47,11,47,12],[33,11,33,12],[45,11,45,12],[34,11,34,12],[35,11,35,12],[36,11,36,12],[46,11,46,12],[37,9,37,10],[17,9,17,10]]}
["SELF EMPLOYED + OTHER INCOME WORKSHEET"]
[]
["Note: All figures are to be entered at 100% and calculator will reduce assessment percentage where appropriate"]
[]
["2.   INCOME AND EXPENSES"]
[]
["Additional Incomes"]
[null,"Base Income","Overtime / Shift Allowance pa","Bonus pa","Commission pa","Investment Income pa","Casual / Second Job pa","Child Support/ Maintenance pa*","Working For Families, Company Car Addback pa","Total allowable Supplementary Income"]
["Individual Applicant 1",{"$f":"AUTO_GrossIncome","v":0},null,null,null,null,null,null,null,{"$f":"SUM(C9*Tables!$A$59,D9*Tables!$B$59,E9*Tables!$C$59,F9*Tables!$D$59,G9*Tables!$E$59,H9*Tables!$F$59,I9*Tables!$G$59)","v":0}]
["Individual Applicant 2",{"$f":"AUTO_GrossIncome2","v":0},null,null,null,null,null,null,null,{"$f":"SUM(C10*Tables!$A$59,D10*Tables!$B$59,E10*Tables!$C$59,F10*Tables!$D$59,G10*Tables!$E$59,H10*Tables!$F$59,I10*Tables!$G$59)","v":0}]
["Individual Applicant 3",{"$f":"AUTO_GrossIncome3","v":0},null,null,null,null,null,null,null,{"$f":"SUM(C11*Tables!$A$59,D11*Tables!$B$59,E11*Tables!$C$59,F11*Tables!$D$59,G11*Tables!$E$59,H11*Tables!$F$59,I11*Tables!$G$59)","v":0}]
["Individual Applicant 4",{"$f":"AUTO_GrossIncome4","v":0},null,null,null,null,null,null,null,{"$f":"SUM(C12*Tables!$A$59,D12*Tables!$B$59,E12*Tables!$C$59,F12*Tables!$D$59,G12*Tables!$E$59,H12*Tables!$F$59,I12*Tables!$G$59)","v":0}]
["Totals for Individuals",{"$f":"Calculator!C34","v":0},{"$f":"SUM(C9:C12)","v":0},0,0,0,0,0,0,0]
[]
["Boarder Income p.w",null,"*must be existing and evidenced continuously over last 90 days",null,null,null,null,"*max age 13 must be through IRD"]
[]
["Taxable Rental Income",null,null,null,null,null,null,"Applicant Ownership%/Tax Split % =100%",null,"Taxable Rental Income Split Per Applicant"]
["Address","Gross  Rental Income pa","Rates pa","Insurances pa","Body Corp pa","Regular Maintenance pa","Lending against security that is tax deductable",1,2,"TaxaTaxable profit/ loss"]
[null,null,null,null,null,null,null,null,null,{"$f":"Tables!E75-Tables!I75","v":0},{"$f":"Tables!F75-Tables!J75","v":0}]
[null,null,null,null,null,null,null,null,null,{"$f":"Tables!E76-Tables!I76","v":0},{"$f":"Tables!F76-Tables!J76","v":0}]
[null,null,null,null,null,null,null,null,null,{"$f":"Tables!E77-Tables!I77","v":0},{"$f":"Tables!F77-Tables!J77","v":0}]
[null,null,null,null,null,null,null,null,null,{"$f":"Tables!E78-Tables!I78","v":0},{"$f":"Tables!F78-Tables!J78","v":0}]
[null,null,null,null,null,null,null,null,null,{"$f":"Tables!E79-Tables!I79","v":0},{"$f":"Tables!F79-Tables!J79","v":0}]
[null,{"$f":"SUM(B19:B23)","v":0},0,0,0,0,{"$f":"SUM(G19:G23)","v":0},null,null,{"$f":"SUM(J19:J23)","v":0},{"$f":"SUM(K19:K23)","v":0},"*",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"*"]
[]
["If interest exempt max term","New Build","CCC issued","Max deductable term (yrs)",null,null,null,null,"* Automatically added to Calculator worksheet and scaled 80%"]
[null,null,null,{"$f":"IF(C27=\"\",\"\",IF(C27>Tables!F63,Tables!D75,\"not valid CCC outside range\"))","v":""},null,"*if no deductability is being claimed (G17-G21)max term 30 years"]
[]
["Self Employed"]
[]
["Most Recent Year"]
["Financial Year End dd/mm/yyyy"]
[]
[null,"Net Profit Before Tax","Shareholder Salary*","Director Fees*","Wages / Salary*","Home Office","One off / Significant Item","Interest**","Depreciation***","%  Depn Addback",null,"Total Income"]
["Individual Applicant 1",null,null,null,null,null,null,null,null,{"$f":"IF(I35<Tables!D98,I35,Tables!D98)","v":0},null,{"$f":"SUM(B35,C35,D35,E35,F35,G35,H35,J35)","v":0}]
["Individual Applicant 2",null,null,null,null,null,null,null,null,{"$f":"IF(I36<Tables!D99,I36,Tables!D99)","v":0},null,{"$f":"SUM(B36,C36,D36,E36,F36,G36,H36,J36)","v":0}]
["Individual Applicant 3",null,null,null,null,null,null,null,null,{"$f":"IF(I37<Tables!D100,I37,Tables!D100)","v":0},null,{"$f":"SUM(B37,C37,D37,E37,F37,G37,H37,J37)","v":0}]
["Individual Applicant 4",null,null,null,null,null,null,null,null,{"$f":"IF(I38<Tables!D101,I38,Tables!D101)","v":0},null,{"$f":"SUM(B38,C38,D38,E38,F38,G38,H38,J38)","v":0}]
[]
[null,null,null,null,"*paid to applicants",null,null,"**Where being refinanced or repaid","***not all asset depreciation is suitable for addback i.e core business assets -taxi drivers vehicle"]
[]
[]
["Year Prior"]
["Financial Year End dd/mm/yyyy"]
[]
[null,"Net Profit Before Tax","Shareholder Salary*","Director Fees*","Wages / Salary*","Home Office","One off / Significant Item","Interest**","Depreciation***","%  Depn Addback",null,"Total Income"]
["Individual Applicant 1",null,null,null,null,null,null,null,null,{"$f":"IF(I47<Tables!D104,I47,Tables!D104)","v":0},null,{"$f":"SUM(B47,C47,D47,E47,F47,G47,H47,J47)","v":0}]
["Individual Applicant 2",null,null,null,null,null,null,null,null,{"$f":"IF(I48<Tables!D105,I48,Tables!D105)","v":0},null,{"$f":"SUM(B48,C48,D48,E48,F48,G48,H48,J48)","v":0}]
["Individual Applicant 3",null,null,null,null,null,null,null,null,{"$f":"IF(I49<Tables!D106,I49,Tables!D106)","v":0},null,{"$f":"SUM(B49,C49,D49,E49,F49,G49,H49,J49)","v":0}]
["Individual Applicant 4",null,null,null,null,null,null,null,null,{"$f":"IF(I50<Tables!D107,I50,Tables!D107)","v":0},null,{"$f":"SUM(B50,C50,D50,E50,F50,G50,H50,J50)","v":0}]
[]
[]
["Income used in servicing for Self Employed Applicants"]
[]
["Individual Applicant 1",{"$f":"IF(Tables!F104<L35,Tables!F104,L35)","v":0},"*","* Auto added to calculator worksheet"]
["Individual Applicant 2",{"$f":"IF(Tables!F105<L36,Tables!F105,L36)","v":0},"*"]
["Individual Applicant 3",{"$f":"IF(Tables!E106<L37,Tables!E106,L37)","v":0},"*"]
["Individual Applicant 4",{"$f":"IF(Tables!E107<L38,Tables!E107,L38)","v":0},"*"]
[]
[]
["Age Check of Financials",{"$f":"IF(B32=\"\",\"\",IF(Tables!C83>270,\"Interims or more up-to-date financials required\",\"Age of financials acceptable\"))","v":""}]

{"sheet":"Tables","index":3,"n_rows":107,"n_cols":19,"rows_kept":107,"cut":false,"hidden":true,"merged":[[35,1,35,3],[19,5,19,7],[27,0,27,2],[95,2,95,3],[92,1,93,2],[61,0,61,1],[60,0,60,1],[66,5,66,6],[84,0,84,3],[72,0,72,1],[72,4,72,7],[56,0,56,7]]}
[]
["Living Expenses (Annual)",null,null,null,10,11,12,13,14,15,20,21,22,23,24,25]
[null,null,null,"Adults",1,1,1,1,1,1,2,2,2,2,2,2]
["Year","Pre-tax monthly income","annualised","Dependants",0,1,2,3,4,5,0,1,2,3,4,5]
[2022,1000,{"$f":"SUM(B5*12)","v":12000},null,566.64876388389905,834.27510134163299,1083.6456779765999,1320.70304967065,1548.57039524632,1769.1613084200001,998.69879752489203,1239.5847144423899,1470.3809372212099,1693.32727014631,1909.8879314947501,2121.09050102009]
[2022,2000,{"$f":"SUM(B6*12)","v":24000},null,709.57744557828801,1044.7085267812499,1356.9791043400801,1653.8306550418299,1939.1741328887599,2215.40580701208,1250.6056428899501,1552.2514321272699,1841.26255267753,2120.4437659810301,2391.6286174913798,2656.1038785969799]
[2022,3000,36000,null,809.36117199267699,1191.61977722857,1547.80313988587,1886.39918811285,2211.86885050107,2526.9453694957501,1426.4710006462401,1770.53547313634,2100.1885373721202,2418.6293719923801,2727.9493632249,3029.6162335931599]
[2022,4000,48000,null,888.55775105259204,1308.2206387009501,1699.2568023285,2070.9847200233198,2428.3018254825502,2774.2088109116999,1566.05222505958,1943.78365630703,2305.6935125258301,2655.2940143102701,2994.88119155091,3326.0663844871501]
[2022,5000,60000,null,955.28615048220001,1406.4646405318799,1826.86661328894,2226.5103405545901,2610.6610406877398,2982.5447501529702,1683.6587152146799,2089.75680444417,2478.8451590944601,2854.6997584839601,3219.7890584366501,3575.84540669537]
[2022,6000,72000,null,1013.51043111158,1492.1880563735999,1938.21334883083,2362.2151896484802,2769.7797099832801,3164.32957183696,1786.2769908688199,2217.1265842401899,2629.9297070145099,3028.6924828280598,3416.0338188272299,3793.7916486058298]
[2022,7000,84000,null,1065.4984373044899,1568.7298259838699,2037.6339808135699,2483.3849912989099,2911.8555291316802,3326.64382172253,1877.9040490745199,2330.8540675040199,2764.8319218296501,3184.0492297546798,3591.2592352381998,3988.3941486570802]
[2022,8000,96000,null,1112.6831635864501,1638.1997424641199,2127.8689340348301,2593.35966321264,3040.80466814896,3473.9615208105101,1961.06549299322,2434.0740322902502,2887.27024072308,3325.0522440381401,3750.29521134995,4165.0169193905804]
[2022,9000,108000,null,1156.0344758178601,1702.02573611362,2210.7729569999301,2694.39969704033,3159.2776323472799,3609.3107338633299,2037.47067757332,2528.9081295619799,2999.76134133307,3454.5997942611798,3896.4106770887802,4327.2903812623499]
[2022,10000,120000,null,1196.2428044656699,1761.2243254258201,2287.6664125846801,2788.11429707464,3269.1612698929698,3734.8470869868602,2108.3364625718,2616.8667253656199,3104.0968022560501,3574.7551069096398,4031.9327262390698,4477.7989668140099]
[2022,12500,150000,null,1286.0775592428499,1893.4877378297299,2459.4642703601799,2997.4945025257798,3514.6668645026598,4015.3244875089299,2266.66710280293,2813.3866790741999,3337.2064803200101,3843.21001189957,4334.7204933944004,4814.0701407124798]
[2022,15000,180000,null,1364.46343417959,2008.8950023022901,2609.3675614348999,3180.1905051968001,3728.88430053927,4260.0567906630304,2404.8194893107402,2984.86138896995,3540.6077821500899,4077.4520116789299,4598.9198459443296,5107.4856484123202]
[2022,20000,240000,null,1497.9771731366,2205.4668386818398,2864.6960742504698,3491.3744580300199,4093.7583401300999,4676.9064445583699,2640.1328245688801,3276.93224578218,3887.0588275453601,4476.4336552026798,5048.9274961418696,5607.2568320928804]
[2022,25000,300000,null,1610.47140215782,2371.0917200876802,3079.8273740006198,3753.5676909603199,4401.1890517128704,5028.1300774147703,2838.4000024268798,3523.0214206740602,4178.9669379001198,4812.6022977142702,5428.0889521694298,6028.34738377965]
[2022,1000000,{"$f":"SUM(B19*12)","v":12000000},null,{"$f":"E18*1.05","v":1690.9949722657111},{"$f":"F18*1.05","v":2489.6463060920642},3233.818742700651,3941.2460755083362,4621.2485042985145,5279.5365812855089,2980.3200025482238,3699.1724917077636,4387.9152847951264,5053.2324125999839,5699.4933997779017,6329.7647529686328]
[]
["Adults",{"$f":"AUTO_NumberAdults","v":0},{"$f":"IF(B21=1,\"10\",\"20\")","v":"20"},"Gross income",{"$f":"SUM('Self Employed and Other Incomes'!B13+'Self Employed and Other Incomes'!C13+'Self Employed and Other Incomes'!D13+'Self Employed and Other Incomes'!E13+'Self Employed and Other Incomes'!G13+'Self Employed and Other Incomes'!H13+'Self Employed and Other Incomes'!I13)/12","v":0}]
["Dependants",{"$f":"AUTO_NumberChildren","v":0},{"$f":"B22","v":0},"Roundup 000's",{"$f":"ROUNDUP(E21,-3)","v":0},null,null,null,null,null,null,null,"Adults ",null,1,2]
[null,null,{"$f":"SUM(C21+C22)","v":20},null,null,null,null,null,null,null,null,null,"Dependants",0,1,2,3,4,5]
["Income banded expense",{"$f":"_xlfn.XLOOKUP(C23,E2:P2,_xlfn.XLOOKUP(E22,B5:B19,E5:P19,,1))","v":998.69879752489203}]
["annualised",{"$f":"SUM(B24*12)","v":11984.385570298704}]
[]
[]
["Benchmark Servicing Interest Rates ",null,null,null,"KS %",null,null,"DTI"]
[null,"Floor","Loading",null,null,null,"Gross Income",{"$f":"Calculator!C34+Calculator!F34+'Self Employed and Other Incomes'!C13+'Self Employed and Other Incomes'!D13+'Self Employed and Other Incomes'!E13+'Self Employed and Other Incomes'!F13+'Self Employed and Other Incomes'!G13+'Self Employed and Other Incomes'!H13+'Self Employed and Other Incomes'!I13+'Self Employed and Other Incomes'!B15+'Self Employed and Other Incomes'!B24","v":0}]
["Prime",0.09,null,null,0.03,null,"Total Debts",{"$f":"AUTO_ExistingOwnerOccupiedLoans+AUTO_ExistingInvestmentLoans+Calculator!E43+Calculator!E44+AUTO_CreditCardLimit+Calculator!E46","v":0}]
["Specialist",0,1.7500000000000002E-2,null,0.04,null,"DTI",{"$f":"IF(ISERROR(H30/H29),0,(H30/H29))","v":0}]
["Prime Alt Doc",9.5000000000000001E-2,null,null,0.06]
["Prime >80% LVR",0.1,null,null,0.08]
[null,null,null,null,0.1]
[]
[null,"Tax Rates effective  01/04/2021"]
[null,"Tax Boundary","Tax Due","Excess %"]
[null,0,0,0.105]
[null,14000,{"$f":"C38+D38*(B39-B38)","v":1470},0.17499999999999999]
[null,48000,{"$f":"C39+D39*(B40-B39)","v":7420},0.3]
[null,70000,{"$f":"C40+D40*(B41-B40)","v":14020},0.33]
[null,180000,{"$f":"C41+D41*(B42-B41)","v":50320},0.39]
[]
[]
[null,null,"Applicant 1","Applicant 2","Applicant 3","Applicant 4"]
[null,"ANNUAL INCOME:",{"$f":"Calculator!$I30","v":0},{"$f":"Calculator!$I31","v":0},{"$f":"Calculator!$I32","v":{"$error":"#REF!"}},{"$f":"Calculator!$I33","v":{"$error":"#REF!"}}]
[]
[null,"Tax Boundary",{"$f":"VLOOKUP(C46,$B$38:$D$43,1)","v":0},{"$f":"VLOOKUP(D46,$B$38:$D$42,1)","v":0},{"$f":"VLOOKUP(E46,Calculator!$B$39:$D$43,1)","v":{"$error":"#REF!"}},{"$f":"VLOOKUP(F46,$B$38:$D$42,1)","v":{"$error":"#REF!"}}]
[null,"Tax Due on Boundary",{"$f":"VLOOKUP(C46,$B$38:$D$43,2)","v":0},{"$f":"VLOOKUP(D46,$B$38:$D$42,2)","v":0},{"$f":"VLOOKUP(E46,$B$38:$D$42,2)","v":{"$error":"#REF!"}},{"$f":"VLOOKUP(F46,$B$38:$D$42,2)","v":{"$error":"#REF!"}}]
[null,"% Due on Excess",{"$f":"VLOOKUP(C46,$B$38:$D$43,3)","v":0.105},{"$f":"VLOOKUP(D46,$B$38:$D$42,3)","v":0.105},{"$f":"VLOOKUP(E46,$B$38:$D$42,3)","v":{"$error":"#REF!"}},{"$f":"VLOOKUP(F46,$B$38:$D$42,3)","v":{"$error":"#REF!"}}]
[]
[null,"Tax due on Income:",{"$f":"IF(C46>0,C49+(C46-C48)*C50,0)","v":0},{"$f":"IF(D46>0,D49+(D46-D48)*D50,0)","v":0},{"$f":"IF(E46>0,E49+(E46-E48)*E50,0)","v":{"$error":"#REF!"}},{"$f":"IF(F46>0,F49+(F46-F48)*F50,0)","v":{"$error":"#REF!"}}]
[null,"KS**",{"$f":"SUM(C46*Calculator!D30)","v":0},{"$f":"SUM(D46*Calculator!D31)","v":0},{"$f":"SUM(E46*Calculator!D32)","v":{"$error":"#REF!"}},{"$f":"SUM(F46*Calculator!D33)","v":{"$error":"#REF!"}},"** % as selected caluclator page"]
[null,"ACC*",{"$f":"+IF(C46>139384,2132.57,C46*0.0153)","v":0},{"$f":"+IF(D46>139384,2132.57,D46*0.0153)","v":0},{"$error":"#REF!"},{"$f":"+IF(F46>130911,1819.66,F46*0.0139)","v":{"$error":"#REF!"}},"*ACC tops out at 2132.57 for income over 139384"]
[null,"NET INCOME (per annum):",{"$f":"C46-C52-C53-C54","v":0},{"$f":"D46-D52-D53-D54","v":0},{"$f":"E46-E52-E53-E54","v":{"$error":"#REF!"}},{"$f":"F46-F52-F53-F54","v":{"$error":"#REF!"}}]
[]
["% Scaling applied to income types"]
["Overtime / Shift Allowance","Bonus","Commission","Investment Income","Casual / Second Job","Child Support/ Maintenance","Working For Families, Company Car Addback","Rental Incomes"]
[1,1,1,1,1,1,1,1]
[]
["Other mortgage lending default term calculation"]
[{"$f":"MAX(Calculator!B20:B25)","v":30},null,null,null,null,"New Build date (counted)"]
[null,null,null,null,null,"2020-03-27"]
[]
[]
[null,"calculated benchmark","Advised","max term remain amort tax deduct"]
["O/O",{"$f":"-PMT((Calculator!C18/12),Tables!B71*12,Calculator!E42,0)","v":0},{"$f":"AUTO_OtherMortgages","v":0},null,null,"Interest Exempt"]
["Investment",{"$f":"-PMT((Calculator!C18/12),Tables!B71*12,Calculator!E43,0)","v":0},{"$f":"AUTO_OtherInvestmentMortgages","v":0},{"$f":"Tables!C92","v":0},null,"yes","no"]
["Credit Card",{"$f":"SUM(Calculator!E45*0.038)","v":0},{"$f":"AUTO_OtherExpenses","v":0}]
[]
["Max term Other debts",{"$f":"MAX(Calculator!B20:B25)","v":30}]
[]
["Rent Split and scale",null,null,null,"Applicants"]
["Gross","scaled","CCC + 20y","term remaining","split1","split2",null,null,"Tax deduct 1","Tax deduct 2"]
[{"$f":"SUM(-'Self Employed and Other Incomes'!C19:F19)+('Self Employed and Other Incomes'!B19)","v":0},{"$f":"SUM(A75*$H$59)","v":0},{"$f":"EDATE('Self Employed and Other Incomes'!C27,240)","v":"1919-12-31"},{"$f":"SUM(C75-$B$83)/365","v":-103.65753424657534},{"$f":"SUM($B$75*'Self Employed and Other Incomes'!H19)","v":0},{"$f":"SUM($B$75*'Self Employed and Other Incomes'!I19)","v":0},null,null,{"$f":"-IPMT(AUTO_LoanInterestRate,1,20,'Self Employed and Other Incomes'!G19)*'Self Employed and Other Incomes'!H19","v":0},{"$f":"-IPMT(AUTO_LoanInterestRate,1,20,'Self Employed and Other Incomes'!G19)*'Self Employed and Other Incomes'!I19","v":0}]
[{"$f":"SUM(-'Self Employed and Other Incomes'!C20:F20)+('Self Employed and Other Incomes'!B20)","v":0},{"$f":"SUM(A76*$H$59)","v":0},{"$f":"EDATE('Self Employed and Other Incomes'!#REF!,240)","v":{"$error":"#REF!"}},{"$f":"SUM(C76-$B$83)/365","v":{"$error":"#REF!"}},{"$f":"SUM($B$76*'Self Employed and Other Incomes'!H20)","v":0},{"$f":"SUM($B$76*'Self Employed and Other Incomes'!I20)","v":0},null,null,{"$f":"-IPMT(AUTO_LoanInterestRate,1,20,'Self Employed and Other Incomes'!G20)*'Self Employed and Other Incomes'!H20","v":0},{"$f":"-IPMT(AUTO_LoanInterestRate,1,20,'Self Employed and Other Incomes'!G20)*'Self Employed and Other Incomes'!I20","v":0}]
[{"$f":"SUM(-'Self Employed and Other Incomes'!C21:F21)+('Self Employed and Other Incomes'!B21)","v":0},0,{"$f":"EDATE('Self Employed and Other Incomes'!#REF!,240)","v":{"$error":"#REF!"}},{"$f":"SUM(C77-$B$83)/365","v":{"$error":"#REF!"}},{"$f":"SUM($B$77*'Self Employed and Other Incomes'!H21)","v":0},{"$f":"SUM($B$77*'Self Employed and Other Incomes'!I21)","v":0},null,null,{"$f":"-IPMT(AUTO_LoanInterestRate,1,20,'Self Employed and Other Incomes'!G21)*'Self Employed and Other Incomes'!H21","v":0},{"$f":"-IPMT(AUTO_LoanInterestRate,1,20,'Self Employed and Other Incomes'!G21)*'Self Employed and Other Incomes'!I21","v":0}]
[{"$f":"SUM(-'Self Employed and Other Incomes'!C22:F22)+('Self Employed and Other Incomes'!B22)","v":0},0,{"$f":"EDATE('Self Employed and Other Incomes'!#REF!,240)","v":{"$error":"#REF!"}},{"$f":"SUM(C78-$B$83)/365","v":{"$error":"#REF!"}},{"$f":"SUM($B$78*'Self Employed and Other Incomes'!H22)","v":0},{"$f":"SUM($B$78*'Self Employed and Other Incomes'!I22)","v":0},null,null,{"$f":"-IPMT(AUTO_LoanInterestRate,1,20,'Self Employed and Other Incomes'!G22)*'Self Employed and Other Incomes'!H22","v":0},{"$f":"-IPMT(AUTO_LoanInterestRate,1,20,'Self Employed and Other Incomes'!G22)*'Self Employed and Other Incomes'!I22","v":0}]
[{"$f":"SUM(-'Self Employed and Other Incomes'!C23:F23)+('Self Employed and Other Incomes'!B23)","v":0},0,{"$f":"EDATE('Self Employed and Other Incomes'!#REF!,240)","v":{"$error":"#REF!"}},{"$f":"SUM(C79-$B$83)/365","v":{"$error":"#REF!"}},{"$f":"SUM($B$79*'Self Employed and Other Incomes'!H23)","v":0},{"$f":"SUM($B$79*'Self Employed and Other Incomes'!I23)","v":0},null,null,{"$f":"-IPMT(AUTO_LoanInterestRate,1,20,'Self Employed and Other Incomes'!G23)*'Self Employed and Other Incomes'!H23","v":0},{"$f":"-IPMT(AUTO_LoanInterestRate,1,20,'Self Employed and Other Incomes'!G23)*'Self Employed and Other Incomes'!I23","v":0}]
[null,null,null,null,null,null,null,null,{"$f":"SUM(I75:I79)","v":0},{"$f":"SUM(J75:J79)","v":0}]
[]
[null,"Today","Financials <=> 270 days"]
["Date",{"$f":"TODAY()","v":"2023-08-02"},{"$f":"DATEDIF('Self Employed and Other Incomes'!B32,B83,\"d\")","v":45140}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,"Self Employed Assessment"]
[null,"Current Year","Total income less Depreciation","Max Depreciation that can be used "]
[null,"Applicant1",{"$f":"SUM('Self Employed and Other Incomes'!B35:H35)","v":0},{"$f":"MIN(C98*20%,'Self Employed and Other Incomes'!I35)","v":0}]
[null,"Applicant 2",{"$f":"SUM('Self Employed and Other Incomes'!B36:H36)","v":0},{"$f":"MIN(C99*20%,'Self Employed and Other Incomes'!I36)","v":0}]
[null,"Applicant 3",{"$f":"SUM('Self Employed and Other Incomes'!B37:H37)","v":0},{"$f":"SUM(C100*20%)","v":0}]
[null,"Applicant 4",{"$f":"SUM('Self Employed and Other Incomes'!B38:H38)","v":0},0]
[]
[null,"Prior Year",null,null,null,"120% Rule"]
[null,"Applicant1",{"$f":"SUM('Self Employed and Other Incomes'!B47:H47)","v":0},{"$f":"MIN(C104*20%,'Self Employed and Other Incomes'!I47)","v":0},{"$f":"'Self Employed and Other Incomes'!L47","v":0},{"$f":"E104*120%","v":0}]
[null,"Applicant 2",{"$f":"SUM('Self Employed and Other Incomes'!B48:H48)","v":0},{"$f":"MIN(C105*20%,'Self Employed and Other Incomes'!I48)","v":0},{"$f":"'Self Employed and Other Incomes'!L48","v":0},{"$f":"E105*120%","v":0}]
[null,"Applicant 3",{"$f":"SUM('Self Employed and Other Incomes'!B49:H49)","v":0},0,{"$f":"'Self Employed and Other Incomes'!L49","v":0},0]
[null,"Applicant 4",{"$f":"SUM('Self Employed and Other Incomes'!B50:H50)","v":0},0,{"$f":"'Self Employed and Other Incomes'!L50","v":0},0]