如何创建自己的贷款摊还计算器|

大多数人认为创建 贷款有一些巫术魔法摊销时间表。但我很高兴地告诉你，没有 - 甚至更好 - 即使是初学者也可以使用Microsoft Excel轻松创建一个。

贷款摊销表为您提供了很多好的信息：支付给贷方的总金额，利息支付部分，本金支付部分和未偿还贷款余额。

贷款摊销时，仅表示将利息付款和本金付款合并为一次定期付款，并且付款金额在每个付款期限内均相等。但是，分期偿还贷款最有意思的特征之一是，每次付款时，支付的利息和本金的金额都会发生变化。

Excel是用于计算您自己的摊销表的非常有用的工具。你甚至可以试验不同的投入，看看不同的利率，还款期和主要贷款额是如何影响你将要支付的贷款。

让我们看一个例子来说明这个过程。

假设您刚购买了2万美元的贷款，必须在4年内完全偿还。贷款的年利率为3％。由于贷款是分期偿还的，因此每月付款全部是相同的金额。

第一步是计算每月付款金额。这可以使用Excel中的PMT（代表“付款”）功能完成。

首先制作新的电子表格并输入以下支付金额计算所需的数据：

A列将包含以下数据标签：

A1 =原始贷款本金（您的贷款金额）

A2 =年限贷款期限

A3 =年利率

A4 =每年支付的数量

A5 =支付金额（我们正在计算的）

B列将包含A列中每个标签的相应值：

B1 = $ 20,000

B2 = 4

B3 = 3％

B4 = 12

B5 =我​​们要查找的值

Excel的PMT功能可以在财务功能菜单中找到。首先选择单元格B5，然后单击公式选项卡下的金融功能菜单，然后从列表中选择PMT功能。将显示一个框，您应该从电子表格中输入数据，就像它在此图像中一样：

这里有几个重要的事项需要注意。首先，我们需要进行两项重要调整，因为我们正在计算 每月 付款。我们需要将我们的年利率除以12得到月利率（利率），我们需要乘以4乘以12才能得到还款期（Nper）的月总数。

第二，现值（PV）输入为负值，因为该金额欠贷方。

一旦您的箱子被填入，点击“确定”。

您的电子表格现在应该如下所示：

现在我们将开始构建摊销表格

在第1部分中制作的电子表格下跳过一行，然后从第7行开始，执行以下操作从A列开始到E列结束的标签：

A7 =月

B7 =付款

C7 =利息

D7 =本金

E7 =余额

这些是五个分期付款表的标题

现在是开始填写表格的时候了。第一步是输入贷款期限的月数。在“月”标题下，在单元格A8（发放贷款的时间）中键入“0”。按照顺序依次填入后面的单元格（通过编号48）（这将使单元格A56）。一招：一旦你输入了0，确保选中了单元格（A8），然后将单元格的右下角拖到第56行。然后从出现在右侧的小方框中选择“填充系列”最后一个单元格。这应该自动填充48个月的数字系列。

接下来，所有的付款都可以填写，因为每个月付款的价值已经确定。从第1个月开始（第0个月没有付款，因为付款发生在每个月末），请填写1-48每个月的价值$ 442.69。在此之后，利息和本金金额可以使用各自的Excel函数进行计算。

使用Excel函数计算付款的利息和主要部分非常简单，可用于填写整个分期付款表。要计算利息支付，请选择单元格C9，然后单击公式选项卡下的财务功能菜单。从列表中选择IPMT（用于“利息支付”）功能。将显示一个框，您应该从电子表格中输入数据，就像它在该图像中一样：

注意 ：单元格字母和数字之间的“$”符号对于我们复制时公式直到后来的剩余单元格。 “$”锁定了引用的单元格，因此当我们将它们复制到其余单元格时，这些公式将保持不变。您可以通过按F4锁定单元格（并查看“$”）。

接下来，我们可以计算付款中的本金金额。与IPMT计算类似，选择单元格D9，然后单击公式选项卡下的财务功能菜单。从列表中选择PPMT（用于“本金支付）”功能。将显示一个框，您应该从电子表格中输入数据，就像它在此图像中一样：

注意 ：委托人可以可以用这个函数计算，或者简单地从总支付额中减去利息额，同样，如果我们先用PPMT函数计算出本金，然后从总支付中扣除那个价值，那么可以计算利息。要知道如何使用两个公式，这也可以让表格的其余部分更有效地填写，如下所示：

最后一列（E列）是剩余余额列，可以很容易地计算出来现在每个时间段我们已经确定了利息和本金的数额，0月份还没有付款，所以余额是$ 20,000，但是我们想要命令Excel自动计算每个期间的余额，所以不是马努盟友输入“$ 20,000”，输入“= B1”，参考上表进行支付计算。对于第1个月，由于余额是按当前月份的本金减去上一个月的余额计算的，请在单元格E9中输入“= E8-D9”。余额将自动计算。您的分期付款表现在已经在第1个月完成了，应该看起来像这样：

通过简单地选择单元格B9-E9，复制选择内容，然后将选择内容粘贴到单元格中，即可填写剩余月份的所有数据B10-E10。由于我们已经为每个单元格输入了公式，并锁定了将在每次计算中使用的单元格，因此会自动计算利息，本金和余额的值。看到第2个月的工作，整个表格可以通过在第48月简单地选择B-E列并粘贴已经复制的信息来完成。 Excel现在使用我们输入的公式计算分期偿还表的每个值。您的填表应该如下所示：

贷款的首年：

贷款的去年：

注意 ：这些表格仅包含第一年和最后一年摊销表。如果您有兴趣计算固定利率抵押贷款的抵押贷款支付，您可以使用这些步骤自行制作摊销表，然后在这两个表格之间形成一个连续的摊销表，或者你可以使用我们的金融计算器来为你做。我们的按揭计算器会显示您的每月付款以及完整的摊销时间表。