樓宇按揭每月供款既公式係乜？

如果識用 Excel, 可用 PMT 這個 function
PMT(rate,nper,pv,fv,type)

Rate = 利率 (e.g. 6%p.a./12 = 0.5% per month)
Nper = 期數 (e.g. 10年 x 12 = 120 期)
pv = 本金 (e.g. $1,000,000)
fv, type 一般可以不理


Excel formula : = PMT(6%/12, 120, 1,000,000)



PMT(rate,nper,pv,fv,type)

Rate is the interest rate for the loan.

Nper is the total number of payments for the loan.

Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.

Fv is the future value, or a cash balance you want to attain after the last payment is made. If fv is omitted, it is assumed to be 0 (zero), that is, the future value of a loan is 0.

Type is the number 0 (zero) or 1 and indicates when payments are due.

Set type equal to If payments are due
0 or omitted At the end of the period
1 At the beginning of the period

假設
P = 貸款額
r = 年利率
N = 每年供款期數
T = 供款年期
M = 每期供款額

第1期
利息: I_1 = P * (r / N)
償還本金: R_1 = M - I_1

第2期
利息: I_2 = (P - R_1) * (r / N)
償還本金: R_2 = M - I_2

第3期
利息: I_3 = (P - R_1 - R_2) * (r / N)
償還本金: R_3 = M - I_3
...
到最後之前1期 (N * T - 1):
利息: I_NT-1 = (P - R_1 - R_2 - ... - R_NT-2) * (r / N)
償還本金: R_NT-1 = M - I_NT-1

到最後1期 (N * T):
利息: I_NT = (P - R_1 - R_2 - ... - R_NT-2 - R_NT-1) * (r / N)
償還本金: R_NT = M - I_NT

由於 P = R_1 + R_2 + ... + R_NT
所以 I_NT = R_NT * (r / N)
M = R_NT + I_NT = (1 + r/N) * R_NT

同樣 I_NT-1 = (R_NT-1 + R_NT) * (r / N)
M = R_NT * (r/N) + R_NT-1 * (1 + r/N)

消去 M, 可以求得
R_NT = (1 + r/N) * R_NT-1

因此償還本金 R_1, R_2, ..., R_NT 成為幾何級數 (Geometric Progression)
[首項(First term): R_1, 公比(Common Ratio): (1 + r/N)]
P = R_1+ R_2 + ... + R_NT
= R_1 * N/r * ((1 + r/N)^(N*T) - 1)

而 R_1 = M - I_1 = M - P * (r / N), 把它代入上一方程式,
可以得出每期供款額
M = P * (r / N) / (1 - (1 + r/N)^(-N*T))

總利息支出 = I_1 + I_2 + ... + I_NT
= N * T * M - P
= P * ((r * T / (1 - (1 + r/N)^(-N*T))) - 1)

舉例:
貸款額 P = 2,000,000
年利率5厘 r = 0.05
每月供款 N = 12
供款20年 T = 20
每期供款額 M = 2,000,000 * (0.05 / 12) / (1 - (1 + 0.05/12)^(-12*20)) = 13,199
總利息支出 = 2,000,000 * ((0.05 * 20 / (1 - (1 + 0.05/12)^(-12*20))) - 1) = 1,167,788