{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["PAYG YTD Income","Debt to Income","Net income","Remuneration","Repayment calculator","Valuation age","HELP Rates"],"not_rendered":{"charts":0,"images":5,"macros":1,"external_links":0,"defined_names":1,"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":"PAYG YTD Income","index":0,"n_rows":19,"n_cols":12,"rows_kept":19,"cut":false,"hidden":false,"merged":[[4,2,4,3],[6,5,7,6]]}
[]
[]
[]
[]
[null,null,"Year to Date Income",null,null,null,null,null,"Monthly",30.416665999999999,12,11]
[null,null,"Borrower Name",null,null,null,null,"Employment type:","Fortnightly",14,26,24]
[null,null,"Employment type","--Please select--",null,{"$f":"IF(D7=H8,\"Employment type does not typically include paid annual leave etc - income calculated over 48 weeks\",\"\")","v":""},null,"Full / part time","Weekly",7,52,48]
[null,null,"Date paid to (as per payslip)",null,null,null,null,"Casual / contract","Employment type does not typically include paid annual leave etc"]
[null,null,"Employment start date (if started after 1 July)",null,null,{"$f":"IF(MONTH(TODAY())<7,DATE(YEAR(TODAY())-1,7,1),DATE(YEAR(TODAY()),7,1))","v":"2022-07-01"}]
[null,null,"Start date current tax year",{"$f":"IF(D9=\"\",F9,D9)","v":"2022-07-01"},null,{"$f":"IF(MONTH(TODAY())<7,DATE(YEAR(TODAY())-1,7,1),DATE(YEAR(TODAY()),7,1))","v":"2022-07-01"}]
[null,null,"Pay cycle","--Please select--"]
[null,null,"YTD earnings"]
[null,null,"No. of days since start FY",{"$f":"SUM(D8-D10)+1","v":-44742}]
[null,null,"Pay cycles in YTD",{"$f":"IFERROR(IF(D11=\"Monthly\",DATEDIF(D10,D8,\"M\")+1,ROUNDUP(SUM(D13/VLOOKUP(D11,$I$5:$K$7,2,FALSE)),0)),\"\")","v":""}]
[null,null,"Total days paid in YTD",{"$f":"IFERROR(SUM(D14*VLOOKUP(D11,$I$5:$K$7,2,FALSE)),\"\")","v":""}]
[null,null,"Start of 1st pay cycle YTD ",{"$f":"IF(OR(D8=\"\",D10=\"\",D11=\"\"),\"\",SUM(D8-D15)+1)","v":""}]
[null,null,"Average per pay cycle from YTD",{"$f":"IF(OR(D8=\"\",D10=\"\",D11=\"\"),\"\",SUM(D12/D14))","v":""}]
[null,null,{"$f":"IF(D7=\"casual / contract\",\"Average per pay cycle over 48 weeks\",\"Average per pay cycle\")","v":"Average per pay cycle"},{"$f":"IF(OR(D8=\"\",D11=\"--Please select--\",D12=\"\"),\"\",IF(D7=\"Full / part time\",D17,IF(D7=\"Casual / contract\",SUM(D19/VLOOKUP(D11,$I$5:$L$7,3,FALSE)),\"\")))","v":""}]
[null,null,{"$f":"IF(D7=\"casual / contract\",\"Annual income over 48 weeks\",\"Annual income\")","v":"Annual income"},{"$f":"IF(OR(D8=\"\",D11=\"--Please select--\",D12=\"\"),\"\",IF(D7=\"Full / part time\",SUM(D17*VLOOKUP(D11,$I$5:$K$7,3,FALSE)),IF(D7=\"Casual / contract\",SUM(D17*VLOOKUP(D11,$I$5:$L$7,4,FALSE)),\"\")))","v":""}]

{"sheet":"Debt to Income","index":1,"n_rows":25,"n_cols":7,"rows_kept":25,"cut":false,"hidden":false,"merged":[[24,2,24,3],[25,2,26,3],[4,2,4,6],[14,4,19,4]]}
[]
[]
[]
[]
[null,null,"Debt to Income (DTI) calculator"]
[null,null,"Income","Gross Annual","Annual Rents","Other Income","Non tax income"]
[null,null,"Borrower 1"]
[null,null,"Borrower 2"]
[null,null,"Borrower 3"]
[null,null,"Borrower 4"]
[null,null,"Borrower 5"]
[]
[null,null,"Debts","Limit"]
[null,null,"New loan amount",null,"Include LMI premium or LRF being capitalised in New Loan Amount."]
[null,null,"Home Loan",null,"Exclude loans being refinanced that are included in the 'New loan amount' above"]
[null,null,"Investment"]
[null,null,"Car / Personal Loan"]
[null,null,"HECS/HELP"]
[null,null,"Other"]
[null,null,"Credit Card"]
[]
[null,null,"Total debt",{"$f":"SUM(D14:D20)","v":0}]
[null,null,"Total income",{"$f":"SUM(D7:G11)","v":0}]
[null,null,"DTI",{"$f":"IF(D23=0,\"\",ROUND(D22/D23,1))","v":""},{"$f":"IF(D23=0,\"\",IF(ROUND(D22/SUM(D7:D11,SUM(E7:E11)*0.8,F7:F11,G7:G11),1)>8,ROUND(D22/SUM(D7:D11,SUM(E7:E11)*0.8,F7:F11,G7:G11),1),0))","v":""},{"$f":"IF(E24=\"\",\"\",\"DTI (using shaded rent)\")","v":""}]
[null,null,{"$f":"IF(D23=0,\"\",IF(E24>8,\"DTI is > 8.00 - refer Credit Policy regarding Investor Risk Fee\",\"\"))","v":""}]

{"sheet":"Net income","index":2,"n_rows":23,"n_cols":15,"rows_kept":23,"cut":false,"hidden":false,"merged":[[21,11,21,13],[22,11,22,13],[4,2,4,3],[7,11,7,14],[8,11,8,12],[8,13,9,13],[8,14,9,14],[20,11,20,13]]}
[]
[]
[]
[]
[null,null,"Net Income Calculator"]
[null,null,"Gross amount"]
[null,null,"Frequency","Yearly"]
[null,null,"Annual income (taxable)",{"$f":"SUM(D6*VLOOKUP(D7,$H$9:$I$12,2,FALSE))","v":0},null,null,null,null,null,null,null,"INCOME TAX SCHEDULE"]
[null,null,"Tax",{"$f":"IF(D8<1,0,(D8-LOOKUP(D8,$M$11:$M$16,$L$12:$L$17))*LOOKUP(D8,M11:M16,N12:N17)+LOOKUP(D8,M11:M16,O11:O16)+IF(D8>O22,D8*O21,0))","v":0},null,null,null,"Weekly",52,null,null,"Bounds",null,"Tax\nRate","Interval Tax"]
[null,null,"Net income per annum",{"$f":"SUM(D8-D9)","v":0},null,null,null,"Fortnightly",26,null,null,"Lower","Upper"]
[null,null,null,null,null,null,null,"Monthly",12,null,null,0,0,0,0]
[null,null,"Show other frequency","Weekly",null,null,null,"Yearly",1,null,null,0,18200,0,{"$f":"(M12-M11)*N12+O11","v":0}]
[null,null,"Income (taxable)",{"$f":"SUM(D8/VLOOKUP(D12,H9:I12,2,FALSE))","v":0},null,null,null,null,null,null,null,18200,45000,0.19,{"$f":"(M13-M12)*N13+O12","v":5092}]
[null,null,"Tax",{"$f":"SUM(D9/VLOOKUP(D12,H9:I12,2,FALSE))","v":0},null,null,null,null,null,null,null,45000,120000,0.32500000000000001,{"$f":"(M14-M13)*N14+O13","v":29467}]
[null,null,"Net income",{"$f":"SUM(D10/VLOOKUP(D12,H9:I12,2,FALSE))","v":0},null,null,null,null,null,null,null,120000,180000,0.37,{"$f":"(M15-M14)*N15+O14","v":51667}]
[null,null,null,null,null,null,null,null,null,null,null,180000,9999999,0.45,{"$f":"(M16-M15)*N16+O15","v":4470666.55}]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(M18=0,\"\",M18)","v":""}]
[]
[null,null,null,null,null,null,null,null,null,null,null,"MEDICARE LEVY",null,null,0.02]
[null,null,null,null,null,null,null,null,null,null,null,"Medicare Levy Threshold",null,null,24276]
[null,null,null,null,null,null,null,null,null,null,null,"COMPANY TAX",null,null,0.25]

