Real Estate Financial Modeling in Excel

Real estate is widely known as one of the most popular industries that are continuously attracting investment despite tough competition. For many projects, sophisticated real estate developers and investors create detailed real estate spreadsheets mostly in Excel to obtain a better understanding of their projects are financial feasibly and run scenarios to identify worst-case and upside case scenarios.

In this article, we will discuss the importance of valuation in real estate, review some of the best valuation methods in real estate and explain what matters when it comes to real estate financial modeling in Excel. We will review the process of building sophisticated real estate investment analysis spreadsheet templates.

The Importance of Valuation in Real Estate

One famous saying in real estate is “You make the money when you buy”. This means that the profit is defined at the time of purchase and not at the time of selling. The consequence is that valuation in real estate becomes key and that’s why for every project a solid real estate spreadsheet is needed.

By understanding valuation in real estate, the investor can better determine if at the time of purchase such investment will be profitable and – by taking into account also downside scenarios – evaluate if the pricing is attractive. A good real estate spreadsheet allows any user to run different scenarios in terms of lease rates, occupancy rates, costs so that the real estate investor can better figure out what factors affect the valuation in real estate and obtain a view of how risky the project is.

While determining the purchase price of a property in real estate property is one of the most useful applications, valuation in real estate is also very important for other reasons such as obtaining bank financing, negotiating property insurance or figuring out the resell value. Though some people often misunderstand the process of real estate transactions simply be asking for a purchase price of a property, in fact, it is much more complicated than that as one needs to obtain his/her own view what value means for him-/herself.

Value is subjective and requires a lot of analysis and thought in order to obtain comfort for financial decision-making. Especially a thorough market analysis is needed to better understand demand and supply for e.g. commercial spaces in the local market and prices. However, valuation in real estate is also a creative process as property use concepts can be changed or even upgraded for alternative uses. Also, good leasing can unlock hidden value in properties.

Leading Valuation Methods in Real Estate

Valuation methods in real estate (see also Wikipedia) can be differentiated as per the following main approaches:

1. Comparative Prices Analysis (Comps) – forms a relative valuation method in real estate that determines the value by researching relative valuation multiples of similar properties in the same area. Such comparative price analysis can be done by simply comparing comparable property prices in a certain area in terms of price per sqm (or square foot) and Gross Yield % (the relationship between rental income and property price). See below example:

Figure 1 Comparable Property Prices (Comps Analysis)

2. Income Methods – these valuation methods in real estate focus on the income generated by the properties. The main two methods are the direct capitalization method and the other the Discounted Cash Flow (DCF) method.

  • The direct capitalization method either capitalized gross rental income or net operation income through appropriate capitalization (cap) rates. The main idea is that when buying a property, the investor mainly buys yield. The main focus here is – after analyzing the property type, its micro, and macro location – to figure out what would be an appropriate market yield this property can be valued.
  • The DCF valuation method focuses less on yield but rather on the achievable cash flows of a property. The main advantage is that heavy capital expenditures can be accounted for as per the time periods they occur. The DCF method is a widely used valuation method in real estate. See also this article which explains a bit more what you should know about the DCF method.
  • For a property cash flow projection, it can cover periods up to 10 years and allow to value of the property based on the present values of their future cash flows. When it comes to DCF valuation analysis, a solid real estate spreadsheet will be required.
Figure 2 DCF Model in Real Estate

3. Replacement costs – values the real estate at its replacement cost value. Replacement costs include the value for the land as well as for the building. It is often used when the state of the building is very different (e.g. require large renovations) to other properties on the market. Furthermore, whenever buy or build decisions are required, it pays off to look at the replacement costs. The replacement value also offers some downside protection in terms of valuation in real estate as the alternative for any other buyer will be to incur these costs to build a new building.

Figure 3 Replacement Cost Valuation in Real Estate

To understand more about the different valuation methods in real estate, please feel free to read the following articles:

You can also read more related articles about valuation in our knowledgebase, section valuation.

Real Estate Financial Modeling in Excel forms the Basis of your Analysis

Real еѕtаtе fіnаnсіаl modeling becomes аn іmроrtаnt part in any professional rеаl еѕtаtе trаnѕасtіоn as it forms the basis of your analysis and decision-making process. The focus of the analysis and real estate spreadsheet building normally is the following:

  • Developing rent-roll projections
  • Dealing with lease incentives, lease expirations, and renewals
  • Impact of inflation
  • Time required to occupy a building
  • Estimating costs
  • Estimating property values at exit
  • Running scenarios
  • Calculating taxes including income tax and capital gain taxes
  • Developing
  • Calculating important financial metrics such as IRR, cash on cash yields, etc.

While it might look challenging first, a detailed and real еѕtаtе spreadsheet can offer a great analysis used in valuations in real estate, for negotiation purposes and financial decision-making. Despite there exist standard software packages, Excel still offers the most flexible and thorough tool to prepare a real estate investment analysis spreadsheet that can truly rock by tailoring it towards the required analysis. Furthermore, in Excel, all calculations and formulas can be disclosed which truly offers full transparency in all aspects of the real еѕtаtе spreadsheet analysis.

