×

Loading...
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务
Ad by
  • 推荐 OXIO 加拿大高速网络,最低月费仅$40. 使用推荐码 RCR37MB 可获得一个月的免费服务

如何理解银行利率和月供的计算

假如银行利息6%,是名义上的利息,半年利率是3%。由于半年一次利滚利(semi-compound),  真正的实际年利息 (effective rate)= 1+3%)2-1= 6.09%

换算成每月的利息 (1+RM12-1 =6.09% ,所以Rm=(1.0606)1/12=0.494%

M = Monthly payment
P =loan size   P0=initial loan size
Rm=monthly interest rate
n= number of monthly payment (25 years = 300 months)

第一个月:P1=  P0 + P0*Rm – M=P0*(1+Rm)-M
第二个月:P2=  P1*(1+Rm)-M = (P0*(1+Rm)-M)*(1+Rm))-M=P0 *(1+Rm)2-M*(1+Rm) -M
第三个月:P3= P0 (1+Rm)3-M(1+Rm)2-M(1+Rm)-M
第n个月:   Pn= Po (1+Rm)n-M(1+Rm)n-1 -… -M(1+Rm)2-M(1+Rm) -M



以25年以算,n=300个月付完贷款,此时, P300=0


用Excel的PMT函数可以计算25年的月供。这是一个贷款10万,年利息为6%,term为5年的计算例子,只列出前5年的表格。可以看出principle (balance)越来越少,所以每月月供包含的利息越来越少。

Input Data








Initial Principal

$100,000



Annual, nominal interest rate

6.00%



Amortization Period (years)

25



Initial Date

01-Jul-02











Computed Data







Effective Annual Rate (to 2 decimals)

6.09%



Periodic Interest Rate

Annual

5.9263%

Monthly

0.493862%



Months to Amortization

300



Periodic Payment, Monthly

639.8066237



Rounded to nearest cent

639.81



Rounded to nearest dollar

640.00











Date

Payment

Interest

Principal

Extra Payments

Balance





01-Jul-02





100,000.00



01-Aug-02

639.81

493.86

145.95

0.00

99,854.05



01-Sep-02

639.81

493.14

146.67

0.00

99,707.38



01-Oct-02

639.81

492.42

147.39

0.00

99,559.99



01-Nov-02

639.81

491.69

148.12

0.00

99,411.87



01-Dec-02

639.81

490.96

148.85

0.00

99,263.02



01-Jan-03

639.81

490.22

149.59

0.00

99,113.43



01-Feb-03

639.81

489.48

150.33

0.00

98,963.10



01-Mar-03

639.81

488.74

151.07

0.00

98,812.03



01-Apr-03

639.81

488.00

151.81

0.00

98,660.22



01-May-03

639.81

487.25

152.56

0.00

98,507.66



01-Jun-03

639.81

486.49

153.32

0.00

98,354.34



01-Jul-03

639.81

485.73

154.08

0.00

98,200.26



01-Aug-03

639.81

484.97

154.84

0.00

98,045.42



01-Sep-03

639.81

484.21

155.60

0.00

97,889.82



01-Oct-03

639.81

483.44

156.37

0.00

97,733.45



01-Nov-03

639.81

482.67

157.14

0.00

97,576.31



01-Dec-03

639.81

481.89

157.92

0.00

97,418.39



01-Jan-04

639.81

481.11

158.70

0.00

97,259.69



01-Feb-04

639.81

480.33

159.48

0.00

97,100.21



01-Mar-04

639.81

479.54

160.27

0.00

96,939.94



01-Apr-04

639.81

478.75

161.06

0.00

96,778.88



01-May-04

639.81

477.95

161.86

0.00

96,617.02



01-Jun-04

639.81

477.15

162.66

0.00

96,454.36



01-Jul-04

639.81

476.35

163.46

0.00

96,290.90



01-Aug-04

639.81

475.54

164.27

0.00

96,126.63



01-Sep-04

639.81

474.73

165.08

0.00

95,961.55



01-Oct-04

639.81

473.92

165.89

0.00

95,795.66



01-Nov-04

639.81

473.10

166.71

0.00

95,628.95



01-Dec-04

639.81

472.28

167.53

0.00

95,461.42



01-Jan-05

639.81

471.45

168.36

0.00

95,293.06



01-Feb-05

639.81

470.62

169.19

0.00

95,123.87



01-Mar-05

639.81

469.78

170.03

0.00

94,953.84



01-Apr-05

639.81

468.94

170.87

0.00

94,782.97



01-May-05

639.81

468.10

171.71

0.00

94,611.26



01-Jun-05

639.81

467.25

172.56

0.00

94,438.70



01-Jul-05

639.81

466.40

173.41

0.00

94,265.29



01-Aug-05

