Here's how much equity you actually own and your interest expense

[Adam Zacharski]

I've noticed reading through a lot of posts that when people are calculating their equity in a deal after a certain number of years they are not breaking out the expense portion of their monthly payments. They often take the mortgage payment x years and add that to their equity. Because mortgages are amortized to have an accurate idea of principal paid down you need to allocate the interest from your payments. The issue is that the amount paid to principal and interest changes for every single payment. Lucky there is an easy way to figure this out with either Excel or Google sheets. Just enter in the following formula and it will tell you what the remaining loan balance is at any point in time, with which you can subtract it from the original balance to see what you have paid off. 

The formula (future value calculation)

Numbers you'll need: 

->Original loan amount - ex. $100,000

->Interest rate - ex.  .10 for 10%

->Monthly loan payment amount -if you don't have this I'll show you how to calculate it below - ex. $1,074.61

->The loan balance date, viewed as how months you've paid into the loan - ex. 5 years = 5 x 12 payments = 60

Formula break down =fv([interest rate/12],[loan period you want to see],[monthly payment],[beginning loan balance as a negative #])

In action our formula would look like this =fv((.10/12),60,1074.61,-100000) 

Our answer would be $81,316.24 as the remaining loan balance after the 60th payment i.e. 5 years. This means that we have $18,683.76(100,000 - 81,316.24) in equity from the loan. The total payments for the time period have been $64,476.60(1,074.61x60) so we paid $45,792.84 in interest over the five years. If you didn't factor this in then your equity assumption would be off by the interest or 71% of your payments. 

If you don't have the monthly payment number you can calculate it like this: 

The formula (payment function)

Numbers you'll need:

->Original loan amount - ex. $100,000

->Interest rate - ex. 10% or .10

->Loan term in numbers of months ex. 15 year = 15 x 12 =180

Formula break down =pmt([interest rate/12],[loan term],[beginning loan balance as a negative #])

In action our formula would look like this =fv((.10/12),180,-100000)