房贷月供计算公式及推导

房贷月供计算公式及推导

1. 房贷月供公式

设

M 为贷款总额

n 为分期数（分期数=贷款月数=贷款年数*12）

py为贷款年利率(百分数 ％）

p 为贷款月利率（月利率=年利率/12 /100 %）

x 为月供（等额还款）

为方便简化和推导计算公式,月利率将百分数转换为小数

2. 房贷月供公式推导

第一期还款后的贷款余额为贷款总额+本期利息-本期月供，即第一期余额为：

第一期还款的本期利息为月利率*贷款总额 ，即

第二期还款的利息为月利率＊上一期贷款余额 ，即 p*((1+p）＊M—x)

第二期还款后的贷款余额为上一期贷款余额+本期利息-本期月供，即:

同理，第三期的贷款余额为:

第k期的贷款余额为

注意到上式中后面为等比数列的k项和，记q=1+p,则，

第n期后的余额为零，即

将 q=1+p代入上式得月供公式

3. 等比数列前n项和公式推导

①

②

①②得

当 时，



4. 房贷月供EXCEL公式

贷款额 M 420,000.00

年利率 py %6.69

分期数 n120

月利率 p0.005575

月供 x 4,809.72



=(B5*（1+B5）^B3）/(（1+B5）^B3-1）＊B1



5. 房贷月供宏模块公式

Sub Macro房贷月供及余额（）

’ 房贷月供及余额计算

Dim M As Double '贷款总额， 输入参数

Dim py As Double ’年利率 %， 输入参数

Dim n As Integer ’分期数（月数=还款年数＊12)， 输入参数

Dim p As Double ’月利率， =年利率 ％ /100/12

Dim x As Double '房贷月供

Dim y As Double ’房贷余额

Dim c As Double ’房贷利息

Dim r As Double '归还房贷本金

Dim k As Integer

M = Range（"B1"）.Value ’贷款总额， 输入参数

py = Range(”B2"）。Value '年利率 ％， 输入参数

n = Range（”B3")。Value ’分期数（月数=还款年数*12）, 输入参数

p = py / 12 / 100

x = （p ＊ （1 + p） ^ n） / （(1 + p) ^ n - 1) ＊ M

y = M

For k = 1 To n

c = p ＊ y

r = x - c

y = (1 + p) ^ k ＊ M — （（1 + p） ^ k — 1） / p ＊ x

Cells（k + 1， 4） = k

Cells（k + 1， 5） = Round(y， 2）

Cells（k + 1, 6） = Round(x, 2)

Cells（k + 1， 7） = Round(c， 2）

Cells(k + 1， 8) = Round(r, 2）

ext k

Columns("E：H”)。Select

Selection。umberFormatLocal = "#,＃＃0.00_）；(＃，#＃0。00)”

Range（"B1")。Select

End Sub