I construct a spreadsheet to investigate when is the best time to pay off a house loan.

In my model, there are two factors at work:

  1. The real estate appreciation rate, in this case I set it to 5%
  2. The Interest rate charged on the mortgage loan, I set it to 4%

and I assume that the rental income collected is exactly the same as the annuity monthly payment and used to offset it, to avoid complications in terms of compounding interest charge on the mortgage loan.

Assuming 30 years mortgage, when is the best time to pay of the house loan given the above information?

The formula for compound gain, r:

(1+r)^n = Total amount after selling/Total amount invested

Solve for r

Given that the house is appreciating faster than the interest rate ( 5% vs. 4%), and given that in the beginning of the mortgage, most of the payment goes to the interest ( very little equity accrued), I would expect that the longer into the mortgage years, the higher the compound gain ( in % terms). So the maximum compound gain should occur at the end of mortgage loan, in this case it is 30 years.

But this is not what I found; instead, I found that the maximum compound gain occurs at somewhere between around year 5.

enter image description here

Playing around with the interest rate, downpayment, appreciation rate and other variables confirms one thing, that maximum compound gain will not always occur when the mortgage matures at 30 years. Instead, it's occurring somewhere in the middle, from year 5 to year 20 quite frequently.

It seems that there is a time to settle the full house loan, and it's not at the end of mortgage term, despite that the house appreciates faster than interest rates on the loan.

I've no idea why this is so, any explanation?


  1. Some object that the appreciation rate is higher than inflation/mortgage loan rate, but this is besides the point. If anything, higher appreciation rate should mean that the the maximum compound gain should happen at the end of mortgage. But it's not, and this is why I'm asking the question.
  2. Even if you set the appreciation rate to be the same as mortgage loan rate, the same conclusion still applies in some situation, eg: selling charge=0%, and annual interest=annual appreciation=4%.
  3. I'm aware that I'm ignoring the tax, and do a few simplifications. But I am not looking forward to invest in real estate just based on this spreadsheet. I do the spreadsheet ( a toy example) for the purpose of pointing out a puzzling observation
  4. Even when the rent is inflation-adjusted, and the management fees and gain tax is included, the same behavior persists.


  1. A bug regarding the house price wasn't properly fixed for columns other than the first one in the spreadsheet is fixed. The original conclusion ( namely, the question still stands)
  2. Another bug regarding the mortgage payment is deducted from final gain is fixed ( it shouldn't, as it's the tenant who pays for the mortgage payment in this contrived example). After fixing, the my results and the answer here results are now the same. But still, the behavior persists.
  • What's your formula for compound gain? I can't figure out why you have a 69% loss after one year.
    – D Stanley
    Feb 7, 2019 at 14:36
  • 2
    Your house price calculation is flawed, for year 1 it looks like the house value less cost to sell, but year 2 it looks like just the house value. ex: 250,000 at 5% for 1 year is ~261,000 and 5 years is ~320,000.
    – Hart CO
    Feb 7, 2019 at 16:51
  • Rental income makes this much more complex especially if there is depreciation and recapture. Feb 7, 2019 at 17:26
  • 5% annual appreciation for 30 years seems wildly optimistic.
    – chepner
    Feb 7, 2019 at 20:05
  • 1
    @Graviton Look again at your formula's, my first comment stands, you're either not applying the 8% selling cost properly for all years or not calculating the 5% growth correctly.
    – Hart CO
    Feb 8, 2019 at 1:13

4 Answers 4


What you are not considering here is the financial leveraging of the equity-based gain. Leveraging refers to the fact that using loaned money to invest, increases the punch of your equity dollars. As long as your earnings are higher than the interest you pay on the loan, you end up with a higher rate of return.

Assume I pay $100k for a house, in cash, and then next year I sell it for $105k. In this case, I gain $5k (5%).

Now assume I pay $200k for a larger house, with $100k of my own cash, and $100k loaned from the bank at 4% interest, and then next year I sell it for $210k after paying the bank $4k in interest. In this case, I take my $206k after-interest proceeds, and repay the bank their $100k, leaving me with $106k. Considering that I used $100k of my own cash, my gain of $6k is 6% - which is a higher % gain than in the first option, even though in both cases, the house increased in value by the same appreciation rate of 5%.

The reason you are seeing the pattern shown in your example, is that as time goes on, your equity increases, and therefore the impact of leveraging decreases. I haven't gone through your numbers in detail to check your math, but it seems relatively reasonable. Consider that your return shown in year 3 of 15% is higher than the appreciation rate on the house, which is 8%. If you had 0 debt, your return would track closely down to this 8% figure. As you approach 0 debt, this is the effect you see.

