點(diǎn)擊藍(lán)字關(guān)注【秋葉 Excel】

發(fā)送【7】

免費(fèi)領(lǐng) 1000+篇 Excel 精選教程！

本文作者：小花

本文編輯：竺蘭

大家好，這里是秋葉 Excel~

在財(cái)務(wù)投資測(cè)算中，回收期是很重要的參考指標(biāo)，它是指從投資到收回本金的時(shí)間。

與凈現(xiàn)值 NPV 和內(nèi)部收益率 IRR 不同，Excel 中并不存在專門的回收期函數(shù)。

于是，為了計(jì)算回收期，財(cái)務(wù)朋友們，可謂八仙過(guò)海，各顯神通！

我見過(guò)的最卑微的一種方法，竟然是用 IF 函數(shù)構(gòu)建輔助行，再通過(guò)求和得出回收期。

▋例 1：IF+輔助行求精確回收期

C4 公式如下：

=IF(C3>0,MAX(-B3/C2,0),1)

公式說(shuō)明：

使用 IF 函數(shù)進(jìn)行條件判斷，當(dāng)期累計(jì)經(jīng)營(yíng)性現(xiàn)金流小于或等于 0 的，返回 1。

如果當(dāng)期累計(jì)經(jīng)營(yíng)性現(xiàn)金流大于 0，則返回上期累計(jì)經(jīng)營(yíng)性現(xiàn)金流與當(dāng)期經(jīng)營(yíng)性現(xiàn)金流之比的負(fù)數(shù)和 0 之間的較大值 m（使用 Max 來(lái)完成大小判斷）。

此時(shí)，只有累計(jì)經(jīng)營(yíng)性現(xiàn)金流在當(dāng)期首次實(shí)現(xiàn)回正，m 才為正數(shù)，否則 Max 函數(shù)返回 0。將輔助行求和的結(jié)果即為精確回收期。

不難發(fā)現(xiàn)，在累計(jì)經(jīng)營(yíng)性現(xiàn)金流回正后，當(dāng)期值出現(xiàn)負(fù)值或累計(jì)值再次轉(zhuǎn)負(fù)時(shí)，該公式均無(wú)法正確計(jì)算。

輔助行+邏輯復(fù)雜，這樣的公式仍漏洞百出，回收期計(jì)算問(wèn)題真的這么難解嗎？

其實(shí)不然！

學(xué)會(huì)我分享的這些公式，讓你輕松拿捏它。

什么，你想系統(tǒng)提升 Excel 技能？

那么千萬(wàn)別錯(cuò)過(guò)秋葉專為 0 基礎(chǔ)小白打造的《Excel 3 天集訓(xùn)營(yíng)》課程！

現(xiàn)在掃碼報(bào)名，你還能免費(fèi)領(lǐng)取函數(shù)手冊(cè)+快捷鍵大全+110 套 Excel 模板！

別再猶豫了，趕緊掃碼

搶占免費(fèi)學(xué)習(xí)名額

助力自己在職場(chǎng)中脫穎而出吧！

求整數(shù)回收期的方法

很多時(shí)候，我們計(jì)算投資回收期時(shí)，并不需要像例 1 那樣精確到小數(shù)，只需求整數(shù)位即可（相當(dāng)于例 1 結(jié)果向上取整）。

這種情況下，可用的公式非常多，以下，僅分享其中比較經(jīng)典的三種方法。

▋例 2：Countif 法求整數(shù)周期

如果累計(jì)經(jīng)營(yíng)性現(xiàn)金流回正后的剩余經(jīng)營(yíng)期間都不會(huì)變?yōu)樨?fù)數(shù)，那么首次回正時(shí)間就是投資回收期。

如下圖中，累計(jì)經(jīng)營(yíng)性現(xiàn)金流在第 4 期回正后，剩余的第 5-6 期都是正數(shù)，沒(méi)有轉(zhuǎn)為負(fù)數(shù)，此時(shí)，首次回正時(shí)間是第 4 期，投資回收期間即為 4。

這種情況下，計(jì)算回收期問(wèn)題就等同于在表示累計(jì)經(jīng)營(yíng)性現(xiàn)金流的一組數(shù)值中統(tǒng)計(jì)負(fù)數(shù)的個(gè)數(shù) n，如果這組數(shù)值包括代表投資首期期初的第 0 期，那么 n 即為投資回收期，否則 n+1 為投資回收期。

因此，使用 Countif 函數(shù)來(lái)統(tǒng)計(jì)負(fù)數(shù)的個(gè)數(shù)進(jìn)而計(jì)算投資回收期，就順理成章了。

