{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":[" Summary","Cover Page","Project Budget","Residential New","Non-Residential","Eligibility and Social Outcome","Mortg Calc"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":13,"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":" Summary","index":0,"n_rows":18,"n_cols":2,"rows_kept":18,"cut":false,"hidden":true,"merged":[]}
[]
["Total Project DCR",{"$f":"('Residential New'!I102+'Non-Residential'!G71)/('Residential New'!I111+'Non-Residential'!G79)","v":{"$error":"#DIV/0!"}}]
[]
["Total Project LTC",{"$f":"('Residential New'!I107+'Non-Residential'!G75)/'Project Budget'!E25","v":{"$error":"#DIV/0!"}}]
[]
["Total Loan",{"$f":"'Residential New'!I107+'Non-Residential'!G75","v":0}]
[]
["90% PGI Test",{"$f":"'Residential New'!#REF!","v":{"$error":"#REF!"}}]
[]
["20% of Units at Median HHI Test",{"$f":"'Residential New'!K52","v":"No"}]
[]
["90% of HHI Test",{"$f":"'Residential New'!R58","v":"No"}]
["80% of HHI Test",{"$f":"'Residential New'!S58","v":"No"}]
["70% of HHI Test",{"$f":"'Residential New'!T58","v":"No"}]
[]
["Eligibility Criteria",{"$f":"'Eligibility and Social Outcome'!G32","v":"No"}]
[]
["Social Outcome Score",{"$f":"'Eligibility and Social Outcome'!H90","v":"Up to 90% Res LTC"}]