There may be some other things going on with your other factors, but that would be the main one.

Now you may be asking: [this is not a hypothetical; in the comments, you did ask] "How can this be because of equity? I don't have an equity calculation in my spreadsheet"

Well really, you do have 'equity' in your spreadsheet. Equity is calculated in your spreadsheet as proceeds - final mortgage payment = net proceeds = equity.

As well, let me point out the 'other side' of leverage: risk. As your debt goes up, your return on 'equity' goes up, but your risk also goes up. The higher the debt, the more you absolutely need your rental income to cover your mortgage payments, and the greater the chance that a market downturn will leave you needing to sell the house and still pay up additional cash after proceeds to clear out your mortgage.

As Joe Taxpayer points out below, the optimism in your spreadsheet [that the value of the property will increase 5% / year without fail] must be removed for you to make a reasonable decision. If you change that 5% to 4%, how long do you need to own the house before it can be sold for no net loss? If you change it to 2.5%, how long? That type of scenario analysis will show you the difference between an optimistic and a pessimistic scenario. You should be considering both.

  • 2
    Yes, a +1 answer. What scares me is that OP is about to use this math to possibly buy a rental. A 5% year on year projected gain is a recipe for disaster. You know this, so I don't need to share the rest of the recipe. Feb 7, 2019 at 20:01
  • 2
    On average, long term, real estate will not exceed wage growth. It literally can't. It can do it for brief periods, and in limited areas for a long time, but not for the long term. Better to assume 2% and laugh at me in 30 years, than 5% and be under water on an investment. Feb 8, 2019 at 1:13
  • 2
    Consider, after 30 year of 3% inflation, we are at 243%, i.e. 2.43X times original price. At 5%, 432% or 4.32X times. So, income tracked inflation and went up 2.43X, but homes went up over 4 fold? That can't last for long. Feb 8, 2019 at 1:33
  • 1
    Well, in my example, the monthly payment rises from say 25% of income to well over 50. It’s safe to say that one can’t spent more than 100% of their income on a house. Feb 8, 2019 at 2:52
  • 2
    @Graviton - The fact that you ask this question for a rental property makes such an assumption pretty alarming. And we run the risk a future member takes such an assumption as historical fact. The 'fact' is that, long term, the inflation-adjusted increase on real estate is..... zero. Feb 8, 2019 at 12:10

Running through the calculations

s = 225000
p = 25000
r = 0.04/12
n = 30*12

d = r (1 + 1/((1 + r)^n - 1)) s = 1074.18

paid[y]    = 12 y d
value[y]   = (s + p) (1 - 0.08) (1 + 0.05)^y
balance[y] = (d + (1 + r)^(12 y) (r s - d))/r
gain[y]    = value[y] - balance[y] - paid[y]
annret[y]  = 100 ((gain[y]/p)^(1/y) - 1)

Plot of Value, Balance & Gain

enter image description here

Plot of Annualised Return

enter image description here

annret[15] = 12.1481

This is the same as your result, so I understand your calculation.

The maximum return is just after year 17.

annret[17] = 12.222

However, I don't see why gain = value - balance - paid.

You are investing the downpayment of 25000, then the investment ticks along without further investment because the tenant pays the mortgage. At any point during the term of the mortgage, if you were to sell, the tenant leaves and you have the remaining balance on the mortgage and the value of the property, so the net asset value nav = value - balance.

The return on this is nav/downpayment - 1

value[y]   = (s + p) (1 - 0.08) (1 + 0.05)^y
balance[y] = (d + (1 + r)^(12 y) (r s - d))/r
nav[y]     = value[y] - Max[balance[y], 0]
ret[y]     = 100 ((nav[y]/p - 1))
annret[y]  = 100 ((nav[y]/p)^(1/y) - 1)

enter image description here

The maximum annualised return occurs just before year 4.

If you sold at year 4 the nav = 279566.44 - 208155.51 = 71410.93

and the return is 71410.93/25000 - 1 = 185.644 %

which annualised is (1 + 1.85644)^(1/4) - 1 = 30.0038 %

On the other hand,

if you sold in year 5 the nav = 293544.76 - 203506.90 = 90037.85

and the return is 90037.85/25000 - 1 = 260.151 %

which annualised is (1 + 2.60151)^(1/5) - 1 = 29.2103 %


