{"text_format":"3","workbook":"xlsx","n_sheets":6,"sheets":["Business Bk Stmt Calculator","Deposits Not Included in Estima","Instructions","Val","Covid Special Considerations","Sheet1"],"not_rendered":{"images":1,"macros":1,"defined_names":3},"read":"whole","date_system":"1900"}

{"sheet":"Business Bk Stmt Calculator","index":0,"n_rows":55,"n_cols":33,"rows_kept":55,"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,14,11,17],[10,18,11,18],[10,19,11,19],[11,0,11,1],[11,2,11,3],[12,0,12,1],[12,2,12,3],[12,5,12,8],[12,9,12,10],[12,12,12,13],[12,14,12,18],[13,9,14,10],[13,11,14,11],[13,12,14,13],[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,0,43,16],[43,17,43,18],[44,0,47,16],[48,0,48,19],[49,0,49,19],[50,0,50,19],[51,0,51,1],[52,0,52,19],[53,0,53,19],[54,0,54,19],[61,0,61,1],[62,0,62,1],[63,0,63,1],[64,0,64,1]]}
[" Business Bank Statement Program Income Calculator (Includes Commingled Accounts***)",{"$nulls":31},{"$f":"IF(AND(F15<>\"\",J15=\"\"),1,\"\")","v":""}]
[{"$nulls":19},{"$f":"-1+(T7*T11)","v":{"$error":"#DIV/0!"}}]
[]
[{"$nulls":8},"Results Analysis"]
[{"$nulls":14},"Income Calculation"]
["Account Information",{"$nulls":4},"Select the  Number of Statements:",{"$nulls":3},"Enter 1003 Monthly Net Income:"]
["Name on Account ",{"$nulls":13},"Monthly Income from Bank Statement Deposits",{"$nulls":4},{"$f":"T44/H6","v":{"$error":"#DIV/0!"}}]
["Financial Institution "]
["Account Number:",{"$nulls":4},"Choose Business Bank Statement Method",{"$nulls":3},{"$f":"VLOOKUP($F$11,Val!$M$1:$P$3,3,0)","v":"Select Biz Type"},null,{"$f":"VLOOKUP($F$11,Val!$M$1:$P$3,4,0)","v":"Select FTE/contractor headcount"},null,null,"Monthly Gross Receipts from P/L",{"$nulls":4},{"$f":"IF(W11=2,J11/H6, \"N/A\")","v":"N/A"}]
["Borrower Name:"]
["Underwriter:",{"$nulls":4},"Option 1 - Fixed Expense Ratio",{"$nulls":3},"Product Business",null,">10",null,null,"Expense Ratio",{"$nulls":3},{"$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.7,"d":"70%"}},{"$f":"S11","v":{"v":0.7,"d":"70.0%"}},null,null,{"$f":"VLOOKUP($F$11,Val!$M$1:$P$3,2,0)","v":1},{"$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":0.7}]
["ARC Loan Number:",{"$nulls":22},{"$f":"IF(I13=\"DNQ\",\"DNQ\",IF(W11=1,IF(AND(J11=\"Product Business\",L11=\"0\"),20%,IF(AND(J11=\"Product Business\",L11=\"1-10\"),40%,IF(AND(J11=\"Product Business\",L11=\">10\"),60%,IF(L11=\"0\",30%,IF(L11=\"1-10\",50%,IF(L11=\">10\",70%,0%)))))),L11))","v":0.6}]
["Date",{"$nulls":4},"Enter Borrower's Percentage of Ownership",{"$nulls":8},{"$f":"IF(T13>T15,\"\",IF(I13=\"DNQ\",\"Does Not Qualfiy\",IF(W11=1,Val!B14,IF(W11=2,Val!B20,Val!B16))))","v":{"$error":"#DIV/0!"}},{"$nulls":4},{"$f":"IF(L6>T15,\"\",IF(I13=\"DNQ\",\"DNQ\",IF(W11=1,Val!E15,IF(W11=2,Val!E20,Val!E15))))","v":{"$error":"#DIV/0!"}},null,null,{"$f":"IF(J15=\"YES\",MIN(((1-T11)*(T7*J13)),L6))","v":false}]
[{"$nulls":14},{"$f":"\"Borrower's Percentage of Ownership\"","v":"Borrower's Percentage of Ownership"},{"$nulls":4},{"$f":"J13","v":{"v":0,"d":"0%"}},null,null,{"$f":"IF(AND(F11=\"Option 2 - 3rd Party Expense Statement\",L11=\"\"),1,0)","v":0}]
["Order of Bank Statements:",{"$nulls":13},{"$f":"IF(T15=T13,\"Qualifying Income\",\"Qualifying Income Based on Borrower's Percentage of Ownership\")","v":{"$error":"#DIV/0!"}},{"$nulls":4},{"$f":"MIN(L6,(((1-T11)*T7)*T14))","v":{"$error":"#DIV/0!"}}]
[]
[]
[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-05-31",{"$nulls":17},{"$f":"SUM(D20,-(SUM(E20:S20)))","v":{"v":0,"d":"$0"}}]
[2,{"$f":"IF($B$18=\"Ascending\",EDATE(B20,1),EDATE(B20,-1))","v":"2020-04-30"},{"$nulls":17},{"v":0,"d":"$0"}]
[3,{"$f":"IF($B$18=\"Ascending\",EDATE(B21,1),EDATE(B21,-1))","v":"2020-03-30"},{"$nulls":17},{"v":0,"d":"$0"}]
[4,"2020-02-29",{"$nulls":17},{"v":0,"d":"$0"}]
[5,"2020-01-29",{"$nulls":17},{"v":0,"d":"$0"}]
[6,"2019-12-29",{"$nulls":17},{"v":0,"d":"$0"}]
[7,"2019-11-29",{"$nulls":17},{"v":0,"d":"$0"}]
[8,"2019-10-29",{"$nulls":17},{"v":0,"d":"$0"}]
[9,"2019-09-29",{"$nulls":17},{"v":0,"d":"$0"}]
[10,"2019-08-29",{"$nulls":17},{"v":0,"d":"$0"}]
[11,"2019-07-29",{"$nulls":17},{"v":0,"d":"$0"}]
[12,"2019-06-29",{"$nulls":17},{"v":0,"d":"$0"}]
[13,"2019-05-29",{"$nulls":17},{"v":0,"d":"$0"}]
[14,"2019-04-29",{"$nulls":17},{"v":0,"d":"$0"}]
[15,"2019-03-29",{"$nulls":17},{"v":0,"d":"$0"}]
[16,"2019-02-28",{"$nulls":17},{"v":0,"d":"$0"}]
[17,"2019-01-28",{"$nulls":17},{"v":0,"d":"$0"}]
[18,"2018-12-28",{"$nulls":17},{"v":0,"d":"$0"}]
[19,"2018-11-28",{"$nulls":17},{"v":0,"d":"$0"}]
[20,"2018-10-28",{"$nulls":17},{"v":0,"d":"$0"}]
[21,"2018-09-28",{"$nulls":17},{"v":0,"d":"$0"}]
[22,"2018-08-28",{"$nulls":17},{"v":0,"d":"$0"}]
[23,"2018-07-28",{"$nulls":17},{"v":0,"d":"$0"}]
[24,"2018-06-28",{"$nulls":17},{"v":0,"d":"$0"}]
["NOTES:",{"$nulls":16},"Total Allowable Deposits:",null,{"$f":"IF(H6=6,SUM(T20:T25),IF(H6=12,SUM(T20:T31),IF(H6=24,SUM(T20:T43))))","v":false}]
[]
[]
[]
[]
["* NSFs:  No more than 6 allowed in a 12 month period. Protected overdrafts are not counted."]
["**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":"Deposits Not Included in Estima","index":1,"n_rows":2,"n_cols":10,"rows_kept":2}
["Deposits Not Included in Estimated Revenue"]
["Document","Page #","Account","Date","Description","Debit","Credit","Balance","Explanation","Comment"]

{"sheet":"Instructions","index":2,"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":3,"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 1 - Fixed Expense Ratio"]
[]
[]
[]
[]
[]
[]
[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":{"$error":"#DIV/0!"}}]
[{"$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":{"$error":"#DIV/0!"}}]
[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":{"$error":"#DIV/0!"}}]
[]
[]
[]
[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":4,"n_rows":20,"n_cols":7,"rows_kept":20,"hidden":true}
[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":2},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":10}]
[]
[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":0},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":0}]
[]
[]
[null,"Average Monthly Income April 2020 Onward",{"$f":"C3/C1","v":0},null,null,"Average Monthly Income Prior to April 2020",{"$f":"G3/G1","v":0}]
[]
[null,"Average Monthly Income Change, April 2020 ",{"$f":"(C6-G6)/G6","v":{"$error":"#DIV/0!"}},{"$f":"IFERROR(IF($E$8<=5%,0,IF(AND($E$8<=10%,$E$8>5%),5%,IF(AND($E$8<=20%,$E$8>10%),10%,IF(AND($E$8<=30%,$E$8>20%),15%,IF(AND($E$8<=40%,$E$8>30%),20%,IF($E$8>40%,\"DNQ\")))))),\"\")","v":""},{"$f":"C8*-1","v":{"$error":"#DIV/0!"}}]
[null,null,"LTV Reduction",{"$f":"IFERROR(IF(D8=5%,5%,IF(D8=10%,10%,IF(D8=15%,10%,IF(D8=20%,\"Case by Case\",IF(D8=\"DNQ\",\"DNQ\",0))))),\"\")","v":{"v":0,"d":"0%"}}]
[]
[]
[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":{"$error":"#DIV/0!"}},"≤ 5%","No Change","No Change","No Change","None"]
[{"$f":"IF(AND($C$8<-0.05,$C$8>=-0.1),\"This Row May Apply\",\" \")","v":{"$error":"#DIV/0!"}},"≤ 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":{"$error":"#DIV/0!"}},"≤ 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":{"$error":"#DIV/0!"}},"≤ 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":{"$error":"#DIV/0!"}},"≤ 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":{"$error":"#DIV/0!"}},"> 40%","Does not qualify","Does not qualify","Does not qualify","Does not qualify"]

{"sheet":"Sheet1","index":5,"n_rows":24,"n_cols":16,"rows_kept":24,"hidden":true}
[1]
[2]
[3]
[4]
[5]
[6]
[7]
[8]
[9]
[10,{"$nulls":6},-11507.358666666667,{"$nulls":4},{"$f":"'Business Bk Stmt Calculator'!T7","v":{"$error":"#DIV/0!"}},{"$f":"M10","v":{"$error":"#DIV/0!"}},null,100]
[11,{"$nulls":9},{"$f":"('Business Bk Stmt Calculator'!T7*'Business Bk Stmt Calculator'!T11)*'Business Bk Stmt Calculator'!J13","v":{"$error":"#DIV/0!"}},null,{"$f":"M10*(1-'Business Bk Stmt Calculator'!T11)","v":{"$error":"#DIV/0!"}},{"$f":"N10*0.45","v":{"$error":"#DIV/0!"}},null,{"$f":"P10-55","v":45}]
[12,{"$nulls":6},11507.358666666667,{"$nulls":4},{"$f":"M11*'Business Bk Stmt Calculator'!T14","v":{"$error":"#DIV/0!"}},{"$f":"N11*0.49","v":{"$error":"#DIV/0!"}}]
[13]
[14]
[15]
[16]
[17]
[18]
[19]
[20]
[21]
[22]
[23]
[24]