7
$\begingroup$

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).

$\endgroup$
7
+50
$\begingroup$

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$.

$\endgroup$
3
$\begingroup$

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:

  • P is the principal amount borrowed
  • A is the periodic amortization payment
  • r is the periodic interest rate divided by 100 (nominal annual interest rate also divided by 12 in case of monthly installments), and
  • n is the total number of payments (for a 30-year loan with monthly payments n = 30 × 12 = 360)

For your example,

  • P = 100,000
  • A is 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.

$\endgroup$
  • $\begingroup$ The excel formula PMT appears to have special case handling when r = 0 to prevent division by zero, in which case the calculations seems to be A = P / n $\endgroup$ – Scott Markwell Apr 4 '16 at 22:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.