The best annualised return from this investment occurs around year 4, at 30%. If you could switch to another investment at year 4 that returned 30% or more that would make sense.

The compounded 30% curve shows that the actual investment returns curve is less steep and cannot keep track to sustain this 30% annualised return.

As to why the annualised returns curve has its shape, note that if the selling cost is set to zero then the returns curve starts at zero too, and according to the resulting annualised returns curve you should sell as soon as possible.

E.g. with cost set to zero, selling in year 1 obtains a return (also annualised) of 65%.

value[1]   = 262500
balance[1] = 221037.67
nav[1]     = 262500 - 221037.67 = 41462.33
ret[1]     = 41462.33/25000 - 1 = 65.8493 %

enter image description here

It it the selling cost that pulls the returns negative at the start, and which gives the annualised returns curve its ascending peak at the beginning. The returns curve in the figure above starts at zero and the annualised returns curve has no ascending portion.

Further Explanation

The OP asks

It seems that there is a time to settle the full house loan, and it's not at the end of mortgage term, despite that the house appreciates faster than interest rates on the loan.

I've no idea why this is so, any explanation?

It seems odd that you refer to the annualised return as 'compound gain' whereas I would refer to the return as the compounded quantity, and the annualised return as the normalised quantity (i.e. not compounded). I don't know if that is a source of confusion. I will continue with both nomenclatures.

Consider how you are calculating your return and compound gain (annualised return), say for year 5 (the red dots).

return = total gain/downpayment - 1 = 90037.85/25000 - 1     = 260.15 %
compound gain or annualised return  = (1 + 2.6015)^(1/5) - 1 =  29.21 %

and for year 20, the green dots

return = total gain/downpayment - 1 = 504161.09/25000 - 1      = 1916.64 %
compound gain or annualised return  = (1 + 19.1664)^(1/20) - 1 =   16.21 %

You will see that this 20 year return, 1916.64%, is higher than the 20 year compounded return from the estate, at 165.33% (the left purple dot).

compound return on the estate = (1 + 0.05)^20 - 1 = 165.33%

Annualising both results produces 16.21% versus 5%.

The extra return in the investment over the basic appreciation is due to your gaining ownership of the property itself, from the rent from the tenant. In time, the annualised return approaches the appreciation figure. I extended the time axis to 120 years to show this.

enter image description here

  • Ah thanks. But even if this is corrected , the main conclusion still stands right ? I will correct the formula later as I am now outside
    – Graviton
    Feb 8, 2019 at 11:40
  • I've updated and corrected the spreadsheet, and the max. compounded gain still not occuring at the end of the mortgage period
    – Graviton
    Feb 8, 2019 at 13:12
  • Thanks for confirming my calculation... but your conclusion is?
    – Graviton
    Feb 9, 2019 at 0:23
  • 1
    @JoeTaxpayer Ok, I see. The mortgage is paid by the tenant. The owner's only investment is the downpayment. Feb 10, 2019 at 12:34
  • 1
    Year 30. House Price = $994K. Why is the gain only $607K? Haven't you stated the only investment you wish to consider is the $25K? Feb 11, 2019 at 10:19

Note: The part about the incorrect formula being used for house price has been fixed and that part of the answer has been moved to the end.

I don't agree with your calculation of total gain. You calculate gain as

Total gain = House Price - mortgage payoff amount - mortgage paid.

which for year 1 is $7,572.12; Then for your two you calculate it as $10,880.67.

Your math is correct but your logic isn't clear.

The amount of money you are paying via the mortgage is not important. You are assuming in the question:

I assume that the rental income collected is exactly the same as the annuity monthly payment and used to offset it, to avoid complications in terms of compounding interest charge on the mortgage loan.

You are ignoring depreciation while you are renting it out, you are ignoring taxes, and your are ignoring recapture; yet you include the amount of the monthly payments. You are also ignoring mortgage insurance, and the typical lender requirement of 20% down.

I would just subtract payoff amount from house price. So after a year you would have 20,462.33 in cash returned to you, which you spent 25K via your down payment. So there is a loss.

If you keep your formulas then you must of ~1.96% annual appreciation to have positive compound growth by year 30. As you increase the annual appreciation the time to get to positive compound growth grows shorter.

Appreciation     breakeven year    Best Year
1.96%            30                30
2.00%            30                30
2.50%            24                30
3.00%            17                30
3.50%            12                30
4.00%             9                26
4.50%             7                21
5.00%             5                17
5.50%             4                14 
6.00%             4                12

using the simpler gain = price-payoff

