{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Cover Page","Rents - Affordability","Project Budget ","Residential","Non-Residential "],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":10,"comments":0,"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":"Cover Page","index":0,"n_rows":16,"n_cols":11,"rows_kept":16,"cut":false,"hidden":false,"merged":[[3,1,14,10],[15,1,18,10]]}
[]
[]
[null,null,null,null,null,null,null,null,null,"Version","2018-06-26"]
[null,"Legal Disclaimer and Notice  - This Assessment Tool provided by CMHC is for general illustrative and estimation purposes only. The amounts it projects are based upon the information provided and the assumptions and estimates made by the model.  CMHC does not guarantee these projections. They are not intended to provide financial or other advice on particular housing projects and they should not be relied upon in that regard.  Neither CMHC nor any of its employees or advisors shall have any liability for the accuracy of the projections contained in the tool or for any damage caused by the use of the information.   The use of this Assessment Tool and its results with regard to any housing project do not oblige CMHC to insure the project financing nor provide funding for the housing project under this initiative or under any other CMHC program or initiative.  You are encouraged to contact your local CMHC Affordable Housing Consultant for assistance with this tool."]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"This Assessment Tool is protected by copyright and is subject to the protection of intellectual property laws. Users are authorized to download the Assessment Tool for its use in relation to the National Housing Co-Investment Fund only and may not further copy, reproduce, modify or distribute the Assessment Tool without the prior written consent of CMHC."]

