輕松辦公-OfficeExcel函數精解

（十）

1、IRR函數

• 函數功能

返回由數值代表的一組現金流的内部收益率。這些現金流不必為均衡的，但作為年金，它們必須按固定的間隔産生，如按月或按年。内部收益率為投資的回收利率，其中包含定期支付（負值）和定期收入（正值）。

• 語法

IRR(values,guess)

參數說明：

• Values 為數組或單元格的引用，包含用來計算返回的内部收益率的數字。
• Values 必須包含至少一個正值和一個負值，以計算返回的内部收益率。
• 函數 IRR 根據數值的順序來解釋現金流的順序。故應确定按需要的順序輸入了支付和收入的數值。
• 如果數組或引用包含文本、邏輯值或空白單元格，這些數值将被忽略。
• Guess 為對函數 IRR 計算結果的估計值。
• Microsoft Excel 使用叠代法計算函數 IRR。從 guess 開始，函數 IRR 進行循環計算，直至結果的精度達到 0.00001%。如果函數 IRR 經過 20 次叠代，仍未找到結果，則返回錯誤值 #NUM!。
• 在大多數情況下，并不需要為函數 IRR 的計算提供 guess 值。如果省略 guess，假設它為 0.1 (10%)。
• 如果函數 IRR 返回錯誤值 #NUM!，或結果沒有靠近期望值，可用另一個 guess 值再試一次。

注解：

• 函數 IRR 與函數 NPV（淨現值函數）的關系十分密切。函數 IRR 計算出的收益率即淨現值為 0 時的利率。下面的公式顯示了函數 NPV 和函數 IRR 的相互關系：

NPV(IRR(B1:B6),B1:B6) 等于 3.60E-08

（在函數 IRR 計算的精度要求之中，數值 3.60E-08 可以當作 0 的有效值）。

• 示例

2、ISPMT函數

• 函數功能

計算特定投資期内要支付的利息。提供此函數是為了與 Lotus 1-2-3 兼容。

• 語法

ISPMT(rate,per,nper,pv)

參數說明：

• Rate 為投資的利率。
• Per 為要計算利息的期數，此值必須在 1 到 nper 之間。
• Nper 為投資的總支付期數。
• Pv 為投資的當前值。對于貸款，pv 為貸款數額。

注解：

• 應确認所指定的 rate 和 nper 單位的一緻性。例如，同樣是四年期年利率為 12% 的貸款，如果按月支付，rate 應為 12%/12，nper 應為 4*12；如果按年支付，rate 應為 12%，nper 為 4。
• 對所有參數，都以負數代表現金支出（如存款或他人取款），以正數代表現金收入（如股息分紅或他人存款）。
• 有關财務函數的其他信息，請參閱 PV 函數。
• 示例

3、LOGEST函數

• 函數功能

在回歸分析中，計算最符合數據的指數回歸拟合曲線，并返回描述該曲線的數值數組。因為此函數返回數值數組，所以必須以數組公式的形式輸入。

此曲線的公式為：

y = b*m^x 或

y = (b*(m1^x1)*(m2^x2)*_)（如果有多個 x 值）

其中因變量 y 是自變量 x 的函數。m 值是各指數 x 的底，而 b 值是常量值。注意 y、x 和 m 可以是向量，LOGEST 函數返回的數組為 {mn,mn-1,...,m1,b}。

• 語法

LOGEST(known_y's,known_x's,const,stats)

參數說明：

