| A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z | AA | AB | AC | AD | |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | ||||||||||||||||||||||||||||||
2 | CASH COSTS | RATE | TOTAL | Okotoks | With energy efficiency, we still need 16 of the units as affordable for full points. | |||||||||||||||||||||||||
3 | Down Payment | 5% | $875,000 | Projected Rent exc. Utilities | Rent | # Of Units | Total | |||||||||||||||||||||||
4 | Mortgage Broker Fee + Lender Fee | 1.25% | $218,750 | 16 one-bed (7 on L1 + 9 on L2-4) | $0 | |||||||||||||||||||||||||
5 | CMHC Screening Fee | $1,500 | 44 two-bed (8 on L1 + 36 on L2-4) | Two Bed | $1,900 | 44 | $83,600 | |||||||||||||||||||||||
6 | Legal Costs | 300K/door | One Bed | $1,600 | 16 | $25,600 | ||||||||||||||||||||||||
7 | Appraisal & Environmental Fee | $5,500 | 54 parking stalls incl. 4 accessible (drawings; was noted 59) | Parking | $100 | 40 | $4,000 | |||||||||||||||||||||||
8 | $0 | |||||||||||||||||||||||||||||
9 | Closing Costs | $225,750 | Total Rent | $113,200 | ||||||||||||||||||||||||||
10 | Total Cash Needed | $1,100,750 | Total Investment | |||||||||||||||||||||||||||
11 | Investor Requirements To Qualify | |||||||||||||||||||||||||||||
12 | $17,500,000 | Networth (Assets less Liabilities) | 25% | $4,375,000 | ||||||||||||||||||||||||||
13 | Total Investment After Refund | $1,100,750 | ||||||||||||||||||||||||||||
14 | DEBT SERVICE | Monthly Cashflow | "CASH" - ROI Yr 3 | THREE YEAR ROI | Liquidity (Cash, Savings, Lines of Credit, Stocks etc) | 10% | $1,750,000 | |||||||||||||||||||||||
15 | Loan to Value Ratio | 95% | $10,018 | 15.59% | 243.37% | |||||||||||||||||||||||||
16 | Mortgage | $16,625,000 | Sensitivity Analysis | Mort Rate | Monthly Payment | Yearly Mortgage | DSCR | Monthly Cashflow | ||||||||||||||||||||||
17 | CMHC Fee | 5.15% | $856,187.50 | DSCR | Networth Req'd | Liquidity Req'd | Worst | 4.60% | $74,515 | $894,179 | 1.05 | $4,091.16 | ||||||||||||||||||
18 | Total Mortgage | $17,481,188 | 1.15 | $4,375,000 | $1,750,000 | Worse | 4.35% | $71,527 | $858,329 | 1.10 | $7,078.66 | |||||||||||||||||||
19 | Interest Rate (annual) | 4.10% | BASE RATE | 4.10% | $68,588 | $823,056 | 1.15 | $10,018.12 | ||||||||||||||||||||||
20 | Interest Rate (monthly) | 0.3417% | Better | 3.85% | $65,699 | $788,387 | 1.20 | $12,907.14 | ||||||||||||||||||||||
21 | Amortization period (in years) | 50 | Best | 3.60% | $62,863 | $754,353 | 1.25 | $15,743.34 | ||||||||||||||||||||||
22 | Monthly Mortgage Payment | $68,588 | ||||||||||||||||||||||||||||
23 | OPERATING EXPENSES (Monthly) | |||||||||||||||||||||||||||||
24 | Property Management | 4% | $4,528.00 | Sensitivity Analysis | PRICE PER UNIT | INVESTMENT | Monthly Mortgage | Yearly Mortgage | DSCR | |||||||||||||||||||||
25 | Property Tax (estimated annual) | 10.0% | $10,867 | Purchase Price | 330000 | $19,800,000 | $77,602 | $931,229 | 1.01 | |||||||||||||||||||||
26 | Insurance | 1.7% | $1,847 | LONG TERM ECONOMICS (estimated) | 310000 | $18,600,000 | $72,899 | $874,790 | 1.08 | |||||||||||||||||||||
27 | Utilities | 0.0% | Initial Value | $17,500,000 | BASE | 291666.6667 | $17,500,000 | $68,588 | $823,056 | 1.15 | ||||||||||||||||||||
28 | Repairs & Maintenance | 2.0% | $2,173.44 | Market Growth Rate | 3.50% | 275000 | $16,500,000 | $64,669 | $776,024 | 1.22 | ||||||||||||||||||||
29 | Management | 4.0% | $4,346.88 | Rental Growth Rate | 2.50% | 260000 | $15,600,000 | $61,141 | $733,695 | 1.29 | ||||||||||||||||||||
30 | Salaries | 2.0% | $2,173 | Expense Growth Rate | 2.00% | |||||||||||||||||||||||||
31 | Replacement Reserve | 1.8% | $1,956 | Year | Asset Value | Loan Balance | Cash Flow Generated Yearly | Total Equity In Property | Total Return On Cash Invested | |||||||||||||||||||||
32 | Other | 2.0% | $2,173 | 1 | $18,112,500 | $ 17,372,839.67 | $ 120,217.43 | $983,348 | 76.4% | |||||||||||||||||||||
33 | % of EGI | 28% | 2 | $18,746,438 | $ 17,259,965.15 | $ 145,603.21 | $1,096,222 | 157.5% | ||||||||||||||||||||||
34 | Total Expenses | $30,065.92 | 3 | $19,402,563 | $ 17,142,374.80 | $ 171,659.71 | $1,213,813 | 243.4% | ||||||||||||||||||||||
35 | Rental Income (Monthly) | 4 | $20,081,653 | $ 17,019,871.62 | $198,404.42 | $1,336,316 | 334.2% | |||||||||||||||||||||||
36 | Monthly Rent | $113,200.00 | 5 | $20,784,510 | $ 16,892,250.35 | $225,855.30 | $1,463,937 | 430.2% | ||||||||||||||||||||||
37 | Vacancy | 4% | $4,528.00 | |||||||||||||||||||||||||||
38 | EGI | $108,672.00 | ||||||||||||||||||||||||||||
39 | Net Income (Monthly) | |||||||||||||||||||||||||||||
40 | Estimated Cap Rate (Annual) | 5.39% | ||||||||||||||||||||||||||||
41 | Estimated Cash Flow | $10,018 | ||||||||||||||||||||||||||||
42 | ||||||||||||||||||||||||||||||
43 | DISCLAIMER: EVERY INVESTMENT INVOLVES RISK, AND ANY PURCHASER OF REAL ESTATE IS RESPONSIBLE FOR THEIR OWN DUE DILIGENCE. NOTHING IS GUARANTEED BY NEW HOMES ALBERTA. ALL FIGURES LISTED ON THIS PAGE | Total Returns Calculator | ||||||||||||||||||||||||||||
44 | ARE FOR THE PURPOSES OF ACADEMIC EVALUATION ONLY, AND ANY INTERESTED INVESTOR IS RESPONSIBLE FOR THEIR OWN MODEL ASSUMPTIONS, AND SHOULD DO THEIR OWN RESEARCH AND EVALUATION BEFORE MAKING AN INVESTMENT | Net Op Cash (Monthly) | Net Op Cash (Yearly) | Cash ROI On Money Out of Pocket | Net Op Cash From Renting | House Appreciation Value | Growth In Equity (YoY) | |||||||||||||||||||||||
45 | Year 1 | © New Homes Alberta & Michael Hale. All rights reserved. | $10,018.12 | $120,217.43 | 10.92% | Year 1 | $ 120,217.43 | $ 612,500.00 | $ 108,347.83 | Total Yearly Return | Total Return Rate on Initial Cash Invested | |||||||||||||||||||
46 | Year 2 | This document is protected by copyright law. Any reproduction, distribution, or unauthorized use of this content without the express written consent of New Homes Alberta and Michael Hale is strictly prohibited and may result in legal action. | $12,133.60 | $145,603.21 | 13.23% | Year 2 | $ 145,603.21 | $ 633,937.50 | $ 112,874.52 | $ 841,065.26 | 76.41% | |||||||||||||||||||
47 | Year 3 | $14,304.98 | $171,659.71 | 15.59% | Year 3 | $ 171,659.71 | $ 656,125.31 | $ 117,590.35 | $ 892,415.23 | 81.07% | ||||||||||||||||||||
48 | Year 4 | Cash Flow Calc | $16,533.70 | $198,404.42 | 18.02% | Year 4 | $ 198,404.42 | $ 679,089.70 | $ 122,503.18 | $ 945,375.37 | 85.88% | |||||||||||||||||||
49 | Year 5 | Projected Monthly Rental Growth | Expense Growth | Mortgage Payment | $18,821.27 | $225,855.30 | 20.52% | Year 5 | $ 225,855.30 | $ 702,857.84 | $ 127,621.27 | $ 999,997.30 | 90.85% | |||||||||||||||||
50 | $113,200.00 | $30,065.92 | $68,588 | $ 1,056,334.40 | 95.96% | |||||||||||||||||||||||||
51 | $116,030.00 | $30,667.24 | $68,588 | |||||||||||||||||||||||||||
52 | $118,930.75 | $31,280.58 | $68,588 | |||||||||||||||||||||||||||
53 | Year | $121,904.02 | $31,906.19 | $68,588 | Rental Profits (Pre-Tax) (Cash) | Profit After Mortgage Payback (Cash) | Total Cash In Pocket | Return on Initial Cash Invested | Annualized ROI | Estimated Taxes on Rental Profits (30%) | ||||||||||||||||||||
54 | 1 | $124,951.62 | $32,544.32 | $68,588 | $120,217.43 | $739,660 | $859,878 | 78.1% | 78.1% | $36,065.23 | Capital Gain | Taxable Capital Gain | Estimated Capital Gains Tax | Total Taxes | Net Profit (Cash Left In Your Pocket) | "Pure Profit" After Removing Initial Investment | Return Rate | |||||||||||||
55 | 2 | $145,603.21 | $1,486,472 | $1,632,076 | 148.3% | 57.6% | $43,680.96 | $612,500 | $306,250 | $91,875 | $127,940 | $731,938 | -$368,812 | -33.5% | ||||||||||||||||
56 | 3 | $171,659.71 | $2,260,188 | $2,431,848 | 220.9% | 47.5% | $51,497.91 | $1,246,438 | $623,219 | $186,966 | $230,647 | $1,401,429 | $300,679 | 27.3% | ||||||||||||||||
57 | 4 | Annualized Rate of Return | (Assuming Sales At Different End Of Year Periods) | $198,404.42 | $3,061,781 | $3,260,185 | 296.2% | 41.1% | $59,521.33 | $1,902,563 | $951,281 | $285,384 | $336,882 | $2,094,965 | $994,215 | 90.3% | ||||||||||||||
58 | 5 | Initial Cash Investment | Cash Collected From Sale | Profit From Sale of House | $225,855.30 | $3,892,260 | $4,118,115 | 374.1% | 36.5% | $67,756.59 | $2,581,653 | $1,290,826 | $387,248 | $446,769 | $2,813,416 | $1,712,666 | 155.6% | |||||||||||||
59 | $1,100,750 | $18,112,500 | $612,500 | $3,284,510 | $1,642,255 | $492,677 | $560,433 | $3,557,682 | $2,456,932 | 223.2% | ||||||||||||||||||||
60 | $1,100,750 | $18,746,438 | $1,246,438 | |||||||||||||||||||||||||||
61 | $1,100,750 | $19,402,563 | $1,902,563 | MAX PRICE SOLVER (at current NOI) | ||||||||||||||||||||||||||
62 | $1,100,750 | $20,081,653 | $2,581,653 | Target DSCR | Max Annual Debt Svc | Max Monthly Pmt | Max Total Mortgage | Max Purchase Price | Price / Door | |||||||||||||||||||||
63 | $1,100,750 | $20,784,510 | $3,284,510 | 1.1 | 857520.8727 | 71460.07273 | $18,213,209.87 | $18,232,810.14 | $303,880.17 | |||||||||||||||||||||
64 | 1.15 | 820237.3565 | 68353.11304 | $17,421,331.18 | $17,440,079.26 | $290,667.99 | ||||||||||||||||||||||||
65 | ||||||||||||||||||||||||||||||
66 | FIXED - these were errors, not assumptions | |||||||||||||||||||||||||||||
67 | Unit mix (U5/U6) | Was 45 two-bed / 15 one-bed. DP Rev 5 unit table reads 44 two-bed / 16 one-bed. The '15 + 45' line on that table is the PER-LEVEL split (L1 vs L2-4), not the bed split. | ||||||||||||||||||||||||||||
68 | Amortization tab (C3) | Ran on F16 = loan before the CMHC premium, while DSCR ran on F18 = loan including it. Now both use F18. Loan basis up ~$856K, so equity build and multi-year ROI come down. | ||||||||||||||||||||||||||||
69 | Cap rate (F40) | Used F36 (gross rent), ignoring vacancy. Now uses F38 (EGI). | ||||||||||||||||||||||||||||
70 | Years 2-5 cash flow (H46:H49) | Vacancy applied in Year 1 but dropped out for Years 2-5, so Yr2 jumped ~66% over Yr1. Now applies E37 throughout. | ||||||||||||||||||||||||||||
71 | Pure Profit (V55:V59) | Each row referenced the FOLLOWING year's net profit against this year's investment. Last row pointed at empty cells and returned -100%. | ||||||||||||||||||||||||||||
72 | Rate sensitivity (T17:T21) | Monthly payments were hardcoded from a different deal, producing DSCRs near 0.5 against a headline of 1.15. Now computed via PMT off F18. | ||||||||||||||||||||||||||||
73 | Price sensitivity (S25:S29) | Was hardcoded to 116 units. Now multiplies price/unit by actual unit count (U5+U6). | ||||||||||||||||||||||||||||
74 | NOT CHANGED - your assumptions, left as set | |||||||||||||||||||||||||||||
75 | Utilities (F27) | Still $0. Flagging only: this is a 4-storey with lobby, corridors, two elevators and lot lighting, and the Town of Okotoks requires water be billed to the property owner. If you add a water cost, add the matching recovery as revenue. | ||||||||||||||||||||||||||||
76 | Legal (F6) | Still blank. Total Cash Needed excludes it until a number goes in. | ||||||||||||||||||||||||||||
77 | Appraisal & environmental (F7) | Left at $5,500. | ||||||||||||||||||||||||||||
78 | Parking (U7) | Left at 40 stalls charged. Note text corrected to 54 built. | ||||||||||||||||||||||||||||
79 | Rents / price / vacancy / opex % | All left exactly as you set them. | ||||||||||||||||||||||||||||
80 | GST | ~$875K at closing, not modelled. Purpose-Built Rental rebate may offset. | ||||||||||||||||||||||||||||
81 | ||||||||||||||||||||||||||||||
82 | ||||||||||||||||||||||||||||||
83 | ||||||||||||||||||||||||||||||
84 | ||||||||||||||||||||||||||||||
85 | ||||||||||||||||||||||||||||||
86 | ||||||||||||||||||||||||||||||
87 | ||||||||||||||||||||||||||||||
88 | ||||||||||||||||||||||||||||||
89 | ||||||||||||||||||||||||||||||
90 | ||||||||||||||||||||||||||||||
91 | ||||||||||||||||||||||||||||||
92 | ||||||||||||||||||||||||||||||
93 | ||||||||||||||||||||||||||||||
94 | ||||||||||||||||||||||||||||||
95 | ||||||||||||||||||||||||||||||
96 | ||||||||||||||||||||||||||||||
97 | ||||||||||||||||||||||||||||||
98 | ||||||||||||||||||||||||||||||
99 | ||||||||||||||||||||||||||||||
100 | ||||||||||||||||||||||||||||||