{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Job Aid ","UPDATES","Wage Earner Income Calculator","Variable Pay","Withholdings Validation","2106 Worksheet","Income Red Flag Job Aid"],"not_rendered":{"charts":0,"images":3,"macros":1,"external_links":2,"defined_names":7,"comments":5,"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":"Job Aid ","index":0,"n_rows":70,"n_cols":49,"rows_kept":70,"cut":false,"hidden":true,"merged":[[69,1,69,16],[64,4,64,16],[66,2,66,3],[66,9,66,11],[66,12,66,16],[62,5,62,7],[62,8,62,12],[62,13,62,14],[68,2,68,3],[68,4,68,16],[62,15,62,16],[57,4,57,16],[58,4,58,7],[58,8,58,11],[58,12,58,16],[46,1,47,1],[47,2,47,16],[48,2,49,3],[48,4,49,4],[48,5,49,7],[54,9,55,12],[54,13,55,16],[56,4,56,7],[56,9,56,12],[56,13,56,16],[46,2,46,16],[52,8,52,11],[52,12,52,16],[53,2,53,3],[53,9,53,12],[53,13,53,16],[44,14,44,16],[48,8,49,8],[0,1,0,16],[2,3,3,7],[2,1,3,2],[1,10,1,14],[1,15,3,16],[1,8,1,9],[15,14,15,16],[39,1,41,1],[39,2,39,13],[41,2,41,16],[32,3,32,4],[33,2,33,3],[21,5,21,8],[20,5,20,8],[30,3,30,4],[25,2,25,16],[48,9,49,10],[48,11,49,12],[48,13,49,16],[1,4,1,7],[2,8,3,9],[2,10,3,14],[13,1,14,1],[34,2,34,4],[23,3,24,4],[31,9,31,13],[32,9,32,13],[15,5,15,8],[26,2,26,4],[27,9,27,13],[26,14,26,16],[26,9,26,13],[27,5,27,8],[15,9,15,13],[16,9,17,13],[21,2,21,4],[22,5,22,8],[23,5,23,8],[18,9,23,16],[22,2,22,3],[20,2,20,4],[18,5,18,8],[19,5,19,8],[19,2,19,4],[18,2,18,4],[24,14,24,16],[9,12,9,13],[9,10,9,11],[9,14,9,16],[9,7,9,9],[4,1,6,16],[7,1,7,7],[7,8,7,10],[7,11,7,16],[27,2,27,4],[8,1,8,16],[9,4,9,6],[9,1,10,1],[10,4,10,6],[10,10,10,11],[9,2,10,3],[10,7,10,9],[11,7,11,8],[11,9,11,12],[10,12,10,13],[16,14,17,16],[17,5,17,8],[10,14,10,16],[12,2,12,16],[15,2,15,4],[11,2,11,3],[17,3,17,4],[16,2,17,2],[11,4,11,6],[14,2,14,16],[16,3,16,4],[16,5,16,8],[13,2,13,16],[11,13,11,16],[24,5,24,13],[28,3,28,4],[29,3,29,4],[50,11,50,12],[50,13,50,16],[36,5,36,8],[37,5,37,8],[38,2,38,13],[37,3,37,4],[28,2,29,2],[29,5,29,8],[43,9,43,13],[42,5,42,8],[40,2,40,16],[42,9,42,13],[42,14,42,16],[43,5,43,8],[43,14,43,16],[36,2,36,4],[36,9,36,13],[35,2,35,4],[34,5,34,8],[31,5,31,8],[28,9,29,13],[61,2,61,3],[61,9,61,12],[61,13,61,16],[62,2,62,4],[61,5,61,7],[67,4,67,7],[67,8,67,10],[63,2,63,3],[63,5,63,8],[63,9,63,10],[66,4,66,7],[63,11,63,14],[64,2,64,3],[65,2,65,3],[65,4,65,7],[65,12,65,16],[65,9,65,11],[63,15,63,16],[60,5,60,7],[50,5,50,7],[50,9,50,10],[43,2,43,4],[42,2,42,4],[58,2,58,3],[57,2,57,3],[53,5,53,7],[50,2,50,3],[52,2,52,3],[51,2,51,3],[51,4,51,16],[52,4,52,7],[54,2,56,3],[54,4,55,7],[54,8,56,8],[59,4,59,16],[60,2,60,3],[59,2,59,3],[60,13,60,16],[60,9,60,12],[44,2,44,3],[44,5,44,8],[44,9,44,13],[27,14,27,16],[26,5,26,8],[28,5,28,8],[28,14,39,16],[37,9,37,13],[35,5,35,8],[35,9,35,13],[30,5,30,8],[30,9,30,13],[32,5,32,8],[33,5,33,8],[34,9,34,13],[33,9,33,13]]}
[null,"WAGE EARNER CALCULATOR WORKSHEET",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null," "]
[null,"DATE",{"$f":"TODAY()","v":"2020-01-31"},"Loan #",null,null,null,null,"Completed By:",null,null,null,null,null,null,"1900-01-01"]
[null,"Applicant Name:",null,null,null,null,null,null,"Employer Name:",null,null,null,null,null,null,null,null," "]
[]
[null,"THIS IS A TOOL AND DOES NOT REPLACE ASSOCIATE ACCOUNTABILITY FOR ACCURATE DETERMINATION AND DOCUMENTATION OF QUALIFYING INCOME. \nQualifying Income Reminders - Refer to the policy guide for detailed income policy requirements. \n* Always compare the income documentation information to the 1003 information\n* All income used for qualification must be stable with a two year history and reasonably expected to continue for the next 3 years. \n* For income to be considered stable and likely to continue - it must be supported by a two year history and current earnings that show a consistent and/or \n  increasing level of monthly income. \n* Documentation is required in the loan file to support verification of income sources, income calculations and the determination of stable income. ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Frequency?",{"$f":"IF(AND(0<K11,K11<2.99),(1),(0))","v":0},"Frequency?",null,{"$f":"MONTH(H11)","v":{"$error":"#VALUE!"}},{"$f":"DAY(H11)","v":{"$error":"#VALUE!"}},{"$f":"YEAR(H11)","v":{"$error":"#VALUE!"}},null,{"$f":"MONTH(#REF!)","v":{"$error":"#REF!"}},{"$f":"DAY(#REF!)","v":{"$error":"#REF!"}},{"$f":"YEAR(#REF!)","v":{"$error":"#REF!"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Annually",{"$f":"IF(AND(2.9999<K11,K11<5.99),(2),(0))","v":0},"12 Months",null,{"$f":"IF(AO5=1,(31),IF(AO5=2,(28),IF(AO5=3,(31),IF(AO5=4,(30),IF(AO5=5,(31),IF(AO5=6,(30),IF(AO5=7,(31),IF(AO5=8,(31)))))))))","v":{"$error":"#VALUE!"}},null,null,null,{"$f":"IF(AS5=1,(31),IF(AS5=2,(28),IF(AS5=3,(31),IF(AS5=4,(30),IF(AS5=5,(31),IF(AS5=6,(30),IF(AS5=7,(31),IF(AS5=8,(31)))))))))","v":{"$error":"#REF!"}}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Monthly/Quarterly",{"$f":"IF(AND(6<K11,K11<8.99),(3),(0))","v":0},"11 Months",null,{"$f":"IF(AO5=9,(30),IF(AO5=10,(31),IF(AO5=11,(30),IF(AO5=12,(31)))))","v":{"$error":"#VALUE!"}},{"$f":"AO5-1","v":{"$error":"#VALUE!"}},null,null,{"$f":"IF(AS5=9,(30),IF(AS5=10,(31),IF(AS5=11,(30),IF(AS5=12,(31)))))","v":{"$error":"#REF!"}},{"$f":"AS5-1","v":{"$error":"#REF!"}},null,null," "]
[null,"Data entry is REQUIRED in ALL SECTIONS - ",null,null,null,null,null,null,"YELLOW FIELDS",null,null," - White fields auto-calculate.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Quarterly",{"$f":"IF(AND(9<K11,K11<12.1),(4),(0))","v":0},"10 Months",null,{"$f":"SUM(AO6:AO7)","v":{"$error":"#VALUE!"}},{"$f":"AP5/AO8","v":{"$error":"#VALUE!"}},null,null,{"$f":"SUM(AS6:AS7)","v":{"$error":"#REF!"}},{"$f":"AT5/AS8","v":{"$error":"#REF!"}},null,null," "]
[null,"To make data entry corrections always use the DELETE key.  Use arrow keys to move from cell to cell - ensure Scroll Lock is not on.  \nWhen entering comments, use the ALT and ENTER key together to start new row. ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"9 Months",null,null,{"$f":"ROUND(AP8,2)","v":{"$error":"#VALUE!"}}]
[null,1,"Current Paystub \nYTD Earnings Calculation \n(paystub must have YTD gross earnings and withholdings or a written VOE is required)",null,"Current  Employer \nYTD Gross Pay",null,null,"Pay Period Ending Date\nMUST BE Most Recent 30 Days\nMM/DD/YY",null,null,"# of Months",null,"Monthly Average Income",null,"Has the applicant worked for \nthis employer since January of this year?  ",null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(AL5:AL8)","v":0},null,null,null,{"$f":"SUM(AP7+AP9)","v":{"$error":"#VALUE!"}},null,null,null,{"$f":"SUM(AT7:AT8)","v":{"$error":"#REF!"}},null,null," "]
[null,null,null,null," ",null,null," ",null,null," ",null,{"$f":"IF(ISERROR(E11/K11),0,(E11/K11))","v":0},null,null,null,null," ",null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"prev emp monthly",null,null,null,{"$f":"H11-#REF!","v":{"$error":"#VALUE!"}},{"$f":"AO11/30.5","v":{"$error":"#VALUE!"}},{"$f":"AP11-AP9","v":{"$error":"#VALUE!"}}]
[null,null,"CURRENT Employer Start Date \nREQUIRED\nMM/DD/YY",null,"Prior Employer(s)\nYTD Earnings",null,null,"Prior  Employer(s)\n# Months Worked \nthis year",null,"Monthly Average Income Prior  Employer(s)",null,null,null,"LIST HERE\nEmployer Name(s)\n",null,null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"E13/#REF!","v":{"$error":"#REF!"}},null,null,null,{"$f":"COUNTA(#REF!)","v":1},"ROUNDED 100TH",null,{"$f":"H11","v":" "},null,null,null,null,{"$f":"AO5-AS5","v":{"$error":"#VALUE!"}},{"$f":"AP5-AT5","v":{"$error":"#VALUE!"}}]
[null,null,"KEY POINTS:  1) Keep in mind the overall policy guidance at the top - \"Qualifying Income Reminders\".  2) Never enter a pay stub into this tool that is hand written or does not have a YTD Gross Income total - refer to policy to obtain acceptable documentation.  3) Ensure the paystub is dated within the most recent 30 days and is not considered an expired document at time of review, otherwise an updated paystub may be needed.  Also, ensure the document will not be at risk of being over 90 days at closing - otherwise request an updated document to avoid closing issues.  4) Be sure to answer the \"Has the applicant worked for current employer since Jan\" and complete the additional required information in cases where there are prior employers.  This is important to ensure the current year earnings and employment history is reflecting the entire year and not just the current employer.  5) When there are multiple employers briefly comment in the \"List Here\" to enter the prior employer name(s). ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(AL12=1,(AP11),IF(AL12=0,(AP10)))","v":{"$error":"#VALUE!"}},{"$f":"(ROUND(AL13,2))","v":{"$error":"#VALUE!"}},null,null,null,null,null,null,null,{"$f":"AU12/AO8+AT12","v":{"$error":"#VALUE!"}}]
[null,2,"CURRENT EMPLOYER BASE or SCHEDULED HOURLY PAY    \n* TEACHERS - determine if they have a 9,10 or 12 month pay cycle - select appropriate frequency on line 23 to accurately calculate teacher income.",null,null,null,null,null,null,null,null,null,null,null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"ytd total income","months worked"]
[null,null,"Enter Base Pay Info From Current Paystub - Salary, Part-time, Hourly Income From CURRENT employer",null,null,null,null,null,null,null,null,null,null,null,null,null,null," ",null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"E13+E11","v":{"$error":"#VALUE!"}},{"$f":"AP21+H13","v":{"$error":"#VALUE!"}}]
[null,null,"Pay Period Frequency",null,null,"BASE",null,null,null,"YEARLY",null,null,null,null,"MONTHLY",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"SEE CELL BELOW"]
[null,"Guidance"," "," ",null,null,null,null,null," ",null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF((AM15=0),\" \")","v":{"$error":"#VALUE!"}},null,{"$f":"MONTH(H11)","v":{"$error":"#VALUE!"}},{"$f":"DAY(H11)","v":{"$error":"#VALUE!"}},{"$f":"YEAR(H11)","v":{"$error":"#VALUE!"}}]
[null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(AO5=1,(31),IF(AO5=2,(28),IF(AO5=3,(31),IF(AO5=4,(30),IF(AO5=5,(31),IF(AO5=6,(30),IF(AO5=7,(31),IF(AO5=8,(31)))))))))","v":{"$error":"#VALUE!"}}]
[null,null,"Weekly:",null,null,null,null,null,null,"KEY POINTS:  1) This section mirrors the policy guide.  2) Carefully review the pay stub  to determine base pay frequency. 3) TEACHER Pay - determine pay frequency 9,10,11,12 mos - select from FREQUENCYdrop down - critical for accurate calculations.  4) OTHER - This allows for other pay options that may not be covered above.  ",null,null,null,null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(AO5=9,(30),IF(AO5=10,(31),IF(AO5=11,(30),IF(AO5=12,(31)))))","v":{"$error":"#VALUE!"}},{"$f":"AO5-1","v":{"$error":"#VALUE!"}}]
[null,null,"Bi-Weekly (80 Hours):",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(AO6:AO7)","v":{"$error":"#VALUE!"}},{"$f":"AP5/AO8","v":{"$error":"#VALUE!"}}]
[null,null,"Semi-Monthly (86.67 Hours):",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"(ROUND(AL13,2))","v":{"$error":"#VALUE!"}}]
[null,null,"Monthly:"]
[null,null,"Teacher Pay:",null,"Frequency?"]
[null,null,"Other Frequency:","Manually calculate and enter figures in yellow fields to the right.  Explain variable pay type, frequency and calculations in Section D below. "]
[null,null," ",null,null,"Total Base Pay",null,null,null,null,null,null,null,null,{"$f":"SUM(O17:Q24)","v":0}]
[null,3,"CURRENT EMPLOYER VARIABLE PAY and VARIABLE HOURLY PAY\nOVERTIME / INCENTIVE / BONUS / COMMISSION / OTHER  VARIABLE  Documented 2 year history required, must be stable and likely to continue for 3+ years"]
[null,null,"Current Paystub",null,null,"YTD Gross",null,null,null,"# of Months Worked  OR  Frequency",null,null,null,null,"Monthly Avg"]
[null,null,"Hourly Pay:",null,null,null,null,null,null,{"$f":"IF(F28=0,\" \",K11)","v":" "},null,null,null,null,{"$f":"IF(ISERROR(F28/J28),0,F28/J28)","v":0}]
[null,"Guidance","Hourly Worker with Scheduled Hours","Hourly Rate:",null,null,null,null,null,{"$f":"IF(F29=0,\" \",(F29*F30*52))","v":" "},null,null,null,null,"VARIABLE PAY KEY POINTS:  1) Variable pay requires a 2 year history and 3+ years continuance, stable to increasing trend.  2) Enter YTD info from paystub into applicable variable pay section.  3) FREQUENCY - Make a selection of either annual or monthly/quarterly to calculate correctly.  If unsure of frequency always choose Annual as that is most conservative. -4)  HOURLY pay - two ways to enter either using scheduled hours or the YTD hourly earnings. 5) OTHER variable - this should be rare.  6) Refer to GUIDANCE for additional information. 7)  Always review policy for detailed requirements. "]
[null,null,null,"Hours Per Week:"]
[null,"Guidance","Hourly Worker With Variable Hours","YTD Hourly Pay",null,null,null,null,null,{"$f":"IF(F31=0,\" \",K11)","v":" "}]
[null,"Guidance"," ",null,"Overtime:",null,null,null,null,{"$f":"IF(F32=0,\" \",K11)","v":" "}]
[null,"Guidance"," ","Bonus/Incentive:",null,null,null,null,null,"Frequency?",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,12]
[null,"Guidance"," ",null," Commission:",null,null,null,null,"Frequency?"]
[null,"Guidance","Shift Differential:",null,null,null,null,null,null,{"$f":"IF(F35=0,\" \",K11)","v":" "}]
[null,"Guidance","Hourly Worker With Variable Hours",null,null,null,null,null,null,{"$f":"IF(F36=0,\" \",K11)","v":" "},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF((AM34=0),\" \")","v":" "},null,{"$f":"MONTH(H27)","v":1},{"$f":"DAY(H27)","v":0},{"$f":"YEAR(H27)","v":1900}]
[null,"Guidance","Auto Allowance:",null,null,null,null,null,null,{"$f":"IF(F37=0,\" \",K11)","v":" "}]
[null,"Guidance","Other Variable Pay:","Describe",null,null,null,null,null,"Frequency?"]
[null,null,"Total Variable Pay"]
[null,4,"W-2 KEY POINTS:  1) Always use Box 5 for total earnings figure. 2)  Be aware of figures in Box 12. Review the code to determine earnings type - some may not be eligible for qualified income.  For example V code is for stock options which are not an acceptable source of income and should be excluded from W-2 wages entered into this tool. Any questions refer to proper level of authority. 3) If there were multiple employers in the prior year(s), combine the W-2 totals into one for each year.  Totals will be averaged for a comprehensive view of the applicants total earnings history. 4) Refer to Guidance section for important W-2  reminders. 5) Select the button that applies to the years of W-2 for the loan - this will update all the year fields accordingly.  Supports 07/08 or 08/09. "]
[null,null," W-2 EARNINGS                     Click the button for the  W-2 years provided:                                        OR      "]
[null,null,"Enter figure from Box 5. Be aware of figures in Box 12 with Code V - this represents stock options and should be deducted from the amount in Box 5 as it is not considered qualifying income.   If multiple employers during year, combine W-2 earnings and # o"]
[null,"Guidance","W-2 Total Earnings",null,null,"AMOUNT",null,null,null,"# MONTHS ",null,null,null,null,"MONTHLY"]
[null,null,"2008 W-2",null,null," ",null,null,null,null,null,null,null,null,{"$f":"IF(ISERROR(F44/J44),0,F44/J44)","v":0}]
[null,null,null,null,"2007 W-2"," ",null,null,null,null,null,null,null,null,{"$f":"IF(ISERROR(F45/J45),0,F45/J45)","v":0}]
[]
[null,5,"QUALIFYING INCOME ANALYSIS SUMMARY"]
[null,null,"Ensure all data entry is complete and accurate."]
[null,null,"A.  JOB HISTORY",null,"2007\n# Months Worked","2008\n# Months Worked",null,null,"YTD Months Worked","YTD Months Available\nTo Work",null,"Total Work History",null,"Potential\nEmployment Gap/Discrepancy\n(address gaps of more than 1 month)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"dual income"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null," ",null,null,"GAP current year",{"$f":"I51-J51","v":{"$error":"#VALUE!"}},null,null,null,{"$f":"SUM(E51:I51)","v":0},null,{"$f":"IF(H13=0,(0),IF(H13>0,(1)))","v":0}]
[null,"Guidance","Review employment history and address any gaps",null,{"$f":"J45","v":0},{"$f":"J44","v":0},null,null,0,{"$f":"AP10","v":{"$error":"#VALUE!"}},null,0,null,{"$f":"IF(AE53=0,0,AF52)","v":{"$error":"#VALUE!"}},null,null,null,null,null,null,null,null,null,null,null,null," ",null,null,null," ","Employment gap",null,null,{"$f":"24+J51","v":{"$error":"#VALUE!"}},null,{"$f":"K11+H13","v":{"$error":"#VALUE!"}}]
[null,null,"Job History Comments",null,"KEY POINTS:  1) This section represents job history information to ensure employment gaps greater than 1 month are addressed.  2) Potential gaps appear as a NEGATIVE number.  A POSITIVE number indicates a discrepancy - applicant worked more months than available to work.  Gaps must be addressed -via comments in this space. Be sure the gap is not due to a data entry error.  If there is not a valid gap provide an explanation so anyone reviewing the file can understand how that determination was made. 3) Review GUIDANCE. 4) Comments are always required in this section - even if there are no gaps comments should be make acknowledging the review was complete and no gaps identified. 5) Comments are required. ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"07/08 history GAP",{"$f":"SUM(E51-12)+SUM(F51-12)","v":-24},{"$f":"AE52+AE50","v":{"$error":"#VALUE!"}},null,null,{"$f":"AI50-AI51","v":{"$error":"#VALUE!"}}]
[null,"Guidance","C. Current Employer\n     Base Pay  ",null,{"$f":"O19","v":0},null,null,null,"ü  Review Withholdings Validation       ü  Review pay stub for debts",null,null,null,{"$f":"IF(E53>N48,\"ALERT - Base Pay is higher than current employer monthly average - must be addressed.\",\"\")","v":""},null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"E51+F51+J51","v":{"$error":"#VALUE!"}}]
[null,null,"Comparison of Historical Avg\n to Current Avg",null,{"$f":"IF(ISERROR(O45),\"0\",O45)","v":0},{"$f":"IF(ISERROR(O44),\"0\",O44)","v":0},null,null,{"$f":"IF(E54=0,(0),IF(F54<E54,(F54),AVERAGE(E54:H54)))","v":0},{"$f":"IF(E13=\"\",N54,SUM(E11,E13)/J51)","v":0},null,null,null,{"$f":"M11","v":0},null,null,null," ",null,null,null,null,null,null,null,null,null,null," ","H48","K48","D51","H52"]
[null,null,"% Change In Income\n Year Over Year \nAddress Significant Increases/Decreases",null,"% Change From \n2007 to 2008",null,null,null," ","% Change From \n2008 to YTD Avg",null,null,null,"% Change from 2008 to Current Employer Avg ",null,null,null," ",null," "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null," ",null," "]
[null,null,null,null,{"$f":"IF(ISERROR(F54/E54),\" \",(SUM(F54-E54)/E54))","v":" "},null,null,null,null,{"$f":"IF(ISERROR(J54/F54),\" \",(SUM(J54-F54)/F54))","v":" "},null,null,null,{"$f":"IF(ISERROR(N54/F54),\" \",(N54-F54)/F54)","v":" "},null,null,null,null,null," "]
[null,null,"Income Trend Comments",null,"KEY POINTS: 1) Review the income history by comparing the prior years earnings to current YTD earnings for earnings consistency and stability. The calculator will alert you to anything that is >10 %. which may  require an explanation and supporting documentation as appropriate. Although the calculator will not apply a \"red alert\" to increases/decreases less than 10% it still may be necessary to address lower levels of change -  if they are significant for a particular transaction.  Consider the overall applicant profile, reasonableness and amount of change when determining if further action is warranted. 2) Comment in this section are required to acknowledged you reviewed this information and to document your determination.  It could be as simple as \"Earnings history is stable and supports an increasing trend\" or more detailed, complex based on the particular situation.  Comments are required. \n",null,null,null,null,null,null,null,null,null,null,null,null,null,null," "]
[null,"Guidance","C. Current Employer\n     Base Pay  ",null,{"$f":"O25","v":0},null,null,null,"Review Withholdings Validation\nReview pay stub for debts",null,null,null," ALERT - Base Pay is higher than current employer monthly average - must be addressed.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total YTD Variable",null,{"$f":"E11+E13","v":{"$error":"#VALUE!"}},{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"I62","v":0},{"$f":"J62","v":0},{"$f":"E67","v":"$0.00"},{"$f":"#REF!","v":{"$error":"#REF!"}}]
[null,null,"Base Income Comments",null,"KEY POINTS: 1)Reminder at this point to compare the current paystub withholdings to the Withholdings Validation tab - it pre-calculates based on the information entered in this spreadsheet. Refer to the Guidance feature on the Withholdings Tab for direction on how to handle discrepancies. 2) Review for any red flags on the documentation - refer to Red Flag Job Aid on the following tab.  3) When reviewing base pay always consider reasonableness based on applicant profile, field, experience, overall risk of transaction. 4) Comments in this section should be something like: \"Base pay is supported by current paystub and is considered reasonable based on applicant profile, field, and experience.  Withholdings validation completed and no red flags detected.\"  Just brief but addresses all key factors.  5) There may be an ALERT MESSAGE - the calculator compares current base pay to YTD Gross monthly average for consistency - if the base pay is higher than the monthly average gross earnings - this warrants an explanation as this is a red flag. \n6) Reminder to double check paystub for are any debts that need to be addressed. 7) Comments Required. ",null,null,null,null,null,null,null,null,null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,{"$f":"O25*(K11+H13)","v":{"$error":"#VALUE!"}}]
[null,null,"D.  VARIABLE PAY HISTORY\n      and CURRENT \n     YTD  VARIABLE",null,2007,2008,null,null,"2 YR Monthly Avg","Total YTD Avg \n(Total current year employer(s) income averaged by YTD total months)",null,null,null,"CURRENT EMPLOYER \nActual Variable YTD Avg",null,null,null,null,null," ",null,null,null,null,null,null,null,null,null,{"$f":"SUM(AD59-AD60)/(K11+H13)","v":{"$error":"#VALUE!"}}]
[null,"Guidance","Comparison of Average Monthly Variable  Income",null,{"$f":"IF(AF64<=0,0,IF(AF64=0,0,AF64))","v":0},{"$f":"IF(AE64<=0,0,IF(AE64=0,0,AE64))","v":0},null,null,{"$f":"IF(E62=0,(0),IF(F62<E62,(F62),AVERAGE(E62:H62)))","v":0},{"$f":"IF(ISERROR(AE61),\"$0.00\",AE61)","v":0},null,null,null,{"$f":"O39","v":0},null,null,null,null,null," ",null,null,null,null,null,null,null,{"$f":"IF(ISERROR(SUM(E11,E13)/SUM(K11,H13)-E59),\"N/A\",(SUM(E11,E13)/SUM(K11,H13)-E59))","v":"N/A"},null,null,"2008 Variable Pay","2008 Variable Pay",null,null,null,null,{"$f":"OR(E62=\"0\",F62=\"0\")*0","v":0}," "]
[null,"Guidance"," Prior 2 Years Plus Current YTD Average Variable Earnings\n(Not for standard use -  only if fully justified and supported)",null,null,{"$f":"AVERAGE(E62,F62,N62)","v":0},null,null,"Prior Year Plus Current YTD Average Variable Earnings\n(Not for standard use -  only if fully justified and supported)",null,null,null,null,{"$f":"AVERAGE(F62,N62)","v":0},null," "]
[null,"Guidance"," ",null," "," Commission % of Current YTD Earnings",null,null,null,{"$f":"IF(ISERROR(F35/E12),0,(F35/E12))","v":0},null,"Bonus % of Current YTD Earnings",null,null,null,{"$f":"IF(ISERROR(F34/E12),0,(F34/E12))","v":0},null," ",null,null," ",null,null,null,null,null,null,null,null,"Annual Base Pay",{"$f":"O25*12","v":0},{"$f":"O25*12","v":0}]
[null,null,"Variable Income Comments",null,"KEY POINTS 1) The Variable Pay History and Current YTD Variable section are based on current policy.  2) It provides a two year history of variable pay and the current level of variable pay to determine if there is the required two year history and stable to increasing earnings levels. 3)  There may be times where more documentation is required such as declining income, red flags or other factors. Refer to policy for detailed requirements. 4) The two year average is calculated based on the most conservative number.  Refer to GUIDANCE for more detailed information  5) The Total YTD Avg field combines all variable pay for the year if there were multiple employers.  If only one employer the Total YTD Avg  and the Current Employer Variable will be the same figure.  The figures will appear red if there is a gap in history or a declining trend or the current variable is lower.  6) On Row 62 aditional caclculations are completed which are not for standard use - only use if fully justified and supported. 7) This section will also calculate the % of total earnings by variable pay type for categories with policy requirements related to these percentages. Refer to GUIDANCE and policy guide for additional \ndetails. 8) Refer to policy for detailed requirements.9) Comments Required. ",null,null,null,null,null,null,null,null,null,null,null,null,null,null," ",null,null,null,null,null,null,null,{"$f":"IF(AB62=\"N/A\",\"$0.00\",IF(E13=0,N62,IF(AB62<=0,\"$0.00\",AB62)))","v":"$0.00"},null,"Variable",{"$f":"(F44-AE64)/12","v":{"$error":"#VALUE!"}},{"$f":"(F45-AF64)/12","v":{"$error":"#VALUE!"}}]
[null,"Guidance","ELIGIBLE BASE PAY\n(Always qualify with base pay only - unless variable pay is\nneeded for qualification.)",null,"ELIGIBLE VARIABLE PAY \n(Amount that meets history and stability considerations - Use only if needed for qualification.  LESS 2106 expenses) ",null,null,null,"2106 Net Monthly Expense Adjustment"," TOTAL ELIGIBLE \nMONTHLY INCOME",null,null,"ENTER VALIDATED QUALIFYING INCOME\n(File must have required  comments and documentation to support all income sources used for qualification)"]
[null,null,{"$f":"E59","v":0},null,{"$f":"IF(I62=0,\"$0.00\",MIN(I62,J62,N62))","v":"$0.00"},null,null,null,{"$f":"IF('[1]2106 Worksheet'!Q11<=0,'[1]2106 Worksheet'!Q11,\"\")","v":0},{"$f":"C67+E67+I67","v":0}]
[null,null,null,null,{"$f":"IF(I62=0,\"NONE\",MIN(I62,N62)+E59)","v":"NONE"}]
[null,"Guidance","Qualifying Income Supporting Comments \n\nLoan File Must Have Supporting Documentation",null,"KEY POINTS: 1) The mathematically eligible base pay and variable pay figures will be carried down here to the ELIGIBLE BASE PAY and ELIGIBLE VARIABLE PAY fields. 2)  It will also calculate a TOTAL ELIGIBLE INCOME combining the two figures LESS any 2106 Expenses entered on the 2106 Tab. 3) THESE FIGURES ARE NOT TO BE USED WITHOUT COMPLETING ALL OF THE ANALYSIS AND REVIEW OF THE PRIOR SECTIONS.  4) The associate needs to independently validate what income meets policy for VALIDATED QUALIFYING INCOME and enter that figure in the yellow cell above.  That is the final step of the calculator.  5) The associate makes the final determination of what will be the qualifying income - the tool took the associate through a methodical thought process to help arrive at that determination. 6) The associate may determine a different amount based on other factors the calculator is not aware of.  Full supporting comments and documentation are required to support the qualifying income figure entered. 7) Always refer to policy for detailed requirements. 8) Comments Required. "]
[null,"TRAINING NOTES: "]

{"sheet":"UPDATES","index":1,"n_rows":31,"n_cols":3,"rows_kept":31,"cut":false,"hidden":false,"merged":[[0,0,0,2]]}
["UPDATE LOG"]
["Date","Description","Author"]
["2013-02-04","Updated to add mileage for 2012. "]
["2013-04-05","Update all tabs to standard processes."]
["2013-07-19","Password Protect Fields","Michael Stalnaker"]
["2013-07-22","Correct Prior Employer Name Field in O13","Michael Stalnaker"]
["2013-07-22","Correct Formula and Format in Cell F62 and N62","Michael Stalnaker"]
["2013-10-26","Added Variable Pay Tab","Michael Stalnaker"]
["2013-10-26","Changed order of entry - YTD, 2012, 2011","Michael Stalnaker"]
["2013-10-26","Updated formulas to coincide with Order of Entry","Michael Stalnaker"]
["2013-11-11","Corrected YTD# of Months Formula on Variable Pay Tab to match Wage Earner","Michael Stalnaker"]
["2013-11-12","Added Annual Base Pay Input to Section 2, Row 23","Michael Stalnaker"]
["2013-11-12","Linked the # of Months in Section 4 on Wage Earner Tab to input dates on Variable Pay Tab","Michael Stalnaker"]
["2013-11-13","Corrected Totals on Variable Pay Tab - Cells F18, H18 & J18","Michael Stalnaker"]
["2013-12-12","Updated Mileage Depreciation rates for 2013","Michael Stalnaker"]
["2014-01-14","Corrected W2 Year Macros on Wage Earner Tab","Michael Stalnaker"]
["2014-01-15","Updated withholding % and maximum earnings on Withholdings Validation tab","Michael Stalnaker"]
["2014-01-27","Updated W2 Macros to shows years accurately","Michael Stalnaker"]
["2014-03-10","Updated Notes for Automobile Depreciation Expense on form 2106","Michael Stalnaker"]
["2014-05-19","Updated Mileage Depreciation rates on 2106","Michael Stalnaker"]
["2014-11-20","2106 Tab:  Updated Total Expense to read \" line 6 columns A plus B or line 21 of 1040 (which ever is greater)\"","Michael Stalnaker"]
["2015-01-09","Updated to 2015: All dates and Withholding Tab","Michael Stalnaker"]
["2015-04-13","Updated Mileage rates","Michael Stalnaker"]
["2016-01-04","Updated Mileage rates","Michael Stalnaker"]
["2016-01-04","Updated to 2016: All dates and Withholding Tab","Michael Stalnaker"]
["2016-01-21","Updating formula in 2106 and WEIC so that Manual Override is picked up correctly","Michael Stalnaker"]
["2017-01-05","Updated to 2017: All dates and Withholding Tab","Michael Stalnaker"]
["2017-11-13","Updated Mileage rates","Michael Stalnaker"]
["2018-01-08","Updated to 2018: All dates and Withholding Tab","Michael Stalnaker"]
["2019-01-10","Updated to 2019: All dates and Withholding Tab","Michael Stalnaker"]
["2020-01-06","Updated to 2020: All dates and Withholding Tab","Michael Stalnaker"]

{"sheet":"Wage Earner Income Calculator","index":2,"n_rows":69,"n_cols":49,"rows_kept":69,"cut":false,"hidden":false,"merged":[[46,1,47,1],[37,5,37,8],[36,5,36,8],[52,9,52,12],[52,13,52,16],[37,9,37,13],[40,1,41,1],[38,9,38,13],[44,5,44,8],[44,9,44,13],[43,9,43,13],[43,5,43,8],[48,5,49,7],[50,13,50,16],[50,5,50,7],[37,2,37,4],[39,2,39,4],[39,5,39,8],[39,9,39,13],[38,2,38,4],[33,9,33,13],[26,2,26,16],[29,2,29,4],[40,2,40,16],[30,3,30,4],[36,14,36,16],[34,9,34,13],[31,3,31,4],[28,2,28,4],[33,14,33,16],[28,14,28,16],[33,5,33,8],[32,2,32,4],[27,2,27,16],[30,5,30,6],[31,5,31,6],[30,7,30,8],[31,7,31,8],[30,9,30,10],[31,9,31,10],[30,15,30,16],[31,15,31,16],[30,11,30,13],[31,11,31,13],[53,5,53,7],[53,9,53,12],[50,11,50,12],[51,2,51,3],[56,9,56,12],[50,9,50,10],[50,2,50,3],[54,9,55,12],[43,2,43,4],[54,4,55,7],[56,4,56,7],[54,8,56,8],[53,2,53,3],[48,8,49,8],[48,2,49,3],[48,4,49,4],[52,5,52,7],[51,4,51,16],[53,13,53,16],[52,2,52,3],[47,17,47,20],[47,2,47,16],[44,14,44,16],[46,2,46,16],[34,3,34,4],[34,14,34,16],[35,5,35,8],[42,9,42,13],[35,2,35,3],[36,2,36,4],[36,9,36,13],[42,2,42,4],[38,5,38,8],[35,9,35,13],[34,5,34,8],[44,2,44,4],[60,13,60,16],[61,13,61,16],[61,2,61,3],[61,9,61,12],[60,9,60,12],[59,2,59,3],[57,2,57,3],[54,2,56,3],[57,4,57,16],[59,4,59,16],[54,13,55,16],[56,13,56,16],[58,2,58,3],[58,12,58,16],[58,4,58,7],[61,5,61,7],[60,5,60,7],[60,2,60,3],[58,8,58,11],[21,5,21,8],[24,9,24,13],[25,14,25,16],[25,5,25,13],[29,5,29,8],[21,9,21,13],[21,2,21,4],[18,14,18,16],[24,2,24,4],[25,2,25,4],[23,2,23,3],[22,2,22,4],[22,5,22,8],[22,9,22,13],[22,14,22,16],[24,5,24,8],[32,5,32,8],[19,9,19,13],[20,9,20,13],[14,2,14,16],[12,13,12,16],[17,3,17,4],[16,3,16,4],[19,5,19,8],[17,5,17,8],[15,5,15,8],[15,9,15,13],[15,14,15,16],[15,2,15,4],[12,4,12,6],[12,2,12,3],[19,14,19,16],[16,9,17,13],[18,2,18,4],[20,2,20,4],[19,2,19,4],[18,9,18,13],[18,5,18,8],[20,5,20,8],[20,14,20,16],[30,2,31,2],[13,1,14,1],[11,2,11,3],[9,7,9,9],[10,7,10,9],[11,4,11,6],[4,1,6,16],[7,1,7,7],[11,9,11,12],[16,2,17,2],[16,14,17,16],[16,5,16,8],[13,2,13,16],[12,9,12,12],[12,7,12,8],[29,14,29,16],[29,9,29,13],[28,5,28,8],[21,14,21,16],[23,14,23,16],[28,9,28,13],[24,14,24,16],[23,9,23,13],[23,5,23,8],[0,1,0,16],[2,3,3,7],[2,1,3,2],[1,10,1,14],[1,15,3,16],[1,8,1,9],[2,10,3,14],[11,13,11,16],[9,14,9,16],[8,1,8,16],[10,14,10,16],[9,1,10,1],[1,4,1,7],[2,8,3,9],[9,2,10,3],[10,10,10,11],[10,12,10,13],[11,7,11,8],[7,8,7,10],[7,11,7,16],[9,12,9,13],[9,10,9,11],[9,4,9,6],[10,4,10,6],[63,15,63,16],[67,2,67,3],[67,4,67,16],[65,12,65,16],[65,9,65,11],[66,2,66,3],[66,9,66,11],[66,12,66,16],[65,2,65,3],[63,5,63,8],[63,9,63,10],[66,4,66,7],[65,4,65,7],[68,1,68,2],[62,8,62,12],[62,13,62,14],[35,14,35,16],[32,14,32,16],[32,9,32,13],[43,1,44,1],[43,14,43,16],[38,14,38,16],[48,13,49,16],[48,11,49,12],[48,9,49,10],[37,14,37,16],[42,14,42,16],[42,5,42,8],[41,2,41,16],[39,14,39,16],[62,15,62,16],[64,2,64,3],[62,2,62,4],[62,5,62,7],[64,4,64,16],[63,2,63,3],[63,11,63,14]]}
[null,"WAGE EARNER CALCULATOR WORKSHEET\n\"PROPRIETARY TO MOUNTAIN WEST FINANCIAL - INTERNAL USE ONLY\"",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null," "]
[null,"DATE",{"$f":"TODAY()","v":"2020-01-31"},"Loan #",null,null,null,null,"Completed By:",null,null,null,null,null,null,"1900-01-01"]
[null,"Applicant Name:",null,null,null,null,null,null,"Employer Name:",null,null,null,null,null,null,null,null," "]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2019 Average",null,null,"Pystub Aging",{"$f":"C2-H11","v":43861},null,null,"Frequency?",{"$f":"IF(AND(0<K11,K11<2.99),(1),(0))","v":0},"Frequency?",null,{"$f":"MONTH(H11)","v":1},{"$f":"DAY(H11)","v":0},{"$f":"YEAR(H11)","v":1900},null,{"$f":"MONTH(C13)","v":1},{"$f":"DAY(C13)","v":0},{"$f":"YEAR(C13)","v":1900}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"2018 Average",null,null,null,null,null,null,"Annually",{"$f":"IF(AND(2.9999<K11,K11<5.99),(2),(0))","v":0},"12 Months",null,{"$f":"IF(AO5=1,(31),IF(AO5=2,(28),IF(AO5=3,(31),IF(AO5=4,(30),IF(AO5=5,(31),IF(AO5=6,(30),IF(AO5=7,(31),IF(AO5=8,(31)))))))))","v":31},null,null,null,{"$f":"IF(AS5=1,(31),IF(AS5=2,(28),IF(AS5=3,(31),IF(AS5=4,(30),IF(AS5=5,(31),IF(AS5=6,(30),IF(AS5=7,(31),IF(AS5=8,(31)))))))))","v":31}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Monthly/Quarterly",{"$f":"IF(AND(6<K11,K11<8.99),(3),(0))","v":0},"11 Months",null,{"$f":"IF(AO5=9,(30),IF(AO5=10,(31),IF(AO5=11,(30),IF(AO5=12,(31)))))","v":false},{"$f":"AO5-1","v":0},null,null,{"$f":"IF(AS5=9,(30),IF(AS5=10,(31),IF(AS5=11,(30),IF(AS5=12,(31)))))","v":false},{"$f":"AS5-1","v":0},null,null," "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Quarterly",{"$f":"IF(AND(9<K11,K11<12.1),(4),(0))","v":0},"10 Months",null,{"$f":"SUM(AO6:AO7)","v":31},{"$f":"AP5/AO8","v":0},null,null,{"$f":"SUM(AS6:AS7)","v":31},{"$f":"AT5/AS8","v":0},null,null," "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"9 Months",null,null,{"$f":"ROUND(AP8,2)","v":0}]
[null,1,"Current Paystub \nYTD Earnings Calculation \n(paystub must have YTD gross earnings and withholdings or a written VOE is required)",null,"Current  Employer \nYTD Gross Pay",null,null,"Pay Period Ending Date\nMUST BE Most Recent 30 Days\nMM/DD/YY",null,null,"# of Months",null,"Monthly Average Income",null,"Has the applicant worked for \nthis employer since January of this year?  ",null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(AL5:AL8)","v":0},null,null,null,{"$f":"SUM(AP7+AP9)","v":0},null,null,null,{"$f":"SUM(AT7:AT8)","v":0},null,null," "]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF((AM13<0),\" \",(AV13))","v":0},null,{"$f":"IF(ISERROR(E11/K11),0,(E11/K11))","v":0},null,null,null,null," ",null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"prev emp monthly",null,null,null,{"$f":"H11-C13","v":0},{"$f":"AO11/30.5","v":0},{"$f":"AP11-AP9","v":0}]
[null,null,"CURRENT Employer Start Date \nREQUIRED\nMM/DD/YY",null,"Prior Employer(s)\nYTD Earnings\n(If Available)",null,null,"Prior  Employer(s)\n# Months Worked \nthis year",null,"Monthly Average Income Prior  Employer(s)",null,null,null,"LIST HERE\nEmployer Name(s)\n",null,null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"COUNTA(C13)","v":0},"ROUNDED 100TH",null,{"$f":"H11","v":"00:00:00"},null,null,null,null,{"$f":"AO5-AS5","v":0},{"$f":"AP5-AT5","v":0}]
[null,null,null,null,null,null,null,null,null,{"$f":"IF(ISERROR(E13/H13),\" \",(E13/H13))","v":" "},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(AL12=1,(AP11),IF(AL12=0,(AP10)))","v":0},{"$f":"(ROUND(AL13,2))","v":0},null,null,null,null,null,null,null,{"$f":"AU12/AO8+AT12","v":0},{"$f":"(ROUND(AU13,2))","v":0}]
[null,2,"CURRENT EMPLOYER BASE PAY   \n* TEACHERS - determine if they have a 9,10 or 12 month pay cycle - select appropriate frequency on line 23 to accurately calculate teacher income.",null,null,null,null,null,null,null,null,null,null,null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"ytd total income","months worked"]
[null,null,"Enter Base Pay Info From Current Paystub - Salary, Part-time, Hourly Income From CURRENT employer",null,null,null,null,null,null,null,null,null,null,null,null,null,null," ",null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"E13+E11","v":0},{"$f":"AP21+H13","v":0}]
[null,null,"Pay Period Frequency",null,null,"BASE",null,null,null,"YEARLY",null,null,null,null,"MONTHLY",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"SEE CELL BELOW"]
[null,"Guidance"," "," ",null,null,null,null,null," ",null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF((AM15=0),\" \")","v":" "},null,{"$f":"MONTH(H11)","v":1},{"$f":"DAY(H11)","v":0},{"$f":"YEAR(H11)","v":1900}]
[null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(AO5=1,(31),IF(AO5=2,(28),IF(AO5=3,(31),IF(AO5=4,(30),IF(AO5=5,(31),IF(AO5=6,(30),IF(AO5=7,(31),IF(AO5=8,(31)))))))))","v":31}]
[null,null,"Weekly:",null,null,null,null,null,null,{"$f":"IF(F19=0,\" \",(F19*52))","v":" "},null,null,null,null,{"$f":"IF(ISERROR(J19/12),\" \",(J19/12))","v":" "},null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(AO5=9,(30),IF(AO5=10,(31),IF(AO5=11,(30),IF(AO5=12,(31)))))","v":false},{"$f":"AO5-1","v":0}]
[null,null,"Bi-Weekly (80 Hours):",null,null,null,null,null,null,{"$f":"IF(F20=0,\" \",(F20*26))","v":" "},null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"SUM(AO6:AO7)","v":31},{"$f":"AP5/AO8","v":0}]
[null,null,"Semi-Monthly (86.67 Hours):",null,null,null,null,null,null,{"$f":"IF(F21=0,\" \",(F21*24))","v":" "},null,null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"(ROUND(AL13,2))","v":0}]
[null,null,"Monthly:",null,null,null,null,null,null,{"$f":"IF(F22=0,\" \",(F22*12))","v":" "},null,null,null,null," "]
[null,null,"Annual:",null,null,null,null,null,null,{"$f":"IF(F23=0,\" \",(F23*1))","v":" "},null,null,null,null,{"$f":"IF(ISERROR(J23/12),\" \",(J23/12))","v":" "}]
[null,null,"Teacher Pay:",null,"Frequency?",null,null,null,null,{"$f":"IF(E24=\"10 MONTHS\",(F24*10),IF(E24=\"11 MONTHS\",(F24*11),IF(E24=\"9 MONTHS\",(F24*9),IF(E24=\"12 MONTHS\",(F24*12),IF(E24=\"Frequency?\",(\" \"))))))","v":" "},null,null,null,null," "]
[null,null,"(see info below) Other Frequency:",null,null,null,null,null,null,null,null,null,null,null,0]
[null,null," \"Other Frequency\" - Manually calculate and enter figures in yellow fields to the right.  Explain variable pay type, frequency and calculations in Section D below. ",null,null,"Total Base Pay",null,null,null,null,null,null,null,null,{"$f":"SUM(O17:Q25)","v":0}]
[null,3,"CURRENT EMPLOYER VARIABLE PAY and HOURLY PAY\nOVERTIME / INCENTIVE / BONUS / COMMISSION / OTHER  VARIABLE  Documented 2 year history required, must be stable and likely to continue for 3+ years"]
[null,null,"Use Variable Pay Tab"]
[null,null,"Current Paystub",null,null,"YTD Average",null,null,null,"2019 Average",null,null,null,null,{"$f":"IF(J29=\"2019 Average\",\"2018 Average\",\"2017 Average\")","v":"2018 Average"}]
[null,null,"Hourly Pay:",null,null,null,null,null,null,{"$f":"IF(F30=0,\" \",K11)","v":" "},null,null,null,null,{"$f":"IF(ISERROR(F30/J30),0,F30/J30)","v":0}]
[null,"Guidance","Hourly Worker with Scheduled Hours","Hourly Rate:"]
[null,null,null,"Hours Per Week:",null,null,null,{"$f":"IF(F31=\"\",\"\",((F31*F32*52)/12))","v":""},null,null,null,{"$f":"IF(J31=\"\",\"\",((J31*J32*52)/12))","v":""},null,null,null,{"$f":"IF(O31=\"\",\"\",((O31*O32*52)/12))","v":""}]
[null,null,"Hourly Worker With Variable Hours",null,null,{"$f":"'Variable Pay'!G11","v":""},null,null,null,{"$f":"'Variable Pay'!I11","v":""},null,null,null,null,{"$f":"'Variable Pay'!K11","v":""}]
[null,null," ",null,"Overtime:",{"$f":"'Variable Pay'!G12","v":""},null,null,null,{"$f":"'Variable Pay'!I12","v":""},null,null,null,null,{"$f":"'Variable Pay'!K12","v":""}]
[null,null," ","Bonus/Incentive:",null,{"$f":"'Variable Pay'!G13","v":""},null,null,null,{"$f":"'Variable Pay'!I13","v":""},null,null,null,null,{"$f":"'Variable Pay'!K13","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,12]
[null,null," ",null," Commission:",{"$f":"'Variable Pay'!G14","v":""},null,null,null,{"$f":"'Variable Pay'!I14","v":""},null,null,null,null,{"$f":"'Variable Pay'!K14","v":""}]
[null,null,"Shift Differential:",null,null,{"$f":"'Variable Pay'!G15","v":""},null,null,null,{"$f":"'Variable Pay'!I15","v":""},null,null,null,null,{"$f":"'Variable Pay'!K15","v":""}]
[null,null,"Auto Allowance:",null,null,{"$f":"'Variable Pay'!G16","v":""},null,null,null,{"$f":"'Variable Pay'!I16","v":""},null,null,null,null,{"$f":"'Variable Pay'!K16","v":""}]
[null,null,{"$f":"'Variable Pay'!D17","v":"Describe"},null,null,{"$f":"'Variable Pay'!G17","v":""},null,null,null,{"$f":"'Variable Pay'!I17","v":""},null,null,null,null,{"$f":"'Variable Pay'!K17","v":""}]
[null,null,"Total Variable Pay",null,null,{"$f":"'Variable Pay'!G18","v":""},null,null,null,{"$f":"'Variable Pay'!I18","v":""},null,null,null,null,{"$f":"'Variable Pay'!K18","v":""}]
[null,4," W-2 EARNINGS                         Click the button for the  W-2 years provided:                                    OR      "]
[null,null,"Enter figure from Box 5. Be aware of figures in Box 12 with Code V - this represents stock options and should be deducted from the amount in Box 5 as it is not considered qualifying income.   If multiple employers during year, combine W-2 earnings and # of months worked.  Refer to documentation and 1003 to determine/validate months worked."]
[null,"Guidance","W-2 Total Earnings",null,null,"AMOUNT",null,null,null,"# MONTHS ",null,null,null,null,"MONTHLY"]
[null,"Reduced Doc Loans","2019 W-2",null,null,null,null,null,null,{"$f":"'Variable Pay'!I8","v":12},null,null,null,null,{"$f":"IF(ISERROR(F44/J44),0,F44/J44)","v":0},null,null," ",null," "]
[null,null,"2018 W-2",null,null,null,null,null,null,{"$f":"'Variable Pay'!K8","v":12},null,null,null,null,{"$f":"IF(ISERROR(F45/J45),0,F45/J45)","v":0},null,null," ",null," "]
[]
[null,5,"QUALIFYING INCOME ANALYSIS SUMMARY",null,null,null,null,null,null,null,null,null,null,null,null,null,null," "]
[null,null,"Ensure all data entry is complete and accurate.",null,null,null,null,null,null,null,null,null,null,null,null,null,null," "]
[null,null,"A.  JOB HISTORY",null,"2019\n# Months Worked","2018\n# Months Worked",null,null,"YTD Months Worked","YTD Months Available\nTo Work",null,"Total Work History",null,"Potential\nEmployment Gap/Discrepancy\n(address gaps of more than 1 month)",null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"dual income"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null," ",null,null,"GAP current year",{"$f":"I51-J51","v":0},null,null,null,{"$f":"SUM(E51:I51)","v":24},null,{"$f":"IF(H13=0,(0),IF(H13>0,(1)))","v":0}]
[null,"Guidance","Review employment history and address any gaps",null,{"$f":"J44","v":12},{"$f":"J45","v":12},null,null,{"$f":"H13+K11","v":0},{"$f":"AP10","v":0},null,{"$f":"SUM(E51:I51)","v":24},null,{"$f":"IF(AE53=0,0,AF52)","v":0},null,null,null,null,null,null,null,null,null,null,null,null," ",null,null,null," ","Employment gap",null,null,{"$f":"24+J51","v":24},null,{"$f":"K11+H13","v":0}]
[null,null,"Job History Comments",null,"Comments Required Here",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"07/08 history GAP",{"$f":"SUM(E51-12)+SUM(F51-12)","v":0},{"$f":"AE52+AE50","v":0},null,null,{"$f":"AI50-AI51","v":0}]
[null,"Guidance","B. EARNINGS HISTORY \n    AND TREND  ",null,"2019 W-2","2018 W-2",null,null,"2 YR Monthly Avg","Total YTD Avg \n(Reflects all current year employer(s) income averaged over full YTD months)",null,null,null,"Current Employer \nYTD Avg\n(averaged over current employer months)",null,null,null," ",null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"E51+F51+J51","v":24}]
[null,null,"Comparison of Historical Avg\n to Current Avg",null,{"$f":"IF(ISERROR(O44),\"0\",O44)","v":0},{"$f":"IF(ISERROR(O45),\"0\",O45)","v":0},null,null,{"$f":"(F44+F45)/(J44+J45)","v":0},{"$f":"IF(E13=\"\",N54,SUM(E11,E13)/J51)","v":0},null,null,null,{"$f":"M11","v":0},null,null,null," ",null,null,null,null,null,null,null,null,null,null," ","H48","K48","D51","H52"]
[null,null,"% Change In Income\n Year Over Year \nAddress Significant Increases/Decreases",null,"% Change From \n2018 to 2019",null,null,null," ","% Change From \n2019 to YTD Avg",null,null,null,"% Change from 2019 to Current Employer Avg ",null,null,null," ",null," "]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null," ",null," "]
[null,null,null,null,{"$f":"IF(ISERROR(E54/F54),\" \",(SUM(E54-F54)/F54))","v":" "},null,null,null,null,{"$f":"IF(ISERROR(J54/E54),\" \",(SUM(J54-E54)/E54))","v":" "},null,null,null,{"$f":"IF(ISERROR(N54/E54),\" \",(N54-E54)/E54)","v":" "},null,null,null,null,null," "]
[null,null,"Income Trend Comments",null,"Comments Required Here",null,null,null,null,null,null,null,null,null,null,null,null,null,null," ",null,null,null,null,null,null,null,null,null,"Actual combined YTD"]
[null,"Guidance","C. Current Employer\n     Base Pay  ",null,{"$f":"O26","v":0},null,null,null,"ü  Review Withholdings Validation       ü  Review pay stub for debts",null,null,null,{"$f":"IF(E59>N54,\"ALERT - Base Pay is higher than current employer monthly average - must be addressed.\",\"\")","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Total YTD Variable",null,{"$f":"E11+E13","v":0},{"$f":"#REF!","v":{"$error":"#REF!"}},{"$f":"I62","v":""},{"$f":"J62","v":""},{"$f":"E66","v":"VARIABLE PAY \n(For variable pay to be confirmed as eligible - policy and documentation requirements must be met.) "},{"$f":"#REF!","v":{"$error":"#REF!"}}]
[null,null,"Base Income Comments",null,"Comments Required Here",null,null,null,null,null,null,null,null,null,null,null,null," ",null,null,null,null,null,null,null,null,null,"YTD Base Total",null,{"$f":"O26*J51","v":0}]
[null,null,"D.  VARIABLE PAY HISTORY\n      and CURRENT \n     YTD  VARIABLE",null,2019,2018,null,null,"2 YR Monthly Avg","Total YTD Avg \n(Total current year employer(s) income averaged by YTD total months)",null,null,null,"CURRENT EMPLOYER \nActual Variable YTD Avg",null,null,null,null,null," ",null,null,null,null,null,null,null,null,null,{"$f":"SUM(AD59-AD60)/J51","v":{"$error":"#DIV/0!"}},{"$f":"IF(C13=\"\",N62,IF(AD61<0,F34/J51,AD61))","v":""}]
[null,"Guidance","Comparison of Average Monthly Variable  Income",null,{"$f":"'Variable Pay'!G28","v":""},{"$f":"'Variable Pay'!H28","v":""},null,null,{"$f":"'Variable Pay'!K28","v":""},{"$f":"IF(ISERROR(AE61),\"$0.00\",AE61)","v":""},null,null,null,{"$f":"F40","v":""},null,null,null," ",null," ",null,null,null,null,null,null,null,{"$f":"IF(ISERROR(SUM(E11,E13)/SUM(K11,H13)-E59),\"N/A\",(SUM(E11,E13)/SUM(K11,H13)-E59))","v":"N/A"},null,null,"2008 Variable Pay","2008 Variable Pay",null,null,null,null,{"$f":"OR(E62=\"0\",F62=\"0\")*0","v":0}," "]
[null,"Guidance"," Prior 2 Years Plus Current YTD Average Variable Earnings\n(Not for standard use -  only if fully justified and supported)",null,null,{"$f":"'Variable Pay'!J28","v":""},null,null,"Prior Year Plus Current YTD Average Variable Earnings\n(Not for standard use -  only if fully justified and supported)",null,null,null,null,{"$f":"'Variable Pay'!I28","v":""},null," ",null," ",null,null," ",null,null,null,null,null,null,null,null,"Annual Base Pay",{"$f":"O26*12","v":0},{"$f":"O26*12","v":0}]
[null,"Guidance"," ",null," "," Commission % of Current YTD Earnings",null,null,null,{"$f":"IF(ISERROR(F36/E11),0,(F36/E11))","v":0},null,"Bonus % of Current YTD Earnings",null,null,null,{"$f":"IF(ISERROR(F35/E11),0,(F35/E11))","v":0},null," ",null,null," ",{"$f":"IF(AND(I62=0,N62+0),\"Y\",\"N\")","v":{"$error":"#VALUE!"}},null,null,null,null,null,{"$f":"IF(AB62=\"N/A\",\"$0.00\",IF(E13=0,N62,IF(AB62<=0,\"$0.00\",AB62)))","v":"$0.00"},null,"Variable",{"$f":"(F44-AE63)/12","v":0},{"$f":"(F45-AF63)/12","v":0}]
[null,"Guidance","Variable Income Comments",null,"Comments Required Here",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"F44-AE63","v":0},{"$f":"F45-AF63","v":0},{"$f":"O40*12","v":{"$error":"#VALUE!"}},{"$f":"AVERAGE(AE65:AG65)/12","v":{"$error":"#VALUE!"}}]
[null,"Guidance","ELIGIBLE BASE PAY\n(Always qualify with base pay only - unless variable pay is\nneeded for qualification.)",null,"VARIABLE PAY \n(For variable pay to be confirmed as eligible - policy and documentation requirements must be met.) ",null,null,null,"2106 Net Monthly Expense Adjustment"," TOTAL ELIGIBLE \nMONTHLY INCOME",null,null,"ENTER VALIDATED QUALIFYING INCOME\n(The Associate is responsible for validation the qualitying income entered below meets policy and documentation requirements.  Documentation must be uploaded to the loan file. )",null,null,null,null,null,null,null,null,{"$f":"IF(AND(E64:E631,I67=0),\"Y\",\"\")","v":""}]
[null,null,{"$f":"E59","v":0},null,{"$f":"'Variable Pay'!F38","v":0},null,null,null,{"$f":"IF('2106 Worksheet'!R7=\"\",'2106 Worksheet'!Q11,'2106 Worksheet'!Q7)","v":0},{"$f":"C67+E67+I67","v":0},null,null,"Enter Qualifying Income Amount"]
[null,"Guidance","Qualifying Income\n Supporting Comments \n\nLoan File Must Have \nRequired Documentation",null,"Comments Required Here"]
[null,null,null,null," "]

{"sheet":"Variable Pay","index":3,"n_rows":38,"n_cols":25,"rows_kept":38,"cut":false,"hidden":false,"merged":[[0,1,0,10],[1,1,1,10],[2,4,2,6],[2,8,2,9],[3,1,3,6],[3,7,3,8],[3,9,3,10],[4,1,4,10],[5,2,5,10],[6,2,6,4],[6,5,6,6],[6,7,6,8],[6,9,6,10],[15,2,15,4],[6,19,6,20],[6,21,6,22],[6,23,6,24],[7,2,7,4],[7,6,8,6],[7,8,8,8],[7,10,8,10],[8,2,8,4],[11,2,11,4],[12,2,12,4],[13,2,13,4],[14,2,14,4],[10,3,10,4],[16,3,16,4],[17,2,17,4],[19,1,27,1],[19,2,19,4],[20,2,20,4],[21,2,21,4],[22,2,22,4],[23,2,23,4],[24,2,24,4],[25,2,25,4],[36,3,36,4],[37,2,37,4],[26,3,26,4],[27,2,27,4],[29,1,37,1],[29,2,29,5],[30,2,30,4],[31,2,31,4],[32,2,32,4],[33,2,33,4],[34,2,34,4],[35,2,35,4]]}
[null,"VARIABLE PAY FROM VOE",null,null,null,null,null,null,null,null,null," "]
[null,"VOEs FOR ALL PERIODS (YTD AND PRIOR YEARS) ARE REQUIRED TO USE THIS FORM"]
[null,"DATE",{"$f":"'Wage Earner Income Calculator'!C2","v":"2020-01-31"},"Loan #",{"$f":"IF('Wage Earner Income Calculator'!E2=\"\",\"\",'Wage Earner Income Calculator'!E2)","v":""},null,null,"Borrower:",{"$f":"IF('Wage Earner Income Calculator'!D3=\"\",\"\",'Wage Earner Income Calculator'!D3)","v":""}]
[null,"Data entry is REQUIRED in ALL SECTIONS - ",null,null,null,null,null,"fill in all YELLOW FIELDS",null," - White fields auto-calculate."]
[null,"To make data entry corrections always use the DELETE key.  Use arrow keys to move from cell to cell - ensure Scroll Lock is not on.  \nWhen entering comments, use the ALT and ENTER key together to start new row. "]
[null,null,"VARIABLE PAY",null,null,null,null,null,null,null,null," "]
[null,null,null,null,null,"YTD Gross",null,{"$f":"'Wage Earner Income Calculator'!J29","v":"2019 Average"},null,{"$f":"'Wage Earner Income Calculator'!O29","v":"2018 Average"},null,null,null,null,null,null,null,null,null,{"$f":"F7","v":"YTD Gross"},null,{"$f":"H7","v":"2019 Average"},null,{"$f":"J7","v":"2018 Average"}]
[null,null,"Employer Start Date (current year):",null,null,"2020-01-01",{"$f":"'Wage Earner Income Calculator'!K11","v":0},"2019-01-01",{"$f":"ROUND(W13,2)","v":12},"2018-01-01",{"$f":"ROUND(Y13,2)","v":12},null,null,null,null,null,null,null,null,{"$f":"MONTH(F8)","v":1},null,{"$f":"MONTH(H8)","v":1},{"$f":"DAY(H8)","v":1},{"$f":"MONTH(J8)","v":1},{"$f":"DAY(J8)","v":1}]
[null,null,"End Date:",null,null,{"$f":"IF('Wage Earner Income Calculator'!H11=\"\",\"\",'Wage Earner Income Calculator'!H11)","v":""},null,"2019-12-31",null,"2018-12-31",null,null,null,null,null,null,null,null,null,{"$f":"MONTH(F9)","v":{"$error":"#VALUE!"}},{"$f":"DAY(F9)","v":{"$error":"#VALUE!"}},{"$f":"MONTH(H9)","v":12},{"$f":"DAY(H9)","v":31},{"$f":"MONTH(J9)","v":12},{"$f":"DAY(J9)","v":31}]
[null,null,null,null,null,"Total","Average","Total","Average","Total","Average"]
[null,"Guidance","Hourly Worker With\nVariable Hours","YTD Hourly Pay:",null,null,{"$f":"IF(G$8=0,\"\",F11/G$8)","v":""},null,{"$f":"IF(H11=0,\"\",H11/I$8)","v":""},null,{"$f":"IF(J11=0,\"\",J11/K$8)","v":""}," "]
[null,"Guidance","Overtime:",null,null,null,{"$f":"IF(G$8=0,\"\",F12/G$8)","v":""},null,{"$f":"IF(H12=0,\"\",H12/I$8)","v":""},null,{"$f":"IF(J12=0,\"\",J12/K$8)","v":""},null,null,null,null,null,null,null,null,{"$f":"T9-T8","v":{"$error":"#VALUE!"}},{"$error":"#VALUE!"},11,30,11,30]
[null,"Guidance","Bonus/Incentive:",null,null,null,"",null,"",null,"",null,null,null,null,null,null,null,null,null,{"$f":"T12+(U12/30)","v":{"$error":"#VALUE!"}},null,{"$f":"V12+(W12/30)","v":12},null,{"$f":"X12+(Y12/30)","v":12}]
[null,"Guidance","Commission:",null,null,null,"",null,"",null,""]
[null,"Guidance","Shift Differential:",null,null,null,"",null,"",null,""]
[null,"Guidance","Auto Allowance:",null,null,null,"",null,"",null,""]
[null,"Guidance","Other: ","Describe",null,null,"",null,"",null,""]
[null,null,"Totals:",null,null,{"$f":"SUM(F11:F17)","v":0},"",{"$f":"SUM(H11:H17)","v":0},{"$f":"IF(H18=0,\"\",H18/I$8)","v":""},{"$f":"SUM(J11:J17)","v":0},""]
[]
[null,null,"Comparison of Average Monthly Variable  Income",null,null,"YTD",{"$f":"H7","v":"2019 Average"},{"$f":"J7","v":"2018 Average"},"YTD and\nPrior Year Avg","YTD and\n2 YR Monthly Avg","2 YR Monthly Avg"]
[null,null,"YTD Hourly Pay",null,null,{"$f":"IF(G11=0,\"\",G11)","v":""},{"$f":"I11","v":""},{"$f":"K11","v":""},{"$f":"IF(((F11+H11)/(G$8+I$8))=0,\"\",((F11+H11)/(G$8+I$8)))","v":""},{"$f":"IF(((F11+H11+J11)/(G$8+I$8+K$8))=0,\"\",((F11+H11+J11)/(G$8+I$8+K$8)))","v":""},{"$f":"IF(((H11+J11)/(I$8+K$8))=0,\"\",((H11+J11)/(I$8+K$8)))","v":""}]
[null,null,"Overtime:",null,null,{"$f":"IF(G12=0,\"\",G12)","v":""},"","",{"$f":"IF(((F12+H12)/(G$8+I$8))=0,\"\",((F12+H12)/(G$8+I$8)))","v":""},{"$f":"IF(((F12+H12+J12)/(G$8+I$8+K$8))=0,\"\",((F12+H12+J12)/(G$8+I$8+K$8)))","v":""},{"$f":"IF(((H12+J12)/(I$8+K$8))=0,\"\",((H12+J12)/(I$8+K$8)))","v":""}]
[null,null,"Bonus/Incentive:",null,null,"","","","","",{"$f":"IF(((H13+J13)/(I$8+K$8))=0,\"\",((H13+J13)/(I$8+K$8)))","v":""}]
[null,null,"Commission:",null,null,"","","","","",""]
[null,null,"Shift Differential:",null,null,"","","","","",""]
[null,null,"Auto Allowance:",null,null,"","","","","",""]
[null,null,null,{"$f":"D17","v":"Describe"},null,"","","","","",""]
[null,null,"Totals:",null,null,"",{"$f":"IF((SUM(G21:G27))=0,\"\",(SUM(G21:G27)))","v":""},{"$f":"IF((SUM(H21:H27))=0,\"\",(SUM(H21:H27)))","v":""},"","",""]
[]
[null,null,"ELIGIBLE VARIABLE PAY "]
[null,null,"YTD Hourly Pay",null,null,{"$f":"IF(G$8=0,\"\",IF(AND(G11<I11,I11<K11),G11,(IF(AND(G11>I11,I11<K11,G11<K11),((F11+H11)/(G$8+I$8)),((F11+H11+J11)/(G$8+I$8+K$8))))\n))","v":""}]
[null,null,"Overtime:",null,null,{"$f":"IF(G$8=0,\"\",IF(AND(G12<I12,I12<K12),G12,(IF(AND(G12>I12,I12<K12,G12<K12),((F12+H12)/(G$8+I$8)),((F12+H12+J12)/(G$8+I$8+K$8))))\n))","v":""}]
[null,null,"Bonus/Incentive:",null,null,""]
[null,null,"Commission:",null,null,""]
[null,null,"Shift Differential:",null,null,""]
[null,null,"Auto Allowance:",null,null,""]
[null,null,null,{"$f":"D17","v":"Describe"},null,""]
[null,null,"Totals:",null,null,{"$f":"(SUM(F31:F37))","v":0}]

{"sheet":"Withholdings Validation","index":4,"n_rows":37,"n_cols":11,"rows_kept":37,"cut":false,"hidden":false,"merged":[[1,0,3,7],[4,1,4,7],[5,5,6,7],[8,3,8,4],[8,5,11,7],[18,8,18,11],[19,8,19,11],[18,6,19,7],[14,1,14,7],[36,1,36,4],[32,1,34,7],[15,1,15,7],[24,1,24,2],[22,1,22,7],[17,8,17,11]]}
[]
["When the pay stub and / or W-2 do not reflect gross earnings, Social Security taxes, withholding taxes or other deductions, the use of alternative income documentation is not acceptable and full documentation must obtained. \n\nFiles that have unresolved issues or numerous red flags must be referred to a Supervisor. The QC Department must be notified of each fraud incident uncovered during the process."]
[]
[null,null,null,null,null,null,null,null,null,null," "]
["Guidance"," PAYSTUB WITHHOLDING VALIDATION\n\nCompare paystub withholdings to the High/Low ranges below - withholdings outside of this range \nare a potential red flag and require further review. \n\nCalculating exact withholdings on a current paystub is difficult due to the many variables for pre-tax deductions and employer methods.  \nGiven these variables a \"withholding range\" has been established to validate withholdings to the current paystub.  \nNote:Social Security (FICA) will be based on the lower of the actual or max earnings limit for current year per table below. "]
[null,null,null,null,null," "]
["Step 1",null,"YTD Gross Income",{"$f":"'Wage Earner Income Calculator'!E11","v":0}]
[]
[null,null,null,"Withholdings on paystub should be within the ranges below. ",null," ",null,null," "]
[null,null,null,"High","Low"]
[null,null,"Social Security",{"$f":"J11","v":0},{"$f":"D11*0.95","v":0},null,null,null,null,{"$f":"IF(G28>D7,D7*0.042,G28*0.042)","v":0}]
[null,null,"Medicare",{"$f":" D7*0.0145","v":0},{"$f":" (D7*0.0145)*0.95","v":0},null,null,null,null,{"$f":"IF(J11>G29,G29,J11)","v":0}]
[null,null,"Combined",{"$f":"SUM(D11:D12)","v":0}]
[]
["Step 2","W-2 Withholding Validation\n "]
[null,"Review W-2's to validate withholdings are consistent with the calculations below. "]
[null,null,"Tax Year",{"$f":"'Wage Earner Income Calculator'!E61","v":2019},{"$f":"'Wage Earner Income Calculator'!F61","v":2018}]
[null,null,"Gross Income Amount",{"$f":"'Wage Earner Income Calculator'!F44","v":0},{"$f":"'Wage Earner Income Calculator'!F45","v":0},null,null,null," "]
[null,null,"Social Security",{"$f":"IF(D18=\"\",\"$0.00\",J27*0.062)","v":0},{"$f":"IF(E18=\"\",\"$0.00\",K27*0.062)","v":0},null,"These calculations are aligned with the table below and do reflect tax year thresholds. ",null," "]
[null,null,"Medicare",{"$f":"D18*0.0145","v":0},{"$f":"E18*0.0145","v":0},null,null,null," "]
[null,null,"Combined",{"$f":"SUM(D19:D20)","v":0}]
[]
[null,"Compare to W-2 Withholdings for prior years.  \nAny variation to current paystub, further research/review warranted.  Follow established escalation procedures. "]
[null,"Withholding Percentages",null,2017,2018,2019,2020]
[null,"Social Security  ",null,6.2E-2,6.2E-2,6.2E-2,6.2E-2,null,null,{"$f":"IF(D17=D24,D28,IF(D17=E24,E28,IF(D17=F24,F28,)))","v":132900},{"$f":"IF(E17=E24,E28,IF(E17=F24,F28,IF(E17=G24,G28)))","v":128400}]
[null,"Medicare  "," ",1.4500000000000001E-2,1.4500000000000001E-2,1.4500000000000001E-2,1.4500000000000001E-2,null,null,{"$f":"D18","v":0},{"$f":"E18","v":0}]
[null,"Maximum Earnings Taxable",null,null,null,null,null,null,null,{"$f":"MIN(J25:J26)","v":0},{"$f":"MIN(K25:K26)","v":0}]
[null,"Social Security (OASDI* only)",null,127200,128400,132900,137700]
[null,"Maximum Contribution",null,{"$f":"D25*D28","v":7886.4},{"$f":"E25*E28","v":7960.8},{"$f":"F25*F28","v":8239.7999999999993},8537]
[null,"Medicare (HI Only)",null,"No Limit","No Limit","No Limit","No Limit"]
[]
[]
[null,"NOTE: The 7.65% rate is the combined rate for Social Security and Medicare.  The Social Security portion (OASDI) is 6.20% or earnings up to the applicable maximum taxable amount.  The Medicare portion (HI) is 1.45% on all earnings."]
[]
[]
[]
[null,"*OASDI =Old Age, Survivors, and Disability Insurance"]

{"sheet":"2106 Worksheet","index":5,"n_rows":11,"n_cols":19,"rows_kept":11,"cut":false,"hidden":false,"merged":[[10,2,10,5],[5,2,5,5],[6,2,6,6],[7,2,7,6],[9,2,9,5],[8,2,8,4],[0,1,0,18],[4,17,5,17],[2,18,5,18],[4,13,5,15],[4,16,5,16],[1,3,1,12],[2,3,2,12],[3,3,3,12],[1,13,3,15],[4,3,4,12]]}
[null,"2106 Worksheet"]
[null,null,"Applicant:",{"$f":"IF('Wage Earner Income Calculator'!D3=\"\",\"\",'Wage Earner Income Calculator'!D3)","v":""},null,null,null,null,null,null,null,null,null,"Auto Calcs Most Conservative"]
[null,null,"Loan #:",{"$f":"IF('Wage Earner Income Calculator'!E2=\"\",\"\",'Wage Earner Income Calculator'!E2)","v":""},null,null,null,null,null,null,null,null,null,null,null,null,null," ","Comments Required for All Overwrite Adjustments\n(briefly explain adjustment in this limited space but expand as necessary in the larger comment section at bottom of sheet)"]
[null,null,"Completed By:",{"$f":"IF('Wage Earner Income Calculator'!K2=\"\",\"\",'Wage Earner Income Calculator'!K2)","v":""},null,null,null,null,null,null,null,null,null,null,null,null," "]
[null,null,"Date:",{"$f":"'Wage Earner Income Calculator'!C2","v":"2020-01-31"},null,null,null,null,null,null,null,null,null,"2 Year Average",null,null,"Monthly Average","Manual Overwrite Adjustment"]
[null,"II.","Form 2106 - Employee Business Expenses",null,null,null," ",null,null,{"$f":"'Wage Earner Income Calculator'!F61","v":2018},null,null,{"$f":"'Wage Earner Income Calculator'!E61","v":2019}]
[null,1,"Total Expenses line 6 columns A plus B of the 2106 or line 21 of 1040 (which ever is greater)",null,null,null,null,null,"-",null,null,null,null,null,{"$f":"IF(Overwrite=\"\",IF(J7=0,M7,IF(J7>M7,(J7+M7)/2,M7)),-Overwrite)","v":0},{"$f":"IF(Overwrite=\"\", \"\", \"*\")","v":""},{"$f":"O7/12","v":0}]
[null,2,"Automobile Depreciation  \n"]
[null,null,"1. Line 28 if complete OR",null,null,null,null,null,"+",null,null,null,null,null," "]
[null,null,"2. Line 13 miles driven multiplied by depreciation factor for appropriate year.",null,null,null,null,null,"+",null,null,null,null,null," "]
[null,3,"Net Employee Business Expense - adjust income by this amount per established procedures. ",null,null,null,null,null,"-",{"$f":"-J7+J9+J10","v":0},null,null,{"$f":"-M7+M9+M10","v":0},null,{"$f":"IF(Overwrite=\"\",IF(J11=0,M11,IF(J11<M11,(J11+M11)/2,M11)),Overwrite)","v":0},null,{"$f":"O11/12","v":0}]

{"sheet":"Income Red Flag Job Aid","index":6,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":false,"merged":[[0,0,0,9]]}
["For Reference Purposes Only - Not required to be printed or placed in file "]