怪老子ETF線上課程

EXCEL投資理財之應用

日期:2007-03-11

在投資的規劃上,我們常常需要一些計算,例如我們想知道向銀行貸款兩百萬,分20年本息定額償還,那麼每月應該支付多少錢。或者有一個基金,五年的報酬率為96%,那麼年化報酬率是多少?諸如此類問題,Excel是個非常實用的工具,可以快速算出所需要的答案。我相信很多人都會使用Excel,但是除了財務專業人士外,能把財務函數用的很熟練的似乎也不多。在本篇文章裡,我只介紹和投資相關的五個財務函數FV、PV、RATE、PMT及NPER,試著用範例的方式呈現出來,讓非專業人士都能容易上手。

基本概念

我們都知道金錢的價值會隨著時間成長,而且是以複利成長。例如將一百萬存放於銀行一年,一年後這一百萬的價值當然不只一百萬,而是一百萬再加上利息,至於利息有多少是由利率來決定,以及經過多少期的複利過程。

單筆及定期投資

投資種類依投入次數可分為單筆投資以及定期重複性投資。以銀行的『整存整付式定存』就是最好的單筆投資的例子,在期初時投入一筆金額,期中都沒有再投入,等到期末後領回一筆本利和。『零存整付式定存』便是定期重複性的投資型態,定期於每一期固定投入一筆金額,等到期末後再領回本利和。下圖是這兩種投資型態的圖示。

單筆或定期

Excel的財務函數是將這兩種混合在一起,所以參數看起來較為複雜容易用錯,但只要了解以後用起來是既簡潔又方便。

定期投資的期初及期末

定期投資部份依照每期金額的投入點,又可分為期初及期末兩種(TYPE),如下圖所示:

期初及期末現金流量

現金流入及流出

Excel財務函數金額部分有正負號之分,正值代表金額流入,負數代表金額流出。例如你要計算整存整付定存的期末領回本利和。期初金額(PV)是由你交給銀行(現金流出所以是負值),期滿後銀行會給付本利和的金額(現金流入所以是正值)。相反的如果是以銀行的角度來看,期初金額(PV)因為存款流入所以是正值,期滿算出的未來值(FV)應該是負值,代表要給付存款人的金額。

如果要計算債券的現值(PV),因為債券每期會支付利息給你,屬於定期金額所以用PMT,而且是現金流入所以要用正值。到期領回金額(FV)亦屬於現金流入都要用正值。可是算出來的現值(PV)會是負值,代表要付出PV(現金流出)的金額去換得該債券未來的本息。

利率(RATE)及期數(NPER)

財務函數的利率(RATE)不是固定使用常用的年利率,而是視每一期的時間來決定利率。多久為一期則要看應用,每一種應用都不一樣,但是利率的單位一定和每一期的時間長短一致。如果每月為一期,那就要用月利率。每年為一期,就要用年利率,依此類推。

例如銀行的存放款都是以一個月為一期,所以代入的利率參數(RATE)就要用月利率(年利率/ 12)。至於期數就看整體投資期間有多長再除以每一期的時間就可以了。

如果我們想計算年利率2.26%的整存整付定存,一年後本利和的金額,因為一個月為一期,一年有12個月所以期數NPER =12。利率必須用月利率RATE = 2.26% / 12。

如果我問:銀行整存整付定存1,000,000元,年利率2.26%一年後可以拿回多少?相信很多人會答說$1,022,600,可是實際上銀行會支付$1,022,836。雖然和預期的金額不符,但是因為銀行付的比較多,大部份的人是不會追究原因的,其實是因為銀行是每月為一期(每月複利一次),所以等於分成12期來計算,每期的利率為月利率計算,等於2.26% / 12 = 0.1883%,
代入公式 =FV(2.26%/12, 12, 0, -1000000) = $1,022,836。

參數介紹

下表所列是本文所要介紹的函數功能及參數:

函數
功能
參數
FV 到期後未來值(終值) =FV(rate, nper, pmt, [pv], [type])
PV 單筆的現值 =PV(rate, nper, pmt, [fv], [type])
RATE 每期的利率或報酬率 =RATE(nper, pmt, pv, [fv], [type], [guess])
PMT 每期投資金額 =PMT(rate, nper, pv, [fv], [type])
NPER 期數 =NPER(rate, pmt, pv, [fv], [type])

