I'm helping a friend to create a mortgage calculator with constant payment rate but variable proportion of amortization and interest during the life of the mortgage. Based on the yearly values of the interest rate, $i$, (i.e. the cost of renting the money), and the amortization rate, $a$, (i.e. the speed at which the mortgage is paid back), the I derived a formula for the interest, amortization and remaining debt values. The formulae that I came up with are the following:
- Interest, Amortization and Remaining debt at $n=0$: $$I_0=A_0=0\quad R_0=P$$ where $P$ is the amount of the mortgage
- Rate (pro annum): $$r_{pa}=R_0(a+i)$$
- Interest, Amortization and Remaining debt at year n: $$ \begin{align} I_n&=R_0\cdot[(a+i)-a(1+i)^{n-1}]\\ A_n&=R_0\cdot a(i+1)^{n-1}\\ R_n&=R_0\cdot\left(1-\frac{a}{i}[(1+i)^n-1]\right) \end{align} $$
which work perfectly for yearly calculations.
For monthly calculations, which the rate would be then $r_{pm}=R_0\frac{(a+i)}{12}$, I simply divided both the interest and the amortization rate per 12 in the formulae above and reworked them:
- Rate (monthly): $$r_{pm}=R_0\frac{(a+i)}{12}$$
- Interest, Amortization and Remaining debt at month n: $$ \begin{align} I_n&=R_0\cdot\frac{1}{12}\left[(a+i)-a\left(1+\frac{i}{12}\right)^{n-1}\right]\\ A_n&=R_0\cdot \frac{a}{12}\left(1+\frac{i}{12}\right)^{n-1}\\ R_n&=R_0\cdot\left(1-\frac{a}{i}\left[\left(1+\frac{i}{12}\right)^n-1\right]\right) \end{align} $$
However, when we compare it with online services, it fails by far since the error accumulates.
Numerical example with $i=4.00$ %, $a=1.00$ %, $P=R_0=119,160.00$, calculated with the formulae above:
| Month-Year | Interest | Amortization | Payment | Remaining |
|---|---|---|---|---|
| Nov-2023 | 397.20 | 99.30 | 496.50 | 119,060.70 |
| Dec-2023 | 396.87 | 99.63 | 496.50 | 118,961.07 |
| Jan-2024 | 396.54 | 99.96 | 496.50 | 118,861.11 |
| Feb-2024 | 396.20 | 100.30 | 496.50 | 118,760.81 |
| Mar-2024 | 395.87 | 100.63 | 496.50 | 118,660.18 |
| Apr-2024 | 395.53 | 100.97 | 496.50 | 118,559.21 |
| May-2024 | 395.20 | 101.30 | 496.50 | 118,457.91 |
| Jun-2024 | 394.86 | 101.64 | 496.50 | 118,356.27 |
| Jul-2024 | 394.52 | 101.98 | 496.50 | 118,254.29 |
| Aug-2024 | 394.18 | 102.32 | 496.50 | 118,151.97 |
| Sep-2024 | 393.84 | 102.66 | 496.50 | 118,049.31 |
| Oct-2024 | 393.50 | 103.00 | 496.50 | 117,946.31 |
| Sum (monthly) | 4,744.31 | 1,213.69 | 5,958.00 | 117,946.31 |
| Calculated | 4,766.40 | 1,191.60 | 5,958.06 | 117,968.40 |
| Website (rounded) | 4,746.00 | 1,214.00 | 5,964.00 | 117,946.00 |
The direction of the numbers is right: as the time passes by, the amortization part of the monthly payment gets higher and the interest lower. The difference in the remaining debt for one year between the monthly values and the calculated directly is -22.09, which is not that far off, but after 10 years it looks way different:
- According to the monthly formulae, there will be a remaining debt of: 104,538.09
- According to the yearly formulae, it will be more: 104,853.52
- According to the website: 104,538.00
So after 10 years, the error added up to around −315.43 when comparing the monthly to the yearly calculations and it gets worse with time.
Where is the thinking mistake here and how can we get close to the correct values?