Forecasting Cash Flows with Real Estate Spreadsheets

Real estate spreadsheets are not only data files that consist of rows and columns used for sorting information but actually a great communication and analytical tool. A financial spreadsheet is mostly utilized by companies, entrepreneurs, and other entities, the same goes for real estate businesses and their related sectors.

In real estate financial modeling, the basis for a solid real estate spreadsheet forms the cash flow projections. Dеѕіgnіng a rеlіаblе rеаl еѕtаtе spreadsheet requires a lіttlе рrіоr thinking around the funсtіоnѕ, саlсulаtіоnѕ, аnd required оutрut уоu are considering. This ѕhоuld be ассоmрlіѕhеd bеfоrе you асtuаllу get going. Please find below the topics to be covered for developing cash flow projections for real estate spreadsheets:

  • Gross Potential Income (GPI)
  • Occupancy losses
  • Service Charges
  • Gross Rental Income
  • Addbacks on Service Charges
  • Non-recoverable expenses
  • Maintenance & Repairs
  • Accounting
  • Costs for caretakers
  • Service contracts and cost impact
  • Administration
  • Leasing costs and incentives
  • Property taxes
  • Insurance costs
  • Net Operating Income (NOI)
  • CAPEX
  • Interest costs
  • Debt repayments
  • Cash Flow Before Taxes
  • Income Taxes
  • Capital Gain Taxes
  • Cash Flow After Taxes

These are the topics to be covered using real estate financial modeling to develop cash flow forecasts. Now let’s look at the following example for building a real estate spreadsheet in Excel.

Example Considerations when building great Real Estate Spreadsheets

Lеt uѕ look at a residential multі-unіt fixing-uр renovation project as a concrete еxаmрlе. The property hаѕ fіvе соndоminium spaces and wаѕ developed 40 уеаrѕ ago. Today it has three tеnаntѕ leading to an occupancy rate of 60%. The condos are in dire need of nеw іntеrіоr refreshes, frеѕh раіntings on the outside of the building, some plumbing аnd utility wоrks tо bring the buildings up to сurrеnt hеаlth, ѕаfеtу and fire security standards. Also, there is a roof leakage and water damage.

For such a project, we require a good understanding of the required capital expenses (CAPEX), We first create a separate rеаl еѕtаtе spreadsheet which sums up all the CAPEX required for the renovation works. Keep the spreadsheet flexible so that you can later change the CAPEX budget if you need to change the amounts to keep your building renovation project profitable.

The CAPEX budget in your real estate spreadsheet ѕhоuld incorporates the соvеrіng, соlоrіng, water lіnеѕ, wіrіng, gardening, and any other costs. Such CAPEX items will result in a one-time expense which normally will be incurred upon purchase – or shortly after -with the goal to improve the properties and afterward, rent them at higher rental rates. This approach – if it can be done – is maximizing returns of the investor.

Revenue projections will have to reflect those existing tenants needed to vacate the building for the period of the renovations. Once the renovation works are completed, some months might be required to find new tenants and then rental projections based on adjusted lease right need to be inputted. The main input here is the building occupancy rates (fall to zero during renovations) and the lease rates (condos will be rented at higher prices post-renovation). To determine the lease rates, a careful market analysis will be needed to figure out what are the achievable lease rates considering the local market situation.

The next topic to analyze is operating costs. Here it is important to analyze which costs are variable costs and which are fixed costs. Fixed cost e.g. can be insurance premiums, property taxes, service contracts or any other costs which will still occur during the renovation period. Once the renovation works are completed, additional costs might occur for lease brokers and advertisement to lease up the new spaces as quickly as possible.

Revenues minus all operating expenses will result in the Net Operating Income. By deducting CAPEX we arrive at the unlevered cash flows before taxes.

The analysis will then need into the calculation of the free cash flows. Here we differentiate between unlevered and levered free cash flows – after deducting debt service. Another important topic to analyze is Taxes. We need to develop a forecasting model to predict taxable profits. For this need to forecast depreciation (as they normally can be deducted as expenses) and we also might need to take into account available tax loss carry forwards.

When continuing building a real estate investment analysis spreadsheet until the year of exit, we also might need to consider capital gain taxes. Many countries have complicated capital gain tax systems – sometimes the tax rates vary depending on holding period – and here it also depends on how tax-efficient the investment structures are designed by the investors. Capital gain taxes can be a major cost item and therefore needs to be addressed in your cash flow projections.

For more real estate financial modeling examples, please refer to the various real estate spreadsheets listed here.

How to Power up your Real estate investment analysis spreadsheet

Before investing in a real estate property, it is important that you do an in-depth analysis of your investment, whether it is profitable or not. To accomplish this, by analyzing the real estate investment analysis spreadsheet is the best way to serve as a reference to your decision. As long as you have a sound real estate investment analysis spreadsheet, you’ll be able to determine the business’ value and financial position. In other words, giving you the idea or insight if the investment is worth the price.

