目录
- 了解您的抵押
- 计算每月付款
- 计算年利率
- 确定贷款期限
- 分解贷款
- Excel中的贷款计算
- 贷款摊销
- 创建贷款时间表
偿还贷款是一种偿还贷款的行为，通常是通过一系列包括本金加利息在内的定期付款来偿还以前从贷方借来的钱。 您知道您可以使用软件程序Excel计算您的还款额吗？
本文是设置贷款计算的分步指南。
重要要点
- 使用Excel通过确定每月还款额，利率和贷款时间表来处理抵押贷款问题。您可以使用excel更深入地查看贷款明细，并创建适合您的还款时间表您可以调整每一步的计算以满足您的特定需求，逐步减少并逐步检查您的贷款可以使还款过程变得不那么繁琐且更易于管理。
了解您的抵押
使用Excel，您可以通过三个简单的步骤更好地了解抵押贷款。 第一步确定每月付款。 第二步计算利率，第三步确定贷款时间表。
您可以在Excel中构建一个表，该表将告诉您利率，贷款期限的贷款计算，贷款的分解，摊销和每月还款额。
计算每月付款
首先，这里是计算抵押贷款每月付款的方法。 使用年利率，本金和期限，我们可以确定每月要偿还的金额。
如上面的屏幕快照所示，公式编写如下：
= -PMT（rate; length; present_value;;）
PMT前面的减号是必需的，因为该公式返回负数。 前三个参数是贷款利率，贷款期限（期数）和借入的本金。 最后两个参数是可选的，剩余值默认为零；默认值为0。 也可以选择预先付款（一）或末尾（零）。
用于计算贷款的每月还款额的Excel公式为：
= -PMT（（1 + B2）^（1/12）-1; B4 * 12; B3）= PMT（（1 + 3, 10％）^（1/12）-1; 10 * 12; 120000）
说明：对于利率，我们使用月利率（利率期限），然后计算期数（10年的120乘以12个月），最后，我们指出本金。 我们的月付款在10年内将为$ 1, 161.88。
计算年利率
我们已经看到了如何设置按揭每月付款的计算。 但是我们可能希望设置一个我们可以负担的最大每月还款额，该最高还款额还显示了我们必须偿还贷款的年限。 因此，我们想知道相应的年利率。
如上面的屏幕快照所示，我们首先计算期间费率（在我们的示例中为每月），然后计算年费率。 如上面的屏幕快照所示，使用的公式为RATE。 内容如下：
= RATE（Nper; pmt; present_value;;）
前三个参数是贷款的长度（期数），偿还贷款的每月还款额以及所借的本金。 最后三个参数是可选的，剩余值默认为零；默认值为0。 提前（对于一个）或最后（对于零）管理到期期限的术语参数也是可选的。 最后，估计参数是可选的，但可以给出速率的初始估计。
用于计算贷款利率的Excel公式为：
= RATE（12 * B4; -B2; B3）= RATE（12 * 13; -960; 120000）
注意：每月付款中的相应数据必须带有负号。 这就是为什么公式前面有一个负号的原因。 利率期限为0.294％。
我们使用公式 =（1 + B5）为12-1 ^ =（1 + 0.294％）^ 12-1 来获得我们的贷款年利率3.58％。 换句话说，要在13年内借入120, 000美元，每月支付960美元，我们应该以每年3.58％的最高利率协商贷款。
使用Excel是跟踪欠款并提供还款时间表的一种好方法，该时间表可以最大程度地减少您可能欠的任何费用。
确定贷款期限
现在，当您知道年利率，借入的本金以及将要偿还的每月还款额时，我们将了解如何确定贷款期限。 换句话说，我们需要多久才能还清利率为3.10％的每月$ 1, 100的抵押贷款$ 120, 000？
我们将使用的公式为NPER，如上面的屏幕截图所示，其编写方式如下：
= NPER（rate; pmt; present_value;;）
前三个参数是贷款的年利率，偿还贷款所需的每月还款额以及本金。 最后两个参数是可选的，剩余值默认为零。 预付（一付）或末付（零付）的术语自变量也是可选的。
= NPER（（1 + B2）^（1/12）-1; -B4; B3）= NPER（（1 + 3, 10％）^（1/12）-1; -1100; 120000）
注意：每月付款中的相应数据必须带有负号。 这就是为什么公式前面有一个负号的原因。 偿还期为127.97个周期（本例中为月）。
我们将使用公式= B5 / 12 = 127.97 / 12来完成偿还贷款的年数。 换句话说，要以年利率3.10％借入120, 000元，并每月支付1, 100元，我们应偿还128个月或10年零8个月的到期日。
分解贷款
贷款支付包括本金和利息。 计算每个时期的利息-例如，十年内每月还款将给我们120个时期。
上表使用PPMT和IPMT公式显示了贷款的细分（总期限等于120）。 这两个公式的参数相同，并且分解如下：
= -PPMT（rate; num_period; length; principal;;）
除了“ num_period”外，参数与已经看到的PMT公式相同，该参数被添加以显示在给定本金和利息的情况下分解贷款的期限。 这是一个例子：
= -PPMT（（1 + B2）^（1/12）-1; 1; B4 * 12; B3）= PPMT（（1 + 3, 10％）^（1/12）-1; 1; 10 * 12; 120000）
结果显示在分析期间的“贷款分解”上方的屏幕快照中，该屏幕截图为“一个”。 即第一期或第一个月。 我们将支付1, 161.88美元，分为856.20美元本金和305.68美元利息。
Excel中的贷款计算
还可以计算多个时段（例如前12个月或前15个月）的本金和利息偿还额。
= -CUMPRINC（rate; length; principal; start_date; end_date; type）
我们找到了在第一部分中使用公式PMT看到的参数，比率，长度，本金和术语（必填）。 但是在这里，我们还需要“ start_date”和“ end_date”参数。 “ start_date”指示要分析的时段的开始，而“ end_date”指示要分析的时段的结束。
这是一个例子：
= -CUMPRINC（（1 + B2）^（1/12）-1; B4 * 12; B3; 1; 12; 0）
结果显示在屏幕截图“第一年累计”中，因此分析的时间段从第一时间段（第一个月）到第十二时间段（第12个月）的1到12。 一年多的时间里，我们将支付$ 10, 419.55的本金和$ 3, 522.99的利息。
贷款摊销
先前的公式使我们可以逐段创建时间表，了解我们每月将支付本金和利息的金额，以及尚需支付的金额。
创建贷款时间表
要创建贷款时间表，我们将使用上面讨论的不同公式，并在期限内扩展它们。
在第一个期间列中，输入“ 1”作为第一个期间，然后将单元格向下拖动。 在我们的例子中，我们需要120个周期，因为10年期贷款偿还乘以12个月等于120。
第二列是我们每月需要支付的每月金额-在整个贷款计划中保持不变。 要计算金额，请在第一个期间的单元格中插入以下公式：
= -PMT（TP-1; B4 * 12; B3）= -PMT（（1 + 3, 10％）^（1/12）-1; 10 * 12; 120000）
第三栏是将每月偿还的本金。 例如，在第40期，我们将按每月总金额1, 161.88美元偿还本金945.51美元。
为了计算赎回本金，我们使用以下公式：
= -PPMT（TP; A18; $ B $ 4 * 12; $ B $ 3）= -PPMT（（1 + 3, 10％）^（1/12）; 1; 10 * 12; 120000）
第四列是利息，我们使用公式计算每月的还款本金，以了解要支付多少利息：
= -INTPER（TP; A18; $ B $ 4 * 12; $ B $ 3）= -INTPER（（1 + 3, 10％）^（1/12）; 1; 10 * 12; 120000）
第五列包含尚待支付的金额。 例如，在第40次付款后，我们将不得不为120, 000美元支付83, 994.69美元。
计算公式如下：
= $ B $ 3 + CUMPRINC（TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0）
该公式在包含借入本金的单元格之前的一段时间内使用本金组合。 当我们复制并向下拖动单元格时，此时间段开始改变。 下表显示，在120期末我们的贷款已还清。
