calculate equivalent yearly interst rate on house purchased with a mortgage

Question

I've got 60k and I'm looking at buying a house for 145k. The bank offered me a 20 year loan of 100k at ~1.8% interest rate. After 20 years I would have repaid the bank around 16k, according to their loan offer.

I'm looking to calculate the equivalent return-of-investment per year I'd need to have if I wanted to make the same amount of money with a different investment, assuming I keep the house for X years and then sell it. The initial numbers don't matter too much, I'm more interested in the calculation.

I've seen several related questions but haven't found one that shows this calculation directly. Please excuse me if it's a duplicate.

Here's what I've got:

Buying the house costs around 160k (145k + 15k for closing costs). Property values typically go up ~2% a year. So at year X the house is worth 145*(1.02^X).

The rent from the house covers the loan, property taxes and regular maintenance, with a negligable amount left over. So after X years, I would have paid back 116*X/20 of the loan, therefore I'd have 116*(1-X/20) of it left to pay. That means after paying back the loan, I have

145*(1.02^X) - 116*(1-X/20)

left. My initial investment was 60k, so I'd have

[145*(1.02)^X - 116*(1-X/20)]/60 

times my initial investment. Assuming I want the same behavior from another investment (compounded yearly), I'd need

{[145*(1.02)^X - 116*(1-X/20)]/60 } ^ (1/X)

roi per year. I get these values for X = 5, 10, 15 and 20 years:

X = 5 => 1.04 => 4% yearly ROI
x = 10 => 1.07 => 7% yearly ROI
x = 15 => 1.07 => 7% yearly ROI
x = 20 => 1.066 => 6.6% yearly ROI

Is this calculation correct? What does it leave out that should be considered?

Edit: replaced "notary fees" with "closing costs" as per Ben Voigt's comment

Answer 1

You have one minor flaw:

So after X years, I would have paid back 116*X/20 of the loan,

Loan principal does not decrease linearly - you pay back very little principal at the beginning (since most of your payment in interest) and it accelerates as you pay it down.

Plus you don't "owe" 116k out of the gate. You only owe 100, so using 116*X/20 would be overstating the amount you owe.

You can use the excel function CUMPRINC to calculate how much principal you would have paid down in 5, 15, 15, and 20 years and see how much of the loan you've paid down.

For a hint, a 100k loan at 1.8% interest should have the following principal due at the end of each year:

 5: 78k
10: 54k
15: 28k
20:  0k

As you can see, the principal does not decrease linearly but is slower at the beginning (only 4,200 the first year) and accelerates (5,900 the last year). With a higher interest rate the difference is much more dramatic.

Answer 2

Here is how I would approach this. Taking the rates, 1.8% and 2%, as effective annual rates.

deposit  = 60000
fees     = 15000
house    = 145000
loan     = house + fees - deposit = 100000
loanrate = 1.8/100 = 0.018
monthlyloanrate = (1 + loanrate)^(1/12) - 1 = 0.00148777
numberofmonths  = 20*12 = 240

s = loan
r = monthlyloanrate
n = numberofmonths
d = monthlypayment

Standard loan equation

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

monthlypayment = 495.78

totalinterest = monthlypayment*numberofmonths - loan = 18987.20

This is obviously different from the 16k mentioned by the OP. It appears either the 16k is wrong or the rate is wrong because it's difficult to square 16k with 1.8% on 100k. So continuing with the calculation as shown.

appreciation = 2.0/100 = 0.02

This is what has been paid out after 5 years

paidout = deposit = 60000

What follows is the amount of the house paid for. (The bank still owns the rest.)

First, the accumulated principal paid down by month n is given by accpr(n) (detailed here)

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

e.g. after 20 years (n = 240) the full balance has been paid down.

accpr(20*12) = 100000

The amount paid out after 5 years is

paidforsofar = 45000 + accpr(5*12) = 66760.78

The value of this amount after appreciation

value = paidforsofar (1 + appreciation)^5 = 73709.30

The ROI for 5 years

roi = value/paidout = 1.22849

annualroi = roi^(1/5) - 1 = 0.0420157

Now for 20 years

paidout deposit = 60000
paidforsofar = 45000 + accpr(20*12)          = 145000
value = paidforsofar (1 + appreciation)^20   = 215462.37
roi = value/paidout        = 3.59105
annualroi = roi^(1/20) - 1 = 0.0660094

And 40 years, but this includes monthly rent payments added to the appreciated house value.

paidout = deposit = 60000
paidforsofar = 45000 + accpr(20*12)          = 145000
value = paidforsofar (1 + appreciation)^40   = 320165.75
roi = (value + 20*12*monthlypayment)/paidout = 7.31923
annualroi = roi^(1/40) - 1 = 0.0510216

Plotting over 50 years

Interestingly there is a sweet spot after 10 years for the annualised ROI.

This calculation doesn't include the effects of inflation, which would not be negligible. Rental income would also increase of course, and would elevate the ROI after the term of the mortgage.

Answer 3

I guess a realistic cash flow for home purchase should also include:

• Annual maintenance costs
• Depreciation due to deterioration
• Inflation
• Risks associated with bad tenants
• Insurance premiums paid
• Insurance $ received
• ....etc.