Excel计算住房贷款与个人储蓄

1、Excel 计算住房贷款和个人储蓄 银行中的利息计算起来非常的烦琐，让大多数没有学过专业财会方面的人都感到束手无策， 比如在银行方面的住房贷款及个人储蓄等方面。 MSOFFICE 中的 Excel 计算完全可以让你解 除这方面的烦恼。请跟我着往下关于这两个问题的实例解决方法。 Excel 2002 中的 PMT 函数，通过单、双变量的模拟运算来实现贷款的利息计算。通过 讲解，相信读者可以很方便地计算分期付款的利息，以及选择分期付款的最优方案。 固定利率的付款计算 PMT 函数可基于固定利率及等额分期付款方式，根据固定贷款利率、定期付款和贷款 金额，来求出每期 （一般为每月 ）应偿还的贷款金额。

2、先来了解一下 PMT 函数的格式和应用 方式： PMT（Rate ， Nper ，Pv， Fv ， Type） 其中各参数的含义如下： Rate :各期利率，例如，如果按 4.2%的年利率借入一笔贷款来购买住房，并按月偿还 贷款，则月利率为 4.2%/12（即 0.35%）。用户可以在公式中输入 4.2%/12、0.35%或 0.0035 作 为 Rate 的值。 Nper :贷款期数，即该项贷款的付款期总数。例如，对于一笔 10年期按月偿还的住房 贷款，共有10X12（即120）个偿款期数。可以在公式中输入 120作为Nper的值。 Pv：现值，或一系列未来付款的当前值的累积和，也就是贷款金

3、额。 Fv：指未来终值，或在最后一次付款后希望得到的现金余额。如果省略 Fv，则假设其 值为零，也就是一笔贷款的未来值为零，一般银行贷款此值为 0。 Type:数字0或1,用以指定各期的付款时间是在期初还是期末。如果为 0或缺省，表 明是期末付款，如果为 1，表明是期初付款。 浮动利率的付款计算 下面结合实例讲解利用该函数的具体计算方法,假定采用分期付款的方式,用户贷款 10 万元用于购买住房,如果年利率是 4.2%,分期付款的年限是 10 年,计算该用户在给定 条件下的每期应付款数。 如图1所示，在单元格 A1、B1、A2、B2、A3、B3中分别输入给定条件， D2D11 中分别输入不同的利

4、率条件，按照下列步骤进行计算： E Microsoft E cel -利用EKCI&式tfjPMT画敎计幫升期忖款 轡丈伴旧骗辖观團仞描入（D福式 工凤U数据助宙口（胡帮助凹 Tines New Ronan * 16 + B z y垂塞臺固貉 El A C D E 1 贷款金额 100000 利率 2 贷款年限 10 3. 3 年利率 4. 20% 3 4 每月付款额 4% 5 4. 6 4. 20% ? 4. 8 4. 40% 9 4. 50% 10 4. 60% 11 4. 70% 1选定要输入公式的单元格 E1，可以直接输入公式 “=PMT（B3/12, B2*12，-B1）”，

5、返回 一个值1021.98，这个值就是用户每月的付款额， 图1中的B4与E1单元格的输入公式相同， 这一步也可以利用粘贴函数来输入函数；操作过程是：单击 常用”工具栏中的 粘贴函数 按钮，弹出 粘贴函数”对话框。在 函数分类”框中选择 财务”，在 函数名”列表框中选择 2.单击 确定”按钮，出现如图2所示的 公式选项板”，在“Rate框中输入利率 “B3/12” 即把年利率转换成月利率； 在“Nper框中输入“B2*12”，即把支付的年限换算成支付的月数； 在“Pv框中输入贷款金额 -B1”加入负号是为返回一个正值 ）。 too .勒 文仲:巳 痂扬 视鬥 A(D瞻式(Q)工眞(!)数咼D囱口

6、辿)帮助但) S1 % 5 拒呼匸 划 二P T(B1Z B2*12, -Bl) E-ate各期利牢是一固定追： 画 计算菇黒三 1,021閃 | 碇 | _取消 | 3单击确定”按钮，即可在E1单元格中得到年利率为 4.2%条件下，分期付款每期应付 的金额数。 4选定包含输入数值和公式的范围，选择 数据”菜单中的 模拟运算表”命令，出现 模 拟运算表”对话框。 5由于变量的替换值排在一列中，因此单击 输入引用列的单元格 ”文本框，然后输入 “ B3。 滲耳自|直竽|毘跑1鼠妙 KU T O* * % A ： FFflT 1021 返回花固定利率下，般费或赁熬的等额分期偿还前” 6单击确定”按

