excel函数 pmt、mpmt、impt

现学现卖，仅供参考（本人非账务专业，但数据结果经工行个人贷款理财计算器验证，结果附在后面）。回答：（1）。使用excel的pmt函数，制下表：按揭 期限 年利率 200000 15 5.94% 期数 应还本金 应还利息 月还款额 贷款余额0 2000001 ￥-691.24 ￥-990.00 ￥-1,681.24 ￥199,308.762 ￥-694.66 ￥-986.58 ￥-1,681.24 ￥198,614.103 ￥-698.10 ￥-983.14 ￥-1,681.24 ￥197,916.014 ￥-701.55 ￥-979.68 ￥-1,681.24 ￥197,214.455 ￥-705.03 ￥-976.21 ￥-1,681.24 ￥196,509.436 ￥-708.52 ￥-972.72 ￥-1,681.24 ￥195,800.91第一期月还款额公式为：=pmt($c$2/12,$b$2*12,$a$2,)。其中$c$2为年利率所在单元格，即5.94%。第一期应还利息公式为：=ipmt($c$2/12,a5,$b$2*12,$a$2)。第一期应还本金公式为：=d5-c5第一期贷款余额公式为：=e4+b5选中这一行，向下复制即可得所有期数的数据。（2）。即算出前6个月的应还利息之和。￥-5,888.34（3）。改变a2，b2，c2中的按揭，期限，年利率值即可。你可以留下邮箱，我把excel文档发给你。附：工行个人贷款理财计算器计算结果：贷款金额：200000.0元 贷款期限：15.0年 贷款年利率：5.94% 还款方式：等额本息 期数 应还本金 应还利息 月还款额 贷款余额 0 200000.0 1 691.24 990.00 1,681.24 199,308.76 2 694.66 986.58 1,681.24 198,614.10 3 698.10 983.14 1,681.24 197,916.01 4 701.55 979.68 1,681.24 197,214.45 5 705.03 976.21 1,681.24 196,509.43 6 708.52 972.72 1,681.24 195,800.91 7 712.02 969.21 1,681.24 195,088.89 8 715.55 965.69 1,681.24 194,373.34 9 719.09 962.15 1,681.24 193,654.25 10 722.65 958.59 1,681.24 192,931.60 11 726.23 955.01 1,681.24 192,205.38 12 729.82 951.42 1,681.24 191,475.56 13 733.43 947.80 1,681.24 190,742.12 14 737.06 944.17 1,681.24 190,005.06 15 740.71 940.53 1,681.24 189,264.35 16 744.38 936.86 1,681.24 188,519.97 17 748.06 933.17 1,681.24 187,771.90 18 751.77 929.47 1,681.24 187,020.14 19 755.49 925.75 1,681.24 186,264.65 20 759.23 922.01 1,681.24 185,505.42 21 762.99 918.25 1,681.24 184,742.44 22 766.76 914.48 1,681.24 183,975.68 23 770.56 910.68 1,681.24 183,205.12 24 774.37 906.87 1,681.24 182,430.75 25 778.21 903.03 1,681.24 181,652.54 26 782.06 899.18 1,681.24 180,870.48 27 785.93 895.31 1,681.24 180,084.55 28 789.82 891.42 1,681.24 179,294.74 29 793.73 887.51 1,681.24 178,501.01 30 797.66 883.58 1,681.24 177,703.35 31 801.61 879.63 1,681.24 176,901.74 32 805.57 875.66 1,681.24 176,096.17 33 809.56 871.68 1,681.24 175,286.61 34 813.57 867.67 1,681.24 174,473.04 35 817.60 863.64 1,681.24 173,655.44 36 821.64 859.59 1,681.24 172,833.80 37 825.71 855.53 1,681.24 172,008.09 38 829.80 851.44 1,681.24 171,178.29 39 833.90 847.33 1,681.24 170,344.39 40 838.03 843.20 1,681.24 169,506.36 41 842.18 839.06 1,681.24 168,664.18 42 846.35 834.89 1,681.24 167,817.83 43 850.54 830.70 1,681.24 166,967.29 44 854.75 826.49 1,681.24 166,112.54 45 858.98 822.26 1,681.24 165,253.56 46 863.23 818.01 1,681.24 164,390.33 47 867.51 813.73 1,681.24 163,522.82 48 871.80 809.44 1,681.24 162,651.02 49 876.11 805.12 1,681.24 161,774.91 50 880.45 800.79 1,681.24 160,894.45 51 884.81 796.43 1,681.24 160,009.64 52 889.19 792.05 1,681.24 159,120.45 53 893.59 787.65 1,681.24 158,226.86 54 898.01 783.22 1,681.24 157,328.85 55 902.46 778.78 1,681.24 156,426.39 56 906.93 774.31 1,681.24 155,519.46 57 911.42 769.82 1,681.24 154,608.05 58 915.93 765.31 1,681.24 153,692.12 59 920.46 760.78 1,681.24 152,771.66 60 925.02 756.22 1,681.24 151,846.64 61 929.60 751.64 1,681.24 150,917.04 62 934.20 747.04 1,681.24 149,982.85 63 938.82 742.42 1,681.24 149,044.02 64 943.47 737.77 1,681.24 148,100.55 65 948.14 733.10 1,681.24 147,152.41 66 952.83 728.40 1,681.24 146,199.58 67 957.55 723.69 1,681.24 145,242.03 68 962.29 718.95 1,681.24 144,279.74 69 967.05 714.18 1,681.24 143,312.69 70 971.84 709.40 1,681.24 142,340.85 71 976.65 704.59 1,681.24 141,364.20 72 981.48 699.75 1,681.24 140,382.72 73 986.34 694.89 1,681.24 139,396.37 74 991.23 690.01 1,681.24 138,405.15 75 996.13 685.11 1,681.24 137,409.02 76 1,001.06 680.17 1,681.24 136,407.95 77 1,006.02 675.22 1,681.24 135,401.94 78 1,011.00 670.24 1,681.24 134,390.94 79 1,016.00 665.24 1,681.24 133,374.94 80 1,021.03 660.21 1,681.24 132,353.90 81 1,026.09 655.15 1,681.24 131,327.82 82 1,031.16 650.07 1,681.24 130,296.65 83 1,036.27 644.97 1,681.24 129,260.38 84 1,041.40 639.84 1,681.24 128,218.99 85 1,046.55 634.68 1,681.24 127,172.43 86 1,051.73 629.50 1,681.24 126,120.70 87 1,056.94 624.30 1,681.24 125,063.76 88 1,062.17 619.07 1,681.24 124,001.59 89 1,067.43 613.81 1,681.24 122,934.16 90 1,072.71 608.52 1,681.24 121,861.44 91 1,078.02 603.21 1,681.24 120,783.42 92 1,083.36 597.88 1,681.24 119,700.06 93 1,088.72 592.52 1,681.24 118,611.34 94 1,094.11 587.13 1,681.24 117,517.23 95 1,099.53 581.71 1,681.24 116,417.70 96 1,104.97 576.27 1,681.24 115,312.73 97 1,110.44 570.80 1,681.24 114,202.29 98 1,115.94 565.30 1,681.24 113,086.36 99 1,121.46 559.78 1,681.24 111,964.90 100 1,127.01 554.23 1,681.24 110,837.89 101 1,132.59 548.65 1,681.24 109,705.30 102 1,138.20 543.04 1,681.24 108,567.10 103 1,143.83 537.41 1,681.24 107,423.27 104 1,149.49 531.75 1,681.24 106,273.78 105 1,155.18 526.06 1,681.24 105,118.60 106 1,160.90 520.34 1,681.24 103,957.69 107 1,166.65 514.59 1,681.24 102,791.05 108 1,172.42 508.82 1,681.24 101,618.63 109 1,178.23 503.01 1,681.24 100,440.40 110 1,184.06 497.18 1,681.24 99,256.34 111 1,189.92 491.32 1,681.24 98,066.43 112 1,195.81 485.43 1,681.24 96,870.62 113 1,201.73 479.51 1,681.24 95,668.89 114 1,207.68 473.56 1,681.24 94,461.21 115 1,213.65 467.58 1,681.24 93,247.56 116 1,219.66 461.58 1,681.24 92,027.90 117 1,225.70 455.54 1,681.24 90,802.20 118 1,231.77 449.47 1,681.24 89,570.43 119 1,237.86 443.37 1,681.24 88,332.57 120 1,243.99 437.25 1,681.24 87,088.58 121 1,250.15 431.09 1,681.24 85,838.43 122 1,256.34 424.90 1,681.24 84,582.09 123 1,262.56 418.68 1,681.24 83,319.53 124 1,268.81 412.43 1,681.24 82,050.73 125 1,275.09 406.15 1,681.24 80,775.64 126 1,281.40 399.84 1,681.24 79,494.24 127 1,287.74 393.50 1,681.24 78,206.50 128 1,294.12 387.12 1,681.24 76,912.39 129 1,300.52 380.72 1,681.24 75,611.87 130 1,306.96 374.28 1,681.24 74,304.91 131 1,313.43 367.81 1,681.24 72,991.48 132 1,319.93 361.31 1,681.24 71,671.55 133 1,326.46 354.77 1,681.24 70,345.09 134 1,333.03 348.21 1,681.24 69,012.06 135 1,339.63 341.61 1,681.24 67,672.43 136 1,346.26 334.98 1,681.24 66,326.17 137 1,352.92 328.31 1,681.24 64,973.25 138 1,359.62 321.62 1,681.24 63,613.63 139 1,366.35 314.89 1,681.24 62,247.28 140 1,373.11 308.12 1,681.24 60,874.17 141 1,379.91 301.33 1,681.24 59,494.26 142 1,386.74 294.50 1,681.24 58,107.51 143 1,393.61 287.63 1,681.24 56,713.91 144 1,400.50 280.73 1,681.24 55,313.41 145 1,407.44 273.80 1,681.24 53,905.97 146 1,414.40 266.83 1,681.24 52,491.57 147 1,421.40 259.83 1,681.24 51,070.16 148 1,428.44 252.80 1,681.24 49,641.72 149 1,435.51 245.73 1,681.24 48,206.21 150 1,442.62 238.62 1,681.24 46,763.60 151 1,449.76 231.48 1,681.24 45,313.84 152 1,456.93 224.30 1,681.24 43,856.90 153 1,464.15 217.09 1,681.24 42,392.76 154 1,471.39 209.84 1,681.24 40,921.37 155 1,478.68 202.56 1,681.24 39,442.69 156 1,486.00 195.24 1,681.24 37,956.69 157 1,493.35 187.89 1,681.24 36,463.34 158 1,500.74 180.49 1,681.24 34,962.60 159 1,508.17 173.06 1,681.24 33,454.42 160 1,515.64 165.60 1,681.24 31,938.79 161 1,523.14 158.10 1,681.24 30,415.65 162 1,530.68 150.56 1,681.24 28,884.97 163 1,538.26 142.98 1,681.24 27,346.71 164 1,545.87 135.37 1,681.24 25,800.84 165 1,553.52 127.71 1,681.24 24,247.32 166 1,561.21 120.02 1,681.24 22,686.10 167 1,568.94 112.30 1,681.24 21,117.16 168 1,576.71 104.53 1,681.24 19,540.45 169 1,584.51 96.73 1,681.24 17,955.94 170 1,592.36 88.88 1,681.24 16,363.59 171 1,600.24 81.00 1,681.24 14,763.35 172 1,608.16 73.08 1,681.24 13,155.19 173 1,616.12 65.12 1,681.24 11,539.07 174 1,624.12 57.12 1,681.24 9,914.95 175 1,632.16 49.08 1,681.24 8,282.79 176 1,640.24 41.00 1,681.24 6,642.56 177 1,648.36 32.88 1,681.24 4,994.20 178 1,656.52 24.72 1,681.24 3,337.68 179 1,664.72 16.52 1,681.24 1,672.97 180 1,672.96 8.28 1,681.24 0.00 合计 200,000.00 102,622.74 302,622.74 20210311