ExcelFrog Clear Excel Tutorials

home about

Excel: Financial Modelling for a Real Estate Investment

Note: if you are looking for a freelancer to build a custom financial model for your business, I'm available. Learn more here.

In our previous tutorial, we learned the concept of problem-solving modelling. Today, we're stepping into a more advanced realm, financial modelling in Excel, with a real-life example.

Before we start, you can download the Excel model by clicking this link. It contains 3 tabs: Inputs, Calculations, and Outputs, that we will cover below.

The Problem

You're interested in investing in real estate: buying an apartment and renting it out. Is it a profitable investment? How much do you need to invest? Real estate investment is exciting, but it can be tricky to navigate the financial aspects.

To make an informed decision, you want to forecast your cash flows and understand your potential returns. The financial model we're introducing here will help you do just that. Let's see how it works!

The Inputs

We start by defining our investment assumptions in the Inputs tab. Here is the data you either have at hand or can make educated guesses about.

For our scenario, the main inputs you need to set are:

• Property Purchase Date and Price.
• Financing inputs
  • Down Payment: the upfront payment made when buying the property.
  • Mortgage Rate: the interest rate on the loan.
  • Mortgage Duration: the duration of the loan in years.
• Operating inputs
  • Monthly Rent: the amount you plan to charge tenants each month.
  • Occupation Rate: the percentage of time your property is rented out over a year.
  • Expenses: regular costs that you need to pay like maintenance, property taxes, insurance, etc.
• Tax assumptions
  • Rental Income Tax Rate: the percentage of gross rental income that is paid to the government as income tax.
  • Tax Deductible Expenses: costs that can be subtracted from the rental income, thus reducing the overall tax.
• Sell case
  • Property Sell Date: the date when you sell your property.
  • Appreciation Rate: the yearly rate at which the property’s value increases.
• Inflation Rate: the percentage increase of prices for goods and services in our economy over a year.

Fill in the inputs based on your specific situation or projections. The better your estimates, the more reliable the results.

So, how do we turn these inputs into meaningful information?

Intermediary Calculations

Now, let's move onto the Calculations tab, which is essentially the powerhouse of our model.

The entire tab is automatically calculated and updated based on the assumptions you provided. The goal of this tab is to break down complex computations into intermediary steps.

There are different computation modules, each dedicated to calculating a specific aspect of the real estate investment, such as revenues, operating expenses, mortgage, tax, and more. You can find the the intermediary results at the top of the tab.

If you are curious about the magic that makes these calculations work, we're crafting a series of tutorials that will open up each module for you.

The Outputs

The Outputs tab is possibly the most sought-after section of our financial model. It contains a dashboard revealing the details of your real estate investment performance.

Here you can explore four key aspects of your investment:

• Uses and Sources for Property Purchase: this section breaks down the costs of acquiring the property and your funding strategy.
• Investment Return Indicators: this presents critical indicators to measure your investment's profitability.
• Mortgage Info: if you've opted for a bank loan to finance your purchase, this area holds all relevant details.
• Cash Flow Chart: this dynamic chart visualizes the year-on-year cash flow scenario of your property. You can choose to display either annual cash flow or monthly average cash flow per year.

This dashboard is customizable, look out for our upcoming tutorials with tips on how to personalize it.

Exploring Different Scenarios

By adjusting the inputs, you can simulate different scenarios and see how they affect your potential returns. This is a powerful way to plan and prepare for a variety of outcomes.

Once you've entered the inputs, the calculations and outputs update automatically, allowing you to easily adjust and test different scenarios.

Model Limitations

Before we conclude, it's important to be aware of the limitations of this model:

• One investment per simulation: this model assumes you're buying a single property. If you have a portfolio of investments, you might need to duplicate the model or adapt it to suit your needs.
• Annual calculation: this model calculates returns on an annual basis. This is a simplification that can lead to less precise results, but it still gives you a good high-level overview.
• High-level simulation: this model includes only essential assumptions and some calculations are simplified, like income tax. If your investment scenario includes unique factors not covered in this model, you may need to modify it.

Conclusion

By breaking down the problem into inputs, intermediary calculations, and outputs, real estate investment analysis becomes far more manageable. With the help of this model, you can confidently navigate your real estate investment journey.

Don't forget to download the model for yourself and start exploring. Stay tuned, keep learning, and happy investing!

See all the tutorials on the homepage