[分享][原创]通用型贷款明细计算表

chrisfang

我制作了一个通用性较强的贷款明细计算工具，可以分析等额还款或等本还款的每月明细情况，另外还支持多达五次调息和一次提前还贷的计算。希望能为大家的贷款投资计划提供一些参考。 同时，本工具的制作也采用了大量的Excel基础应用，包括数据有效性、条件格式、单元格格式设定以及公式函数等，希望能够为大家在Excel的基本功能及公式应用方面拓展一些新的思路，提供一些借鉴。 为了避免使用中对表内公式设置造成无意的破坏，本工具设置了保护和只读密码，密码为excelhome，在研究查看时可凭此密码打开权限。本工具打开时会询问是否运行宏，请选择Yes，允许宏的运行（只有控件所包含的代码，没有危害或危险）。如果没有跳出这样的询问窗口，请事先在Excel的工具——宏——安全性中，将安全级别选为“中”，然后再打开本工具。 详细使用方法可详见附件中的使用说明文档。 ZDL2YUyK.rar (222.82 KB, 下载次数: 4863)

2006-4-11 09:55 上传

点击文件名下载附件
[分享][原创]通用型贷款明细计算表

[此贴子已经被作者于2006-4-12 8:32:03编辑过]

chrisfang

通用型贷款明细计算表（函数公式版）使用说明 工具特性： 本工具采用Excel2000制作，除极少量控件和相关VBA代码外，所有功能基本全部由Excel本身的基础应用（包括条件格式、单元格格式、数据有效性等）和大量的函数公式来实现完成，无需加载额外的分析工具库等库文件。 此贷款工具主要用于房贷还款计算，以等额还款方式和等本还款方式（即递减还款方式）为主要计算基础模型，以每月明细表格和阶段汇总相结合的方式直观地显示出了贷款期间任何时间段的偿付本金和偿付利息具体金额以及剩余的借贷本金。 本工具还提供了最多达5次的调息计算和1次提前还款的计算。可以对贷款期间银行（或公积金）利率变化及提前还款等操作作出及时的反映。其中提前还款部分目前只支持“减少还款额度”的方式，还不支持“缩短还款期限”的方式。（关于提前还款部分，此说明中相关部分有更详细的解说。）对比目前网上的一些现有的贷款计算工具，其中大部分都缺乏每月的明细数据分析，也比较少含有多次调息计算的功能。除了房贷计算以外，此工具也可推广到其他类似的以月为计息和还款单位进行操作的贷款项目。 因为此版本为函数公式版，所以在开发制作中尽量使用了函数公式及基础应用来实现相关功能，除了为使外观界面相对简洁美观采用了两个复选框及其相关代码外，不留任何其他VBA内容。当然，类似这样的工具如果用VBA代码或是VB、VC等软件开发工具来做可能会简单许多，功能也更完善，但制作现在这样一个版本主要是为了尽量发掘Excel本身自带的已有功能，也是为了使大家能够在更深入了解和使用Excel基本功能及公式应用方面拓展一些思路，提供一些借鉴。未来也计划制作此工具的VBA版本，相关功能也将更丰富更完善些。 适用对象： 1， 还未贷款者，可以使用此工具进行未来贷款的规划和预测分析，为选择适合自己的贷款方式、进行规避利率风险的有效操作提供了借鉴。 2， 已经贷款者，一方面可以动态的观察自己贷款的还款情况，分析利息和本金之间的比例关系，观察历次调息对贷款整体的影响。另一方面也可以使用此工具进行未来提前还款操作的预测分析，为选择可承受的还款时间和方式、进行规避利率风险的有效操作提供了借鉴。 贷款说明： 目前主流的贷款方式基本分为等额还款和等本还款，这两种方式也是本工具所涉及的两种方式。 等额方式：每月偿付本金和利息的总额是相等的。在没有调息和提前还贷的前提下，每月偿付的本金逐月递增，并呈一等比数列（等本数列的公比为月率+1），而每月偿付利息逐月减少。（关于等额方式的算法有兴趣的朋友可以看一下说明：算法说明） 等本方式：每月偿付的本金相等的。在没有调息和提前还贷的前提下，每月偿付利息逐月减少（呈一等差数列），相应的，每月偿付的本息合计也逐月减少，所以此种还款方式又称之为递减还款。（关于等本方式的算法有兴趣的朋友可以看一下说明：算法说明） 关于组合贷款：贷款根据不同的借贷来源通常可分为公积金贷款和商业贷款，有单独采用以上两者之一的，也有同时采用两者相组合的方式叫做组合贷款。公积金贷款和商业贷款的利率是不同的，所以在使用本工具时，如果采用组合贷款的，要将公积金贷款和商业贷款分开进行计算，这在后面的使用说明中有更详细解说。如果采用组合贷款方式，在提前还贷操作时，根据不同银行的要求不同通常也有几种不同的方式：一种是提前还贷的款项必须按比例分摊到公积金和商业贷款上，另一种是可以自主选择提前还款的是公积金部分还是商业贷款部分。由于存在这样的差异，所以同样也要求在使用工具时将公积金部分和商业部分分开计算，如果有提前还贷操作，也需要预先分配出各自的还贷部分。 关于利率：利率分年率和月率，一般情况下通常所称的房贷利率均指年率。但因为房贷是按月计息、按月还款的，所以计算时我们经常要用到月率，月率就等于年率除以12。本工具中，用户只需手动输入所采用的年率，其月率会在公式中自行换算。另外，年率通常表达为百分之几，比如4.5%、5.508%等，在本工具中，我们约定以百分之一为单位，输入年率时只需输入4.5和5.508即可，公式中会自动除以100。 关于调息：央行会不定期的调整房贷利率，包括公积金的利率和商业贷款的利率。就近期来说，2005年之前原有一个利率（暂时称为利率A），2005年1月1日起实现了一次新的利率（暂时称为利率B），2005年3月17日又进行了一次新的调息（暂时称为利率C）。对于2005年3月17日之前已经贷款的，2005年之前使用利率A，从2005年1月1日起实行利率B，然后在2006年1月1日起采用利率C。对于2005年3月17日之后贷款的，自贷款起就采用利率C。简单言之，对于已经贷款者来说，调息会在政策发布的下一年年初时执行；而对于新贷款者来说，就执行最近政策所发布的利率。本工具利率查询中包含了近几年的调息利率，可供参考，但注意不同地区不同银行可能存在差异。 关于提前还款：提前还款通常也有两种方式，一种是选择每月的还款额度不变，缩短贷款期限。另一种是选择保持贷款期限不变，减少每月的还款额度。本工具目前只支持后面一种选择。另外由于部分银行在提前还款时要进行日息的计算，下面作一个比较详细的介绍和解释： 通常贷款的计息都以月为单位，还款结算也以月为单位。但部分银行（未作全面了解），在客户进行提前还贷操作时会对日息进行计算（银行始终是最精明的！）打个比方，每月的贷款结算日比如为20号，每到20号银行就会从你的卡上扣除这个月的应付本金和应付利息，此时扣除的应付利息实际上是从上个月20日开始至本月20日所产生的贷款利息。如果你在第八个月的10号进行了提前还款的操作，银行会认为你从第七个月20日开始至第八个月10号之前的应付利息已然产生，仍需照以前一样支付，而10号之后的的利息才是根据你还款之后新的本金计算而得的。所以在这个月里，银行是按天算的利息。这个月的结算金额也比较复杂，有多种形式：有的银行会让客户将上月20日至本月10日之间的这部分利息与提前还款额合在一起进行支付，当月结算的就是剩下10天的利息加上本金部分（本息合计相对后面几个月要少些）；有的会把两部分利息合在一起结算在当月还款中（这样本息合计相对后面几个月要多些）。由于这部分情况比较复杂，所以在本工具中，并没有去像部分银行一样计算日息，而是仍旧按照月息进行计算，相当于默认下面这种情况：如果是第八月提前还款，就认为是第七个月的二十一日进行了还款操作，第八月的利息为新产生的利息。如果是第九月提前还款，就认为是第八月的二十一日进行了还款操作，第九月的利息为新产生的利息。所以大家在使用本工具的时候，填写提前还款日期的时候请注意，约定以还款后的那个结算日所在月为还款当月。 也是由于上面的这个原因，本工具中等额还款方式在进行提前还款计算时，可能会跟实际情况稍有出入，但一般这个差值并不会太大。如果把银行要求一起支付的那部分日息和提前还款部分合在一起输入在提前还贷金额栏内，会有助于减少这个误差。未来如果制作此工具的VBA版本，会尽量把这种复杂的情况也包含在内。 工具使用说明： 本文件打开时会询问是否运行宏，请选择yes，允许宏的运行（只是控件所包含的代码，没有任何危害或危险）。如果没有跳出这样的询问窗口，请事先在Excel的工具——宏——安全性中，将安全级别选为“中”。然后再打开本工具。 打开后界面如下图： 默认打开的是等额还款的计算工具，如果要切换到等本计算，可在图中1处点击等本还款的页面链接。 在进行计算前，先要在图中2处输入贷款金额（以万元为单位），选择贷款年限（只能在下拉框里选择，最大为30年），输入初始的贷款年率（年率只需输入百分比前的数字，工具会自动除以100）。如果你使用了组合贷款，请将公积金和商业贷款分开成两次分别进行计算。也可以将本工具改名后存为另一个副本，同时在Excel里面打开两个窗口，分别计算公积金和商业贷款。 如果不清楚目前的年率，也可在图中1处点击利率查询的页面工具进行查询或换算。 三个贷款的主要参数输入后，在图中3处已经出现了每个月的明细数据，上方的黑体字则是贷款期的合计统计。在图中6处可通过下拉框选择统计前多少个月的合计情况。 如果有要进行提前还款计算，请在图中5处的小方框内打勾。 如果要进行经历调息的计算，则在图中4处的小方框内打勾。 见下图： “提前还款”前打勾后，会要求用户输入还款日期，格式为贷款开始的第几个月，具体定义可参见前文。还要输入还款金额，同样以万元为单位。此两项信息输入后，会在下面详表里出现提前还款月的标识，用户可以核对一下是否确实是在那个月，还款后的计算结果也同时实时生成。在涉及提前还款的计算时，其结果与实际银行结算情况可能存在一些出入，原因如前文所述。 “是否调息”前打勾后，会首先要求用户输入贷款的起始时间，某年某月，就是银行第一次开始扣款的所在月份。然后下面会自动出现第一次调息的信息框，要求用户输入第一次调息年份和调息后利率。调息的月份通常都默认为调息年的一月份，利率输入格式与前相同。调息日期请不要比贷款起始日期还早，否则工具会提示错误。第一次调息年率输入后，会自动显示第二次调息的输入框，如果你没有接下来的第二次调息，请将第二次调息的年率保持为0。如果你要进行第二次调息的计算，同样可以依照前面输入第二次调息的相关信息。总之，调息信息要按顺序输入，不可跳跃次数。如果不需要后面的几次调息计算，可将它的利率改为0。如果需要将所有调息信息重新更改再次进行计算，把上面的勾重新勾选一下也是一种不错的选择。调息信息输入后，下方详表内同样会产生调息月的标识，以便查看。 只要信息输入完全，下面的详表内会实时反映数据，所见即所得，不需要再点其它按钮。等本还款的工具使用方法也与上面方法一样。 有些用户可能对自己的贷款利率不是很清楚，一般可以咨询贷款银行或查询当地的房贷网站，工具的利率查询页面里也为大家提供了换算工具以便参考。只要你知道你首月的本息合计还款金额，就可以通过工具换算得到你的贷款初始利率。如果经历了调息，想要知道调息后新的利率，则需根据调息和提前还款的情况提供更多信息来进行换算。白色单元格一般都是需要输入相关信息的地方。另外，此页面的表头上，还为大家罗列了一些近期的公积金和商业贷款利率，但由于地域和银行的不同，此利率也可能不尽相同，所以仅供参考。 整个工具的界面和使用其实是比较简单的，可能需要注意的是数据输入的格式。本工具在制作中已经应用了一些差错控制，只要大家输入的信息不是非常离谱，一般都没有问题。希望大家都能够方便愉快地使用此工具。 最后的话：本人并非经济专业或财务金融专业出身，此工具的设计制作和以上说明完全依据个人实际经验和相关资料，如有谬误和不足之处希望大家指正。另外，此工具完成后，测试验证数据十分有限（一般不涉及提前还贷的计算基本都是精确的），希望大家在使用过程中如果发现什么问题或错漏，及时向我反馈的同时也敬请谅解。 附： 等额方式相关算法简略说明： 前面提到等额方式的每月偿付本金为一个等比数列，数列的等比值为（月率+1），另外这个等比数列的总合即为你的借贷本金总额，所以根据等本数列求和公式可以求得首月的偿付本金。首月的偿付利息即为借贷总额×月率。有了首月的本金和利息，就可求得等额还款的每月应付总额。（本工具中没有采用此种计算方法，而是直接使用了Excel提供的现成函数PMT。）当调息发生时，相对于开始一次新的借贷过程，新的借贷本金、新的借贷期限和新的借贷利率。当提前还款发生时，也类似于开始一次新的借贷过程，只是利率沿用了前面所使用的利率。返回 等本方式相关算法简略说明： 前面提到等额方式的每月偿付本金是各月相同的，而总合即为你的借贷本金总额，所以用总额除以贷款期限就可以求得每月应偿付的本金。另外，每月的偿付利息的计算方法就是当月剩余的借贷本金×月率，因为每月的剩余借贷本金是一个等差数列（其差值就是每月相等的偿付本金），所以每月的偿付利息也是一个等差数列。有了利息和本金的算法就可以算出每月的明细情况了。当调息发生时，每月的等额本金仍保持不变，变的只是每月的偿付利息（根据调息后利率变化）。当提前还款发生时，也类似于开始一次新的借贷过程，每月等额的偿付本金要重新计算，算法是剩余借贷本金除以剩余借贷期限，而利息方面沿用前面所使用的利率进行计算。返回

