How to Create a Real Estate Investment Model in Excel

This tutorial will show you how to create a Real Estate Cash Flow Model in Excel.

It will first present examples of calculating monthly revenues and costs from property rental and sale. Then, it will forecast the resulting operating, investing and financing cash flows. Lastly, it will show you how to value and calculate returns from your real estate investment.

Download my Real Estate Investment Model Template in Excel to follow the examples used in this tutorial.

Step 1. Create a Real Estate Monthly Build Forecast

To create a Real Estate Cash Flow Forecast, start with a monthly spreadsheet showing the property build’s progress over time.

For example, set a target number of properties and specify the months it will take to complete them. Divide the progress into stages, so some properties can generate income while the build still takes place. 

My Real Estate Investment Model’s Excel Template splits the source of cash flows into two categories:

• Sales Income: it assumes that the business will sell some properties shortly after their completion,
• Property Rental: the remaining properties will be rented and generate monthly income.

Next, use the property forecast to calculate your rental and sales income.

Learn More: Visit my tutorial on How to Build a Cash Flow Forecast in Excel for more examples of creating growth projections.

Step 2. Forecast Rental Income from Properties

To calculate real estate income, take your monthly rental properties projection and then multiply the numbers by the average rent per property.

To calculate the average monthly rent, my Real Estate Investment Model’s Excel Template takes an annual yield % assumption and multiplies it by the property’s average open market value.

For example, a yield of 5.0% and the Open Market Value of $240,000 will result in a monthly rent of $1,000 (5.0% x $240,000 / 12 = $1,000).

Notice that the model assumes that the rent increases in line with the property’s open market value.

Step 3. Calculate Real Estate Operating Expenses

To forecast operating costs in a real estate business, group the expenses into variable and fixed costs.

Variable expenses depend on the growth, so link them to the number of properties sold or rented. Fixed costs are more stable and dependent on other drivers, such as the number of central staff running the business.

For example, my Real Estate Investment Model’s Excel Template calculates property maintenance costs as a function of the number of units rented and an assumed expense per unit. Therefore, the resulting cash flow forecast shows it as a variable expense. 

On the other hand, the model takes the overall headcount assumption and multiplies it by the average salary to calculate the central staff cost. Hence, the cash flow projection shows it as a fixed expense.

Notice that the operating costs increase in line with the annual inflation assumption.

Step 4. Forecast Real Estate Capital Expenditure

To calculate the real estate capital expense, multiply the projected units by the average unit build cost. Then, use the build timeline to spread the cost over time.

For example, let’s assume that the target number of properties is 100, the average cost is $240,000, and the time to complete the is 24 months. In this case, the projected monthly capital expense will be $1,000,000 (100 units x $240,000 / 24 months = $1,000,000 per month).

Download my Real Estate Investment Model Template to see how to create a monthly property build cost forecast in Excel.

Notice that the overall build cost is spread linearly over time in line with the properties build projection.

Learn More: Visit my tutorial on How to Create a Project Finance Model in Excel to read more about modelling capital expenditure in a financial model.

Step 5. Forecast Cash Flow from Sales of Properties

To calculate income from real estate sales, allocate a portion of the properties to a for-sale pool. Then, use the average open market value to estimate proceeds from the transaction.

My Real Estate Investment Model’s Excel Template calculates the number of properties for sale. When a transaction occurs, the spreadsheet takes the number of properties sold. Then, it multiplies it by an average market value at a given period to calculate the revenue from real estate sales.

To calculate the direct costs from the property sales, the model uses two methods:

• Property Sale Cost as % of Gross Revenue: the model takes the overall revenue generated by the property sale and allocates a portion of it as an expense (e.g. $10,000 gross revenue x 10% = $1,000),
•  Property Sale Cost per Unit Sold: the Cash Flow Forecast calculates the expense as the number of properties sold times a unit cost (e.g. 10 units x $1,000 = $10,000).

To forecast the capital expenditure, multiply the number of properties sold by their historical build cost per unit.

Finally, deduct the build and direct costs from the sales proceeds to calculate an overall profit.

Step 6. Calculate the Profitability of Rental Properties

