第5章 制作贷款模拟运算表

模拟运算表数字格式
模拟运算表是一种用于显示一个或两个变量如何改变另一个变量的影响的工具。

在Excel中，模拟运算表可以通过“数据”菜单中的“模拟运算表”命令来创建。

模拟运算表的数字格式可以根据需要进行设置。

在Excel中，可以使用“单元格格式”对话框来设置数字格式。

具体步骤如下：
1. 选择要设置格式的单元格或列。

2. 点击鼠标右键，选择“单元格格式”选项。

3. 在“单元格格式”对话框中，选择“数字”选项卡。

4. 在“分类”列表中选择所需的数字格式，例如“常规”、“数值”、“货币”、“日期”等。

5. 单击“确定”按钮，应用数字格式。

此外，还可以使用自定义数字格式来设置模拟运算表的数字格式。

自定义数字格式可以使用Excel内置的数字格式代码和一些自定义代码来创建。

例如，以下是一个自定义数字格式代码示例：
`%`
这将使数字显示为百分比格式，并保留两位小数。

总之，模拟运算表的数字格式可以根据需要进行设置，可以使用Excel内置的数字格式选项或自定义数字格式代码来创建所需的格式。

Excel模拟运算表工具Excel模拟运算表工具是一种只需一步操作就能计算出所有变化的模拟分析工具。

它可以显示公式中某些值的变化对计算结果的影响，为同时求解某一运算中所有可能的变化值组合提供了捷径。

并且，模拟运算表还可以将所有不同的计算结果同时显示在工作表中，便于查看和比较。

下面举个例子加以说明。

如某人买房，需资金90万，一部分由银行贷款取得，年利率假设为4%，采取每月等额还款的方式，贷款数额是80万、70万、60万、50万、40万还是30万？还款期限是10年、15年、20年、25年还是30年？利用Excel模拟运算表的具体步骤如下：1．使用[文件/新建]或[新建]工具，建立一新工作簿，并选择一张工作表，将住房贷款有关的基本数据输入该工作表，如图1（B2:D6）单元格区域所示。

图1 住房贷款还款的Excel模拟运算表分析图2．计算总付款期数。

总付款期数是借款年限与每年付款期数的乘积（=借款年限×每年付款期数），在选择D7单元后，输入公式：=D5*D6即可。

3．计算每期偿还金额。

每期偿还金额属于年金问题，因此，计算每期偿还金额可使用PMT（）函数。

选择D8单元后，输入公式：=PMT($D$4/$D$6,$D$7,$D$3)即可，4．列示住房贷款“贷款金额”和“总付款期数”各种可能的数据，如图1（A14:A19）、（B13:F13）单元格区域所示，并在行与列交叉的A13单元格中输入目标函数PMT（），即在该单元格中输入公式：=PMT($D$4/$D$6,$D$7,$D$3)。

5．选择目标单元区域（A13:F19），使用[数据/模拟运算表]命令，出现如图2所示的对话框。

在其对话框的[输入引用行的单元格]中输入$D$7，[输入引用列的单元格]中输入$D$3，再单击[确定]按钮，各分析值自动填入双因素分析表中，如图1（B14:F19）单元格区域所示。

图2 模拟运算表工具使用示意图由于在工作表中，借款金额（单元格D3）、借款年利率（单元格D4）、借款年限（单元格D5）、每期偿还金额（单元格D8）以及各因素可能组合（单元格区域A14:A19和B13:F13）这些基本数据之间建立了动态链接，因此，财务人员可通过改变单元格D3、单元格D4、单元格D5或单元格D6中的数据，或调整单元格区域A14:A19和B13:F13中的各因素可能组合，各分析值将会自动计算，不用再重复上述步骤。

EXCEL模拟运算表例某人贷款购车，车价20万元，规定年利率为5.5%，24个月还清。

计算购车人的月还款额。

首先在工作表中建立如图7-37所示的计算模型。

其中前三项数据都是常数。

月付款额用公式：“PMT（利率，期数，-车价）”计算，结果为月付￥8 819.13元。