{"sheet":"Remuneration","index":3,"n_rows":13,"n_cols":9,"rows_kept":13,"cut":false,"hidden":false,"merged":[[4,2,4,3],[12,2,12,3]]}
[]
[]
[]
[]
[null,null,"Total Remuneration "]
[null,null,"Annual income"]
[null,null,"Includes Super?","Yes",null,null,"Super (calculated with SGC: ",{"$f":"D12*100","v":11},"%)"]
[null,null,"Salary",{"$f":"IF(D7=\"Yes\",SUM(D6*H8),D6)","v":0},null,null,"Factor (TEC x this factor = Gross income)",{"$f":"1/H9","v":0.9009009009009008}]
[null,null,{"$f":"CONCATENATE(G7,H7,I7)","v":"Super (calculated with SGC: 11%)"},{"$f":"IF(D7=\"Yes\",SUM(D6*H10),SUM(D6*H7%))","v":0},null,null,"Factor (Gross income x this factor = TEC)",{"$f":"1+SUM(H7/100)","v":1.1100000000000001}]
[null,null,"Total Remuneration",{"$f":"SUM(D8:D9)","v":0},null,null,"Factor (Annual income x factor = Super)",{"$f":"H8*(H7/100)","v":9.9099099099099086E-2}]
[null,null,null,null,null,null,"link:","http://salary.calculatorsaustralia.com.au/"]
[null,null,"SGC for calculation (select)",0.11]
[null,null,{"$f":"IF(D12=9.5%,\"Employer contribution rate 01/07/2020 to 30/06/2021 selected\",IF(D12=10%,\"Employer contribution rate 01/07/2021 to 30/6/2022 selected\",IF(D12=10.5%,\"Employer contribution rate 01/07/2022 to 30/06/2023 selected\",IF(D12=11%,\"Employer contribution rate 01/07/2023 to 30/06/2024 selected\",\"\"))))","v":"Employer contribution rate 01/07/2023 to 30/06/2024 selected"}]

