{"text_format":"1","workbook":"xlsx","n_sheets":13,"sheets":["Workbook Instructions and Notes","Targeting & Financing Sources","Sources of Funds Summary","Development Budget","Discounted Financing","Empowerment Budget","Benchmarks","Targeting Score","Subsidy Per Unit Score","Households Requiring Lg. Units","AHP Application Comparison","Data Import Summary","Version Control"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":2,"defined_names":38,"comments":2,"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":"Workbook Instructions and Notes","index":0,"n_rows":49,"n_cols":2,"rows_kept":49,"cut":false,"hidden":false,"merged":[]}
[null,"Owner-Occupied AHP Application Financial Workbook"]
[null,{"$f":"'Targeting & Financing Sources'!A2","v":"Version 5.1 Updated 1/26/21"}]
[]
["General Instructions and Notes"]
["1.","All shaded cells are protected and cannot be altered."]
["2.","Complete all worksheets sequentially."]
["Targeting & Financing Sources Worksheet"]
["1.","Enter the current 50% (very-low) AMI, adjusted for family size, based on the HUD income limit guidelines."]
["2.","Enter the targeted AMI for each unit type, (i.e. 50%, 60% etc.)"]
["3.","If sponsor is providing discounted financing, (i.e. zero % mortgages), select \"Y\" in the box and complete the Discounted Financing  worksheet."]
["4.","If project is a self-help project financed by the USDA RD 502 program, select \"Y\" in the box. "]
["5.","Payments must be calculated and entered manually for any additional payments for second or third mortgages under \"Other     Payments.\""]
["6. ","Enter the terms, rates, payment terms, and status of funding commitment for each funding source for homebuyer purchases."]
["7.","Provide documentation for approved sources of financing."]
["8.","To ensure consistent and reasonable rate assumptions, interest rates and spreads should be set in accordance with the Interest Rate Assumptions Guidelines."]
["9.","If applicable, select which units are reserved for Homeless, Special Needs-Seniors, or Special Needs-Others. Number of units reserved must be consistent with the Homeless and Special Needs scoring categories to receive scoring credit."]
["Sources of Funds Summary Worksheet"]
["1.","The worksheet collects data required by the Federal Housing Finance Agency. Select which sources are applicable to your project."]
["2.","Include only permanent financing sources; exclude construction and bridge financing."]
["3.","For HUD, USDA, and VA financing, select \"Other Federal Housing Programs.\""]
["4.","If a source does not fall clearly within the listed categories, select “Other.”"]
["Development Budget Worksheet"]
["1.","If AHP funds will be used as downpayment assistance for the purchase of existing homes with no rehabilitation, enter the acquisition prices in the development budget under “Purchase of Existing Homes.” The targeting worksheet must also reflect the same estimate of the sales prices of the homes to be purchased by eligible homebuyers."]
["2. ","Enter the project's residential square footage. "]
["3. ","Select the building type, \"Condos,” \"Single Family Homes,” or \"Townhomes.\""]
["4.","If sources do not equal uses, enter an explanation at the bottom of the worksheet."]
["5.","If sponsor is providing discounted financing, complete the Discounted Financing worksheet."]
["6.","Do not include non-cash \"costs,\" e.g. donated land, appraised value of land, volunteer labor, pro-bono services, fee waivers, expenses to be reimbursed."]
["7.","Construction management (sponsor managed and project specific) line item should not include other overhead costs (unrelated to construction). Include other overhead costs in developer fee. Documentation to support this line item will be required if the project is approved."]
["8.","If a lot is sold by the sponsor to the homebuyer for a sales price that exceeds the cash cost to purchase and develop the lot, the difference between the sales price and the cash cost is considered developer fee."]
["9.","Include site improvements under the \"On-site Improvements\" and \"Off-site Improvements,\" not under \"Land Costs\" line items."]
["10.","Ensure purchase price/acquisition cost is consistent with readiness and donation sections in the AHP Application and that documentation is provided."]
["11.","\"Closing costs\" are those to be paid by the developer, not the homebuyer."]
["Discounted Financing Worksheet"]
["1. ","Complete the Discounted Financing worksheet only for projects which provide discounted financing for homebuyers."]
["2.","\"Total Adjusted Sources of Financing\" must equal \"Total Uses.\""]
["Promotion of Empowerment Budget Worksheet"]
["1. ","This worksheet ties to the scoring of promotion of empowerment. No empowerment points will be awarded if this is not filled out."]
["2.","Include the source(s) of revenue that will be used to pay for promotion of empowerment services described in the application."]
["3","Include all expenses related to these services."]
["4","Total revenue minus total expenses should equal zero."]
["Benchmarks Worksheet"]
["1.","Provide detailed explanations and supporting documentation for all benchmark deviations (e.g. requirements of other funding sources). Except for construction cost per square foot and interest rate assumptions, benchmark deviations are indicated by  “No” under the column \"Within Benchmarks?\". Deviation explanations must be quantifiable in relation to the amount the benchmark is exceeded. Supporting documentation from a third party is required for the Construction Cost per Square Foot benchmark deviation, and is preferred for all other benchmark deviations."]
["2. ","Enter the project’s RSMeans maximum construction cost per square feet limit within the “Construction Costs per Square Foot\" benchmark explanation. Refer to the attached RSMeans list of construction costs per square foot by building type and location."]
["Targeting Score Worksheet"]
["1.","Informational only, no input necessary."]
["AHP Application Comparison Worksheet"]
["1.","After completing all other worksheets, press the “Run Comparison Now” button. A new window will open on your screen. Find the project’s AHP Application, select that file, and press “Open.” (Make sure macros are enabled in the AHP Financial workbook)."]
["2.","If there are any “No’s” in the “Match” column, review and edit the responses on the AHP Application and AHP Financial Workbook to ensure consistent data between the two documents."]

{"sheet":"Targeting & Financing Sources","index":1,"n_rows":85,"n_cols":38,"rows_kept":85,"cut":false,"hidden":false,"merged":[[56,15,57,15],[56,16,57,19],[12,24,13,26],[12,3,12,4],[13,3,13,4],[15,3,15,4],[16,18,16,19],[50,15,51,15],[50,16,51,19],[9,8,12,8],[10,11,12,11],[10,12,12,12],[19,10,21,10],[19,11,21,11],[10,9,12,9],[10,10,12,10],[0,0,0,19],[1,0,1,19],[5,1,5,2],[5,18,5,19],[5,14,5,17],[5,3,5,6],[22,0,48,0],[16,3,16,4],[10,1,10,2],[12,1,12,2],[14,3,14,4],[11,1,11,2],[50,1,50,12],[16,14,16,17],[14,14,14,18],[12,14,12,18],[13,14,13,18],[19,9,21,9],[10,18,10,19],[10,14,10,17],[8,3,8,4],[8,14,8,17],[9,14,9,17],[10,5,10,6],[6,1,6,2],[6,3,6,6],[8,9,8,12],[8,1,8,2],[9,1,9,2],[7,1,7,6],[9,5,9,6],[6,18,6,19],[7,18,7,19],[8,18,8,19],[9,18,9,19],[6,14,6,17],[7,14,7,17],[11,5,11,6],[12,5,12,6],[9,3,9,4],[10,3,10,4],[11,3,11,4],[52,15,53,15],[52,16,53,19],[54,15,55,15],[54,16,55,19],[8,5,8,6],[13,5,13,6],[18,6,21,6],[14,5,14,6],[15,5,15,6],[16,5,16,6],[48,2,48,6],[13,1,13,2],[14,1,14,2],[16,1,16,2],[15,1,15,2],[19,12,21,12]]}
["Owner-Occupied Projects: Targeting & Financing Sources"]
["Version 5.1 Updated 1/26/21"]
[]
[null,"Note: Attach copy of page from HUD statistics showing AMIs for this PMSA/MSA"]
[]
[null,"Project Name",null,null,null,null,null,null,null,null,null,null,"Per unit",null,"Actual AMI Ratios",null,null,null,"Proposed"]
[null,"PMSA",null,null,null,null,null,null,null,"AHP Subsidy Requested",null,{"$f":"+H49","v":""},{"$f":"IF(S11=0,\"\",+L7/S11)","v":""},null,"Units with AMI ratio of 65% or less",null,null,null,{"$f":"IF(SUM(X24:X48)=\"\",\"\",SUM(X24:X48))","v":""}]
[null,"HUD - 2021 Income Limits (if unavailable, use 2020)",null,null,null,null,null,null,null,null,null,null,null,null,"Units with AMI ratio over 65% and up to 70%",null,null,null,{"$f":"IF(SUM(Y24:Y48)=\"\",\"\",SUM(Y24:Y48))","v":""}]
[null,"50% AMI",null,"100% AMI",null,"Family Size",null,null,null,"Financing Sources",null,null,null,null,"Units with AMI ratio over 70% and up to 75%",null,null,null,{"$f":"IF(SUM(Z24:Z48)=\"\",\"\",SUM(Z24:Z48))","v":""}]
[null,null,null,{"$f":"IF(B10=\"\",\"\",B10*2)","v":""},null,"1 person",null,null,null,"(1)","(2)","(3)","(4)",null,"Units with AMI ratio over 75% and up to 80%",null,null,null,{"$f":"IF(SUM(AA24:AA48)=\"\",\"\",SUM(AA24:AA48))","v":""},null,null,null,null,null,null,"Per Unit"]
[null,null,null,{"$f":"IF(B11=\"\",\"\",B11*2)","v":""},null,"2 people",null,null,null,{"$f":"J20","v":"First Mortgage Amount"},{"$f":"IF(K20=\"\",\"\",K20)","v":""},{"$f":"IF(L20=\"\",\"\",L20)","v":""},{"$f":"IF(M20=\"\",\"\",M20)","v":""},null,"Total Units: ",null,null,null,{"$f":"IF(SUM(S7:S10)=\"\",\"\",SUM(S7:S10))","v":""}]
[null,null,null,"",null,"3 people"]
[null,null,null,"",null,"4 people",null,null,null,null,null,null,null,null," Sweat equity hours per home",null,null,null,null,null,null,null,null,null,"FHFB AMI Tally"]
[null,null,null,"",null,"5 people",null,null,"Term (yrs)",null,null,null,null,null," Is sponsor providing discounted financing?"]
[null,null,null,"",null,"6 people",null,null,"Rate",null,null,null,null,null," Is USDA RD 502 financing for self-help homes?",null,null,null,null,null,null,null,null,null,"<=30%","<=50%","<=80%"]
[null,null,null,"",null,"7 people",null,null,"Payment Terms",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(V49=0,0)","v":0},{"$f":"IF(W49=0,0,W49)","v":0},{"$f":"(IF(X49=0,0,X49))+(IF(Y49=0,0,Y49))+(IF(Z49=0,0,Z49))+(IF(AA49=0,0,AA49))","v":0}]
[null,null,null,"",null,"8 people",null,null,"Committed?","Y",null,null,null,null,"Total Project Funds Committed",null,null,null,{"$f":"I49+J49+IF(K17=\"Y\",K49,0)+IF(L17=\"Y\",L49,0)+IF(M17=\"Y\",M49,0)","v":0}]
[]
[null,null,null,null,null,null,"Homeless/ Special Needs",null,null,"(1)","(2)","(3)","(4)"]
[null,"No. of","Bed-","Assumed","Target","Target",null,"AHP","Borrower","First Mortgage Amount",null,null,null,"Purchase","1st mtg.","Other","Tax","Home-","Total ","Hsg."]
[null,"Units","rooms","Family","Family","AMI",null,"Subsidy","Deposit",null,null,null,null,"Price**","P & I","Pymts","and","owners","Housing","Exp./"]
[null,"Proposed",null,"Size","Income","Ratio",null,null,null,null,null,null,null,null,"Calc.",null,"Ins.","Dues","Expense","Income",null,"<or=30%","<or=50%","<or=65%",">65%<or=70%",">70%<or=75%",">75%<or=80%",null,"Pmt as %","AHP","Down-","1st ","2nd","Other","Other","Homeless","Special ","3+ Bedroom"]
[null,null,null,null,"$","%",null,"$","$","$","$","$","$","$","$","$","$","$","$","%",null,null,null,null,null,null,null,null,"of income",null,"payment","Mtg","Mtg","Funds","Funds",null,"Needs","Units"]
[null,null,null,null,{"$f":"IF($D24=1,$F24*$D$10,IF($D24=2,$F24*$D$11,IF($D24=3,$F24*$D$12,IF(D24=4,$F24*$D$13,IF($D24=5,$F24*$D$14,IF($D24=6,$F24*$D$15,IF($D24=7,$F24*$D$16,IF($D24=8,$F24*$D$17))))))))","v":false},null,null,null,null,null,null,null,null,{"$f":"IF(SUM(G24:M24)=\"\",\"\",SUM(G24:M24))","v":""},{"$f":"IF(J24>0,(PMT($J$15/12,$J$14*12,J24)*-1),\"\")","v":""},null,null,null,{"$f":"IF(SUM(O24:R24)=\"\",\"\",SUM(O24:R24))","v":""},{"$f":"IF(E24>0,(S24/(E24/12)),\"\")","v":""},null,{"$f":"IF(AND($F24>0,$F24<=0.3)=1,B24,0)","v":0},{"$f":"IF(AND($F24>0,$F24<=0.5)=1,B24,0)","v":0},{"$f":"IF(AND($F24>0,$F24<=0.65)=1,B24,0)","v":0},{"$f":"IF(AND($F24>0.65,$F24<=0.7)=1,B24,0)","v":0},{"$f":"IF(AND($F24>0.7,$F24<=0.75)=1,B24,0)","v":0},{"$f":"IF(AND($F24>0.75,$F24<=0.8)=1,B24,0)","v":0},null,{"$f":"IF(T24>0.35,B24,0)","v":0},{"$f":"+H24*B24","v":0},{"$f":"+I24*$B24","v":0},{"$f":"+J24*$B24","v":0},{"$f":"+K24*$B24","v":0},{"$f":"+L24*$B24","v":0},{"$f":"+M24*$B24","v":0},{"$f":"IF($G24=\"\",\"\",IF($G24=\"Special Needs, Seniors\",\"\",IF($G24=\"Special Needs, Other\",\"\",(IF($G24=\"\",\"\",$B24)))))","v":""},{"$f":"IF($G24=\"\",\"\",IF($G24=\"Homeless\",\"\",IF($G24=\"\",\"\",$B24)))","v":""},{"$f":"IF(C24>=3,B24,0)","v":0}]
[null,null,null,null,{"$f":"IF($D25=1,$F25*$D$10,IF($D25=2,$F25*$D$11,IF($D25=3,$F25*$D$12,IF(D25=4,$F25*$D$13,IF($D25=5,$F25*$D$14,IF($D25=6,$F25*$D$15,IF($D25=7,$F25*$D$16,IF($D25=8,$F25*$D$17))))))))","v":false},null,null,null,null,null,null,null,null,{"$f":"IF(SUM(G25:M25)=\"\",\"\",SUM(G25:M25))","v":""},{"$f":"IF(J25>0,(PMT($J$15/12,$J$14*12,J25)*-1),\"\")","v":""},null,null,null,{"$f":"IF(SUM(O25:R25)=\"\",\"\",SUM(O25:R25))","v":""},"",null,{"$f":"IF(AND($F25>0,$F25<=0.3)=1,B25,0)","v":0},{"$f":"IF(AND($F25>0,$F25<=0.5)=1,B25,0)","v":0},{"$f":"IF(AND($F25>0,$F25<=0.65)=1,B25,0)","v":0},{"$f":"IF(AND($F25>0.65,$F25<=0.7)=1,B25,0)","v":0},{"$f":"IF(AND($F25>0.7,$F25<=0.75)=1,B25,0)","v":0},{"$f":"IF(AND($F25>0.75,$F25<=0.8)=1,B25,0)","v":0},null,0,{"$f":"+H25*B25","v":0},0,0,0,{"$f":"+L25*$B25","v":0},{"$f":"+M25*$B25","v":0},{"$f":"IF($G25=\"\",\"\",IF($G25=\"Special Needs, Seniors\",\"\",IF($G25=\"Special Needs, Other\",\"\",(IF($G25=\"\",\"\",$B25)))))","v":""},{"$f":"IF($G25=\"\",\"\",IF($G25=\"Homeless\",\"\",IF($G25=\"\",\"\",$B25)))","v":""},{"$f":"IF(C25>=3,B25,0)","v":0}]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,{"$f":"IF(SUM(O26:R26)=\"\",\"\",SUM(O26:R26))","v":""},"",null,0,{"$f":"IF(AND($F26>0,$F26<=0.5)=1,B26,0)","v":0},{"$f":"IF(AND($F26>0,$F26<=0.65)=1,B26,0)","v":0},{"$f":"IF(AND($F26>0.65,$F26<=0.7)=1,B26,0)","v":0},{"$f":"IF(AND($F26>0.7,$F26<=0.75)=1,B26,0)","v":0},{"$f":"IF(AND($F26>0.75,$F26<=0.8)=1,B26,0)","v":0},null,0,0,0,0,0,{"$f":"+L26*$B26","v":0},0,"","",{"$f":"IF(C26>=3,B26,0)","v":0}]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,{"$f":"IF(SUM(O27:R27)=\"\",\"\",SUM(O27:R27))","v":""},"",null,0,{"$f":"IF(AND($F27>0,$F27<=0.5)=1,B27,0)","v":0},{"$f":"IF(AND($F27>0,$F27<=0.65)=1,B27,0)","v":0},{"$f":"IF(AND($F27>0.65,$F27<=0.7)=1,B27,0)","v":0},{"$f":"IF(AND($F27>0.7,$F27<=0.75)=1,B27,0)","v":0},{"$f":"IF(AND($F27>0.75,$F27<=0.8)=1,B27,0)","v":0},null,0,0,0,0,0,{"$f":"+L27*$B27","v":0},0,"","",{"$f":"IF(C27>=3,B27,0)","v":0}]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,{"$f":"IF(SUM(O28:R28)=\"\",\"\",SUM(O28:R28))","v":""},"",null,0,{"$f":"IF(AND($F28>0,$F28<=0.5)=1,B28,0)","v":0},{"$f":"IF(AND($F28>0,$F28<=0.65)=1,B28,0)","v":0},{"$f":"IF(AND($F28>0.65,$F28<=0.7)=1,B28,0)","v":0},{"$f":"IF(AND($F28>0.7,$F28<=0.75)=1,B28,0)","v":0},{"$f":"IF(AND($F28>0.75,$F28<=0.8)=1,B28,0)","v":0},null,0,0,{"$f":"+I28*$B28","v":0},{"$f":"+J28*$B28","v":0},{"$f":"+K28*$B28","v":0},{"$f":"+L28*$B28","v":0},0,"","",{"$f":"IF(C28>=3,B28,0)","v":0}]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"",{"$f":"IF(J29>0,(PMT($J$15/12,$J$14*12,J29)*-1),\"\")","v":""},null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,{"$f":"IF(SUM(O41:R41)=\"\",\"\",SUM(O41:R41))","v":""},"",null,0,{"$f":"IF(AND($F41>0,$F41<=0.5)=1,B41,0)","v":0},{"$f":"IF(AND($F41>0,$F41<=0.65)=1,B41,0)","v":0},{"$f":"IF(AND($F41>0.65,$F41<=0.7)=1,B41,0)","v":0},{"$f":"IF(AND($F41>0.7,$F41<=0.75)=1,B41,0)","v":0},{"$f":"IF(AND($F41>0.75,$F41<=0.8)=1,B41,0)","v":0},null,0,0,0,0,0,{"$f":"+L41*$B41","v":0},0,"","",{"$f":"IF(C41>=3,B41,0)","v":0}]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,null,null,null,false,null,null,null,null,null,null,null,null,"","",null,null,null,"","",null,0,0,0,0,0,0,null,0,0,0,0,0,0,0,"","",0]
[null,{"$f":"IF(SUM(B24:B48)=\"\",\"\",SUM(B24:B48))","v":""},"Total Sources x Units =",null,null,null,null,{"$f":"IF($B49=\"\",\"\",SUM(AD24:AD48))","v":""},"","","","","",{"$f":"IF(SUM(H49:M49)=\"\",\"\",SUM(H49:M49))","v":""},null,null,null,null,null,null,null,{"$f":"SUM(V24:V48)","v":0},0,0,0,0,0,null,{"$f":"SUM(AC24:AC48)","v":0},0,0,0,0,0,0,0,0,{"$f":"SUM(AL24:AL48)","v":0}]
[]
[null,"Adjusted Sources of Funds (if sponsor provides below market-rate financing)",null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(T14=\"N\",N49,N49-J49+L67)","v":""},null,{"$f":"W49+V49","v":0},"Units with AMI ratio of 50% or less"]
[]
[null,"* Amount of AHP subsidy to be used for downpayment assistance only.",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"AJ49","v":0},"Total Homeless Units"]
[null,"** Home purchase price must equal contract sales price as indicated on final settlement statement."]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"AK49","v":0},"Total Special Needs Units"]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(COUNTIF(C24:C48,\">=3\"), \"Yes\", \"No\")","v":"No"},"Units with 3 or more bedrooms"]
[]
[null,null,null,null,null,null,"Sponsor Discounted Financing (if applicable)"]
[null,null,null,null,null,null,"Net Present Value Mortgage Calculation"]
[null,null,null,null,null,null,"(Sponsor's cash contribution)"]
[null,null,null,null,null,null,"Market Rate (Assigned by FHLB SF) ",null,null,null,null,2.6700000000000002E-2]
[null,null,null,null,null,null,"Note/Mortgage Amount",null,null,null,null,{"$f":"IF(T$14=\"N\",\"\",'Targeting & Financing Sources'!$J$49)","v":""}]
[null,null,null,null,null,null,"Mortgage Term (months) ",null,null,null,null,{"$f":"IF(T$14=\"N\",\"\",'Targeting & Financing Sources'!$J$14*12)","v":""}]
[null,null,null,null,null,null,"Sponsor's Discounted Rate",null,null,null,null,{"$f":"IF(T$14=\"N\",\"\",'Targeting & Financing Sources'!$J$15)","v":""}]
[null,null,null,null,null,null,"Mortgage P&I Payment (discounted)",null,null,null,null,{"$f":"IF(L63=0,\"\",PMT(L65/12,L64,-L63,0))","v":""}]
[null,null,null,null,null,null,"Net Present Value of Below Market Rate Mortgage",null,null,null,null,{"$f":"IF(L63=0,\"\",PV((L62/12),L64,-L66))","v":""}]
[null,null,null,null,null,null,"Adjustment for NPV of Below Market Rate Mortgage",null,null,null,null,{"$f":"L67-L63","v":0}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,"Column1"]
[]
[null,null,null,null,null,null,null,null,"Y",null,"Deferred",null,"Homeless"]
[null,null,null,null,null,null,null,null,"N",null,"Forgivable",null,"Special Needs, Seniors"]
[null,null,null,null,null,null,null,null,null,null,"Nonforgivable",null,"Special Needs, Other"]

