Rental Property Cash Flow Return Calculator

Purpose and model boundary

This calculator works out the cash flow and return of one rental property bought with a fixed-rate loan, held for a number of years and sold. The main result is the return on the money invested, the internal rate of return of the down payment and closing costs, the yearly cash flows and the sale, at your average assumptions. Every figure is before income tax. The page also shows the cap rate, the cash-on-cash return and the debt service coverage of the first year. It then simulates 100 possible futures of rent growth, vacancy and property value, and reports the share of futures returning less than the mortgage rate and the share with at least one year of negative cash flow.

For the cost of owning against renting your own home, use the Rent vs Buy Calculator. For the monthly payment with tax and insurance, use the Mortgage Payment PITI Calculator, and for the costs of closing, the Closing Costs Estimator. The Monte Carlo Retirement Success Calculator simulates market returns for a retirement plan with the same seeded generator.

The model is educational. It is not a prediction of rents, prices or interest rates, and it is not financial, investment, tax or legal advice.

Inputs and units

Enter the purchase price, down payment and any closing costs paid in cash; the loan's yearly rate and its term in whole years; the monthly rent in the first year; the share of rent lost to vacancy; property tax and insurance in the first year; repairs as a share of the scheduled rent and management as a share of the rent collected; the yearly growth of rent, of property tax and insurance, and of the property value; the years you hold before selling; and selling costs as a share of the sale price. Amounts are US dollars; rates and shares are percentages.

The uncertainty inputs drive the simulation only: the volatility of rent growth and of appreciation, as standard deviations, and the vacancy in a good year and in a bad year. Closing costs may be left blank for none, and the growth of property tax and insurance blank to grow them with rent.

The workbook always runs 100 futures. Its optional seed defaults to 42 for the worked examples. On the page, Simulate uses the last result's next seed before a repeated run, while the first run after a worked example or Reset uses that example's seed. API and MCP callers can supply a seed to reproduce a run.

Governing relationships

The loan is paid monthly at the fixed rate. With the loan L, the monthly rate i, the term n in years and A(m) = (1 + i)^m − 1, the payment and the balance after t years are

payment = L × i × (1 + A(12 n)) / A(12 n)

balance(t) = L × (A(12 n) − A(12 t)) / A(12 n)

and the loan is paid off once the term is reached. A(m) is worked out from LN and EXP in Kahan's compensated forms of ln(1 + i) and e^x − 1, so a tiny positive rate keeps full accuracy instead of cancelling; a rate of exactly 0 uses the interest-free payment L / (12 n). In year t, with scheduled rent R, vacancy v, property tax and insurance F and debt service DS,

net operating income = R × (1 − v) × (1 − management) − repairs × R − F

cash flow = net operating income − DS

Rent in the first year is twelve times the monthly rent; it grows from the second year. At the end of the hold the property sells for its value less selling costs, and the loan balance is repaid; if that leaves less than nothing, the owner pays the difference. The return r is the yearly rate at which

money invested = Σ cash flow(t) / (1 + r)^t + sale / (1 + r)^years

In each simulated future, each year's rent growth is the expected growth plus the volatility times a standard normal draw, each year's vacancy is drawn evenly between the good-year and bad-year figures, and each year's appreciation is a lognormal draw whose mean and standard deviation match the expected appreciation and its volatility. Everything else follows the same arithmetic as the figures at the average assumptions.

Calculation sequence

The workbook works out the figures at the average assumptions, then calculates 100 futures and derives the percentiles, the shares and the return chart. The return is found without iteration, by halving the range from −99% to 300% a year: 48 times at the average assumptions and 32 times in each future, so it is exact to well under a hundredth of a percentage point. A repeated Simulate action uses the last result's next seed before the new run.

Outputs and interpretation

The return on the money invested is the main result. The cap rate divides the first year's net operating income by the price and ignores financing; the cash-on-cash return divides the first year's cash flow by the money invested; the debt service coverage divides the first year's net operating income by its debt service, and is not shown for a cash purchase. Below 1, the property's income does not cover its loan payments in the first year.

The first chart is a bridge of the first year: gross rent, net operating income and cash flow are bars from zero, and each reduction is a red bar below zero. The second chart shows, for each year of the hold, the cash flow, the principal paid down and the rise in value. The year table gives each of these with the loan balance and value, and the sale in the last year.

The return chart counts the simulated futures in 20 equal bins from the 2nd to the 98th percentile of their returns; red bars return less than the mortgage rate. When even a return of −99% a year cannot break even, the future shows −99% and counts as one where the owner pays to sell; a future above 300% shows 300%. Neither kind, nor the few futures outside the chart's range, is drawn, but every future counts in the shares. The shares from 100 futures carry a sampling error of about ±9.8 percentage points at worst.

Validation and status logic

The workbook evaluates the conditions below from top to bottom and returns the first matching status. This is the workbook's priority order, including warnings that may accompany a computed result.

NOT VALID is a refusal, not a numeric answer. CHECK may accompany a computable result but calls for the stated review. The self-check is separately reported as arithmetic-integrity evidence; it does not replace this ordered status chain. A negative cash flow or a debt service coverage below 1 is a result, not a warning.

Assumptions and limitations

Every figure is before income tax: the model does not include depreciation, its recapture, tax on the gain at sale or the deductibility of interest, which shape the after-tax return of most rentals. Each year is one period, and the loan has a fixed rate with no refinancing. Rent growth, vacancy and appreciation are drawn independently, so a future with weak rents is no more likely to have weak prices; vacancy in one year does not carry into the next. Capital spending such as a new roof, homeowner association fees and any cost not entered are not modelled. A cash flow pattern that changes sign more than once can have more than one return; the search returns the one within its range. Amounts are nominal.

Restrictions and non-computing states

This calculator enforces its numeric limits before it accepts a result. An optional input left blank takes its stated default; a retained seed remains part of every simulation.

Errors and warnings

A request rejected by the page's input limits is not a computed result. A transport or service failure cannot establish a fresh workbook answer. Review the workbook's References and Disclaimer, and check rents, costs and financing for a real property with a qualified professional before using a result for a real decision.

Sources and further reading

The model's prices, rents, costs, rates and growth assumptions come from the inputs, not market data. The workbook's References sheet identifies the calculation sources. The links below explain the methods; they do not endorse this calculator.

• S. K. Park and K. W. Miller, “Random number generators: good ones are hard to find,” Communications of the ACM 31(10), 1988, and the Park–Miller–Stockmeyer 1993 correspondence describe the generator family. The workbook uses multiplier 48,271 and modulus 2³¹ − 1.
• The NIST/SEMATECH e-Handbook's lognormal distribution section describes the distribution behind the appreciation draws, and Microsoft Support's NORM.S.INV and PERCENTILE.INC documentation the workbook functions used for the draws and the percentiles.
• Microsoft Support's IRR documentation defines the internal rate of return the workbook finds by bisection.

This is an educational estimate, not financial, investment, tax or legal advice. Rents, costs, values and financing differ from property to property; check them with a qualified professional.