在財務(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é)會小花分享的這些公式，讓你輕松拿捏它。

求整數(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(查找目標(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)位置的值?COLUMN(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 兩種方法，是否解決了你在計算投資回收期方面的困惱呢？

學(xué) Excel，來《秋葉 Excel 3 天集訓(xùn)營》，還有更多技巧等著你！

?? 集訓(xùn)營里，不僅教透視表、常用函數(shù)、圖表的使用技巧，更教 Excel 數(shù)據(jù)處理的思路方法、教表格設(shè)計的內(nèi)功心法；

???上課有老師在線直播講解；有學(xué)員群，有助教督促你一起學(xué)習(xí)；

???遇到問題，現(xiàn)場提問、現(xiàn)場解答，再也不用一個人苦苦撓破頭！

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

想提升，趁現(xiàn)在！

助你不再被加班支配，不再為表格發(fā)愁！

現(xiàn)在報名

還免費贈送【35 個常用函數(shù)說明】！

趕緊掃碼預(yù)約吧！

??????

*廣告