{"text_format":"1","workbook":"xlsx","n_sheets":14,"sheets":["Workbook Instructions and Notes","Targeting","Sources & Uses of Funds","Sources of Funds Summary","15-Year Op Pro Forma","15-Year Commercial Op Pro Forma","Empowerment Budget","Benchmarks","Targeting Self-Score","Subsidy Per Unit Score","Households Requiring Lg. Units","AHP Application Comparison","Data Import Summary","Version Control"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":2,"defined_names":28,"comments":3,"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":66,"n_cols":2,"rows_kept":66,"cut":false,"hidden":false,"merged":[]}
[null,"Rental AHP Application Financial Workbook"]
[null,{"$f":"Targeting!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 Worksheet"]
["1.","Enter the annual 50% (very-low) AMI, adjusted for family size, based on the HUD income limit guidelines."]
["2.","Attach a copy of the relevant HUD Income Limits page showing the income data for this project."]
["3.","If applicable, indicate which units are reserved for Homeless, Special Needs, or both. Numbers of units reserved must be consistent with the Homeless and Special Needs scoring categories to receive scoring credit."]
["4.","For transitional housing, enter the number of projected households as the number of units."]
["5.","Under bedroom size, for studios, select “0” as the number of bedrooms. "]
["6.","Do not input operating subsidies as rental subsidies (see 15-Year Operating Pro Forma)."]
["7.","Always note non-income restricted units and manager's units."]
["8.","Enter the scheduled rent and rental subsidy for each type of unit. AHP guidelines require that the rent paid by each household (excluding utilities) not exceed 30% of the targeted income for the unit. The farthest column to the right shows if a unit passes or fails this “30% Rent Test.”"]
["9.","For occupied rental projects, households must be income-qualified to meet the AHP targeting commitments at one of two points in time:\n• Upon initial occupancy after completion of acquisition or rehab for projects with an approved relocation plan, or\n• At application for projects without an approved relocation plan.\n\nThis means that grandfathering-in households who were income-qualified at a move-in date prior to the AHP application is not allowed. For households whose incomes have increased during their tenure at the project, and now exceed the unit's original targeting designation, targeting commitments in the AHP application should reflect current household income. For example, the targeting for a unit where the household was income-qualified for a 50% AMI unit at move-in in 2015 who is now earning 80% AMI should be set at 80% AMI, not 50% AMI."]
["Sources and Uses Worksheet"]
["1.","Enter the project's total residential square footage which may include common areas and residential podium parking."]
["2.","Enter tax credit sales price, if applicable."]
["3.","Select construction type, either \"New Construction\" and/or \"Rehab.\" Also select the building type, \"Apartments,” \"Single Family Homes,” or \"Townhomes.\""]
["4.","Do not include non-cash \"costs,\" that are both a use and a source, e.g. donated land, appraised value of land, accrued soft debt interest, volunteer labor, pro-bono services, fee waivers, expenses to be reimbursed."]
["5.","Ensure purchase price/acquisition cost is consistent with readiness and donation sections in the AHP Application and that documentation is provided."]
["6.","If the property owner, lessee, or sponsor acquires the project property from a party, affiliated in any way with the property owner, lessee, sponsor, or any other persons or entities involved in the project, in a non-arm’s length transaction, the Bank will consider such acquisition to be a related party transaction. When evaluating related party transactions, the Bank may consider any net cash gain from the transaction as excess sources of funds, unless the gain is: 1) contributed to the project as owner equity, 2) provided to the project as carry-back financing, or 3) used to retire existing debt on the property."]
["7.","For rehabilitation of existing affordable housing projects, not involving the acquisition of property, acquisition costs (i.e., those costs reflected on the Sources and Uses Worksheet) will not be included in the total development cost for the purpose of determining the allowable developer fee. "]
["8.","Capitalized reserves (such as operating, replacement, transition, or social service reserves) must be paid for by sources other than AHP subsidy or conventional financing."]
["9.","Do not include California's Mental Health Service Act Capitalized Operating Subsidy Reserve as a development cost if the reserve is not held by the project."]
["10.","If capitalized partnership management, asset management, and/or investor service fee reserves are included in the budget, provide an explanation and third-party documentation justifying why the capitalized fees are required. Excessive and/or unnecessary reserves may indicate that the project does not demonstrate a need for the AHP award. The Bank, at its discretion, will determine whether the reserves are acceptable and appear necessary for project feasibility."]
["11.","Be sure to indicate all financing details, hard, soft, rates etc., of all permanent debt. The spreadsheet fully amortizes all debt. If necessary, alter worksheet columns to properly reflect simple interest or interest only payments. If financing is soft debt, loan term cells will be grayed out and details do not need to be included."]
["12.","Hard debt is defined as contractually obligated debt requiring repayment from project operating cash flow, excluding residual receipts-based loans and soft debt fee payments."]
["13.","The Annual Debt Service calculation is editable in the last column to allow for interest-only debt payments or other mandatory fee payments to be added to total debt service if needed."]
["14.","To ensure consistent and reasonable rate assumptions, interest rates and spreads should be set in accordance with the Interest Rate Assumptions Guidelines."]
["15.","Indicate whether funds have been approved or not approved at the bottom of the worksheet."]
["16.","Documentation of approved sources of financing must be provided."]
["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.”"]
["15-Year Operating Pro Forma Worksheet"]
["1.","All costs except for the replacement reserves trend upward. All unshaded cells may be edited."]
["2.","Indicate if the project is receiving project-based rental subsidy and the status of the rental subsidy commitment, if applicable. Submit approval letter or rental subsidy agreement with Evidence of Committed Financing documentation."]
["3.","Three lines are included for \"Other\" expenses. Provide details for these \"Other\" expenses. "]
["4.","Enter residual receipt payments, any \"below the line\" partnership management fees, or deferred fees paid from cash flow on the appropriate lines."]
["5.","Adjust debt service if loan payments change during the 15-year retention period."]
["6.","Social service costs cannot be included as an operating expense."]
["15-Year Commercial Operating Pro Forma Worksheet"]
["1.","Complete this worksheet if the project includes income-generating commercial space."]
["2.","If your project has permanent debt and the permanent loan has already been underwritten, indicate whether the lender included commercial income in their underwriting and sizing of the permanent loan."]
["3.","Indicate whether the commercial lease will be triple net (NNN)."]
["4.","Enter estimated commercial rents from all potential commercial tenants. If the commercial space will be master leased, enter the estimated amount of the master lease payments."]
["5.","Enter all commercial expenses in the lines provided. Three lines are included for “Other” expenses. Provide details for these “Other” expenses."]
["6.","If hard debt was used to pay for commercial development costs, include the amount of debt service that the commercial portion of the building will carry."]
["7","Total effective commercial gross income and total commercial expenses will automatically populate in the 15-year Operating Pro Forma worksheet based on what you enter in the Commercial Operating Pro Forma."]
["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 resident services described in the promotion of empowerment section of 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” in Column E. 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, Capitalized Replacement Reserve, and Capitalized Operating Reserve benchmark deviations, 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 RSMeans list of construction costs per square-foot by building type and location."]
["Targeting Self-Score Worksheet"]
["1.","Enter AMI target(s) other than 50% to determine the project’s Targeting score."]
["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 workbook)."]
["2.","If there are any “No’s” in the “Match” column, review and edit the responses on the application and financial workbook to ensure consistent data between the two documents. "]

{"sheet":"Targeting","index":1,"n_rows":57,"n_cols":34,"rows_kept":57,"cut":false,"hidden":false,"merged":[[0,0,0,13],[1,0,1,13],[4,8,4,11],[13,2,17,2],[13,0,17,0],[16,6,17,6],[16,11,17,11],[8,0,11,0],[4,1,4,6],[13,1,17,1],[13,4,17,4]]}
["Rental Projects: Targeting"]
["Version 5.1 Updated 1/26/21"]
[]
[]
["Project Name",null,null,null,null,null,null,"MSA/ PMSA",null,null,null,null,"State"]
[]
[]
[]
["2021 (if unavailable, use 2020)","Household size",null,null,"1 Person","2 Person","3 Person","4 Person","5 Person","6 person","7 Person","8 Person"]
[null,"50% AMI (HUD Very Low)"]
[null,"100% Area Median Income",null,null,{"$f":"E10*2","v":0},{"$f":"F10*2","v":0},0,0,{"$f":"I10*2","v":0},0,0,0]
[null,"Half-person",null,null,{"$f":"IF(E11=\"\",\"\",IF(E11<>0,AVERAGEA(E11:F11),0))","v":""},{"$f":"IF(F11=\"\",\"\",IF(F11<>0,AVERAGEA(F11:G11),0))","v":""},"","",{"$f":"IF(I11=\"\",\"\",IF(I11<>0,AVERAGEA(I11:J11),0))","v":""},"",""]
[]
["Number of Units ","Homeless","Special Needs",null,"Bedrooms per Unit"]
[null,null,null,"Weighted ",null," Assumed","Target","AMI","Target",0.3,"Total Rent","Rental","Rent","30% Rent"]
[null,null,null,"Average ",null,"Occupancy","AMI %","Adjusted for","Income","Rent","Received","Subsidy","Paid by","Test"]
[null,null,null,"Calculation",null,"per Unit",null,"Family Size","for Unit","Cap","for Unit",null,"Household","Pass/Fail"]
[null,null,null,null,null,"(1.5 persons/bedroom)",null,null,null,null,null,null,null,null,null,"Rent","1/2 Person","VLI Units","2nd Line","3rd Line","4th Line","5th Line","6th Line","7th Line",null,null,"Homeless","Special Needs","3+ Br Units",null,"Unit Subs","Net Rent"]
[null,null,null,{"$f":"A19*E19","v":0},null,{"$f":"IF(A19=\"\",\"\",IF(A19>0,IF(E19=0,1,(E19*1.5))))","v":""},null,{"$f":"IF(F19=1,$E$11,IF(F19=2,$F$11,IF(F19=3,$G$11,IF(F19=4,$H$11,IF(F19=5,$I$11,IF(F19=6,$J$11,IF(F19=7,$K$11,IF(F19=8,$L$11,Q19))))))))","v":0},{"$f":"IF(G19=\"\",\"\",(G19*H19))","v":""},{"$f":"IF(I19=\"\",\"\",+I19/12*0.3)","v":""},null,null,{"$f":"IF($K19=\"\",\"\",K19-L19)","v":""},{"$f":"IF(A19<>\"\",IF(K19>=0,IF((K19-L19)>J19,\"Fail\",\"Pass\")),\" \")","v":" "},null,{"$f":"+K19*A19","v":0},{"$f":"IF(F19=1.5,$E$12,IF(F19=2.5,$F$12,IF(F19=3.5,$G$12,IF(F19=4.5,$H$12,IF(F19=5.5,$I$12,IF(F19=6.5,$J$12,IF(F19=7.5,$K$12,0)))))))","v":0},{"$f":"IF(AND(G19>0,G19<=0.5),A19,0)","v":0},{"$f":"IF(G19='Targeting Self-Score'!$D$6,A19,0)","v":0},{"$f":"IF(G19='Targeting Self-Score'!$D$7,A19,0)","v":0},{"$f":"IF(G19='Targeting Self-Score'!$D$8,Targeting!A19,0)","v":0},{"$f":"IF(G19='Targeting Self-Score'!$D$9,Targeting!A19,0)","v":0},{"$f":"IF(G19='Targeting Self-Score'!$D$10,Targeting!A19,0)","v":0},{"$f":"IF(G19='Targeting Self-Score'!$D$11,Targeting!A19,0)","v":0},{"$f":"(E19*2)+1","v":1},null,{"$f":"IF($B19=\"No\",\"\",IF($B19=\"\",\"\",$A19))","v":""},{"$f":"IF($C19=\"\",0,$A19)","v":0},{"$f":"IF(E19>=3,A19,0)","v":0},null,{"$f":"(A19*L19)*12","v":0},{"$f":"M19*12*A19","v":0},null,0]
[null,null,null,0,null,{"$f":"IF(A20=\"\",\"\",IF(A20>0,IF(E20=0,1,(E20*1.5))))","v":""},null,{"$f":"IF(F20=1,$E$11,IF(F20=2,$F$11,IF(F20=3,$G$11,IF(F20=4,$H$11,IF(F20=5,$I$11,IF(F20=6,$J$11,IF(F20=7,$K$11,IF(F20=8,$L$11,Q20))))))))","v":0},{"$f":"IF(G20=\"\",\"\",(G20*H20))","v":""},{"$f":"IF(I20=\"\",\"\",+I20/12*0.3)","v":""},null,null,{"$f":"IF($K20=\"\",\"\",K20-L20)","v":""},{"$f":"IF(A20<>\"\",IF(K20>=0,IF((K20-L20)>J20,\"Fail\",\"Pass\")),\" \")","v":" "},null,0,{"$f":"IF(F20=1.5,$E$12,IF(F20=2.5,$F$12,IF(F20=3.5,$G$12,IF(F20=4.5,$H$12,IF(F20=5.5,$I$12,IF(F20=6.5,$J$12,IF(F20=7.5,$K$12,0)))))))","v":0},{"$f":"IF(AND(G20>0,G20<=0.5),A20,0)","v":0},{"$f":"IF(G20='Targeting Self-Score'!$D$6,A20,0)","v":0},{"$f":"IF(G20='Targeting Self-Score'!$D$7,A20,0)","v":0},{"$f":"IF(G20='Targeting Self-Score'!$D$8,Targeting!A20,0)","v":0},{"$f":"IF(G20='Targeting Self-Score'!$D$9,Targeting!A20,0)","v":0},{"$f":"IF(G20='Targeting Self-Score'!$D$10,Targeting!A20,0)","v":0},{"$f":"IF(G20='Targeting Self-Score'!$D$11,Targeting!A20,0)","v":0},1,null,{"$f":"IF($B20=\"No\",\"\",IF($B20=\"\",\"\",$A20))","v":""},{"$f":"IF($C20=\"\",0,$A20)","v":0},0,null,{"$f":"(A20*L20)*12","v":0},0,null,1]
[null,null,null,0,null,"",null,{"$f":"IF(F21=1,$E$11,IF(F21=2,$F$11,IF(F21=3,$G$11,IF(F21=4,$H$11,IF(F21=5,$I$11,IF(F21=6,$J$11,IF(F21=7,$K$11,IF(F21=8,$L$11,Q21))))))))","v":0},"","",null,null,{"$f":"IF($K21=\"\",\"\",K21-L21)","v":""}," ",null,0,0,0,{"$f":"IF(G21='Targeting Self-Score'!$D$6,A21,0)","v":0},{"$f":"IF(G21='Targeting Self-Score'!$D$7,A21,0)","v":0},{"$f":"IF(G21='Targeting Self-Score'!$D$8,Targeting!A21,0)","v":0},{"$f":"IF(G21='Targeting Self-Score'!$D$9,Targeting!A21,0)","v":0},{"$f":"IF(G21='Targeting Self-Score'!$D$10,Targeting!A21,0)","v":0},{"$f":"IF(G21='Targeting Self-Score'!$D$11,Targeting!A21,0)","v":0},1,null,"",0,0,null,0,0,null,2]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G22='Targeting Self-Score'!$D$6,A22,0)","v":0},{"$f":"IF(G22='Targeting Self-Score'!$D$7,A22,0)","v":0},{"$f":"IF(G22='Targeting Self-Score'!$D$8,Targeting!A22,0)","v":0},{"$f":"IF(G22='Targeting Self-Score'!$D$9,Targeting!A22,0)","v":0},{"$f":"IF(G22='Targeting Self-Score'!$D$10,Targeting!A22,0)","v":0},{"$f":"IF(G22='Targeting Self-Score'!$D$11,Targeting!A22,0)","v":0},1,null,"",0,0,null,0,0,null,3]
[null,null,null,{"$f":"A23*E23","v":0},null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G23='Targeting Self-Score'!$D$6,A23,0)","v":0},{"$f":"IF(G23='Targeting Self-Score'!$D$7,A23,0)","v":0},{"$f":"IF(G23='Targeting Self-Score'!$D$8,Targeting!A23,0)","v":0},{"$f":"IF(G23='Targeting Self-Score'!$D$9,Targeting!A23,0)","v":0},{"$f":"IF(G23='Targeting Self-Score'!$D$10,Targeting!A23,0)","v":0},{"$f":"IF(G23='Targeting Self-Score'!$D$11,Targeting!A23,0)","v":0},1,null,"",0,0,null,0,0,null,4]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G24='Targeting Self-Score'!$D$6,A24,0)","v":0},{"$f":"IF(G24='Targeting Self-Score'!$D$7,A24,0)","v":0},{"$f":"IF(G24='Targeting Self-Score'!$D$8,Targeting!A24,0)","v":0},{"$f":"IF(G24='Targeting Self-Score'!$D$9,Targeting!A24,0)","v":0},{"$f":"IF(G24='Targeting Self-Score'!$D$10,Targeting!A24,0)","v":0},{"$f":"IF(G24='Targeting Self-Score'!$D$11,Targeting!A24,0)","v":0},1,null,"",0,0,null,0,0,null,5]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G25='Targeting Self-Score'!$D$6,A25,0)","v":0},{"$f":"IF(G25='Targeting Self-Score'!$D$7,A25,0)","v":0},{"$f":"IF(G25='Targeting Self-Score'!$D$8,Targeting!A25,0)","v":0},{"$f":"IF(G25='Targeting Self-Score'!$D$9,Targeting!A25,0)","v":0},{"$f":"IF(G25='Targeting Self-Score'!$D$10,Targeting!A25,0)","v":0},{"$f":"IF(G25='Targeting Self-Score'!$D$11,Targeting!A25,0)","v":0},1,null,"",0,0,null,0,0]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G26='Targeting Self-Score'!$D$6,A26,0)","v":0},{"$f":"IF(G26='Targeting Self-Score'!$D$7,A26,0)","v":0},{"$f":"IF(G26='Targeting Self-Score'!$D$8,Targeting!A26,0)","v":0},{"$f":"IF(G26='Targeting Self-Score'!$D$9,Targeting!A26,0)","v":0},{"$f":"IF(G26='Targeting Self-Score'!$D$10,Targeting!A26,0)","v":0},{"$f":"IF(G26='Targeting Self-Score'!$D$11,Targeting!A26,0)","v":0},1,null,"",0,0,null,0,0]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G27='Targeting Self-Score'!$D$6,A27,0)","v":0},{"$f":"IF(G27='Targeting Self-Score'!$D$7,A27,0)","v":0},{"$f":"IF(G27='Targeting Self-Score'!$D$8,Targeting!A27,0)","v":0},{"$f":"IF(G27='Targeting Self-Score'!$D$9,Targeting!A27,0)","v":0},{"$f":"IF(G27='Targeting Self-Score'!$D$10,Targeting!A27,0)","v":0},{"$f":"IF(G27='Targeting Self-Score'!$D$11,Targeting!A27,0)","v":0},1,null,"",0,0,null,0,0]
[null,null,null,0,null,"",null,0,{"$f":"IF(G28=\"\",\"\",(G28*H28))","v":""},{"$f":"IF(I28=\"\",\"\",+I28/12*0.3)","v":""},null,null,""," ",null,0,0,0,{"$f":"IF(G28='Targeting Self-Score'!$D$6,A28,0)","v":0},{"$f":"IF(G28='Targeting Self-Score'!$D$7,A28,0)","v":0},{"$f":"IF(G28='Targeting Self-Score'!$D$8,Targeting!A28,0)","v":0},{"$f":"IF(G28='Targeting Self-Score'!$D$9,Targeting!A28,0)","v":0},{"$f":"IF(G28='Targeting Self-Score'!$D$10,Targeting!A28,0)","v":0},{"$f":"IF(G28='Targeting Self-Score'!$D$11,Targeting!A28,0)","v":0},1,null,"",0,0,null,0,0]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G29='Targeting Self-Score'!$D$6,A29,0)","v":0},{"$f":"IF(G29='Targeting Self-Score'!$D$7,A29,0)","v":0},{"$f":"IF(G29='Targeting Self-Score'!$D$8,Targeting!A29,0)","v":0},{"$f":"IF(G29='Targeting Self-Score'!$D$9,Targeting!A29,0)","v":0},{"$f":"IF(G29='Targeting Self-Score'!$D$10,Targeting!A29,0)","v":0},{"$f":"IF(G29='Targeting Self-Score'!$D$11,Targeting!A29,0)","v":0},1,null,"",0,0,null,0,0]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G30='Targeting Self-Score'!$D$6,A30,0)","v":0},{"$f":"IF(G30='Targeting Self-Score'!$D$7,A30,0)","v":0},{"$f":"IF(G30='Targeting Self-Score'!$D$8,Targeting!A30,0)","v":0},{"$f":"IF(G30='Targeting Self-Score'!$D$9,Targeting!A30,0)","v":0},{"$f":"IF(G30='Targeting Self-Score'!$D$10,Targeting!A30,0)","v":0},{"$f":"IF(G30='Targeting Self-Score'!$D$11,Targeting!A30,0)","v":0},1,null,"",0,0,null,0,0]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G31='Targeting Self-Score'!$D$6,A31,0)","v":0},{"$f":"IF(G31='Targeting Self-Score'!$D$7,A31,0)","v":0},{"$f":"IF(G31='Targeting Self-Score'!$D$8,Targeting!A31,0)","v":0},{"$f":"IF(G31='Targeting Self-Score'!$D$9,Targeting!A31,0)","v":0},{"$f":"IF(G31='Targeting Self-Score'!$D$10,Targeting!A31,0)","v":0},{"$f":"IF(G31='Targeting Self-Score'!$D$11,Targeting!A31,0)","v":0},1,null,"",0,0,null,0,0]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G32='Targeting Self-Score'!$D$6,A32,0)","v":0},{"$f":"IF(G32='Targeting Self-Score'!$D$7,A32,0)","v":0},{"$f":"IF(G32='Targeting Self-Score'!$D$8,Targeting!A32,0)","v":0},{"$f":"IF(G32='Targeting Self-Score'!$D$9,Targeting!A32,0)","v":0},{"$f":"IF(G32='Targeting Self-Score'!$D$10,Targeting!A32,0)","v":0},{"$f":"IF(G32='Targeting Self-Score'!$D$11,Targeting!A32,0)","v":0},1,null,"",0,0,null,0,0]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G33='Targeting Self-Score'!$D$6,A33,0)","v":0},{"$f":"IF(G33='Targeting Self-Score'!$D$7,A33,0)","v":0},{"$f":"IF(G33='Targeting Self-Score'!$D$8,Targeting!A33,0)","v":0},{"$f":"IF(G33='Targeting Self-Score'!$D$9,Targeting!A33,0)","v":0},{"$f":"IF(G33='Targeting Self-Score'!$D$10,Targeting!A33,0)","v":0},{"$f":"IF(G33='Targeting Self-Score'!$D$11,Targeting!A33,0)","v":0},1,null,"",0,0,null,0,0]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G34='Targeting Self-Score'!$D$6,A34,0)","v":0},{"$f":"IF(G34='Targeting Self-Score'!$D$7,A34,0)","v":0},{"$f":"IF(G34='Targeting Self-Score'!$D$8,Targeting!A34,0)","v":0},{"$f":"IF(G34='Targeting Self-Score'!$D$9,Targeting!A34,0)","v":0},{"$f":"IF(G34='Targeting Self-Score'!$D$10,Targeting!A34,0)","v":0},{"$f":"IF(G34='Targeting Self-Score'!$D$11,Targeting!A34,0)","v":0},1,null,"",0,0,null,0,0]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,0,{"$f":"IF(G35='Targeting Self-Score'!$D$6,A35,0)","v":0},{"$f":"IF(G35='Targeting Self-Score'!$D$7,A35,0)","v":0},{"$f":"IF(G35='Targeting Self-Score'!$D$8,Targeting!A35,0)","v":0},{"$f":"IF(G35='Targeting Self-Score'!$D$9,Targeting!A35,0)","v":0},{"$f":"IF(G35='Targeting Self-Score'!$D$10,Targeting!A35,0)","v":0},{"$f":"IF(G35='Targeting Self-Score'!$D$11,Targeting!A35,0)","v":0},1,null,"",0,0,null,0,0]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,{"$f":"IF(AND(G36>0,G36<=0.5),A36,0)","v":0},{"$f":"IF(G36='Targeting Self-Score'!$D$6,A36,0)","v":0},{"$f":"IF(G36='Targeting Self-Score'!$D$7,A36,0)","v":0},{"$f":"IF(G36='Targeting Self-Score'!$D$8,Targeting!A36,0)","v":0},{"$f":"IF(G36='Targeting Self-Score'!$D$9,Targeting!A36,0)","v":0},{"$f":"IF(G36='Targeting Self-Score'!$D$10,Targeting!A36,0)","v":0},{"$f":"IF(G36='Targeting Self-Score'!$D$11,Targeting!A36,0)","v":0},{"$f":"(E36*2)+1","v":1},null,"",{"$f":"IF($C36=\"\",0,$A36)","v":0},{"$f":"IF(E36>=3,A36,0)","v":0},null,{"$f":"(A36*L36)*12","v":0},{"$f":"M36*12*A36","v":0}]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,{"$f":"IF(AND(G37>0,G37<=0.5),A37,0)","v":0},{"$f":"IF(G37='Targeting Self-Score'!$D$6,A37,0)","v":0},{"$f":"IF(G37='Targeting Self-Score'!$D$7,A37,0)","v":0},{"$f":"IF(G37='Targeting Self-Score'!$D$8,Targeting!A37,0)","v":0},{"$f":"IF(G37='Targeting Self-Score'!$D$9,Targeting!A37,0)","v":0},{"$f":"IF(G37='Targeting Self-Score'!$D$10,Targeting!A37,0)","v":0},{"$f":"IF(G37='Targeting Self-Score'!$D$11,Targeting!A37,0)","v":0},{"$f":"(E37*2)+1","v":1},null,"",0,{"$f":"IF(E37>=3,A37,0)","v":0},null,{"$f":"(A37*L37)*12","v":0},{"$f":"M37*12*A37","v":0}]
[null,null,null,0,null,"",null,0,"","",null,null,""," ",null,0,0,{"$f":"IF(AND(G38>0,G38<=0.5),A38,0)","v":0},{"$f":"IF(G38='Targeting Self-Score'!$D$6,A38,0)","v":0},{"$f":"IF(G38='Targeting Self-Score'!$D$7,A38,0)","v":0},{"$f":"IF(G38='Targeting Self-Score'!$D$8,Targeting!A38,0)","v":0},{"$f":"IF(G38='Targeting Self-Score'!$D$9,Targeting!A38,0)","v":0},{"$f":"IF(G38='Targeting Self-Score'!$D$10,Targeting!A38,0)","v":0},{"$f":"IF(G38='Targeting Self-Score'!$D$11,Targeting!A38,0)","v":0},{"$f":"(E38*2)+1","v":1},null,"",0,{"$f":"IF(E38>=3,A38,0)","v":0},null,{"$f":"(A38*L38)*12","v":0},{"$f":"M38*12*A38","v":0}]
[null,null,null,{"$f":"A39*E39","v":0},null,"",null,0,"","",null,null,""," ",null,0,0,{"$f":"IF(AND(G39>0,G39<=0.5),A39,0)","v":0},{"$f":"IF(G39='Targeting Self-Score'!$D$6,A39,0)","v":0},{"$f":"IF(G39='Targeting Self-Score'!$D$7,A39,0)","v":0},{"$f":"IF(G39='Targeting Self-Score'!$D$8,Targeting!A39,0)","v":0},{"$f":"IF(G39='Targeting Self-Score'!$D$9,Targeting!A39,0)","v":0},{"$f":"IF(G39='Targeting Self-Score'!$D$10,Targeting!A39,0)","v":0},{"$f":"IF(G39='Targeting Self-Score'!$D$11,Targeting!A39,0)","v":0},{"$f":"(E39*2)+1","v":1},null,"",{"$f":"IF($C39=\"\",0,$A39)","v":0},{"$f":"IF(E39>=3,A39,0)","v":0},null,{"$f":"(A39*L39)*12","v":0},{"$f":"M39*12*A39","v":0}]
[{"$f":"IF(SUM(A19:A39)=\"\",\"\",SUM(A19:A39))","v":""},"Income-restricted units",null,null,null,null,null,null,null,null,{"$f":"IF(P40=\"\",\"\",+P40*12)","v":""},{"$f":"IF(AE40=0,\"\",AE40)","v":""},{"$f":"IF(AF40=0,\"\",AF40)","v":""},null,null,{"$f":"SUM(P19:P39)","v":0},null,{"$f":"SUM(R19:R39)","v":0},0,0,0,0,0,0,null,null,null,null,{"$f":"SUM(AC19:AC39)","v":0},null,{"$f":"SUM(AE19:AE39)","v":0},{"$f":"SUM(AF19:AF39)","v":0}]
[0,"Income-restricted manager units (included above)"]
[0,"Non income-restricted manager unit(s)"]
[0,"Non income-restricted special needs unit(s)"]
[0,"Other non income-restricted unit(s)"]
[{"$f":"IF((+A40+A42+A44+A43)=0,\"\",+A40+A42+A44+A43)","v":""},"Total units in project (non-income restricted and restricted)"]
[{"$f":"IF(A40=\"\",\"\",IF(A40>0,R40/A45,0))","v":""},"Percentage of units targeted to 50% AMI or below (must be at least 20%)"]
[{"$f":"IF(SUM(AA$19:AA$41)=0,\"\",SUM(AA$19:AA$41))","v":""},"Homeless units"]
[{"$f":"IF(SUM(AB$19:AB$41)+A43=0,\"\",SUM(AB$19:AB$41)+A43)","v":""},"Special needs units"]
[{"$f":"IF(A45=\"\",\"\",SUM(D19:D39)/A40)","v":""},"Average bedrooms per unit"]
[{"$f":"IF(A40=\"\",\"\",SUMPRODUCT(G19:G39,A19:A39)/SUM(A19:A39))","v":""},"Weighted Average Target AMI %"]
[{"$f":"SUMIF(E19:E39,\">=3\",A19:A39)","v":0},"Units with 3+ bedrooms"]
[]
[]
[]
[]
[null,null,null,null,null,null,null,"Yes","Seniors"]
[null,null,null,null,null,null,null,"No","Other"]

