Loading...
假如银行利息6%，是名义上的利息，半年利率是3%。由于半年一次利滚利(semi-compound), 真正的实际年利息 (effective rate)= 1+3%）2-1= 6.09%
换算成每月的利息 （1+RM）12-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 | ||