{"text_format":"1","workbook":"xlsx","n_sheets":6,"sheets":["Guide","Low Deposit Fee Calc.","Change Log","Calculation","Inputs","Base"],"not_rendered":{"charts":0,"images":4,"macros":1,"external_links":0,"defined_names":14,"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":"Guide","index":0,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":false,"merged":[]}

{"sheet":"Low Deposit Fee Calc.","index":1,"n_rows":38,"n_cols":4,"rows_kept":38,"cut":false,"hidden":false,"merged":[[0,1,2,4],[37,1,41,4],[28,2,28,4],[21,2,21,4],[7,2,7,4],[4,2,4,4],[24,2,24,4],[26,2,26,4],[27,2,27,4],[8,2,8,4],[13,2,13,4],[14,2,14,4],[11,2,11,4]]}
[null,"Low Deposit Fee Calculator"]
[]
[]
[]
[null,"Date",{"$f":"TODAY()","v":"2019-06-27"}]
[]
[null,"Customer Information"]
[null,"Borrower name"]
[null,"Lender reference number"]
[]
[null,"Loan Information"]
[null,"New loan amount"]
[]
[null,"Predominant loan purpose"]
[null,"Employment"]
[]
[null,"Security purpose",null,"Security value"]
[null,"Security purpose",null,"Security value"]
[null,"Security purpose",null,"Security value"]
[null,"Security purpose",null,"Security value"]
[]
[null,"FHOG to be applied for","No"]
[]
[null,"Premium"]
[null,"LVR",{"$f":"IF(OR(SUM(SecAmount)=0,C8=\"\",C9=\"\"),\"\",IF(OR(C12>2500000,C12<0,E17=\"\"),\"\",ROUND(C12/SUM(SecAmount),4)))","v":""}]
[]
[null,"Low deposit fee",{"$f":"IFERROR(IF(C25=\"\",\"\",\nIF(OR(C25<Base!$A$7,\nAND(SecurityType=\"Rental\",LVR>0.9),\nAND(SecurityType<>\"Rental\",LVR>0.95)\n),\"Not Applicable\",ROUND(FinalLDF,2))),\"\")","v":""}]
[null,"Total loan amount where LDF capitalised",{"$f":"IF(OR(LVR=\"\",C27=\"\"),\"\",\nIF(OR(C27=\"Not Applicable\",C25<0.8),\"Not Applicable\",ROUND((FinalLDF+LoanAmount),2)))","v":""}]
[null,"LDF LVR Check",{"$f":"IF(C28=\"\",\"\",\nIF(C28=\"Not Applicable\",\"Not Applicable\",\nIF(AND(SecurityType=\"Rental\",(C27+LoanAmount)/SUM(SecAmount)>0.9),\"LDF LVR exceeds 90%\",\nIF(AND(SecurityType<>\"Rental\",(C27+LoanAmount)/SUM(SecAmount)>0.95),\"LDF LVR exceeds 95%\",(C27+LoanAmount)/SUM(SecAmount)))))","v":""}]
[]
[]
[]
[]
[null,"Version","2017-04"]
[null,"Effective date","2017-04-28"]
[]
[null,"Disclaimer:"]
[null,"The information contained in this calculator is an estimate only and should not be regarded as a prediction or as personal advice. They are not forecasts or projections but are intended to allow you to make your own projections, based on the information you provide. The calculator is not intended to be a substitute for professional financial advice.  To the extent permitted by law, under no circumstances will Macquarie be liable for any loss or damage (consequential or otherwise) caused by a user's use or reliance on information obtained by using this calculator."]

{"sheet":"Change Log","index":2,"n_rows":6,"n_cols":8,"rows_kept":6,"cut":false,"hidden":true,"merged":[]}
["Date","Version ","Change type","Desciption","Changed by","Change requestor","Reviewed by","Comment"]
["2017-04-21","v10","Initial","Initial Version. Assumptions agreed with AP","JW","AP","AP","Sent for WTW review"]
["2017-04-27","v10.1","Minor","Changes to comments and headers","AP","AP"]
["2017-04-27","v10.2","Minor","This change log added\n\nAlso, per AP instructions:\n1. Update per Paul Caputo feedback below. \n          Need to change display such that the message which appears for a 79.99% LVR result (Not Applicable), appears for an 80.00& LVR. Current 80.00% LVR displays as “$0.00”. Should be not applicable same as 79.99%. Threshold is 80.01%                                \n          a. Guide indicates LVR to be >80% and less than or equal to 95%. To be entirely consistent with this, cell c27 on calc sheet should not show value if c25<=0.8, rather than if c25<0.8.\n2. Formatting changes\n          a. Hide tabs other than Guide & LDF Fee Calc\n          b. Hide lines and columns on spreadsheet\n          c. Any other formatting changes such that display user sees, will be same as Servicing Calculator (attached). i.e. this is view they see today\n3. Formatting changes part 2\n          a. Save file version as Macquarie\n          b. Remove Macquarie logo banner (leaving generic display Low Deposit Fee Calculator as text)\n          c. Save file version as Brand\n\n4. Locking down spreadsheets (Both Macquarie & Brand) & password protect\n          a. Lock down behind the scenes rules, formula etc, such that cannot be edited\n          b. Add password, let’s use SIM_2018\n","JZ","AP/WTW","AP"]
["2017-04-28","v10.3","Minor","- Hidden tabs locked, cells hidden\n- Effective Date applied: 28 April 2017\n- Instruction added to LDF Calc tab: \"All green coloured fields must be completed\"\n\n\n","JZ","AP","AP","Distributed to users"]
["2017-05-01","v10.4","Minor","LDF LVR Check on 'Low Deposit Fee Calc.' tab to be updated to show LVR when below threshold\nConditional Format updated accordingly for this cell","JZ","AP","AP"]

{"sheet":"Calculation","index":3,"n_rows":38,"n_cols":11,"rows_kept":38,"cut":false,"hidden":true,"merged":[[3,1,4,1],[16,5,16,8],[5,1,6,1],[7,1,8,1],[9,1,11,1],[16,1,16,3]]}
[null,"Final security Type",{"$f":"IF('Low Deposit Fee Calc.'!E17=\"\",\"\",IF('Low Deposit Fee Calc.'!C17=\"\",\"\",IF(SUMIF(SecPurp,\"Rental\",SecAmount)/SUM(SecAmount)<=0.5,\"Owner Occupied\",\"Rental\")))","v":""}]
[]
["ID","Field","Drop down","Classification","Selection"]
[{"$f":"IF(E4=\"Y\",D4,\"\")","v":""},"First Home Buyer","Yes","FHB",{"$f":"IF(E7=\"Y\",\"N\",IF(SecurityType=\"Owner Occupied\",IF(FHB=C4,\"Y\",\"N\"),\"N\"))","v":"N"}]
["",null,"No","Base",{"$f":"IF(E7=\"Y\",\"Y\",IF(SecurityType=\"Owner Occupied\",IF(FHB=C5,\"Y\",\"N\"),\"N\"))","v":"N"}]
["","Security","Owner occupied","Base",{"$f":"IF(SecurityType=C6,\"Y\",\"N\")","v":"N"}]
["",null,"Rental","Investment",{"$f":"IF(SecurityType=C7,\"Y\",\"N\")","v":"N"}]
["","Employment","PAYG","Base",{"$f":"IF(EmploymentType=C8,\"Y\",\"N\")","v":"N"}]
["",null,"Self Employed","Self employed",{"$f":"IF(EmploymentType=C9,\"Y\",\"N\")","v":"N"}]
["","Predominant Loan purpose","Purchase, Construction or Home Improvement","Base",{"$f":"IF(LPType=C10,\"Y\",\"N\")","v":"N"}]
["",null,"Refinance/Debt Consolidation","Refinance",{"$f":"IF(LPType=C11,\"Y\",\"N\")","v":"N"}]
["",null,"Other including equity release or cash-out","Refinance",{"$f":"IF(LPType=C12,\"Y\",\"N\")","v":"N"}]
[]
[null,"Category final",{"$f":"A4&A5&A6&A7&A8&A9&A10&A11&A12","v":""}]
[]
[]
[null,"Scenarios",null,null,null,"Loading"]
["ID","First Home Buyer - classificaiton","Security - classification","Employment -  classification","Predominant Loan purpose - classification","First home buyer (-5%)*","Investment (15%)","Loan purpose (5%)","Employment Type (10%)","Loan Amount","Total"]
[{"$f":"B19&C19&D19&E19","v":"BaseBaseBaseBase"},"Base","Base","Base","Base",{"$f":"1+IF(B19=\"FHB\",-0.05,0)","v":1},{"$f":"1+IF(C19=\"Investment\",0.15,0)","v":1},{"$f":"1+IF(E19=\"Refinance\",0.05,0)","v":1},{"$f":"1+IF(D19=\"Self Employed\",0.1,0)","v":1},{"$f":"LoanAmount","v":0},{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J19,3)*H19,3)*G19,3)*I19,3)   )","v":""}]
[{"$f":"B20&C20&D20&E20","v":"FHBBaseBaseBase"},"FHB","Base","Base","Base",{"$f":"1+IF(B20=\"FHB\",-0.05,0)","v":0.95},{"$f":"1+IF(C20=\"Investment\",0.15,0)","v":1},{"$f":"1+IF(E20=\"Refinance\",0.05,0)","v":1},{"$f":"1+IF(D20=\"Self Employed\",0.1,0)","v":1},0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J20,3)*H20,3)*G20,3)*I20,3)   )","v":""}]
["BaseInvestmentBaseBase","Base","Investment","Base","Base",1,1.1499999999999999,1,1,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J21,3)*H21,3)*G21,3)*I21,3)   )","v":""}]
["BaseBaseSelf EmployedBase","Base","Base","Self Employed","Base",1,1,1,1.1000000000000001,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J22,3)*H22,3)*G22,3)*I22,3)   )","v":""}]
["BaseBaseBaseRefinance","Base","Base","Base","Refinance",1,1,1.05,1,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J23,3)*H23,3)*G23,3)*I23,3)   )","v":""}]
["FHBInvestmentBaseBase","FHB","Investment","Base","Base",0.95,1.1499999999999999,1,1,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J24,3)*H24,3)*G24,3)*I24,3)   )","v":""}]
["FHBBaseSelf EmployedBase","FHB","Base","Self Employed","Base",0.95,1,1,1.1000000000000001,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J25,3)*H25,3)*G25,3)*I25,3)   )","v":""}]
["FHBBaseBaseRefinance","FHB","Base","Base","Refinance",0.95,1,1.05,1,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J26,3)*H26,3)*G26,3)*I26,3)   )","v":""}]
["BaseInvestmentSelf EmployedBase","Base","Investment","Self Employed","Base",1,1.1499999999999999,1,1.1000000000000001,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J27,3)*H27,3)*G27,3)*I27,3)   )","v":""}]
["BaseInvestmentBaseRefinance","Base","Investment","Base","Refinance",1,1.1499999999999999,1.05,1,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J28,3)*H28,3)*G28,3)*I28,3)   )","v":""}]
["BaseBaseSelf EmployedRefinance","Base","Base","Self Employed","Refinance",1,{"$f":"1+IF(C29=\"Investment\",0.15,0)","v":1},1.05,1.1000000000000001,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J29,3)*H29,3)*G29,3)*I29,3)   )","v":""}]
["FHBInvestmentSelf EmployedBase","FHB","Investment","Self Employed","Base",0.95,1.1499999999999999,1,1.1000000000000001,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J30,3)*H30,3)*G30,3)*I30,3)   )","v":""}]
["FHBInvestmentBaseRefinance","FHB","Investment","Base","Refinance",0.95,1.1499999999999999,1.05,1,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J31,3)*H31,3)*G31,3)*I31,3)   )","v":""}]
["FHBBaseSelf EmployedRefinance","FHB","Base","Self Employed","Refinance",0.95,1,1.05,1.1000000000000001,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J32,3)*H32,3)*G32,3)*I32,3)   )","v":""}]
["BaseInvestmentSelf EmployedRefinance","Base","Investment","Self Employed","Refinance",1,1.1499999999999999,1.05,1.1000000000000001,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J33,3)*H33,3)*G33,3)*I33,3)   )","v":""}]
["FHBInvestmentSelf EmployedRefinance","FHB","Investment","Self Employed","Refinance",0.95,1.1499999999999999,1.05,1.1000000000000001,0,{"$f":"IF(LVR=\"\",\"\",ROUND(ROUND(ROUND(ROUND(INDEX(Base!$A$2:$I$21,MATCH(LVR,Base!$A$2:$A$21,1),MATCH(LoanAmount,Base!$A$2:$I$2,1))/100*J34,3)*H34,3)*G34,3)*I34,3)   )","v":""}]
[null,null,null,null,null,"* Currently not in use"]
[]
[]
[null,"Final Rate",{"$f":"(IF('Low Deposit Fee Calc.'!C8=\"\",\"\",VLOOKUP(Calculation!$C$14,Calculation!$A$17:$K$34,COLUMNS($A$17:$K$17),0)))","v":""}]

