房贷月供计算公式及推导.docx

房贷月供计算公式及推导1. 房贷月供公式设M 为贷款总额n 为分期数（分期数=贷款月数=贷款年数*12） py为贷款年利率（百分数 %）p 为贷款月利率（月利率=年利率/12 /100 %）x 为月供（等额还款）为方便简化和推导计算公式，月利率将百分数转换为小数x=p(1+p)n(1+p)n-1M2. 房贷月供公式推导第一期还款的本期利息为月利率*贷款总额 ，即 pM第一期还款后的贷款余额为贷款总额+本期利息-本期月供，即第一期余额为： M1=M+pM-x=1+pM-x第二期还款的利息为月利率*上一期贷款余额 ，即 p*(1+p)*M-x)第二期还款后的贷款余额为上一期贷款余额+本期利息-本期月供，即： M2=1+p1+pM-x-x=1+p2M-(1+p+1)x同理,第三期的贷款余额为:M3=1+p1+p2M-1+p+1x-x=1+p3M-1+p2+1+p+1x第k期的贷款余额为Mk=1+pkM-1+pk-1+1+pk-2+1+p+1x注意到上式中后面为等比数列的k项和，记q=1+p，则，Mk=qkM-qk-1+qk-2+q+1x=qkM-1-qk1-qx第n期后的余额为零，即qnM-1-qn1-qx=0qnM=1-qn-11-qxx=(1-q)qnM1-qn=(q-1)qnMqn-1将 q=1+p代入上式得月供公式x=p(1+p)n(1+p)n-1M3. 等比数列前n项和公式推导 Sn=a1+a1q+a1q2+a1q3+a1qn-2+a1qn-1 qSn= a1q+a1q2+a1q3+a1qn-2+a1qn-1+a1qn -得： Sn-qSn=a1-a1qn当q1 时， Sn=a1(1-qn)1-q4. 房贷月供EXCEL公式=(B5*(1+B5)B3)/(1+B5)B3-1)*B15. 房贷月供宏模块公式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) Next k Columns(E:H).Select Se