{"sheet":"Repayment calculator","index":4,"n_rows":25,"n_cols":13,"rows_kept":25,"cut":false,"hidden":false,"merged":[[4,2,4,3],[4,8,4,11]]}
[]
[]
[]
[]
[null,null,"Loan repayment calculator",null,null,null,null,null,"Weighted Average Interest Rate"]
[null,null,"Repayment Type (select)","Principal & Interest",null,"Principal & Interest",{"$f":"IF(F6=1,\"PI\",\"IO\")","v":"IO"},null,"App#","Interest Rate","Repayment type (PI or IO)","Amount"]
[null,null,"Loan amount/Balance",0,null,"Interest Only",null,null,null,null,null,null,{"$f":"IFERROR(SUM(L7*J7)/(SUM($L$7:$L$16)),0)","v":0}]
[null,null,"Advance / redraw",0,null,null,null,null,null,null,null,null,{"$f":"IFERROR(SUM(L8*J8)/(SUM($L$7:$L$16)),0)","v":0}]
[null,null,"Total loan limit",{"$f":"SUM(D7:D8)","v":0},null,null,null,null,null,null,null,null,0]
[null,null,"Interest rate",0,null,null,null,null,null,null,null,null,0]
[null,null,"Term in months",360,null,null,null,null,null,null,null,null,0]
[null,null,"Monthly Repayment",{"$f":"IF(AND(D9>0,D10>0,D11>0),IF(D6=\"Principal & Interest\",PMT(D10/12,D11,-D9,0,0),SUM((D9*D10/365)*31)),\"\")","v":""},null,null,"--Please select--",null,null,null,null,null,0]
[null,null,"Interest only period (years)","--Please select--",null,null,"1 year",12,null,null,null,null,0]
[null,null,"PI repayment after IO period",{"$f":"IF(D13=G12,\"\",IF(AND(D9>0,D10>0,D11>0),IF(D6=\"Interest Only\",PMT(D10/12,D11-VLOOKUP(D13,G12:H17,2,FALSE),-D9,0,0),\"Not applicable\"),\"\"))","v":""},null,null,"2 years",24,null,null,null,null,0]
[null,null,null,null,null,null,"3 years",36,null,null,null,null,0]
[null,null,null,null,null,null,"4 years",48,null,null,null,null,0]
[null,null,null,null,null,null,"5 years",60,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,null,null,0]
[null,null,null,null,null,null,null,null,null,null,"Total PI",{"$f":"SUMPRODUCT((K7:K21=\"PI\")*L7:L21)","v":0}]
[null,null,null,null,null,null,null,null,null,null,"Total IO",{"$f":"SUMPRODUCT((K7:K21=\"IO\")*L7:L21)","v":0}]
[null,null,null,null,null,null,null,null,null,null,"Total new loan amount",{"$f":"SUM(L7:L21)","v":0}]
[null,null,null,null,null,null,null,null,null,null,"WAIR",{"$f":"ROUNDDOWN(SUM(M7:M21),4)","v":0}]