注意其中年利率与月利率的区别。

现在，如果购车人或银行希望了解不同的利率变化时月付金额的相应变化情况，就可以使用Excel 2000中的一个很有用的分析工具：模拟运算表。

1. 单变量模拟运算表在工作表的一块空白区输入两个利率值4.5%，4.6%，选择这两个单元格后，拖住选择区右下角的复制柄向下拖曳（至利率6.0%为止）。

在紧邻利率的右侧一列，起始利率的上面一行（图中的E2单元格）输入：“=B5”，于是E2中也出现了B5中的值，并且将会随着B5的变化而改变。

1）选择单元格区域D2:E18，把利率系列及上方的公式单元格（E2）全部包括在内。

2）打开“数据”菜单，选择“模拟运算”命令。

3）在弹出的对话框中单击“输入引用列的单元格”文本框，使文本插入符出现在该文本框中。

输入$B（或直接点选B3单元格）后退出（这一步的含义是指出将要引用“利率”列的单元格是$B）。

这时，在利率的右侧一列上已自动计算出对应所有不同利率的月付金额了。

可以看到，随着利率的升高，月付金额值也会有所提高（见图7-38中的单变量模拟运算表）。

2. 双变量模拟运算表上例是单变量的模拟运算。

还可以做双变量的模拟运算。

假设大家所关心的不仅是利率变化带来的影响，还关心还贷期限长短对月付金额大小产生的影响，那么可以把上面的模拟运算表改造一下。

如图7-39所示。

1）12，24，36，48等不同的期数放在表上方的行中。

2）最左上角单元格D2中引入计算公式（或将公式简化为“=B5”也可）。

3）选中整个区域后，打开“数据”菜单，选择“模拟运算”命令。

4）在“输入引用行的单元格”文本框中添入单元格地址B4（期数），在“输入引用列的单元格”文本框中添入单元格地址B3（利率）。

等额本息excel计算明细表摘要：1.等额本息的定义和计算方法2.Excel 在等额本息计算中的应用3.如何制作等额本息Excel 计算明细表4.实例演示：房贷等额本息计算正文：一、等额本息的定义和计算方法等额本息是一种按期定额偿还贷款本金和利息的方式。

在等额本息贷款中，借款人每个月支付的金额相等，包含一部分本金和一部分利息。

每月偿还的本金逐月递增，利息逐月递减。

等额本息贷款的计算公式为：每月还款金额= [贷款本金×月利率×(1+月利率)^还款月数]÷[(1+月利率)^还款月数-1]。

二、Excel 在等额本息计算中的应用Excel 作为一款功能强大的办公软件，可以轻松实现等额本息计算。

在Excel 中，我们可以使用内置的函数来计算等额本息，如PMT 函数、NPER 函数和RATE 函数等。

三、如何制作等额本息Excel 计算明细表1.打开Excel，在第一列（A 列）输入贷款期数，在第二列（B 列）输入每月利率，在第三列（C 列）输入贷款本金。

2.在D1 单元格输入以下公式：`=PMT(B2,C2,A2)`，按Enter 键。

这个公式将计算每月还款金额。

3.将D1 单元格中的公式向下填充至贷款期数结束的行，得到每月还款明细。

4.在E1 单元格输入以下公式：`=NPER(B2,C2,D2,0)`，按Enter 键。

这个公式将计算贷款的期数。

5.将E1 单元格中的公式向下填充至贷款期数结束的行，得到每期贷款的期数。

6.在F1 单元格输入以下公式：`=RATE(B2,C2,D2,E2)`，按Enter 键。

这个公式将计算每月利率。

7.将F1 单元格中的公式向下填充至贷款期数结束的行，得到每月利率。

四、实例演示：房贷等额本息计算假设某人贷款30 万元，年利率为4.9%，贷款期限为30 年，我们可以按照上述步骤在Excel 中输入相关数据，然后计算每月还款金额。

