What's the math formula that is used to calculate the monthly payment in this mortgage calculator?
What's the math formula that is used to calculate the monthly payment in this mortgage calculator?
I would like to know this math formula so that I can plug in the following values
Mortgage Amount: $100,000
Rate Type: Fixed
Interest Rate: 6%
Interest Term: 5 Years
Payment Frequency: Monthly
Amortization Rate: 5%
and calculate the monthly payment to $1,929.86 (as shown in the mortgage calculator).
The amortization table you cited states that “interest is compounded semi-annually for fixed interest rates.” (Note: This fact is not derived from finance theory; this is the bank's policy.) Therefore, the effective monthly interest factor is computed as $R\equiv 1.03^{1/6}$, since $3$% is the semi-annual interest rate and there are six months in an interest period.
The goal is to determine the fixed amount that needs to be paid each month in order to pay off the debt in 5 years, i.e., 60 months. Let $p$ denote this unknown number and $v_0$ the initial principal. The total debt by the end of the first month is: $$v_1=R v_0-p,$$ because the principal yields interest but $p$ is paid off. Similarly, $$v_2=Rv_1-p=R(Rv_0-p)-p=R^2 v_0-Rp-p.$$ By induction, it is not difficult to see that $$v_T=R^T v_0-p\sum_{t=0}^{T-1}R^t=R^T v_0-p\frac{R^T-1}{R-1}$$ after $T$ months.
Now, if the debt is to be paid off in $T$ months, then $v_{T}=0$, so that solving for $p$ yields: \begin{align*} 0=R^T v_0-p\frac{R^T-1}{R-1}, \end{align*} or, after rearrangement: $$\boxed{p=\dfrac{R^T(R-1)}{R^T-1}v_0}$$ If you plug in $T=60$, $v_0=100\mathord, 000$, and $R=1.03^{1/6}$, then you get a monthly payment of $\$1\mathord,929\mathord .86$.
The formula is the amortization or Equated Monthly Payment formula (see also this link):
$A = P \cfrac{r (1+r)^n}{(1+r)^n - 1}$
Without getting into the details of how it is derived, what you need to know is the following:
-
Pis the principal amount borrowed -
Ais the periodic amortization payment -
ris the periodic interest rate divided by 100 (nominal annual interest rate also divided by 12 in case of monthly installments), and -
nis the total number of payments (for a 30-year loan with monthly payments n = 30 × 12 = 360)
For your example,
-
P= 100,000 -
Ais what we want to find -
r= $\cfrac{6}{100 \cdot 12}$ = $\cfrac{6}{1200}$ = 0.005, but as triple_sec pointed out, the amortization calculator you used says that "interest is compounded semi-annually for fixed interest rates and each payment period for variable interest rates," which means you use this calculation instead:r= ${0.03}^{1/6}$ = $\cfrac{6}{1200} \approx$ 0.049386 -
n= 5 years * 12 months/year = 60 months
So, we get this:
$A = 100000\cdot\cfrac{0.0049386\cdot1.0049386^{60}}{1.0049386^{60}-1}$ = $1,929.86
(You would get the same result if you put =PMT(1.03^(1/6)-1, 60, 100000) into a cell in Excel, because it does the same formula I quoted above.)
Note: you say "Amortization rate" where I believe you meant "amortization term" (5 years); that matches the calculator and makes more sense in this context.