639.81

465.54

174.27

0.00

94,091.02



01-Sep-05

639.81

464.68

175.13

0.00

93,915.89



01-Oct-05

639.81

463.82

175.99

0.00

93,739.90



01-Nov-05

639.81

462.95

176.86

0.00

93,563.04



01-Dec-05

639.81

462.07

177.74

0.00

93,385.30



01-Jan-06

639.81

461.19

178.62

0.00

93,206.68



01-Feb-06

639.81

460.31

179.50

0.00

93,027.18



01-Mar-06

639.81

459.43

180.38

0.00

92,846.80



01-Apr-06

639.81

458.54

181.27

0.00

92,665.53



01-May-06

639.81

457.64

182.17

0.00

92,483.36



01-Jun-06

639.81

456.74

183.07

0.00

92,300.29



01-Jul-06

639.81

455.84

183.97

0.00

92,116.32



01-Aug-06

639.81

454.93

184.88

0.00

91,931.44



01-Sep-06

639.81

454.01

185.80

0.00

91,745.64



01-Oct-06

639.81

453.10

186.71

0.00

91,558.93



01-Nov-06

639.81

452.17

187.64

0.00

91,371.29



01-Dec-06

639.81

451.25

188.56

0.00

91,182.73



01-Jan-07

639.81

450.32

189.49

0.00

90,993.24



01-Feb-07

639.81

449.38

190.43

0.00

90,802.81



01-Mar-07

639.81

448.44

191.37

0.00

90,611.44



01-Apr-07

639.81

447.50

192.31

0.00

90,419.13



01-May-07

639.81

446.55

193.26

0.00

90,225.87



01-Jun-07

639.81

445.59

194.22

0.00

90,031.65



01-Jul-07

639.81

444.63

195.18

0.00

89,836.47




​​​​​​​

Report