這五個函數其實是跟金錢的時間價值息息相關的五個參數。當你知道任何其中四個就可以求出剩下的那一個。例如已知每期利率RATE、期初投資金額PV、每期投入PMT以及期數NPER,就可得知期末本利和FV。

參數部份有打中括號的代表可以省略的參數,其餘部分則是必須有的參數。

下載下列範例之Excel檔案

這Excel檔案有5個工作表:分別為未來值(FV)、現值(PV)、利率(RATE)、每期投資金額(PMT)、期數(NPER)。一個函數一個工作表,方便讀者參考。

未來值Future Value(FV)

當利率RATE、期數NPER、期初投資PV及每期投資PMT均為已知時,所求得的未來本利和FV。

公式 =FV(rate, nper, pmt, [pv], [type])

詳細請參考:Excel函數-FV未來值

整存整付定存

以“整存整付定存”存入銀行一百萬,每月複利一次計算,年利率5%,期間為半年。到期後本利和為多少?

RATE = 5% / 12 (每月為一期,月利率 = 年利率 / 12)
NPER = 6(半年分6期)
PMT = 0 (只有單筆所以設定0)
PV = -1,000,000(存入銀行一百萬,現金流出所以為負值)

=FV(5%/12, 6, 0, -1000000)
= $1,025,262
期末領回本利和$1,025,262

零存整付定存

每月期初均存款一萬元至銀行,年利率4.5%,一年後(12期)會領回多少錢?

RATE = 4.5% / 12 (每月為一期,月利率 = 年利率 / 12)
NPER = 12(分12期)
PMT = -10,000(每月定期一萬元,因為現金流出所以負值)
PV = 0 (其初沒有單筆投入,所以等於零)

=FV(4.5%/12, 12, -10000, 0, 1)
= $122,966。
期末領回$122,966

預估投資收益

現年37歲擁有存款兩百萬元可投資,每月扣除生活開銷外尚有餘錢三萬元可做投資運用,假如預計60歲退休,每年平均投資報酬率設定為6%,到退休時,我會擁有多少錢?

RATE = 6%/12(每月為一期,月利率 = 年利率 / 12)
NPER = 12*(60-37)(投資期數 276期)
PMT = -30,000(每月投資3萬元)
PV = -2,000,000(期初投200萬元)

=FV(6%/12, 12*(60-37), -30000, -2000000, 1)
= $25,778,895

如果想知道每年平均投資報酬率改為8%,結果會變成什麼?只需要將上述公式6%改成8%:
=FV(8%/12, 12*(60-37), -30000, -2000000, 1)
= $36,336,094。

我們立即知道相差約一千萬元,這可不是個小數目!

現值Present Value (PV)

當利率RATE、期數NPER、期末金額FV及每期投資PMT均為已知時,所求得的現值PV。

公式 =PV(rate, nper, pmt, [fv], [type])

詳細請參考:Excel函數-PV現值

退休金的現值

預期五年後可以拿到的兩百萬退休金,假使通貨膨脹率每年以2%成長,相當於現在多少的價值?

RATE = 2%(一期為一年,每年以2%成長)
NPER = 5(一年一期,所以期數等於5)
PAYMENT = 0(只有單筆,所以為0)
FV = 2,000,000(期末拿到兩百萬退休金)

=PV(2%, 5, 0, 2000000)
= -1,811,461

這代表五年後的兩百萬,如果計算通貨膨脹後,只相當於現今的1,811,461。 負值的意義是代表現金流出,現在拿出約181萬,五年後換回200萬。

債券的價值

債券每半年領息三萬元,三年半後到期,到期領回一百萬,若以年利率5%計算,相當於現值多少錢?

RATE:= 5% / 2(每半年為一期,每期利率為年利率除以2)
NPER = 7 (三年半,每半年一期總共七期)
PAYMENT = 30,000(每半年定期領息三萬元)
FV = 1,000,000(到期領回一百萬)

=PV(5%/2, 7, 30000, 1000000)
= -1,031,747

負值代表必須現在拿出-1,031,747,才可換得此債券未來之利息及本金。

利率(RATE)

當期數NPER、每期投資PMT、期初投資PV及期末金額FV均為已知時,所求得的等值利率RATE。

