能源合同管理（十二）拓展案例股东贷款置换

这个模型是按月来计算财务数据的，方便我们把贷款计算精确到月。为了看起来更容易理解，我把上一篇文章中贷款的假设条件修改了一下。新借的第三方贷款只够偿还80%的股东借款，这样一来20%的股东贷款一直还本付息到贷款合同期满。宽限期我把6个月成了3个月，还本也从年度还本改成了季度还本。

图一 贷款假设条件

图二贷款还本付息计算表

图二是贷款还本付息计算表，你可能看到密密麻麻的英文字母就头疼，它的结构和计算其实跟可研报告的还本付息计算表是差不多的。例如红色方框是所有贷款还本付息计算的汇总：

第10行是期初借款余额（beginning balance）

第11行是当期借款（loan drawdown）

第12行是当期还本（principal repayment）

第13行期末余额（ending balance）

第14行是当期付息（interest paid）

图二中蓝色方框是股东贷款的详细条件，大部分数据是直接引用假设条件表的数据，但是也有一部分有关还本的计算放在这里，这样就不用在下面绿色方框里面再计算，相当于分拆了计算步骤。

图三 股东贷款条件

图三中第17行股东贷款的本金，是用图一中假设条件的总投资6,505乘以借款比例70%再乘以股东贷款比例100%来确定的。

第18行到第20行，22行和23行是直接引用图一的假设条件数据。

第21行贷款期满日期是在第23行的首次还本日期加上贷款期限（Tenor 6年x12个月）的时间得到的。这里需要特别说明一下，第23行的标题虽然叫做首次还本日期，其实并不妥当。它是宽限期结束日，从这个日期开始计算还本的时间。这里显示的是2012年12月31日，如果是季度还本，则2013年的3月31日是第一次还本的日期。

第24行是每期还本金额，是根据第22行的还本频率来确定的。这个还本是没有股东贷款置换时的每期还本，如果有股东贷款置换，则是在股东贷款置换之前的每期还本金额。它的计算是用if公式根据第22行的Q，SA，A的选择结果，来确定第17行的Rm4,553k的本金在贷款期内是按季度，半年或者年来偿还。公式是：

IF($D22="Q", $D17/(D20*4),IF($D22="a",$D17/D20,IF(D22="sa",$D17/(D20*2),0)))

第25行是如果只有部分股东贷款被替换掉，剩余本金在剩余的贷款期限内再按照第22行的Q，SA，A选择来分摊本金。这个计算公式的关键是要确定用第三方贷款偿还部分股东贷款本金后剩余的股东贷款本金。这个金额就需要用到下面图四的贷款计算来确认了。它的公式比较长，有兴趣的可以琢磨一下。

=IF(Assumpt!$C$28,SUMIF($E$7:$CJ$7,$D$40,$E$32:$CJ$32)*(1-Assumpt!$C$28)/IF($D22="Q",ROUND((D21-D40)/91.25,0),IF($D22="a",ROUND((D21-D40)/365,0),IF(D22="sa",(ROUND((D21-D40)/182.5,0))))),0)

这个公式的原理，第一步是先找到第三方贷款入账日期对应的股东贷款当期余额即3,984，第二步是用3,984x(1-80%)得到剩余的股东贷款本金，第三步再分别计算年度，半年或季度还本时的每期金额。

图四 贷款计算

图四的贷款计算跟以前文章中介绍的借款还本付息计算表没有大的区别，只不过是把计算步骤给拆分，看起来更简单一些。

第28行debt counter我也不知道中文应该叫啥，这行的目的是用if公式来判断每个单元格对应的日期是否是还本日，如果是还本日就为1，不是则为0。你能看L列和O列的数字是1，与它相对应日期的第31行还本（principal repayment）里是有当期的还本金额的。

第29行是期初借款余额，它等于上个月第35行的期末余额。

第30行是当期借款，用IF公式判断当前日期是否有借款本金入账。

第31行是当期还本，它等于单元格D24乘以第28行的数据，即每期的还本额190乘以1或0。

第32行是股东贷款置换前的本金余额，等于第29行加第30行再减第31行。

第33行是股东贷款置换金额，它是置换发生时第32行对应的金额乘以假设条件的置换比例，这里是3,984x80%=3,187

第34行则是剩余的股东贷款本金继续还本，它等于单元格D25的金额乘以第28行的1或0。我们这里再回头去看单元格D25的计算是不是比较容易理解了。

第35行是股东贷款置换后的期末余额。

第36行是当期付息，它的计算是用利率乘以（期初余额+当期借款）/12。

这里介绍的股东贷款的计算由于是按月计算，且考虑了几种还本频率，所以看起来计算好像很复杂。如果你的模型是按年来计算的，公式就会简单很多了。