{"sheet":"Sources of Funds Summary","index":2,"n_rows":23,"n_cols":11,"rows_kept":23,"cut":false,"hidden":false,"merged":[[20,1,20,3],[8,1,9,3],[13,1,13,3],[14,1,14,3],[16,1,16,3],[19,1,19,3],[17,1,17,3],[0,0,0,3],[1,0,1,3],[7,0,7,3],[5,0,6,3],[18,1,18,3],[4,1,4,3],[15,1,15,3],[8,0,9,0],[10,1,10,3],[11,1,11,3],[12,1,12,3]]}
["Owner-Occupied Projects: Sources of Funds Summary"]
[{"$f":"'Targeting & Financing Sources'!A2:T2","v":"Version 5.1 Updated 1/26/21"}]
[]
[]
["Project Name",{"$f":"IF('Targeting & Financing Sources'!D6=\"\",\"\",'Targeting & Financing Sources'!D6)","v":""}]
["Please provide the required information below."]
[]
["Project Financing Sources* "]
["Select             \"Yes\" or \"No\"","Does the project intend to utilize funding from the following:"]
[null,null,null,null,null,null,null,null,null,null,"Response"]
[null,"HOME",null,null,null,null,null,null,null,null,"Yes"]
[null,"Community Development Block Grant (CDBG)",null,null,null,null,null,null,null,null,"No"]
[null,"Federal Housing Administration (FHA) insured"]
[null,"Low Income Housing Tax Credits (LIHTC)"]
[null,"Other Federal Housing Programs (excluding those listed above)"]
[null,"Tax-exempt Multifamily Housing Bond"]
[null,"State Funds (excluding Federal Government program funds)"]
[null,"Local Government Funds (excluding Federal Government program funds)"]
[null,"Non-Government Donated Funds (charitable funds)"]
[null,"Member Permanent Loans"]
[null,"Other"]
[]
["* List only permanent financing sources."]

