Calculating Equity

[Jarred S.]

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?

[Jon Holdman]

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.