{"sheet":"Sources & Uses of Funds","index":2,"n_rows":109,"n_cols":20,"rows_kept":109,"cut":false,"hidden":false,"merged":[[61,0,61,2],[60,0,60,2],[31,0,31,2],[32,0,32,2],[33,0,33,2],[48,0,48,2],[50,0,50,2],[51,0,51,1],[49,0,49,2],[46,0,46,2],[39,0,39,2],[37,0,37,2],[34,0,34,2],[55,0,55,2],[56,0,56,2],[57,0,57,1],[59,0,59,2],[52,0,52,2],[53,0,53,2],[47,0,47,1],[58,0,58,2],[54,0,54,2],[77,0,77,2],[62,0,62,2],[87,0,87,2],[73,0,73,2],[79,0,79,1],[86,0,86,2],[64,0,64,2],[75,0,75,2],[68,0,68,2],[80,0,80,2],[81,0,81,2],[84,0,84,2],[85,0,85,2],[82,0,82,2],[83,0,83,2],[69,0,69,2],[67,0,67,2],[70,0,70,2],[71,0,71,2],[76,0,76,2],[78,0,78,2],[72,0,72,1],[74,0,74,2],[0,0,0,14],[1,0,1,14],[8,0,8,2],[15,0,15,2],[38,0,38,2],[44,0,44,2],[45,0,45,2],[43,0,43,2],[35,0,35,2],[30,0,30,2],[40,0,40,2],[42,0,42,2],[19,0,19,2],[23,0,23,2],[22,0,22,2],[29,0,29,2],[27,0,27,2],[36,0,36,2],[41,0,41,1],[26,0,26,2],[25,0,25,2],[14,0,14,2],[12,0,12,2],[10,0,10,2],[6,14,7,14],[6,13,7,13],[6,6,7,6],[3,12,3,13],[6,12,7,12],[6,7,7,7],[5,7,5,14],[6,3,7,3],[6,5,7,5],[6,11,7,11],[5,4,5,6],[6,8,7,8],[3,5,3,6],[3,8,3,10],[6,9,7,9],[6,10,7,10],[3,2,3,4],[88,0,88,1],[3,0,3,1],[20,0,20,2],[28,0,28,2],[21,0,21,2],[6,15,7,15],[108,9,108,13],[13,0,13,2],[6,4,7,4],[11,0,11,2],[18,0,18,2],[16,0,16,2],[17,0,17,2],[9,0,9,2],[92,0,92,2],[97,2,97,3],[96,5,96,6],[94,3,94,4],[65,0,65,2],[66,0,66,2],[91,0,91,2],[93,0,93,2],[89,0,89,2],[63,0,63,2]]}
["Rental Projects: Sources & Uses of Funds"]
[{"$f":"Targeting!A2:M2","v":"Version 5.1 Updated 1/26/21"}]
[]
["Project Name",null,{"$f":"IF(+Targeting!B5=\"\",\"\",+Targeting!B5)","v":""},null,null,"Total Units",null,{"$f":"Targeting!A45","v":""},"Residential Square Footage",null,null,null,"Tax Credit Sales Price"]
[]
[null,null,"Select From Below",null,null,null,null,"Sources of Financing (Enter additional sources below)"]
["Construction Type",null,null,"Uses of Funds","AHP Requested","Owner Equity","LIHTC Proceeds","Permanent Loan",null,null,null,null,null,null,"(Must = 0)","Pct of Total Expenses"]
["Building Type",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Column1","Column2"]
["LAND COST/ACQUISITION",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"New","Apartments"]
["Land Cost",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D10-SUM(E10:N10)","v":0},{"$f":"IF(OR($D$92=\"\",D10=\"\",D10=0),\"\",D10/$D$92)","v":""},null,null,"Rehab","Single Family Homes"]
["Holding Costs",null,null,null,null,null,null,null,null,null,null,null,null,null,0,{"$f":"IF(OR($D$92=\"\",D11=\"\",D11=0),\"\",D11/$D$92)","v":""},null,null,null,"Townhomes"]
["Demolition",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Legal (includes closing costs)",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Total Land Cost",null,null,{"$f":"SUM(D10:D13)","v":0},{"$f":"SUM(E10:E13)","v":0},0,0,0,0,0,0,0,0,0,0,""]
["Existing Improvements",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Off-Site Improvements",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["  Total Land & Acquisition Costs",null,null,{"$f":"SUM(D14:D16)","v":0},{"$f":"SUM(E14:E16)","v":0},{"$f":"SUM(F14:F16)","v":0},{"$f":"SUM(G14:G16)","v":0},0,0,0,0,0,0,0,0,""]
["NEW CONSTRUCTION / REHAB",null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]
["Site Work",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D19-SUM(E19:N19)","v":0},""]
["Structures",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D20-SUM(E20:N20)","v":0},""]
["General Requirements",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Contractor Overhead",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Contractor Profit",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Prevailing Wages",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["General Liabilities Insurance ",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Environmental Remediation",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Hard Cost Contingency",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Total New Construction / Rehab Costs",null,null,{"$f":"SUM(D19:D27)","v":0},{"$f":"SUM(E19:E27)","v":0},{"$f":"SUM(F19:F27)","v":0},0,0,0,{"$f":"SUM(J19:J27)","v":0},0,0,0,0,0,""]
["ARCHITECTURAL FEES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]
["Design",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D30-SUM(E30:N30)","v":0},""]
["Supervision",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D31-SUM(E31:N31)","v":0},""]
["Survey & Engineering",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D32-SUM(E32:N32)","v":0},""]
["Total Architectural & Engineering Costs",null,null,{"$f":"SUM(D30:D32)","v":0},{"$f":"SUM(E30:E32)","v":0},{"$f":"SUM(F30:F32)","v":0},{"$f":"SUM(G30:G32)","v":0},0,0,0,0,0,0,0,{"$f":"+D33-SUM(E33:N33)","v":0},""]
["CONSTRUCTION INTEREST & FEES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]
["Construction Loan Interest",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D35-SUM(E35:N35)","v":0},""]
["Origination Fee",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Credit Enchancement & Application Fee",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Bond Premium",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Taxes",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Insurance",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Title & Recording",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Other",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Total Construction Interest & Fees",null,null,{"$f":"SUM(D35:D42)","v":0},{"$f":"SUM(E35:E42)","v":0},{"$f":"SUM(F35:F42)","v":0},{"$f":"SUM(G35:G42)","v":0},0,0,0,0,0,0,0,0,""]
["PERMANENT FINANCING",null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]
["Loan Origination Fee",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D45-SUM(E45:N45)","v":0},""]
["Credit Enhancement & Application Fee",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D46-SUM(E46:N46)","v":0},""]
["Title & Recording",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D47-SUM(E47:N47)","v":0},""]
["Other",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D48-SUM(E48:N48)","v":0},""]
["Total Permanent Financing Costs",null,null,{"$f":"SUM(D45:D48)","v":0},{"$f":"SUM(E45:E48)","v":0},{"$f":"SUM(F45:F48)","v":0},{"$f":"SUM(G45:G48)","v":0},0,0,0,0,0,0,0,{"$f":"+D49-SUM(E49:N49)","v":0},""]
["LEGAL FEES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]
["Lender Legal Paid by Applicant",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D51-SUM(E51:N51)","v":0},""]
["Other",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D52-SUM(E52:N52)","v":0},""]
["Total Attorney Costs",null,null,{"$f":"SUM(D51:D52)","v":0},{"$f":"SUM(E51:E52)","v":0},{"$f":"SUM(F51:F52)","v":0},{"$f":"SUM(G51:G52)","v":0},0,0,0,0,0,0,0,{"$f":"+D53-SUM(E53:N53)","v":0},""]
["RESERVES",null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]
["Rent Reserves",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D55-SUM(E55:N55)","v":0},""]
["Operating Reserves",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D56-SUM(E56:N56)","v":0},""]
["Replacement Reserves",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D57-SUM(E57:N57)","v":0},""]
["Other",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D58-SUM(E58:N58)","v":0},""]
["  Total Reserve Costs",null,null,{"$f":"SUM(D55:D58)","v":0},{"$f":"SUM(E55:E58)","v":0},{"$f":"SUM(F55:F58)","v":0},{"$f":"SUM(G55:G58)","v":0},0,0,0,0,0,0,0,{"$f":"+D59-SUM(E59:N59)","v":0},""]
["OTHER PROJECT COSTS",null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]
["TCAC Application/Allocation/Monitoring Fees",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D61-SUM(E61:N61)","v":0},""]
["Environmental Audit",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Local Development Impact Fees",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Permit Processing Fees",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Capital Fees",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Marketing",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Furnishings",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["OTHER PROJECT COSTS (continued)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]
["Appraisal",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D69-SUM(E69:N69)","v":0},""]
["Market Study",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Relocation Expenses",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Soft Cost Contingency",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Other",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Total Other Costs",null,null,{"$f":"SUM(D61:D73)","v":0},{"$f":"SUM(E61:E73)","v":0},{"$f":"SUM(F61:F73)","v":0},{"$f":"SUM(G61:G73)","v":0},0,0,0,0,0,0,0,0,""]
["DEVELOPER COSTS",null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(OR($D$92=\"\",D75=\"\",D75=0),\"\",D75/$D$92)","v":""}]
["Developer Fee (includes Project Administration)",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"D76-SUM(E76:N76)","v":0},""]
["Consultant/Processing Agent",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Broker Fees Paid with Project Funds to a Related Party",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Construction Management Oversight",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Other",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Total Developer Costs",null,null,{"$f":"SUM(D76:D80)","v":0},{"$f":"SUM(E76:E80)","v":0},{"$f":"SUM(F76:F80)","v":0},{"$f":"SUM(G76:G80)","v":0},0,0,0,0,0,0,0,0,""]
["SYNDICATION (Investor & General Partner)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,""]
["Organizational Fee",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"D83-SUM(E83:N83)","v":0},""]
["Bridge Loan Fees/Expenses",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Legal Fees",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Consultant Fees",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Accountant Fees",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Tax Opinion",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Other",null,null,null,null,null,null,null,null,null,null,null,null,null,0,""]
["Total Syndication Costs",null,null,{"$f":"SUM(D83:D89)","v":0},{"$f":"SUM(E83:E89)","v":0},{"$f":"SUM(F83:F89)","v":0},{"$f":"SUM(G83:G89)","v":0},0,0,0,0,0,0,0,{"$f":"+D90-SUM(E90:N90)","v":0},""]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Loan"]
["  Total Residential Development Costs",null,null,{"$f":"+D17+D28+D33+D43+D49+D53+D59+D74+D81+D90","v":0},{"$f":"+E17+E28+E33+E43+E49+E53+E59+E74+E81+E90","v":0},0,0,0,0,0,0,0,0,0,{"$f":"+D92-SUM(E92:N92)","v":0},{"$f":"IF(OR($D$92=\"\",$D$92=0),\"\",D92/$D$92)","v":""},null,null,"Grant"]
["  Total Commercial Development Costs",null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"+D93-SUM(E93:N93)","v":0},null,null,null,"Equity"]
["  Grand Totals Sources and Uses",null,null,{"$f":"D92+D93","v":0},{"$f":"E92+E93","v":0},0,0,0,0,0,0,0,0,0,{"$f":"+D94-SUM(E94:N94)","v":0}]
[null,null,null,"Loan, grant, or equity?",null,"Equity","Equity","Loan"]
[null,null,"Status of Permanent Financing Commitment (Approved or Not Approved)"]
[null,null,null,null,null,"If loan, soft or hard debt? "]
[null,null,"If hard debt, complete all required cells",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Soft"]
[null,null,null,null,null,null,"Loan Term (years)",null,null,null,null,null,null,null,null,null,null,null,"Hard"]
[null,null,null,null,null,null,"Loan Amortization Period (years)"]
[null,null,null,null,null,null,"Loan Interest Rate"]
[null,null,null,null,null,null,"Annual Debt Service (year 1) (calculate for hard debt only)",{"$f":"IF(H101>0,ABS(PMT(H101/12,H100*12,H94)*12),0)","v":0},{"$f":"IF(I101>0,ABS(PMT(I101/12,I100*12,I94)*12),0)","v":0},0,0,{"$f":"IF(L101>0,ABS(PMT(L101/12,L100*12,L94)*12),0)","v":0},{"$f":"IF(M101>0,ABS(PMT(M101/12,M100*12,M94)*12),0)","v":0},{"$f":"IF(N101>0,ABS(PMT(N101/12,N100*12,N94)*12),0)","v":0},{"$f":"SUM(H102:N102)","v":0}]
[null,null,null,null,null,null,"Loan Adjustment Period(s)"]
[null,null,null,null,null,null,"Loan Adjustment Caps (basis points)",null,null,null,null,null,null,null,null,null,null,null,"Not Approved"]
[null,null,null,null,null,null,"Balloon Payments (term year)",null,null,null,null,null,null,null,null,null,null,null,"Approved"]
[null,null,null,null,null,null,"Special Terms?"]
[]
[]
[null,null,null,null,null,null,null,null,null,"Total Committed Financing",null,null,null,null,{"$f":"F94+IF(G96=\"Approved\",G94,0)+IF(H96=\"Approved\",H94,0)+IF(I96=\"Approved\",I94,0)+IF(J96=\"Approved\",J94,0)+IF(K96=\"Approved\",K94,0)+IF(L96=\"Approved\",L94,0)+IF(M96=\"Approved\",M94,0)+IF(N96=\"Approved\",N94,0)","v":0}]

{"sheet":"Sources of Funds Summary","index":3,"n_rows":23,"n_cols":13,"rows_kept":23,"cut":false,"hidden":false,"merged":[[0,0,0,3],[1,0,1,3],[13,1,13,3],[7,0,7,3],[4,1,4,3],[5,0,6,3],[8,1,9,3],[8,0,9,0],[10,1,10,3],[11,1,11,3],[12,1,12,3],[14,1,14,3],[20,1,20,3],[16,1,16,3],[17,1,17,3],[18,1,18,3],[19,1,19,3],[15,1,15,3]]}
["Rental Projects: Sources of Funds Summary"]
[{"$f":"Targeting!A2","v":"Version 5.1 Updated 1/26/21"}]
[]
[]
["Project Name",{"$f":"IF(Targeting!B5:G5=\"\",\"\",Targeting!B5:G5)","v":""},null,null,null,null,null,null,null,null,null,null,"Response"]
["Please provide the required information below.",null,null,null,null,null,null,null,null,null,null,null,"Yes"]
[null,null,null,null,null,null,null,null,null,null,null,null,"No"]
["Project Financing Sources* "]
["Select                 \"Yes\" or \"No\"","Does the project intend to utilize funding from the following:"]
[]
[null,"HOME"]
[null,"Community Development Block Grant (CDBG)"]
[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 Bonds"]
[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":"15-Year Op Pro Forma","index":4,"n_rows":48,"n_cols":30,"rows_kept":48,"cut":false,"hidden":false,"merged":[[0,0,0,19],[1,0,1,19],[3,8,3,13],[17,0,17,2],[16,0,16,2],[6,0,6,4],[7,0,7,4],[3,5,3,6],[13,2,13,4],[10,0,10,4],[5,0,5,2],[8,0,8,4],[9,0,9,2],[3,3,3,4],[14,0,14,4],[3,0,3,2],[23,0,23,4],[22,0,22,4],[28,0,28,2],[27,0,27,2],[24,0,24,4],[25,0,25,4],[21,0,21,4],[12,0,12,1],[13,0,13,1],[11,0,11,4],[12,2,12,4],[19,0,19,4],[18,0,18,4],[20,0,20,4],[42,3,42,4],[26,0,26,4],[29,0,29,4],[37,0,37,2],[35,2,35,4],[30,0,30,4],[32,0,32,4],[34,2,34,4],[36,2,36,4],[34,0,34,1],[35,0,35,1],[33,0,33,4],[31,0,31,4],[3,15,3,17],[3,18,3,19],[47,0,47,4],[38,0,38,4],[39,0,39,4],[40,0,40,4],[41,0,41,4],[45,0,45,2],[45,3,45,4],[44,0,44,2],[43,3,43,4],[44,3,44,4],[42,0,42,2],[46,0,46,2],[46,3,46,4],[43,0,43,2]]}
["Rental Projects: 15-Year Operating Pro Forma"]
[{"$f":"'Sources & Uses of Funds'!A2","v":"Version 5.1 Updated 1/26/21"}]
[]
["Project Name",null,null,{"$f":"IF(+Targeting!B5=\"\",\"\",+Targeting!B5)","v":""},null,"Total Units",null,{"$f":"Targeting!A45","v":""},"Project Based Rental Subsidy (Indicate \"Yes\" or \"No\")",null,null,null,null,null,null,"If Yes, what is the status?"]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"Column1"]
["Income Assumptions",null,null,"Rate of Increase",null,"Year 1","Year 2","Year 3","Year 4","Year 5","Year 6","Year 7","Year 8","Year 9","Year 10","Year 11","Year 12","Year 13","Year 14","Year 15","Year 1 Pct of Total Expenses",null,null,null,null,null,null,null,null,"Yes"]
["Gross Residential Rents-Restricted Income Units",null,null,null,null,{"$f":"IF(+Targeting!M40=\"\",0,+Targeting!M40)","v":0},{"$f":"(F7*$E$6)+F7","v":0},{"$f":"(G7*$E$6)+G7","v":0},0,0,0,0,0,0,0,0,0,0,0,0,null,null,null,null,null,null,null,null,null,"No"]
["Gross Residential Rents-Restricted Income Units Subsidy",null,null,null,null,{"$f":"IF(+Targeting!L40=\"\",0,+Targeting!L40)","v":0},{"$f":"(F8*$E$6)+F8","v":0},{"$f":"(G8*$E$6)+G8","v":0},0,0,0,0,0,0,0,0,0,0,0,0]
["Gross Residential Rents-Non-restricted Income Units",null,null,null,null,0,{"$f":"(F9*$E$6)+F9","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,null,null,null,null,null,null,null,null,null,"Status of Project Based Rental Subsidy Commitment"]
["   Less Vacancy ",null,null,"% of Gross Rents =",null,{"$f":"((F7+F8+F9)*$E$10)*-1","v":0},{"$f":"((G7+G8+G9)*$E$10)*-1","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,null,null,null,null,null,null,null,null,null,"Approved"]
["Commercial Income",null,null,null,null,{"$f":"'15-Year Commercial Op Pro Forma'!F11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!G11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!H11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!I11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!J11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!K11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!L11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!M11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!N11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!O11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!P11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!Q11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!R11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!S11","v":0},{"$f":"'15-Year Commercial Op Pro Forma'!T11","v":0},null,null,null,null,null,null,null,null,null,"Not Approved"]
["Laundry, Parking",null,null,null,null,null,{"$f":"(F12*$E$6)+F12","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
["Other",null,null,null,null,null,{"$f":"(F13*$E$6)+F13","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
["Other",null,null,null,null,null,{"$f":"(F14*$E$6)+F14","v":0},{"$f":"(G14*$E$6)+G14","v":0},0,0,0,0,0,0,0,0,0,0,0,0]
["Total Effective Gross Income",null,null,null,null,{"$f":"SUM(F7:F14)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,0]
[]
["Expense Assumptions",null,null,"Rate of Increase"]
["Management Fee",null,null,"Dollars per Unit/Mo. =",{"$f":"IF(F18=\"\",\"\",(F18/$H$4)/12)","v":""},null,{"$f":"(F18*$E$17)+F18","v":0},{"$f":"(G18*$E$17)+G18","v":0},0,0,0,0,0,0,0,0,0,0,0,0,{"$f":"IF(OR($F$38=\"\",F18=0),\"\",F18/$F$38)","v":""}]
["Legal & Audit",null,null,null,null,null,{"$f":"(F19*$E$17)+F19","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,{"$f":"IF(OR($F$38=\"\",F19=0),\"\",F19/$F$38)","v":""}]
["Advertising",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Gas & Electric",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Water, Sewer, Waste Removal",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Supplies",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Payroll",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Insurance",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Pest Control",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Maintenance / Repair",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Operating Reserves",null,null,"Dollars per Unit =",{"$f":"IF(F28=\"\",\"\",(F28/$H$4))","v":""},null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Replacement Reserves",null,null,"Dollars per Unit =",{"$f":"IF(F29=\"\",\"\",+F29/H4)","v":""},null,{"$f":"F29","v":0},{"$f":"G29","v":0},0,0,0,0,0,0,0,0,0,0,0,0,""]
["Real Estate Taxes (exempt if in CA)",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Local & State Assessments",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Partnership Management Fee (tax credits only)",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Mandatory Debt Fee Payments (e.g., CA MHP, TOD, MHSA)",null,null,null,null,null,{"$f":"(F33)","v":0},{"$f":"(G33)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,""]
["Commercial Expenses",null,null,null,null,{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!F31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!G31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!H31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!I31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!J31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!K31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!L31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!M31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!N31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!O31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!P31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!Q31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!R31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!S31)","v":0},{"$f":"IF('15-Year Commercial Op Pro Forma'!$T$4=\"Yes\",\"0\",'15-Year Commercial Op Pro Forma'!T31)","v":0},""]
["Other",null,null,null,null,null,{"$f":"(F35*$E$17)+F35","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Other",null,null,null,null,null,{"$f":"(F36*$E$17)+F36","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Other",null,null,null,null,null,{"$f":"(F37*$E$17)+F37","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Total Expenses",null,null,"Operating Cost/Unit",{"$f":"IF(F38=0,\"\",+F38/H4)","v":""},{"$f":"SUM(F18:F37)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Net Operating Income",null,null,null,null,{"$f":"+F15-F38","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,0]
["Debt Service (Hard Debt Only from S&U Statement)",null,null,null,null,{"$f":"+'Sources & Uses of Funds'!O102","v":0},{"$f":"+F40","v":0},{"$f":"+G40","v":0},0,0,0,0,0,0,0,0,0,0,0,0]
["Debt Service Coverage Ratio",null,null,null,null,{"$f":"IF(F40>0,(F39/F40),0)","v":0},{"$f":"IF(G40>0,(G39/G40),0)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
["Cash Flow",null,null,null,null,{"$f":"+F39-F40","v":0},{"$f":"+G39-G40","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
["Operating Grant/Reserve"]
["Operating Grant/Reserve"]
["Residual Receipts Payment to"]
["Residual Receipts Payment to"]
["Residual Receipts Payment to"]
["Net Cash Flow",null,null,null,null,{"$f":"+F42+F43+F44-F45-F46-F47","v":0},{"$f":"+G42+G43+G44-G45-G46-G47","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]

{"sheet":"15-Year Commercial Op Pro Forma","index":5,"n_rows":41,"n_cols":24,"rows_kept":41,"cut":false,"hidden":false,"merged":[[5,0,5,2],[6,0,6,4],[0,0,0,19],[1,0,1,19],[3,0,3,2],[3,3,3,4],[3,5,3,6],[3,8,3,15],[3,17,3,18],[7,0,7,2],[8,0,8,1],[8,2,8,4],[9,0,9,1],[9,2,9,4],[22,0,22,4],[10,0,10,4],[12,0,12,2],[13,0,13,2],[14,0,14,4],[15,0,15,4],[16,0,16,4],[17,0,17,4],[18,0,18,4],[19,0,19,4],[20,0,20,4],[21,0,21,4],[30,0,30,2],[23,0,23,2],[24,0,24,2],[25,0,25,4],[26,0,26,4],[27,0,27,1],[27,2,27,4],[28,0,28,1],[28,2,28,4],[29,2,29,4],[31,0,31,4],[32,0,32,4],[33,0,33,4],[34,0,34,4],[35,0,35,2],[35,3,35,4],[39,0,39,2],[39,3,39,4],[40,0,40,4],[36,0,36,2],[36,3,36,4],[37,0,37,2],[37,3,37,4],[38,0,38,2],[38,3,38,4]]}
["Rental Projects: 15-Year Commercial Operating Pro Forma"]
[{"$f":"'Sources & Uses of Funds'!A2","v":"Version 5.1 Updated 1/26/21"}]
[]
["Project Name",null,null,{"$f":"IF(+Targeting!B5=\"\",\"\",+Targeting!B5)","v":""},null,"Total Units",null,{"$f":"Targeting!A45","v":""},"Did your permanent lender include commercial income in their underwriting?",null,null,null,null,null,null,null,null,"Triple Net Lease?"]
[]
["Income Assumptions",null,null,"Rate of Increase",null,"Year 1","Year 2","Year 3","Year 4","Year 5","Year 6","Year 7","Year 8","Year 9","Year 10","Year 11","Year 12","Year 13","Year 14","Year 15","Pct of Year 1 Total Expenses"]
["Scheduled Commercial Rents",null,null,null,null,null,{"$f":"(F7*$E$6)+F7","v":0},{"$f":"(G7*$E$6)+G7","v":0},{"$f":"(H7*$E$6)+H7","v":0},0,0,0,0,0,0,0,0,0,0,0,null,null,null,"Yes"]
["   Less Vacancy ",null,null,"% of Gross Rents =",null,{"$f":"(F7*$E$8)*-1","v":0},{"$f":"(G7*$E$8)*-1","v":0},{"$f":"(H7*$E$8)*-1","v":0},0,0,0,0,0,0,0,0,0,0,0,0,null,null,null,"No"]
["Other",null,null,null,null,null,{"$f":"(F9*$E$6)+F9","v":0},{"$f":"(G9*$E$6)+G9","v":0},0,0,0,0,0,0,0,0,0,0,0,0,null,null,null,"N/A"]
["Other",null,null,null,null,null,{"$f":"(F10*$E$6)+F10","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
["Total Effective Commercial Gross Income",null,null,null,null,{"$f":"SUM(F7:F10)","v":0},{"$f":"SUM(G7:G10)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
[]
["Expense Assumptions",null,null,"Rate of Increase"]
["Management Fee",null,null,"Dollars per Unit/Mo. =",{"$f":"IF(F14=\"\",\"\",(F14/$H$4)/12)","v":""},null,{"$f":"F14*(1+$E$13)","v":0},{"$f":"G14*(1+$E$13)","v":0},0,0,{"$f":"J14*(1+$E$13)","v":0},0,0,0,0,0,0,0,0,0,{"$f":"IF(OR($F$31=\"\",F14=0),\"\",F14/$F$31)","v":""}]
["Legal & Audit",null,null,null,null,null,{"$f":"(F15*$E$13)+F15","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,{"$f":"IF(OR($F$31=\"\",F15=0),\"\",F15/$F$31)","v":""}]
["Advertising",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Gas & Electric",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Water, Sewer, Waste Removal",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Supplies",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Payroll",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Insurance",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Pest Control",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Maintenance / Repair",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Operating Reserves",null,null,"Dollars per Unit =",{"$f":"IF(F24=\"\",\"\",(F24/$H$4))","v":""},null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Replacement Reserves",null,null,"Dollars per Unit =",{"$f":"IF(F25=\"\",\"\",+F25/H4)","v":""},null,{"$f":"F25","v":0},{"$f":"G25","v":0},0,0,0,0,0,0,0,0,0,0,0,0,""]
["Real Estate Taxes",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Local & State Assessments",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Other",null,null,null,null,null,{"$f":"(F28*$E$13)+F28","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Other",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Other",null,null,null,null,null,0,0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Total Expenses",null,null,"Operating Cost/Unit",{"$f":"IF(F31=0,\"\",+F31/H4)","v":""},{"$f":"SUM(F14:F30)","v":0},{"$f":"SUM(G14:G30)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0,""]
["Net Operating Income",null,null,null,null,{"$f":"+F11-F31","v":0},{"$f":"+G11-G31","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
["Debt Service (Hard Debt Only)",null,null,null,null,null,{"$f":"+F33","v":0},{"$f":"+G33","v":0},0,0,0,0,0,0,0,0,0,0,0,0]
["Debt Service Coverage Ratio",null,null,null,null,{"$f":"IF(F33>0,(F32/F33),0)","v":0},{"$f":"IF(G33>0,(G32/G33),0)","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
["Cash Flow",null,null,null,null,{"$f":"+F32-F33","v":0},{"$f":"+G32-G33","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]
["Operating Grant/Reserve"]
["Operating Grant/Reserve"]
["Residual Receipts Payment to"]
["Residual Receipts Payment to"]
["Residual Receipts Payment to"]
["Net Cash Flow",null,null,null,null,{"$f":"+F35+F36+F37-F38-F39-F40","v":0},{"$f":"+G35+G36+G37-G38-G39-G40","v":0},0,0,0,0,0,0,0,0,0,0,0,0,0]

{"sheet":"Empowerment Budget","index":6,"n_rows":27,"n_cols":7,"rows_kept":27,"cut":false,"hidden":false,"merged":[[25,5,25,7],[26,5,26,7],[12,0,12,3],[25,0,25,3],[26,0,26,3],[15,5,15,7],[16,5,16,7],[17,5,17,7],[18,5,18,7],[19,5,19,7],[20,5,20,7],[21,5,21,7],[22,5,22,7],[23,5,23,7],[24,5,24,7],[20,0,20,3],[22,0,22,3],[23,0,23,3],[24,0,24,3],[15,0,15,3],[16,0,16,3],[17,0,17,3],[18,0,18,3],[19,0,19,3],[3,4,3,5],[3,0,3,2],[0,0,0,7],[1,0,1,7],[21,0,21,3],[11,0,11,3],[14,0,14,3],[12,5,12,7],[6,5,6,7],[5,5,5,7],[7,5,7,7],[8,5,8,7],[9,5,9,7],[10,5,10,7],[11,5,11,7],[6,0,6,3],[7,0,7,3],[8,0,8,3],[9,0,9,3],[10,0,10,3],[5,0,5,3]]}
["Rental Projects: Promotion of Empowerment Budget"]
[{"$f":"'Sources & Uses of Funds'!A2","v":"Version 5.1 Updated 1/26/21"}]
[]
["Project Name",null,null,{"$f":"IF(+Targeting!B5=\"\",\"\",+Targeting!B5)","v":""},"Total Units",null,{"$f":"Targeting!A45","v":""}]
[]
["Source of Revenue",null,null,null,"Year 1","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":7,"n_rows":44,"n_cols":6,"rows_kept":44,"cut":false,"hidden":false,"merged":[[23,4,26,4],[23,5,26,5],[16,0,17,0],[18,5,19,5],[11,1,14,1],[11,4,14,4],[23,0,26,0],[23,1,26,1],[23,3,24,3],[25,3,26,3],[15,2,15,3],[0,0,0,5],[1,0,1,5],[18,2,19,2],[18,3,19,3],[18,4,19,4],[11,0,14,0],[8,0,9,0],[8,2,9,4],[8,5,9,5],[11,5,14,5],[16,1,17,1],[16,2,17,2],[16,4,17,4],[16,5,17,5],[3,1,3,2],[35,5,36,5],[38,2,38,3],[33,0,34,0],[33,2,34,2],[33,4,34,4],[33,5,34,5],[39,2,39,4],[35,0,36,0],[35,1,36,1],[35,2,36,2],[35,4,36,4]]}
["Rental Projects: Benchmarks"]
[{"$f":"Targeting!A2:M2","v":"Version 5.1 Updated 1/26/21"}]
[]
["Project Name",{"$f":"IF(+Targeting!B5=\"\",\"\",+Targeting!B5)","v":""},null,"Total Units",{"$f":"'15-Year Op Pro Forma'!H4","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."]
[]
["Development Budget"]
["Construction costs per square foot (excluding land costs and soft costs) ",{"$f":"IF('Sources & Uses of Funds'!C8=\"\",\"\",'Sources & Uses of Funds'!C8)","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 Reference Guide. \nCalculation takes total new construction or rehab costs minus environmental remediation and hard cost contingency, and divides it by total residential square footage. Off-site work, environmental remediation, and hard cost continency line items cannot be used to explain benchmark deviation."]
[null,{"$f":"IF('Sources & Uses of Funds'!D92=0,0,(('Sources & Uses of Funds'!D28-'Sources & Uses of Funds'!D26-'Sources & Uses of Funds'!D27)/'Sources & Uses of Funds'!L4))","v":0}]
["Total construction hard costs",{"$f":"IF('Sources & Uses of Funds'!D28=0,\"\",SUM('Sources & Uses of Funds'!D19:D25))","v":""}]
["Construction/rehabilitation hard cost contingency as % of construction/rehabilitation hard costs",{"$f":"IF('Sources & Uses of Funds'!D28=0,0,'Sources & Uses of Funds'!D27/('Sources & Uses of Funds'!D28-'Sources & Uses of Funds'!D27))","v":0},"New Construction =","New Construction =",{"$f":"IF('Sources & Uses of Funds'!C7=\"New\",IF(B12>=C13,IF(B12<=D13,\"Yes\",\"No\"),\"No\"),IF(B12>=C15,IF(B12<=D15,\"Yes\",\"No\"),\"No\"))","v":"No"}]
[null,null,0.05,0.1]
[null,null,"Rehab =","Rehab ="]
[null,null,7.4999999999999997E-2,0.2]
["Capitalized replacement reserves ",{"$f":"+'Sources & Uses of Funds'!D57","v":0},"$0 on new construction projects. Variable on rehabilitation projects depending on age and condition of building (Exceptions made if reserves required by funding agency)",null,{"$f":"IF('Sources & Uses of Funds'!C7=\"Rehab\",\"Yes\",IF(B16=0,\"Yes\",\"No\"))","v":"Yes"}]
["Capitalized operating reserves ",{"$f":"+'Sources & Uses of Funds'!D55+'Sources & Uses of Funds'!D56+'Sources & Uses of Funds'!D58","v":0},0,"12 months operating expense and debt service payments (must be covered by financing sources other than AHP. Must provide documentation for benchmark deviation.):",{"$f":"IF(B17<=D18,\"Yes\", \"No\")","v":"Yes"}]
[null,null,null,{"$f":"('15-Year Op Pro Forma'!F38+'15-Year Op Pro Forma'!F40)","v":0}]
["Total developer fee",{"$f":"IF(+'Sources & Uses of Funds'!D92=0,0,'Sources & Uses of Funds'!D76)","v":0},0,"Not greater than 12% of total residential development costs (including developer fee, but excluding all capitalized reserves)",{"$f":"IF(B20=\"\",\"\",IF(B20>0.12,\"No\",\"Yes\"))","v":"Yes"}]
["Total developer fee as percentage of total residential development costs",{"$f":"IF('Sources & Uses of Funds'!D92=0,0,('Sources & Uses of Funds'!D76)/('Sources & Uses of Funds'!D92-'Sources & Uses of Funds'!D59))","v":0}]
["Soft cost contingency as % of soft costs",{"$f":"IF('Sources & Uses of Funds'!D72=0,0,('Sources & Uses of Funds'!D72/('Sources & Uses of Funds'!D92-'Sources & Uses of Funds'!D17-'Sources & Uses of Funds'!D28)))","v":0},0,0.1,{"$f":"IF(B21>D21,\"No\", \"Yes\")","v":"Yes"}]
["15-Year Operating Pro Forma"]
["Pro forma assumptions for income compared to expense increases. Expense increase minus income increase =                                                                                                       ",{"$f":"IF('15-Year Op Pro Forma'!E17=\"\",\"0%\",'15-Year Op Pro Forma'!E17-'15-Year Op Pro Forma'!E6)","v":"0%"},"Expenses increase 1% faster than revenues; e.g., 4% cost increase versus 3% income increase","Expenses increase 2% faster than revenues; e.g., 5% cost increase versus 3% income increase",{"$f":"IF(B23>=0.01,IF(B23<=0.02,\"Yes\",\"No\"),\"No\")","v":"No"}]
["Vacancy and bad debt rate: residential",{"$f":"IF(+'15-Year Op Pro Forma'!E10=\"\",\"\",'15-Year Op Pro Forma'!E10)","v":""},"Projects with No Project-Based Rental Subsidies","All Projects",{"$f":"IF('15-Year Op Pro Forma'!O4=\"Yes\",IF(B24>=C27,IF(B24<=D26,\"Yes\",\"No\"),\"No\"),IF(B24>=C25,IF(B24<=D26,\"Yes\",\"No\"),\"No\"))","v":"No"}]
[null,null,0.05]
[null,null,"Projects with Project-Based Rental Subsidies",0.1]
[null,null,0.03]
["Vacancy and bad debt rate: commercial",{"$f":"IF(+'15-Year Commercial Op Pro Forma'!E8=\"\",\"\",'15-Year Commercial Op Pro Forma'!E8)","v":""},"10% of gross rents","50% of gross rents",{"$f":"IF(B28=\"\",\"N/A\",IF(B28>=0.1,IF(B28<=0.5,\"Yes\",\"No\"),\"No\"))","v":"N/A"}]
["Management fees per unit per month",{"$f":"IF('15-Year Op Pro Forma'!E18=\"\",\"$0\",'15-Year Op Pro Forma'!E18)","v":"$0"},"Not less than $25","Not greater than $65",{"$f":"IF(B29>=25,IF(B29<=65,\"Yes\",\"No\"),\"No\")","v":"No"}]
["Operating reserves per unit per year",{"$f":"IF('15-Year Op Pro Forma'!E28=\"\",\"$0\",'15-Year Op Pro Forma'!E28)","v":"$0"},"$0, but if zero, must have adequate cushion built into debt service coverage ratio ","Not greater than $300 per unit per year",{"$f":"IF(B30=\"$0\",\"Yes\",IF(B30<301,\"Yes\",\"No\"))","v":"Yes"}]
["Annual replacement reserves per unit",{"$f":"IF('15-Year Op Pro Forma'!E29=\"\",\"$0\",'15-Year Op Pro Forma'!E29)","v":"$0"},250,600,{"$f":"IF(B31<250,\"No\",IF(B31>600,\"No\",\"Yes\"))","v":"No"}]
["Operating cost per unit per year ",{"$f":"IF('15-Year Op Pro Forma'!E38=\"\",\"$0\",'15-Year Op Pro Forma'!E38)","v":"$0"},"Not less than $4,000","Not greater than $8,000",{"$f":"IF(B32<4000,\"No\",IF(B32>8000,\"No\",\"Yes\"))","v":"No"}]
["Debt service coverage ratio in Year 1                                                                      (defined as net operating income divided by debt service payment)",{"$f":"IF('15-Year Op Pro Forma'!F41=0,\"\",ROUND('15-Year Op Pro Forma'!F41,2))","v":""},1.05,1.45,{"$f":"IF('Sources & Uses of Funds'!O102>0,IF(B33>=C33,IF(B33<=D33,\"Yes\",\"No\"),\"No\"),\"N/A\")","v":"N/A"}]
["Net cashflow in Year 1",{"$f":"IF(+'15-Year Op Pro Forma'!F48=0,0,ROUND('15-Year Op Pro Forma'!F48,0))","v":0},0,"Not to exceed 10% of gross income in year one unless cashflow is needed by project to cover deficits throughout the retention period, or in order to meet minimum underwriting requirements for other lenders.",{"$f":"IF(B35>=0,IF(B35<D35,\"Yes\",\"No\"),\"No\")","v":"Yes"}]
[null,{"$f":"ROUNDDOWN(IF('15-Year Op Pro Forma'!F48=0,0,'15-Year Op Pro Forma'!F48/'15-Year Op Pro Forma'!F15),B44)","v":0},null,0.1]
["Net cashflow in Years 1  through 5",{"$f":"IF(+SUM('15-Year Op Pro Forma'!F48:J48)=0,0,SUM('15-Year Op Pro Forma'!F48:J48))","v":0},0,"Not to exceed the amount of the subsidy request.",{"$f":"IF(B36>=0,IF(B36<D37,\"Yes\",\"No\"),\"No\")","v":"No"}]
[null,null,null,{"$f":"'Sources & Uses of Funds'!E94","v":0}]
["Partnership management fees (tax credits only)",{"$f":"IF(+'15-Year Op Pro Forma'!F32=\"\",\"$0\",'15-Year Op Pro Forma'!F32)","v":"$0"},0,"$10,000 in year one (annual increases not to exceed 3.5%)",{"$f":"IF(B38=\"$0\",\"Yes\",IF(B38<=10000,\"Yes\",\"No\"))","v":"Yes"}]
["Term of permanent financing",{"$f":"MIN(IF('Sources & Uses of Funds'!H97:N97=\"hard\",'Sources & Uses of Funds'!H99:N99,\"\"))","v":0},"Not less than 15 years, unless extenuating circumstances",null,{"$f":"IF(OR(B39=\"\",B39=0),\"N/A\",IF(B39>=15,\"Yes\", \"No\"))","v":"N/A"}]
["Interest rate assumptions",{"$f":"MAX(IF('Sources & Uses of Funds'!H97:N97=\"Hard\",'Sources & Uses of Funds'!H101:N101))","v":0},"To ensure consistent and reasonable rate assumptions, interest rates and spreads should be set in accordance with the Interest Rate Assumptions Guidelines included in the application package."]
[]
[]
[]
[null,3]

{"sheet":"Targeting Self-Score","index":8,"n_rows":30,"n_cols":7,"rows_kept":30,"cut":false,"hidden":false,"merged":[[0,0,0,6],[1,0,1,6],[25,0,25,1],[19,0,19,1],[20,0,20,1],[21,0,21,1],[24,0,24,1],[12,3,12,5],[13,3,13,5],[14,3,14,5],[15,3,15,5],[16,3,16,5],[26,0,26,1],[27,0,27,1],[28,0,28,1],[29,0,29,1],[18,0,18,1],[23,0,23,1]]}
["Rental Projects: Targeting Self-Score"]
[{"$f":"Targeting!A2:M2","v":"Version 5.1 Updated 1/26/21"}]
[]
["Project Name",{"$f":"IF(+Targeting!B5=\"\",\"\",+Targeting!B5)","v":""},"Proposed No. of Units","Proposed AMI Targets","Proposed No. of Units @ 50% AMI or less","Proposed No. of Units  >50% & <=80% AMI","WAAMIT Calc"]
[null,null,{"$f":"+Targeting!R40","v":0},0.5,{"$f":"IF(D5=\"\",0,IF(D5<=0.5,C5,0))","v":0},{"$f":"IF(D5>0.5,C5,0)","v":0},{"$f":"IF(D5>0.5,C5*D5,0)","v":0}]
[null,null,{"$f":"Targeting!S40","v":0},null,0,0,0]
["Note:",null,{"$f":"+Targeting!T40","v":0},null,0,0,0]
["1. No additional points given for units targeted",null,{"$f":"+Targeting!U40","v":0},null,0,0,0]
["below 50% AMI.",null,{"$f":"+Targeting!V40","v":0},null,0,0,0]
[null,null,{"$f":"+Targeting!W40","v":0},null,0,0,0]
["2. In order to score your own project, you must",null,{"$f":"+Targeting!X40","v":0},null,0,0,0]
["fill in the AMI target(s) greater than 50%",null,{"$f":"SUM(C5:C11)","v":0},"Total Targeted",{"$f":"SUM(E5:E11)","v":0},{"$f":"SUM(F5:F11)","v":0},{"$f":"SUM(G5:G11)","v":0}]
["proposed in your Rental Projects Worksheet.",null,{"$f":"Targeting!A42","v":0},"Non-Restricted Manager Units"]
[null,null,{"$f":"Targeting!A43","v":0},"Non Income-Restricted Special Needs Units"]
["3. Shaded cells are password protected.",null,{"$f":"Targeting!A44","v":0},"Other Non-Restricted Units"]
[null,null,{"$f":"+C12+C13+C14+C15","v":0},"Total Units - must match Targeting Worksheet"]
[null,null,{"$f":"COUNTIF(Targeting!N19:N296,\"=Fail\")","v":0},"Units failing rent cap of 30% income"]
[]
["Step I",null,"Line","Score"]
["Percent of total units at 50% AMI or lower",null,1,{"$f":"IF($C$16=Targeting!$A$45,IF($C$12>0,+$E$12/$C$16*100,0),\"Fill in targeting\")","v":"Fill in targeting"}]
["Subtract 20 from line above",null,2,{"$f":"IF(D20<20,0,IF(C12>0,+D20-20,0))","v":0}]
["Multiply this percentage by .50 (base score)",null,3,{"$f":"IF($D$21*0.5>=20,20,$D$21*0.5)","v":0}]
[]
["Step II",null,"Line","Score"]
["Subtract base score from 20 (remaining possible points)",null,4,{"$f":"IF(D20<20,0,IF(C12>0,20-D22,0))","v":0}]
["Weighted Average Area Median Income Target (WAAMIT) for units between 50% and 80% AMI",null,5,{"$f":"IF($D$25>0,IF($F12<=0,0,$G$12/$F$12*100),0)","v":0}]
["Divide remaining possible points by 30 ",null,6,{"$f":"+D25/30","v":0}]
["Line 5 subtracted from .80",null,7,{"$f":"IF(D26>0,80-D26,0)","v":0}]
["Multiply line 6 by line 7 (adjustment)",null,8,{"$f":"+D28*D27","v":0}]
["Base score plus adjustment (final score)",null,9,{"$f":"+D29+D22","v":0}]

{"sheet":"Subsidy Per Unit Score","index":9,"n_rows":18,"n_cols":5,"rows_kept":18,"cut":false,"hidden":true,"merged":[[6,0,6,2],[4,0,4,1],[4,2,4,4],[0,0,0,4],[1,0,1,4],[7,0,7,2],[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]]}
["Rental Projects: Subsidy Per Unit Score"]
[{"$f":"Targeting!A2:M2","v":"Version 5.1 Updated 1/26/21"}]
[]
[]
["Project Name",null,{"$f":"IF(+Targeting!B5=\"\",\"\",+Targeting!B5)","v":""}]
[]
["Subsidy Requested",null,null,{"$f":"IF('Sources & Uses of Funds'!E94=0,0,'Sources & Uses of Funds'!E94)","v":0},"Line 1"]
["AHP Project Units (income restricted units)",null,null,{"$f":"IF(Targeting!A40=\"\",0,Targeting!A40)","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 Rental Project Subsidy per Unit Range",null,null,40000,"Line 4"]
["Low End of Rental Project Subsidy per Unit Range",null,null,15000,"Line 5"]
[]
["Step 1:","Determine Subsidy per Unit Range Difference (Subtract line 5 from line 4)",null,{"$f":"+D10-D11","v":25000},"Line 6"]
["Step 2:","Determine Scoring Factor (Divide 12 possible category points by line 6)",null,{"$f":"12/D13","v":4.8000000000000001E-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,+D14*D15)","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":10,"n_rows":13,"n_cols":4,"rows_kept":13,"cut":false,"hidden":true,"merged":[[12,1,12,2],[7,0,7,2],[8,0,8,2],[10,1,10,2],[0,0,0,3],[1,0,1,3],[4,0,4,1],[4,2,4,3],[6,0,6,2]]}
["Rental Projects: Housing for Large Units Score"]
[{"$f":"Targeting!A2:M2","v":"Version 5.1 Updated 1/26/21"}]
[]
[]
["Project Name",null,{"$f":"IF(+Targeting!B5=\"\",\"\",+Targeting!B5)","v":""}]
[]
["AHP Project Total Units ",null,null,{"$f":"IF(Targeting!A50=\"\",0,Targeting!A45)","v":0}]
["Units with 3+ bedrooms",null,null,{"$f":"IF(Targeting!A51=\"\",0,Targeting!A51)","v":0}]
["Percentage of Units with 3+ Bedrooms",null,null,{"$f":"D8/D7","v":{"$error":"#DIV/0!"}}]
[]
[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":"#DIV/0!"}}]

{"sheet":"AHP Application Comparison","index":11,"n_rows":42,"n_cols":13,"rows_kept":42,"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!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 Rental 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('Sources & Uses of Funds'!E94=\"\",0,'Sources & Uses of Funds'!E94)","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!A45=\"\",0,Targeting!A45)","v":0},"TOTAL_UNITS",0,null,"","Not Yet Compared",1]
[null,"Total Income-Restricted Units",{"$f":"IF(Targeting!A40=\"\",0,Targeting!A40)","v":0},"INCOME_RESTRICTED_UNITS",0,null,"","Not Yet Compared",1]
[null,"Total Non-Income Restricted Units",{"$f":"SUM(Targeting!A42:A44)","v":0},"NONINCOME_RESTRICTED_UNITS",0,null,"","Not Yet Compared",1]
[null,"Manager's Units",{"$f":"SUM(Targeting!A41:A42)","v":0},"MANAGER_UNITS",0,null,"","Not Yet Compared",1]
[null,"Number of Homeless Units",{"$f":"IF(Targeting!A47=\"\",0,Targeting!A47)","v":0},"HOMELESS_UNITS",0,null,"","Not Yet Compared",1]
[null,"Total Number of Special Needs Units",{"$f":"IF(Targeting!A48=\"\",0,Targeting!A48)","v":0},"SPECIALNEEDS_TOTAL_UNITS",0,null,"","Not Yet Compared",1]
[null,"Targeting Weighted Average",{"$f":"IF(Targeting!A50=\"\",0,Targeting!A50)","v":0},"[CALCULATED]",0,{"$f":"IF(F12=\"\",\"\",IFERROR(SUMPRODUCT(F18:F29,F30:F41)/SUM(F18:F29),E17))","v":""},{"$f":"IF(F17=\"\",\"\",F17)","v":""},{"$f":"IF(I17=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,COMPARISON_DEFAULT_STATUS,IF(I17=2,\"Yes\",\"No\")))","v":"Not Yet Compared"},{"$f":"IF(C17=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,1,IF(IF(ISBLANK(C17),0,C17)=IF(ISBLANK(G17),0,G17),2,0)))","v":1}]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW1_UNITS",0,null,{"$f":"IF(F18=\"\",\"\",F18)","v":""}]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW2_UNITS",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW3_UNITS",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW4_UNITS",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW5_UNITS",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW6_UNITS",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW7_UNITS",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW8_UNITS",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW9_UNITS",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW10_UNITS",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW11_UNITS",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW12_UNITS",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW1_AMI",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW2_AMI",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW3_AMI",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW4_AMI",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW5_AMI",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW6_AMI",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW7_AMI",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW8_AMI",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW9_AMI",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW10_AMI",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW11_AMI",0,null,""]
[null,"INCOME_RESTRICTED_AMI_TABLE","N/A","INCOME_RESTRICTED_TBL_ROW12_AMI",0,null,""]
[null,"Households Requiring Large Units",{"$f":"Targeting!A51","v":0},"LARGE_UNITS",0,null,"",{"$f":"IF(I42=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,COMPARISON_DEFAULT_STATUS,IF(I42=2,\"Yes\",\"No\")))","v":"Not Yet Compared"},{"$f":"IF(C42=\"\",\"\",IF(IFERROR(DATEVALUE(TEXT(COMPARISON_LAST_RUN,\"mm/dd/yyy\")),0)<=0,1,IF(IF(ISBLANK(C42),0,C42)=IF(ISBLANK(G42),0,G42),2,0)))","v":1}]

{"sheet":"Data Import Summary","index":12,"n_rows":46,"n_cols":15,"rows_kept":46,"cut":false,"hidden":true,"merged":[]}
["IRIS Tab","Question","ID ","Financial Workbook Worksheet","Cell","Additional field name details","Type","Required ","Validation","Export?","Link to Workbook","DB Field Name / \nDynamic Framework Question ID","DB Field Type  ","Dynamic Framework TABLE.COLUMN_NAME","Additional Notes"]
["Basic Review","Sources = Uses",null,"Sources & Uses of Funds","O94",null,"Number","Y","If cell =0 then \"Yes\",  if not equal to zero then \"No\".","Yes",{"$f":"IF('Sources & Uses of Funds'!$O$94=0,1,0)","v":1}]
["Basic Review","Cash Flow",null,"15-Year Op Pro Forma","F48:T48",null,"Number","Y","If sum of cells is >=0 then \"Yes\", if not then \"No\".","Yes",{"$f":"SUM('15-Year Op Pro Forma'!F48:T48)","v":0}]
["Basic Review","20% VLI",null,"Targeting Worksheet","A46",null,"Percentage","Y","If cell is >=20% then \"Yes\", if not then \"No\".","Yes",{"$f":"Targeting!$A$46","v":""}]
["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,"Sources & Uses of Funds","D94",null,"Number","Y","=Value in cell","Yes",{"$f":"'Sources & Uses of Funds'!$D$94","v":0}]
["Project Data","Committed Sources",null,"Sources & Uses of Funds","O110",null,"Number","Y","=Value in cell","Yes",{"$f":"'Sources & Uses of Funds'!$O$109","v":0}]
["Project Data","3 Bedroom or More",null,"Targeting Worksheet","E19:E39",null,"Number","Y","If any cell is equal to or greater than 3, then box should be checked, if not, blank. ","Yes, if any cell is >= 3. ",{"$f":"IF(Targeting!$AC$40>0,1,0)","v":0}]
["Benchmarks","Avg Bedrooms per Unit",null,"Targeting Worksheet","A49",null,"Number","Y","=Value in cell","Yes",{"$f":"Targeting!$A$49","v":""}]
[]
["Benchmarks","Const Cost per SF for project",null,"Benchmarks","B10",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$10","v":0}]
["Benchmarks","Building Type",null,"Sources & Uses of Funds","C8",null,"Drop-down (Apartments, Single Family Homes, Townhomes)","Y","=Value in cell","Yes",{"$f":"'Sources & Uses of Funds'!$C$8","v":0}]
["Benchmarks","Const/Rehab Contingency",null,"Benchmarks","B12",null,"Percentage","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$12","v":0}]
["Benchmarks","Rep Reserve Amount",null,"Benchmarks","B16",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$16","v":0}]
["Benchmarks","% Over Benchmark (ops)",null,"Benchmarks","B17 and D18",null,"Currency","Y","If E16 =\"No\" then =(Value in B16- Value in D17)/Value in D17. Otherwise zero. ","Yes",{"$f":"IF(Benchmarks!E17=\"No\", (Benchmarks!B17-Benchmarks!D18)/Benchmarks!D18, 0)","v":0}]
["Benchmarks","Max Op Reserve",null,"Benchmarks","D18",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$D$18","v":0}]
["Benchmarks","Developer Fee Percentage",null,"Benchmarks","B20",null,"Percentage","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$20","v":0}]
["Benchmarks","Soft cost contingency %",null,"Benchmarks","B21",null,"Percentage","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$21","v":0}]
["Benchmarks","Rate difference",null,"Benchmarks","B23",null,"Percentage","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$23","v":"0%"}]
["Benchmarks","Vacancy Residential",null,"Benchmarks","B24",null,"Percentage","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$24","v":""}]
["Benchmarks","Vacancy Commercial",null,"Benchmarks","B28",null,"Percentage","Y","=Value in cell if cell has a value or zero. ","Yes",{"$f":"Benchmarks!$B$28","v":""}]
["Benchmarks","Mgmt Fee per unit per month",null,"Benchmarks","B29",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$29","v":"$0"}]
["Benchmarks","Oper res/unit/year",null,"Benchmarks","B30",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$30","v":"$0"}]
["Benchmarks","Rep res/unit/year",null,"Benchmarks","B31",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$31","v":"$0"}]
["Benchmarks","Op Cost PUPY",null,"Benchmarks","B32",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$32","v":"$0"}]
["Benchmarks","DSCR",null,"Benchmarks","B33",null,"Number","N","=Value in cell if cell has a value or zero. ","Yes",{"$f":"Benchmarks!$B$33","v":""}]
["Benchmarks","Year 1 cash flow",null,"Benchmarks","B35",null,"Percentage","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$35","v":0}]
["Benchmarks","Year 1-5 cash",null,"Benchmarks","B36",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$36","v":0}]
["Benchmarks","PM Fees",null,"Benchmarks","B38",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$38","v":"$0"}]
["Benchmarks","Term ",null,"Benchmarks","B39",null,"Number","N","=Value in cell if cell has a value or zero. ","Yes",{"$f":"Benchmarks!$B$39","v":0}]
["Benchmarks","Interest Rate",null,"Benchmarks","B40",null,"Percentage","N","=Value in cell if cell has a value or zero. ","Yes",{"$f":"Benchmarks!$B$40","v":0}]
["Application Info","Subsidy Requested",null,"Sources & Uses of Funds","E94",null,"Number","Y","=Value in cell","No",{"$f":"'Sources & Uses of Funds'!$E$94","v":0}]
["Project Data","Square Footage ",null,"Sources & Uses of Funds","L4",null,"Number","Y","=Value in cell","Yes",{"$f":"'Sources & Uses of Funds'!$L$4","v":0}]
["Benchmarks","Total construction hard costs",null,"Benchmarks","B11",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$11","v":""}]
["Benchmarks","Capitalized Operating Reserve (actual number)",null,"Benchmarks","B16",null,"Currency","Y","=Value in cell","Yes",{"$f":"Benchmarks!$B$17","v":0}]
["Scoring","Households Requiring Large Units",null,"Targeting","A51",null,"Number","Y","=Value in cell","Yes",{"$f":"Targeting!A51","v":0},null,null,null,"New for 2021"]

{"sheet":"Version Control","index":13,"n_rows":50,"n_cols":6,"rows_kept":50,"cut":false,"hidden":true,"merged":[]}
["AHP Application Financial Workbook Changes Log"]
[]
["Updated","2020-12-30"]
["By","Rose Wirtz"]
[]
[]
["Financial Workbook","Version","Tab","Description of Change from Previous Version","Completed by, date","Approver"]
["Rental",5,"All tabs","New FHLBSF logo and version number (5.0)"]
["Rental",5,"Targeting","Locked Homeless, Special Needs, and Bedrooms per Unit cells so only other acceptable input than the drop-down choices is 0"]
["Rental",5,"Sources & Uses of Funds","Added \"Pct of Funds\" column for easier analysis by feasbility analyst "]
["Rental",5,"15-Year Op Pro Forma","Added \"Pct of Year 1 Total Expenses\" column for easier analysis by feasbility analyst "]
["Rental",5,"15-Year Commercial Op Pro Forma","Added \"Pct of Year 1 Total Expenses\" column for easier analysis by feasbility analyst "]
["Rental",5,"Benchmarks","Adjusted formula in B10 (Construction costs per square foot calculation). Calculation now factors in Site Work in the Sources & Uses of Funds tab"]
["Rental",5,"Benchmarks","Line 11 added for Total construction hard costs. Formula calculates all costs under \"New Construction/Rehab\" except for Environmental Remediation and Hard Cost Contingency. "]
["Rental",5,"Benchmarks","Formulas fixed for residential vacancy/bad debt rate (E24) and commercial vacancy and bad debt rate (E28) so that errors are no longer shown"]
["Rental",5,"Benchmarks","3 decimal places for B33, C33, D33 (debt service coverage ratio)"]
["Rental",5,"Benchmarks","Formula fixed for term of permanent financing (B39). MINIFS formula introduced so that the workbook finds the lowest hard debt term from the Sources & Uses of Funds  tab"]
["Rental",5,"Benchmarks","Formula fixed for term of interest rate assumptions (B40). MAXIFS formula introduced so that the workbook finds the highest hard-debt interest rate from the Sources & Uses of Funds  tab"]
["Rental",5,"Benchmarks","Any \"No\" response in Column E is now highlighted yellow (Conditional Formatting)"]
["Rental",5,"Data Import Summary","Import square footage figure from Sources & Uses of Funds"]
["Rental",5,"Data Import Summary","Import total construction hard costs from Benchmarks tab"]
["Rental",5,"Data Import Summary","Import the actual capitalized operating reserve calculation from Benchmarks "]
["Rental",5,"Data Import Summary","All Benchmark cell references moved one line down (highlighted)"]
["Rental",5,"All tabs","1/21/20 - removed \"Edit Scenarios\" and \"Edit Objects\" permission from most tabs"]
[]
["Rental",5.0999999999999996,"Targeting","Added Units with 3+ bedrooms cell","J. Tsang 11/13/20"]
["Rental",5.0999999999999996,"Large Units","Added Households Requiring Large Units tab and score calculation","J. Tsang 11/13/20"]
["Rental",5.0999999999999996,"AHP Application Comparison ","Macro check to include large units check  - Tappan to add","J. LeSauvage (Tappan) 12/16/20"]
["Rental",5.0999999999999996,"Benchmarks - Hard debt term - lowest","Formula fixed for term of permanent financing (B39). Replaced MINIFS formula as it is not available to non-Office 365 users (please check logic)","J. Tsang 11/13/20, formula tested 11/20/20 R. Wirtz"]
["Rental",5.0999999999999996,"Benchmarks - Interest rate - highest","Formula fixed for term of interest rate assumptions (B40). Replaced MAXIFS formula as it is not available to non-Office 365 users (please check logic)","J. Tsang 11/13/20, formula tested 11/20/20 R. Wirtz"]
["Rental",5.0999999999999996,"Subsidy per Unit","Range increased to $15,000-$40,000","J. Tsang 11/13/20"]
["Rental",5.0999999999999996,"All tabs","New version number (5.1)","R. Wirtz 11/19/20"]
["Rental",5.0999999999999996,"Sources & Uses of Funds","Unlocked debt service payment cell for one column to allow for interest-only payments or HCD mandatory debt fee payments","R. Wirtz 11/20/20"]
["Rental",5.0999999999999996,"Workbook Instructions & Notes","Added note to explain new ability to put interest-only debt payments in sources & uses tab","R. Wirtz 11/20/20"]
["Rental",5.0999999999999996,"Targeting","Adjusted formatting for 30% rent test so that it displays \"Pass\" if total rent received and rental subsidy are both 0","R. Wirtz 12/2/20"]
["Rental",5.0999999999999996,"Benchmarks","Adjusted formatting to make sure that if net cash flow in year 1 is $0, a percentage should not be calculated","R. Wirtz 11/20/20"]
["Rental",5.0999999999999996,"Targeting Self-Score","Fixed cell C17. Cell references Targeting!M19..M296, however, correct reference should be Targeting!N19..N296","R. Wirtz 11/20/20"]
["Rental",5.0999999999999996,"Targeting","Adjusted cells D19-D22 to mimic D23-D39/fixed column D to correct avg. BR calculation","R. Wirtz 11/20/20"]
["Rental",5.0999999999999996,"Benchmarks","Deleted\"site work\" from description of CCPSF","R. Wirtz 11/20/20"]
["Rental",5.0999999999999996,"Workbook Instructions & Notes","Made clear that soft debt terms do not need to be input into financial worksheets - bolded font","R. Wirtz 11/20/20"]
["Rental",5.0999999999999996,"Targeting","Changed AMI years to reference 2021 and 2020 instead of 2020 and 2019","R. Wirtz 11/20/20"]
["Rental",5.0999999999999996,"Sources & Uses of Funds","Fixed gray formatting for loan terms section for when \"soft\" debt is selected in row 97","R. Wirtz 12/2/20"]
["Rental",5.0999999999999996,"Operating Pro Forma","Removed formula from F33 to allow users to input their own mandatory debt fee payment amounts","R. Wirtz 12/2/20"]
["Rental",5.0999999999999996,"Operating Pro Forma","Added back in formula to trend management fee in line 18","R. Wirtz 12/2/20"]
["Rental",5.0999999999999996,"Operating Pro Forma","Added ROUNDDOWN formula for cell B35 - now, does not display anything in the cell if net cash flow is less than 0.1%","R. Wirtz 12/2/20"]
["Rental",5.0999999999999996,"AHP Application Comparison ","Added lines for large units and subsidy per unit, Jeff to add the macros, see above","R. Wirtz 12/2/20"]
["Rental",5.0999999999999996,"All tabs","Hid Subsidy/Unit and Households Requiring Large Units tabs","R. Wirtz 12/2/20"]
["Rental",5.0999999999999996,"Data Import Summary","Added row to import data for # of Households Requiring Large Units","J. Tsang 12/8/20"]
["Rental",5.0999999999999996,"Workbook Instructions & Notes","Removed section on Subsidy per Unit tab","R. Wirtz 12/30/20"]
["Rental",5.0999999999999996,"All tabs","Updated date to 1/26, when applications are published","R. Wirtz 12/30/20"]