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 significant 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.

There are out there cool calculators - https://tecalculo.com/en/mortgage-calculator

And this one for salaries - https://tecalculo.com/en/spanish-net-salary-calculator

But how about creating something to understand the underlying logic?

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.

In this case it starts with ~85% of what you pay, being interests.

As per the french amortization behaviour, it goes down to 0% in the last payment.

This means, that if you return principal earlier and yo keep the monthly payments constant, you will finish the operation earlier, leading to a save on the last interests you had to pay.

As you can imagine, the more you advance payments, the more % of interest you are saving versus the amount given.

And overall, it would be ~54,4%.

Again, all with this sample figures.

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)

Net assets

So in the beginning of ’the game’, these are your cards:

• The lender owns the principal, but you can use it. At least until you keep paying as per payment schedule
• You have extra liabilities to pay now, the interests

Basically, your net assets at the moment of the operation are going from something, to less of that something.

And your net assets at the initial moment could be less than zero.

This is an example, where in the NAV the interests to be paid are not included, but visible in a separated line:

How much into the deep could this operation make me be?

Well, that depends ofc of:

1. Interest rates. See Historical ones for PL
2. Years to Pay
3. LTV - How much you ask, versus how much you got initially

Makes sense right?

But why would someone make its net assets negative?

Lets see.

Dividend Growth vs Rent Growth

Having a look to the growth of the dividends of a real estate stock, like NYSE:O.

#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?

Also, not all companies, sectors, do similarly:

I Created this chart thanks to the R/Stocks Project, which I talked about here.

Lets see the CAGR applied for dividend growth.

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.

$$ Annualized\ ROI = \left( \frac{Ending\ Value}{Beginning\ Value} \right)^{\frac{1}{n}} - 1 $$

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, ofc).

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 configuration of parameters that will make you place additional money from your pocket (in addition to the one you give initially, together with the bank to get the property).

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/

• https://www.idealista.com/sala-de-prensa/informes-precio-vivienda/alquiler/

• https://www.idealista.com/sala-de-prensa/informes-precio-vivienda/venta/

• https://www.idealista.com/sala-de-prensa/informes-precio-vivienda/alquiler/andalucia/sevilla-provincia/dos-hermanas/

• https://www.idealista.com/sala-de-prensa/informes-precio-vivienda/venta/andalucia/sevilla-provincia/dos-hermanas/

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 [Post]

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 these kind of conversations.

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).

In the end it all comes down to your personal situation.

Make sure to analyze yourself the figures and check with a professional, as there are several ways to get your ideas funded, and they will provide different conditions, as examples:

• https://www.caixabank.es/particular/hipotecas.html
• https://www.openbank.es/hipoteca-fija
• https://www.bancosantander.es/particulares/hipotecas/no-residentes
• https://www.ing.pl/indywidualni/kredyty-i-pozyczki/kredyt-hipoteczny/kalkulator-kredytowy

Its interesting to see options, like freezing your shares while you are returning the loan with a reduced interest rate.

Real Estate Tech

A quick streamlit app is always a great way to get started.

Real Estate Web

AI Ready Website for a RE Agency

Data Chat

Real Estate Agent with LLamaIndex and Mem0

This is how I have built a Streamlit web app with the real estate logic explained:

#ssh -T git@gitlab.com
#git clone git@gitlab.com:fossengineer1/py_stocks.git
git clone https://gitlab.com/fossengineer1/py_stocks

cd EDA_Mortage

python3 -m venv amortization_venv
source amortization_venv/bin/activate

pip install -r requirements.txt
streamlit run mortage_v4.py #streamlit app

docker image build -t py_mortage:v6 .

FROM python:3.12.3

WORKDIR /app

# Copy requirements first for caching - this is a best practice
COPY requirements.txt .
RUN pip install -r requirements.txt

# Copy only the necessary application files.  List them explicitly!
COPY mortage_v6a.py .
COPY mortage_plots.py .
COPY UDF.py .

EXPOSE 8501

CMD ["streamlit", "run", "mortage_v6a.py"]

docker run -d \
    --name py_mortage \
    -p 8509:8501 \
    -w /app \
    --restart always \
    py_mortage:v6 \
    streamlit run py_mortage_v4.py

#ifconfig eth0 | grep "inet " | awk '{ print $2 }' #if ETH Connected - SEE THE LOCAL IP

services:
  py_mortage_streamlit_app:
    image: py_mortage:v6a
    container_name: py_mortage
    ports:
      - "8509:8501"
    working_dir: /app
    restart: always
    networks: 
      - cloudflare_tunnel
    # command: streamlit run py_mortage_v6a.py  <-- Remove this line

networks:
   cloudflare_tunnel: #https://www.youtube.com/watch?v=k75PviBQeA0
     external: true    

#docker image build -t py_mortage:v4 .
#docker image build -t py_mortage:v4 -f ./Docker/Dockerfile .
#podman image build -t py_mortage:v4 -f ./Docker/Dockerfile .
docker exec -it py_mortage /bin/bash

docker run -p 8509:8501 py_mortage:v6a

https://realestate.jalcocertech.com/

Useful Concepts

1. Compund anual interest rate - Or how much something as grown or decreased YoY.
2. The rule of 72 - Or how many years for something to duplicate.

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% every year.

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 NYSE: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!

What if the Interest Change?

Maybe, you will get a fixed interest for the first 5Y, then, an estimation of what you will have to pay.

Im sure some of you have created an excel, which needs such interest value.

Probably with some condition on the month, to consider the initial or the changed interest.

But…what would that virtual, future interest be potentially?

# #cd EDA_Mortage
# #source mortage_venv/bin/activate #(linux)
# #python3 given_

import numpy_financial as npf

def calculate_interest_rate(loan_amount, monthly_payment, loan_term_months):
    """Calculates the annual interest rate.

    Args:
        loan_amount: The principal loan amount.
        monthly_payment: The monthly payment amount (should be negative).
        loan_term_months: The total number of months in the loan term.

    Returns:
        The annual interest rate (as a decimal), or None if an error occurs.
    """
    try:
        monthly_rate = npf.rate(loan_term_months, monthly_payment, loan_amount, fv=0) # fv=0 added
        annual_rate = monthly_rate * 12
        return annual_rate
    except Exception as e:
        print(f"Error calculating interest rate: {e}")
        return None

# Example usage:
loan_amount = 630000
monthly_payment = -3274
loan_term_months = 360

annual_interest_rate = calculate_interest_rate(loan_amount, monthly_payment, loan_term_months)

if annual_interest_rate is not None:
    percentage_rate = annual_interest_rate * 100
    print(f"The annual interest rate is approximately: {percentage_rate:.2f}%")

# Example of calculating monthly payment
def calculate_monthly_payment(loan_amount, annual_interest_rate, loan_term_months):
  try:
    monthly_rate = annual_interest_rate / 12
    monthly_payment = npf.pmt(monthly_rate, loan_term_months, loan_amount)
    return monthly_payment
  except Exception as e:
    print(f"Error calculating monthly payment: {e}")
    return None

loan_amount = 630000
annual_interest_rate = 0.08 # 8%
loan_term_months = 360

monthly_payment = calculate_monthly_payment(loan_amount, annual_interest_rate, loan_term_months)

if monthly_payment is not None:
  print(f"The monthly payment is approximately: {monthly_payment:.2f}")

Thanks To

Thanks to airbnb and idealista for the historical data figures!

• 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!

Getting Better at RE

• https://notebooklm.google.com/notebook/