Real Estate Calculator with Python

A friend asked me about some add-on for his real estate project.

He wanted to know how profitable can be real estate, compared with a dividend investing strategy.

After thinking about it for a while, we can see the following pattern:

1. Stock value can be seen as property value
2. Stock dividend yield as the renting price of the property
3. Most 8significant difference*: we dont take loans for dividend investing, but people normally do for real estate

This arise the question: what it is the return on the money that ive given from my pocket?

Apparently, finance people call that the ROIC (without leverage on a loan, ROI=ROIC)

But its all about few mathematical ways to represent very logical financial concepts.

Understanding Loans

When we get a mortage, our net total assets today are reduced, as we have some interests to pay (liabilities):

French Amortization 101

This is the French amortization formula: $M = P \times \frac{r(1 + r)^n}{(1 + r)^n - 1}$.

How does french amortization looks like in practice?

As mentioned, you will have constant payments (given constant interest rate) and you start by paying mostly interests.

There you have a sample split evolution of how much you are paying on interest and on principal return to the bank.

The shape of the curve depends on the parameters you set:

• Interest Rate
• Years of Return

As you can imagine, the higher those 2 are, the higher interest you pay in value and also in relation with the principal you got the loan for.

That’s right, it can be 50%, but the interest you pay back does not have to be a 50/50 split with the pincipal.

I mean in total.

Because if you understood previous graph, you already now that the split varies month per month.

This below, is just the full picture (end to end) of the operation with the lender:

If you have high interest and high period onf years to return, you end into the red zone (more interest returned than principal)

Dividend Growth vs Rent Growth

#https://www.nasdaq.com/market-activity/stocks/o/dividend-history
=(importxml("https://www.nasdaq.com/market-activity/stocks/o/dividend-history";$AJ$28))

First div of 2025 has been 0.264 and first of 2024 was 0.2565, or a 2.92% growth.

But some years are better than others, right?

Lets see the CAGR for dividend growth

Useful Concepts

CAGR

The CAGR formula is just: $CAGR = \left( \frac{V_f}{V_i} \right)^{\frac{1}{t}} - 1$.

Where:

• ( V_f ) is the final value
• ( V_i ) is the initial value
• ( t ) is the time period (usually in years)

In the example, as per NASDAQ O Data, the first div of 2020 was 0.2325$, thats a x1.135, but in 5 years.

So…can we have some kind of constant growth rate over that period?

$$ CAGR = \left( \frac{0.264}{0.2325} \right)^{\frac{1}{5}} - 1 \approx 0.0257 $$

So its an equivalent of 2,57% of dividend growth, each year, during the last 5 years.

This is a ‘virtual’ number, some years was more, some less, buts thats the compound rate

And it ofc depends in your stock investment, same as your potential property investment.

Other example with MCD:

$$ CAGR = \left( \frac{1.77}{1.25} \right)^{\frac{1}{5}} - 1 \approx 0.072 $$

Or ‘just’ a ~7,2%

And now, the logical question appears: when will (if ever) catch up with the higher initial yield one?

How many years to…

1. The 72 rule: The “Rule of 72” is a simple way to estimate the number of years it takes for an investment to double, based on a fixed annual rate of return (interest or growth rate).

It’s an easy-to-remember approximation that gives results that are accurate enough for most financial calculations (especially with rates between 6% and 10%).

If you divide 72 by the interest rate in percentage terms, you get a good estimate for the doubling time.

For example:

• At a 6% return: ( 72 \div 6 = 12 ) years to double.
• At a 9% return: ( 72 \div 9 = 8 ) years to double.

2. With proper math:

The exact formula to find the time to double is $t = \frac{\ln(2)}{\ln(1 + r)}$.

3. The formula to find the time to grow by a general factor of XYZ is $t = \frac{\ln(XYZ)}{\ln(1 + r)}$.

With this one, we can see when MCD will potentially, catch up with O (while O also grows, but to a slower rate)

