對投資新手的簡單易懂教學-Excel在投資分析資料的應用-計算單利與複利(EFFECT函數)

對投資新手的簡單易懂教學-Excel在投資分析資料的應用-計算單利與複利(EFFECT函數)

為何要計算利率?

萬物皆有價格,那金錢的價格就是利率,當要選擇哪種投資標的或是向哪間銀行借款,必須要先了解如何正確計算利息、銀行借款利息或投資股票的報酬率,才能做出最適合的選擇。

如何計算「單利」與「複利」?

此部分先說明如何計算單利與複利,並演示如何用EXCEL計算。

本例以定存來做說明:

貝多羅(筆者)若向銀行存100元的定存,年利率10%,預計存10年,

一年後本金加利息就是110元,多出來的10元就是利息。

若會將每年茲息領出,只留原來本金續存,就是單利反之,不會將每年茲息領出,將本金與利息一起續存,利滾利,就是複利

「單利」利息

若每年都把利息10元領出去,每年可再投資定存的金額只有100元,

故下一年的利息也還是10元…最終第十年後,每年利息都是10元不變,單利十年的利息會是10*10=100元。

單利各年計算表

時間 利息 再投資金額 計算式
第1年後 10
100 =100*(1+10%)=110
2年後
10
100
=100*(1+10%)=110
第3年後
10
100
=100*(1+10%)=110
第4年後
10
100
=100*(1+10%)=110
第5年後
10
100
=100*(1+10%)=110
第6年後
10
100
=100*(1+10%)=110
... ... ... ...
10年後
10
100
=100*(1+10%)=110
總計 100


「複利」利息

若每年不把利息10元領出去,第一年後的可再投資定存金額就會變成110元,而第二年後的本利和就是110*(1+10%)=121,利息是121-100=21元,利息此時較單利的10元增加了11元。

若持續做定存到第十年,第十年後的利息會是159.37,反而遠大於本金的100元。複利十年利息總額是753.12元,也遠遠大於單利十年的100

愛因斯坦曾說:「宇宙間最大的能量是複利,世界的第八大奇蹟是複利」。

複利各年計算表


時間 利息(四捨五入到小數點第二位) 再投資金額 計算式
第1年後
10.00
110.00
=100*(1+10%)=110
第2年後
21.00
121.00
=110*(1+10%)=121
第3年後
33.10
133.10
=121*(1+10%)=133.1
第4年後
33.10
146.41
=133.1*(1+10%)=146.41
第5年後
61.05
161.05
=146.41*(1+10%)=161.05
第6年後
77.16
177.16
=161.05*(1+10%)=177.16
...
... ... ...
第10年後
159.37
259.37
=235.79*(1+10%)=259.37
總計 753.12


研究範例

例如有一商品,宣稱投入200萬,在5年後可拿回2,499,527元,

單利利率算法:

2,499,527-2,000,000=499,527(五年利息總額)(499,527/5)/2,000,000=約為5.00%,其意義代表「每年利息/投入本金」約為5.00%

但依實務在用的複利算法,利率只有4.56%

用EXCEL示範單利與複利利率比較

如何用excel計算複利:

第一種方法:使用EFFECT函數

可在插入函數的「財務」類別,選擇「EFFECT」函數。

EXCEL函數-EFFECT


依「本金*本金(EFFECT(利率,期間數)*第幾期,第幾期)」方式,輸入各期資料,結果會如下圖所示,至於「第幾期」部分是直接指定G欄,這樣參數就能從外部輸入,能較快達成想要的結果。

如何用EFFECT函數計算複利


第二種方法:直接在儲存格打上計算式

下圖的B21是本金的值,而C21是利率的值,之所以打成$B$21代表絕對位置,這樣拉公式往下拉時,之後的公式不會隨之變動(沒打$符號的就會變動)*」是星號,代表相乘,「^」就是脫字符,代表計算次方

以第二年為例,公式會是100*(1+10%)^2,可以寫成100*(1+10%)*(1+10%),也就是110*(1+10%),最後會等於121

以EXCEL的公式計算複利




延伸閱讀
pattra的大頭照
貝多羅

曾在上市櫃財務會計單位服務多年,經歷財務、徵信與經營分析等工作,喜歡爬山、旅遊與閱讀。 取筆名「貝多羅」是因最初的佛經是刻寫在貝多羅葉上,希望我的文章能有文以載道之效。

留言