公式 =RATE(nper, pmt, pv, [fv], [type], [guess])

汽車貸款利率

買一輛新車80萬元整,已付頭期款20萬元,其餘60萬元分3年36期貸款,每期需繳納$19,360,求該貸款年利率為多少?通常用來驗證車商告訴我們的貸款利率是否確實。

NPER = 36 (每月一期分36期支付)
PMT= -$19,360(每期支付19,360)
PV = 600,000(貸款60萬)

=RATE(36, -19360, 600000)
= 0.83324094142316%

所求出為月利率,年利率必須再乘以12,所以等於10.00%

投資年化報酬率 I

有一股票型基金期初投資10萬元經過5年後該基金淨值成長至22萬元,求該基金之年化報酬率?

NPER = 5(每年為一期,分5期計算)
PMT = 0(每期沒有投資,所以為0)
PV = -$100,000(期初投資10萬元,現金流出)
FV = $220,000(期末淨值22萬元,現金流入)

=RATE(5, 0, -100000, 220000)
= 17.08%

這相當於每年固定以年利率17.08%成長

投資年化報酬率 II

有一股票型基金每月定期定額投資1萬元經過5年後該基金淨值為80萬元,求該基金之年化報酬率?

NPER = 5*12(每月為一期,分60期計算)
PMT = -10,000(每月投資10,000,現金流出)
PV = 0(期初沒有單筆投資)
FV = $800,000(期末淨值80萬元)

=RATE(5*12, -10000, 0, 800000,1)*12
= 10.89%

這相當於每年固定以年利率10.89%成長 (每月為一期,所以RATE計算結果為月利率,必須乘以12才會成為年利率)

每期投資金額PAYMENT (PMT)

當期初投資PV、每期利率RATE、期數NPER及期末值FV均為已知時,求得每期該投資多少金額PMT。

公式 =PMT(rate, nper, pv, [fv], [type])

本息定額償還貸款

向銀行房屋貸款350萬元,年利率3.5%,以本息定額分20年償還,每月該繳款多少錢?

RATE = 3.5%/12(每月為一期,月利率 = 年利率 / 12)
NPER = 20*12(分20*12 = 240期償還)
PV = $3,500,000(貸款金額350萬)

=PMT(3.5%/12, 20*12, 3500000)
= -$20,299

每月必需繳款本息$20,299元 (因為是現金流出所以是負值)

退休規劃

目前擁有存款200萬元,希望15年後退休,退休時必需擁有現金1000萬元,如果以年報酬率6%計算,每月該定期定額投資多少錢?

RATE = 6%/12(每月為一期,月利率 = 年利率 / 12)
NPER = 15*12(分15*12 = 180期投資)
PV = -$2,000,000(期初投資200萬)
FV = $10,000,000(期末金額1000萬)

=PMT(6%/12, 15*12, -2000000, 10000000)
= -17,509

每月需要投資17,509才有機會達成目標。 如果每月投資金額過大,試著把投資期間改為20年,再看結果 =PMT(6%/12, 20*12, -2000000, 10000000) = -7,314 試著調整這些參數,直到適合你自己的狀況為止。如果你要調整年報酬率的話,請同時考慮所帶來的波動風險。

期數(NPER)

當期每期利率RATE、每期投資金額PMT、初投資PV及期末值FV均為已知時,求多少期可以達成目標。

公式 =NPER(rate, pmt, pv, [fv], [type])

退休規劃

目前擁有存款200萬元,退休時必需擁有現金1000萬元,如果以年報酬率6%計算,每月有能力投資五萬元,多久後可以退休?

RATE = 6%/12(每月為一期,月利率 = 年利率 / 12)
PMT = -50,000(每月投資5萬元)
PV = $-2,000,000(已有200萬)
FV = $10,000,000(期末金額1000萬)

=NPER(6%/12, -50000, -2000000, 10000000)
= 103期

因為每月為一期,除以12得到約8.5年可達成目標

延伸閱讀

複利-完全攻略篇

年金-理論篇

年金-應用篇

Excel函數-PV現值




■ 本網站內容儘可能精確完整,但不保證無誤。若做為投資依據,風險請自行斟酌 ,本網站不負賠償之責任。
■ 網站所有資料均為版權所有,非經書面允許請勿轉載或使用。