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

發(fā)送【7】

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

本文作者：小花

本文編輯：竺蘭

大家好，這里是秋葉 Excel~

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

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

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

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

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

C4 公式如下：

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

公式說明：

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

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

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

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

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

其實不然！

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

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

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

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

別再猶豫了，趕緊掃碼

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

助力自己在職場中脫穎而出吧！

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

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

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

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

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

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

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

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

公式如下：

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

公式說明：

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

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

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

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

公式如下:

=FREQUENCY(B3:H3,0)

公式說明：

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

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

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

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

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

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

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

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

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

公式說明：

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

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

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

此時，使用上述兩種方法計算投資回收期就會出錯。

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

這是因為，這種情況下計算回收期不再等同于求負(fù)數(shù)的個數(shù)，而是求最后一個負(fù)數(shù)出現(xiàn)的位置序數(shù)，我們需要使用 Match 的模糊查找來實現(xiàn)。

公式如下：

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

公式說明：

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

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

求精確回收期的方法

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

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

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

此時，我們可以使用 Lookup 來計算精確回收期，公式簡單，但理解起來可能有點難度。

B6 單元格公式如下：

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

公式說明:

查詢區(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īng)營性現(xiàn)金流回正缺口占當(dāng)期經(jīng)營性現(xiàn)金流的比值，只有在現(xiàn)金流回正的前一期，查詢區(qū)域?qū)?yīng)位置的值才等于投資回收期，其余數(shù)值均為無效結(jié)果。

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

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

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

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

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

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

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

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

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

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

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

先給大家看看部分截圖 ↓

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

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

現(xiàn)在僅需 0 元

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