To understand the profitability of a property, create a simple calculator that shows average rental revenues and costs. The numbers will allow you to estimate the financial contribution of each unit to the overall real estate cash flows and the investment payback.

My Real Estate Investment Model’s Excel Template links the rental revenue and cost assumptions to calculate overall profitability and investment returns. It also includes and excludes depreciation charges from the calculation. 

Finally, the breakeven metrics estimate the time it takes to repay the initial investment and generate positive cash flows.

Notice the impact of the occupancy rates and average rental periods on the overall profitability.

Learn More: Visit my tutorial on How to Create a Subscription Model with Churn Calculation for another example of creating a user lifetime value calculator.

Step 7. Adding Debt to a Real Estate Investment Model 

When creating a Real Estate Cash Flow, it’s safe to assume that you can fund part of the properties’ build costs through debt. Including a bank loan will lower your initial equity requirement, although the debt servicing will increase future expenses.

For example, my Real Estate Investment Model’s Excel Template allows you to assume the percentage of build costs financed by debt. It also lets you set the interest rate and the other loan terms, such as its duration or the number of repayments.

As a result, you will notice the model has two sets of monthly cash flows:

• Unlevered Cash Flows: includes all the property rental and sales revenues and expenses without bank loan financing.
• Levered Cash Flows: assumes the injection of real estate debt, costs related to drawing it and its future servicing in the form of principal repayments and interest expense.

Notice that in the case of property sales, the model will automatically repay the relevant portion of the debt at the time of the transaction.

Learn More: Visit my tutorial on creating a Financial Model with Debt Funding to read more about integrating loans into a Cash Flow Forecast.

Step 8. Create a Balance Sheet for a Real Estate Investment Model

To create a balance sheet in an Excel financial model, ensure that the Cash Flow and Profit and Loss sheets are interlinked. That way, a change in a calculation in one tab is reflected in the other. Then, use their outputs to create a balance sheet.

For example, my Real Estate Model’s Excel Template takes the capital expenditure from the investment cash flows to estimate the balance of assets. Then, it offsets it with the depreciation calculation from the Profit and Loss statement.

To estimate the balance of long-term liabilities, take the amount of debt drawn in a given period and offset it with the principal repayment. Interest expense is part of the profit and loss calculation, so the financial model’s template includes it in the balance sheet’s retained profit calculation.

Notice that my real estate model allocates part of the property build cost to stock. The amount is related to the properties intended for sale, not rent. For simplicity, the model does not apply depreciation to those units.

Learn More: To read more about stock and inventory in a balance sheet, click here [external link].

Step 9. How to Evaluate and Analyse a Real Estate Investment

Analyse a real estate cash flow forecast to understand the model’s key contributors. Clearly show the spend from the initial investment, such as build cost. Then, show the level of income the investment will generate annually.

To calculate returns in a real estate investment model, summarise and split the cash flows into the following:

• Cash Flow from Operations: Summarises the inflows and outflows from the month-to-month operations, such as rental income, maintenance expenses, salaries and admin costs. It also includes adjustments for gain or loss from the sale of properties.
• Cash Flow from Investment: For example, a real estate model will display the spending on the building and development of the properties here. It will also include the proceeds from sales.
• Cash Flow from Financing: Includes debt financing, principal repayments, and invested equity. Notice that the interest expense is part of the net income and hence the Cash Flow from Operations.

The statements explain the level and type of funding required. For example, my Real Estate Investment Model’s Excel Template shows an annual contribution of each cash flow. Then, it links the resulting cash deficit to the calculation of additional equity required.

Finally, the model takes the annual summaries to analyse the returns on a real estate investment. To calculate the terminal value, it divides the rental gross margin by the rental yield. Then, it discounts the cash flow to calculate the net present value (NPV) and internal return of return (IRR).

Learn More: To read more about other methods of valuing a real estate business, click here [external link].

Step 10. Learn How to Stress-Test a Real Estate Model

Paste the main inputs next to a real estate cash flow summary, so you can quickly see how a change in one assumption affects the output. Using different scenarios allows you to stress-test the investment and analyse how sensitive the model is. 

