{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["BS Calculator"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":3,"defined_names":5,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"BS Calculator","index":0,"n_rows":77,"n_cols":12,"rows_kept":77,"cut":false,"hidden":false,"merged":[[20,3,22,3],[76,1,76,5],[1,1,3,5],[12,1,12,2],[6,1,6,5],[43,3,43,4]]}
[null,"Version Dated 06/01/2023"]
[null,"BANK STATEMENT CALCULATOR"]
[]
[]
[]
[]
[null,"Description"]
[null,"Borrower Name:",null,"Initial URLA Income:"]
[null,"Loan Number:",null,"Type of Bank Statement Review:"]
[null,"Date Completed:",null,{"$f":"IF(E9=\"Personal\",\"In the Analysis Type field select 'Third Party Expense Ratio' and leave Verified Expense Factor Recent Year field blank\",\"\")","v":""}]
[null,"Completed By: "]
[]
[null,"* = Required Field"]
[]
[null,"Number Months Bank Statements (12 or 24)",12,"*"]
[null,"Borrowers' Ownership % of the Business",null,{"$f":"_xlfn.IFS(C16<25,\"Not Eligible, must be minimum 25% business owner\",C16<50,\"Borrower must own ≥ 50% when using business bank statements\",C16>=50,\"\")","v":"Not Eligible, must be minimum 25% business owner"}]
[null,"Analysis Type","TER","*"]
[]
[null,"Variable Expense Ratio",15,"Option #1 (Variable Expense Ratio)"]
[]
[null,"Gross Monthly Revenue on P&L most recent 12 Months",null,"Option #2 (Third Party Profit & Loss Statement)"]
[null,"Gross Monthly Revenue on P&L prior 12 Months"]
[null,"Option 2 Gross Revenue on P&L Qualifying Income",{"$f":"IF($C$22=\"\",$C$21,IF($C$21<$C$22,$C$21,($C$21+$C$22)/2))","v":0}]
[]
[null,"Verified Expense Factor Recent Year",null,"Option #2 or #3 (Third Party Expense Ratio)"]
[]
[null,"Loan Amount",null,"*"]
[null,"LTV",null,"*"]
[null,"FICO",null,"*"]
[null,"Rate",null,"*"]
[null,"Product Type",null,"*"]
[]
[null,"Qualifying Mortgage Payment",null,"*",null," "]
[null,"Monthly Taxes",null,"*"]
[null,"Monthly Insurance",null,"*"]
[null,"Monthly HOA Dues",null,"*"]
[null,"Other Liabilities (Monthly)",null,"*"]
[null,"Proposed Monthly Obligation",{"$f":"SUM(C33:C37)","v":0}]
[]
[null,"Bank Statement / P&L Gross Monthly Income",{"$f":"IF(OR(C17 = \"VER\", C17 = \"TER\"), IF(C15 = 12, F60, IF(F60 >F75, F75, F60)),IF(C17=\"PLS\", C23, ))","v":0}]
[null,"Bank Statement / P&L Adjusted Gross Monthly Income",{"$f":"IF(C17 = \"VER\", C40 *((100-C19)/100)*(C16/100), IF(OR(C17=\"TER\",C17=\"PLS\"), C40*((100-C25)/100)*(C16/100),))","v":0}]
[null,"Bank Statement to P&L Variance: Threshold +/- 10%",{"$f":"IF(C17=\"PLS\", 1-(IF(C15=12,C23/F60,IF(F60>F75, C23/(F75),C23/F60))), )","v":0},{"$f":"IF(AND(C42<10%,C42>-10%),\"Requirement Met\",\"Requirement Not Met\")","v":"Requirement Met"}]
[null,"Other Documented Qualifying Income"]
[null,"Total Calculated Income",{"$f":"C41+C43","v":0},"Total Qualifying Income (lesser of initial URLA and calculated income):",null,{"$f":"IF(E8<C44,E8,C44)","v":0}]
[null,"DTI",{"$f":"$C$38/$F$44","v":{"$error":"#DIV/0!"}},{"$f":"_xlfn.IFS(C45>55%,\"DTI exceeds maximum allowance\",C45>=45%,\"Confirm DTI is within Program limits\",C45<45%,\"\")","v":{"$error":"#DIV/0!"}},{"$f":"IF(C45>43%,\"Residual Income Required\",\"\")","v":{"$error":"#DIV/0!"}}]
[]
[null,"Month","Total Deposits into Bank Statements","Less: Transfers, Non Business Deposits (Rent, SSI, Joint Account Wage Income, Refunds, Etc.)","Less: Proceeds from the sale or liquidation of significant assets (including sales of real estate, financial assets - including stocks and bonds, life insurance policies and autos) and loan proceeds from borrowing)","Deposits into Bank Statements"]
[null,"Month 1",null,null,null,{"$f":"C48-D48-E48","v":0}]
[null,"Month 2",null,null,null,0]
[null,"Month 3",null,null,null,0]
[null,"Month 4",null,null,null,0,null,null,null,null,null,1200]
[null,"Month 5",null,null,null,0]
[null,"Month 6",null,null,null,0]
[null,"Month 7",null,null,null,0]
[null,"Month 8",null,null,null,0]
[null,"Month 9",null,null,null,0]
[null,"Month 10",null,null,null,0]
[null,"Month 11",null,null,null,0]
[null,"Month 12",null,null,null,0]
[null,"12 Months Consecutive Income",null,null,null,{"$f":"SUM(F48:F59)/12","v":0}]
[]
[null,"Month","Total Deposits into Bank Statements","Less: Transfers, Non Business Deposits (Rent, SSI, Joint Account Wage Income, Refunds, Etc.)","Less: Proceeds from the sale or liquidation of significant assets (including sales of real estate, financial assets - including stocks and bonds, life insurance policies and autos) and loan proceeds from borrowing)","Adjusted Deposits into Bank Statements"]
[null,"Month 13",null,null,null,{"$f":"C63-D63-E63","v":0}]
[null,"Month 14",null,null,null,0]
[null,"Month 15",null,null,null,0]
[null,"Month 16",null,null,null,0]
[null,"Month 17",null,null,null,0]
[null,"Month 18",null,null,null,0]
[null,"Month 19",null,null,null,0]
[null,"Month 20",null,null,null,0]
[null,"Month 21",null,null,null,0]
[null,"Month 22",null,null,null,0]
[null,"Month 23",null,null,null,0]
[null,"Month 24",null,null,null,0]
[null,"24 Months Consecutive Income",null,null,null,{"$f":"((SUM(F63:F74)+SUM(F48:F59))/24)","v":0}]
[]
[null,"Unusually large deposits must be explained via LOE and must be consistent with business profile. Unexplained large deposits are subject to sourcing requirements or may be excluded from calculations"]