{"sheet":"Rents - Affordability","index":1,"n_rows":35,"n_cols":24,"rows_kept":35,"cut":false,"hidden":false,"merged":[[5,10,5,11],[16,10,16,11],[4,10,4,11]]}
["Note: Only YELLOW hi-lighted fields that are applicable need to be input. "]
[]
[null,"Proforma Rents - Residential",null,"Determining Rent Levels:",null,null,null,null,null,null,"To obtain data on Median Market rents (MMR) for your area, "]
[null,null,null,"1) For units to be deemed \"Affordable\", rent must be below 80% of Median Market Rent for unit type",null,null,null,null,null,null,"please use the following link:"]
[null,"Province",null,"2) Remainder of units to be included as \"Market\"",null,null,null,null,null,null,"HOUSING MARKET INFORMATION PORTAL"]
[null,"City",null,"3) Input average rent for each unit type under Subject Monthly Rent",null,null,null,null,null,null,"Test 1"]
[null,"Zone",null,null,null,null,null,null,null,null,"30% of units with ",null,null,null,null,null,null,"Affordable Rent Ranges as percentage of MMR "]
[null,null,null,null,null,null,null,"Monthly","Annual",null,"rents below 80% of ",null,null,null,null,null,null,null,"70% to","60% to","50% to","Below"]
[null,null,"Unit","Number","Subject Monthly","Monthly Median ","Affordable Rent","Proforma","PGI Proforma",null,"MMR",null,null,null,null,null,null,0.8,0.79,0.69,0.59,0.5]
[null,"Unit type","Size (sq ft)","of Units","Rent ","Mkt Rent (MMR)","as % of MMR ","Rents ","Total",null,{"$f":"X22","v":0},"# Units below 80% of MMR",null,null,null,null,null,0.8,0.79,0.69,0.59,0.5]
[null,"Bachelor - market",null,null,null,null,null,{"$f":"E11","v":0},{"$f":"$D11*$E11*12","v":0},null,{"$f":"X23","v":0},"% Units that meet Test"]
[null,"Bachelor - affordable",null,null,null,null,{"$f":"IF(F12=0,\"-\",E12/F12)","v":"-"},{"$f":"E12","v":0},{"$f":"$D12*$H12*12","v":0},null,{"$f":"IF(K11>=30%, \"Yes\", \"No\")","v":"No"},"30% Condition met?",null,null,null,null,null,{"$f":"IF(G12=0,0,IF($G$12<=$R$10,D12,0))","v":0},{"$f":"IF(G12=0,0,IF(G12<=$S$10,D12,0))","v":0},{"$f":"IF(G12=0,0,IF(G12<=$T$10,D12,0))","v":0},{"$f":"IF(G12=0,0,IF(G12<=$U$10,D12,0))","v":0},{"$f":"IF(G12=0,0,IF(G12<$V$10,D12,0))","v":0},null,{"$f":"IF(R12=0,\"No\",IF(G12<=80%,\"Yes\", \"No\"))","v":"No"}]
[null,"1 bed (incl. 1 bed plus den) - market",null,null,null,null,null,0,{"$f":"$D13*$E13*12","v":0}]
[null,"1 bed (incl. 1 bed plus den) - affordable",null,null,null,null,{"$f":"IF(F14=0,\"-\",E14/F14)","v":"-"},0,{"$f":"$D14*$H14*12","v":0},null,"# of Affordable",{"$f":"K10","v":0},null,null,null,null,null,{"$f":"IF(G14=0,0,IF($G$14<=$R$10,D14,0))","v":0},{"$f":"IF(G14=0,0,IF(G14<=$S$10,D14,0))","v":0},{"$f":"IF(G14=0,0,IF(G14<=$T$10,D14,0))","v":0},{"$f":"IF(G14=0,0,IF(G14<=$U$10,D14,0))","v":0},{"$f":"IF(G14=0,0,IF(G14<$V$10,D14,0))","v":0},null,{"$f":"IF(R14=0,\"No\",IF(G14<=80%,\"Yes\", \"No\"))","v":"No"}]
[null,"2 bed (incl. 2 bed plus den) - market",null,null,null,null,null,0,{"$f":"$D15*$E15*12","v":0},null,"# of Market ",{"$f":"D22-L14","v":0}]
[null,"2 bed (incl. 2 bed plus den) - affordable",null,null,null,null,{"$f":"IF(F16=0,\"-\",E16/F16)","v":"-"},0,{"$f":"$D16*$H16*12","v":0},null,"Total",{"$f":"SUM(L14:L15)","v":0},null,null,null,null,null,{"$f":"IF(G16=0,0,IF($G$16<=$R$10,D16,0))","v":0},{"$f":"IF(G16=0,0,IF(G16<=$S$10,D16,0))","v":0},{"$f":"IF(G16=0,0,IF(G16<=$T$10,D16,0))","v":0},{"$f":"IF(G16=0,0,IF(G16<=$U$10,D16,0))","v":0},{"$f":"IF(G16=0,0,IF(G16<$V$10,D16,0))","v":0},null,{"$f":"IF(R16=0,\"No\",IF(G16<=80%,\"Yes\", \"No\"))","v":"No"}]
[null,"3 bed (incl. 3 bed plus den) - market",null,null,null,null,null,0,{"$f":"$D17*$E17*12","v":0},null,"Test 2"]
[null,"3 bed (incl. 3 bed plus den) - affordable",null,null,null,null,{"$f":"IF(F18=0,\"-\",E18/F18)","v":"-"},0,{"$f":"$D18*$H18*12","v":0},null,"Average rental cost of affordable units as a % of Average MMR",null,null,null,null,null,null,{"$f":"IF(G18=0,0,IF($G$18<=$R$10,D18,0))","v":0},{"$f":"IF(G18=0,0,IF(G18<=$S$10,D18,0))","v":0},{"$f":"IF(G18=0,0,IF(G18<=$T$10,D18,0))","v":0},{"$f":"IF(G18=0,0,IF(G18<=$U$10,D18,0))","v":0},{"$f":"IF(G18=0,0,IF(G18<$V$10,D18,0))","v":0},null,{"$f":"IF(R18=0,\"No\",IF(G18<=80%,\"Yes\", \"No\"))","v":"No"}]
[null,"Other - (Shelter space, SRO rooms, beds)",null,null,null,null,null,0,{"$f":"$D19*$E19*12","v":0},null,"Median Market rent (weighted avg.)",null,{"$f":"R25","v":0}]
[null,"Other - (Shelter space, SRO rooms, beds)",null,null,null,null,{"$f":"IF(F20=0,\"-\",E20/F20)","v":"-"},0,{"$f":"$D20*$H20*12","v":0},null,"Subject (weighted avg of affordable rents)",null,{"$f":"T25","v":0},null,null,null,null,{"$f":"IF(G20=0,0,IF($G$20<=$R$10,D20,0))","v":0},{"$f":"IF(G20=0,0,IF(G20<=$S$10,D20,0))","v":0},{"$f":"IF(G20=0,0,IF(G20<=$T$10,D20,0))","v":0},{"$f":"IF(G20=0,0,IF(G20<=$U$10,D20,0))","v":0},{"$f":"IF(G20=0,0,IF(G20<$V$10,D20,0))","v":0},null,{"$f":"IF(R20=0,\"No\",IF(G20<=80%,\"Yes\", \"No\"))","v":"No"}]
[null,"Total Square Feet (Residential)",{"$f":"(C11*D11)++(C12*D12)+(C13*D13)+(C14*D14)+(C15*D15)++(C16*D16)+(C17*D17)+(C18*D18)+(C19*D19)+(C20*D20)","v":0},null,null,null,null,null,{"$f":"$D21*$H21*12","v":0},null,"Average level of Affordability",null,{"$f":"IF(L14=0,0,M20/M19)","v":0}]
[null,"Total Residential Units",null,{"$f":"SUM(D11:D21)","v":0},null,null,null,null,{"$f":"SUM(I11:I21)","v":0},null,null,null,null,null,null,null,null,{"$f":"IF(D22=0, 0,(R12+R14+R16+R18++R20)/D22)","v":0},{"$f":"IF(D22=0, 0,(+S12+S14++S16+S18+S20)/D22)","v":0},{"$f":"IF(D22=0, 0,(T12+T14+T16+T18+T20)/D22)","v":0},{"$f":"IF(D22=0, 0,(U12+U14+U16+U18+U20)/D22)","v":0},{"$f":"IF(D22=0, 0,(V12+V14+V16+V18+V20)/D22)","v":0},null,{"$f":"IF(X12=\"Yes\",D12, 0)+IF(X14= \"yes\", D14, 0)+IF(X16=\"yes\",D16,0)+IF(X18=\"yes\",D18,0)+IF(X20=\"yes\",D20,0)","v":0}]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(D22=0,0,X22/D22)","v":0}]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,{"$f":"IF(L14=0,0,(($D$12*F12)+($D$14*F14)+($D$16*F16)+($D$18*F18)+($D$20*F20)+($D$21*F21))/L14)","v":0},null,{"$f":"IF(L14=0,0,(($D$12*H12)+($D$14*H14)+($D$16*H16)+($D$18*H18)+($D$20*H20)+($D$21*H21))/L14)","v":0}]
[null,"Helpful Tips for accessing Median Market Rents on CMHC Market Analysis Portal"]
[null,"1) Click on Link - \"HOUSING MARKET INFORMATION PORTAL\" (cell K5 above)",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"30% of Units at or below  80% MMR ",null,{"$f":"K12","v":"No"}]
[null,"2) In the website, click on \"Housing Market Information Portal\". ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"31% to 40% of Units below 80% MMR ",null,{"$f":"IF(K11>=30.99%,\"Yes\",\"No\")","v":"No"}]
[null,"3) In the black ribbon along the top, select \"Tables\". ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"41% to 50% of Units below 80% MMR ",null,{"$f":"IF(K11>=40.99%,\"Yes\",\"No\")","v":"No"}]
[null,"4) Choose the province in which your project is located.",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"51% to 55% of Units below 80% MMR ",null,{"$f":"IF(K11>=50.99%,\"Yes\",\"No\")","v":"No"}]
[null,"5) Choose the city or area in which your project is located. ",null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,"More than 55% of Units below 80% MMR ",null,{"$f":"IF(K11>=54.99%,\"Yes\",\"No\")","v":"No"}]
[null,"6) Choose the most specific zone (or Market Area / Subdivision) applicable for your project."]
[null,"Note: If your project is not located in a surveyed centre or you are unable to find the required data, please contact your Affordable Housing Consultant. "]
[null,"7) In the tables on the left panel, select \"Primary Rental Market\", then \"Median Rent ($)\". "]
[null,"8) Enjoy completing the \"Rents - Affordability\" table. "]