$$ t = \frac{\ln(5.8/2.5)}{\ln(1 + (7.2-2.7))}=17.8 $$

And with those rates, the yield of that stock you are buying today, catch up in ~17.8 years!

Dividend Growth

The important fact here is, that we dont get a loan for dividend investing (do we?)

In these examples, for dividends:

1. Higher initial yield ~5,8% and lower Div Growth ~2,7%
2. Lower initial yield ~2,5% and Higher Div Growth ~7,2%

What’s better?

As always, it depends, faster growing divs, also tend to imply higher stock value growth rate.

Parameters to Track

1. DCA or all in approach?
2. Value of the stock and yield on cost
3. Estimations of: value growth and dividend (it could also be decrease!)

Rent Growth

In this case, we are receiving rental income (dividend income) also from the borrowed amount!

This can (or not) help to pay mortage interests.

So the costs from your pocket of this one are: Initial payment + Mortage Payment - Net Rent

Parameters to Track

1. Interest Rate & Years
2. Value of the property and % the bank loaned
3. Estimations of: property value growth and rental growth (it could also be decrease!)

What’s going on here?

1. You are earning dividends on the full property
2. But you only invested initially a part of the full property value

As you can imagine, if real life goes like this, you have earned a lot by using debt.

You were using the rental price to pay the mortage (and you got a surplus later on)

And also the property most likely increased the value on a 25 years horizon since you bought it.

Those 2 factors, while you ‘just’ put from your pocket the orange part of the graph.

This is an example on how ROIC » ROI

Rent Price vs Property Price

There are some interesting dynamics between these 2, the price2rent ratio.

People trying to leverage loans, tend to look for low property price to rent price ratios.

Also using low interest rates and long horizons to pay back the debt, so that very quickly the rental prices exceed the mortage amount, which provides them with Free Cash Flow very early (using loaned money and exposed to other risks).

What? here you have a Sankey diagram

---
config:
  sankey:
    showValues: true
---

sankey-beta

"Own Money","Property",20
"Bank Money","Property",80
"Property","Rental",10
"Rental","Return to Bank",5
"Rental","FCF",5

And the workflow would be something like:

flowchart LR
    A[Apply for Loan] --> B{Loan Approved?}
    B -- Yes --> C[Receive Loan]
    C --> D[Purchase Property]
    D --> E[Rent Property]
    E --> F{Rent Paid?}
    F -- Yes --> G[Receive Rent]
    G --> H[Repay Loan]
    H --> I{Loan Repaid?}
    I -- Yes --> J[Profit/Ownership]
    I -- No --> K[Continue Repayment]
    K --> H
    B -- No --> L[Loan Denied]

And during the full period of the operation, it could be as follows:

---
config:
  sankey:
    showValues: true
---

sankey-beta

"Own Money","Property",20
"Bank Money (Principal)","Property",80
"Bank","Bank Money (Interest)",30
"Property","Rental",80
"Rental","Return to Bank",100
"Rental","FCF",50
"Bank","Bank Money (Principal)",80
"Own Money","Return to Bank",10

You might need to put some more money in the initial stages of the process, despite having later on FCF available.

What exactly? We need to make some zoom in on the previous cash flow graph:

The break even point is much later on, but see how initially you might have some parameters that will make you place additional money from your pocket (in addition to the one you give initially).

That will assist the initially lower rental price to pay back all the liabilities.

This of course has several risks involved:

1. How consistent is the rental? Any damages? Occupancy ratio? Whats the rental growth if any?
2. What will be the interest rate evolution during the loan period?

If interest rise enough, you can go from FCF to be unable to pay to the bank, hence potentially loosing the house (and more).

Those situations where people lost it all, happened not so long ago, in ~2008.

People buying it very high prices, then interest went up, making the property value go down (which was their biggest asset) and when you cant pay and all you have lost 50% of its value…you stay with nothing.

To avoid such situations to happen again (in theory), there are rules that wont let you take too much credit in comparison with what you earn.

For example: Max credit monthly payment < 0.35*(Net Salary + Other Net Income)

