This comprehensive case study brings together key principles of financial analysis to solve a real-world dilemmaassessing the profitability of a real estate purchase. Specifically, we will calculate the IRR (Internal rate of return) for an apartment. This analysis is complex, as it integrates cash flow, taxation, debt, and inflation. Therefore, I will guide you through the calculation process step-by-step. The complete Excel model used for this calculation is available for download at the end of this article. Let’s begin by defining the investment scenario.
This article was originally written in Czech. Therefore, the currency used in the example is the Czech Crown (CZK), and some parameters are specific to Czech law. However, you can download the Excel file with all tables and formulas at the end of this article to make your own adjustments
Apartment Investment and Profitability Calculation – Case Study
We are planning to realize a real estate investment in a district town—a panel apartment 3+1, 73 m2—for the purpose of renting and future sale (in 30 years). The purchase price of the property is 3,000,000 CZK. We will finance the property with a mortgage loan with 80% LTV (we must pay 20% from our own resources), a 30-year maturity, monthly repayment frequency, and an interest rate of 2.7%.
We asked a real estate consultant for an estimate of income and expenses. According to the real estate consultant, we can assume:
Income:
- Rent in the first 10 years – 12,000 CZK per month
- Rent between the 10th and 20th year – 13,500 CZK per month
- Rent between the 20th and 30th year – 15,000 CZK per month
- The real estate agent warns us that we can expect an average annual income loss equal to 0.5 month’s rent (tenant turnover), and we should account for this fact in the calculation (a conservative approach)
- We estimate that we will sell the property for 4,000,000 CZK in 30 years (a conservative approach).
Expenses:
- Our real monthly costs will be 500 CZK for a car, 1,300 CZK for the repair fund, 1,500 CZK for the apartment repair reserve (set aside), 1,500 CZK for owner’s time, insurance, the annuity loan payment, and an annual 15% income tax.
- We assume an average annual inflation rate of 2.7% p.a. throughout the investment, reducing our purchasing power.
Our required rate of return is at least 1.5% p.a. + inflation. Is the investment suitable for us?
How to calculate the Internal Rate of Return (IRR) for Real Estate Investment
Calculation of the Annuity Payment and Amortization Schedule
To calculate the Internal Rate of Return, we will need to know the cash flow (Income – Expenses) for each year. From the assignment, we see that most of our income and expenses are fixed, except for the loan payment. We don’t know that yet and must calculate the annuity payment. We will need the annuity payment as a part of our regular expenses. Besides that, we will need to calculate the amortization schedule because we will deduct the loan interest from the tax base.
If we know the loan amount, repayment period, frequency, and interest rate, it’s not a problem to create an amortization schedule with 360 rows (30 years * 12). The annuity payment we will send to the bank every month is 9,734 CZK.
Summarizing Income and Expenses into a Clear Table
From the assignment and the previous step, we know that our income and expenses look like this:
The green and red tables represent the income and expenses entering the Cash Flow. The last item that will also enter the cash flow is the income tax. This annual income tax will be calculated according to the black table.
Creating a Simple Cash Flow Table and Calculating Income Tax
We can now clearly summarize all the data into a table where each row represents 1 year.
- Operating Income is calculated based on the expected rent, subtracting half a month’s rent each year as an income loss due to tenant turnover.
- Sale Income in Year 30 – in the 30th year, we sell the property for 4,000,000 CZK.
- Investment Outlay – in year 0, we incur an investment of 600,000 CZK because the bank only covers 80% of the property price.
- Operating Expenses – we calculated these for each year from the red table (see 2a).
- 15% Income Tax – To calculate the income tax, we must first calculate the “Eligible Expenses” (gray table, see 2a) and then take 15% of the difference between income and expenses. If the result is negative (a loss), the income tax is 0 (it is not a tax bonus, such as a child tax credit).
- Total CF = Income + Real Expenses (negative) + Tax (negative)
Now we have a pretty good overview of how much the investment yields, and we just need to calculate the investment profitability in %.
Calculating the Internal Rate of Return (IRR) and Investment Return
The article on the Internal Rate of Return (IRR) provides the methodology. IRR is the rate at which the Net Present Value (NPV) is equal to 0.
Since the IRR cannot be directly expressed from the formula, linear interpolation is used for the calculation:
- First, we estimate IRR1 so that the Net Present Value (NPV1) is negative.
- Then, we estimate IRR2 so that the Net Present Value (NPV2) is positive.
And we substitute into the formula:
Investment Evaluation and Reasoning
The Internal Rate of Return (IRR) for the apartment investment, given the chosen parameters, is 4.86% p.a. Our required rate of return is 1.5% + 2.7% inflation = 4.2% p.a. Thus, the investment is above the required profitability threshold, and we will accept it. We approached the parameter estimation conservatively—we included reserves for apartment repairs, accounted for the risk of tenant turnover, and conservatively estimated the property’s selling price in 30 years (4,000,000 CZK).
Info: Investment modeling is dependent on estimated input parameters. In reality, no one knows the future rate of inflation, income, or the interest rate of loans after refinancing.
I am sharing the Excel file – Excel with the solution »