{"sheet":"Development Budget","index":3,"n_rows":52,"n_cols":11,"rows_kept":52,"cut":false,"hidden":false,"merged":[[49,1,49,3],[51,1,52,5],[53,1,53,5],[15,2,15,3],[16,1,16,3],[19,1,19,3],[20,1,20,3],[21,1,21,3],[24,1,24,3],[45,1,45,3],[47,1,47,3],[18,1,18,3],[37,1,37,3],[43,1,43,3],[29,1,29,3],[42,1,42,3],[0,0,0,5],[1,0,1,5],[11,1,11,3],[12,1,12,3],[30,1,30,3],[13,1,13,3],[14,2,14,3],[3,2,3,3],[10,1,10,3],[7,1,7,3],[9,1,9,3],[5,2,5,3],[31,1,31,3],[32,1,32,3],[28,1,28,3],[25,1,25,3],[22,1,22,3],[23,1,23,3],[26,1,26,3],[27,1,27,3],[39,2,39,3],[40,2,40,3],[41,2,41,3],[33,1,33,3],[34,1,34,3],[36,1,36,3],[35,1,35,3],[38,2,38,3]]}
["Owner-Occupied Projects: Development Budget"]
[{"$f":"'Targeting & Financing Sources'!A2:T2","v":"Version 5.1 Updated 1/26/21"}]
[]
[null,"Project Name",{"$f":"IF('Targeting & Financing Sources'!D6=\"\",\"\",'Targeting & Financing Sources'!D6)","v":""},null,"Number of Homes",{"$f":"IF('Targeting & Financing Sources'!B49=0,\"\",'Targeting & Financing Sources'!B49)","v":""}]
[]
[null,"Building Type"]
[null,null,null,null,"Total","Per Home","Pct of Budget"]
[null,"Average Square Footage",null,null,{"$f":"IF(F4=\"\",\"\",F4*F8)","v":""},null,null,null,null,null,"Condos"]
[null,null,null,null,null,null,null,null,null,null,"Single Family Homes"]
[null,"Acquisition Costs",null,null,null,null,null,null,null,null,"Townhomes"]
[null,"Land Cost (cash cost of vacant, unimproved land only)",null,null,null,{"$f":"IFERROR(IF(OR(E11=0,ISBLANK(E11)),0,E11/$F$4),0)","v":0},{"$f":"IF(OR(E11=0,E11=\"\",$E$46=0,$E$46=\"\"),\"\",(E11/$E$46))","v":""}]
[null,"Purchase of Existing Homes",null,null,null,0,{"$f":"IF(OR(E12=0,E12=\"\",$E$46=0,$E$46=\"\"),\"\",(E12/$E$46))","v":""}]
[null,"Liens and Taxes",null,null,null,0,""]
[null,"Demolition",null,null,null,0,""]
[null,"Other",null,null,null,0,""]
[null,"Other",null,null,null,0,""]
[null,"Subtotal Acquisition Costs",null,null,{"$f":"SUM(E11:E16)","v":0},{"$f":"IFERROR(IF(OR(E17=0,ISBLANK(E17)),0,E17/$F$4),0)","v":0},""]
[]
[null,"Development Costs"]
[null,"Holding Period Interest",null,null,null,{"$f":"IFERROR(IF(OR(E20=0,ISBLANK(E20)),0,E20/$F$4),0)","v":0},{"$f":"IF(OR(E20=0,E20=\"\",$E$46=0,$E$46=\"\"),\"\",(E20/$E$46))","v":""}]
[null,"Local Permits/Fees",null,null,null,0,{"$f":"IF(OR(E21=0,E21=\"\",$E$46=0,$E$46=\"\"),\"\",(E21/$E$46))","v":""}]
[null,"Architecture/Engineering",null,null,null,0,""]
[null,"Survey",null,null,null,0,""]
[null,"Construction Bond Premium",null,null,null,0,""]
[null,"Rehabilitation Construction - Labor and Materials ",null,null,null,{"$f":"IFERROR(IF(OR(E25=0,ISBLANK(E25)),0,E25/$F$4),0)","v":0},""]
[null,"New Construction - Labor and Materials ",null,null,null,{"$f":"IFERROR(IF(OR(E26=0,ISBLANK(E26)),0,E26/$F$4),0)","v":0},""]
[null,"Hard Cost Contingency ",null,null,null,0,""]
[null,"On-site Improvements",null,null,null,0,""]
[null,"Off-site Improvements",null,null,null,0,""]
[null,"Construction Loan Fee",null,null,null,0,""]
[null,"Construction Loan Interest",null,null,null,0,""]
[null,"Real Estate Taxes",null,null,null,0,""]
[null,"Appraisal",null,null,null,0,""]
[null,"Legal",null,null,null,0,""]
[null,"Construction Management (sponsor managed and project specific)",null,null,null,0,""]
[null,"Construction Management (out-sourced and paid to a third party)",null,null,null,0,""]
[null,"Environmental ",null,null,null,0,""]
[null,"Closing Costs (charged to builder/developer)",null,null,null,0,""]
[null,"Other",null,null,null,0,""]
[null,"Other",null,null,null,0,""]
[null,"Other",null,null,null,0,""]
[null,"Other",null,null,null,0,""]
[null,"Developer Fee",null,null,null,0,""]
[null,"Subtotal Development Costs",null,null,{"$f":"SUM(E20:E43)","v":0},{"$f":"IFERROR(IF(OR(E44=0,ISBLANK(E44)),0,E44/$F$4),0)","v":0},""]
[]
[null,"Total Uses of Funds",null,null,{"$f":"E44+E17","v":0},{"$f":"IFERROR(IF(OR(E46=0,ISBLANK(E46)),0,E46/$F$4),0)","v":0},""]
[]
[null,"Total Sources: Targeting & Financing Worksheet",null,null,{"$f":"IF('Targeting & Financing Sources'!N51=0,0,'Targeting & Financing Sources'!N51)","v":""}]
[]
[null,"Difference Between Sources and Uses (Should = 0)",null,null,{"$f":"IF(E46=0,0, -E46+E48)","v":0}]
[]
[null,"Please provide an explanation below if sources do not equal uses. Projects with sponsor-provided discounted financing should use the Discounted Financing Worksheet."]

