Rental Property DCF Sensitivity in Excel

A rental property DCF sensitivity model tests how an investment valuation changes when income growth and required returns change. Separating property operations from borrowing costs makes the results easier to interpret.

This Excel worked example builds the income-to-cash bridge, a five-year discounted cash flow valuation and a two-variable sensitivity table using hypothetical assumptions.

Estimate rental property cash flow by deducting vacancy, operating expenses and capital expenditure from potential rent. Then deduct debt service to calculate owner cash before tax. This original hypothetical example separates that financing calculation from an unlevered discounted cash flow (DCF) valuation. It contains no client results or current market-rate advice.

Build the income-to-cash bridge

Use a sheet named Rental. All figures are dollars and annual unless stated otherwise.

Cell	Assumption or calculation	Value or formula	Output
B2	Purchase price at time zero	500000	$500,000
B3	Potential annual rent	30000	$30,000
B4	Vacancy fraction	5%	5%
B5	Annual operating cash expenses	10000	$10,000
B6	Annual capital expenditure	0	$0
B7	Growth in net operating cash	2%	2%
B8	Annual valuation discount rate	6%	6%
B9	Exit capitalisation rate	5%	5%
B10	Effective annual rent	`=B3*(1-B4)`	$28,500
B11	Net operating income	`=B10-B5`	$18,500
B12	Unlevered annual cash flow	`=B11-B6`	$18,500
B13	Average monthly unlevered cash	`=B12/12`	$1,541.67

Capital expenditure is zero solely to keep this example short. Replace it with a dated spending schedule for actual replacement and improvement costs. Monthly averages conceal vacancy timing and irregular repair bills.

The property yield calculator offers a separate rent and expense check. Its financing calculation uses principal-and-interest repayments, so it cannot reproduce the interest-only owner-cash example below. Compare operating figures separately, or use consistent amortising terms in both models before comparing owner cash. Personal tax treatment is outside this example.

Calculate owner cash after financing separately

Assume an illustrative $300,000 interest-only facility at 6% annually. Annual debt service is =300000*6%, or $18,000. Owner cash before tax is =18500-18000, or $500 annually and $41.67 monthly. The initial $200,000 deposit is an equity outflow, and the $300,000 principal remains repayable at maturity or sale. Acquisition costs would increase initial equity needs.

Changing that facility's contractual rate to 7% increases annual interest to $21,000 and reduces owner cash to -$2,500. Changing only B8 leaves those payments unchanged. An amortising loan requires principal as well as interest in the debt-service calculation.

Project five annual unlevered cash flows

Assume year-end operating receipts, growth in the entire net operating cash flow and sale at the end of year five. Selling costs and future capital expenditure are zero in this demonstration. The exit value capitalises year-six income at B9. An actual model should separate rent growth, expense growth and capex.

Set C16:G16 to years 1 through 5. Put =$B$12 in C17 and =C17*(1+$B$7) in D17. Copy D17 across to G17. Set C18:F18 to zero and G18 to =G17*(1+$B$7)/$B$9. In C19 enter =C17+C18 and copy across.

Row	Year 1	Year 2	Year 3	Year 4	Year 5
Operating cash, row 17	18,500.00	18,870.00	19,247.40	19,632.35	20,024.99
Exit proceeds, row 18	0	0	0	0	408,509.90
Total cash, row 19	18,500.00	18,870.00	19,247.40	19,632.35	428,534.89

Retain full precision. B21 contains =NPV(B8,C19:G19) and returns $386,184.41. B22 contains =B21-B2 and returns investment NPV of -$113,815.59. Under these assumptions the value of future property cash is below the purchase price.

Microsoft's NPV documentation explains equally spaced end-of-period cash flows, with time-zero cash added separately.

The NPV and IRR calculator can check the annual series. Enter -$500,000 at time zero, followed by the five total cash flows, with a 6% annual discount rate.

Calculate the sensitivity grid

Keep first-year operating cash at $18,500, the horizon at five years and the exit capitalisation rate at 5%. Vary growth in the entire operating cash flow and the annual discount rate. Each result below is present value of future property cash, rounded to whole dollars.

Annual discount rate	0% cash growth	2% cash growth	4% cash growth
4%	$386,472	$421,352	$458,942
6%	$354,414	$386,184	$420,420
8%	$325,681	$354,671	$385,907

For an Excel two-input Data Table, put =B21 in J25, growth assumptions in K25:M25 and discount rates in J26:J28. Select J25:M28 and choose Data > What-If Analysis > Data Table. Set the row input cell to B7 and the column input cell to B8. Subtract the $500,000 purchase price from any result to obtain that scenario's investment NPV.

Test the exit cap rate separately. The exit proceeds already incorporate the assumed resale value; adding another capital-growth receipt would double-count that value. Keep debt outstanding out of the unlevered valuation and deduct it when calculating equity sale proceeds.

Review assumptions and liquidity together

Use a rent roll, vacancy history, operating invoices, capital works plan and selling-cost estimate to replace the demonstration inputs. Record the evidence and observation date beside each assumption. Discount and exit rates require property-specific support.

Stress vacancy, repairs and sale proceeds together, then inspect the actual financing schedule for monthly cash deficits and balloon repayments. This is an educational pre-tax calculation with simplified timing. It provides a reproducible starting point for property-specific professional review.

FAQs

Frequently asked questions

How do I build a two-variable property DCF sensitivity table in Excel?

Create a valuation formula linked to separate assumption cells. Put a reference to the result in the top-left corner of the table, one assumption across the top and the other down the first column. Set the row and column input cells to the corresponding model assumptions.

What is the difference between property present value and investment NPV?

Property present value discounts future operating cash and net sale proceeds. Investment NPV also includes the purchase and acquisition cash outflows at time zero. Keep financing consistent: an unlevered valuation excludes loan draws, interest and principal repayments.

How do I estimate terminal value for a rental property DCF?

One approach divides the next period's stabilised net operating income by an assumed exit capitalisation rate, then deducts selling costs. Support both income and exit-rate assumptions with property-specific evidence. Keep capital expenditure separate and test the exit assumption because it can dominate value.

How should growth feed into terminal value?

When the exit value capitalises next-period income, project income one period beyond the final operating year before dividing by the exit capitalisation rate. Use consistent nominal or real assumptions and deduct selling costs. A separate appreciation receipt would double-count the assumed resale value.

Should a rental DCF use monthly or annual cash flows?

Use a period that captures material timing differences and match the discount rate to it. Annual periods can support a simplified valuation, while monthly forecasts expose vacancy gaps, irregular bills and debt payments. Avoid treating an annual average as a liquidity forecast.