公式如下：

=COUNTIF(B3:H3,"<0")

公式說(shuō)明：

Countif 函數(shù)用于統(tǒng)計(jì)滿足條件的單元格個(gè)數(shù)，它的首個(gè)參數(shù)（條件區(qū)域）B3:H3 為包含第 0 期的累計(jì)經(jīng)營(yíng)性現(xiàn)金流數(shù)值組，第二個(gè)參數(shù)設(shè)置為"<0"，即可統(tǒng)計(jì)累計(jì)經(jīng)營(yíng)性現(xiàn)金流數(shù)值組中小于 0 的個(gè)數(shù)，其結(jié)果就是投資回收期。如果條件區(qū)域不包含第 0 期，則公式如下：

=COUNTIF(C3:H3,"<0")+1

▋例 3：Frequency 法求整數(shù)周期

在一組數(shù)值中統(tǒng)計(jì)負(fù)數(shù)的個(gè)數(shù) n，F(xiàn)requency 函數(shù)也是一把好手，而且似乎公式更為簡(jiǎn)潔。

公式如下:

=FREQUENCY(B3:H3,0)

公式說(shuō)明：

Frequency 函數(shù)用于計(jì)算數(shù)據(jù)范圍內(nèi)的單元格數(shù)值在指定范圍中的分布頻率，怎么理解？

Frequency 函數(shù)的基礎(chǔ)語(yǔ)法：

=FREQUENCY(Data_array,Bins_array)=FREQUENCY(統(tǒng)計(jì)的區(qū)域,分段點(diǎn))

相當(dāng)于將第一個(gè)參數(shù)（數(shù)據(jù)范圍）上的所有數(shù)值依次在數(shù)軸上描點(diǎn)，再按第二個(gè)參數(shù)（指定范圍）的 n 個(gè)數(shù)值將數(shù)軸分為 n+1 段，統(tǒng)計(jì)每一數(shù)軸上的數(shù)據(jù)點(diǎn)個(gè)數(shù)。

本例中的第二個(gè)參數(shù)為 0，F(xiàn)requency 函數(shù)以 0 為分界點(diǎn)，返回 B3:H3 中小于等于 0 的數(shù)據(jù)點(diǎn)個(gè)數(shù) 4，即投資回收期。

需要注意的是，如果累計(jì)經(jīng)營(yíng)性現(xiàn)金流可能出現(xiàn)嚴(yán)格等于 0 的情況，就會(huì)有點(diǎn)問(wèn)題 , 如下圖：

如果數(shù)據(jù)點(diǎn)包含 0 , 分段點(diǎn)為 0 的情況下 , 0 會(huì)被包含進(jìn)去。

更加嚴(yán)謹(jǐn)?shù)墓綉?yīng)該使用-0.1^9 這樣接近于 0 的負(fù)數(shù)來(lái)作為分界點(diǎn)，公式如下：

=FREQUENCY(B3:H3,-0.1^9)

公式說(shuō)明：

B3:H3 中小于等于-0.1^9 的值有 4 個(gè)（包含第 0 期），大于 0 的值有 3 個(gè)，F(xiàn)requency 計(jì)算得到{4;3}，公式返回 4。

▋例 4：Match 法取整數(shù)周期

有些時(shí)候，累計(jì)經(jīng)營(yíng)性現(xiàn)金流在短暫回正后，會(huì)重新轉(zhuǎn)為負(fù)數(shù)，然后在一段時(shí)間后再次實(shí)現(xiàn)回正。

此時(shí)，使用上述兩種方法計(jì)算投資回收期就會(huì)出錯(cuò)。

例如下圖中，累計(jì)經(jīng)營(yíng)性現(xiàn)金流在第 2 期首次回正后，在 3-4 期右轉(zhuǎn)為負(fù)數(shù)，第 5 期才完全實(shí)現(xiàn)回正，該例中的投資回收期應(yīng)該為 5，但上述兩個(gè)公式的計(jì)算結(jié)果都為 4，顯然錯(cuò)誤。

這是因?yàn)椋@種情況下計(jì)算回收期不再等同于求負(fù)數(shù)的個(gè)數(shù)，而是求最后一個(gè)負(fù)數(shù)出現(xiàn)的位置序數(shù)，我們需要使用 Match 的模糊查找來(lái)實(shí)現(xiàn)。

公式如下：

=MATCH(-0.1^9,B3:H3,1)

公式說(shuō)明：

=MATCH(查找目標(biāo),查找范圍,查找方式)

