时间:2019-08-22 作者:周国钢 (作者单位:青岛大学商学院)
[大]
[中]
[小]
摘要:
长期贷款是指企业从银行或其他金融机构借入的一年以上的贷款。长期贷款按照本息偿还方案的不同,可分为到期一次还本付息和分期偿还的等额本金、等额利息、等额本息四种基本还款方案。无论采用何种还款方案,企业都应计算每期应付利息和偿还本金,编制还款计划表,选择有利于企业的资金结构优化、资金成本较低的贷款方案。把各种还款方案集成设计在一个一体化模型中,则便于比较还款计划和资金成本,有利于辅助决策。本文将依据由浅入深、逐步扩展的设计思路,依次完成由静态模型到动态模型再到扩展模型的设计路径,在逐步比较分析静态模型和动态模型的优缺点的基础上,完成最终具有通用性的扩展模型的设计和应用。
一、长期贷款决策模型设计原理和工作表函数
(一)设计原理
设计原理如表1所示。其中,等额本息还款方案恰好可利用PMT、PPMT、IPMT三个函数计算,且PMT=PPMT+ IPMT。
(二)长期贷款相关的工作表函数
1.FV函数
功能:计算到期一次还本付息还款方案下的本息合计总额。
公式:=FV(rate,nper,pmt,pv,...
长期贷款是指企业从银行或其他金融机构借入的一年以上的贷款。长期贷款按照本息偿还方案的不同,可分为到期一次还本付息和分期偿还的等额本金、等额利息、等额本息四种基本还款方案。无论采用何种还款方案,企业都应计算每期应付利息和偿还本金,编制还款计划表,选择有利于企业的资金结构优化、资金成本较低的贷款方案。把各种还款方案集成设计在一个一体化模型中,则便于比较还款计划和资金成本,有利于辅助决策。本文将依据由浅入深、逐步扩展的设计思路,依次完成由静态模型到动态模型再到扩展模型的设计路径,在逐步比较分析静态模型和动态模型的优缺点的基础上,完成最终具有通用性的扩展模型的设计和应用。
一、长期贷款决策模型设计原理和工作表函数
(一)设计原理
设计原理如表1所示。其中,等额本息还款方案恰好可利用PMT、PPMT、IPMT三个函数计算,且PMT=PPMT+ IPMT。
(二)长期贷款相关的工作表函数
1.FV函数
功能:计算到期一次还本付息还款方案下的本息合计总额。
公式:=FV(rate,nper,pmt,pv,type)或=FV(rate,nper,pv)
参数:rate为各期利率;nper为付款总期数;pmt为各期应支付的等额本息金额,pv为贷款本金。pmt和pv必选其一,省略位于中间的参数的前置逗号保留。type省略为0(期末付款)或取值1(期初付款)。
说明:①rate和nper在期间单位应保持一致,或月或年;②在所有参数中,支出为负数;收入为正数;③验证可知,按年计息比按月计息所负担的利息总额要低,因此,企业应采用按年计息到期一次还本付息。
2.PMT函数
功能:计算等额本息还款方案下的每期还款金额(本金和利息)。
公式:=PMT(rate,nper,pv,fv,type)或=PMT(rate,nper,pv)
参数:fv为贷款余额,省略为0,即一笔贷款的未来值为零。其他参数含义同FV函数。
说明:①若要计算贷款期间的支付总额,可用PMT函数值乘以nper;②其他说明同FV函数;③验证可知,按年计算比按月计算的年偿还总额要高,因此,企业应采用按月分期等额偿还的方案与银行签约。
3.PPMT函数
功能:计算等额本息还款方案下的各期偿还本金。
公式:=PPMT(rate,per,nper,pv,fv,type)或=PPMT(rate,per,nper,-pv)
参数:per分期的当前期数,必须介于1到nper之间。其他参数含义及说明同FV和PMT函数。
4.IPMT函数
功能:计算等额本息还款方案下的各期偿还利息。
公式:=IPMT(rate,per,nper,pv,fv,type)或=IPMT(rate,per,nper,-pv)
PMT、PPMT、IPMT三者关系为PMT=PPMT+IPMT,且随时间推移PMT中的PPMT逐渐增加,IPMT逐渐减少。
5.CUMPRINC函数
功能:计算一笔贷款在给定期间内累计偿还的本金数额。
公式:=CUMPRINC(rate,nper,pv,start_period,end_period,type)
参数:start_period为期间的首期,end_period为期间的末期,type不可省略,其他参数同前。
6.CUMIPMT函数
功能:计算一笔贷款在给定期间内累计偿还的利息数额。
公式:=CUMIPMT(rate,nper,pv,start_period,end_period,type)
参数同前。
二、长期贷款决策静态模型设计
根据还款方案的不同,笔者结合具体实例,阐述长期贷款决策静态模型的设计思路。
假设A公司拟从银行取得2000万元的5年期长期贷款,年利率为6%。要求建立在四种不同的还款方案下每期还本付息计划表模型。
该模型的设计思路是:利用Excel的数据链接技术和单元格公式定义,将上述四种常见的还本付息还款方案集成在一个静态模型中,便与比较四种方案的还款计划,辅助财务人员根据企业自身的情况做出相应决策。模型设计的总体结构及其各要素安排如图1所示。
在应用该模型时,用户只要输入已知条件中的各项基础数据,模型将立刻展示四种还款方案下的还款计划表数据。从模型计算结果可知,同样条件下,利息总额负担由轻至重排列的还款方案顺序为:等额本金→等额本息→等额利息→到期一次还本付息。决策者可以参照模型计算结果,根据自身的情况和银行的要求,或选择利息总额最小的还款方案,或选择前期还款压力较轻的还款方案。该模型的具体设计过程主要涉及单元格公式的定义。依据设计原理,充分利用前文论述的函数和工具,即可完成图1所示的静态模型设计。
值得注意的是,该静态模型虽然很好地解决了例中的问题,并且规范、有效地集成了四种还款方案的比较,使得四种还款计划一目了然,但是,该模型也有一个缺陷,即当贷款期限不等于5年时(如1至4年),模型中的各种还款方案的计算结果均会发生数据错误或语法错误,如图2所示。为此,笔者进一步设计了更具一般意义的动态模型。
三、长期贷款决策动态模型设计
1.设计思路
仔细分析“静态模型”产生错误信息的原因会发现:在各种还款方案中,各年份的数据计算不能随着贷款期限的变化而变化,即表达各种还款方案中各年份相关数据的行数不能随着贷款期限的增减而动态地增减,因此,动态模型的设计思路是:将表达分期的A列中与期数相关的各单元格与表达贷款期限的$H$2单元格相关联,根据某一年份值是否在贷款期限内决定是否要填写该年份的相关数据,即是否要对该行的相关单元格赋值。如果是,则赋以正确值;否则,置零。采用的方法是利用Excel的IF函数来定义相关单元格的计算公式,即用IF函数创建单元格的条件公式,有条件地增减行数据。
2.设计过程
动态模型的总体设计结构及其各要素安排如图3所示。为使读者更清楚地看到动态模型的全貌,将贷款期限扩展至7年。为了增强动态模型的通用性,此处特意设计了三个参数调节按钮,分别对贷款金额、贷款年利率和贷款期限作动态调节,其取值范围取决于动态模型的应用需求。本例中贷款金额为1万元至8000万元,贷款利率为2.00%至10.00%,贷款期限为1至7年。
在静态模型的基础上,动态模型的设计主要应用了IF函数。例如:在D7中输入公式:“=IF(A7=$H$2,FV($E$2,$H$2,,-$C$2),0)”,即:如果当前分期数与贷款期限($H$2)相等(即到期)则偿还全部贷款本金($C$2),否则(未到期或超期)不需偿还本金(置零)。在E17中输入公式“=IF(A17<=$H$2,PMT($E$2,$H$2,-$C$2),0)”,即:贷款期限内等额还款,超期置零。同理可定义各还款方案第一年的单元格公式,只是IF函数内含的公式依据还款方案不同而不同。其余各行复制年份第1行的公式或独立定义。将模型中零值设为空白。
从图3可以看出,动态模型各还款方案的各年数据随着贷款期限的增减而增减,与贷款期限所要求的数据行数完全一致,排除了静态模型中由于更改贷款期限而产生的错误。但其仍具有一定的局限性,如只能以年为单位而不能以季、月为单位计息和还款;只能展示7个分期的还款计划表,5年的长期贷款若按月分期还款,则需要60行相关数据,该动态模型无法完成;与长期贷款决策所需的相关信息无法展示,如某期间累计偿还的本金、截止到各期间的未偿还本金、累计利息、还款时点在期初还是期末;等等。
四、长期贷款决策动态模型的扩展设计
1.模型设计
为克服上述动态模型的局限性,可将长期贷款决策动态模型扩展设计成动态扩展模型。动态扩展模型的总体结构布局如图4所示。设计原理与过程如前述,不予赘述。
2.动态扩展模型的特点
图中展示的是按月等额本息、期末还款的数据。该模型具有较强的通用性,其主要功能特点包括:
一是区域清晰、层次分明。右边是基本数据区及分析结果区,左边是数据分析区,即分期还款计划明细表。既有长期贷款的摘要信息(右边),又有分期还款的明细信息(左边)。
二是动态参数、随需应变。诸如贷款总额、贷款总期限(年)等8个带有参数调节按钮的可变数据,还款方案、还款期间和还款时点选择分别带有下拉组合框供决策者选择,企业可根据不同的长期贷款数据进行相应调节。
三是弹性表格、行随需变。左边的数据分析区所展示的分期还款计划明细表可随右边贷款年限参数的变化而弹性伸缩。图4展示的是2年按月还款24个月的还款计划明细表。若是3年,则该表自动扩展至36行的分期还款计划明细表,最多可扩展至15年的长期贷款、按月还款多达180行的分期还款计划明细表。若是按季还款,则该模型按贷款年限乘以4动态确定明细表行数。
四是信息详尽、满足决策。右边的基本数据区及分析结果区有多达23个贷款指标数据和动态可调参数,它们是长期贷款的摘要数据;左边的数据分析区动态展示的是各期还款的明细数据。利用动态数据链接技术使二者一致、同步变化,决策者可以根据决策需求,既了解摘要数据,又掌握明细数据,对长期贷款做出符合实际需求的决策。
五、结束语
综上所述,长期贷款决策的动态扩展模型较好地解决了模型的通用性问题,使该模型的通用化程度大幅提高,可以应用于所有情形的长期贷款决策。再以分析结果数据绘制图形,则更加直观。扩展模型以一览表的形式,方便地比较各种长期贷款的资金成本和还款计划,快速、准确地辅助长期贷款决策。
需要说明的是,图4模型所展示和实现的是按月等额本息、期末还款的数据。所有公式都按照还款方案为“等额本息”、还款期间为“月付”、还款时点为“期末”的相应计算公式来定义。这3个下拉组合框的其他选项所对应的计算公式也能够在模型中作实质性设计,作为一种设计思路的提示,读者可以根据相同的设计原理和设计思路在定义相应公式时利用IF函数来实现。
责任编辑 李卓
相关推荐