贷款期数（月）：1 至360每月利率：4.9%/12=0.004083贷款本金：30 万元按照上述步骤，在Excel 中输入公式并计算，得到每月还款金额约为1509 元。

模拟运算表是一个单元格区域，它可显示一个或多个公式中替换不同值时的结果。

有两种类型的模拟运算表：单输入模拟运算表和双输入模拟运算表。

单输入模拟运算表中，用户可以对一个变量键入不同的值从而查看它对一个或多个公式的影响。

双输入模拟运算表中，用户对两个变量输入不同值，而查看它对一个公式的影响。

模拟运算表是一组命令的组成部分，这些命令也被称作模拟分析工具。

使用模拟运算表即意味着执行模拟分析。

模拟分析是指通过更改单元格中的值来查看这些更改对工作表中公式结果的影响的过程。

例如，可以使用模拟运算表更改贷款利率和期限以确定可能的月还款额。

模拟分析工具的种类Excel 中包含三种模拟分析工具：方案、模拟运算表和单变量求解。

方案和模拟运算表根据各组输入值来确定可能的结果。

单变量求解与方案和模拟运算表的工作方式不同，它获取结果并确定生成该结果的可能的输入值。

与方案类似的是，模拟运算表有助于寻找一组可能的结果。

不同于方案的是，模拟运算表会在工作表中的一个表中显示所有结果。

使用模拟运算表可以轻松查看一系列可能性。

由于只关注一个或两个变量，表格形式的结果易于阅读和共享。

模拟运算表无法容纳两个以上的变量。

如果要分析两个以上的变量，则应改用方案。

尽管只能使用一个或两个变量（一个用于行输入单元格，另一个用于列输入单元格），但模拟运算表可以包括任意数量的不同变量值。

方案可拥有最多32 个不同的值，但可以创建任意数量的方案。

创建单变量模拟运算表单变量模拟运算表的输入值被排列在一列（列方向）或一行（行方向）中。

单变量模拟运算表中使用的公式必须仅引用一个输入单元格。

1.在一列或一行中的单元格中，键入要替换的值列表。

将值任一侧的几行和几列单元格保留为空白。

2.请执行下列操作之一：如果模拟运算表为列方向的（变量值位于一列中），请在紧接变量值列右上角的单元格中键入公式。

“概述”部分中所示的单变量模拟运算表插图是列方向的，公式包含在单元格D2 中。

用ＥＸＣＥＬ制作贷款投资经营表EXCEL是我们在进行财务管理时经常需要应用的的一个软件，其中里面的财务函数更是让我们的财务工作得心应用，解决了不少繁琐的计算问题。

本文就综合运用财务函数对一项投资进行比较恰当的预测和分析。

本例进行一项贷款投资预算，该项投资以9.5%的年利率贷款2500000购买一批机器进行生产加工，贷款期限为10年。

预计这批机器在这10年中产生的回报额及10年后的残值如图所示，现要通过财务函数计算这批机器的各期折旧值，贷款中各期的本金与利息，投资现值及报酬率，完成计算机后的表格如下：本文大致可分为3个部分来完成，首先，用SYD函数计算机课机器的各期折旧值勤，然后用IPMT和PPMT函数计算机课每期还款中的利息和本金，并统计各期的利期和本金之和及各期末的末还款金额，再用NPV和IRR函数计算机课该项投资的回报额及内部收益率。

1．计算机器折旧值这里用年限总和折旧法计算机课该项批机器在各年的折旧值勤，因此需要SYD函数，为了后面计算内部收益率的方便，在制作表格时列出了贷款当年的记录，为后面的内部收益率预留了一定的空间。

第1步，在EXCEL中制作如图所示的电子表格，并为各部分填表充不同的颜色以示区别。

第2步，选择G4：H14单元格区域，选择《格式》/《单元格》菜单命令，打开“单元格格式”对话框的“数字”选项卡。

在“分类”列表框中选择“货币”选项，将“小数位数”数值框中的值设为“2”，在“货币符号”下拉列表框中选择“￥”符号，在“负数”列表框中选择为带符号的黑色文字，然后单击“确定”按钮。

