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.
Table of Contents
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.
Step 1. Modelling Property Build Progress in Excel. The model phases the number of completed units by multiplying addressable properties by the build progress %. You can download the entire template here.
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).
Step 2. Forecasting Rental Revenue in a Real Estate Cash Flow Model. The template calculates the monthly average rent as a function of a property’s average Open Market Value and the yield assumption. You can download the entire Excel file here.
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.
Step 3. Real Estate Rental Operating Expenses Forecast. The Staff Cost is directly linked with the headcount and the average monthly salary. On the other hand, the General Opex is more steady and increases over time with inflation and revenues. You can download the complete Excel model here.
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.
Step 4. Real Estate Capex Forecast Model in Excel. The model calculates the total build cost by multiplying the portion of buildings completed by an average cost assumption. You can download the entire Excel template here.
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).
Step 5. Real Estate Sales’ Cash Flow Forecast in Excel. The income is derived from the sale of properties, which is adjusted for expenses and building costs to calculate the monthly cash flow contribution. You can download the complete model template here.
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.
Step 6. Real Estate Investment Return Calculator. To estimate the payback period, the calculation divides a property build cost by an average annual rental net income. You can download the entire Excel template here.
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.
Step 7. An example of Real Estate Unlevered and Levered Cash Flow Forecast. The model assumes debt financing to offset a share of the build cost and decrease the overall equity requirement. You can download the entire Excel file here.
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.
Step 8. A Balance Sheet in a Real Estate Excel Investment Model. The example’s Long-Term assets reflect the investment in properties over time. On the other hand, the liabilities consist mainly of long-term debt and equity injections. You can download the complete template here.
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.
Step 9a. An example of a Cash Flow from Operations Statement in a Real Estate Model. The model adjusts the net income for deprecation and other non-cash items to arrive at the final figure. You can download the entire Excel file here.
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).
Step 9b. A Table with a Real Estate Investment Return Analysis. The template calculates the exit proceeds by dividing the rental gross margin and by a yield assumption. It also uses Excel’s NPV and IRR formulas to estimate returns on investment. You can download the complete Excel Model here.
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.
Step 10. An example of using Excel’s Data Table for sensitivity analysis in a Real Estate Investment Model. The output tests the impact of debt interest rate % and average property build cost on the equity investment requirement (Scenario #1, left) and the Internal Rate of Return (Scenario #2, right). You can download the entire template here.
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:
- Start with a Building Progress Timeline: show a monthly forecast of completing properties over time.
- Calculate Rental Revenues: link the number of completed properties to average monthly rent assumptions.
- Estimate Operating Expenses: show the correlation between the number of rented properties and ongoing costs.
- Forecast Capital Expenditure: use average build cost per unit to calculate overall build cost.
- Show Cash Flow from Real Estate Sales: calculate property sales’ contribution to the investment plan.
- Analyse Profitability of Average Property: create a simple calculator displaying the financial return from the real estate investment.
- Include Debt in the Real Estate Investment Model: show the impact of adding bank loans on the cash flow.
- Create a Balance Sheet: to keep track of the property book value and other assets and liabilities.
- Analyse the Returns on the Real Estate Investment: clearly show the required funding and the potential return on equity invested.
- 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].
*Order risk-free with a 30-day Money Back Guarantee
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.