How to Create a Real Estate Investment Model in Excel

This tutorial shows how to create a Real Estate Cash Flow Model in Excel.

First, it presents examples of calculating monthly revenues and costs from property rental and sale. Then, it shows how to forecast the resulting operating, investing and financing cash flows. Lastly, it covers the valuation and returns from a real estate investment.

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

You can also watch the video version of the tutorial at the end of this post.

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. Then, divide the progress into stages so some properties can generate income while the building is ongoing. 

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

• Sales Income: the model assumes that the business will sell some properties shortly after their completion,
• Property Rental: properties for rent generating monthly income.

Lastly, 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, multiply the number of projected rental properties by the average monthly rent.

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. On the other hand, 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. 

Lastly, the model multiplies the overall headcount assumption 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, and the average cost is $240,000. In addition, the time to complete the is 24 months. Consequently, 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 schedule of properties built.

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. Firstly, 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 its portion 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).

Also, 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. As a result, 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, you can assume that part of the funding for the properties’ build costs will come from debt. Consequently, 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. Therefore, a change in a calculation in one tab is reflected in the others. Then, combine the numbers 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. As the interest expense is part of the profit and loss calculation, the 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, which is related to the properties intended for sale and not rent. Furthermore, 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. First, show how the main expenditures, such as building costs, are converted into additional units. Then, show the level of annual income generated by the investment.

To calculate returns, my Real Estate Investment Model’s Excel Template aggregates and splits 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 and interest expenses.
• Cash Flow from Investment: For example, the spending on the building and development of the properties, including the proceeds from sales.
• Cash Flow from Financing: Includes debt financing, principal repayments, and invested equity.

Finally, my template divides the rental gross margin by the rental yield to calculate the terminal value. 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 to see how a change in one assumption affects the output. Moreover, use different scenarios to stress-test the investment and analyse how sensitive the model is. 

For example, Excel allows you to use data tables to test the overall impact of a range of assumption inputs. You can 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, consider financial metrics, 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 on the 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. In addition, it 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 change the primary inputs and stress easily test the cash flow forecast.

The file is in Excel format, but you can convert it to Google Sheets using the instructions here [external link].

Video: How to Use the Real Estate Investment Model Template

For more details and a step-by-step explanation of using and creating the Real Estate Investment Model Template in Excel, watch my video tutorial below:

 

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 to book my One-to-One Financial Modelling Crash Course. For more Financial Modelling Tutorials, visit this page. If you need help with financial modelling or cash flow forecasting, see my Financial Modelling and Forecasting Consulting services here.

Note that this tutorial is provided for general informational and educational purposes only and is not a substitute for professional advice.

Learn More:

Telecom Investment Model in Excel – An example of forecasting cash flow and returns on invested equity in a capital-intensive infrastructure project.

Project Finance Model in Excel – Learn how to consolidate multiple projects into one cash flow and use returns from one entity to fund others.

Capital Investment Plan in Excel – Convert an initial investment into growth and revenues forecast along with cash flow balance sheet calculations.

Monthly Budget and Forecast in Excel – Learn how to convert a short-term budget into a long-term forecast.

Merger and Acquisition Model in Excel – An example of calculating a company’s value and using debt financing to acquire other companies’ assets.

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 – An example of implementing debt in a cash flow model using projected revenues.

Subscription Model in Excel – Learn more about the cash flow impact of growing and losing customers over time.

Your First Steps in Excel – Beginner’s Tutorial – Refresh your spreadsheet basics with this overview of the main formulas and functionalities.