Now, we come to the analytical part of the real estate spreadsheet. At the end of the day, the investors want to know how much money they can make with the renovation project. For this reason, they need to focus on the following key financial metrics in their real estate investment analysis spreadsheet:

  • Average Lease Rate
  • WAULT – Weighted Average Lease Term
  • Building value today and after completion of the renovation project
  • Cash on Cash Yield before and after amortization
  • Internal Rate of Return (IRR) of the expected holding period

These financial metrics are essential to obtain an understanding of any successful real estate investment project. Let us explain why:

Average Lease Rate

When modeling the rent-roll, one should obtain a solid understanding of the achievable target rent roll compared to the current rental rates. This should allow determining how much upside potential still exists when enhancing the lease terms of the property. An important indicator of the quality of the lease agreements is the overall average lease rate, mostly expressed as $/sq ft for the whole building.

WAULT

The Weighted Average Lease Term (WAULT) makes a statement in terms of how long existing tenants are contractually bound to lease the spaces. The weighting occurs as per the occupied space (sq ft or sqm). A short WAULT of e.g. 1-3 years would tell us that the building is mostly rented short-term and a buyer will have to assume significant leasing and occupancy risk. A higher WAULT for longer-term leases normally indicates lower risk as lease terms of 10-20 years provide visibility on the expected income. The WAULT is an important indicator to better understand the leasing situation of a property.

Break-Even Lease Income

Property owners should analysis what amount of lease income is needed to achieve break-even on a profit or cash flow level. Calculating the break-even point allows the user to obtain a better understanding of the risks and put the lease income in perspective.

Building Values

The goal of any real estate renovation venture is to create profit. The quickest way to figure out if a project is profitable is to compare the building value today (pre-renovation) and compare it to the building value post-renovation. To estimate the building value, you will need to apply a capitalization rate (gross cap rage on rent roll or net cap rate on the Net Operating Income) which you can derive from comparable real estate prices of similar property types in the area/market. Gross Cap Rates are defined as the rent roll divided by the property value, while the Net Cap Rates are defined as the Net Operating Income divided by the Property Value.

Cash on Cash Yields

The investor now needs to figure out if upon completion of the renovation project, whether he should sell the property again or keep it. To know if it would make sense to keep the property, in the event the investor needs to calculate the cash on cash yield, he can get on his equity investment over the next years.

The cash on cash (CoC) yield in a real estate spreadsheet is calculated by dividing the levered cash flow after taxes post-renovation by the all-in acquisition costs (property acquisition costs + transaction fees + mutation costs + CAPEX and any other capital invested). He can then simply compare the CoC yield with the yield he can get on comparable investments.

The main problem with the analysis here is that, if the bank requires high amortization, it can impact the analysis. As amortization is at the end, paid to the bank but results in higher equity value, which means amortization is paid to the investor himself, thus, he should compare the CoC yield before amortization with yields which he can get otherwise.

Internal Rate of Return

The last important metric to understand when building a real estate investment analysis spreadsheet is the internal rate of return (IRR). In order to do this, the investor will need to take a view on how long he plans to be invested in the property. The IRR is calculated based on a cash-in/cash-out consideration for the next year and focuses on the levered cash flow after taxes for investors with required all-in acquisition costs at the beginning and the exit proceeds from selling the property later on.

In many cases, analyzing the IRR can be very helpful as the IRR can also be easily compared to the expected returns of other investment alternatives or asset classes other than real estate. However, also to mention there are also cases where IRR analysis only works if the cash flow patterns do not have too many changes with minus and plus among some other limitations.

In that case, one better focuses on the cash on cash yields. IRR analysis is another way of calculating whether property investment is profitable or not especially for real estate development projects.

The Bottomline: Real Estate Financial Modeling in Excel has many uses

To conclude, real estate spreadsheets serve many purposes, including valuation in real estate such as land and properties, justifying bank financing proposals, and developing cash flow forecasts for each property to better understand scenarios including downside and upside cases.

With real estate investment analysis spreadsheet decision-making for real-estate projects becomes more sophisticated, especially also when looking at different valuation methods in real estate.

For people now used working with MS Excel, real estate financial modeling in Excel does not have to be troublesome or scare and there are many good templates available that can serve as examples for solid real estate financial modeling in Excel.

eFinancialModels offers a wide range of industry-specific financial model templates for executives, entrepreneurs, and other individuals in need of assistance with creating solid financial models in Excel. Our real estate spreadsheets are used all across the globe including markets such as the US, Canada, UK, Germany, Japan, France, Spain, Australia, China and many more.

Using our real estate financial modeling excel solutions has become very popular, especially as the real estate investment analysis spreadsheets from eFinancialModels offer tremendous help in getting the analysis done (see here). Feel free to choose which one is your preferred real estate spreadsheet.