当前位置：首页 > Excel小技巧 > EXCEL中如何计算比较贷款支付功能和实现-Excel学习网

EXCEL中如何计算比较贷款支付功能和实现-Excel学习网

2019年10月22日 20:59 发布来源：Excel学习网

抵押贷款人为您提供多种利率和摊销期限的选择。该工作簿可帮助您从中选择。

当您比较抵押贷款的付款方式时，您的选择可能会无所适从...

在圣艾夫斯时，我找到了家，

然后看了七种贷款。

但是每笔贷款都有七个计划。

每个计划有七个学期。

每个学期有七个比率；

有了所有这些利率，条款，计划和贷款，

我改变了主意并租了房。

-查理·基德（Charley Kyd），1982年

减少这种混乱的一种方法是在Excel中分析您的还贷选项。我最近发现了我在购买当前房屋时创建的工作表。我把它打扮了一下，并更新了数字。您将在下面看到它的图像。

我的初始贷款分析

贷款付款计算器最初，我设置了这个贷款计算器，让我可以比较我最关心的两个项目：每月还款额和支付的利息总额。

我在单元格C3中输入贷款金额，并将名称LoanAmt分配给该单元格。为此，我选择了范围B3：C3，按Ctrl + Shift + F3启动“创建名称”对话框，确保仅选中了“左列”，然后选择“确定”。

因为我希望列C的宽度比显示大数目的列要窄，所以我合并了单元格C3和D3。为此，我选择了范围C3：D3，按Ctrl + 1启动“设置单元格格式”对话框，在“对齐”选项卡中选择“合并单元格”，然后选择“确定”。

为了找到第一笔付款金额，我在显示的单元格中使用了此公式：

C5： =-PMT（$ B5 / 12，$ A5 * 12，LoanAmt）

PMT函数具有以下语法…

= PMT（rate，nper，pv，fv，type）

…所以在单元格C5中…

rate是单元格B5中的年利率除以12即可得出月利率。

nper是单元格A5中的年数乘以12可得出每月摊销期的数。

pv是贷款金额。

其他两个参数是可选的，可以忽略。

为了找到支付的总利息，我在显示的单元格中使用了以下公式：

D5： = $ C5 *（$ A5 * 12）-LoanAmt

在这里，支付的利息总额等于贷款期限内的总付款额减去贷款金额。

然后，我将范围C5：D5复制到单元格C6。

我的扩展贷款分析

当我寻找更多抵押贷款选择时，我发现自己正在比较许多不同的结果。因此，我没有写下来，而是在工作表中添加了四个表。

每月付款方式该图显示了第一张表。在这里，我在第11行输入了各种利率，在A列输入了各种摊销期。

我突出强调了中间值是主要选择，而其他值则是要考虑的选项。

这是显示的单元格的公式：

B12： =-PMT（B $ 11/12，$ A12 * 12，LoanAmt）

该公式使用与以前的PMT公式相同的逻辑。我将其复制到范围B12：F16。

总利息支出接下来，我想通过查看每个选项支付的利息总额来比较支付选项，如下所示。

为此，我想建立一种能力来测试我对房子可以住多久的假设，该时期与摊销期不同。

因此，年贷款寿命单元格M9包含假定的年限。但是我从99年的价值开始，这使我可以在需要时忽略贷款期限的设置。（您可以在以下公式中查看其工作原理。）

这是显示的单元格的公式：

I12： =-CUMIPMT（I $ 11/12，$ H12 * 12，LoanAmt，1，MIN（$ M $ 9，$ H12）* 12，0）

CUMIPMT函数查找指定期间的累计利息。这是它的语法：

= CUMIPMT（rate，nper，pv，start_period，end_period，type）

所以在单元格I12中...

rate是I11单元格中的年利率除以12

nper是单元格H12中的年数乘以12

pv是LoanAmt

start_period为1，因为我正在查看每笔贷款的开始时间

end_period等于以年为单位的贷款期限或以年为单位的摊销期限（以最小者为准）乘以12。

type等于0，Excel告诉我们这是期末计算的设置。

15年的总利息支付

我将此公式复制到范围I12：M16，然后通过将贷款寿命更改为15年来对其进行了测试，如下所示。

如您所见，与上一个数字相比，在25年和30年的摊销期内，利率差异不那么显着。但是即使这样，如果我要在30年内摊还这笔贷款，同时又要在15年内还清这笔贷款，这种选择会使我损失76,169到115,806之间的差额，即近40,000美元。

最后，在我的分析中，我希望能够看到随着利率和摊销期限的变化，我每月的付款会有何不同。因此，我设置了以下两个表：

贷款的利息和期限期权之间的差异

例如，在第一张表中，如果我有15年的贷款，如果我可以将利率从3.125％降低到3％，我的付款每月就会减少18美元左右。

在第二张表中，如果我的贷款利率为3％，那么如果我可以将我的贷款期限从15年延长为30年，我的付款将减少807美元。

这些表中的公式仅显示上方标题为“每月付款选项”的表中各个单元之间的差异。

最后的警告...不要完全依靠此工作表来选择特定的贷款。其计算和假设可能与您的特定情况不符。因此，请务必与您的贷方或财务顾问重新确认工作簿的关键假设和付款方式。

原创文章如转载请注明：转载自Excel学习网 [ http://www.excelxx.com/]
需要保留本文链接地址：http://www.excelxx.com/jiqiao/10742.html

分享到：

上一篇：住房贷款还款方式的比较-Excel教程-Excel学习网下一篇：excel技巧：如何使用图表XY 散点图的乐趣-Excel学习网