|
在上一期，我们介绍了三个财务应用类的Excel函数——FV、PV和PMT，所涉及的案例是投资的计算。本期我们继续介绍PMT函数应用于贷款的还款额计算。 如下图所示，我们设计了一个关于贷款还款额的计算模型：
01 我们需要根据所提供的信息，计算出C15单元格中的“Monthly Payment（月还款额）”。 还款频率可以是按照每月还款，也可以按照每年还款。
我们先要计算出“Periods Per Year”：在C12单元格中输入函数IF，如果还款频率为“每月”，则得到每年还款期数为12，否则为1（即按照每年还款频率）。
在得到每年的还款期数的基础上，我们就可以计算出总的还款期数，在C13单元格中输入公式如下：每年的还款期数乘以总的还款年限。
同理，我们也可以计算出每期的还款利率，在G7单元格中输入公式如下：年利率除以每期的还款期数。
我们现在知道了还款额、每期还款利率、还款总期数，可以在C15单元格中通过PMT函数，计算出每月应还款的数额。
此类PMT函数中的两个可选参数，都不会用到，所以在此忽略即可，按Enter键后，即可返回结果。
因此处为还款额，所以符号用了“-”，如果我们不想看到此符号，在PMT函数前加上一个“-”（减号）即可。
在“Monthly Payment”的基础上，我们可以计算出“Projected Interest”，在G8单元格中输入公式如下：月还款额乘以总还款期数，再减去本金。
02 在下面的数据表格中，我们设计了一个还款的时间表。最开始的还款额为C7单元格中的贷款数额，首个还款日为C11单元格中的日期。
在首日还款日的基础上，我们计算出每期还款的时间，所用到的函数是EDATE，需要注意的是此例中EDATE函数的第二个参数需要根据C10单元格中的还款频率来确定，故我们通过IF函数来判定，如果是“Monthly”，则时间往后添加一个月，如果是“Annual”，则往后加12个月。
要确定最后一个还款日，我们需要根据“Balance”来判断，即当还款余额为0时，就无需再添加还款日了。因此在EDATE函数前再使用IF函数：当G20（上一还款日的余额小于等于0，当前还款日为空，否则继续执行EDATE函数）。
按Enter键后，B21单元格中返回为空，因G20单元格数据为0，但我们仍通过快速填充复制此公式。
在C20单元格中，我们来计算到期应付款项，所用的函数是IF，如果还款余额（Balance）加上利息，小于每期应还款额，则只需支付还款余额加上利息，否则应还款为“Monthly Payment”。
在E20单元格中，计算出每期应还的利息：本金乘以利率。
在F20单元格中，计算出到期应还本金：到期应还款额减去利息，再加上任何额外已付款项。
在G20单元格中，计算出剩余应还款额：上一余额减去每期应还本金。
选中F20和G20单元格，使用快速填充功能完成数据填充。
如果我们在第一个月有额外还款，所有的相关数据均会进行重算。
最后，我们也可以计算出G11单元格中的“预估节省的利息”：预估利息减去实际的总利息。
通过以上的案例，我们可以在Excel中利用财务类的函数以及其他的一些方法来设计一个贷款还款的计算模型以及时间表。其中重要的是，理解计算的过程所涉及到的相关参数，找到其间的互相联系与逻辑，以便我们在进行数据的运算或处理时更加得心应手。 |
|
|