{"sheet":"Discounted Financing","index":4,"n_rows":33,"n_cols":5,"rows_kept":33,"cut":false,"hidden":false,"merged":[[0,0,0,4],[1,0,1,4],[16,0,16,3],[28,0,28,3],[5,1,5,3],[26,1,26,3],[14,0,14,3],[25,1,25,3],[10,0,10,3],[7,0,7,1],[3,0,3,4],[15,0,15,3],[13,0,13,3],[12,0,12,3],[11,0,11,3],[33,0,40,4],[18,0,18,3],[20,0,20,3],[30,0,30,3],[22,0,22,3],[27,1,27,3]]}
["Owner-Occupied Projects: Discounted Financing Calculation"]
[{"$f":"'Targeting & Financing Sources'!A2","v":"Version 5.1 Updated 1/26/21"}]
[]
["This Worksheet is to be completed by sponsors providing discounted financing to homebuyers as indicated on the Targeting & Financing Sources Worksheet."]
[]
["Project Name",{"$f":"IF('Targeting & Financing Sources'!D6=\"\",\"\",'Targeting & Financing Sources'!D6)","v":""}]
[]
["Is Sponsor Providing Discounted Financing?",null,{"$f":"IF('Targeting & Financing Sources'!T14=\"Y\", \"Yes\", \"No\")","v":"No"}]
[]
["Sponsor's Discounted Financing"]
["Market Rate (Assigned by FHLBSF) ",null,null,null,{"$f":"IF(C8=\"No\",\"\",'Targeting & Financing Sources'!L62)","v":""}]
["Note/Mortgage Amount (Provided by Sponsor As Cash Contribution)",null,null,null,{"$f":"IF(E11=\"\",\"\",'Targeting & Financing Sources'!$J$49)","v":""}]
["Mortgage Term (months) ",null,null,null,{"$f":"IF(E11=\"\",\"\",'Targeting & Financing Sources'!J14*12)","v":""}]
["Sponsor's Discounted Rate",null,null,null,{"$f":"IF(E11=\"\",\"\",'Targeting & Financing Sources'!J15)","v":""}]
["Mortgage P&I Payment (discounted)",null,null,null,{"$f":"IFERROR(IF(E11=\"\",\"\",PMT(E14/12,E13,-E12,0)),0)","v":""}]
["Net Present Value of Below Market Rate Mortgage",null,null,null,{"$f":"IFERROR(IF(E11=\"\",\"\",PV((E11/12),E13,-E15)),0)","v":""}]
["Adjustment for NPV of Below Market Rate Mortgage",null,null,null,{"$f":"IFERROR(IF(E11=\"\",\"\",E16-E12),0)","v":""}]
[]
["Adjusted Sources of Funds (Including Sources from Targeting & Financing Worksheet)",null,null,null,{"$f":"IFERROR(IF(E11=\"\",\"\",'Targeting & Financing Sources'!N49+'Discounted Financing'!E17),0)","v":""}]
[]
["Total Uses of Funds (From Development Budget Worksheet)",null,null,null,{"$f":"IF(E11=\"\",\"\",'Development Budget'!E46)","v":""}]
[]
["Difference Between Adjusted Sources of Funds and Total Uses ",null,null,null,{"$f":"IF(E11=\"\",\"\",E19-E21)","v":""}]
[]
["If sources are less than uses, indicate how deficit will be covered:"]
["Other Cash Source"]
["Other Cash Source"]
["Other Cash Source"]
["Total Adjusted Sources of Funds",null,null,null,{"$f":"IF(E19=\"\",\"\",E19+E27+E28+E26)","v":""}]
[]
["Total Adjusted Sources less Total Uses (must = zero)",null,null,null,{"$f":"IF(E17=\"\",\"\",E29-E21)","v":""}]
[]
["Additional Explanations if Needed"]