Excel allows you to use data tables to test an overall impact of a range of assumption inputs. For example, download my Real Estate Investment Model’s Excel Template to see how changing annual interest rates and properties’ open market value affects the overall returns.

When creating a cash flow model’s output, remember to display the main financial metrics required, such as IRR, NPV or an overall equity investment amount.

Summary: How to Create a Real Estate Investment Model in Excel

This tutorial has shown you how to create a Real Estate Investment Model in Excel. First, it focused on starting with a property build progress forecast. Then, it explained how to link the projections with real estate rental and sales. Finally, it concluded by valuing the investment and stress-testing the model.

Here are the basic steps required to create a Real Estate Investment Model in Excel:

1. Start with a Building Progress Timeline: show a monthly forecast of completing properties over time.
2. Calculate Rental Revenues: link the number of completed properties to average monthly rent assumptions.
3. Estimate Operating Expenses: show the correlation between the number of rented properties and ongoing costs.
4. Forecast Capital Expenditure: use average build cost per unit to calculate overall build cost.
5. Show Cash Flow from Real Estate Sales: calculate property sales’ contribution to the investment plan.
6. Analyse Profitability of Average Property: create a simple calculator displaying the financial return from the real estate investment.
7. Include Debt in the Real Estate Investment Model: show the impact of adding bank loans to the overall cash flow.
8. Create a Balance Sheet: to keep track of the property book value and other assets and liabilities.
9. Analyse the Returns on the Real Estate Investment: clearly show the required funding and the potential return on equity invested.
10. Stress Test the Cash Flows: use Excel to run sensitivity analysis and find the weak spots of the real estate investment model.

Download: Real Estate Investment Model Template in Excel

Click the button below to download my Real Estate Investment Model Template in Excel. The spreadsheet allows you to forecast the property build progress. Then, it links the completed units to calculate revenues and costs. Finally, it takes the resulting cash flow to analyse the return on the real estate investment.

The Excel file contains the following tabs and sections:

• Cash Flow Monthly Model with Debt: a monthly forecast of rental and sales revenues and costs. It also includes monthly debt calculations with principal repayments, interest expenses and debt fees.
• Depreciation and Tax Calculation: a monthly forecast showing the depreciation of assets over time, calculated separately for for-sale and rental properties. Also, an estimate of payable tax with an option to include interest expense in the calculation.
• Profit & Loss Summary and Balance Sheet: an annual breakdown of real estate net income, assets (i.e. properties book value) and liabilities (i.e. outstanding loan balance).
• Equity Investment Return Analysis and Rental Investment Calculator: a calculation of investment returns based on the real estate cash flow model and an estimate of the profitability of an average property.
• Assumptions and Sensitivity Analysis: the model allows you to easily change the primary inputs and stress test the cash flow forecast.

Get in Touch

Hi, my name is Jacek, and I love Excel. I hope you’ve enjoyed reading this tutorial as much as I enjoyed writing it! If you have any questions about Real Estate Financial Models in particular or financial analysis in general, don’t hesitate to get in touch.

Click here if you would like to book my One-to-One Financial Modelling Crash Course. For more Financial Modelling Tutorials, visit this page. If you are looking for help with financial modelling or cash flow forecasting, see my Financial Modelling and Forecasting Consulting services here.

Learn More:

How to Create a Telecom Investment Model in Excel – explore another example of how to forecast cash flow and returns on invested equity in a capital-intensive infrastructure project.

How to Create a Project Finance Model in Excel – this post will explore consolidating multiple projects into a single cash flow.

Tutorial on How to Create a Cash Flow Forecast in Excel – learn more about forecasting growth and its impact on the monthly revenue and costs.

Learn More on How to Build a Financial Model with Debt Financing in Excel – this tutorial will show you more examples of implementing debt in a cash flow model using projected revenues.

How to Create a Subscription Model in Excel – I talk more about the cash flow impact of growing and losing customers over time.

Your First Steps in Excel – Beginner’s Tutorial – if you need to refresh your Excel basics, this tutorial will take you through the main formulas and functionalities.