Do bank use the same math formula to calculate the mortgage amortization table?

Question

I've started making a spreadsheet to evaluate mortgages and how they would fit other financial plans. The most complicated part seems to be the mortgage calculation formula. That's the formula the banks use to create the mortgage amortization table and give you the overall summary of the mortgage.

Does anyone know if this formula is worth pursuing in a spreadsheet or things vary¹ too much between banks to get a generalized solution?

If the answer to the above is yes, please provide an example of it.

---

¹ I know the interests and input parameters will vary between banks, but am referring to the formula itself

Answer 1

Banks don't necessarily use the same formula, but in most countries they must disclose the effective interest you'd be paying (which may vary from the nominal interest due to extra charges and calculation differences) and explain how your payment is calculated.

In some countries banks are required to precalculate and provide the amortization schedule for the loan.

For loans with fixed interest and fixed monthly payment (these are common for mortgages and car loans in the US, for example), you'll usually see the standardized annuity formula used for calculation. This formula also exists as a built-in function in Microsoft Excel and Google Sheets, and any other similar software.

This may vary from country to country, so you should check with your local regulatory authority (the central bank, usually, or CFPB or FTC in the US) for details.

Answer 2

The mathematics on which the usual formula is based is that the sum of the payments d, each discounted to present value (PV) by 1/(1 + r)^k, should equal the initial (present value) value of the loan s.

The summation can be converted to a formula by induction, so

r is the periodic interest rate, so if the APR is a nominal annual rate compounded monthly r = APR/12.

If the APR is an effective annual rate use r = (1 + APR)^(1/12) - 1 to obtain the monthly rate.

An expression can be obtained for the periodic payment d

  s = (d - d (1 + r)^-n)/r
∴ d = r s (1 + 1/((1 + r)^n - 1))

See also Calculating the Present Value of an Ordinary Annuity where they show

Their example applied to the formula for d

s = 4329.58
r = 0.05
n = 5
d = r s (1 + 1/((1 + r)^n - 1)) = 1000

Likewise in Excel =PMT(0.05, 5, 4329.58)

The interim periodic balances can be obtained with this formula

p(x) = (d + (1 + r)^x (r s - d))/r

where x is the period number, i.e.

p(0) = (d + (1 + r)^0 (r s - d))/r = 4329.58
p(1) = (d + (1 + r)^1 (r s - d))/r = 3546.04
p(2) = (d + (1 + r)^2 (r s - d))/r = 2723.31
p(3) = (d + (1 + r)^3 (r s - d))/r = 1859.45
p(4) = (d + (1 + r)^4 (r s - d))/r =  952.40
p(5) = (d + (1 + r)^5 (r s - d))/r =    0

Answer 3

I had a mortgage in the UK starting in 1990 which did not follow the standard formula. 12 months' interest on the outstanding balance was debited to the account at the anniversary date and I had to write my own formula.

Answer 4

If you prefer to have your own spreadsheet and you're using Excel for a standard 12 monthly payments per year, the formula for monthly payment is:

=ROUND(LoanAmount/((1-((1+InterestRate/12)^-(Years*12)))/(InterestRate/12)),2)

For a reality check, if --

LoanAmount = $100,000 and InterestRate = .05 or 5% and Years = 30 then the monthly payment is $536.82.

For further embellishment, if you'd like to know how much of a payment is interest and how much is principal, compute (InterestRate/12) times the remaining balance of principal. Using the above example, the first payment includes $416.67 in interest, with the rest, $120.15, as principal.

For the second payment, the remaining balance of principal has been reduced by $120.15, so the second payment is $416.17 interest, $120.66 principal. And so on.

Have fun!

Answer 5

Does anyone know if this formula is worth pursuing in a spreadsheet or things vary too much between banks to get a generalized solution?

Others have noted that in many cases, there's enough consistency to use a spreadsheet or even a simple direct (closed form equation) calculation. But this is not true for all cases.

In particular, be aware that variable rate mortgages and loans are much more varied than fixed-rate ones. Here a spreadsheet may not suffice.

You can sometimes benefit from splitting a loan into multiple parts. In the US, at least, though, it's common for secondary loans (e.g., a 2nd mortgage) to have significantly worse terms than primary loans (the first mortgage), even if both loans are fixed-rate. (This happens for numerous reasons, including the fact that the primary mortgage holder is first in line in the case of a default.)

For my first house, in the 1990s, I went with an "80-10-10" plan: I put 10% down, got a conventional 80% mortgage at the then-great-rate of around 8%, and a 10% secondary mortgage at a double-digit rate, though I forget now what it was (11 to 12% perhaps?). The secondary mortgage used a daily interest accrual based on the date they received payment, so that the amount that went to principal vs the amount that went to interest was unpredictable. Remember that at this time all payments went via US mail, with variable delay in the postal system. I made extra principal payments and these were frequently somehow—"accidentally" of course—credited as interest prepayments, requiring followups with the bank to correct this. I paid this secondary mortgage off as quickly as possible. This arrangement also allowed avoiding PMI payments,¹ and given that I expected to be able to pay off the secondary mortgage within a few years, saved quite a bit overall.

(Due to an overall housing-market rise and changes in interest rates, I was able to refinance into a substantially lower-rate fixed-rate mortgage within a few years as well. That would probably have been roughly equivalent, but that was not knowable at the time I got the 80-10-10 arrangement.)

---

¹PMI, or Private Mortgage Insurance, are payments you make on your mortgage for insurance that covers the lender's loss in case you default. The exact details get complicated here; see the link if you're in the US.