[Summary of this article]
-
I simulated real estate investment in Excel, and the difference from the actual amount after 2 years was 50,000 yen, showing that I was able to create a highly accurate simulation.
-
The three lessons learned are: I underestimated the vacancy rate, I did not include the assumption that interest rates would change, and the lack of precision in predicting repair costs.
Hello! I am Mage (@mage_log), a condo investment investor.
Two years ago, I bought one used condo in Tokyo.
When buying a condo for investment, you usually get a calculation sheet from a salesperson, right?
“You can save this much on taxes.”
“Even if you sell it for the same price you bought it, you will make this much profit.”
They skillfully present the benefits of real estate investment.
However, I had doubts, “Is this simulation really correct?”
I used Excel a lot for work, so I was reasonably confident in my Excel skills.
So, I created a simulation sheet in Excel and calculated how much would remain each month and how much cash would be left over when selling.
I am currently in the process of selling, and I have verified whether my simulation from two years ago was correct or if it was significantly off.
So, this time, I will summarize the details of the simulation results, the lessons learned, and the points to apply in the future.
Excel simulation created before buying
Before purchasing the real estate, I performed a simulation in two stages.
-
Cash remaining after subtracting expenses that definitely occur from rent (management fees, repair reserve funds, management consignment fees, fixed asset taxes)
-
Cash remaining after also subtracting expenses that occur unexpectedly (vacancies, restoration, tenant acquisition costs, etc.)
The calculation sheet I received only went up to 1.
However, in reality, there are several expenses that can occur if you are unlucky, such as vacancies or repair costs when equipment like air conditioners breaks down.
Therefore, I considered the best-case scenario where no vacancies occur and nothing breaks as ‘1’, and the worst-case scenario where vacancies occur and things break as ‘2’.
Detailed Comparison
The projected cumulative cash flow after 2 years is as follows.
-
If no vacancies occur: -498,000 yen
-
If vacancies and repairs occur: -843,000 yen
However, the fact that the difference stayed within 50,000 yen is not because my calculations were correct.
There were two sources of income I had not included in my projections.
These were the 149,000 yen in short-term cancellation penalties received upon move-out, and 126,500 yen in cleaning fees. That totals 276,000 yen.
On the other hand, restoration costs and tenant acquisition fees for two move-outs exceeded my projections by over 210,000 yen.
Rent was also short by 430,000 yen due to 4 months of vacancy.
In other words, I was saved by ‘unforeseen income’ and just
happened to land on a figure close to my projection.
The assumptions I had set were actually off.
Comparison of Actual Figures After 2 Years
So, what actually happened?
-
Up to September 2026: -956,000 yen
-
Projected landing in December 2026: -893,000 yen
Compared to the projected -843,000 yen, the margin of error is 50,000 yen.
Since it is a 50,000 yen difference out of about 8.2 million yen that flowed in and out over two years, I think the simulation was quite accurate, don’t you?
I am genuinely happy about this.
However, the out-of-pocket expenses were slightly higher than the worst-case scenario.
I also feel the scariness of real estate investment at the same time.
Details of the actual amount comparison after 2 years
The details are as shown in the image above.
As you can see from this image, the fact that the difference stayed within 50,000 yen is not because the calculation was correct.
There were two sources of income that I had not included in the forecast.
These were the 149,000 yen in short-term cancellation penalties and 126,500 yen in cleaning fees that came in when the tenants moved out.
On the other hand, the restoration and tenant acquisition costs for two move-outs exceeded the forecast by over 210,000 yen.
Rent was also short by 430,000 yen due to 4 months of vacancy.
In other words, I was saved by “unforeseen income” and just happened to land on a figure close to the estimate.
The assumptions I made were off.
Now, I will summarize the bad points and good points of the simulation.
Points where the simulation was off
1. I underestimated vacancies
In Excel, I set it to “1 month of vacancy every 2 years,” but in reality, it was vacant for 4 months in 2 years.
There were two move-outs…
Although there was the benefit of being able to raise the rent thanks to the move-outs, each move-out resulted in a cash outflow of 400,000 to 500,000 yen.
The vacancy period was 2 months both times. The vacancy period was tough.
I think there was also an aspect of bad luck.
However, from next time, I have decided to calculate it as 2 months in 2 years.
Under these assumptions, the forecast is -980,000 yen, which I think is a good simulation for a worst-case scenario.
2. I did not include the assumption that interest rates would change.
The interest rate rose from 1.65% to 2.25%.
Even if interest rates rise, the repayment amount does not change for a certain period.
The bank I borrowed from has a “5-year rule,” so even if the interest rate changes, the repayment amount remains the same for 5 years.
What changes is the composition; the proportion of interest within the repayment amount increases.
In other words, while the monthly cash flow does not decrease, the reduction of the principal slows down.
This does not affect the balance while holding the property, but it does have an impact when selling.
Calculating the remaining debt after an interest rate change in Excel is complex, and I didn’t do it at the time, but this definitely needs to be rebuilt.
3. I was averaging equipment repairs on an annual basis.
I assumed repair costs would be 500,000 yen over 15 years, so in the simulation, I set it to accrue 2,500 yen per month.
However, in reality, an air conditioner broke within two years, costing 100,000 yen.
A simulation based on current averages isn’t bad, but those buying properties over 10 years old should be careful, as those averages tend to cluster in the first few years.
Things the simulation got right
1. Air conditioner replacement cost
I had written, “Replace every 10-15 years, 70,000-100,000 yen.”
It was actually replaced at 13 years old, and the cost incurred was 100,000 yen.
It was just as expected.
As for other forecasts, I have written down 120,000 yen for a water heater, 75,000 yen for a gas stove, and 100,000 yen for a bathroom dryer.
These are expected to come up in order.
2. Not including key money and renewal fees in the calculations
In Excel, I set both key money and renewal fees to zero.
I haven’t received either even once in two years.
It was the right decision not to count on them.
Summary
What did you think?
If you are planning to run your own simulations, please use my lessons learned here as a reference!
Thank you for reading until the end.
I’ve also started on X and Threads, so please follow me!
Threads: https://www.threads.com/@mage.log
