|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
REAL ESTATE INVESTMENT
ANALYSIS
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This
spreadsheet is designed to allow you to forecast the income, expenses, NOI
and after tax returns of an income property held for 10 years.
|
|
|
It
will also calculate estimated profits when you sell, both before and after
tax.
|
|
|
|
|
|
|
|
|
|
Of
course, the old theory applies, garbage in, garbage out. And it also must be
understood that it
|
|
|
is
truly impossible to predict the future path of inflation, rent increases and
property prices.
|
|
|
|
|
|
|
|
|
Enter numbers in the
YELLOW fields only
|
|
|
|
|
|
|
|
Purchase Price:
|
|
|
|
|
|
|
Down Payment:
|
|
|
|
|
|
|
Interest Rate on Loan:
|
|
% p.a.
|
|
|
|
|
Term of Loan:
|
|
|
yrs.
|
|
|
|
|
Improvement Ratio:
|
|
|
%
|
percentage of building itself to total
property value including land |
|
|
|
No. Yrs. of Depreciation:
|
|
yrs. Straightline for
residential.
|
39.5 years for
commercial.
|
|
|
Scheduled Annual Gross
Income:
|
|
|
|
|
|
Vacancy/Collection
losses:
|
|
%
|
|
|
|
|
(Annual Operating
Expenses)
|
---------
|
|
|
|
|
Property taxes :
|
|
|
|
|
|
|
Insurance:
|
|
|
|
|
|
|
Electricity:
|
|
|
|
|
|
|
Gas:
|
|
|
|
|
|
|
Oil:
|
|
|
|
|
|
|
Water:
|
|
|
|
|
|
|
Trash:
|
|
|
|
|
|
|
Management:
|
|
|
% of income
|
|
|
|
|
Repairs/Maintenance:
|
|
% of income
|
|
|
|
|
Advertising:
|
|
|
|
|
|
|
Telephone:
|
|
|
|
|
|
|
Other:
|
|
|
|
|
|
|
Other:
|
|
0
|
|
|
|
|
Other:
|
|
|
|
|
|
|
Other:
|
|
|
|
|
|
|
Annual Increase of
Income:
|
|
%
|
p.a.
|
|
|
|
|
Annual Increase of
Expenses:
|
|
%
|
p.a.
|
|
|
|
|
Annual Appreciation Rate:
|
|
%
|
p.a.
|
|
|
|
|
Investor's Tax Bracket:
|
|
%
|
|
|
|
|
Capital Gain Tax Rate:
|
|
%
|
This varies depending on
the tax payer.
|
|
|
|
CGT Rate on Recaptured
Depreciation
|
|
%
|
|
|
|
|
Expected Capital
Improvements:
|
|
|
|
|
|
Approx. Buying Costs:
|
|
%
|
of total
|
|
|
|
|
Approx. Sales Costs:
|
|
|
%
|
of total
|
|
|
|
|
|
|
|
RESULTS
|
|
|
|
|
|
|
|
|
|
ACQUISITION
DATA
|
|
LOAN DATA
|
|
DEPRECIATION
|
|
|
|
|
|
|
|
|
Price
|
|
|
Interest %
|
|
|
Land
|
|
|
|
|
|
Dn. Pymt.
|
|
|
No. Yrs.
|
|
|
Improvement
|
|
|
|
|
|
Loan Amt.
|
|
|
Mo. P & I
|
|
|
No. Yrs.
|
|
|
|
|
Buy Costs
|
|
|
Yr. P & I
|
|
|
Yr. Depr.
|
|
|
|
|
Cap Impr.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ANNUAL
OPERATING INCOME
|
Yr.1
|
Yr. 2
|
Yr. 3
|
Yr. 4
|
Yr. 5
|
Yr. 6
|
Yr. 7
|
Yr. 8
|
Yr. 9
|
Yr. 10
|
|
|
|
|
|
|
|
Increase
in income
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Expected
Gross Income
|
|
|
|
|
|
|
|
|
|
|
|
|
less
Vacancy/Collection losses
|
|
|
|
|
|
|
|
|
|
|
|
|
|
EFFECTIVE
GROSS INCOME
|
|
|
|
|
|
|
|
|
|
|
|
|
(EGI)
|
|
|
|
|
|
|
|
|
ANNUAL
OPERATING EXPENSES
|
Yr. 1
|
Yr. 2
|
Yr. 3
|
Yr. 4
|
Yr. 5
|
Yr. 6
|
Yr. 7
|
Yr. 8
|
Yr. 9
|
Yr. 10
|
|
|
|
|
|
|
(Increase
in expenses)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Property
Taxes
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Insurance
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Electricity
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Gas
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Oil
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Water
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Trash
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Management
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Repairs/Maintenance
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Advertising
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Telephone
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Other
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Other
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Other
|
|
|
|
|
|
|
|
|
|
|
|
|
|
TOTAL
OPERATING EXPENSES
|
|
|
|
|
|
|
|
|
|
|
|
|
Op.
Expenses as %age of income
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
NET
OPERATING INCOME
|
|
|
|
|
|
|
|
|
|
|
|
|
(NOI)
excluding financing costs
|
|
|
|
|
|
|
|
|
|
|
|
CASH
FLOW (BEFORE TAXES)
|
Yr. 1
|
Yr. 2
|
Yr. 3
|
Yr. 4
|
Yr. 5
|
Yr. 6
|
Yr. 7
|
Yr. 8
|
Yr. 9
|
Yr. 10
|
|
|
|
|
|
|
Net
Operating Income
|
|
|
|
|
|
|
|
|
|
|
|
|
Yrly. P & I
|
|
|
|
|
|
|
|
|
|
|
|
|
|
CASH
FLOW (BEFORE TAXES)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
TAX
BENEFIT
|
|
Yr. 1
|
Yr. 2
|
Yr. 3
|
Yr. 4
|
Yr. 5
|
Yr. 6
|
Yr. 7
|
Yr. 8
|
Yr. 9
|
Yr. 10
|
|
|
|
|
|
|
Net
Operating Income
|
|
|
|
|
|
|
|
|
|
|
|
|
-Annual Interest
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Annual Depreciation
|
|
|
|
|
|
|
|
|
|
|
|
|
Taxable
Income
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Investor's Tax Bracket
|
|
|
|
|
|
|
|
|
|
|
|
|
|
TAX
BENEFIT
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
MORTGAGE
PRINCIPAL REDUCTION
|
Yr. 1
|
Yr. 2
|
Yr. 3
|
Yr. 4
|
Yr. 5
|
Yr. 6
|
Yr. 7
|
Yr. 8
|
Yr. 9
|
Yr. 10
|
|
|
|
|
|
|
Start
of Yr. Balance
|
|
|
|
|
|
|
|
|
|
|
|
|
|
End of Yr. Balance
|
|
|
|
|
|
|
|
|
|
|
|
|
|
TOTAL
PRINCIPAL REDUCTION
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
PROPERTY APPRECIATION
|
Yr. 1
|
Yr. 2
|
Yr. 3
|
Yr. 4
|
Yr. 5
|
Yr. 6
|
Yr. 7
|
Yr. 8
|
Yr. 9
|
Yr. 10
|
|
|
|
|
|
|
(Increase)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Start
of Year Value
|
|
|
|
|
|
|
|
|
|
|
|
|
|
End
of Year Value
|
|
|
|
|
|
|
|
|
|
|
|
|
|
TOTAL
ANNUAL APPRECIATION
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
FINANCIAL ANALYSIS
|
|
|
|
|
|
|
|
|
RETURN
ON INITIAL EQUITY(Assume Initial
Equity = Down Payment + Capital Improvement)
|
|
|
|
|
|
|
|
|
Yr. 1
|
Yr. 2
|
Yr. 3
|
Yr. 4
|
Yr. 5
|
Yr. 6
|
Yr. 7
|
Yr. 8
|
Yr. 9
|
Yr. 10
|
|
|
|
|
|
|
Cash
Flow (Before Taxes)
|
|
|
|
|
|
|
|
|
|
|
|
|
Tax
Benefit
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Debt
Reduction
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Appreciation
|
|
|
|
|
|
|
|
|
|
|
|
|
|
$ RETURN ON INITIAL EQUITY
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
INITIAL
EQUITY:
|
|
|
|
|
Down
Payment
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Capital
Improvement
|
|
|
|
|
|
|
|
|
|
|
|
|
|
TOTAL
INITIAL EQUITY
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
%
RETURN ON INITIAL EQUITY
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
RETURN
ON TOTAL EQUITY (Assume Total Equity
= Start of Year Value - Start of Year Loan Balance)
|
|
|
|
|
|
|
|
|
Yr. 1
|
Yr. 2
|
Yr. 3
|
Yr. 4
|
Yr. 5
|
Yr. 6
|
Yr. 7
|
Yr. 8
|
Yr. 9
|
Yr. 10
|
|
|
|
|
|
|
Cash
Flow (Before Taxes)
|
|
|
|
|
|
|
|
|
|
|
|
|
Tax
Benefit
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Debt
Reduction
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Appreciation
|
|
|
|
|
|
|
|
|
|
|
|
|
|
$
RETURN ON TOTAL EQUITY
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
TOTAL
EQUITY:
|
|
|
|
|
Beginning
of Year Value inc. Cap. Imp.
|
|
|
|
|
|
|
|
|
|
|
|
|
Begining
of Year Balance
|
|
|
|
|
|
|
|
|
|
|
|
|
TOTAL
EQUITY
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
%
RETURN ON TOTAL EQUITY
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
ESTIMATED SALE PROCEEDS
(AFTER EXPENSES & TAXES)
|
|
|
|
|
|
|
|
|
|
|
|
|
Yr. 1
|
Yr. 2
|
Yr. 3
|
Yr. 4
|
Yr. 5
|
Yr. 6
|
Yr. 7
|
Yr. 8
|
Yr. 9
|
Yr. 10
|
|
|
ADJUSTED
COST BASIS
|
|
|
|
Original
Basis
|
|
|
|
|
|
|
|
|
|
|
|
|
|
+
Capital Improvements`
|
|
|
|
|
|
|
|
|
|
|
|
|
+
Sales Costs
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-
Accum. Depreciation
|
|
|
|
|
|
|
|
|
|
|
|
|
=
ADJUSTED COST BASIS
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
True
CAPITAL GAIN
|
|
|
|
|
Sales
Price
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Non
adjusted cost
|
|
|
|
|
|
|
|
|
|
|
|
|
|
True
Gain or loss
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
CAPITAL
GAIN for tax purposes
|
|
|
|
Sales
Price
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-
Adjusted Cost Basis
|
|
|
|
|
|
|
|
|
|
|
|
|
CAPITAL
GAIN (tax purposes)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
CAPITAL
GAIN TAX
|
|
|
|
|
Capital
Gain for tax purposes
|
|
|
|
|
|
|
|
|
|
|
|
|
=
Tax on Capital Gain
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
EST.
NET SALE PROCEEDS (after tax)
|
|
|
|
Sales
Price
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-
Sales Costs
|
|
|
|
|
|
|
|
|
|
|
|
|
|
-
Ending Loan Balance
|
|
|
|
|
|
|
|
|
|
|
|
|
=
Proceeds Before Taxes
|
|
|
|
|
|
|
|
|
|
|
|
|
-
Capital Gain Tax
|
|
|
|
|
|
|
|
|
|
|
|
|
|
=
EST NET SALE PROCEEDS A/T
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
You
must hold the property for a year and a day to qualify for long term capital
gains tax treatment.
|
|
|
|
|
|
 DEBT SERVICE COVERAGE RATIO (DSC)
|
|
|
|
|
|
|
|
|
|
Yr. 1
|
Yr. 2
|
Yr. 3
|
Yr. 4
|
Yr. 5
|
Yr. 6
|
Yr. 7
|
Yr. 8
|
Yr. 9
|
Yr. 10
|
|
|
|
|
|
|
Net
Operating Income
|
|
|
|
|
|
|
|
|
|
|
|
|
-
Debt Service (Yr. P & I)
|
|
|
|
|
|
|
|
|
|
|
|
|
DSC
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This
report is based on certain assumptions. The projections are estimates only
|
|
|
Anyone
using or relying on this report is advised to seek competent legal,
financial, and/or tax advice.
|
|
|
|
|
|
|
This
spreadsheet is copyright Mortgage-Investments.Com, Inc. 2004
|
|
|
|
|
|
|
|
|