Match 的最后一個(gè)參數(shù)為 1，表示模糊查找，公式返回條件區(qū)域 B3:H3 中不大于第 1 個(gè)參數(shù)-0.1^9（無(wú)限接近于 0）的最后一個(gè)值所處的位置，B3:H3 中滿足這個(gè)條件的值為-6，它是 B3:H3 中的第 5 個(gè)值，因此，公式返回 5。

求精確回收期的方法

如果我們需要計(jì)算精確的投資回收周期，則上述三種方法都將不再適用。

這是因?yàn)椋塾?jì)現(xiàn)金流回正的當(dāng)期，所對(duì)應(yīng)的回收期不再為 1，而是取上期累計(jì)經(jīng)營(yíng)性現(xiàn)金流回正缺口占當(dāng)期經(jīng)營(yíng)性現(xiàn)金流的比值。

例 4 中，累計(jì)經(jīng)營(yíng)性現(xiàn)金流在第 5 期實(shí)現(xiàn)回正，但第 4 期累計(jì)經(jīng)營(yíng)性現(xiàn)金流為-6，經(jīng)營(yíng)性凈流入只需再實(shí)現(xiàn)+6，即可實(shí)現(xiàn)回正，而第 5 期經(jīng)營(yíng)性現(xiàn)金流為+140，相當(dāng)于實(shí)現(xiàn)+6 僅占用了 6/140=0.04 期時(shí)間，所以精確回收期應(yīng)該為 4.04，而不是 5。

此時(shí)，我們可以使用 Lookup 來(lái)計(jì)算精確回收期，公式簡(jiǎn)單，但理解起來(lái)可能有點(diǎn)難度。

B6 單元格公式如下：

=LOOKUP(-0.1^9,B3:H3,COLUMN(A:G)-1-B3:H3/C2:I2)

公式說(shuō)明:

查詢區(qū)域 Column(A:G)-1-B3:H3/C2:I2 的設(shè)置是本公式的核心。

其中 Column(A:G)-1 返回 0-6 組成的數(shù)組，表示當(dāng)前期間以前經(jīng)歷的期數(shù)，-B3:H3/C2:I2 為上期累計(jì)經(jīng)營(yíng)性現(xiàn)金流回正缺口占當(dāng)期經(jīng)營(yíng)性現(xiàn)金流的比值，只有在現(xiàn)金流回正的前一期，查詢區(qū)域?qū)?yīng)位置的值才等于投資回收期，其余數(shù)值均為無(wú)效結(jié)果。

而 Lookup 的原理與 Match 模糊查找類似，剛好能夠準(zhǔn)確定位累計(jì)現(xiàn)金流回正前一期的位置，它根據(jù)條件區(qū)域 B3:H3 中不大于第 1 個(gè)參數(shù)-0.1^9 的最后一個(gè)值所處的位置 F3，返回查詢區(qū)域中對(duì)應(yīng)位置的值Columu(E:E)-1-F3/G2，即 4.04，從而完成投資回收期的精確計(jì)算。

以上，就是我分享的5 種計(jì)算回收期的方法，包括：

?使用 IF+Max 構(gòu)建輔助行再進(jìn)行求和；

?使用 Countif 統(tǒng)計(jì)小于 0 的數(shù)值個(gè)數(shù)；

?使用 Frequency 統(tǒng)計(jì)數(shù)據(jù)范圍小于等于 0 的頻率；

?使用 Match 模糊匹配最后一個(gè)負(fù)數(shù)的位置序數(shù)；

?使用 Lookup 構(gòu)建內(nèi)含數(shù)組計(jì)算精確回收周期。

這些方法，特別是 Match 和 Lookup 兩種方法，是否解決了你在計(jì)算投資回收期方面的困惱呢？

掌握 Excel，真的可以助你解決數(shù)據(jù)難題，讓你的工作更高效！

如果你想更深入系統(tǒng)地學(xué)習(xí)，那么一定要加入秋葉 Excel 3 天集訓(xùn)營(yíng)~

秋葉金牌講師 @拉登Dony 手把手帶你提升 Excel，現(xiàn)在加入，還能獲得秋葉學(xué)員實(shí)戰(zhàn)用的10 套精選表格模板：

先給大家看看部分截圖 ↓

名額有限，學(xué)到就是賺到！

秋葉 Excel 3 天集訓(xùn)營(yíng)

現(xiàn)在僅需 0 元

▲ 現(xiàn)在報(bào)名，即可免費(fèi)領(lǐng)取超多福利資料