{"sheet":"Project Budget ","index":2,"n_rows":53,"n_cols":13,"rows_kept":53,"cut":false,"hidden":false,"merged":[[8,1,8,6],[9,1,10,6],[15,8,15,11],[14,8,14,11]]}
[null,"SUMMARY"]
[null,"Maximum NHCF Investment (percent of eligible costs)"]
[null,null,"Total funding","Contribution"]
[null,null,"Up to","Up to"]
[null,"Non-Profit, Co-op, Indigenous Groups",0.95,0.4]
[null,"Province / Territory, Municipality",0.75,0.3]
[null,"For-Profit",0.75,0.15]
[null,"Note: "]
[null,"(i) NHCF Investment limited to 75% of cost on non-residential component. "]
[null,"(ii) For Repairs (Renewal) of existing buildings, maximum NHCF Investment is limited to 85% of lending value (i.e. market value) for the residential component"]
[]
[null,"Project DCR",{"$f":"IF(C14=0,0,Residential!C12)","v":0}]
[null,"Project LTC (all NHCF Investment)",{"$f":"IF(C16=0,0,C16/E34)","v":0},null,"Per unit"]
[null,"NHCF Loan",{"$f":"Residential!C7","v":0},null,{"$f":"IF(C14=0,0,C14/E23)","v":0}]
[null,"NHCF Contribution",{"$f":"Residential!C6","v":0},null,{"$f":"IF(C15=0,0,C15/E23)","v":0}]
[null,"NHCF Investment",{"$f":"SUM(C14:C15)","v":0}]
[]
["Note: Only YELLOW hi-lighted fields that are applicable need to be input. "]
[null,"Total Project Budget Costs"]
[null,"Project Characteristics",null,null,null,null,null,null,"Residential",null,"Non-Residential","Combined - verify Uses and Sources"]
[null,"Total sq feet (Gross Floor Area estimated)",null,null,{"$f":"L21","v":0},null,null,null,null,null,null,{"$f":"SUM(I21:K21)","v":0}]
[null,"Proportion of total",null,null,{"$f":"IF(L21=0,0,L22)","v":0},null,null,null,{"$f":"IF(L21=0,0,I21/L21)","v":0},null,{"$f":"IF(L21=0,0,K21/L21)","v":0},{"$f":"SUM(I22:K22)","v":0}]
[null,"Number of Residential units",null,null,{"$f":"'Rents - Affordability'!D22","v":0}]
[]
[null,"1. Project budget",null,null,"Total Project Costs","Per Unit",null,null,"Pro-Rata Project Costs"]
[null,"Hard costs",null,null,null,{"$f":"IF($E$23=0,0,E26/$E$23)","v":0},null,null,{"$f":"E26*I$22","v":0},null,{"$f":"E26*K$22","v":0},{"$f":"SUM(I26:K26)","v":0}]
[null,"Soft costs",null,null,null,{"$f":"IF($E$23=0,0,E27/$E$23)","v":0},null,null,0,null,0,0]
[null,"Financing costs",null,null,null,0,null,null,0,null,0,0]
[null,"HST ",null,null,null,0,null,null,0,null,0,0]
[null,"Misc and Buffer",null,null,null,0,null,null,0,null,0,0]
[null,"Existing Debt",null,null,null,0,null,null,0,null,0,0]
[null,"Other (describe)",null,null,null,0,null,null,0,null,0,0]
[null,"Other (describe)",null,null,null,0,null,null,0,null,0,0]
[null,"Total Budget (Uses)",null,null,{"$f":"SUM(E26:E33)","v":0},0,null,null,{"$f":"SUM(I26:I33)","v":0},null,{"$f":"SUM(K26:K33)","v":0},0]
[]
[null,"Other Sources of Funding"]
[null,"Other Debt Financing (Non NHCF)",null,null,null,{"$f":"IF($E$23=0,0,E37/$E$23)","v":0},null,null,null,null,null,{"$f":"E37","v":0}]
[null,"NHCF Contribution (refer to Funding Eligibility Worksheet for potential NHCF funding)",null,null,null,{"$f":"IF($E$23=0,0,E38/$E$23)","v":0},null,null,{"$f":"E38*I22","v":0},null,{"$f":"E38*K22","v":0},{"$f":"E38","v":0}]
[null,"            Other Grants / Contributions",null,null,null,0,null,null,null,null,null,0]
[null,"Owner cash equity",null,null,null,0,null,null,null,null,null,0]
[null,"Other (describe)",null,null,null,0,null,null,null,null,null,0]
[null,"Other (describe)",null,null,null,0,null,null,null,null,null,0]
[null,"Other (Dev Charge waiver )",null,null,null,0,null,null,null,null,null,0]
[null,"CMHC Seed",null,null,null,0,null,null,null,null,null,0]
[null,"Total Other Sources",null,null,{"$f":"SUM(E37:E44)","v":0},{"$f":"IF($E$23=0,0,E45/$E$23)","v":0},null,null,null,null,null,{"$f":"SUM(L37:L44)","v":0}]
[]
[null,"NHCF Loan Requested / Required ",null,null,{"$f":"E34-E45","v":0},{"$f":"IF($E$23=0,0,E47/$E$23)","v":0},null,null,null,null,null,{"$f":"Residential!C7","v":0},"NHCF Loan Recommended"]
[]
[null,"Total Sources",null,null,{"$f":"E45+E47","v":0},{"$f":"IF($E$23=0,0,E49/$E$23)","v":0},null,null,null,null,null,{"$f":"SUM(L45:L48)","v":0}]
[]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"L34-L49","v":0},"Additional Funding Required"]
[]
[null,null,null,null,null,null,null,null,"Total Recommended Sources",null,null,{"$f":"SUM(L49:L52)","v":0}]

