Saturday, 10 August 2019

Best Formula For Calculating Monthly Mortgage Payments?

Best Formula For Calculating Monthly Mortgage Payments?

Today I am going to show you how to calculate the mortgage payments based on the loan terms. Suggestions, principle borrowing amount, the interest rate and the loan link. 

Calculating a mortgage payment is kind of tricky if you think about it, because every single month when you pay into it, the principal amount reduces, so you end up paying a little bit less interest every single time. And at the same time, you pay a little bit more principal

To calculate the monthly payment and you want equal monthly payments to, throughout the whole term of your loan then that gets a little tricky, how the heck do you do it, you actually write out a spreadsheet and just do it line by line for 360 lines

There is actually a formula for this initially right here

Mortgage Payment Calculator

  • #200k Loan +P
  • 4% APR +R  r= R/12, n= 30x12 = 360
  • 30 Year Fixed:   
  • Monthly payment (1+r)^n

You can use the spreadsheet below where it will actually calculate it line by line and it would also give you the monthly payment based on the equation Mortgage Calculate Spreadsheet

If you just look at the equation there might be a little bit hard to decipher, so my purpose here is to just explain to you how this works so you can actually look at the equations and plug in the right numbers yourself.
  • For example, let say you have a $200,000 loan
  • You have a 4% APR
  • You have a 30-year fixed-rate loan

Now the interest on a mortgage payment does not compound every single day or continuously, it compounds every single month. So once you borrowed a loan, they calculate how much interest you earn one month later and then they add it to your principal, then they take out your payment which includes the interest and a little bit of the principal.

The thing to keep in mind here is that 4% APR is 4% over one year, in order to get the monthly interest that you have to pay, you divide that by 12 because there are 12 months in a year. Otherwise, you don't want to pay 4% every single month

If you have $200,000 you don't want to pay $8,000 every single month in terms of interest so you divide this down by 12, so "the big "R" in our equation is actually the interest rate divided by 12.

Now, because you have a 30-year mortgage, you are going to compound this 30 times 12 and 30x12 = 360

"P" is the $200,000 and you multiply it by little "r" which is 4% divided 12, so it is .04 divided 12, it is not 4 divided 12, because 4 is a percentage that is why you use .04.

Just check out the spreadsheet below for more understanding.

Mortgage Calculate Spreadsheet

If you want to look deeper into it and just kind of play around with maybe prepayments and stuff, you can go and download the Mortgage Calculate Spreadsheet above. Then you can change the principal amount of your loan, change the APR that you get, change to the how many year mortgages that you end up getting.

Then you can calculate your actual monthly payment.

No comments:

Post a Comment