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.
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 ...
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:
For a reality check, if --
LoanAmount = $100,000 and InterestRate = .05 or 5% and Years = 30 then the monthly payment is $536.82.
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 = ...
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 ...