{"sheet":"Residential","index":3,"n_rows":110,"n_cols":11,"rows_kept":110,"cut":false,"hidden":false,"merged":[[94,8,94,9],[37,8,37,9],[38,8,38,9],[10,6,11,10]]}
[null,"SUMMARY"]
[null,null,null,null,null,null,"Maximum NHCF Investment (percent of eligible costs)"]
[null,"Project Cost",{"$f":"'Project Budget '!E34","v":0},null,null,null,null,null,null,"Total funding","Contribution"]
[null,null,null,null,null,null,null,null,null,"Up to","Up to"]
[null,null,"NHCF Investment","Percent of Cost","Per unit",null,"Non-Profit / Co-op / Indigenous Group",null,null,0.95,0.4]
[null,"NHCF Contribution",{"$f":"'Project Budget '!L38","v":0},{"$f":"IF(C6=0,0,C6/$C$3)","v":0},{"$f":"IF(C6=0,0,C6/$C$21)","v":0},null,"Province / Territory / Municipality",null,null,0.75,0.3]
[null,"NHCF Loan",{"$f":"IF(G101=0,0,G101+'Non-Residential '!G67)","v":0},{"$f":"IF(C7=0,0,C7/$C$3)","v":0},{"$f":"IF(C7=0,0,C7/$C$21)","v":0},null,"For-Profit (Private Sector)",null,null,0.75,0.15]
[null,"Total NHCF Investment",{"$f":"SUM(C6:C7)","v":0},{"$f":"IF(C8=0,0,C8/$C$3)","v":0}]
[null,null,null,null,null,null,"Note: "]
[null,"NHCF Investment within Max eligibility",{"$f":"IF(D8<=D18,\"YES\",\"NO\")","v":"YES"},null,null,null,"(i) NHCF Investment limited to 75% of cost on non-residential component. "]
[null,"NHCF Contribution within Max eligibility",{"$f":"IF(D6<=E18,\"YES\",\"NO\")","v":"YES"},null,null,null,"(ii) For Repairs (Renewal) of existing buildings, maximum NHCF Investment is limited to 85% of lending value (ie. market value). "]
[null,"Project DCR (All debt payments)",{"$f":"IF(G103+G96+'Non-Residential '!G69=0,0,(G84+'Non-Residential '!G52)/(G103+G96+'Non-Residential '!G69))","v":0}]
[null,"Additional Required Equity and/or funding ",{"$f":"C36-C7","v":0}]
[]
["Note: Only YELLOW hi-lighted fields that are applicable need to be input. "]
[null,"Residential New: Financial Viability Assessment"]
[null,null,"Eligibility","Funding - Max","Contribution - Max"]
[null,"Proponent Type (please select)",null,{"$f":"IF(C18=\"For-Profit (Private Sector)\",J7,IF(C18=G6,J6,J5))","v":0.95},{"$f":"IF(C18=\"For-Profit (Private Sector)\",K7,IF(C18=G6,K6,K5))","v":0.4}]
[]
[null,"Percentage of Project Costs - Residential ",{"$f":"'Project Budget '!I22","v":0}]
[null,"Number of Residential Units",{"$f":"'Rents - Affordability'!D22","v":0}]
[]
[null,"1. Project budget"]
[null,null,null,"Per Unit","New Rental"]
[null,"Hard costs",null,{"$f":"IF($C$21=0,0,E25/$C$21)","v":0},{"$f":"C$20*'Project Budget '!E26","v":0}]
[null,"Soft costs",null,{"$f":"IF($C$21=0,0,E26/$C$21)","v":0},{"$f":"C$20*'Project Budget '!E27","v":0}]
[null,"Financing costs",null,0,{"$f":"C$20*'Project Budget '!E28","v":0}]
[null,"HST ",null,0,{"$f":"C$20*'Project Budget '!E29","v":0}]
[null,"Misc. and buffer",null,0,{"$f":"C$20*'Project Budget '!E30","v":0}]
[null,"Existing Debt",null,0,{"$f":"C$20*'Project Budget '!E31","v":0}]
[null,{"$f":"'Project Budget '!B32","v":"Other (describe)"},null,0,{"$f":"C$20*'Project Budget '!E32","v":0}]
[null,{"$f":"'Project Budget '!B33","v":"Other (describe)"},null,0,{"$f":"C$20*'Project Budget '!E33","v":0}]
[null,"Total Budget (Uses)",null,0,{"$f":"SUM(E25:E32)","v":0}]
[]
[null,"Maximum Loan at Funding Max. ",{"$f":"D18*E33-'Project Budget '!I38","v":0},"Pro-rata Cost * Max funding LESS Pro-rata Contribution"]
[null,"NHCF Loan Requested / Required",{"$f":"'Project Budget '!E47","v":0}]
[]
[null,"2. Income Analysis- Residential"]
[null,null,null,null,null,null,"Annual PGI Proforma"]
[null,"Potential Gross Income (From Rents - Affordability)",null,null,null,null,{"$f":"'Rents - Affordability'!I22","v":0}]
[null,"Vacancy rate /bad debt"]
[null,"Less: Vacancy rate /bad debt",null,null,null,null,{"$f":"G40*G41","v":0}]
[null,"Effective Residential Gross Income",null,null,null,null,{"$f":"G40-G42","v":0}]
[]
[null,"3. Ancillary Income"]
[null,null,"# Units","Rent"]
[null,"Parking (monthly)",null,null,null,null,{"$f":"D47*C47*12","v":0}]
[null,"Storage lockers (monthly)",null,null,null,null,{"$f":"D48*C48*12","v":0}]
[null,"Laundry (annually)"]
[null,"Total",null,null,null,null,{"$f":"G47+G48+G49","v":0}]
[null,"Vacancy rate /bad debt (%)"]
[null,"Less: Vacancy rate /bad debt",null,null,null,null,{"$f":"G51*G50","v":0}]
[null,"Effective Gross Income",null,null,null,null,{"$f":"G50-G52","v":0}]
[]
[null,"4. Other income (Annual)"]
[null,"Operational Funding (Municipal, Provincial / Territorial, Federal)"]
[null,"Other (describe)"]
[null,"Other (describe)"]
[null,"Subtotal",null,null,null,null,{"$f":"SUM(G56:G58)","v":0}]
[null,"Vacancy rate /bad debt (%)"]
[null,"Less: Vacancy rate /bad debt",null,null,null,null,{"$f":"G60*G59","v":0}]
[null,"Effective Gross Income",null,null,null,null,{"$f":"G59-G61","v":0}]
[]
[null,"Total Effective Gross Income",null,null,null,null,{"$f":"G43+G53+G62","v":0}]
[]
[null,"5. Operating Costs"]
[null,null,null,null,null,"Per Unit/Yr","Annual"]
[null,"Realty / Property Taxes",null,null,null,{"$f":"IF($C$21=0,0,G68/$C$21)","v":0}]
[null,"Insurance",null,null,null,{"$f":"IF($C$21=0,0,G69/$C$21)","v":0}]
[null,"Heat",null,null,null,null,{"$f":"F70*$C$21","v":0}]
[null,"Hydro",null,null,null,null,{"$f":"F71*$C$21","v":0}]
[null,"Water",null,null,null,null,{"$f":"F72*$C$21","v":0}]
[null,"Total Utilities",null,null,null,{"$f":"IF($C$21=0,0,G73/$C$21)","v":0},{"$f":"SUM(G70:G72)","v":0}]
[null,"Repair and Maintenance",null,null,null,null,{"$f":"F74*$C$21","v":0}]
[null,"Wages- Super",null,null,null,null,0]
[null,"Other (describe)",null,null,null,null,0]
[null,"Other (describe)",null,null,null,null,0]
[null,"Other (describe)",null,null,null,null,0]
[null,"Replacement Reserve (Mandatory requirement)",null,null,null,null,0]
[null,"Management Fee (%)",null,null,null,null,{"$f":"F80*G64","v":0}]
[null,"General and Admin (%)",null,null,null,null,{"$f":"F81*G64","v":0}]
[null,"Total Operating Costs",null,null,null,{"$f":"IF($C$21=0,0,G82/$C$21)","v":0},{"$f":"SUM(G68:G72,G74:G81)","v":0},null,"Operating Expense Ratio",null,{"$f":"IF(G64=0,0,G82/G64)","v":0}]
[]
[null,"Net Operating Income (NOI)",null,null,null,null,{"$f":"G64-G82","v":0}]
[]
[null,"Property (Appraised) Value",null,null,null,null,{"$f":"IF(E86=0,0,ROUND(E86,-3))","v":0}]
[null,"Existing Debt to remain in Priority to NHCF Loan"]
[null,"Maximum NHCF Loan at 85% LTV (combined with any Existing Debt)",null,null,null,null,{"$f":"(0.85*G86)-G87","v":0}]
[]
[null,"6. NHCF Loan -DCR and Ratio Calculation"]
[null,"NHCF Loan (ie. lesser of max funding OR Loan Requested / Required OR 85% LTV)",null,null,null,null,{"$f":"MIN(C35,C36,G88)","v":0}]
[null,"Interest Rate (contact CMHC to obtain more information about our lending rates)"]
[null,"Loan Amortization ( years)",null,null,null,null,null,null,"Note: Loan Amortization can not exceed 40 years"]
[null,"NHCF Loan Mortgage Payment (monthly) P+I ",null,null,null,null,{"$f":"IF(G91=0, 0,IF(G93=0,0, -PMT((((G92/2)+1)^(1/6))-1, G93*12, G91, 0, 0)))","v":0}]
[null,"NHCF Loan Mortgage Payment (annually) P+I",null,null,null,null,{"$f":"G94*12","v":0}]
[null,"Other Debt Mortgage Payment (annually) P+I"]
[null,"Total Mortgage payments (NHCF loan + Other)",null,null,null,null,{"$f":"SUM(G95:G96)","v":0}]
[null,"Debt Coverage Ratio (DCR) - NHCF Loan Requested / Required  ",null,null,null,null,{"$f":"IF(G95=0, 0, ROUND(G84/(G95+G96),2))","v":0}]
["     Max Loan at minimum DCR",null,null,null,"Minimum DCR",1,{"$f":"IF(G$91=0,0,IF(G93=0,0,(((G84-G96)/F$99/12)/(((1+((G92*100)/200))^(1/6)-1)/(1-((1+(G92*100)/200)^(1/6))^(-12*G93))))))","v":0}]
[]
[null,"Recommended NHCF Loan - Residential Portion",null,null,null,null,{"$f":"MIN(G91,G99)","v":0}]
[null,"Mortgage Payment (monthly) P+I",null,null,null,null,{"$f":"IF(G101=0, 0, -PMT((((G92/2)+1)^(1/6))-1, G93*12, G101, 0, 0))","v":0}]
[null,"Mortgage Payment (annually) P+I",null,null,null,null,{"$f":"G102*12","v":0}]
[null,"Debt Coverage Ratio (DCR) - Recommended NHCF Loan (Excluding Other Debt Payments)",null,null,null,null,{"$f":"IF(G103=0, 0, ROUND(G84/G103,2))","v":0}]
[]
[null,"NHCF loan required can be supported",null,null,null,null,{"$f":"IF(G101=C36,\"Yes\",IF(G101<C36,\"No\",\"Yes\"))","v":"Yes"}]
[null,"Loan - Maximum NHCF loan (Residential portion)",null,null,null,null,{"$f":"G101","v":0}]
[null,"Additional Required Equity and / or funding - Can additional Loan be supported by Non-Residential",null,null,null,null,{"$f":"C36-G101","v":0},null,"Go to Non-Residential tab"]
[null,"Project Cost - Residential",null,null,null,null,{"$f":"E33","v":0}]
[null,"NHCF Loan to Cost (LTC) - Residential",null,null,null,null,{"$f":"IF(G107=0,0,G101/G109)","v":0}]