{"sheet":"Empowerment Budget","index":5,"n_rows":27,"n_cols":7,"rows_kept":27,"cut":false,"hidden":false,"merged":[[26,0,26,3],[26,5,26,7],[23,0,23,3],[23,5,23,7],[24,0,24,3],[24,5,24,7],[25,0,25,3],[25,5,25,7],[20,0,20,3],[20,5,20,7],[21,0,21,3],[21,5,21,7],[22,0,22,3],[22,5,22,7],[17,0,17,3],[17,5,17,7],[18,0,18,3],[18,5,18,7],[19,0,19,3],[19,5,19,7],[16,0,16,3],[16,5,16,7],[9,0,9,3],[9,5,9,7],[10,0,10,3],[10,5,10,7],[11,0,11,3],[11,5,11,7],[12,0,12,3],[12,5,12,7],[14,0,14,3],[15,0,15,3],[15,5,15,7],[6,0,6,3],[6,5,6,7],[7,0,7,3],[7,5,7,7],[8,0,8,3],[8,5,8,7],[0,0,0,7],[1,0,1,7],[3,0,3,2],[3,4,3,5],[5,0,5,3],[5,5,5,7]]}
["Owner-Occupied Projects: Promotion of Empowerment Budget"]
[{"$f":"'Targeting & Financing Sources'!A2:T2","v":"Version 5.1 Updated 1/26/21"}]
[]
["Project Name",null,null,{"$f":"'Targeting & Financing Sources'!D6:G6","v":0},"Total Units",null,{"$f":"'Targeting & Financing Sources'!B49","v":""}]
[]
["Source of Revenue",null,null,null,"Amount","Notes"]
[]
[]
[]
[]
[]
[]
["Total Revenue",null,null,null,{"$f":"SUM(E7:E12)","v":0}]
[]
["Expenses"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Total Expenses",null,null,null,{"$f":"SUM(E16:E25)","v":0}]
["Total Revenue minus Total Expenses",null,null,null,{"$f":"E13-E26","v":0}]

{"sheet":"Benchmarks","index":6,"n_rows":14,"n_cols":7,"rows_kept":14,"cut":false,"hidden":false,"merged":[[12,2,12,4],[11,2,11,3],[0,0,0,6],[8,5,10,5],[8,6,10,6],[3,1,3,3],[8,2,10,2],[6,0,7,0],[6,2,7,4],[6,5,7,5],[6,6,7,6],[1,0,1,6],[8,4,10,4],[9,0,10,0],[9,1,10,1]]}
["Owner-Occupied Projects: Benchmarks"]
[{"$f":"'Development Budget'!A2:H2","v":"Version 5.1 Updated 1/26/21"}]
[]
["Project Name",{"$f":"IF('Targeting & Financing Sources'!D6=\"\",\"\",'Targeting & Financing Sources'!D6)","v":""}]
[]
["Criterion","Per Schedules","Feasibility Benchmark","Oversubsidization Benchmark","Within Benchmarks?","Provide detailed explanations and supporting documentation for all benchmark deviations. Deviation explanations must be quantifiable in relation to the amount the benchmark is exceeded.  Third party supporting documentation is preferred.","Check"]
["Construction costs per square foot (excluding land costs and soft costs)",{"$f":"IF('Development Budget'!C6=\"\",\"\",'Development Budget'!C6)","v":""},"To ensure consistent and reasonable cost assumptions, the per square foot construction cost should not be greater than the gross residential square footage cost estimates provided by the RSMeans Company and included in the application package."]
[null,{"$f":"IFERROR(IF('Development Budget'!F44=0,0,('Development Budget'!F25+'Development Budget'!F26+'Development Budget'!F28)/'Development Budget'!F8),\"\")","v":0}]
["Total developer fee",{"$f":"IF('Development Budget'!E43=\"\",\"$0\",IF('Development Budget'!E43=0,\"\",'Development Budget'!E43))","v":"$0"},"$0","Not greater than",{"$f":"IF(B10=\"\",\"Yes\",IF(B10>=D10,\"No\",\"Yes\"))","v":"Yes"}]
["Total developer fee as percentage of total development cost",{"$f":"IF('Development Budget'!E46=0,0,('Development Budget'!E43/'Development Budget'!E46))","v":0},null,0.12]
[null,null,null,"of total residential development costs (including developer fee, but excluding all capitalized reserves)"]
["Term of 1st mortgage (years)",{"$f":"IF('Targeting & Financing Sources'!J14=0,,'Targeting & Financing Sources'!J14)","v":0},"Not less than 15 years",null,{"$f":"IF(B12<15,\"No\",\"Yes\")","v":"No"}]
["Interest rate assumptions",{"$f":"IF('Targeting & Financing Sources'!J15=0,0,'Targeting & Financing Sources'!J15)","v":0},"Must be consistent with single-family mortgage rates in effect at the time the mortgage is funded. To ensure consistent and reasonable rate assumptions, interest rates and spreads should be set in accordance with the Interest Rate Assumptions Guidelines."]
["Number of units for which housing payment (including principal, interest, taxes, insurance, and homeowners' dues payment) as percentage of gross income is greater than 35%",{"$f":"IF('Targeting & Financing Sources'!B49=\"\",0,'Targeting & Financing Sources'!AC49)","v":0},0.35,"N/A",{"$f":"IF(B14>0, \"No\", \"Yes\")","v":"Yes"}]

{"sheet":"Targeting Score","index":7,"n_rows":14,"n_cols":4,"rows_kept":14,"cut":false,"hidden":false,"merged":[[13,3,13,4],[13,0,13,2],[3,1,3,4],[0,0,0,4],[1,0,1,4],[11,0,11,4]]}
["Owner-Occupied Projects: Targeting Score"]
[{"$f":"'Sources of Funds Summary'!A2:G2","v":"Version 5.1 Updated 1/26/21"}]
[]
["Project Name",{"$f":"IF('Targeting & Financing Sources'!D6=\"\",\"\",'Targeting & Financing Sources'!D6)","v":""}]
[]
[]
["Actual Area Median Income Ratios","Proposed No. of Units","% of Total Units"]
["Units with AMI ratio at or below 65%",{"$f":"IF('Targeting & Financing Sources'!S7=\"\",\"\",'Targeting & Financing Sources'!S7)","v":""},{"$f":"IF(B8=\"\",\"\",B8/B10)","v":""}]
["Units with AMI ratio of greater than 65%",{"$f":"IF(SUM('Targeting & Financing Sources'!S8:S10)=\"\",\"\",SUM('Targeting & Financing Sources'!S8:S10))","v":0},{"$f":"IFERROR(IF(B9=\"\",\"\",B9/B10),\"\")","v":""}]
["Total Units",{"$f":"IF('Targeting & Financing Sources'!S11=\"\",\"\",'Targeting & Financing Sources'!S11)","v":""}]
[]
["If 20% or more of the units are for households at or below 65% of AMI, 20 points are awarded. If fewer than 20% of the units are for households at or below 65% of AMI, 18 points are awarded. "]
[]
["Targeting Score",null,null,{"$f":"IF(C8=\"\",\"\",IF(C8>=20%,20,18))","v":""}]

{"sheet":"Subsidy Per Unit Score","index":8,"n_rows":18,"n_cols":5,"rows_kept":18,"cut":false,"hidden":true,"merged":[[8,0,8,2],[9,0,9,2],[10,0,10,2],[17,1,17,2],[12,1,12,2],[13,1,13,2],[14,1,14,2],[15,1,15,2],[7,0,7,2],[4,0,4,1],[6,0,6,2],[4,2,4,4],[0,0,0,4],[1,0,1,4]]}
["Owner-Occupied Projects: Subsidy Per Unit Score"]
[{"$f":"'Targeting & Financing Sources'!A2:T2","v":"Version 5.1 Updated 1/26/21"}]
[]
[]
["Project Name",null,{"$f":"IF(+'Targeting & Financing Sources'!D6=\"\",\"\",+'Targeting & Financing Sources'!D6)","v":""}]
[]
["Subsidy Requested",null,null,{"$f":"IF('Targeting & Financing Sources'!L7=\"\",0,'Targeting & Financing Sources'!L7)","v":0},"Line 1"]
["AHP Project Units (income restricted units)",null,null,{"$f":"IF('Targeting & Financing Sources'!S11=\"\",0,'Targeting & Financing Sources'!S11)","v":0},"Line 2"]
["Subsidy per Unit (line 1 divided by line 2)",null,null,{"$f":"IF(D7=0,0,D7/D8)","v":0},"Line 3"]
["High End of Owner-Occupied Project Subsidy per Unit Range",null,null,45000,"Line 4"]
["Low End of Owner-Occupied Project Subsidy per Unit Range",null,null,25000,"Line 5"]
[]
["Step 1:","Determine Subsidy per Unit Range Difference (Subtract line 5 from line 4)",null,{"$f":"+D10-D11","v":20000},"Line 6"]
["Step 2:","Determine Scoring Factor (Divide 12 possible category points by line 6)",null,{"$f":"12/D13","v":5.9999999999999995E-4},"Line 7"]
["Step 3:","Subtract line 3 by line 5",null,{"$f":"IF(D9=0,0,D9-D11)","v":0},"Line 8"]
["Step 4:","Multiply line 8 by Scoring Factor",null,{"$f":"IF(D15=0,0,D15*D14)","v":0},"Line 9"]
[]
["Step 5:","Score (12 possible points subtracted by line 9)",null,{"$f":"IF(D7=0,0,IF(12-D16>12,12,IF(12-D16<0,\"0.00\",12-D16)))","v":0}]

{"sheet":"Households Requiring Lg. Units","index":9,"n_rows":13,"n_cols":4,"rows_kept":13,"cut":false,"hidden":true,"merged":[[8,0,8,2],[10,1,10,2],[12,1,12,2],[0,0,0,3],[1,0,1,3],[4,0,4,1],[4,2,4,3],[6,0,6,2],[7,0,7,2]]}
["Owner-Occupied Projects: Housing for Large Units Score"]
[{"$f":"'Sources of Funds Summary'!A2:G2","v":"Version 5.1 Updated 1/26/21"}]
[]
[]
["Project Name",null,{"$f":"IF(+'Targeting & Financing Sources'!D6=\"\",\"\",+'Targeting & Financing Sources'!D6)","v":""}]
[]
["AHP Project Total Units ",null,null,{"$f":"IF('Targeting & Financing Sources'!B49=0,\"\",'Targeting & Financing Sources'!B49)","v":""}]
["Units with 3+ bedrooms",null,null,{"$f":"SUMIF('Targeting & Financing Sources'!C24:C48,\">=3\",'Targeting & Financing Sources'!B24:B48)","v":0}]
["Percentage of Units with 3+ Bedrooms",null,null,{"$f":"D8/D7","v":{"$error":"#VALUE!"}}]
[]
[null,"If percentage is greater than or equal to 25%, then 3 points are awarded. If less than 25%, then 0 points are awarded."]
[]
[null,"Score (0 or 3 points)",null,{"$f":"IF(D9>=25%,3,0)","v":{"$error":"#VALUE!"}}]

{"sheet":"AHP Application Comparison","index":10,"n_rows":14,"n_cols":13,"rows_kept":14,"cut":false,"hidden":false,"merged":[[4,1,4,7]]}
[null,null,null,null,null,null,null,null,"AHP Application Comparison",null,null,"Hidden to prevent data entry; field needs to be unlocked for macro to update"]
[null,null,null,null,null,null,null,null,{"$f":"'Targeting & Financing Sources'!A2","v":"Version 5.1 Updated 1/26/21"},null,null,"Status (Unlocked)","Not Yet Compared"]
[null,null,null,null,null,null,null,null,null,null,null,"Field Text Default","Not Yet Compared"]
[null,"Instructions"]
[null,"Click the \"Run Comparison Now\" button below to validate that the below referenced fields have matching values between the project's AHP Application and this Owner-Occupied AHP Application Financial Workbook."]
[]
[null,"Comparison Last Executed",{"$f":"COMPARISON_LAST_RUN","v":"Not Yet Compared"}]
[]
[null,"Field","Financial Workbook Value","EFORM_FIELD_NAME","EFORM_VALUE_IF_BLANK","EFORM_FIELD_VALUE","AHP Application Value","Match?"]
[null,"Direct Subsidy Requested",{"$f":"IF('Targeting & Financing Sources'!H49=\"\",0,'Targeting & Financing Sources'!H49)","v":0},"DIR_SUB_AMT",0,null,{"$f":"IF(F10=\"\",\"\",F10)","v":""},{"$f":"IF(I10=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,COMPARISON_DEFAULT_STATUS,IF(I10=2,\"Yes\",\"No\")))","v":"Not Yet Compared"},{"$f":"IF(C10=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,1,IF(IF(ISBLANK(C10),0,C10)=IF(ISBLANK(G10),0,G10),2,0)))","v":1}]
[null,"Total Number of Units in Project",{"$f":"IF('Targeting & Financing Sources'!B49=\"\",0,'Targeting & Financing Sources'!B49)","v":0},"TOTAL_UNITS",0,null,{"$f":"IF(F11=\"\",\"\",F11)","v":""},{"$f":"IF(I11=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,COMPARISON_DEFAULT_STATUS,IF(I11=2,\"Yes\",\"No\")))","v":"Not Yet Compared"},{"$f":"IF(C11=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,1,IF(IF(ISBLANK(C11),0,C11)=IF(ISBLANK(G11),0,G11),2,0)))","v":1}]
[null,"Number of Homeless Units",{"$f":"IF('Targeting & Financing Sources'!P53=\"\",0,'Targeting & Financing Sources'!P53)","v":0},"HOMELESS_UNITS",0,null,{"$f":"IF(F12=\"\",\"\",F12)","v":""},{"$f":"IF(I12=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,COMPARISON_DEFAULT_STATUS,IF(I12=2,\"Yes\",\"No\")))","v":"Not Yet Compared"},{"$f":"IF(C12=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,1,IF(IF(ISBLANK(C12),0,C12)=IF(ISBLANK(G12),0,G12),2,0)))","v":1}]
[null,"Total Number of Special Needs Units",{"$f":"IF('Targeting & Financing Sources'!P55=\"\",0,'Targeting & Financing Sources'!P55)","v":0},"SPECIALNEEDS_TOTAL_UNITS",0,null,{"$f":"IF(F13=\"\",\"\",F13)","v":""},{"$f":"IF(I13=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,COMPARISON_DEFAULT_STATUS,IF(I13=2,\"Yes\",\"No\")))","v":"Not Yet Compared"},{"$f":"IF(C13=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,1,IF(IF(ISBLANK(C13),0,C13)=IF(ISBLANK(G13),0,G13),2,0)))","v":1}]
[null,"Households Requiring Large Units",{"$f":"SUMIF('Targeting & Financing Sources'!C24:C48,\">=3\",'Targeting & Financing Sources'!B24:B48)","v":0},"LARGE_UNITS",0,null,{"$f":"IF(F14=\"\",\"\",F14)","v":""},{"$f":"IF(I14=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,COMPARISON_DEFAULT_STATUS,IF(I14=2,\"Yes\",\"No\")))","v":"Not Yet Compared"},{"$f":"IF(C14=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,1,IF(IF(ISBLANK(C14),0,C14)=IF(ISBLANK(G14),0,G14),2,0)))","v":1}]

{"sheet":"Data Import Summary","index":11,"n_rows":56,"n_cols":15,"rows_kept":56,"cut":false,"hidden":true,"merged":[[3,0,3,10],[28,0,28,10],[18,0,18,10],[27,0,27,10],[24,0,24,10],[23,0,23,10],[22,0,22,10],[21,0,21,10],[26,0,26,10],[31,0,31,10],[29,0,29,10],[30,0,30,10],[37,0,37,10],[36,0,36,10],[35,0,35,10],[34,0,34,10],[32,0,32,10],[33,0,33,10]]}
[]
["IRIS Tab","Question","ID ","Financial Workbook Worksheet","Cell","Additional field name details","Type","Required ","Validation","Export?","Link to application workbook","DB Field Name / \nDynamic Framework Question ID","DB Field Type  ","Dynamic Framework TABLE.COLUMN_NAME","Additional Notes"]
["Basic Review","Sources = Uses",null,"Development Budget","E50",null,"Number","Y","If cell =0 then \"Yes\",  if not equal to zero then \"No\".","Yes",{"$f":"IF('Development Budget'!$E$50=0,1,0)","v":1}]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["Sources of Funds","HOME",null,"Sources of Funds Summary","A11",null,"Drop-down ( Yes, No)","N, but one cell must say \"Yes\". ","If cell = \"Yes\" then check, if other value, then blank. ","Yes if cell = \"Yes\"",{"$f":"'Sources of Funds Summary'!$A$11","v":0}]
["Sources of Funds","CDBG",null,"Sources of Funds Summary","A12",null,"Drop-down ( Yes, No)","N, but one cell must say \"Yes\". ","If cell = \"Yes\" then check, if other value, then blank. ","Yes if cell = \"Yes\"",{"$f":"'Sources of Funds Summary'!$A$12","v":0}]
["Sources of Funds","FHA",null,"Sources of Funds Summary","A13",null,"Drop-down ( Yes, No)","N, but one cell must say \"Yes\". ","If cell = \"Yes\" then check, if other value, then blank. ","Yes if cell = \"Yes\"",{"$f":"'Sources of Funds Summary'!$A$13","v":0}]
["Sources of Funds","LIHTC",null,"Sources of Funds Summary","A14",null,"Drop-down ( Yes, No)","N, but one cell must say \"Yes\". ","If cell = \"Yes\" then check, if other value, then blank. ","Yes if cell = \"Yes\"",{"$f":"'Sources of Funds Summary'!$A$14","v":0}]
["Sources of Funds","Other Federal Housing",null,"Sources of Funds Summary","A15",null,"Drop-down ( Yes, No)","N, but one cell must say \"Yes\". ","If cell = \"Yes\" then check, if other value, then blank. ","Yes if cell = \"Yes\"",{"$f":"'Sources of Funds Summary'!$A$15","v":0}]
["Sources of Funds","Tax Exempt",null,"Sources of Funds Summary","A16",null,"Drop-down ( Yes, No)","N, but one cell must say \"Yes\". ","If cell = \"Yes\" then check, if other value, then blank. ","Yes if cell = \"Yes\"",{"$f":"'Sources of Funds Summary'!$A$16","v":0}]
["Sources of Funds","State Funds",null,"Sources of Funds Summary","A17",null,"Drop-down ( Yes, No)","N, but one cell must say \"Yes\". ","If cell = \"Yes\" then check, if other value, then blank. ","Yes if cell = \"Yes\"",{"$f":"'Sources of Funds Summary'!$A$17","v":0}]
["Sources of Funds","Local Govt",null,"Sources of Funds Summary","A18",null,"Drop-down ( Yes, No)","N, but one cell must say \"Yes\". ","If cell = \"Yes\" then check, if other value, then blank. ","Yes if cell = \"Yes\"",{"$f":"'Sources of Funds Summary'!$A$18","v":0}]
["Sources of Funds","Non-Govt",null,"Sources of Funds Summary","A19",null,"Drop-down ( Yes, No)","N, but one cell must say \"Yes\". ","If cell = \"Yes\" then check, if other value, then blank. ","Yes if cell = \"Yes\"",{"$f":"'Sources of Funds Summary'!$A$19","v":0}]
["Sources of Funds","Member Perm Loans",null,"Sources of Funds Summary","A20",null,"Drop-down ( Yes, No)","N, but one cell must say \"Yes\". ","If cell = \"Yes\" then check, if other value, then blank. ","Yes if cell = \"Yes\"",{"$f":"'Sources of Funds Summary'!$A$20","v":0}]
["Sources of Funds","Other",null,"Sources of Funds Summary","A21",null,"Drop-down ( Yes, No)","N, but one cell must say \"Yes\". ","If cell = \"Yes\" then check, if other value, then blank. ","Yes if cell = \"Yes\"",{"$f":"'Sources of Funds Summary'!$A$21","v":0}]
["Project Data","Total Development Cost",null,"Development Budget","E46",null,"Number","Y","=Value in cell","Yes",{"$f":"'Development Budget'!$E$46","v":0}]
["Project Data","Committed Sources",null,"Targeting & Financing Sources","S17",null,"Accounting","Y","=Value in cell","Yes",{"$f":"'Targeting & Financing Sources'!$S$17","v":0}]
["Project Data","3 Bedroom or More",null,"Targeting & Financing Sources","P57",null,"Number","Y","If cell = \"Yes\" then check, if other value, then blank. ","Yes, if any cell = \"Yes\". ",{"$f":"IF('Targeting & Financing Sources'!P57 = \"Yes\", 1, 0)","v":0}]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["Benchmarks","Const Cost per SF for project",null,"Benchmarks","B8",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$8","v":0}]
["Benchmarks","Building Type",null,"Benchmarks","B7",null,"Drop-down (Condos, Single Family Homes, Townhomes)","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$7","v":""}]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["Benchmarks","Developer Fee Percentage",null,"Benchmarks","B10",null,"Percentage","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$10","v":0}]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["DO NOT DELETE. REQUIRED FOR IRIS DATA IMPORT."]
["Benchmarks","Loan Term",null,"Benchmarks","B12",null,"Number","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$12","v":0}]
["Benchmarks","Interest Rate",null,"Benchmarks","B13",null,"Percentage","Y","=Value in cell ","Yes",{"$f":"Benchmarks!$B$13","v":0}]
["Application Info","Subsidy Requested",null,"Targeting & Financing Sources","L7",null,"Number","Y","=Value in cell ","No",{"$f":"'Targeting & Financing Sources'!$L$7","v":""}]
["Scoring","Housing for Households Requiring Large Units",null,"AHP Application Comparison","C14",null,"Number","Y","=Value in cell ","Yes",{"$f":"'AHP Application Comparison'!C14","v":0}]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,0]