Replies, comments and Discussions:

  • 枫下家园 / 枫下觅巢 / 如何理解银行利率和月供的计算 +2

    假如银行利息6%,是名义上的利息,半年利率是3%。由于半年一次利滚利(semi-compound),  真正的实际年利息 (effective rate)= 1+3%)2-1= 6.09%

    换算成每月的利息 (1+RM12-1 =6.09% ,所以Rm=(1.0606)1/12=0.494%

    M = Monthly payment
    P =loan size   P0=initial loan size
    Rm=monthly interest rate
    n= number of monthly payment (25 years = 300 months)

    第一个月:P1=  P0 + P0*Rm – M=P0*(1+Rm)-M
    第二个月:P2=  P1*(1+Rm)-M = (P0*(1+Rm)-M)*(1+Rm))-M=P0 *(1+Rm)2-M*(1+Rm) -M
    第三个月:P3= P0 (1+Rm)3-M(1+Rm)2-M(1+Rm)-M
    第n个月:   Pn= Po (1+Rm)n-M(1+Rm)n-1 -… -M(1+Rm)2-M(1+Rm) -M



    以25年以算,n=300个月付完贷款,此时, P300=0


    用Excel的PMT函数可以计算25年的月供。这是一个贷款10万,年利息为6%,term为5年的计算例子,只列出前5年的表格。可以看出principle (balance)越来越少,所以每月月供包含的利息越来越少。

    Input Data








    Initial Principal

    $100,000



    Annual, nominal interest rate

    6.00%



    Amortization Period (years)

    25



    Initial Date

    01-Jul-02











    Computed Data







    Effective Annual Rate (to 2 decimals)

    6.09%



    Periodic Interest Rate

    Annual

    5.9263%

    Monthly

    0.493862%



    Months to Amortization

    300



    Periodic Payment, Monthly

    639.8066237



    Rounded to nearest cent

    639.81



    Rounded to nearest dollar

    640.00











    Date

    Payment

    Interest

    Principal

    Extra Payments

    Balance





    01-Jul-02





    100,000.00



    01-Aug-02

    639.81

    493.86

    145.95

    0.00

    99,854.05



    01-Sep-02

    639.81

    493.14

    146.67

    0.00

    99,707.38



    01-Oct-02

    639.81

    492.42

    147.39

    0.00

    99,559.99



    01-Nov-02

    639.81

    491.69

    148.12

    0.00

    99,411.87



    01-Dec-02

    639.81

    490.96

    148.85

    0.00

    99,263.02



    01-Jan-03

    639.81

    490.22

    149.59

    0.00

    99,113.43



    01-Feb-03

    639.81

    489.48

    150.33

    0.00

    98,963.10



    01-Mar-03

    639.81

    488.74

    151.07

    0.00

    98,812.03



    01-Apr-03

    639.81

    488.00

    151.81

    0.00

    98,660.22



    01-May-03

    639.81

    487.25

    152.56

    0.00

    98,507.66



    01-Jun-03

    639.81

    486.49

    153.32

    0.00

    98,354.34



    01-Jul-03

    639.81

    485.73

    154.08

    0.00

    98,200.26



    01-Aug-03

    639.81

    484.97

    154.84

    0.00

    98,045.42



    01-Sep-03

    639.81

    484.21

    155.60

    0.00

    97,889.82



    01-Oct-03

    639.81

    483.44

    156.37

    0.00

    97,733.45



    01-Nov-03

    639.81

    482.67

    157.14

    0.00

    97,576.31



    01-Dec-03

    639.81

    481.89

    157.92

    0.00

    97,418.39



    01-Jan-04

    639.81

    481.11

    158.70

    0.00

    97,259.69



    01-Feb-04

    639.81

    480.33

    159.48

    0.00

    97,100.21



    01-Mar-04

    639.81

    479.54

    160.27

    0.00

    96,939.94



    01-Apr-04

    639.81

    478.75

    161.06

    0.00

    96,778.88



    01-May-04

    639.81

    477.95

    161.86

    0.00

    96,617.02



    01-Jun-04

    639.81

    477.15

    162.66

    0.00

    96,454.36



    01-Jul-04

    639.81

    476.35

    163.46

    0.00

    96,290.90



    01-Aug-04

    639.81

    475.54

    164.27

    0.00

    96,126.63



    01-Sep-04

    639.81

    474.73

    165.08

    0.00

    95,961.55



    01-Oct-04

    639.81

    473.92

    165.89

    0.00

    95,795.66



    01-Nov-04

    639.81

    473.10

    166.71

    0.00

    95,628.95



    01-Dec-04

    639.81

    472.28

    167.53

    0.00

    95,461.42



    01-Jan-05

    639.81

    471.45

    168.36

    0.00

    95,293.06



    01-Feb-05

    639.81

    470.62

    169.19

    0.00

    95,123.87



    01-Mar-05

    639.81

    469.78

    170.03

    0.00

    94,953.84



    01-Apr-05

    639.81

    468.94

    170.87

    0.00

    94,782.97



    01-May-05

    639.81

    468.10

    171.71

    0.00

    94,611.26



    01-Jun-05

    639.81

    467.25

    172.56

    0.00

    94,438.70



    01-Jul-05

    639.81

    466.40

    173.41

    0.00

    94,265.29



    01-Aug-05

    639.81

    465.54

    174.27

    0.00

    94,091.02



    01-Sep-05

    639.81

    464.68

    175.13

    0.00

    93,915.89



    01-Oct-05

    639.81

    463.82

    175.99

    0.00

    93,739.90



    01-Nov-05

    639.81

    462.95

    176.86

    0.00

    93,563.04



    01-Dec-05

    639.81

    462.07

    177.74

    0.00

    93,385.30



    01-Jan-06

    639.81

    461.19

    178.62

    0.00

    93,206.68



    01-Feb-06

    639.81

    460.31

    179.50

    0.00

    93,027.18



    01-Mar-06

    639.81

    459.43

    180.38

    0.00

    92,846.80



    01-Apr-06

    639.81

    458.54

    181.27

    0.00

    92,665.53



    01-May-06

    639.81

    457.64

    182.17

    0.00

    92,483.36



    01-Jun-06

    639.81

    456.74

    183.07

    0.00

    92,300.29



    01-Jul-06

    639.81

    455.84

    183.97

    0.00

    92,116.32



    01-Aug-06

    639.81

    454.93

    184.88

    0.00

    91,931.44



    01-Sep-06

    639.81

    454.01

    185.80

    0.00

    91,745.64



    01-Oct-06

    639.81

    453.10

    186.71

    0.00

    91,558.93



    01-Nov-06

    639.81

    452.17

    187.64

    0.00

    91,371.29



    01-Dec-06

    639.81

    451.25

    188.56

    0.00

    91,182.73



    01-Jan-07

    639.81

    450.32

    189.49

    0.00

    90,993.24



    01-Feb-07

    639.81

    449.38

    190.43

    0.00

    90,802.81



    01-Mar-07

    639.81

    448.44

    191.37

    0.00

    90,611.44



    01-Apr-07

    639.81

    447.50

    192.31

    0.00

    90,419.13



    01-May-07

    639.81

    446.55

    193.26

    0.00

    90,225.87



    01-Jun-07

    639.81

    445.59

    194.22

    0.00

    90,031.65



    01-Jul-07

    639.81

    444.63

    195.18

    0.00

    89,836.47




    ​​​​​​​

    • 谢谢,第一次知道月供是这样算出来的。
    • 谢谢,第一次知道月供是这样算出来的
    • 好复杂
    • 谢谢,正想知道算这个