{"sheet":"Non-Residential ","index":4,"n_rows":70,"n_cols":8,"rows_kept":70,"cut":false,"hidden":false,"merged":[]}
["Note: Only YELLOW hi-lighted fields that are applicable need to be input. "]
[null,"Non-Residential : Financial Viability Assessment"]
[null,null,null,null,"Check"]
[null,"Percentage of Project Costs for Non-Residential ",null,{"$f":"'Project Budget '!K22","v":0},{"$f":"E6","v":{"$error":"#DIV/0!"}},"  (Non-Residential portion of project costs cannot exceed 30% of project costs)"]
[null,null,null,null,{"$f":"IF(D4+Residential!C20=100%,\"Yes\",\"No\")","v":"No"},"  (Residential and Non-Residential equals 100% of project costs)"]
[null,"Percentage of Project Floor Space for Non-Residential ",null,{"$f":"'Project Budget '!K21/'Project Budget '!E21","v":{"$error":"#DIV/0!"}},{"$f":"IF(D6<=30%,\"Yes\",\"No\")","v":{"$error":"#DIV/0!"}},"  (Non-Residential portion of floor space cannot exceed 30% of total project floor space)"]
[]
[null,"1. Project budget - Non-Residential portion"]
[null,"Hard costs",{"$f":"D$4*'Project Budget '!E26","v":0}]
[null,"Soft costs",{"$f":"D$4*'Project Budget '!E27","v":0}]
[null,"Financing costs",{"$f":"D$4*'Project Budget '!E28","v":0}]
[null,"HST ",{"$f":"D$4*'Project Budget '!E29","v":0}]
[null,"Misc. and buffer",{"$f":"D$4*'Project Budget '!E30","v":0}]
[null,"Existing Debt",{"$f":"D$4*'Project Budget '!E31","v":0}]
[null,"Other (describe)",{"$f":"D$4*'Project Budget '!E32","v":0}]
[null,"Other (describe)",{"$f":"D$4*'Project Budget '!E33","v":0}]
[null,"Total Budget (Uses)",{"$f":"SUM(C9:C16)","v":0}]
[]
[null,"Maximum Loan at Funding Max. ",{"$f":"(C17*75%)-'Project Budget '!K38","v":0},"Less Pro-rata Contribution"]
[null,"Loan Still Required",{"$f":"Residential!G108","v":0}]
[]
[]
[null,"2. Non-Residential / Commercial"]
[null,null,null,"Size (sf)","# Units","Monthly Rent","Annual "]
[null,"Retail/Office 1",null,null,null,null,{"$f":"E25*F25*12","v":0}]
[null,"Retail/Office 2",null,null,null,null,{"$f":"E26*F26*12","v":0}]
[null,"Retail/Office 3",null,null,null,null,{"$f":"E27*F27*12","v":0}]
[null,"Retail/Office 4",null,null,null,null,{"$f":"E28*F28*12","v":0}]
[null,"Add: Recoveries"]
[null,"Total",null,{"$f":"SUM(D25:D28)","v":0},{"$f":"SUM(E25:E28)","v":0},null,{"$f":"SUM(G25:G29)","v":0}]
[null,"Vacancy rate /bad debt"]
[null,"Less: Vacancy rate /bad debt",null,null,null,null,{"$f":"G31*G30","v":0}]
[null,"Effective Gross Income",null,null,null,null,{"$f":"G30-G32","v":0}]
[]
[null,"3. Operating Costs"]
[null,null,null,null,null,null,"Annual"]
[null,"Realty / Property Taxes"]
[null,"Insurance"]
[null,"   Heat"]
[null,"   Hydro"]
[null,"   Water"]
[null,"Total Utilities",null,null,null,null,{"$f":"SUM(G39:G41)","v":0}]
[null,"Repair and Maintenance"]
[null,"Wages- Super"]
[null,"Other (describe)"]
[null,"Other (describe)"]
[null,"Other (describe)"]
[null,"Management Fee (%)",null,null,null,null,{"$f":"F48*$G$33","v":0}]
[null,"General and Admin (%)",null,null,null,null,{"$f":"F49*$G$33","v":0}]
[null,"Total Operating Costs",null,null,null,null,{"$f":"SUM(G37:G41,G43:G49)","v":0}]
[]
[null,"Net Operating Income",null,null,null,null,{"$f":"G33-G50","v":0}]
[]
[null,"Lending Value (NOI / Capitalization rate)","Capitalization Rate",null,null,null,{"$f":"IF(E54=0,0,ROUND(G52/E54,-4))","v":0}]
[null,"Existing Debt to remain in Priority to NHCF Loan"]
[null,"Maximum NHCF Loan at 75% LTV (combined with any Existing Debt)",null,null,null,null,{"$f":"(0.75*G54)-G55","v":0}]
[]
[null,"4. Loan - DCR and Ratio Calculation"]
[null,"Loan (Non-Residential component)",null,null,null,null,{"$f":"MIN(C19,C20,G56)","v":0},"Lesser of 75% LTC, 75% LTV and Loan still required"]
[null,"Interest Rate",null,null,null,null,{"$f":"Residential!G92","v":0}]
[null,"Loan Amortization ( years)",null,null,null,null,{"$f":"Residential!G93","v":0}]
[null,"Mortgage Payment (monthly) P+I",null,null,null,null,{"$f":"IF(G59=0, 0, -PMT((((G60/2)+1)^(1/6))-1, G61*12, G59, 0, 0))","v":0}]
[null,"Mortgage Payment (annually) P+I",null,null,null,null,{"$f":"G62*12","v":0}]
[null,"Debt Coverage Ratio (DCR) ",null,null,null,null,{"$f":"IF(G63=0, 0, G52/G63)","v":0}]
[null,"Max loan at Min DCR",null,"Minimum DCR",null,1.4,{"$f":"IF(G$59=0,0,((G52/F65/12)/(((1+((G60*100)/200))^(1/6)-1)/(1-((1+(G60*100)/200)^(1/6))^(-12*G61)))))","v":0}]
[]
[null,"Recommended NHCF Loan - Non-Residential Portion",null,null,null,null,{"$f":"MIN(G59,G65,C20)","v":0}]
[null,"Mortgage Payment (monthly) P+I",null,null,null,null,{"$f":"IF(G67=0, 0, -PMT((((G60/2)+1)^(1/6))-1, G61*12, G67, 0, 0))","v":0}]
[null,"Mortgage Payment (annually) P+I",null,null,null,null,{"$f":"G68*12","v":0}]
[null,"Debt Coverage Ratio (DCR)  - Non-Residential portion only",null,null,null,null,{"$f":"IF(G69=0, 0, ROUND(G52/G69,2))","v":0}]