{"sheet":"Inputs","index":4,"n_rows":4,"n_cols":7,"rows_kept":4,"cut":false,"hidden":true,"merged":[]}
["First Home Buyer",null,"Predominant loan purpose",null,"Employment",null,"Security"]
["Yes",null,"Purchase, Construction or Home Improvement",null,"PAYG",null,"Owner occupied"]
["No",null,"Refinance/Debt Consolidation",null,"Self Employed",null,"Rental"]
[null,null,"Other including equity release or cash-out"]

{"sheet":"Base","index":5,"n_rows":25,"n_cols":14,"rows_kept":25,"cut":false,"hidden":true,"merged":[[3,2,5,13]]}
[null,"Base"]
[null,null,0,300000.00099999999,500000.00099999999,750000.00100000005,1000000.001,1500000.0009999999,2000000.0009999999]
[null,"MBL","STANDARD TO $300,000","STANDARD FROM $300,001 TO $500,000","STANDARD FROM $500,001 TO $750,000","STANDARD FROM $750,001 TO $1.0M","STANDARD FROM $1.0M TO $1.5M","STANDARD FROM $1.5M TO $2.0M","STANDARD FROM $2.0M TO $2.5M","STANDARD FROM $2.5M TO $3M","STANDARD FROM $3.0M TO $3.5M","STANDARD FROM $3.5M TO $4M","STANDARD FROM $4M TO $4.5M","STANDARD FROM $4.5M TO $5M"]
[0.6,"up to 60%","Not applicable. Borrower does not pay premium/fee in these bands"]
[0.60009999999999997,"above 60% to 70%"]
[0.70009999999999994,"above 70% to 80%"]
[0.80010000000000003,"above 80% to 81%",0.46400000000000002,0.53600000000000003,0.85499999999999998,0.85499999999999998,0.89100000000000001,0.96399999999999997,0.98199999999999998]
[0.81010000000000004,"above 81% to 82%",0.47299999999999998,0.53600000000000003,0.85499999999999998,0.85499999999999998,0.89100000000000001,1.0089999999999999,1.018]
[0.82010000000000005,"above 82% to 83%",0.65500000000000003,0.80900000000000005,1,1,1.145,1.1910000000000001,1.2729999999999999]
[0.83009999999999995,"above 83% to 84%",0.65500000000000003,0.80900000000000005,1.073,1.073,1.282,1.3360000000000001,1.409]
[0.84009999999999996,"above 84% to 85%",0.82699999999999996,1.0820000000000001,1.2270000000000001,1.236,1.4550000000000001,1.536,1.609]
[0.85009999999999997,"above 85% to 86%",0.85499999999999998,1.0820000000000001,1.345,1.345,1.6180000000000001,1.7909999999999999,1.9]
[0.86009999999999998,"above 86% to 87%",1.0089999999999999,1.218,1.4910000000000001,1.5089999999999999,1.8,1.891,1.9179999999999999]
[0.87009999999999998,"above 87% to 88%",1.0089999999999999,1.218,1.573,1.5820000000000001,1.9,1.9910000000000001,2.0089999999999999]
[0.88009999999999999,"above 88% to 89%",1.3089999999999999,1.718,2.0089999999999999,2.0640000000000001,2.3359999999999999,2.4359999999999999,2.5179999999999998]
[0.8901,"above 89% to 90%",1.4359999999999999,1.8180000000000001,2.3180000000000001,2.327,2.5550000000000002,2.573,2.6360000000000001]
[0.90010000000000001,"above 90% to 91%",1.982,2.5550000000000002,3.4550000000000001,3.5089999999999999,4.0549999999999997,4.1820000000000004,4.5270000000000001]
[0.91010000000000002,"above 91% to 92%",1.982,2.5550000000000002,3.5910000000000002,3.6360000000000001,4.2359999999999998,4.3360000000000003,4.7]
[0.92010000000000003,"above 92% to 93%",2.282,2.891,3.7269999999999999,3.7360000000000002,4.5640000000000001,4.7910000000000004,4.8550000000000004]
[0.93010000000000004,"above 93% to 94%",2.282,2.891,4.0819999999999999,4.1449999999999996,4.7359999999999998,4.9640000000000004,5.0270000000000001]
[0.94010000000000005,"above 94% to 95%",2.536,3.1909999999999998,4.282,4.282,4.7359999999999998,4.9640000000000004,5.2450000000000001]
[null,"Notes"]
[null,null,"Not applicable for borrower pays"]
[null,null,"Not Applicable outside market appetite"]
[null,null,"Rates based on NSW, no stamp duty, no GST and applicable to all states"]