Filetype Excel XLSX | Posted on 16 Aug 2022 | 3 years ago
Authentication
digital resources
156x Filetype XLSX File size 0.06 MB Source: stephenlnelson.com
Sheet 1: Analyzer
| The Multi-Family Real Estate Investment Planner | |||||||||||||||
| Global Inputs | |||||||||||||||
| Inflation rate | 3.00% | ||||||||||||||
| Vacancy rate | 5.00% | ||||||||||||||
| Purchase Inputs | |||||||||||||||
| Purchase price | $625,000.00 | ||||||||||||||
| Down payment | $125,000.00 | ||||||||||||||
| Depreciable basis | $500,000.00 | ||||||||||||||
| Closing costs | $5,000.00 | ||||||||||||||
| Loan costs and cash flows | |||||||||||||||
| Loan fee | $10,000.00 | ||||||||||||||
| Loan amount | $500,000.00 | ||||||||||||||
| Loan interest rate | 5.250% | ||||||||||||||
| Loan term | 240 | ||||||||||||||
| Taxation Inputs | |||||||||||||||
| Marginal tax rate | 25.00% | ||||||||||||||
| Cap gain rate | 15.00% | ||||||||||||||
| Sales Exit Inputs | |||||||||||||||
| Exit cap rate | 8.00% | ||||||||||||||
| Exit selling costs | 5.00% | ||||||||||||||
| NPV Calculation Inputs | |||||||||||||||
| Pretax discount rate | 12.00% | ||||||||||||||
| Aftertax discount rate | 9.00% | ||||||||||||||
| Monthly loan payment | $(3,369.22) | ||||||||||||||
| Part I - Forecast of Operating Income, Operating Expenses, and Operating Cash Flows | |||||||||||||||
| Operating Income | |||||||||||||||
| Rental Income | Monthly | Units | Year 1 | Year 2 | Year 3 | Year 4 | Year 5 | Year 6 | Year 7 | Year 8 | Year 9 | Year 10 | Year 11 | ||
| Studios | $625.00 | 2 | $15,000.00 | $15,450.00 | $15,913.50 | $16,390.91 | $16,882.63 | $17,389.11 | $17,910.78 | $18,448.11 | $19,001.55 | $19,571.60 | $20,158.75 | ||
| One bedrooms | $750.00 | 2 | $18,000.00 | $18,540.00 | $19,096.20 | $19,669.09 | $20,259.16 | $20,866.93 | $21,492.94 | $22,137.73 | $22,801.86 | $23,485.92 | $24,190.49 | ||
| Two bedrooms | $900.00 | 2 | $21,600.00 | $22,248.00 | $22,915.44 | $23,602.90 | $24,310.99 | $25,040.32 | $25,791.53 | $26,565.28 | $27,362.23 | $28,183.10 | $29,028.59 | ||
| Other Income | Price | Quantity | |||||||||||||
| Laundry | $0.75 | 500 | $375.00 | $386.25 | $397.84 | $409.77 | $422.07 | $434.73 | $447.77 | $461.20 | $475.04 | $489.29 | $503.97 | ||
| Late Fees | $25.00 | 12 | $300.00 | $309.00 | $318.27 | $327.82 | $337.65 | $347.78 | $358.22 | $368.96 | $380.03 | $391.43 | $403.17 | ||
| Parking | $15.00 | 72 | $1,080.00 | $1,112.40 | $1,145.77 | $1,180.15 | $1,215.55 | $1,252.02 | $1,289.58 | $1,328.26 | $1,368.11 | $1,409.16 | $1,451.43 | ||
| Total Potential Income | $56,355.00 | $58,045.65 | $59,787.02 | $61,580.63 | $63,428.05 | $65,330.89 | $67,290.82 | $69,309.54 | $71,388.83 | $73,530.49 | $75,736.41 | ||||
| Vacancy Allowance | $2,817.75 | $2,902.28 | $2,989.35 | $3,079.03 | $3,171.40 | $3,266.54 | $3,364.54 | $3,465.48 | $3,569.44 | $3,676.52 | $3,786.82 | ||||
| Forecasted Income | $53,537.25 | $55,143.37 | $56,797.67 | $58,501.60 | $60,256.65 | $62,064.35 | $63,926.28 | $65,844.06 | $67,819.39 | $69,853.97 | $71,949.59 | ||||
| Operating Expenses | |||||||||||||||
| Advertising | $100.00 | $103.00 | $106.09 | $109.27 | $112.55 | $115.93 | $119.41 | $122.99 | $126.68 | $130.48 | $134.39 | ||||
| Auto and travel | $50.00 | $51.50 | $53.05 | $54.64 | $56.28 | $57.96 | $59.70 | $61.49 | $63.34 | $65.24 | $67.20 | ||||
| Cleaning and maintenance | $450.00 | $463.50 | $477.41 | $491.73 | $506.48 | $521.67 | $537.32 | $553.44 | $570.05 | $587.15 | $604.76 | ||||
| Commissions | $600.00 | $618.00 | $636.54 | $655.64 | $675.31 | $695.56 | $716.43 | $737.92 | $760.06 | $782.86 | $806.35 | ||||
| Insurance | $1,100.00 | $1,133.00 | $1,166.99 | $1,202.00 | $1,238.06 | $1,275.20 | $1,313.46 | $1,352.86 | $1,393.45 | $1,435.25 | $1,478.31 | ||||
| Legal and other professional fees | $500.00 | $515.00 | $530.45 | $546.36 | $562.75 | $579.64 | $597.03 | $614.94 | $633.39 | $652.39 | $671.96 | ||||
| Management fees | $3,600.00 | $3,708.00 | $3,819.24 | $3,933.82 | $4,051.83 | $4,173.39 | $4,298.59 | $4,427.55 | $4,560.37 | $4,697.18 | $4,838.10 | ||||
| Repairs | $500.00 | $515.00 | $530.45 | $546.36 | $562.75 | $579.64 | $597.03 | $614.94 | $633.39 | $652.39 | $671.96 | ||||
| Supplies | $1,200.00 | $1,236.00 | $1,273.08 | $1,311.27 | $1,350.61 | $1,391.13 | $1,432.86 | $1,475.85 | $1,520.12 | $1,565.73 | $1,612.70 | ||||
| Taxes | $3,000.00 | $3,090.00 | $3,182.70 | $3,278.18 | $3,376.53 | $3,477.82 | $3,582.16 | $3,689.62 | $3,800.31 | $3,914.32 | $4,031.75 | ||||
| Utilities | $300.00 | $309.00 | $318.27 | $327.82 | $337.65 | $347.78 | $358.22 | $368.96 | $380.03 | $391.43 | $403.17 | ||||
| Other | $125.00 | $128.75 | $132.61 | $136.59 | $140.69 | $144.91 | $149.26 | $153.73 | $158.35 | $163.10 | $167.99 | ||||
| Total Operating Expenses | $11,525.00 | $11,870.75 | $12,226.87 | $12,593.68 | $12,971.49 | $13,360.63 | $13,761.45 | $14,174.30 | $14,599.53 | $15,037.51 | $15,488.64 | ||||
| Operating Income | $42,012.25 | $43,272.62 | $44,570.80 | $45,907.92 | $47,285.16 | $48,703.71 | $50,164.82 | $51,669.77 | $53,219.86 | $54,816.46 | $56,460.95 | ||||
| Part II - Forecast of Pretax Cash Flows, Rate of Return, and Net Present Value | |||||||||||||||
| Total Annual Loan payments | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | ||||
| Pretax Cash Flows | |||||||||||||||
| Initial Investment | $(140,000.00) | ||||||||||||||
| Operating Income | $42,012.25 | $43,272.62 | $44,570.80 | $45,907.92 | $47,285.16 | $48,703.71 | $50,164.82 | $51,669.77 | $53,219.86 | $54,816.46 | $56,460.95 | ||||
| Loan payments | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | $(40,430.65) | ||||
| Sales proceeds | $705,761.89 | ||||||||||||||
| Selling costs | $(35,288.09) | ||||||||||||||
| Loan repayment | $(314,024.36) | ||||||||||||||
| Total Pretax Cash Flows | $(140,000.00) | $1,581.60 | $2,841.97 | $4,140.15 | $5,477.27 | $6,854.51 | $8,273.06 | $9,734.17 | $11,239.12 | $12,789.21 | $14,385.81 | $372,479.73 | |||
| Pretax IRR | 12.28% | ||||||||||||||
| Pretax NPV | $2,751.65 | ||||||||||||||
| Part III - Forecast of Taxable Income and of Aftertax Cash Flows, Rate of Return, and Net Present Value | |||||||||||||||
| Taxable Income Forecast | |||||||||||||||
| Operating Income | $42,012.25 | $43,272.62 | $44,570.80 | $45,907.92 | $47,285.16 | $48,703.71 | $50,164.82 | $51,669.77 | $53,219.86 | $54,816.46 | $56,460.95 | ||||
| Loan interest | $(25,903.75) | $(25,122.47) | $(24,299.17) | $(23,431.58) | $(22,517.34) | $(21,553.93) | $(20,538.71) | $(19,468.88) | $(18,341.52) | $(17,153.52) | $(15,901.63) | ||||
| Building depreciation | $(18,181.82) | $(18,181.82) | $(18,181.82) | $(18,181.82) | $(18,181.82) | $(18,181.82) | $(18,181.82) | $(18,181.82) | $(18,181.82) | $(18,181.82) | $(18,181.82) | ||||
| Loan fee amortization | $(500.00) | $(500.00) | $(500.00) | $(500.00) | $(500.00) | $(500.00) | $(500.00) | $(500.00) | $(500.00) | $(500.00) | $(5,500.00) | ||||
| Taxable Income (loss) | $(2,573.32) | $(531.67) | $1,589.81 | $3,794.52 | $6,086.00 | $8,467.96 | $10,944.30 | $13,519.07 | $16,196.53 | $18,981.12 | $16,877.50 | ||||
| Tax expense (savings) | $(643.33) | $(132.92) | $397.45 | $948.63 | $1,521.50 | $2,116.99 | $2,736.08 | $3,379.77 | $4,049.13 | $4,745.28 | $4,219.38 | ||||
| Aftertax operating cash flows | $2,224.93 | $2,974.88 | $3,742.69 | $4,528.64 | $5,333.01 | $6,156.07 | $6,998.10 | $7,859.35 | $8,740.08 | $9,640.53 | $11,810.93 | ||||
| Other aftertax cash flows | |||||||||||||||
| Initial investment | $(140,000.00) | ||||||||||||||
| Sales proceeds | $705,761.89 | ||||||||||||||
| Selling costs | $(35,288.09) | ||||||||||||||
| Loan repayment | $(314,024.36) | ||||||||||||||
| Capital gains tax | $(33,343.80) | ||||||||||||||
| Aftertax all-inclusive cash flows | $(140,000.00) | $2,224.93 | $2,974.88 | $3,742.69 | $4,528.64 | $5,333.01 | $6,156.07 | $6,998.10 | $7,859.35 | $8,740.08 | $9,640.53 | $334,916.56 | |||
| Aftertax IRR | 10.71% | ||||||||||||||
| Aftertax NPV | $19,729.21 | ||||||||||||||
| Copyright 2015 by Stephen L. Nelson CPA, PLLC. All rights reserved. | |||||||||||||||
no reviews yet
Please Login to review.