Imo, even with such formulas to see how solvent you are, there are risk, but…what do I know about finances!

What if you get it right?

Depending in the economical cycle, where you look… these ratios will be different.

But this ratio can be seen as the PER on stocks.

If you got an initial yield of 10% (PER 10) on the total property value (more on your invested capital), it will be very hard to a stock to a dividend stock to catch up!

But those values are just a snapshot and in real life all those values are floating: interest, rental price (yield), property value,…

Real Estate Data

First thing I thought was airbnb data.

• https://insideairbnb.com/get-the-data/
  • https://insideairbnb.com/valencia/

But I also heard about idealista:

• https://www.idealista.com/data/
• https://www.idealista.com/labs/blog/?p=4207
  • https://www.idealista.com/labs/blog/?p=4207
  • https://paezha.github.io/idealista18/

They even create a R Package paezha/idealista18 for this, with 2018 data!

Credits to both platforms for sharing such interesting data!

Modelling Bull and Bear Markets

If you have a look at the data, its clear that the trend (at least the nominal value), tends to be upwards.

But there are moments where the price and rental price dont grow, or even decrease.

It also happens with interest rates!

I thought about 2 very simple ways to model this:

1. Constant growth, whatever you decide, ignoring the big ups and downs, which should be more or less precise on the long run (if you get right the rates, ofc)
2. To make something cooler, how about:

• The general trend is upwards, but there will be sin functions applied as well
• The initial/final values will be the same
• Just in between, thanks to the periodic functions we will have ups and downs

How does it looks like?

Which could be summarized as:

‘Change is the only constant’

And in the meantime, nominal prices tend to go up

You can also expect for interest rates to fluctuate (if you did not chose a fixed interest loan).

Which could also be modelled similarly to these.

Conclusions

See some crazy rates and you will be soon rich?

Calm down :)

Those are nominal growth values, dont forget to take into consideration inflation, which is part of those figures.

More about inflation and how it can hurt (or benefit?)

Retirement Facts

Life savings and inflation in a R Shiny App.

Retirement Facts

Life savings and inflation in a R Shiny App.

Taking inflation into consideration is very important.

I was talking with a friend recently, who bought a flat in 2020 and she told me now it’s wort more than 50% what she paid for.

Remember to have a broader look to this kind of conversation.

Providing that return is true, that’s the nominal one.

And as you understand what it is CAGR, you know that x1.5 in 5 years is a ~8% compund yearly return.

Is it much? is it low?

Here is where most people disconnect, please continue reasoning

Well, on the same period, the inflation level has been ~8% a year.

So now you now that in real value, flats are as expensive or as cheap as they were in 2020 compared with the rest of prices.

Was it a good decision? a bad one?

As always, depends how you look at it.

My friend is not renting the apartment (hence doing the automatic reinvestment of the property yield).

But, she is not paying an increasingly high rent (which actually in this period grew more than the flat price).

Thanks To

• Thanks to airbnb and idealista for the historical data

• Interesting Financial realted Posts:

  • https://estudinero.substack.com/
  • https://estudinero.substack.com/p/la-inflacion-el-impuesto-invisible

• https://wtfhappenedin1971.com/

• Thanks to HUGO Hextra Theme and katex!

$$ \begin{aligned} \nabla \cdot \mathbf{E} &= \frac{\rho}{\varepsilon_0} \ \nabla \cdot \mathbf{B} &= 0 \ \nabla \times \mathbf{E} &= -\frac{\partial \mathbf{B}}{\partial t} \ \nabla \times \mathbf{B} &= \mu_0 \left( \mathbf{J} + \varepsilon_0 \frac{\partial \mathbf{E}}{\partial t} \right) \end{aligned} $$

$$\ce{Hg^2+ ->[I-] HgI2 ->[I-] [Hg^{II}I4]^2-}$$

KNO3 + C12H22O11 → CO2 + H2O + N2 + K2O

Al + HNO3 → Al(NO3)3 + NOx + H2O