7、钮，得到如图3所示的结果，即得出在不同利率条件下每月应付的金额 数。用户即可根据自己的需要进行选择。 应Micrcsoft Ewcel -利用Encel式的PMT函数H烷分期忖諛 |越対利日温嘉 Q 视獣少捲入过祸式 工凤D數風凶窗口他帮助凹 H 13 a船电isQ 16 - BZU Ell k c E 1 1 俶款金额 100000 利率 1,021.98 2 贷款年限 10 3.80% 1,002.97 3 年利率 4.20% 3.90% 1,007.71 每月付款额 l?021.9S 4% 1,012.45 4.10% 1,017.21 g 4.20% 1,021.98 7 430% 1

8、,026.77 8 440% 1,031.57 9 4.50% 1,03638 10 4.60% 1.04 匚 21 n 4.70% 1,046.05 1 1 14 4 k H Sheetl /Sheet2/Sheet3/ Id 冷繆硃遂 . p _ 逶殳 ?就绪 III璽 Eus iNiin | |- 上述计算运用的是单变量模拟运算， 就是考查一个值的变化(这里是利率的变化)对公式 的计算结果的影响程度。 由于我国现行的贷款利率由政府统一规定， 所以较少出现上述的计算。随着市场经济的 深化和我国加入 WTO，利率市场化的步伐也逐步加快，国家已要求各地在适当的时候和合 适的条件下实施利率市场化

9、改革， 到那时必将出现不同利率的贷款， 用户就可以更多地利用 上述方法计算贷款利息了。浮动利率、浮动年限的付款计算 用户在计算住房贷款时，根据个人经济条件往往会考虑不同的利率和不同的分期付款年 限条件下的每月付款额。 这种情况下的计算只要在上述计算的基础上加一个变量， 也就是双 变量模拟运算表，即输入两个变量的不同替换值，然后计算这两个变量对公式的影响。 当计算不同利率不同年限的分期付款额时， 需要建立有两个变量的模拟运算表， 一个表 示不同的利率，另一个表示不同的付款年限，具体操作如下： 1建立如图4所示的表格，在单元格 B6中输入公式 “=PMT(B3/12, B2*12，-B1)”，用

10、户要注意的itf I xi limes New Roman 是不同的利率值输入在一列中，必须在 PMT公式的正下方，不同年限输入在一 行中，行输入项必须在公式的右侧。国血E”d -累!用E典白处式的PET帝蠶计聲势则柑就 Jti 4单击确定”按钮，即可得到如图 5所示的运算结果。 勺坤& 期強餐啦觐他福超)XM(D看播畑 Bn(帮蹦也 _ I。0日：罰静0爭注崎趙$|町*|龙齐斛坯IO屛100% 朗. 2选定包含公式及输入值的行和列的单元格数据。选择 数据”菜单中的 模拟运算表”命 令，出现一个 模拟运算表”对话框。 3由于付款年限被编排成行，因此在 输入行的引用单元格”文本框中输入“

11、B2；年利率 被编排成列，在 输入列的引用单元格”文本框中输入“ B3。 & B 1 C D K F 1 1 贷款金额 100000 2 贷款年限 10 3 年利率 4.20% 4 S 贷款年数 Yl,02L98 8 9 10 1 11 ? 利率 3.80% 8 3.90% 9 4% 10 4.10% 11 4.20% 12 4.30% 13 440% U .50% 1S 4.60% 14 1 *1 5taU /Shi? et 2 / 51i?el 3/ hl * H | 固帶% ,歸我 环眠_厂峦払* r n t*ew K.Qfnjn J_ (n/122*lZ-W ur； 二| 口主

12、 亠 I 蚩凶 貼 二 j 二 PtfT 倨时忆 32*12*41) A 丨 B I _ f _ 口 _ _ 基 _ I 芦 1 三 1 贷款奋额 100000 J- 贷款年限 I 卜 t mlI | - - - - - - - - : - 3年利率 4.20 蚪一 一一 _ 丄 1 - 二 _ 费款年数 6 1,02 L98 8 9 10 1 T 利率 3.80% 1,209.砧 ltd94.72 1 h002.97 928.09 8 5.90% 1,214.28 1,099.40 1,007.71 932.87 9 4% 1,218.93 LJ04 10 1,01245 937.67 10

13、 4.10% 1.223.58 YL10S.80 1,017.21 42.48 11 4.20% 1.228.25 LJ 13.53 Y1.02L98 $4730 19 430% Y 132.93 1.J 1926 XI ,026.77 952 15 13 4.40% 1,237.62 YL.123.00 1,031.57 97.00 M 4,50% 1.24232 Y1J27.76 1M38 96L7 lb 0% _V1.247.04 1、13253 p 1 l,04L21 966.76 r M 13_ _ I如| ttW= V WrMl j| CAK NW 用户可以根据个人实际情况， 代入相应数据后，选择适合自己的分期付款利率和年限的 支付方案。以上仅仅介绍了 Excel 2002中的PMT函数计算贷款条件下的分期付款额，这个 公式还可以用来计算用户的零存整取储蓄额， 如用户想在几年后达到一定的存款额， 给定存 款利率和年