155x 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.