16.投行财务模型基础——搭建计算工作表（运营期银行贷款）

本课程视频请参见网易云课堂投行财务模型基础

1. B66输入“运营期银行贷款”

2. C67至C79输入“贷款期序号”，“贷款期期初”，“贷款期期末”，“期初贷款余额（贷款年期初）”，“还本付息金额”，“其中：当期支付的利息”，“其中：当期支付的本金”，“期末贷款余额（贷款年期末）”，“贷款余额（资产负债表）”，“期初应付利息”，“新增应付（财务费用，利润表）”，“当期支付的利息”，“期末应付利息（资产负债表）”

对于银行贷款的计算，由于运营开始日为10月。因此，银行贷款以上一年10月1日至次年9月30日为一个周期，贷款的利息以10月1日时剩余的本金余额乘以年利率为此周期应支付的利息。但是由于财务报表的报告期以1月1日至12月31日为一个周期，因此利润表显示的财务费用应当分成两部分：1月至9月期间按照上一年9月30日还本付息之后的本金余额为基础计算的9个月的利息，加上10月至12月，本年9月30日还本付息后的本金余额为基础计算的3个月的利息。同理，由于每年9月30日按照贷款周期实际支付的利息与当年账面上计的财务费用有一定的差距，因此，项目资产负债表上会有“应付利息”科目，记录实际支付的利息与当期的会计周期记取的财务费用之间的差异。而资产负债表上，12月31日的贷款余额则取当年9月30日还本付息之后的本金余额作为报告的贷款余额。

1. 为便于查看对应的区间，第67、68和69行分别表示贷款期期间。K67输入公式“=SUM(输入假设!$K$79:K79)*输入假设!K79 ”，以“输入假设”工作表第79行累计加总，得到贷款期间各期序号，公式里的乘以79行可以保证原来不属于贷款期间（79行为0）的单元格数值仍为0

2. K68输入公式“=IF(K67=1,输入假设!$D$17,IF(K67=0, “”,J69+1))”，如果第67行的某单元格为1，则对应的第68行的数值为还本付息期间起始日，也是运营起始日（输入假设工作表的D17单元格），其余期间的起始日为上期终止日向后数1日（即，J69+1），对于第67行等于0的单元格，表示不属于贷款期间，起始日无数值（用“”表示无数值）

3. K69输入公式“=IFERROR(EDATE(K68,输入假设!$D$22)-1,“”)”，即，第68行起始日向后数12个月后再向前数1日。如果第68行的单元格无数值，则EDATE公式将会返回错误值，因此用IFERROR函数，如果EDATE函数返回错误，则公式的返回值设定为无数值，即“”

IFERROR函数表示判断计算公式是否有意义，如果计算公式有意义，可以得出计算结果，那么返回公式计算的结果，如果计算公式没有意义，无法计算，那么返回需要显示的信息，就是返回函数的第二个参数。IFERROR函数的格式为：=IFERROR（计算公式，计算公式无意义需要显示的内容）

1. 第70行显示贷款期期初本金余额，输入公式“=IF(K67=1,输入假设!$I$74,计算!J74)”，即，如果第67行单元格是1，表示为贷款还本付息期首期，为建设期累计提取的贷款金额（即，输入假设工作表I74单元格），如果非首期，则期初本金余额为上期期末的本金余额（即，第74行前一期的数值）

2. 第71行为每年9月30日还本付息金额，K71输入公式“=-输入假设!K79*输入假设!$D$81”，每期还本付息金额即输入假设D81单元格，乘以输入假设第79行（还本付息期时间标识）的目的为判别是否属于还本付息期，如果属于还本付息期，则还本付息金额为固定的值（输入假设D81），如果不属于还本付息期，则单元格的值为0

3. K72输入公式“=K70*输入假设!$D$86”，即，还本付息期当期的利息为期初金额乘以年利率

4. K73输入公式“=K71+K72”，即，当期的本金为每年固定的还本付息金额除去利息后的金额

5. 第74行表示期末贷款余额，K74输入公式“=SUM(K70,K73)”，即，期末金额等于期初金额减去当期偿还的金额

6. 第75行表示当年会计年度资产负债表上列计的贷款余额（即，当期12月31日贷款余额），此金额应当与贷款年度期末余额相等（即，当年10月1日的贷款余额），但是，如果是运营期首年，贷款余额为建设期累计提取的贷款金额。因此，K75输入公式“=IF(YEAR(K5)=YEAR(输入假设!$D$17),输入假设!$I$74,计算!K74)*计算!K9”

7. 第76至79行将用来计算应付利息。由于每年计列的财务费用和当期实际现金支付的利息存在差异，因此账面上会存在应付利息项目。账上应付利息的计算方法为：第76行用来计算期初应付利息（即，上期期末应付未付的利息），第77行用来计算当期新增的应付利息（即当期利润表计列的财务费用），第78行用来计算当期9月30日支付的利息。那么，第79行期末应付利息等于期初应付利息加上当期新增的财务费用减去当期偿还的利息

8. 因此，K76输入公式“=J79”

9. K77输入公式“=IF(YEAR(K5)=YEAR(输入假设!$D$17),计算!K10*输入假设!$I$74/输入假设!$D$22*输入假设!$D$86,(计算!K70*MONTH(输入假设!$D$76)+计算!K74*(输入假设!$D$22-MONTH(输入假设!$D$76)))/输入假设!$D$22*输入假设!$D$86”，由于贷款年度为上年10月1日至当年9月30日，而会计年度为当年1月1日至12月31日，因此，当年会计计列的利息的算法，前9个月按照上一个贷款年度期初（即上一年10月1日）的贷款余额乘以年利率乘以9个月除以12个月得到，而后3个月的利息，按照贷款年度期末（即当年9月30日）的贷款余额乘以年利率乘以3个月除以12个月得到

10. 因此，此公式的后半段：“(计算!K70*MONTH(输入假设!$D$76)+计算!K74*(输入假设!$D$22-MONTH(输入假设!$D$76)))/输入假设!$D$22*输入假设!$D$86”，算法即用期初余额为基础算9个月的利息（K70乘以9个月，MONTH（输入假设!D76)表示贷款终止日时的月数，即9月），使用期末余额为基础算剩下3个月的利息，即K74乘以3个月，然后乘以月利率，即，年利率除以12个月（输入假设!D86/输入假设!D22)，前半段公式“=IF(YEAR(K5)=YEAR(输入假设!$D$17),计算!K10*输入假设!$I$74/输入假设!$D$22*输入假设!$D$86,…)”，表示当处于运营期首期，2021年10月1日至2021年12月31日期间（即，当期期末日期的年数为运营期起始日的年数2021时，即，YEAR(K5)=YEAR(输入假设!$D$17)），财务费用为以建设期累计贷款为基础计算的3个月的利息

11. 当期支付的利息与对应的贷款期期间（即，9月30日）支付的利息相同，即，K78输入公式“=-K72”

12. 期末资产负债表上计列的应付利息为期初应付利息+当期的财务费用-当期支付的利息，即，K79输入公式“=SUM(K76:K78)”

13. 将K67至K79的单元格在L67至AG79区间复制粘贴

14. 第I列补全求和公式，即I67输入“=SUM(K67:AG67)”，将I67复制粘贴至I79

15. 第E70至E79输入“万元”