• Known_y's 滿足指數回歸拟合曲線 y=b*m^x 的一組已知的 y 值。
• 如果數組 known_y's 在單獨一列中，則 known_x's 的每一列被視為一個獨立的變量。
• 如果數組 known_y's 在單獨一行中，則 known_x's 的每一行被視為一個獨立的變量。
• Known_x's 滿足指數回歸拟合曲線 y=b*m^x 的一組已知的 x 值，為可選參數。
• 數組 known_x's 可以包含一組或多組變量。如果僅使用一個變量，那麼隻要 known_x's 和 known_y's 具有相同的維數，則它們可以是任何形狀的區域。如果使用多個變量，則 known_y's 必須是向量（即具有一列高度或一行寬度的單元格區域）。
• 如果省略 known_x's，則假設該數組為 {1,2,3,...}，其大小與 known_y's 相同。
• Const 為一邏輯值，用于指定是否将常數 b 強制設為 1。
• 如果 const 為 TRUE 或省略，b 将按正常計算。
• 如果 const 為 FALSE，則常量 b 将設為 1，而 m 的值滿足公式 y=m＾x。
• Stats 為一邏輯值，指定是否返回附加回歸統計值。
• 如果 stats 為 TRUE，函數 LOGEST 将返回附加的回歸統計值，因此返回的數組為 {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r 2,sey; F,df;ssreg,ssresid}。
• 如果 stats 為 FALSE 或省略，則函數 LOGEST 隻返回系數 m 和常量 b。

注解：

• 由數據繪出的圖越近似于指數曲線，則計算出來的曲線就越符合原來給定的數據。正如 LINEST 函數一樣，LOGEST 函數返回一組描述數值間相互關系的數值數組，但 LINEST 函數是用直線來拟合數據，而 LOGEST 函數則以指數曲線來拟合數據。有關詳細信息，請參閱 LINEST 函數。
• 當僅有一個自變量 x 時，可直接用下面的公式計算出 y 軸截距 (b) 的值：

Y 軸截距 (b)：

INDEX(LOGEST(known_y's,known_x's),2)

可用 y=b*m＾x 公式來預測 y 的值，但是 Microsoft Excel 另外提供了可以預測因變量 y 值的 GROWTH 函數。有關詳細信息，請參閱 GROWTH 函數。

• 對于返回結果為數組的公式，必須以數組公式的形式輸入。
• 當輸入一個數組常量（如 known_x's）作為參數時，以逗号作為同一行中各數值的分隔符，以分号作為不同行中各數值的分隔符。分隔符可能因“控制面闆”的“區域和語言選項”中區域設置的不同而有所不同。
• 應注意的一點是：如果由回歸公式所預測的 y 值超出用來計算回歸公式的 y 的取值區間，則該值可能無效。
• 示例

1、m 系數和常量 b

注釋：示例中的公式必須以數組公式輸入。将示例複制到空白工作表後，請選擇以公式單元格開始的數據區域 A9:B9。按 F2，再按 Ctrl Shift Enter。如果公式未以數組公式的形式輸入，則結果為單值 1.463275628。

當作為數組輸入時，将返回 m 系數和常量 b。

y = b*m1^x1，或使用數組中得到的值：

y = 495.3 * 1.4633x

通過替換公式中的月份 x 值，可以估計以後幾個月的銷售情況，或者可使用 GROWTH 函數。

2、完全統計

注釋：示例中的公式必須以數組公式輸入。将示例複制到空白工作表後，請選擇以公式單元格開始的數據區域 A9:B13。按 F2，再按 Ctrl Shift Enter。如果公式未以數組公式的形式輸入，則結果為單值 1.463275628。

當作為數組輸入時，将返回下面的回歸統計值，用該值可識别所需的統計值。

可以用附加的回歸統計值（上述輸出數組中的單元格 A10:B13）來确定公式對于未來值預測的有效程度。

要點：使用 LOGEST 對公式進行測試的方法與使用 LINEST 時非常近似。但是，函數 LOGEST 所返回的附加統計值是基于下面的線形模型而計算得出的：

ln y = x1 ln m1 ... xn ln mn ln b

在計算附加的回歸統計值，尤其是 sei 和 seb 值時，請牢記這些數值應與 ln mi 和 ln b 比較，而不是與 mi 和 b 比較。

更多精彩内容将在以後的章節分享給朋友們，請添加收藏，歡迎關注！

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!