{"sheet":"Valuation age","index":5,"n_rows":16,"n_cols":7,"rows_kept":16,"cut":false,"hidden":false,"merged":[[4,2,4,3],[14,2,14,3],[13,2,13,3]]}
[]
[]
[]
[]
[null,null,"Age of valuation",null,null,"Principal & Interest",{"$f":"IF(F5=1,\"PI\",\"IO\")","v":"IO"}]
[null,null,"Valuation date:",null,null,"Interest Only"]
[null,null,"Today's date",{"$f":"NOW()","v":"2023-06-19T18:01:42.150"}]
[null,null,"Valuation age (days):",{"$f":"IF(OR(D6=\"\",D7=\"\"),\"\",IF(SUM(D7-D6)>0,SUM(D7-D6),\"Val date entered is in the future\"))","v":""},{"$f":"IF(D8=\"\",\"\",D8/365)","v":""}]
[null,null,"Val report 90 days old on:",{"$f":"IF(OR(D6=\"\",D7=\"\"),\"\",IF(SUM(D7-D6)>0,SUM(D6+89),\"Val date entered is in the future\"))","v":""},{"$f":"IF(D6=\"\",\"\",SUM(D9-D6+1))","v":""},{"$f":"IF(OR(D6=\"\",D7=\"\"),\"\",IF(AND(D8>90,D8<=180),\"Valuation is out of date for assessment purposes, refer to Credit\",\"\"))","v":""}]
[null,null,"Val report 180 days old on:",{"$f":"IF(OR(D6=\"\",D7=\"\"),\"\",IF(SUM(D7-D6)>0,SUM(D6+179),\"Val date entered is in the future\"))","v":""},{"$f":"IF(D6=\"\",\"\",SUM(D10-D6+1))","v":""},{"$f":"IF(OR(D6=\"\",D7=\"\"),\"\",IF(D8>180,\"Valuation is out of date & unacceptable - new valuation required\",\"\"))","v":""}]
[null,null,"Contract of Sale date (if applicable):"]
[null,null,"Total purchase / cost price:",null,null,"CoS Date ==>"]
[null,null,"Valuation amount:"]
[null,null,{"$f":"IF(OR(D12=0,D13=0),\"\",IF(D16<0,CONCATENATE(\"Valuation in low - total purchase / cost price is \",TEXT(-D16,\"#,#0.00%\"),\" above current market value\"),IF(D16=0,\"Market value on valuation report matches purchase price\",CONCATENATE(\"Market value is \",TEXT(D16,\"#,#0.00%\"),\" above purchase price\"))))","v":""}]
[null,null,{"$f":"IF(D16<-20%,\"Security may not be acceptable due to signficant valuation variance\",IF(AND(D16<-10%,D16>=-20%),\"Valuation variation letter required prior to settlement\",IF(AND(D11>0,SUM(D7-D11)>365,D13>D12),\"May rely on valuation  amount for LVR as CoS date is more than 12 months\",\"\")))","v":""}]
[null,null,null,{"$f":"IF(D12>D13,IFERROR(ROUND(SUM(D12-D13)/-D13,4),\"\"),IF(D12=D13,0,IF(D12<D13,IFERROR(ROUND(SUM(D13-D12)/D13,4),\"\"))))","v":0}]

{"sheet":"HELP Rates","index":6,"n_rows":23,"n_cols":4,"rows_kept":23,"cut":false,"hidden":true,"merged":[[11,1,11,2]]}
[]
[]
[]
[null,"Annual income"]
[null,"HELP "]
[]
[]
[]
[]
[]
[]
[null,"Repayment income (RI*)",null,"Repayment rate"]
[null,"Lower","Upper","Rate"]
[null,0,54868,0]
[null,54869,61119,0.04]
[null,61120,67368,4.4999999999999998E-2]
[null,67369,70909,0.05]
[null,70910,76222,5.5E-2]
[null,76223,82550,0.06]
[null,82551,86894,6.5000000000000002E-2]
[null,86895,95626,7.0000000000000007E-2]
[null,95627,101899,7.4999999999999997E-2]
[null,101900,1000000,0.08]