the numbers change as follows

Appreciation     breakeven year    Best Year
-7.13%            30                30
 0.00%             5                16
 2.00%             3                 7
 2.50%             3                 6
 3.00%             2                 5
 3.50%             2                 5
 4.00%             2                 5
 4.50%             2                 4
 5.00%             2                 4
 5.50%             2                 4 
 6.00%             2                 3

The higher the growth of the value of the house the sooner that compound growth occurs. I am just not sure what either formula is designed to measure. It is driven mostly by the annual growth of the house and the amount you put down

This part discusses a formula which has been corrected. The equation for house price is wrong.

For year 1 you have it as

250,000*(1.05)*.92=241,500. That is correct.

But for year two you have it as:


It should be 253,575. you dropped the 8% selling charge.

Another way to calculate it is 1.05 times the year 1 number which also gives you 253,575.

  • 1. the spreadsheet is now corrected, yet the question still remains valid. 2. I also do subtract payoff amount from house price. 3. Total gain = House Price - mortgage payoff amount - mortgage paid. What is wrong with this formula that you don't understand? Or would you propose to correct it?
    – Graviton
    Feb 8, 2019 at 13:14
  • @Graviton The 'mortgage paid', countered by the mortgage interest, is what has resulted in the 'mortgage payoff amount', which is the mortgage balance remaining. It is superfluous to subtract it to obtain the net asset value, which is house price - mortgage balance. Feb 10, 2019 at 22:23

TL:DR The behavior is an artifact of the OP ignoring the fact that rent pays the mortgage. When the math is done correctly, it goes away once the cost of sales (8%) is covered.

I am viewing the spreadsheet and made a copy so I can edit it.

  1. For trouble shooting purposes, I set the selling fee and appreciation equal to zero. Given your scenario where we ignore any other costs, and assume the rent covers them and the mortgage, you have invested $25K, and 30 years hence, have a paid off property, worth $250K. Your spreadsheet shows a loss of $137K, i.e. all the interest for 30 years. This scenario, however unlikely, returned 10X your investment, or 7.98%/yr.
  2. When I adjust profit to show as the return on your initial $25K, and crank the appreciation to 5%, the results are more accurate. A higher % return in year one, and a steady drop from there.
  3. Last, and final. Putting the 8% cost of sale back in, the return peaks in year 3, as part of year 2 growth is still needed to offset that cost.

For the above, note, I am not debating the numbers, per se, only trying understand the choice of considering the principal paid by the tenant as a cost.

In the end, your conclusion of some peak return is based on a false premise, you are ignoring the fact that the tenant should be paying at least the mortgage, and ideally, all expenses as well. You are treating the mortgage principal payment as an expense, and keeping the rent flat for the full 30 years. This makes no sense.

How else do you explain selling a house for nearly $1M yet doing the math on a gain of only $600K? (Taxes ignored. Except for $25K you put in, all is a gain)

enter image description here

Once the gain* from principal paydown is taken into account, you can easily see the returns are greatest in the first year after the cost to sell (8%) is exceeded. The behavior we were trying to understand was the result of a spreadsheet error.

  • The word 'gain' is used here just to address the benefit to OP's bottom line, the entire exercise ignores true numbers. After 30 years of having a tenant pay his mortgage, he now owns the house outright. Of course, for tax purposes, this does not apply.
  • Your screenshot doesn't tally with my spreadsheet. How do you get to that? And how do you propose to correct the calculation? Can show us your spreadsheet?
    – Graviton
    Feb 11, 2019 at 8:02
  • Cell E24 is "=E23/25000" and E25 is "=E24^(1/E13)" Of course, to pretty it up, I should have subtracted 1 from % CAGR after taking the Nth root. Note. I am not always right, but when member Chris Degnen verifies my math, my confidence level jumps to 100%. Feb 11, 2019 at 10:27
  • 1
    JoeTaxpayer's results match mine, with the maximum CAGR of 30% in year 4. (Expressed as 1.30003837 in the spreasheet screenshot.) Feb 11, 2019 at 10:28
  • @JoeTaxpayer, now my calculation tallies with yours and Chris Degnen after correcting for the mistake.
    – Graviton
    Feb 12, 2019 at 9:30
  • @JoeTaxpayer, I have another spreadsheet that takes into inflation-adjusted gain rents and some miscellaneous rental-related fees into account, still, the curve is there. So the steady drop of the compound gain is not due to "spreadsheet error", but due to some other reasons
    – Graviton
    Feb 12, 2019 at 9:32

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .