{"text_format":"1","workbook":"xlsx","n_sheets":4,"sheets":["Calculator","Non Discretion Living 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":96,"n_cols":21,"rows_kept":96,"cut":false,"hidden":false,"merged":[[62,1,65,2],[68,0,75,10],[14,4,14,5],[13,4,13,5],[49,7,49,10],[43,0,43,1],[28,3,28,4],[19,7,25,7],[39,8,42,9],[36,0,37,0],[63,5,65,7],[44,2,44,3],[40,0,40,1],[41,0,41,1],[11,2,11,5],[18,0,18,1],[4,8,4,9],[10,8,10,9],[18,3,18,4]]}
[]
["DSR / NSR Calculator",null,null,null,null,null,null,null,null,"Created:",{"$f":"NOW()","v":"2024-04-24T11:04:48.500"}]
["Version 11.00  24/04/24"]
[]
["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"]
["Loan Term (yrs)",null,"Loan Amount","IO Term (yrs)",null,"Estimated Actual Repayment*","Assessed Loan Repayment"]
["Portion A",30,null,null,null,{"$f":"-PMT(($C$18/12),((B20-D20)*12),C20,0)","v":0},{"$f":"IF(-PMT(($G$18/12),((B20-D20)*12),C20,0),(-PMT(($G$18/12),((B20-D20)*12),C20,0)),\"$\")","v":"$"},"*If Interest Only, estimated repayment is after Interest Only period has expired (P&I remaining term)"]
["Portion B",30,null,null,null,0,"$"]
["Portion C",30,null,null,null,0,"$"]
["Portion D",30,null,null,null,0,"$"]
["Portion E",30,0,null,null,0,"$"]
["Portion F",30,0,null,null,0,"$"]
["Total Loan",null,{"$f":"SUM(C20:C23)","v":0},null,null,{"$f":"SUM(F20:F25)","v":0},{"$f":"SUM(G20:G25)","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+N30","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},null,null,{"$f":"IF(H30<1,0,H30)","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+N31","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},null,null,{"$f":"IF(H31<1,0,H31)","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}]
["Monthly Living Expenses",null,null,null,"Annualised",null,"Annualised with Buffer",{"$f":"IF(H31<0,0,H31)","v":0}]
["Applicant Advised Non Discretionary Living Expenses",null,{"$f":"SUM('Non Discretion Living Expenses'!D27)","v":0},null,{"$f":"SUM(AUTO_TotalDeclaredLivingExpenses*12)","v":0},null,{"$f":"'Non Discretion Living Expenses'!L27*12","v":0}]
[]
["Monthly Financial Expenses",null,null,null,"Limit / Balance","Estimated Repayment Amount pm","Assessed Repayment pm"]
["Proposed Loan",null,null,null,{"$f":"C26","v":0},{"$f":"F26","v":0},{"$f":"G26","v":0},null,"*For all continuing financial expenses documents to evidence limit/outstanding amount, payments and frequency are required with application"]
["Other Mortgages - Owner Occupied",null,null,null,null,null,{"$f":"MAX(Tables!B67:C67)","v":0},"*"]
["Other Mortgages - Investment",null,null,null,null,null,{"$f":"MAX(Tables!B68:C68)","v":0},"*"]
["Personal Loans",null,null,null,null,null,{"$f":"AUTO_PersonalLoans","v":0},"*"]
["Credit Cards Limit",null,null,null,null,null,{"$f":"MAX(F44,Tables!B69)","v":0},"*"]
["Student Loan, Other Debts",null,null,null,null,null,{"$f":"F45","v":0},"*"]
[]
["3.   NSR CALCULATION"]
[null,null,"Annual"]
["Individual Gross Income"]
["Taxable Income",null,{"$f":"C34+F34+(G34-'Self Employed and Other Incomes'!I13)*Tables!H59","v":0},null,null,null,null,"Frequency Converter"]
["Net Rental Income",null,{"$f":"'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}]
["Tax Applicable",null,{"$f":"J34","v":0}]
["Net Cash after Tax*",null,{"$f":"C50+C51-C52","v":0},null,null,null,null,"Amount",null,"Monthly","Annually"]
["Government Benefits / Tax Free",null,{"$f":"'Self Employed and Other Incomes'!I13","v":0},null,null,null,null,null,"Weekly",{"$f":"H54*52/12","v":0},{"$f":"H54*52","v":0}]
["Boarder Income ",null,{"$f":"IF('Self Employed and Other Incomes'!B15>100,5200,'Self Employed and Other Incomes'!B15*52)","v":0},null,null,null,null,null,"Fortnightly",{"$f":"H55*26/12","v":0},{"$f":"H55*26","v":0}]
["Living Expenses (as per scales)",null,{"$f":"IF(AUTO_NumberAdults=\"\",\"                                             0\",IF('Non Discretion Living Expenses'!D27=0,\"                                              0\",MAX('Non Discretion Living Expenses'!L34,'Non Discretion Living Expenses'!P34)))","v":"                                             0"},{"$f":"SUM(C53+C54+C55-C56)","v":0},null,null,null,null,"4 weekly",{"$f":"H56*13/12","v":0},{"$f":"H56*13","v":0}]
["Total Financial Expense",null,{"$f":"(G40+G41+G42+G43+G44+G45)*12","v":0},null,null,null,null,null,"Monthly",null,{"$f":"H57*12","v":0}]
[null,null,null,null,null,null,null,null,"Quarterly",{"$f":"H58*4/12","v":0},{"$f":"H58*4","v":0}]
["Surplus Cash after Financial Expense",null,{"$f":"SUM(C53+C54+C55-C56-C57)","v":0},null,null,null,null,null,"Annually",{"$f":"H59/12","v":0}]
[null,null,null,null,"Minimum: Surplus (+)",null,null,null,null,null,null,null,null,null,{"$f":"Tables!J75=-IPMT(C18,1,20,'Self Employed and Other Incomes'!G24)","v":true}]
["Net Service Ratio (NSR)",null,{"$f":"IF(ISERROR(D56/C57),0,(D56/C57))","v":0}]
[null,null,null,null,"Minimum NSR = 1.00 "]
[null,{"$f":"IF(C14=\"\",\"Incomplete No Adult Applicants (C14)\",IF(C16=\"\",\"Incomplete Product Required (C16)\",IF(AUTO_LoanInterestRate=\"\",\"Incomplete Actual Rate Required (C18)\",IF(AUTO_LoanAmount=\"\",\"Incomplete Loan amount Required (C20-C23)\",IF('Non Discretion Living Expenses'!D9=\"\",\"Incomplete Living Expenses (2nd Tab)\",IF(K34=0,\"Incomplete no incomes entered\",IF(C61>1.001,\"Pass\",\"Fail\")))))))","v":"Incomplete No Adult Applicants (C14)"}]
[null,null,null,null,null,"NOTE: This servicing calculator was prepared as a guide for loan assessment. If NSR results are marginal, other mitigating factors may be considered.  Please provide a detailed explanation in the \"Comments\" section.",null,null,null,"DTI"]
[null,null,null,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 Discretion Living 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]]}
["NON DISCRETIONARY LIVING EXPENSES"]
[null,null,null,null,null,null,null,"******Hidden Cells***** for Underwriter use*****"]
[null,"MONTHLY 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,{"$f":"IF(D6=\"\",\"input required\",IF(D6<100,\"Low input\",\"\"))","v":"input required"},null,null,{"$f":"D6","v":0},null,null,null,{"$f":"MAX(D6:J6)*1.05","v":0}]
[".","Rates",null,null,{"$f":"IF(D7=\"\",\"input required\",IF(D7<120,\"Low input\",\"\"))","v":"input required"},"As per Council websites",null,0,null,null,null,{"$f":"MAX(D7:J7)*1.05","v":0},null,null,null,null,"Rates"]
[null,"Property Maintenance ",null,null,{"$f":"IF(D8=\"\",\"input required\",IF(D8<50,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0]
[null,"Food & Takeaways",null,null,{"$f":"IF(D9=\"\",\"input required\",IF(D9<500,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0]
[null,"Transport",null,null,{"$f":"IF(D10=\"\",\"input required\",IF(D10<100,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0]
[null,"Clothing and Footwear",null,null,{"$f":"IF(D11=\"\",\"input required\",IF(D11<50,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0]
[null,"Personal Care",null,null,{"$f":"IF(D12=\"\",\"input required\",IF(D12<10,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0]
[null,"Childcare",null,null,{"$f":"IF(D13=\"\",\"input required\",IF(D13<100,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0]
[".","Insurances ",null,null,{"$f":"IF(D14=\"\",\"input required\",IF(D14<150,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0]
[null,"Communications",null,null,{"$f":"IF(D15=\"\",\"input required\",IF(D15<50,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0]
[null,"Medical and Health",null,null,{"$f":"IF(D16=\"\",\"input required\",IF(D16<50,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0]
[null,"Education and Schooling",null,null,{"$f":"IF(D17=\"\",\"input required\",IF(D17<20,\"Low input\",\"\"))","v":"input required"},"Private Schooling or payments required above donations please indicate",null,0,null,null,null,0]
[null,"Recreation and Entertainment",null,null,{"$f":"IF(D18=\"\",\"input required\",IF(D18<100,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0]
[null,"Regular Savings ",null,null,{"$f":"IF(D19=\"\",\"input required\",IF(D19<500,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0]
[".","Body Corp Fees",null,null,"input required","Supporting document:",null,0,null,null,null,0,null,null,null,null,"BC fees"]
[null,"Tithings",null,null,{"$f":"IF(D21=\"\",\"input required\",IF(D21<5,\"Low input\",\"\"))","v":"input required"},null,null,0,null,null,null,0,null,null,null,null,"Tithing"]
[".","Child Maintenance",null,null,{"$f":"IF(D22=\"\",\"input required\",IF(D22<250,\"Low input\",\"\"))","v":"input required"},"Supporting document:",null,0,null,null,null,0,null,null,null,null,"Child Maint"]
[".","All Other Regular Expenses",null,null,{"$f":"IF(D23=\"\",\"input required\",IF(D23<20,\"Low input\",\"\"))","v":"input required"},"Supporting document:",null,0,null,null,null,0,null,null,null,null,"Other"]
[]
[".","Rent or Board Paid",null,null,{"$f":"IF(D25=\"\",\"input required\",IF(D25<650,\"low\",\"\"))","v":"input required"},"Supporting document:",null,{"$f":"D25","v":0},null,null,null,{"$f":"MAX(D25:J25)*1.05","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+J14+J15+J16+J17+J18+J21)*12","v":0},null,{"$f":"SUM(L6+L8+L9+L10+L11+L12+L13+L15+L16+L18)*12","v":0},null,{"$f":"Tables!B25*0.95","v":11954.424606372957},null,{"$f":"Tables!B25","v":12583.60484881364}]
[null,"Non Benchmark Total",null,null,null,null,"Not Benchmarked expense type",null,null,{"$f":"SUM(J7+J19+J20+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,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":12583.60484881364}]
[]
[]
[]
[]
[]
[]
[]
[]
[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,0.5,0.5,{"$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},{"$f":"SUM(C19:C23)","v":0},{"$f":"SUM(D19:D23)","v":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!L78,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!C88,I35,Tables!C88)","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!C89,I36,Tables!C89)","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!C90,I37,Tables!C90)","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!C91,I38,Tables!C91)","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!C94,I47,Tables!C94)","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!C95,I48,Tables!C95)","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!C96,I49,Tables!C96)","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!C97,I50,Tables!C97)","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!E94<L35,Tables!E94,L35)","v":0},"*","* Auto added to calculator worksheet"]
["Individual Applicant 2",{"$f":"IF(Tables!E95<L36,Tables!E95,L36)","v":0},"*"]
["Individual Applicant 3",{"$f":"IF(Tables!D96<L37,Tables!D96,L37)","v":0},"*"]
["Individual Applicant 4",{"$f":"IF(Tables!D97<L38,Tables!D97,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":97,"n_cols":19,"rows_kept":97,"cut":false,"hidden":true,"merged":[[85,0,85,2],[35,1,35,3],[19,5,19,7],[27,0,27,2],[61,0,61,1],[60,0,60,1],[72,11,72,12],[72,0,72,1],[72,4,72,7],[56,0,56,7],[72,8,72,9]]}
[]
["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,594.98120207809404,875.9888564087147,1137.82796187543,1386.7382021541825,1625.998915008636,1857.6193738410002,1048.6337374011366,1301.5639501645094,1543.8999840822705,1777.9936336536255,2005.3823280694876,2227.1450260710944]
[2022,2000,{"$f":"SUM(B6*12)","v":24000},null,745.05631785720243,1096.9439531203125,1424.8280595570841,1736.5221877939214,2036.132839533198,2326.1760973626842,1313.1359250344476,1629.8640037336336,1933.3256803114066,2226.4659542800819,2511.2100483659488,2788.9090725268293]
[2022,3000,36000,null,849.82923059231086,1251.2007660899985,1625.1932968801636,1980.7191475184925,2322.4622930261235,2653.2926379705377,1497.7945506785522,1859.0622467931571,2205.1979642407264,2539.5608405919993,2864.3468313861454,3181.0970452728179]
[2022,4000,48000,null,932.98563860522165,1373.6316706359976,1784.2196424449251,2174.5339560244861,2549.7169167566776,2912.9192514572851,1644.354836312559,2040.9728391223816,2420.9781881521217,2788.0587150257838,3144.6252511284556,3492.3697037115076]
[2022,5000,60000,null,1003.05045800631,1476.787872558474,1918.2099439533872,2337.8358575823199,2741.1940927221272,3131.6719876606189,1767.841650975414,2194.2446446663785,2602.7874170491832,2997.4347464081584,3380.7785113584828,3754.6376770301385]
[2022,6000,72000,null,1064.185952667159,1566.79745919228,2035.1240162723716,2480.3259491309045,2908.2686954824444,3322.546050428808,1875.5908404122611,2327.9829134521997,2761.4261923652357,3180.1271069694631,3586.8355097685917,3983.4812310361212]
[2022,7000,84000,null,1118.7733591697145,1647.1663172830636,2139.5156798542484,2607.5542408638553,3057.4483055882642,3492.9760128086568,1971.799251528246,2447.3967708792211,2903.0735179211329,3343.2516912424139,3770.82219700011,4187.8138560899342]
[2022,8000,96000,null,1168.3173217657727,1720.109729587326,2234.2623807365717,2723.0276463732721,3192.8449015564083,3647.659596851036,2059.1187676428813,2555.777733904763,3031.6337527592341,3491.3048562400472,3937.8099719174479,4373.2677653601095]
[2022,9000,108000,null,1213.8361996087531,1787.1270229193012,2321.3116048499269,2829.1196818923468,3317.2415139646441,3789.7762705564965,2139.3442114519862,2655.3535360400788,3149.7494083997235,3627.329783974239,4091.2312109432191,4543.6549003254677]
[2022,10000,120000,null,1256.0549446889534,1849.2855416971113,2402.0497332139144,2927.5200119283722,3432.6193333876186,3921.5894413362034,2213.7532857003903,2747.7100616339012,3259.3016423688528,3753.492862255122,4233.529362551023,4701.6889151547102]
[2022,12500,150000,null,1350.3814372049924,1988.1621247212165,2582.4374838781891,3147.3692276520687,3690.4002077277928,4216.090711884377,2380.0004579430765,2954.05601302791,3504.0668043360106,4035.3705124945486,4551.4565180641202,5054.7736477481039]
[2022,15000,180000,null,1432.6866058885696,2109.3397524174047,2739.835939506645,3339.2000304566404,3915.3285155662338,4473.0596301961823,2525.0604637762772,3134.1044584184474,3717.6381712575944,4281.3246122628761,4828.865838241546,5362.8599308329367]
[2022,20000,240000,null,1572.87603179343,2315.7401806159319,3007.9308779629932,3665.9431809315211,4298.4462571366048,4910.7517667862885,2772.1394657973242,3440.7788580712891,4081.4117689226282,4700.2553379628143,5301.3738709489635,5887.6196736975244]
[2022,25000,300000,null,1690.9949722657111,2489.6463060920642,3233.818742700651,3941.2460755083362,4621.2485042985145,5279.5365812855089,2980.3200025482238,3699.1724917077636,4387.9152847951264,5053.2324125999839,5699.4933997779017,6329.7647529686328]
[2022,1000000,{"$f":"SUM(B19*12)","v":12000000},null,{"$f":"SUM(E18*1.1)","v":1860.0944694922823},{"$f":"SUM(F18*1.1)","v":2738.6109367012709},3557.2006169707165,4335.37068305917,5083.3733547283664,5807.4902394140599,3278.3520028030466,4069.0897408785404,4826.706813274639,5558.5556538599831,6269.4427397556919,6962.7412282654968]
[]
["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":1048.6337374011366}]
["annualised",{"$f":"SUM(B24*12)","v":12583.60484881364}]
[]
[]
["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 Debt",{"$f":"AUTO_ExistingOwnerOccupiedLoans+AUTO_ExistingInvestmentLoans+Calculator!E42+Calculator!E43+AUTO_CreditCardLimit+Calculator!E45","v":0}]
["Specialist",0,1.7500000000000002E-2,null,0.04,null,null,{"$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,$B$38:$D$42,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},{"$f":"+IF(E46>130911,1819.66,E46*0.0139)","v":{"$error":"#REF!"}},{"$f":"+IF(F46>130911,1819.66,F46*0.0139)","v":{"$error":"#REF!"}},"*ACC tops out at 2132.57 for income over 139,384"]
[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 Income"]
[0.8,0.8,0.8,0.8,0.8,0.8,1,0.9]
[]
["Other mortgage lending default term calculation"]
[{"$f":"MAX(Calculator!B20:B25)","v":30}]
[]
[]
[]
[null,"calculated benchmark","Advised"]
["O/O",{"$f":"-PMT((Calculator!G18/12),Tables!B71*12,Calculator!E41,0)","v":0},{"$f":"AUTO_OtherMortgages","v":0}]
["Investment",{"$f":"-PMT((Calculator!G18/12),Tables!B71*12,Calculator!E42,0)","v":0},{"$f":"AUTO_OtherInvestmentMortgages","v":0}]
["Credit Card",{"$f":"SUM(Calculator!E44*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",null,null,null,"Tax Deduct Loan calc",null,null,"Interest Exempt"]
["Net","scaled","CCC + 20y","term remaining","split1","split2",null,null,"Tax deduct 1","Tax deduct 2",null,"yes","no"]
[{"$f":"SUM(-'Self Employed and Other Incomes'!C19:F19)+('Self Employed and Other Incomes'!B19)","v":0},{"$f":"SUM(A75*0.9)","v":0},{"$f":"EDATE('Self Employed and Other Incomes'!C27,240)","v":"1919-12-31"},{"$f":"SUM(C75-$B$83)/365","v":-104.38630136986302},{"$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*0.9)","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},{"$f":"SUM(A77*0.9)","v":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},null,"New Build date (counted)"]
[{"$f":"SUM(-'Self Employed and Other Incomes'!C22:F22)+('Self Employed and Other Incomes'!B22)","v":0},{"$f":"SUM(A78*0.9)","v":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},null,"2020-03-27"]
[{"$f":"SUM(-'Self Employed and Other Incomes'!C23:F23)+('Self Employed and Other Incomes'!B23)","v":0},{"$f":"SUM(A79*0.9)","v":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":"2024-04-24"},{"$f":"DATEDIF('Self Employed and Other Incomes'!B32,B83,\"d\")","v":45406}]
[]
[]
["Self Employed Assessment"]
["Current Year","Total income less Depreciation","Max Depreciation that can be used "]
["Applicant1",{"$f":"SUM('Self Employed and Other Incomes'!B35:H35)","v":0},{"$f":"MIN(B88*20%,'Self Employed and Other Incomes'!I35)","v":0}]
["Applicant 2",{"$f":"SUM('Self Employed and Other Incomes'!B36:H36)","v":0},{"$f":"MIN(B89*20%,'Self Employed and Other Incomes'!I36)","v":0}]
["Applicant 3",{"$f":"SUM('Self Employed and Other Incomes'!B37:H37)","v":0},{"$f":"SUM(B90*20%)","v":0}]
["Applicant 4",{"$f":"SUM('Self Employed and Other Incomes'!B38:H38)","v":0},0]
[]
["Prior Year",null,null,null,"120% Rule"]
["Applicant1",{"$f":"SUM('Self Employed and Other Incomes'!B47:H47)","v":0},{"$f":"MIN(B94*20%,'Self Employed and Other Incomes'!I47)","v":0},{"$f":"'Self Employed and Other Incomes'!L47","v":0},{"$f":"D94*120%","v":0}]
["Applicant 2",{"$f":"SUM('Self Employed and Other Incomes'!B48:H48)","v":0},{"$f":"MIN(B95*20%,'Self Employed and Other Incomes'!I48)","v":0},{"$f":"'Self Employed and Other Incomes'!L48","v":0},{"$f":"D95*120%","v":0}]
["Applicant 3",{"$f":"SUM('Self Employed and Other Incomes'!B49:H49)","v":0},0,{"$f":"'Self Employed and Other Incomes'!L49","v":0},0]
["Applicant 4",{"$f":"SUM('Self Employed and Other Incomes'!B50:H50)","v":0},0,{"$f":"'Self Employed and Other Incomes'!L50","v":0},0]