{"sheet":"Version Control","index":12,"n_rows":31,"n_cols":6,"rows_kept":31,"cut":false,"hidden":true,"merged":[]}
["AHP Application Financial Workbook Changes Log"]
[]
["Updated","2021-01-07"]
["By","Rose Wirtz"]
[]
[]
["Financial Workbook","Version","Tab","Description of Change from Previous Version","Completed by, date","Approver"]
[]
[]
["Owner-occupied ",5,"All tabs","New FHLBSF logo and version number (5.0)"]
["Owner-occupied ",5,"Development Budget","Added \"Pct of Funds\" column for easier analysis by feasbility analyst "]
["Owner-occupied ",5,"Benchmarks","Any \"No\" response in Column E is now highlighted yellow (Conditional Formatting)"]
["Owner-occupied ",5,"Benchmarks","Construction cost per square foot formula revised to include Development Budget F28, On-site Improvements, in the calculation"]
[]
["Owner-occupied ",5.0999999999999996,"Targeting & Financing Sources","Added Units with 3+ bedrooms cell - make sure this did not impact Data Import Summary cells","J. Tsang 11/13/20"]
["Owner-occupied ",5.0999999999999996,"Large Units Score","Added Households Requiring Large Units Score tab","R. Wirtz 11/20/20"]
["Owner-occupied ",5.0999999999999996,"AHP Application Comparison ","Macro check to include large units check  - Tappan to add","J. LeSauvage (Tappan) 12/16/20"]
["Owner-occupied ",5.0999999999999996,"Subsidy per Unit","Range increased to $25,000-$45,000","J. Tsang 11/13/20"]
["Owner-occupied ",5.14,"All tabs","New version number (5.1)","R. Wirtz 12/2/20"]
["Owner-occupied ",5.0999999999999996,"Targeting Score","Change weighting: 20 points if 20% or more of units are for HH 65% and less; 18 points when <20% of units are for 65% AMI","J. Tsang 11/13/20"]
["Owner-occupied ",5.0999999999999996,"Targeting & Financing Sources","Changed AMI years to reference 2021 and 2020 instead of 2020 and 2019","R. Wirtz 11/20/20"]
["Owner-occupied ",5.0999999999999996,"Targeting & Financing Sources","Added data validation to column F to prevent users from inputting AMIs over 80%","R. Wirtz 11/20/20"]
["Owner-occupied ",5.0999999999999996,"All tabs","De-linked external source","R. Wirtz 12/2/20"]
["Owner-occupied ",5.0999999999999996,"Targeting Score","Changed formatting of tab and added formulas for units over 65%","R. Wirtz 12/2/20"]
["Owner-occupied ",5.0999999999999996,"Application Comparison","Added row for Households Requiring Large Units","R. Wirtz 12/2/20"]
["Owner-occupied ",5.0999999999999996,"Data Import Summary","Changed 3-Bedroom question to link to cell P57 instead of cells C24:C28","R. Wirtz 12/2/20"]
["Owner-occupied ",5.0999999999999996,"All tabs","Hid Subsidy/Unit and Households Requiring Large Units tabs","R. Wirtz 12/2/20"]
["Owner-occupied ",5.0999999999999996,"Data Import Summary","Added row to import data for # of Households Requiring Large Units","J. Tsang 12/8/20"]
["Owner-occupied ",5.0999999999999996,"Workbook Instructions and Notes","Removed section on Subsidy per Unit tab","R. Wirtz 12/30/20"]
["Owner-occupied ",5.0999999999999996,"All tabs","Updated date to 1/26, date applications published","R. Wirtz 12/30/20"]
["Owner-occupied ",5.0999999999999996,"Targeting & Financing Sources","Updated discounted financing rate","R. Wirtz 1/7/21"]