{"text_format":"3","workbook":"xlsx","n_sheets":4,"sheets":["Business Bk Stmt Calculator","Instructions","Val","Covid Special Considerations"],"not_rendered":{"images":1,"macros":1,"defined_names":3},"read":"whole","date_system":"1900"}

{"sheet":"Business Bk Stmt Calculator","index":0,"n_rows":51,"n_cols":23,"rows_kept":51,"merged":[[0,0,0,19],[3,0,3,4],[3,8,3,9],[3,10,3,11],[4,10,4,11],[4,14,5,19],[5,0,5,3],[5,5,6,6],[5,7,6,7],[5,9,6,10],[5,11,6,12],[6,0,6,1],[6,2,6,3],[6,14,7,18],[6,19,7,19],[7,0,7,1],[7,2,7,3],[8,0,8,1],[8,2,8,3],[8,5,9,8],[8,9,9,10],[8,11,9,12],[8,14,9,18],[8,19,9,19],[9,0,9,1],[9,2,9,3],[10,0,10,1],[10,2,10,3],[10,4,16,4],[10,5,11,8],[10,9,11,10],[10,11,11,12],[10,13,16,13],[10,14,11,18],[10,19,11,19],[11,0,11,1],[11,2,11,3],[12,0,12,1],[12,2,12,3],[12,14,12,18],[13,5,13,8],[13,14,13,18],[14,0,17,0],[14,14,16,18],[14,19,16,19],[15,25,15,28],[18,4,18,18],[18,25,19,26],[18,27,19,28],[22,23,22,25],[22,26,22,27],[23,23,23,25],[23,26,23,27],[24,23,24,25],[24,26,24,27],[43,17,43,18],[44,0,44,19],[45,0,45,19],[46,0,46,19],[47,0,47,1],[48,0,48,19],[49,0,49,19],[50,0,50,19],[57,0,57,1],[58,0,58,1],[59,0,59,1],[60,0,60,1]]}
[" Business Bank Statement Program Income Calculator (Includes Commingled Accounts***)"]
[]
[]
[{"$nulls":8},"Results Analysis"]
[{"$nulls":14},"Income Calculation"]
["Account Information",{"$nulls":4},"Select the  Number of Statements:",null,24,null,"Enter 1003 Monthly Net Income:",null,{"v":130000,"d":"$130,000"}]
["Name on Account ",null,"Acme Brick",{"$nulls":11},"Monthly Income from Bank Statement Deposits",{"$nulls":4},{"$f":"T44/H6","v":{"v":100000,"d":"$100,000"}}]
["Financial Institution ",null,"Acme Bank"]
["Account Number:",null,1313,null,null,"Choose Business Bank Statement Method",{"$nulls":3},{"$f":"VLOOKUP($F$11,Val!$M$1:$P$3,3,0)","v":""},null,{"$f":"VLOOKUP($F$11,Val!$M$1:$P$3,4,0)","v":"Enter CPA/ Certified Tax Preparer Expense Ratio"},null,null,"Monthly Gross Receipts from P/L",{"$nulls":4},{"$f":"IF(W11=2,J11/H6, \"N/A\")","v":"N/A"}]
["Borrower Name:",null,"joe doe"]
["Underwriter:",null,"gayla",null,null,"Option 2 - 3rd Party Expense Statement",{"$nulls":5},{"v":0.5,"d":"50%"},null,null,"Expense Ratio",{"$nulls":4},{"$f":"IF(W11=1,IF(AND(J11=\"Service Business\",L11=\"0\"),20%,IF(AND(J11=\"Service Business\",L11=\"1-10\"),40%,IF(AND(J11=\"Service Business\",L11=\">10\"),60%,IF(L11=\"0\",30%,IF(L11=\"1-10\",50%,IF(L11=\">10\",70%,0%)))))),L11)","v":{"v":0.5,"d":"50.0%"}},null,null,{"$f":"VLOOKUP($F$11,Val!$M$1:$P$3,2,0)","v":3}]
["ARC Loan Number:",null,1234]
["Date",null,{"$f":"TODAY()","v":"2020-07-10"},{"$nulls":11},{"$f":"IF(W11=1,Val!B14,IF(W11=2,Val!B20,Val!B16))","v":"Qualifying Income Using the CPA/Tax-Preparer Expense Ratio"},{"$nulls":4},{"$f":"IF(W11=1,Val!E15,IF(W11=2,Val!E20,Val!E15))","v":{"v":50000,"d":"$50,000"}}]
[{"$nulls":5},"Please Enter Borrower Percentage of Ownership",{"$nulls":3},{"v":0.5,"d":"50%"},{"$nulls":4},"Borrower's Percentage of Ownership",{"$nulls":4},{"$f":"J14","v":{"v":0.5,"d":"50%"}}]
["Order of Bank Statements:",{"$nulls":13},"Qualifying Income Based on Borrower's Percentage of Ownership",{"$nulls":4},{"$f":"T14*T13","v":{"v":25000,"d":"$25,000"}}]
[]
[]
[null,"Descending"]
["Month","Statement End Date","*NSFs","Total Monthly Deposits","**Ineligible deposits to be subtracted   (input figures as positive numbers)",{"$nulls":14},"Total Adjusted Deposits"]
[1,"2020-06-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"$f":"SUM(D20,-(SUM(E20:S20)))","v":{"v":100000,"d":"$100,000"}}]
[2,{"$f":"IF($B$18=\"Ascending\",EDATE(B20,1),EDATE(B20,-1))","v":"2020-05-15"},0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[3,{"$f":"IF($B$18=\"Ascending\",EDATE(B21,1),EDATE(B21,-1))","v":"2020-04-15"},0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[4,"2020-03-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[5,"2020-02-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[6,"2020-01-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[7,"2019-12-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[8,"2019-11-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[9,"2019-10-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[10,"2019-09-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[11,"2019-08-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[12,"2019-07-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[13,"2019-06-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[14,"2019-05-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[15,"2019-04-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[16,"2019-03-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[17,"2019-02-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[18,"2019-01-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[19,"2018-12-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[20,"2018-11-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[21,"2018-10-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[22,"2018-09-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[23,"2018-08-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[24,"2018-07-15",0,{"v":100000,"d":"$100,000"},{"$nulls":15},{"v":100000,"d":"$100,000"}]
[{"$nulls":17},"Total Allowable Deposits:",null,{"$f":"IF(H6=12, SUM(T20:T31),SUM(T20:T43))","v":{"v":2400000,"d":"$2,400,000"}}]
["* NSFs and overdrafts:  No more than 3 allowed in a 12 month period"]
["**Ineligible deposits Include: Transfers from other accounts, Income from other sources, credit lines, business loans,  rental Income, one-time deposits, cash advances from credit cards, returns/refunds for credit"]
["***Comingled accounts -Single account reflecting both personal/business income and expenses"]
["DISCLAIMER:"]
["The information provided via this calculator are intended for illustrative purposes for mortgage professionals only; accuracy is not guaranteed.  "]
["The results provided do not constitute either a pre-qualification or a credit decision.  "]
["Qualification for loan programs that are offered by Arc Home requires specific borrower, property and other applicable information to be reviewed by an Arc Home underwriter."]

{"sheet":"Instructions","index":1,"n_rows":14,"n_cols":2,"rows_kept":14,"hidden":true,"merged":[[13,1,13,7]]}
[null,"Instructions"]
[null,"1) Confirm 24 months Bank Statement data is available for input. "]
[null,"2) Input Account Information  (as available).  Account must be in the name of the business reflecting only business income and expenses and borrower must be 100% owner of the business."]
[null,"3) Input Statement Ending Date for each month reviewed. "]
[null,"4) Input Total Monthly Deposits from each statement"]
[null,"5) Identify and Input the number of NSF or Overdrafts reported on each statement"]
[null,"6) Input any amounts related to deposits that are not part of the self employed earnings that are being evaluated"]
[null,"7) Input any amounts pertaining to Business Expenses "]
[null,"8) The calculated Average Monthly Cash Flow will be displayed within the \"Results Analysis\""]
[null,"9) Input  Revenue data from Profit and Loss statement prepared by CPA or Licensed Tax preparer."]
[null,"10) Compare the calculated Average Monthly Cash Flow versus the Revenue data from the P&L to ensure within 5%."]
[]
[]
["Comments"]

{"sheet":"Val","index":2,"n_rows":20,"n_cols":16,"rows_kept":20,"hidden":true,"merged":[[12,1,12,4],[13,1,14,2],[15,1,16,2],[15,3,15,4],[16,3,16,4],[17,1,17,2],[17,3,17,4]]}
[{"$nulls":12},"Option 1 - Fixed Expense Ratio",1,"Select Biz Type","Select FTE/contractor headcount"]
["Service Business","0",{"$nulls":10},"Option 2 - 3rd Party Expense Statement",3,{"$f":"\"\"","v":""},"Enter CPA/ Certified Tax Preparer Expense Ratio"]
["Product Business","1-10"]
[null,">10"]
[]
["Ascending"]
["Descending",{"$nulls":10},"curr opt","Option 2 - 3rd Party Expense Statement"]
[]
[]
[]
[]
[]
[]
[null,{"$f":"IF('Business Bk Stmt Calculator'!L6<'Business Bk Stmt Calculator'!T7*(1-'Business Bk Stmt Calculator'!T11),\"Use Income from 1003\",\"Qualifying Income Using the Fixed Expense Ratio\")","v":"Qualifying Income Using the Fixed Expense Ratio"}]
[{"$nulls":4},{"$f":"IF('Business Bk Stmt Calculator'!L6<'Business Bk Stmt Calculator'!T7*(1-'Business Bk Stmt Calculator'!T11),'Business Bk Stmt Calculator'!L6,'Business Bk Stmt Calculator'!T7*(1-'Business Bk Stmt Calculator'!T11))","v":{"v":50000,"d":"$50,000"}}]
[null,{"$f":"IF('Business Bk Stmt Calculator'!L6<'Business Bk Stmt Calculator'!T7*(1-'Business Bk Stmt Calculator'!T11),\"Use Income from 1003\",\"Qualifying Income Using the CPA/Tax-Preparer Expense Ratio\")","v":"Qualifying Income Using the CPA/Tax-Preparer Expense Ratio"}]
[]
[]
[]
[null,{"$f":"IF(ABS(('Business Bk Stmt Calculator'!T9-'Business Bk Stmt Calculator'!T7)/'Business Bk Stmt Calculator'!T7)>=10%,\"P/L Revenue and Bank Statement deposits vary by more than 10%, cannot use this Option\",IF('Business Bk Stmt Calculator'!T9*(1-'Business Bk Stmt Calculator'!T11)<'Business Bk Stmt Calculator'!L6,\"Qualifying Monthly Income using Borrower Prepared P/L\",\"Use Income from 1003\"))","v":{"$error":"#VALUE!"}},null,null,{"$f":"IF(ABS(('Business Bk Stmt Calculator'!T9-'Business Bk Stmt Calculator'!T7)/'Business Bk Stmt Calculator'!T7)>=10%,\"N/A\",MIN('Business Bk Stmt Calculator'!L6,IF('Business Bk Stmt Calculator'!T9<'Business Bk Stmt Calculator'!T7,'Business Bk Stmt Calculator'!T9*(1-'Business Bk Stmt Calculator'!T11),'Business Bk Stmt Calculator'!T7*(1-'Business Bk Stmt Calculator'!T11))))","v":{"$error":"#VALUE!"}}]

{"sheet":"Covid Special Considerations","index":3,"n_rows":20,"n_cols":7,"rows_kept":20}
[null,"Months April 2020 or onward:",{"$f":"IF('Business Bk Stmt Calculator'!H6=24,COUNTIF('Business Bk Stmt Calculator'!B20:B43,\">3/31/2020\"),COUNTIF('Business Bk Stmt Calculator'!B20:B31,\">3/31/2020\"))","v":3},null,null,"Months Prior to April 2020",{"$f":"IF('Business Bk Stmt Calculator'!H6=24,COUNTIF('Business Bk Stmt Calculator'!B20:B43,\"<4/1/2020\"),COUNTIF('Business Bk Stmt Calculator'!B20:B31,\"<4/1/2020\"))","v":21}]
[]
[null,"Eligible Deposits, April 2020 or onward:",{"$f":"IF('Business Bk Stmt Calculator'!H6=24,SUMIF('Business Bk Stmt Calculator'!B20:B43,\">3/31/2020\",'Business Bk Stmt Calculator'!T20:T43),SUMIF('Business Bk Stmt Calculator'!B20:B31,\">3/31/2020\",'Business Bk Stmt Calculator'!T20:T31))","v":{"v":300000,"d":"300,000.00"}},null,null,"Eligible Deposits, Prior to April 2020",{"$f":"IF('Business Bk Stmt Calculator'!H6=24,SUMIF('Business Bk Stmt Calculator'!B20:B43,\"<4/1/2020\",'Business Bk Stmt Calculator'!T20:T43),SUMIF('Business Bk Stmt Calculator'!B20:B31,\"<4/1/2020\",'Business Bk Stmt Calculator'!T20:T31))","v":{"v":2100000,"d":"2,100,000.00"}}]
[]
[]
[null,"Average Monthly Income April 2020 Onward",{"$f":"C3/C1","v":{"v":100000,"d":"100,000.00"}},null,null,"Average Monthly Income Prior to April 2020",{"$f":"G3/G1","v":{"v":100000,"d":"100,000.00"}}]
[]
[null,"Average Monthly Income Change, April 2020 ",{"$f":"(C6-G6)/G6","v":{"v":0,"d":"0.00%"}}]
[]
[]
[]
[null,"Bank Statement Qualifiaction Guide Post Covid 2020"]
[]
["Review to determine Additional Restrictions:","Reduction in income","Expense ratio addition","Reduction in LTV","Additional reserves","LOE"]
[{"$f":"IF(AND($C$8<0,$C$8>=-0.05),\"This Row May Apply\",\" \")","v":" "},"≤ 5%","No Change","No Change","No Change","None"]
[{"$f":"IF(AND($C$8<-0.05,$C$8>=-0.1),\"This Row May Apply\",\" \")","v":" "},"≤ 10%",{"v":0.05,"d":"5%"}," 5% from limit","3 months","Steps taken to return to typical revenue"]
[{"$f":"IF(AND($C$8<-0.1,$C$8>=-0.2),\"This Row May Apply\",\" \")","v":" "},"≤ 20%",{"v":0.1,"d":"10%"},"10% from limit","6 months","Steps taken to return to typical revenue"]
[{"$f":"IF(AND($C$8<-0.2,$C$8>=-0.3),\"This Row May Apply\",\" \")","v":" "},"≤ 30%",{"v":0.15,"d":"15%"},"10% from limit","9 months","Steps taken to return to typical revenue and P&L is Required"]
[{"$f":"IF(AND($C$8<-0.3,$C$8>=-0.4),\"This Row May Apply\",\" \")","v":" "},"≤ 40%",{"v":0.2,"d":"20%"},"Case by Case","Case by Case","Steps taken to return to typical revenue and P&L is Required"]
[{"$f":"IF(C8<-0.400000001,\"These Restrictions May Apply\",\" \")","v":" "},"> 40%","Does not qualify","Does not qualify","Does not qualify","Does not qualify"]