Discounted cash flow analysis - Real Estate

Real Estate Investment evaluation can be done (like almost all investment evaluations) with a discounted cash flow analysis. I have created an Excel Model to perform such calculations.

File: Evaluation of Real State Investments

This Excel Model helps to estimate the future cash-flows from different investment options. After forecasting the stream of cash flows, they are discounted to calculate their present value. To decide if the investment should be done, the NPV criteria is employed.

An example of investments that can be evaluated:

1. Buy a property and to sell it at a higher price.

1. Buy a property and rent it.

1. Buying a property , rent it and sell it at a higher price.

Both possible financing ways are considered, with mortgage or paid-in-cash.

Practical aspects of the model:

First  sheet:  Entry data. You have to introduce parameters and data to perform the calculations.

• Related to the property; purchase price, time to evaluate the investment, total property depreciation (expected loss of VALUE, not loss of price) and penalty for sale (due to the illiquidity of the market). The property depreciation and the penalty for sale are subjective value which need to be estimated based on historical data and past values of similar properties.

• Related to the income; the monthly rental payment (equal to zero if not renting); average months without renting per year (estimate with the data of similar properties and similar renters) and average rental payment growth rate  (estimate from historical data of the local market).

• Related to the expenses; the general expenses amount (like phone, internet and energy) and maintenance and repair. Both need to be estimated with historical data from similar properties. The expenses will be updated each period with the inflation rate.

• Related to taxes; this aspect is based on the Spanish regulation, with which I am familiar, but it can differ a lot in different countries.  Owning a property has two main taxes: income tax and property tax. The income tax is a percentage of the personal income (revenues minus deductions, detailed in the Excel Model). The property tax is a percentage of the rateable value, which can increase over the time. In Spain, the renter can deduce the depreciation, which is the 3% of the maximum value between purchase price and rateable value. Selling a property has also a tax. It is a percentage of the increment of the rateable value between the purchase and the sale. If not renting, enter 0 in amortization rate and income tax rate.

• Related to the mortgage; the model considers a fixed-rate mortgage. It includes also the expenses related with the negotiation of the mortgage (formalisation fee and extra fees). If the buyer will not borrow a mortgage, enter zero in the extra fees, legal costs and in the formalisation fee.  Also, the entrance fee must be equal to the purchase price.

• Related to the market; the inflation rate (average value) and the annual increment of house prices need to be specified. They can be obtained from historical data.

• Related to the discount rate of the cash flows, it is equal to the sum of the risk-free rate (like 1 year T-bill), opportunity cost (depend of the investor), the liquidity premium (depending on the local real estate market) and the desired additional profitability by the investor.

The second sheet is the mortgage payment sheet. This sheet calculates the payments to the mortgage. Also, it shows the interest and the amortization components of each payment, and how they vary along the time.

The third sheet is the cash flows sheet. This sheet calculates the cash flow stream by forecasting the revenues, costs and taxes.

The fourth sheet is the Net Present Value sheet: this sheet shows the outputs of the model, like the future value of the house, the selling price, selling taxes, present value of the obtained cash in the sale and the net present value of the investment (for two scenarios, selling or holding the house).