如何使用excel計算投資收益率
啊南 5293閱讀 2020.06.18
【導語】: 投資收益率怎么計算?實際上,EXCEL這個工具如果你運用得好,可以解決你大部分的計算問題,而投資理財的投資收益率,當你了解相關的公式之后,也是可以輕松解決的。
本文介紹 Microsoft Excel 中 NPV 函數的公式語法和用法。
說明
使用貼現率和一系列未來支出(負值)和收益(正值)來計算一項投資的凈現值。
語法
NPV(rate,value1,[value2],...)
NPV 函數語法具有下列參數:
rate 必需。 某一期間的貼現率。
value1, value2, ... Value1 是必需的,后續值是可選的。 這些是代表支出及收入的 1 到 254 個參數。
Value1, value2, ...在時間上必須具有相等間隔,并且都發生在期末。
NPV 使用 value1, value2,... 的順序來說明現金流的順序。 一定要按正確的順序輸入支出值和收益值。
忽略以下類型的參數:參數為空白單元格、邏輯值、數字的文本表示形式、錯誤值或不能轉化為數值的文本。
如果參數是一個數組或引用,則只計算其中的數字。 數組或引用中的空白單元格、邏輯值、文本或錯誤值將被忽略。
備注
NPV 投資開始于 value1 現金流所在日期的前一期,并以列表中最后一筆現金流為結束。 NPV 的計算基于未來的現金流。 如果第一筆現金流發生在第一期的期初,則第一筆現金必須添加到 NPV 的結果中,而不應包含在值參數中。 有關詳細信息,請參閱下面的示例。
如果 n 是值列表中現金流的筆數,則 NPV 的公式如下:
NPV 類似于 PV 函數(現值)。 PV 與 NPV 的主要差別在于:PV 既允許現金流在期末開始也允許現金流在期初開始。 與可變的 NPV 的現金流值不同,PV 現金流在整個投資中必須是固定的。 有關年金與財務函數的信息,請參閱 PV。
NPV 與 IRR 函數(內部收益率)也有關。 函數 IRR 是使 NPV 等于零的比率:NPV(IRR(...), ...) = 0。
示例
復制下表中的示例數據,然后將其粘貼進新的 Excel 工作表的 A1 單元格中。 要使公式顯示結果,請選中它們,按 F2,然后按 Enter。 如果需要,可調整列寬以查看所有數據。
數據 |
說明 |
|
---|---|---|
0.1 |
年貼現率 |
|
-10000 |
一年前的初期投資 |
|
3000 |
第一年的收益 |
|
4200 |
第二年的收益 |
|
6800 |
第三年的收益 |
|
公式 |
說明 |
結果 |
=NPV(A2, A3, A4, A5, A6) |
此項投資的凈現值 |
¥11,884.40 |
數據 |
說明 |
|
---|---|---|
0.08 |
年貼現率。 可表示整個投資的通貨膨脹率或利率。 |
|
-40000 |
初期投資 |
|
8000 |
第一年的收益 |
|
9200 |
第二年的收益 |
|
10000 |
第三年的收益 |
|
12000 |
第四年的收益 |
|
14500 |
第五年的收益 |
|
公式 |
說明 |
結果 |
=NPV(A2, A4:A8)+A3 |
此項投資的凈現值 |
¥19,220.60 |
=NPV(A2, A4:A8, -9000)+A3 |
此項投資的凈現值,包括第六年 9000 的賠付 |
(¥37,494.70) |
返回定期支付利息的債券的收益。 函數 YIELD 用于計算債券收益率。
語法
YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis])
重要: 應使用 DATE 函數輸入日期,或者將日期作為其他公式或函數的結果輸入。
例如,使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。 如果日期以文本形式輸入,則會出現問題。
YIELD 函數語法具有下列參數:
Settlement 必需。 有價證券的結算日。 有價證券結算日是在發行日之后,有價證券賣給購買者的日期。
Maturity 必需。 有價證券的到期日。 到期日是有價證券有效期截止時的日期。
Rate 必需。 有價證券的年息票利率。
Pr 必需。 有價證券的價格(按面值為 ¥100 計算)。
Redemption 必需。 面值 ¥100 的有價證券的清償價值。
Frequency 必需。 年付息次數。
如果按年支付,frequency = 1;按半年期支付,frequency = 2;按季支付,frequency = 4。
Basis 可選。 要使用的日計數基準類型。
說明
WPS表格可將日期存儲為可用于計算的序列號。默認情況下,1900年1月1日的序列號是 1,而2008年1月1日的
序列號是 39448,這是因為它距1900年1月1日有 39448 天。
結算日是購買者買入息票(如債券)的日期。 到期日是息票有效期截止時的日期。 例如,在2008年1月1日發行
的30年期債券,六個月后被購買者買走。則發行日為2008年1月1日,結算日為2008年7月1日,而到期日是在發
行日2008年1月1日的 30 年后,即2038年1月1日。
Settlement、maturity、frequency 和 basis 將被截尾取整。
如果 settlement 或 maturity 不是有效日期,函數 YIELD 返回 錯誤值 #VALUE!。
如果 rate < 0,函數 YIELD 返回 錯誤值 #NUM!。
如果 pr ≤ 0 或 redemption ≤ 0,函數 YIELD 返回 錯誤值 #NUM!。
如果 frequency 不為數字 1、2 或 4,函數 YIELD 返回 錯誤值 #NUM!。
如果 basis < 0 或 basis > 4,函數 YIELD 返回 錯誤值 #NUM!。
如果 settlement ≥ maturity,函數 YIELD 返回 錯誤值 #NUM!。
如果在清償日之前只有一個或是沒有付息期間,函數 YIELD 的計算公式為:
其中:
A = 付息期的第一天到結算日之間的天數(應計天數)。
DSR = 結算日與清償日之間的天數。
E = 付息期所包含的天數。
如果在 redemption 之前尚有多個付息期間,則通過 100 次迭代來計算函數 YIELD。
基于函數 PRICE 中給出的公式,并使用牛頓迭代法不斷修正計算結果。
這樣,收益率將不斷更改,直到根據給定收益率計算的估計價格接近實際價格。
在財務中會遇到計算定期支付利息債券的收益率等情況,此時可以使用WPS表格中的YIELD函數。
例如,購買了10年期的國債,每年都要支付利息,到期后支付最后一期的利息和本金,要計算這一投資行為的實際投資收益率。
我們在這個表格中實際操作看看幫助大家理解。
選中B8單元格,點擊插入函數。
在查找框中查找并選中YIELD函數,點擊確定。
此時彈出對話框,共七項參數。
成交日是證券的結算日,即發行日期之后,證券賣給購買者的日期,填入B1。
到期日指證券有效期截止時的日期,填入B2。
利率是證券的年息票利率,填入B3。
票面價值即證券的票面價值,填入B4
面值100元的證券的清償價值,填入B5。
年付息次數可填1、2和4。
其中1代表按年支付,2代表按半年支付,4代表按季度支付。
因為我們的年付息類型是按年支付,所以我們在年付息次數中輸入1。
基準選項是采用的年基準類型。
如下表所示有4種類型,此處輸入1以實際天數計算。點擊確定,即可快速計算。
返回某一連續期間內現金流的修正內部收益率。函數 MIRR 同時考慮了投資的成本和現金再投資的收益率。
語法
MIRR(values,finance_rate,reinvest_rate)
values 為一個數組或對包含數字的單元格的引用。這些數值代表著各期的一系列支出(負值)及收入(正值)。
? 參數 Values 中必須至少包含一個正值和一個負值,才能計算修正后的內部收益率,否則函數 MIRR 會返回錯誤
值 #DIV/0!。
? 如果數組或引用參數包含文本、邏輯值或空白單元格,則這些值將被忽略;但包含零值的單元格將計算在內。
finance_rate 為現金流中使用的資金支付的利率。
reinvest_rate 為將現金流再投資的收益率。
說明
? 函數 MIRR 根據輸入值的次序來解釋現金流的次序。所以,務必按照實際的順序輸入支出和收入數額,并使用正
確的正負號(現金流入用正值,現金流出用負值)。
隨著時代發展,越來越多的人選擇自主理財,那么如何計算投資成本和再投資的收益率呢?
此處涉及MIRR函數。
下面就詳細的為大家演示一下,如何使用此函數計算再投資的收益率。
具體方法與步驟如下:
首先打開表格,將光標放在C11處,點擊插入函數-財務-MIRR函數。
此時彈出對話框,共三項參數:
“現金流”指固定期內支出和收入,輸入C3:C8;
“支付利率”指投入資金和融資利率,輸入C9;
“再投資的收益率”指各期收入凈額再投資的報酬率,輸入C10。
點擊確定,就能得出再投資的收益率了。
這個小技巧,你學會了嗎?
返回由數值代表的一組現金流的內部收益率。這些現金流不必為均衡的,但作為年金,它們必須按固定的間隔產生,
如按月或按年。內部收益率為投資的回收利率,其中包含定期支付(負值)和定期收入(正值)。
語法
IRR(values,guess)
values 為數組或單元格的引用,包含用來計算返回的內部收益率的數字。
? Values 必須包含至少一個正值和一個負值,以計算返回的內部收益率。
? 函數 IRR 根據數值的順序來解釋現金流的順序。故應確定按需要的順序輸入了支付和收入的數值。
? 如果數組或引用包含文本、邏輯值或空白單元格,這些數值將被忽略。
guess 為對函數 IRR 計算結果的估計值。
? WPS表格 使用迭代法計算函數 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 的有效值)。
當我們要資金投資的時候,需要計算內部收益率,來判斷投資的合理性,這時可以用IRR函數計算。
其函數的意義是返回由數值代表的一組現金流的內部收益率,使用這個函數能幫助我們快速的求出收益率。
以這個表格數據為例,我們來做一下運算。
將數據庫中符合指定條件的記錄的特定字段中的值的乘積。
語法
DPRODUCT(database,field,criteria)
Database 構成列表或數據庫的單元格區域。數據庫是包含一組相關數據的列表,其中包含相關信息的行為記錄,
而包含數據的列為字段。列表的第一行包含著每一列的標志項。
Field 指定函數所使用的數據列。列表中的數據列必須在第一行具有標志項。Field 可以是文本,即兩端帶引號的
標志項,如“樹齡”或“產量”;此外,Field 也可以是代表列表中數據列位置的數字:1 表示第一列,2 表示第
二列,等等。
Criteria為一組包含給定條件的單元格區域??梢詾閰?criteria 指定任意區域,只要它至少包含一個列標志和列
標志下方用于設定條件的單元格。
在生活和工作中我們常常利用Excel表格中的函數功能進行計算。
那么如何使用DPRODUCT函數計算指定區域并且滿足條件的數據乘積呢?
以此銷售表為例。
具體方法與步驟如下:
將光標放在(G3)處,點擊插入函數-數據庫-DPRODUCT函數。
此時彈出對話框,共三項參數:
“數據庫區域”是引用數據的區域;
“操作域”是指包含的單元格區域;
“條件”是所設置的區域條件。
我們在“數據庫區域”中輸入(B5:E17);
因為我們引用區域共四項單元格,所以在“操作域”中輸入4;
在“條件”中輸入(B2:E4),意思是我們設置的條件是銷售量大于1000的數據。
返回一組不一定定期發生的現金流的內部收益率。 若要計算一組定期現金流的內部收益率,請使用函數 IRR。
語法
XIRR(values, dates, [guess])
XIRR 函數語法具有下列參數:
值:必需,與 dates 中的支付時間相對應的一系列現金流。首期支付是可選的,并與投資開始時的成本或支付有
關。如果第一個值是成本或支付,則它必須是負值。所有后續支付都基于 365 天/年貼現。值系列中必須至少包含
一個正值和一個負值。
日期:必需,與現金流支付相對應的支付日期表。日期可按任何順序排列。應使用 DATE 函數輸入日期,或者將
日期作為其他公式或函數的結果輸入。
例如,使用函數 DATE(2008,5,23) 輸入 2008 年 5 月 23 日。 如果日期以文本形式輸入,則會出現問題 。
Guess:可選,對函數 XIRR 計算結果的估計值。
說明
WPS表格可將日期存儲為可用于計算的序列號。默認情況下,1900年1月1日的序列號是 1,而2008年1月1日的
序列號是 39448,這是因為它距1900年1月1日有 39448 天。
Dates 中的數值將被截尾取整。
函數 XIRR 要求至少有一個正現金流和一個負現金流,否則函數 XIRR 返回 錯誤值 #NUM!。
如果 dates 中的任一數值不是有效日期,函數 XIRR 返回 錯誤值 #VALUE!。
如果 dates 中的任一數字早于開始日期,函數 XIRR 返回 錯誤值 #NUM!。
如果 values 和 dates 所含數值的數目不同,函數 XIRR 返回 錯誤值 #NUM!。
大多數情況下,不必為函數 XIRR 的計算提供 guess 值。 如果省略, guess 值假定為 0.1 (10%)。
函數 XIRR 與凈現值函數 XNPV 密切相關。 函數 XIRR 計算的收益率即為函數 XNPV = 0 時的利率。
WPS表格使用迭代法計算函數 XIRR。
通過改變收益率(從 guess 開始),不斷修正計算結果,直至其精度小于 0.000001%。
如果函數 XIRR 運算 100 次,仍未找到結果,則返回 錯誤值 #NUM!。 函數 XIRR 的計算公式如下:
其中:
di = 第 i 個或最后一個支付日期。
d1 = 第 0 個支付日期。
Pi = 第 i 個或最后一個支付金額。
隨著時代發展,越來越多的人開始自主理財,那么如何知道自己基金投入與分紅的收益率呢?
此處涉及XIRR函數,以此基金收支表為例。
具體方法與步驟如下:
將光標放在D9處,點擊插入函數-財務-XIRR函數。
此時彈出對話框,共三項參數。
“現金流”是財務收支的數值,此處輸入C3:C8。
“日期流”是財務收支所對應的日期,此處輸入B3:B8。
“預估值”是內部報酬率的猜測值,若忽略則為百分之十,此處我們忽略不填。
點擊確定,就能得出此基金的收益率了。