本課程視頻見(jiàn)網(wǎng)易云課堂投行財(cái)務(wù)模型基礎(chǔ)

經(jīng)營(yíng)資產(chǎn)模塊：

1. B68輸入“經(jīng)營(yíng)資產(chǎn)”

2. C69輸入“應(yīng)收賬款周轉(zhuǎn)天數(shù)”，D69輸入“150”，E69輸入“日”；C70輸入“應(yīng)付賬款周轉(zhuǎn)天數(shù)”，D70輸入“30”，E70輸入“日”

資本結(jié)構(gòu)模塊：

1. B72輸入“資本結(jié)構(gòu)”

2. C73輸入“資本金投入”，D73輸入“20%”

3. C74輸入“銀行貸款提款”，D74輸入“=1-D73”

4. C75輸入“貸款起始日”，D75輸入“=D15”

5. C76輸入“貸款終止日”

6. C77輸入“首期還本付息日”，D77輸入“=EDATE(D17,D22)-1”，即，首期還本付息日為運(yùn)營(yíng)日開(kāi)始后12個(gè)月的月末

7. C78輸入“還本付息期數(shù)”，D78輸入“15”

8. 貸款終止日為項(xiàng)目開(kāi)始運(yùn)營(yíng)日后15年年末。即，D76單元格輸入公式“=EDATE(D17,D22*D78)-1”

9. 第76行的K列至AG列做一個(gè)時(shí)間線，使貸款終止日所處的期間單元格的值為1，其余期間單元格的值為0。因此，K76輸入公式：“=IF(YEAR($D$76)=YEAR(K5),1,0)”，將K76在L76至AG76之間復(fù)制粘貼?？梢钥闯?，貸款終止年2036年時(shí)，單元格AB76的結(jié)果為1

10. 第77行做一個(gè)時(shí)間線，使首個(gè)還本付息年的單元格數(shù)值為1，其他數(shù)值為0。K77輸入公式“=IF(YEAR($D$77)=YEAR(K5),1,0)”，將K77在L77至AG77之間復(fù)制粘貼?？梢钥闯觯讉€(gè)還本付息日在2022年，對(duì)應(yīng)的單元格N77數(shù)值為1，其余單元格結(jié)果為0

11. 將第79行做一個(gè)時(shí)間線，表示還本付息的期間，即，當(dāng)年需要支付還本付息資金的單元格數(shù)值為1，不需要還本付息的單元格數(shù)值為0。在單元格C79輸入“還本付息期間”，在單元格K79輸入公式“=IF(AND(YEAR(K5)<=YEAR($D$76),YEAR(K5)>=YEAR($D$77)),1,0)”，此公式的意思是：當(dāng)所處期間期末的年數(shù)大于等于首期還本付息期間的年數(shù)，并且小于等于貸款終止期間的年數(shù)時(shí)，單元格的數(shù)值為1，否則為0

12. 單元格C80輸入“還款方式”，D80輸入“等額本息”

13. C81輸入“每期還本付息金額”

本項(xiàng)目的貸款，在建設(shè)期時(shí)，只是在每期期末向銀行支付當(dāng)期利息。為簡(jiǎn)化處理，在建設(shè)期末（2021年9月30日），向銀行支付當(dāng)年截止到當(dāng)日應(yīng)當(dāng)支付的利息。而自運(yùn)營(yíng)期開(kāi)始后，以后的15個(gè)9月30日，以等額本息的形式支付銀行相同金額，包括利息和本金。

在每月9月30日還本付息是現(xiàn)金流層面向銀行付息和償還，但是在利潤(rùn)表層面，當(dāng)期應(yīng)計(jì)的利息應(yīng)當(dāng)按照當(dāng)期包含的應(yīng)計(jì)息的月數(shù)來(lái)計(jì)算。比如在銀行借款到期日2036年9月30日，2036年應(yīng)計(jì)的利息應(yīng)當(dāng)只是9個(gè)月的利息。因此，需要計(jì)算每個(gè)期間應(yīng)當(dāng)計(jì)息的月份數(shù)，從而得到利潤(rùn)表角度當(dāng)期的應(yīng)計(jì)利息。

14. 接下來(lái)分建設(shè)期、運(yùn)營(yíng)期和借款到期日的那一年這三種情況計(jì)算當(dāng)期應(yīng)計(jì)利息的月數(shù)，然后將三種情況合并，看整個(gè)項(xiàng)目期間每一期應(yīng)計(jì)利息的月數(shù)。C82輸入“最后一期還本付息計(jì)息月數(shù)”，C83輸入“建設(shè)期計(jì)息月數(shù)”，C84輸入“運(yùn)營(yíng)期計(jì)息月數(shù)”，C85輸入“當(dāng)期計(jì)息月數(shù)”

15. K82單元格輸入公式“=IF(K76=1,MONTH($D$76),0)”，即，如果當(dāng)期為貸款終值日所處期間，則當(dāng)期計(jì)息月數(shù)為終止日的月份數(shù)（9月30日），9個(gè)月。對(duì)于其余期間，單元格的值為0。將K82單元格復(fù)制粘貼至L82到AG82的區(qū)間