{"sheet":"Cover Page","index":1,"n_rows":16,"n_cols":2,"rows_kept":16,"cut":false,"hidden":false,"merged":[[3,1,14,10],[15,1,18,10]]}
[]
[]
[]
[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 Representative 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 CMHC Rental Construction Financing Initiative only and may not further copy, reproduce, modify or distribute the Assessment Tool without the prior written consent of CMHC."]

{"sheet":"Project Budget","index":2,"n_rows":36,"n_cols":6,"rows_kept":36,"cut":false,"hidden":false,"merged":[]}
[null,"SUMMARY"]
[]
[null,"Project DCR",{"$f":"IF(OR(('Residential New'!I111+'Non-Residential'!G79)=0, ISERR(('Residential New'!I111+'Non-Residential'!G79))), 0, ('Residential New'!I102+'Non-Residential'!G71)/('Residential New'!I111+'Non-Residential'!G79))","v":0},null,"90% PGI",{"$f":"'Residential New'!G59","v":"Yes"}]
[null,"Project LTC",{"$f":"IF('Project Budget'!E25=0, 0, ('Residential New'!I107+'Non-Residential'!G75)/'Project Budget'!E25)","v":0},null,"20% of Units at Median HHI",{"$f":"'Residential New'!K52","v":"No"}]
[null,"Total Loan",{"$f":"'Residential New'!I107+'Non-Residential'!G75","v":0},null,"90% of HHI",{"$f":"'Residential New'!R58","v":"No"}]
[null,null,null,null,"80% of HHI",{"$f":"'Residential New'!S58","v":"No"}]
[null,"Eligibility Criteria",{"$f":"'Eligibility and Social Outcome'!G32","v":"No"},null,"70% of HHI",{"$f":"'Residential New'!T58","v":"No"}]
[]
[null,"Social Outcome Score",{"$f":"'Eligibility and Social Outcome'!H90","v":"Up to 90% Res LTC"}]
[]
[]
[]
[null,"Total Project Budget Costs"]
[]
[null,"1. Project budget",null,null,"Total Project Costs"]
[]
[null,"Land Market Value"]
[null,"Hard costs"]
[null,"Soft costs"]
[null,"Financing costs"]
[null,"HST "]
[null,"Misc and Buffer"]
[null,"Other (describe)"]
[null,"Other (describe)"]
[null,"Total Budget (Uses)",null,null,{"$f":"SUM(E17:E24)","v":0}]
[]
[null,"Other Sources of Funding"]
[null,"Owner cash equity \n(Cell I125 in 'Residential New' Tab) + Cell G89 in 'Non Residential' Tab)",null,null,{"$f":"'Residential New'!I125+'Non-Residential'!G92","v":0}]
[null,"Land Value Equity \n(Land Market Value - Land Acquisition Cost)"]
[null,"Other Financing (describe )"]
[null,"Other Financing(describe )"]
[null,"Total Other Sources",null,null,{"$f":"SUM(E28:E31)","v":0}]
[]
[null,"Loan Financing",null,null,{"$f":"E25-E32","v":0}]
[]
[null,"Total Sources",null,null,{"$f":"E32+E34","v":0}]

{"sheet":"Residential New","index":3,"n_rows":146,"n_cols":20,"rows_kept":146,"cut":false,"hidden":false,"merged":[[37,6,39,6],[37,7,39,7],[37,8,39,8],[141,0,141,4],[62,5,62,7],[45,10,45,12],[107,4,107,7],[111,4,111,7],[108,4,108,7],[97,4,97,6],[98,4,98,6],[111,9,111,10]]}
[null,"SUMMARY"]
[]
[null,"Project DCR",{"$f":"IF(OR(('Residential New'!I111+'Non-Residential'!G79)=0, ISERR(('Residential New'!I111+'Non-Residential'!G79))), 0, ('Residential New'!I102+'Non-Residential'!G71)/('Residential New'!I111+'Non-Residential'!G79))","v":0},null,"90% PGI",null,{"$f":"G59","v":"Yes"}]
[null,"Project LTC",{"$f":"IF('Project Budget'!E25=0, 0, ('Residential New'!I107+'Non-Residential'!G75)/'Project Budget'!E25)","v":0},null,"20% of Units at Median HHI",null,{"$f":"'Residential New'!K52","v":"No"}]
[null,"Total Loan",{"$f":"'Residential New'!I107+'Non-Residential'!G75","v":0},null,"90% of HHI",null,{"$f":"'Residential New'!R58","v":"No"}]
[null,null,null,null,"80% of HHI",null,{"$f":"'Residential New'!S58","v":"No"}]
[null,"Eligibility Criteria",{"$f":"'Eligibility and Social Outcome'!G32","v":"No"},null,"70% of HHI",null,{"$f":"'Residential New'!T58","v":"No"}]
[]
[null,"Social Outcome Score",{"$f":"'Eligibility and Social Outcome'!H90","v":"Up to 90% Res LTC"}]
[]
[]
[]
[null,"Residential New: Financial Viability Assessment"]
[]
[null,"Percentage of Project Costs of Residential "]
[]
[]
[null,"1. Project budget",null,null,null,"Per Unit","New Rental"]
[null,"Land Input Value",null,null,null,{"$f":"IF($D$58=0, \"\", G19/$D$58)","v":""},{"$f":"D$15*'Project Budget'!E17","v":0}]
[null,"Hard costs",null,null,null,{"$f":"IF($D$58=0, \"\", G20/$D$58)","v":""},{"$f":"D$15*'Project Budget'!E18","v":0}]
[null,"Soft costs",null,null,null,"",{"$f":"D$15*'Project Budget'!E19","v":0}]
[null,"Financing costs",null,null,null,"",{"$f":"D$15*'Project Budget'!E20","v":0}]
[null,"HST ",null,null,null,"",{"$f":"D$15*'Project Budget'!E21","v":0}]
[null,{"$f":"'Project Budget'!B22","v":"Misc and Buffer"},null,null,null,"",{"$f":"D$15*'Project Budget'!E22","v":0}]
[null,{"$f":"'Project Budget'!B23","v":"Other (describe)"},null,null,null,"",{"$f":"D$15*'Project Budget'!E23","v":0}]
[null,{"$f":"'Project Budget'!B24","v":"Other (describe)"},null,null,null,"",{"$f":"D$15*'Project Budget'!E24","v":0}]
[null,"Total Budget (Uses)",null,null,null,"",{"$f":"SUM(G19:G26)","v":0}]
[]
[null,"Sources of Funding"]
[null,"Owner Cash Equity",null,null,null,{"$f":"IF($D$58=0, \"\", G30/$D$58)","v":""},{"$f":"I125","v":0}]
[null,"Land Value Equity",null,null,null,null,{"$f":"D$15*'Project Budget'!E29","v":0}]
[null,{"$f":"'Project Budget'!B30","v":"Other Financing (describe )"},null,null,null,{"$f":"IF($D$58=0, \"\", G32/$D$58)","v":""},{"$f":"D$15*'Project Budget'!E30","v":0}]
[null,{"$f":"'Project Budget'!B31","v":"Other Financing(describe )"},null,null,null,{"$f":"IF($D$58=0, \"\", G33/$D$58)","v":""},{"$f":"D$15*'Project Budget'!E31","v":0}]
[null,"Total Other Sources",null,null,null,{"$f":"IF($D$58=0, \"\", I34/$D$58)","v":""},{"$f":"SUM(G30:G33)","v":0}]
[]
[]
[null,null,null,null,null,null,"Test 1",null,null,null,"Test 2"]
[null,null,null,null,"MONTHLY",null,"Proforma Rents","Potential Gross Income","New Rental Annual Proforma ",null,"20% Affordable",null,null,null,null,null,"Depth of Affordability based on MHHI Level"]
[null,"2. Income Analysis- Residential",null,"Number","Market ","Affordable Rent",null,null,null,null,"(30% x  Median",null,null,null,null,null,1,0.9,0.8,0.7]
[null,"Unit type","Size (sf)","Units","Rent(PGI)","as % of Market",null,null,null,null,"Total Income)",null,null,null,null,null,{"$f":"K48","v":0},{"$f":"(($K$42*R39)*30%)/12","v":0},{"$f":"(($K$42*S39)*30%)/12","v":0},0]
[null,"bachelor-market",null,null,null,null,{"$f":"E41","v":0},{"$f":"(E41*(D41+D42))*12","v":0},{"$f":"G41*D41*12","v":0},null,null,"Minimum Test 2- 30% x Median Income",null,null,null,null,{"$f":"IF(G41<$K$48,D41,0)","v":0},{"$f":"IF($G41<R$40,$D41,0)","v":0},0,0]
[null,"bachelor- affordable",null,null,null,null,{"$f":"E41*F42","v":0},null,0,null,null,"Median Income CMA-StatCan *",null,null,null,null,{"$f":"IF(G42<$K$48,D42,0)","v":0},0,0,0]
[null,"1 bed- market",null,null,null,null,{"$f":"E43","v":0},{"$f":"(E43*(D43+D44))*12","v":0},0,null,{"$f":"K42*0.3","v":0},"30% of Median Total Income",null,null,null,null,0,0,0,0]
[null,"1 bed- affordable",null,null,null,null,{"$f":"E43*F44","v":0},null,0,null,"* Median total income, by family type, by census metropolitan area ",null,null,null,null,null,0,0,0,0]
[null,"1 bed+den- market",null,null,null,null,{"$f":"E45","v":0},{"$f":"(E45*(D45+D46))*12","v":0},0,null,"(All census families)- Stats Canada",null,null,null,null,null,0,0,0,0]
[null,"1 bed+den- affordable",null,null,null,null,{"$f":"E45*F46","v":0},null,0,null,"https://www150.statcan.gc.ca/t1/tbl1/en/tv.action?pid=1110000901",null,null,null,null,null,0,0,0,0]
[null,"2 bed-market",null,null,null,null,{"$f":"E47","v":0},{"$f":"(E47*(D47+D48))*12","v":0},0,null,{"$f":"ROUND(D58*0.2,0)","v":0},"Min # units (rounded)",null,null,null,null,0,0,0,0]
[null,"2 bed - affordable",null,null,null,null,{"$f":"E47*F48","v":0},null,0,null,{"$f":"ROUND(K43/12,0)","v":0},"Max Rent for Test ",null,null,null,null,0,0,0,0]
[null,"2 bed+den- market",null,null,null,null,{"$f":"E49","v":0},{"$f":"(E49*(D49+D50))*12","v":0},0,null,null,null,null,null,null,null,0,0,0,0]
[null,"2 bed+den- affordable",null,null,null,null,{"$f":"E49*F50","v":0},null,0,null,{"$f":"SUM(Q41:Q56)","v":0},"Units meeting Test ",null,null,null,null,0,0,0,0]
[null,"3 bed- market",null,null,null,null,{"$f":"E51","v":0},{"$f":"(E51*(D51+D52))*12","v":0},0,null,{"$f":"Q57","v":0},"% Units that meet  Test",null,null,null,null,0,0,0,0]
[null,"3 bed- affordable",null,null,null,null,{"$f":"E51*F52","v":0},null,0,null,{"$f":"IF(K51>=20%, \"Yes\", \"No\")","v":"No"},"20% Condition met?",null,null,null,null,0,0,0,0]
[null,"3 bed+den- market",null,null,null,null,{"$f":"E53","v":0},{"$f":"(E53*(D53+D54))*12","v":0},0,null,null,null,null,null,null,null,0,0,0,0]
[null,"3 bed+den- affordable",null,null,null,null,{"$f":"E53*F54","v":0},null,0,null,null,null,null,null,null,null,0,0,0,0]
[null,"4 bed- market",null,null,null,null,{"$f":"E55","v":0},{"$f":"(E55*(D55+D56))*12","v":0},0,null,null,null,null,null,null,null,0,0,0,0]
[null,"4 bed- affordable",null,null,null,null,{"$f":"E55*F56","v":0},null,0,null,null,null,null,null,null,null,0,0,0,0]
[null,"Other",null,null,null,null,{"$f":"E57","v":0},{"$f":"G57*D57*12","v":0},0,null,null,null,null,null,null,null,{"$f":"IF(D58=0, 0, SUM(Q41:Q56)/D58)","v":0},{"$f":"IF(D58=0, 0, SUM(R41:R56)/D58)","v":0},{"$f":"IF(D58=0, 0, SUM(S41:S56)/D58)","v":0},{"$f":"IF(D58=0, 0, SUM(T41:T56)/D58)","v":0}]
[null,"Total Residential",{"$f":"SUMPRODUCT(C41:C57,D41:D57)","v":0},{"$f":"SUM(D41:D57)","v":0},null,null,{"$f":"IF(H58=0, 0, I58/H58)","v":0},{"$f":"SUM(H41:H57)","v":0},{"$f":"SUM(I41:I57)","v":0},null,null,null,null,null,null,null,null,{"$f":"IF(R57=20%,\"Yes\",IF(R57>20%,\"Yes\",\"No\"))","v":"No"},{"$f":"IF(S57=20%,\"Yes\",IF(S57>20%,\"Yes\",\"No\"))","v":"No"},{"$f":"IF(T57=20%,\"Yes\",IF(T57>20%,\"Yes\",\"No\"))","v":"No"}]
[null,"% of Area",{"$f":"IF(C58+'Non-Residential'!C50=0,\"\",C58/(C58+'Non-Residential'!C50))","v":""},null,null,"90% test met?",{"$f":"IF(G58<=90%,\"Yes\", \"No\")","v":"Yes"}]
[null,null,"Affordable #",{"$f":"D42+D44+D46+D48+D50+D52+D54+D56","v":0}]
[null,null,"Market #",{"$f":"D58-D60","v":0}]
[]
[null,"Vacancy rate /bad debt (%)",null,null,null,"Vacancy rate / bad debt (cannot be zero)  "]
[null,"Less: Vacancy rate /bad debt",null,null,null,null,null,null,{"$f":"I58*I63","v":0}]
[null,"Effective Gross Income",null,null,null,null,null,null,{"$f":"I58-I64","v":0}]
[null,null,null,null,"MONTHLY",null,"ANNUAL"]
[null,"3. Ancillary Income",null,"# Units","Rent"]
[null,"Parking",null,null,null,null,{"$f":"E68*D68*12","v":0},null,{"$f":"G68","v":0}]
[null,"Storage lockers",null,null,null,null,{"$f":"E69*D69*12","v":0},null,{"$f":"G69","v":0}]
[null,"Laundry",null,null,null,null,null,null,0]
[null,"Total",null,null,null,null,{"$f":"SUM(G68:G70)","v":0},null,0]
[null,"Vacancy rate /bad debt (%)",null,null,null,null,{"$f":"I63","v":0}]
[null,"Less: Vacancy rate /bad debt",null,null,null,null,{"$f":"G72*G71","v":0},null,{"$f":"G73","v":0}]
[null,"Effective Gross Income",null,null,null,null,{"$f":"G71-G73","v":0},null,{"$f":"G74","v":0}]
[]
[]
[null,"4. Other income",null,null,null,null,"ANNUAL"]
[null,"Other Income (Describe)",null,null,null,null,null,null,{"$f":"G78","v":0}]
[null,"Vacancy rate /bad debt (%)"]
[null,"Less: Vacancy rate /bad debt",null,null,null,null,{"$f":"G79*G78","v":0},null,{"$f":"G80","v":0}]
[null,"Effective Gross Income",null,null,null,null,{"$f":"G78-G80","v":0},null,{"$f":"G81","v":0}]
[]
[null,"Total Effective Gross Income (EGI)",null,null,null,null,null,null,{"$f":"I81+I74+I65","v":0}]
[]
[]
[null,"5. Operating Costs",null,null,null,null,null,"Per Unit/Yr"]
[null,"Municipal Taxes",null,null,null,null,null,null,{"$f":"H87*$D$58","v":0}]
[null,"Insurance",null,null,null,null,null,null,{"$f":"H88*$D$58","v":0}]
[null,"Heat",null,null,null,null,null,null,{"$f":"H89*$D$58","v":0}]
[null,"Hydro",null,null,null,null,null,null,{"$f":"H90*$D$58","v":0}]
[null,"Water",null,null,null,null,null,null,{"$f":"H91*$D$58","v":0}]
[null,"Total Utilities",null,null,null,null,null,{"$f":"IF(D58=0, \"\", I92/D58)","v":""},{"$f":"SUM(I89:I91)","v":0}]
[null,"Repair and Maintenance",null,null,null,null,null,null,{"$f":"H93*$D$58","v":0}]
[null,"Wages- Super",null,null,null,null,null,null,{"$f":"H94*$D$58","v":0}]
[null,"Other (describe)",null,null,null,null,null,null,{"$f":"H95*$D$58","v":0}]
[null,"Other (describe)",null,null,null,null,null,null,{"$f":"H96*$D$58","v":0}]
[null,"Other (describe)",null,null,null,null,null,null,{"$f":"H97*$D$58","v":0}]
[null,"Management Fee (% of EGI)",null,null,null,null,null,null,{"$f":"H98*I83","v":0}]
[null,"General and Admin (% of EGI)",null,null,null,null,null,null,{"$f":"H99*I83","v":0}]
[null,"Total Operating Costs",null,null,null,null,null,null,{"$f":"SUM(I87:I91,I93:I99)","v":0}]
[]
[null,"Net Operating Income",null,null,null,null,null,null,{"$f":"I83-I100","v":0}]
[]
[]
[]
[null,"6. Loan Debt Coverage Ratio (DCR) and Loan to Cost (LTC) Calculation"]
[null,"Loan",null,null,null,null,null,null,{"$f":"G27-G34","v":0}]
[null,"Interest Rate (+ 100 bps)",null,null,"Contact CMHC for the Appropriate Indicative Interest Rate)  "]
[null,"Loan Amortization ( years)",null,null,"Maximum 50 Years Amortization  "]
[null,"Mortgage Payment (monthly) P+I",null,null,null,null,null,null,{"$f":"IF(I107=0, 0, -PMT((((I108/2)+1)^(1/6))-1, I109*12, I107, 0, 0))","v":0}]
[null,"Mortgage Payment (annually) P+I",null,null,null,null,null,null,{"$f":"I110*12","v":0}]
[null,"Debt Coverage Ratio (DCR)",null,null,"Minimum Eligible DCR of 1.1  ",null,null,null,{"$f":"IF(I111=0, 0, ROUND(I102/I111,2))","v":0}]
[null,"Test 3- Minimum Residential DCR of 1.1 achieved?",null,null,null,null,null,null,{"$f":"IF(I112=1.1,\"Yes\",IF(I112>1.1,\"Yes\",\"No\"))","v":"No"}]
[]
[null,"Loan",null,null,null,null,null,null,{"$f":"G27-G34","v":0}]
[null,"Project Cost",null,null,null,null,null,null,{"$f":"G27","v":0}]
[null,"Loan to Cost (LTC)",null,null,null,null,null,null,{"$f":"IF(I116=0, 0, I107/I116)","v":0}]
[null,"Max LTC- (up to 100% LTC based on Social Outcome Score)",null,null,null,null,null,null,{"$f":"IF('Eligibility and Social Outcome'!H88<'Eligibility and Social Outcome'!G96,90%,IF('Eligibility and Social Outcome'!H88>'Eligibility and Social Outcome'!H96,100%,95%))","v":0.9}]
[null,"Test 4- Residential Loan to Cost is Within Limits?",null,null,null,null,null,null,{"$f":"IF(I118=I117,\"Yes\", IF(I117<I118,\"Yes\",\"No\"))","v":"Yes"}]
[]
[null,"7. Required Equity Injection"]
[null,"Calculated Required Owners Equity to Meet Max LTC",null,null,null,null,null,null,{"$f":"IF(((1-I118)-(1-I117))<0,0,((1-I118)-(1-I117))*G27)","v":0}]
[null,"Calculated Required Owners Equity to Meet Min DCR",null,null,null,null,null,null,{"$f":"IF(I107>C146,I107-C146,0)","v":{"$error":"#DIV/0!"}}]
[null,"Calculated Required Owners Equity (Higher Amount)",null,null,null,null,null,null,{"$f":"IF(I122>I123,I122,I123)","v":{"$error":"#DIV/0!"}},null,"Enter this amount at cell I125"]
[null,"Actual Additional Owners Equity"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Calculated Required Additional Owners Equity to Meet Min DCR"]
[null,"Required Monthly Payment (P&I)",{"$f":"I102/(12*1.1)","v":0}]
[null,"Monthly Interest Rate (%)",{"$f":"(((I108/2)+1)^(1/6))-1","v":0}]
[null,"Number of Periods (Months)",{"$f":"I109*12","v":0}]
[null,"Required Loan Amount",{"$f":"C143*(((1+C144)^C145)-1)/(C144*((1+C144)^C145))","v":{"$error":"#DIV/0!"}}]

{"sheet":"Non-Residential","index":4,"n_rows":109,"n_cols":9,"rows_kept":109,"cut":false,"hidden":false,"merged":[[79,2,79,5],[84,4,84,5],[89,4,89,5],[104,1,104,2]]}
[null,"SUMMARY"]
[]
[null,"Project DCR",{"$f":"IF(OR(('Residential New'!I111+'Non-Residential'!G79)=0, ISERR(('Residential New'!I111+'Non-Residential'!G79))), 0, ('Residential New'!I102+'Non-Residential'!G71)/('Residential New'!I111+'Non-Residential'!G79))","v":0},null,"90% PGI",null,{"$f":"'Residential New'!G59","v":"Yes"}]
[null,"Project LTC",{"$f":"IF('Project Budget'!E25=0, 0, ('Residential New'!I107+'Non-Residential'!G75)/'Project Budget'!E25)","v":0},null,"20% of Units at Median HHI",null,{"$f":"'Residential New'!K52","v":"No"}]
[null,"Total Loan",{"$f":"'Residential New'!I107+'Non-Residential'!G75","v":0},null,"90% of HHI",null,{"$f":"'Residential New'!R58","v":"No"}]
[null,null,null,null,"80% of HHI",null,{"$f":"'Residential New'!S58","v":"No"}]
[null,"Eligibility Criteria",{"$f":"'Eligibility and Social Outcome'!G32","v":"No"},null,"70% of HHI",null,{"$f":"'Residential New'!T58","v":"No"}]
[]
[null,"Social Outcome Score",{"$f":"'Eligibility and Social Outcome'!H90","v":"Up to 90% Res LTC"}]
[]
[]
[]
[null,"Non-Residential : Financial Viability Assessment"]
[null,null,null,null,"Test 1"]
[null,"Percentage of Project Costs for Non-Residential ",null,null,{"$f":"IF(D15<=30%,\"Yes\",\"No\")","v":"Yes"},"  (Non-Residential portion of project costs cannot exceed 30% of project costs)"]
[]
[null,null,null,null,"Test 2"]
[null,null,null,null,{"$f":"IF(D15+'Residential New'!D15=100%,\"Yes\",\"No\")","v":"No"},"  (Residential and Non-Residential equals 100% of project costs)"]
[]
[null,null,null,null,"Test 3"]
[null,"Percentage of Project Floor Space for Non-Residential ",null,{"$f":"C50/(C50+'Residential New'!C58)","v":{"$error":"#DIV/0!"}},{"$f":"IF(D21<=30%,\"Yes\",\"No\")","v":{"$error":"#DIV/0!"}},"  (Non-Residential portion of floor space cannot exceed 30% of project costs)"]
[]
[null,"1. Project budget","Non-Residential"]
[null,"Land Input Value",{"$f":"D$15*'Project Budget'!E17","v":0}]
[null,"Hard costs",{"$f":"D$15*'Project Budget'!E18","v":0}]
[null,"Soft costs",{"$f":"D$15*'Project Budget'!E19","v":0}]
[null,"Financing costs",{"$f":"D$15*'Project Budget'!E20","v":0}]
[null,"HST ",{"$f":"D$15*'Project Budget'!E21","v":0}]
[null,{"$f":"'Project Budget'!B22","v":"Misc and Buffer"},{"$f":"D$15*'Project Budget'!E22","v":0}]
[null,{"$f":"'Project Budget'!B23","v":"Other (describe)"},{"$f":"D$15*'Project Budget'!E23","v":0}]
[null,{"$f":"'Project Budget'!B24","v":"Other (describe)"},{"$f":"D$15*'Project Budget'!E24","v":0}]
[null,"Total Budget (Uses)",{"$f":"SUM(C24:C31)","v":0}]
[]
[null,"Other Sources of Funding"]
[null,"Owner Cash Equity",{"$f":"G92","v":0}]
[null,"Land Value Equity",{"$f":"'Project Budget'!E29*'Non-Residential'!D15","v":0}]
[null,{"$f":"'Project Budget'!B30","v":"Other Financing (describe )"},{"$f":"'Project Budget'!E30*'Non-Residential'!D15","v":0}]
[null,{"$f":"'Project Budget'!B31","v":"Other Financing(describe )"},{"$f":"'Project Budget'!E31*'Non-Residential'!D15","v":0}]
[null,"Total Other Sources",{"$f":"SUM(C35:C38)","v":0}]
[]
[]
[]
[null,null,null,null,"MONTHLY",null,"ANNUAL"]
[null,"2. Non-Residential/Commercial","Size (sf)","# Units","Rent"]
[null,"Retail/Office 1",null,null,null,null,{"$f":"D45*E45*12","v":0}]
[null,"Retail/Office 2",null,null,null,null,{"$f":"D46*E46*12","v":0}]
[null,"Retail/Office 3",null,null,null,null,0]
[null,"Retail/Office 4",null,null,null,null,0]
[null,"Add: Recoveries"]
[null,"Total",{"$f":"SUMPRODUCT(C45:C48,D45:D48)","v":0},{"$f":"SUM(D45:D48)","v":0},null,null,{"$f":"SUM(G45:G49)","v":0}]
[null,"Vacancy rate /bad debt"]
[null,"Less: Vacancy /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,"3. Annual Operating Costs",null,null,null,null,"ANNUAL"]
[null,"Municipal Taxes"]
[null,"Insurance"]
[null,"Heat"]
[null,"Hydro"]
[null,"Water"]
[null,"Total Utilities",null,null,null,null,{"$f":"SUM(G58:G60)","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":"F67*$G$53","v":0}]
[null,"General and Admin (%)",null,null,null,null,{"$f":"F68*$G$53","v":0}]
[null,"Total Operating Costs",null,null,null,null,{"$f":"SUM(G56:G60,G62:G68)","v":0}]
[]
[null,"Net Operating Income",null,null,null,null,{"$f":"G53-G69","v":0}]
[]
[]
[null,"4. Loan Debt Coverage Ratio (DCR) and Loan to Cost (LTC) Calculation"]
[null,"Loan",null,null,null,null,{"$f":"C32-C39","v":0}]
[null,"Interest Rate",null,null,null,null,{"$f":"'Residential New'!I108","v":0}]
[null,"Loan Amortization ( years)",null,null,null,null,{"$f":"'Residential New'!I109","v":0}]
[null,"Mortgage Payment (monthly) P+I",null,null,null,null,{"$f":"IF(G75=0, 0, -PMT((((G76/2)+1)^(1/6))-1, G77*12, G75, 0, 0))","v":0}]
[null,"Mortgage Payment (annually) P+I",null,null,null,null,{"$f":"G78*12","v":0}]
[null,"Debt Coverage Ratio (DCR)","Minimum Eligible DCR of 1.4  ",null,null,null,{"$f":"IF(G79=0, 0, G71/G79)","v":0}]
[null,"Test 4 - Minimum Non-Residential DCR of 1.4 achieved?",null,null,null,null,{"$f":"IF(G80>=1.4,\"Yes\",\"No\")","v":"No"}]
[]
[null,"Loan",null,null,null,null,{"$f":"C32-C39","v":0}]
[null,"Project Cost",null,null,null,null,{"$f":"C32","v":0}]
[null,"Non-Residential Loan-to-Cost (LTC)",null,null,"Max 75% Non-Res LTC  ",null,{"$f":"IF(G84=0, \"\", G83/G84)","v":""}]
[null,"Test 5 - Non-Residential Loan to Cost is Within Limits (75%)",null,null,null,null,{"$f":"IF(G85<=0.75,\"Yes\",\"No\")","v":"No"}]
[]
[null,"5. Required Equity Injection"]
[null,"Calculated Required Additional Owners Equity to Meet Max LTC",null,null,null,null,{"$f":"IF((0.25-(1-G85))<0,0,(0.25-(1-G85))*C32)","v":{"$error":"#VALUE!"}}]
[null,"Calculated Required Additional Owners Equity to Meet Min DCR",null,null,null,null,{"$f":"IF(G75>C109,G75-C109,0)","v":{"$error":"#DIV/0!"}}]
[null,"Actual Required Additional Owners Equity (Higher Amount)",null,null,null,null,{"$f":"IF(G89>G90,G89,G90)","v":{"$error":"#VALUE!"}},null,"Enter this amount at cell G92"]
[null,"Actual Required Additional Owners Equity"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"Calculated Required Additional Owners Equity to Meet Min DCR"]
[null,"Required Monthly Payment (P&I)",{"$f":"G71/(12*1.4)","v":0}]
[null,"Monthly Interest Rate (%)",{"$f":"(((G76/2)+1)^(1/6))-1","v":0}]
[null,"Number of Periods (Months)",{"$f":"G77*12","v":0}]
[null,"Required Loan Amount",{"$f":"C106*(((1+C107)^C108)-1)/(C107*((1+C107)^C108))","v":{"$error":"#DIV/0!"}}]

{"sheet":"Eligibility and Social Outcome","index":5,"n_rows":108,"n_cols":11,"rows_kept":108,"cut":false,"hidden":false,"merged":[[15,3,15,4],[18,3,18,4],[20,3,20,4],[22,3,22,4],[37,5,39,5],[25,3,25,4],[26,3,26,4],[29,3,29,4],[69,6,74,6],[69,7,74,7],[37,6,39,6],[37,7,39,7],[42,5,44,5],[42,6,44,6],[42,7,44,7],[46,6,49,6],[46,7,49,7],[58,5,62,5],[58,6,62,6],[58,7,62,7],[93,4,93,5],[94,4,94,5],[95,4,95,5],[96,4,96,5],[46,5,49,5],[69,4,74,4],[69,5,74,5]]}
[null,"SUMMARY"]
[]
[null,"Project DCR",null,{"$f":"IF(OR(('Residential New'!I111+'Non-Residential'!G79)=0, ISERR(('Residential New'!I111+'Non-Residential'!G79))), 0, ('Residential New'!I102+'Non-Residential'!G71)/('Residential New'!I111+'Non-Residential'!G79))","v":0},null,"90% PGI",null,{"$f":"'Residential New'!G3","v":"Yes"}]
[null,"Project LTC",null,{"$f":"IF('Project Budget'!E25=0, 0, ('Residential New'!I107+'Non-Residential'!G75)/'Project Budget'!E25)","v":0},null,"20% of Units at Median HHI",null,{"$f":"'Residential New'!K52","v":"No"}]
[null,"Total Loan",null,{"$f":"'Residential New'!I107+'Non-Residential'!G75","v":0},null,"90% of HHI",null,{"$f":"'Residential New'!R58","v":"No"}]
[null,null,null,null,null,"80% of HHI",null,{"$f":"'Residential New'!S58","v":"No"}]
[null,"Eligibility Criteria",null,{"$f":"'Eligibility and Social Outcome'!G32","v":"No"},null,"70% of HHI",null,{"$f":"'Residential New'!T58","v":"No"}]
[]
[null,"Social Outcome Score",null,{"$f":"'Eligibility and Social Outcome'!H90","v":"Up to 90% Res LTC"}]
[]
[]
[]
[null,"Eligibility Requirements"]
[]
[null,"Viability",null,null,null,null,null,"Check"]
[null,"Viability Assessment Results",null,"DCR calculated from your completed Viability Assessment (Project must meet a minimum DCR of 1.1 times)",null,null,{"$f":"D3","v":0},{"$f":"IF(G16=1.1,\"Yes\",IF(G16>1.1,\"Yes\",\"No\"))","v":"No"},null,null,{"$f":"IF(H16=\"Yes\",0,1)","v":1}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF(G18=\"Yes\",0,1)","v":1}]
[null,"Affordability"]
[null,"Other Government Programs Supporting Affordable Rental Housing",null,"Has your project been approved under other government programs / initiatives (federal, provincial, territoral, or municipal) that provides support for development of affordable rental housing and will the affordable rent levels be maintain for a minimum of 10 years?  (If \"yes\", you do not need to answer the eligibility criteria questions on Duration and Depth below).",null,null,null,null,null,null,{"$f":"IF(G19=\"Yes\",0,1)","v":1}]
[]
[null,"Duration",null,"Affordability will be maintained for a minimum of 10 years ",null,null,null,null,null,null,{"$f":"IF(G21=\"Yes\",0,1)","v":1}]
[]
[null,"Depth",null,"Will your project's gross achievable residential rental income be 90%, or less, of Potential Gross Income, and 20% of units have rents that represent no more that 30% of household median income for your Municipality?",null,null,null,null,null,null,{"$f":"IF(G23=\"Yes\",0,1)","v":1}]
[]
[null,"Accessibility "]
[null,"Meet/exceed local requirements",null,"Will 10% of units in your project meet or exceed the local accessibility requirements as prescribed by your Municipality or Province/Territory, or in absence of the aforementioned, the accessibility requirements of the 2015 National Building Code?",null,null,null,null,null,null,{"$f":"IF(G26=\"Yes\",0,1)","v":1}]
[]
[null,"Barrier Free",null," Will access to your project and to its common areas be barrier free?",null,null,null,null,"`",null,{"$f":"IF(G28=\"Yes\",0,1)","v":1}]
[null,null,null,null,null,null,null,null,null,null,{"$f":"IF(G30=\"Yes\",0,1)","v":1}]
[null,"Energy Efficiency",null,"Will your project achieve a minimum 15% decrease in energy use and green house gas emissions under the applicable reference  model building code?\nLow-rise multi-unit buildings under Part 9 of the National Building code must demonstrate a minimum 15% improvement over the 2015 NBC. All buildings under Part 3 of the National Building Code must demonstrate a minimum 15% improvement over 2017 NECB. ",null,null,null,null,null,null,{"$f":"K16+K19+K26+K28+K29","v":5}]
[]
[null,"Have you met the eligibility requirements?",null,"The result must be  \"Yes\" for your project to be considered by the Rental Construction Financing initiative. If your project does meet the qualification criterie, pleae proceed to the Prioritization Scoring section below)",null,null,{"$f":"IF(K32>0,\"No\",\"Yes\")","v":"No"},null,null,null,{"$f":"IF(K19=0, K30, (K16+K21+K23+K26+K28+K29))","v":6}]
[]
[null,"Prioritization Scoring"]
[null,null,null,null,"Points",null,"Select Score","Total Points"]
[null,"Affordability"]
[null,"Affordability - duration",null,"10 Years (Minimum Eligibility Requirement)",0]
[null," ",null,"11 years and up to 15 years",1,null,null,{"$f":"G38","v":0}]
[null," ",null,"16 years and up to 20 years",2]
[null,null,null,"21 years or more",3]
[]
[null,"Affordability – depth",null,"Rents at 100% of 30% of median income (Minimum Eligibility Requirement)",0]
[null,null,null,"Rents at 90% of 30% of median income",1,null,null,{"$f":"G43","v":0}]
[null,null,null,"Rents at 80% of 30% of median income",2]
[null,null,null,"Rents at 70%  of 30% of median income or below",3]
[]
[null,"Affordability – number of units",null,"20% of total units (Minimum Eligibility Requirement)",0,null,null,{"$f":"G47","v":0}]
[null,null,null,"Up to 5 more units over the eligibility requirement",1]
[null,null,null,"From 6 more units to 10 more unit over the eligibility requirement",2]
[null,null,null,"More than 10 units over the eligibility requirement",3]
[]
[null,"Accessibility "]
[null,"Adaptable units",null,"Project contains a minimum of 2 adaptable units, in addition to minimum 10% of unit accessible requirement.",2,"0= No\n2= Yes",null,{"$f":"G53","v":0}]
[]
[null,"Universal design units",null,"Project contains a minimum of 2 units with universal design, in addition to minimum 10% of unit accessible requirement.",2,"0= No\n2= Yes",null,{"$f":"G55","v":0}]
[]
[null,"Energy Efficiency - Greenhouse Gas"]
[]
[null,"Level of reduction",null,"15% more efficient than the applicable  reference model building building code. (Minimum Eligibility Requirement)",0,null,null,{"$f":"G59","v":0}]
[null,null,null,"More than 15% and up to 25% more efficient than the applicable reference model building building code.",1]
[null,null,null,"More than 25% and up to 50% more efficient than the applicable reference model building building code.",2]
[null,null,null,"More than 50% more efficient than the applicable reference model building building code.",3]
[null,null,null,"Net zero energy ready or equivalent standards of performance",5]
[]
[]
[null,"Fostered Collaboration"]
[null,"Partnerships",null,"Non-Profits: are other Non-Profits or For-profit Developers, Urban Aboriginal Groups, or Municipalities taking a long term stake in the project?\nFor-Profit Developers: are Non-Profits, Urban Aboriginal Groups, or Municipalities taking a long term stake in the project?\nMunicipalities: are Non-Profits, For-Profit Developers or Urban Aboriginal Groups taking a long term stake in the project?","0 = none\n1 = 1 partner or more",null,0,{"$f":"G67","v":0}]
[]
[null," "]
[null,"Other Government Supports:",null,"• Grants","0 = no support\n1  = 1 or 2 supports\n2 = 3 or more supports\n",null,0,{"$f":"G70","v":0}]
[null,"(Federal / Provincial / Territorial / Municipal)",null,"• Concessions on property taxes"]
[null,null,null,"• Concessions on levies"]
[null,null,null,"• Waiver of development cost charges or other provincial/municipal fees"]
[null,null,null,"• Expedited Approvals "]
[null,null,null,"• Waiver of community amenity contributions"]
[null,null,null,"• Other"]
[]
[]
[null,"Land donation",null,"Was the land donated by a non-related party?",2,"0= No\n2= Yes",null,{"$f":"G79","v":0}]
[]
[]
[null,"Transit Oriented"]
[null,"Proximity to public transit",null,"Within 1 km of public transit such as bus stop, train station, rapid transit or subway station",1,"0= No\n1= Yes",null,{"$f":"E83*G83","v":0}]
[]
[null,"Altenative forms of public transit",null,"Project offers access to alternative forms of public transit (parking spots for car sharing service, shuttle bus service, direct connection to underground path system, etc.)",1,"0= No\n1= Yes",null,{"$f":"E85*G85","v":0}]
[]
[]
[null,"Your Score",null,null,null,null,null,{"$f":"H38+H43+H47+H53+H55+H59+H67+H70+H79+H83+H85","v":0}]
[]
[null,"Eligible Loan To Cost Percentage ",null,null,null,null,null,{"$f":"IF(H88<G96,E95,IF(H88<G97,E96,E97))","v":"Up to 90% Res LTC"}]
[]
[]
[]
[null,null,null,"Tier","Eligible Res Loan-to-Cost (LTC)",null,"Min","Max"]
[null,null,null,3,"Up to 90% Res LTC",null,0,9]
[null,null,null,2,"Up to 95% Res LTC",null,10,18]
[null,null,null,1,"Up to 100% Res LTC",null,19,25]
[]
[]
[null,null,null,null,"No",0,0,0]
[null,null,null,null,"Yes",1,2,1]
[null,null,null,null,null,2,null,2]
[null,null,null,null,null,null,null,3]
[null,null,null,null,null,null,null,5]
[]
[null,null,null,null,null,"Min","Max"]
[null,null,null,null,0.9,0,9]
[null,null,null,null,0.95,10,18]

{"sheet":"Mortg Calc","index":6,"n_rows":11,"n_cols":9,"rows_kept":11,"cut":false,"hidden":true,"merged":[]}
["Mortgage  Calculator"]
[" "," "," "]
[null,null,"Principal",null,20000000,null,"Total of All Payments:",{"$f":"E6*E7*E9","v":3948787.8802890573},"over term"]
[null,null,"Interest",null,2.5000000000000001E-2]
[null,null,"Comp Frequency",null,2,"(2=Semi Annul, 4=Quarterly, 12=Monthly)"]
[null,null,"Payment Freq",null,12,"(Number of Payments a year)"]
[null,null,"Term",null,5,"(years)"]
[null,null,"Amortization",null,40,"(years)"]
[null,null,"Calculated Pmt",null,{"$f":"(E3*(((1+E4/E5)^(E5/E6))-1)/((1-(1+E4/E5)^(-E8*E5))))","v":65813.131338150954},"Overide:",0]
[null,null,"Interest Factor",null,{"$f":"((1+E4/E5)^(E5/E6))-1","v":2.0725647992037022E-3},null,"(Use to override the Calulated Payment)"]
[null,null,"Pmts Per Year",null,{"$f":"E9*12","v":789757.5760578115}]