chrisfang

关于PMT函数的含义和用法，可以参考下面这个帖子： http://club.excelhome.net/dispbbs.asp?boardid=102&replyid=306282&id=152505&page=1&skin=0&Star=3

BIN_YANG168

不错，谢谢楼主提供分享，一定下了不少功夫，建议斑竹加分。

yuzetao

呵呵 很好 对我有用 谢谢楼主 不过要是提前还款再多点的话会更好

chrisfang

以下是引用[I]yuzetao[/I]在2006-4-11 10:35:07的发言：
呵呵 很好 对我有用 谢谢楼主 不过要是提前还款再多点的话会更好

如果你有兴趣的话，也可以变通一下实现多次提前还款的计算。因为提前还款就相当于开始一个新的贷款合约，所以可以这样操作： 先进行一次提前还款的计算，然后通过查看显示的剩余本金，记下这个数字，同时记下剩余的贷款期； 然后新开工具，在初始贷款信息内输入新的贷款额度(即前面的剩余本金)，新的贷款期(即剩余的贷款期)，提前还款前所采用的利率，开始一次新的贷款合约计算，这样就可以进行第二次提前还款计算了。 依次类推，任意次提前还款都可以进行类似操作。

[此贴子已经被作者于2006-4-11 11:15:18编辑过]

chrisfang

以下是引用[I]yuzetao[/I]在2006-4-11 10:35:07的发言：[BR]呵呵 很好 对我有用 谢谢楼主 不过要是提前还款再多点的话会更好

这个版本里面应该不会再增加提前还款的数量了，因为增加这么一次计算，公式就要成倍增长，也增加了系统结构的复杂化。还是以后用VBA来实现这些附加功能比较方便，结构也比较清晰。 反正以后还要跟着兰老师学VBA，边学边做吧。

偶来学习VBA

我也很想要这样一个工具。

chrisfang

真的没人对这个有兴趣吗？

apolloh

呵呵，不错！

你的签名，我好像在一本书看过，作者应该是吴甘霖：受苦的人没有悲观的权利。