16. K83單元格輸入公式“=K7*K6”，即，如果當(dāng)期為建設(shè)期，則月份數(shù)為第6行計(jì)算的當(dāng)期月份數(shù)。將K83在L83至AG83區(qū)間復(fù)制粘貼

17. K84單元格輸入公式“=IF(YEAR($D$17)=YEAR(K5),K10,IF(K76=1,K82,K10*K79))”，即，如果當(dāng)期為運(yùn)營(yíng)期首年，則運(yùn)營(yíng)月數(shù)為第10行計(jì)算的月數(shù)，對(duì)于其他年份，如果第76行的數(shù)值為1，即當(dāng)年為貸款終止年，則當(dāng)期的計(jì)息月數(shù)為第82行算的最后一期計(jì)息月數(shù)，除了貸款終止年以外的年份，在貸款存續(xù)期間，計(jì)息月數(shù)為第10行計(jì)算的月數(shù)。將K84在L84至AG84區(qū)間復(fù)制粘貼

18. 基于第83和第84行建設(shè)期和運(yùn)營(yíng)期計(jì)息月數(shù)，可以得到整個(gè)項(xiàng)目期間每期的計(jì)息月數(shù)，即，K85輸入公式“=SUM(K83:K84)，將K85在L85和AG85區(qū)間復(fù)制粘貼

19. C86輸入“貸款利率”，D86輸入“5.5%”

20. D81輸入公式“=-PMT(D86,D78,I74)”，求得每期需要還本付息的金額

PMT公式用來(lái)計(jì)算等額分期付款的每期付款額，函數(shù)的格式為：PMT(期利率，期數(shù)，本金金額）

補(bǔ)全建設(shè)期利息的計(jì)算：

1. 由于資本金和項(xiàng)目貸款是以20%、80%的比例匹配。因此，在K73輸入公式“=$D$73*K37”，即，資本金的投入為總投資的20%，在K74輸入公式“=$D$74*K37”，銀行貸款為總投資的80%。將K73和K74在L73至AG74之間復(fù)制粘貼

2. 建設(shè)期利息為當(dāng)期累計(jì)的借款乘以對(duì)應(yīng)計(jì)息月數(shù)的利率。假設(shè)每期期初進(jìn)行銀行貸款提款。因此，當(dāng)期的利率為期初累計(jì)銀行提款乘以年利率乘以當(dāng)期計(jì)息月數(shù)除以12個(gè)月，由于只計(jì)算建設(shè)期的利息，因此，最后乘以建設(shè)期的時(shí)間標(biāo)識(shí)（第7行）即，在K36輸入公式“=SUM($K$74:K74)*$D$86*K85/$D$22*K7”

由于建設(shè)期利息是根據(jù)當(dāng)期總投資額（靜態(tài)投資額與當(dāng)期建設(shè)期利息之和）計(jì)算當(dāng)期累計(jì)提取的貸款金額乘以利率計(jì)算的，也就是當(dāng)期建設(shè)期利息取決于總投資金額，而總投資金額反過(guò)來(lái)也取決于當(dāng)期建設(shè)期利息的值。這樣在Excel表格中存在循還引用。通常，在Excel的默認(rèn)設(shè)置中，循環(huán)引用是不允許存在的，因此Excel會(huì)出現(xiàn)出錯(cuò)提示，如右圖。

一般情況下，財(cái)務(wù)模型中如果有大量的數(shù)據(jù)計(jì)算，則循環(huán)引用的存在會(huì)使Excel文件由于迭代計(jì)算的存在使運(yùn)算次數(shù)過(guò)多，極有可能導(dǎo)致內(nèi)存不足，使整個(gè)工作表打開(kāi)緩慢，或無(wú)法打開(kāi)出現(xiàn)錯(cuò)誤。因此，在財(cái)務(wù)模型中應(yīng)盡量避免循環(huán)引用導(dǎo)致的迭代計(jì)算，此時(shí)，多數(shù)情況下，我們會(huì)采用Macro宏的形式將循環(huán)計(jì)算斷開(kāi)。由于本課程為基礎(chǔ)課程，這里不就Macro宏進(jìn)行介紹。此內(nèi)容將留給后面的提升課程。這里僅通過(guò)修改Excel的設(shè)置使軟件接受循環(huán)引用迭代計(jì)算，取消提示。

設(shè)置的方法：在出來(lái)的提示框先單擊“確定”，以關(guān)閉提示框。單擊Excel軟件左上角的“文件”菜單，出現(xiàn)右圖頁(yè)面。單擊“選項(xiàng)”，單擊“公式”，勾選啟用迭代計(jì)算選項(xiàng)卡。這樣就完成了使Excel允許循環(huán)引用迭代計(jì)算的設(shè)置。

3. 允許迭代后，Excel將不會(huì)報(bào)錯(cuò)。將K36的公式在L36至AG36區(qū)間復(fù)制粘貼，此時(shí)建設(shè)期總投資以及資本金投資和貸款金額也相應(yīng)地隨著建設(shè)期利息的補(bǔ)全而更新。

4. 補(bǔ)齊I73，I74，I76，I77，I79，I82至I85的求和公式，即I73輸入“=SUM(K73:AG73)，其余類(lèi)似