然后在G4单元格中输入“2500000”，按[Enter]键确认后，数值自动变为”￥250,000.00.”第3步，单击C5单元格，输入公式“=SYD($B$2,$H$2,$D$2,YEAR(B5)-2006)”，按[Ctrl+Enter]键计算出使用一年后机器的折旧值.然后用鼠标指针拖动C5单元格右下角的填表充柄至C14单元格处释放,计算出各年的机器折旧值。

假设要取得一笔30年，金额200000的抵押贷款，如果利率6%-8.5%，每月需支付多少呢？贷款额解1200,000￥555.56=PMT(A4/12,360,-C3)6.00%6.50%7.00%7.50%8.00%8.50%解2￥555.566.00%6.50%7.00%7.50%8.00%8.50%[表]函数的格式如下：‘=表(输入引用行的单元格,输入引用列的单元格)有关解释EXCEL从不改变A4的值,所以占位符可设在数据表区域之外的任意单元格中.为什么要选A4呢?当然也可选任意单元格,但关键要和输入引用列的单元格保持一致.由于A4是空的,所以函数返回一个无用的结果¥555.56;在利率为0时,每月需分期偿还的金额.实际上A4是占位符,通过它EXCEL可将值送到数据源区域中.1、在B5:B10单元格，输入可能的利率。

并在C4单元格输入公式 =PMT(A4/12,360,-C3)2、选中B4:C10单元格。

【数据】-【模拟运算表】3、弹出的对话框中，在“输入引用列的单元格”框中单击，再用鼠标选中A4单元格并单击。

4、单击“确定”按钮，完成操作。

选中C5:C10任一单元格，编辑栏均显示单元格内容为“=表(,A4)”2、步骤3的设置告诉EXCEL本次计算只有1个变量（即利率）,这个变量存放在B列，所以在步骤3中，A4是“引用列的单元格”，而不是“引用行的单元格”，同时因为只有1个变量，要把“引用行的单元格”留空。

3、不要去关心A4单元格为什么是空的，EXCEL已经得到了足够的信息来生成用户需要的结果。

如果觉得C4单元格的公式破坏了表格的可读性，可以把这个单元格设置为白色字体。

1、步骤1和步骤2向EXCEL告知2件事情。

一是模拟运算表的表格区域；二是本次计算要计算的是每月还款额，以及每月还款额如何计算得到的操作步骤模拟运算表的计算过程。

模拟运算表固定利率的付款计算:
PMT 函数计算在指定时期内分期偿清一笔贷款所需的定期付款额,该函数的形式为:=PMT(RATE,number of periods,present value,future value,type)
RATE 贴现率或利率
number of periods 投资期限
present value,当前投资额
future value 投资期限结束时的值（忽略为０）
type 支付时间标识（忽略为０）０＝周期结束　１＝周期开始例题：
首先８％除以１２，获得月利率
然后30×１２，将周期转换为月
最后将月利率,周期数,贷款额代入公式
¥-1,467.53=PMT(8%/12,30*12,200000)
实战例题:
开放式竞赛[基础27]问题
银行中的利息计算起来非常的繁琐,让多数没有学过专业财会方面的人都感到束手无策,比如在银行方面的住房贷款及个人储蓄等方面.而EXCEL 中的[数据]>[模拟运算表]命令,完全可以让你解除这方面的烦恼.
选择[数据]菜单下的[模拟运算表]命令可以创建两种数据表:
1.基于输入单变量的数据表,可用多个公式来检验变量的影响.
2.基于两个输入变量的数据表中,只能用一个公式来检验变量的影响.
假设要取得一笔30年，金额200000的抵押贷款，如果利率８％，每月需支付多少呢？
可计算出的每月抵押贷款的还款额为¥-1467.53
(因为是一项成本支出,所以结果为负)
开放式竞赛[基础27]答案
一、基于单输入变量的数据表:二、基于两个输入变量的数据表:引用列的单元格
引用行的单元格
by:bin_yang168。