Formula for calculating mortgage principal over time
November 10, 2011 6:20 PM   Subscribe

Financial maths experts, mortgage brokers, bank employees: I need a formula for calculating expected mortgage principal owing at a given sale date, please. (Explanation inside.)

A person buys a property on date {Purchase Date} for an agreed price of {Purchase Price}, with a deposit of {Purchase Deposit}.

He/she takes out a mortgage for {Purchase Date Principal} for (approximately) the difference between {Purchase Price} and {Purchase Deposit}. These amounts are immediately paid to the vendor who then pays out his/her own mortgage, pays real estate agents and lawyers, etc.

The mortgage has an average annual interest rate of {Interest} over its lifespan, which is normally expressed as a percentage eg 7.75% and can be assumed to be compounded daily (365.24 days per annum). It is usually paid monthly at a minimum amount of {Repayment}, which should be calculable although I am unsure how exactly it is calculated. (Bonus question: how does that work?)

As time goes by the person presumably reduces the principal and thereby gains equity. Assuming they sold the house with a {Sale Date} of the next day for the same price they paid it, this would be approximately equal to {Deposit} (ignoring early termination and real estate agent fees).

However if {Sale Date} was ten years later and it sold for the same price, the equity would be the difference between the sale price and the principal of the mortgage at that time {Sale Date Principal}. If it were an interest-only loan, or set up in such a way (as I believe they commonly are) as to have reduction of the principal only start happening after a very long period of time, this could potentially end up being not much more than {Purchase Deposit} and if conditions were bad enough in that area, could be a lot less and even a negative figure (being under water on the mortgage).

I'd like a formula for estimating {Sale Date Principal}s, please. In practice the person may make more than minimum payments, redraw from equity, take out a second mortgage, etc - ignore these for this purpose. If they paid {Price} including a {Purchase Deposit} and a mortgage for the difference {Purchase Date Principal}, and just quietly paid each month and didn't mess around with their mortgage in any way, how much is {Sale Date Principal} as at {Sale Date}?
posted by aeschenkarnos to Work & Money (3 answers total) 1 user marked this as a favorite
Do you want to program this for other people to use, or are you just looking to run some numbers yourself? Because there are about a billion online amortization calculators that will do this -- you just need to pick one of the ones that shows a detailed schedule of payments and look at the breakdown to find the total principal paid by year 10. Personally, I like this one, and it'll even show you the year 10 totals in its handy chart.

If you want to do the math yourself, the wikipedia page on loan amortization actually gives a rundown of what's required.
posted by jacquilynne at 6:46 PM on November 10, 2011

It is usually paid monthly at a minimum amount of {Repayment}, which should be calculable although I am unsure how exactly it is calculated. (Bonus question: how does that work?)
posted by kithrater at 6:58 PM on November 10, 2011

Thanks folks. Amortization is the word that was missing from my vocabulary. :)
posted by aeschenkarnos at 7:45 PM on November 10, 2011

« Older Brownie just won't cut it!   |   Will Skyrim run on my machine? Newer »
This thread is closed to new comments.