Recently, I have been doing calculations to help myself decide what a good deal would be on a few different homes. While calculating total return on investment I noticed my calculation of total equity accrued in one year is different than several examples I have found online. I was under the impression it would simply be the mortgage amount x 12 months. Can someone tell me if I am missing something in my equity calculation for a given year?
Your mortgage payments include interest, your financial equity is the amount of principal reduction as amortized.
The only way to establish the true equity at a point in time is to sell the property, everyting else is accounting or an estimate.
Most of the mortgage payment on the first 10 (or 20) years of a 30 year loan is interest. Only the principle portion of the payment accumulates toward equity.
To calculate first year pay down in Excel:
Fill in cell A1 with the loan amount
Cell A2 with the term in years
Cell A3 with the annual rate (not APR, just the rate)
In cell A4 put =-PMT(A3/12,A2*12,A1)
A4 will be your P&I payment. We don't care about taxes or insurance for this calculation
Now, you need to determine the balance of the loan at some point in the future.
In cell a5 put 1 to calculate the balance after one year
in cell a6 put =-FV(A3/12,A5*12,-A4,A1)
Thats the "future value" function. You use it to calculate the loan balance in the future. In this case, after the number of years from cell a5.
Now, in a7 put =A1-A6
This is the reduction in loan balance, which is the contribution to equity. The good news is this amount increases every year. But only very slowly.
Here's the results from my sample spreadsheet:
$100,000 initial balance
30 term, years
5% rate, annual
$536.82 P&I payment
1 years in future
$98,524.63 future loan balance
$1,475.37 equity gained
For grins I also calculated:
Total of the P&I payment for one year $6,441.86
Interest paid in the first year $4,966.49
Since we're at this point, keep in mind only the interest is deductible on your taxes, not the entire payment. Well, taxes and insurance are, too, only principle is not.
If you want to calculate the equity in some future year, use the FV function twice. Once to calculate the loan at the start of some year, then for one year later. The difference is the equity for that year.
Your mortgage payment is debt. Equity is released, if you will, by the function of making a mortgage payment that includes interest and principal. The amount of principal freed up is related to how your loan amortizes. So if the loan is interest only making a payment does not free any equity. Excel has some templates for loans you can use to look at these numbers if your not familiar with the math.
There might be some other miscalculations you are going through. if you want post an example and I am sure some folks will help square you away.
I can't thank you enough for that assessment. I use Excel a lot for work and I thought I was at the least a novice. I need to learn more functions though. After reading how essential they are to some investors on here is one thing but to see it explained so clearly will make me refocus on calculating things better.
I thank everyone for their responses as well. I found each one helpful.
Create Lasting Wealth Through Real Estate
Join the millions of people achieving financial freedom through the power of real estate investing