{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Cover Page","Rents - Affordability","Project Budget ","Residential New - Proforma ","Non-Residential - Proforma "],"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":10,"rows_kept":16,"cut":false,"hidden":false,"merged":[[3,1,14,10],[15,1,18,10]]}
[]
[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,null,null,null,"Affordable Rent Ranges as percentage of MMR "]
[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 Monthly Market 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,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","Monthly Market","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 (PGI)","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,null,{"$f":"F12*G12","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($G$12=0,0,IF($G$12<=$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,{"$f":"E13","v":0},{"$f":"$D13*$E13*12","v":0}]
[null,"1 bed (incl. 1 bed plus den) - affordable",null,null,null,null,null,{"$f":"F14*G14","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($G$14=0,0,IF($G$14<=$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,{"$f":"E15","v":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,null,{"$f":"F16*G16","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($G$16<=$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,{"$f":"E17","v":0},{"$f":"$D17*$E17*12","v":0},null,"Test 2"]
[null,"3 bed (incl. 3 bed plus den) - affordable",null,null,null,null,null,{"$f":"F18*G18","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($G$18<=$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,0,null,{"$f":"E19","v":0},{"$f":"$D19*$E19*12","v":0},null,"Median Market rent (weighted avg.)",null,{"$f":"R26","v":0}]
[null,"Other - (Shelter space, SRO rooms, beds)",null,null,null,null,null,{"$f":"F20*G20","v":0},{"$f":"$D20*$H20*12","v":0},null,"Subject (weighted avg of affordable rents)",null,{"$f":"T26","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($G$20<=$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,"Helpful Tips for accessing Median Market Rents on CMHC Market Analysis Portal",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,"1) Click on Link - \"HOUSING MARKET INFORMATION PORTAL\""]
[null,"2) In the website, click on \"Housing Market Information Portal\". "]
[null,"3) In the black ribbon along the top, select \"Tables\". "]
[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,"30% of Units at or below 80% MMR ",null,{"$f":"'Rents - Affordability'!K12","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,"31% to 40% of Units below 80% MMR ",null,{"$f":"IF('Rents - Affordability'!K11>=30.99%,\"Yes\",\"No\")","v":"No"}]
[null,"6) Choose the most specific zone (or Market Area / Subdivision) applicable for your project.",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('Rents - Affordability'!K11>=40.99%,\"Yes\",\"No\")","v":"No"}]
[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,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('Rents - Affordability'!K11>=50.99%,\"Yes\",\"No\")","v":"No"}]
[null,"7) In the tables on the left panel, select \"Primary Rental Market\", then \"Median Rent ($)\". ",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('Rents - Affordability'!K11>=54.99%,\"Yes\",\"No\")","v":"No"}]
[null,"8) Enjoy completing the \"Rents - Affordability\" table. "]

{"sheet":"Project Budget ","index":2,"n_rows":52,"n_cols":13,"rows_kept":52,"cut":false,"hidden":false,"merged":[[12,8,12,11],[11,8,11,11],[7,1,7,6]]}
[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: NHCF Investment limited to 75% of cost on non-residential component. "]
[null,"Project DCR",{"$f":"IF(C11=0,0,'Residential New - Proforma '!C12)","v":0}]
[null,"Project LTC (all NHCF Investment)",{"$f":"IF(C13=0,0,C13/E31)","v":0},null,"Per unit"]
[null,"NHCF Loan",{"$f":"'Residential New - Proforma '!C7","v":0},null,{"$f":"IF(C11=0,0,C11/E20)","v":0}]
[null,"NHCF Contribution",{"$f":"'Residential New - Proforma '!C6","v":0},null,{"$f":"IF(C12=0,0,C12/E20)","v":0}]
[null,"NHCF Investment",{"$f":"SUM(C11:C12)","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":"L18","v":0},null,null,null,null,null,null,{"$f":"SUM(I18:K18)","v":0}]
[null,"Proportion of total",null,null,{"$f":"IF(L18=0,0,L19)","v":0},null,null,null,{"$f":"IF(L18=0,0,I18/L18)","v":0},null,{"$f":"IF(L18=0,0,K18/L18)","v":0},{"$f":"SUM(I19:K19)","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,"Land",null,null,null,{"$f":"IF($E$20=0,0,E23/$E$20)","v":0},null,null,{"$f":"E23*I$19","v":0},null,{"$f":"E23*K$19","v":0},{"$f":"SUM(I23:K23)","v":0}]
[null,"Hard costs",null,null,null,{"$f":"IF($E$20=0,0,E24/$E$20)","v":0},null,null,0,null,0,0]
[null,"Soft costs",null,null,null,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,"Contingency",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(E23:E30)","v":0},0,null,null,{"$f":"SUM(I23:I30)","v":0},null,{"$f":"SUM(K23:K30)","v":0},0]
[]
[null,"Other Sources of Funding"]
[null,"Other Debt Financing (Non NHCF)",null,null,null,{"$f":"IF($E$20=0,0,E34/$E$20)","v":0},null,null,null,null,null,{"$f":"E34","v":0}]
[null,"NHCF Contribution (refer to Funding Eligibility Worksheet for potential NHCF Funding)",null,null,null,{"$f":"IF($E$20=0,0,E35/$E$20)","v":0},null,null,{"$f":"E35*I19","v":0},null,{"$f":"E35*K19","v":0},{"$f":"E35","v":0}]
[null,"            Other Grants / Contributions",null,null,null,0,null,null,null,null,null,0]
[null,"Land Value Equity",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(E34:E42)","v":0},0,null,null,null,null,null,{"$f":"SUM(L34:L42)","v":0}]
[]
[null,"NHCF Loan Requested / Required",null,null,{"$f":"E31-E43","v":0},0,null,null,null,null,null,{"$f":"'Residential New - Proforma '!C7","v":0},"NHCF Loan Recommended"]
[]
[null,"Total Sources",null,null,{"$f":"E43+E45","v":0},{"$f":"IF($E$20=0,0,E47/$E$20)","v":0},null,null,null,null,null,{"$f":"SUM(L43:L46)","v":0}]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,{"$f":"L31-L47","v":0},"Additional Funding Required"]
[]
[null,null,null,null,null,null,null,null,"Total Recommended Funding ",null,null,{"$f":"SUM(L47:L51)","v":0}]

{"sheet":"Residential New - Proforma ","index":3,"n_rows":106,"n_cols":11,"rows_kept":106,"cut":false,"hidden":false,"merged":[[90,8,90,9],[8,6,8,10],[37,8,37,9],[36,8,36,9]]}
[null,"SUMMARY"]
[null,null,null,null,null,null,"Maximum NHCF Investment (percent of eligible costs)"]
[null,"Project Cost",{"$f":"'Project Budget '!E31","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 '!L35","v":0},{"$f":"IF(C6=0,0,C6/$C$3)","v":0},{"$f":"IF(C6=0,0,C6/C21)","v":0},null,"Province / Territory / Municipality",null,null,0.75,0.3]
[null,"NHCF Loan",{"$f":"G97+'Non-Residential - Proforma '!G63","v":0},{"$f":"IF(C7=0,0,C7/$C$3)","v":0},{"$f":"IF(C7=0,0,C7/C21)","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: NHCF Investment limited to 75% of cost on non-residential component. "]
[null,"NHCF Investment within Max eligibility",{"$f":"IF(D8<=D18,\"YES\",\"NO\")","v":"YES"}]
[null,"NHCF Contribution within Max eligibility",{"$f":"IF(D6<=E18,\"YES\",\"NO\")","v":"YES"}]
[null,"Project DCR (All debt payments)",{"$f":"IF(G99+G92+'Non-Residential - Proforma '!G65=0,0,(G84+'Non-Residential - Proforma '!G52)/(G99+G92+'Non-Residential - Proforma '!G65))","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=G7,J7,IF(C18=G6,J6,J5))","v":0.95},{"$f":"IF(C18=G7,K7,IF(C18=G6,K6,K5))","v":0.4}]
[]
[null,"Percentage of Project Costs - Residential ",{"$f":"'Project Budget '!I19","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,"Land",null,{"$f":"IF($C$21=0,0,E25/$C$21)","v":0},{"$f":"C$20*'Project Budget '!E23","v":0}]
[null,"Hard costs",null,{"$f":"IF($C$21=0,0,E26/$C$21)","v":0},{"$f":"C$20*'Project Budget '!E24","v":0}]
[null,"Soft costs",null,0,{"$f":"C$20*'Project Budget '!E25","v":0}]
[null,"Financing costs",null,0,{"$f":"C$20*'Project Budget '!E26","v":0}]
[null,"HST ",null,0,{"$f":"C$20*'Project Budget '!E27","v":0}]
[null,"Contingency",null,0,{"$f":"C$20*'Project Budget '!E28","v":0}]
[null,{"$f":"'Project Budget '!B29","v":"Other (describe)"},null,0,{"$f":"C$20*'Project Budget '!E29","v":0}]
[null,{"$f":"'Project Budget '!B30","v":"Other (describe)"},null,0,{"$f":"C$20*'Project Budget '!E30","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 '!I35","v":0},"Pro-rata Cost * Max funding LESS Pro-rata Contribution"]
[null,"NHCF Loan Requested / Required",{"$f":"'Project Budget '!E45","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":"SUM(G47: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,{"$f":"F75*$C$21","v":0}]
[null,"Other (describe)",null,null,null,null,{"$f":"F76*$C$21","v":0}]
[null,"Other (describe)",null,null,null,null,{"$f":"F77*$C$21","v":0}]
[null,"Other (describe)",null,null,null,null,{"$f":"F78*$C$21","v":0}]
[null,"Replacement Reserve (Mandatory requirement)",null,null,null,0.04,{"$f":"F79*G64","v":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,"6. NHCF Loan -DCR and Ratio Calculation"]
[null,"NHCF Loan (ie. lesser of max funding OR Loan Requested / Required)",null,null,null,null,{"$f":"MIN(C35,C36)","v":0}]
[null,"Interest Rate (contact CMHC to obtain more information about our lending rates)"]
[null,"Loan Amortization ( years)"]
[null,"NHCF Loan Mortgage Payment (monthly) P+I ",null,null,null,null,{"$f":"IF(G87=0, 0, IF(G89=0,0, -PMT((((G88/2)+1)^(1/6))-1, G89*12, G87, 0, 0)))","v":0}]
[null,"NHCF Loan Mortgage Payment (annually) P+I",null,null,null,null,{"$f":"G90*12","v":0}]
[null,"Other Debt Mortgage Payment (annually) P+I"]
[null,"Total Mortgage payments (NHCF loan + Other)",null,null,null,null,{"$f":"SUM(G91:G92)","v":0}]
[null,"Debt Coverage Ratio (DCR) - NHCF Loan Requested / Required  ",null,null,null,null,{"$f":"IF(G91=0, 0, ROUND(G84/(G91+G92),2))","v":0}]
["     Max Loan at minimum DCR",null,null,null,"Minimum DCR",1,{"$f":"IF(G$87=0,0,IF(G89=0,0,(((G84-G92)/F$95/12)/(((1+((G88*100)/200))^(1/6)-1)/(1-((1+(G88*100)/200)^(1/6))^(-12*G89))))))","v":0}]
[]
[null,"Recommended NHCF Loan - Residential Portion",null,null,null,null,{"$f":"MIN(G87,G95)","v":0}]
[null,"Mortgage Payment (monthly) P+I",null,null,null,null,{"$f":"IF(G97=0, 0, -PMT((((G88/2)+1)^(1/6))-1, G89*12, G97, 0, 0))","v":0}]
[null,"Mortgage Payment (annually) P+I",null,null,null,null,{"$f":"G98*12","v":0}]
[null,"Debt Coverage Ratio (DCR) - Recommended NHCF Loan (Excluding Other Debt Payments)",null,null,null,null,{"$f":"IF(G99=0, 0, ROUND(G84/G99,2))","v":0}]
[]
[null,"NHCF loan required can be supported",null,null,null,null,{"$f":"IF(G97=C36,\"Yes\",IF(G97<C36,\"No\",\"Yes\"))","v":"Yes"}]
[null,"Loan - Maximum NHCF loan (Residential portion)",null,null,null,null,{"$f":"G97","v":0}]
[null,"Additional Required Equity and / or funding - Can additional Loan be supported by Non-Residential",null,null,null,null,{"$f":"C36-G97","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(G103=0,0,G97/G105)","v":0}]

{"sheet":"Non-Residential - Proforma ","index":4,"n_rows":66,"n_cols":8,"rows_kept":66,"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 '!K19","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 New - Proforma '!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 '!K18/'Project Budget '!E18","v":{"$error":"#DIV/0!"}},{"$f":"IF(D6<=30%,\"Yes\",\"No\")","v":{"$error":"#DIV/0!"}},"  (Non-Residential portion of floor space cannot exceed 30% of project space)"]
[]
[null,"1. Project budget - Non-Residential portion"]
[null,"Land",{"$f":"D$4*'Project Budget '!E23","v":0}]
[null,"Hard costs",{"$f":"D$4*'Project Budget '!E24","v":0}]
[null,"Soft costs",{"$f":"D$4*'Project Budget '!E25","v":0}]
[null,"Financing costs",{"$f":"D$4*'Project Budget '!E26","v":0}]
[null,"HST ",{"$f":"D$4*'Project Budget '!E27","v":0}]
[null,"Misc. and Buffer",{"$f":"D$4*'Project Budget '!E28","v":0}]
[null,{"$f":"'Project Budget '!B29","v":"Other (describe)"},{"$f":"D$4*'Project Budget '!E29","v":0}]
[null,{"$f":"'Project Budget '!B30","v":"Other (describe)"},{"$f":"D$4*'Project Budget '!E30","v":0}]
[null,"Total Budget (Uses)",{"$f":"SUM(C9:C16)","v":0}]
[]
[null,"Maximum Loan Amount at 75% of costs ",{"$f":"(C17*75%)-'Project Budget '!K35","v":0},"Pro-rata Cost * Max funding LESS Pro-rata Contribution"]
[null,"Loan Still Required",{"$f":"'Residential New - Proforma '!G104","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,"4. Loan - DCR and Ratio Calculation"]
[null,"Loan (Non-Residential component)",null,null,null,null,{"$f":"MIN(C19,C20)","v":0},"Lesser of 75% LTC and Loan still required"]
[null,"Interest Rate",null,null,null,null,{"$f":"'Residential New - Proforma '!G88","v":0}]
[null,"Loan Amortization ( years)",null,null,null,null,{"$f":"'Residential New - Proforma '!G89","v":0}]
[null,"Mortgage Payment (monthly) P+I",null,null,null,null,{"$f":"IF(G55=0, 0, -PMT((((G56/2)+1)^(1/6))-1, G57*12, G55, 0, 0))","v":0}]
[null,"Mortgage Payment (annually) P+I",null,null,null,null,{"$f":"G58*12","v":0}]
[null,"Debt Coverage Ratio (DCR) ",null,null,null,null,{"$f":"IF(G59=0, 0, G52/G59)","v":0}]
[null,"Max loan at Min DCR",null,"Minimum DCR",null,1.4,{"$f":"IF(G$55=0,0,((G52/F61/12)/(((1+((G56*100)/200))^(1/6)-1)/(1-((1+(G56*100)/200)^(1/6))^(-12*G57)))))","v":0}]
[]
[null,"Recommended NHCF Loan - Non-Residential Portion",null,null,null,null,{"$f":"MIN(G55,G61,C20)","v":0}]
[null,"Mortgage Payment (monthly) P+I",null,null,null,null,{"$f":"IF(G63=0, 0, -PMT((((G56/2)+1)^(1/6))-1, G57*12, G63, 0, 0))","v":0}]
[null,"Mortgage Payment (annually) P+I",null,null,null,null,{"$f":"G64*12","v":0}]
[null,"Debt Coverage Ratio (DCR) - on Non-Residential loan only",null,null,null,null,{"$f":"IF(G65=0, 0, ROUND(G52/G65,2))","v":0}]