日期:2016-06-01
如果年報酬率12%,每月投入1萬元,以複利計算40年後的淨值高達1億1,765萬元。這是數學算出來的結果,不會錯誤的。只是,報酬率為何每年12%,年報酬率若是改成6.0%,結果卻只剩1,991萬元。也就是說報酬率是關鍵因素,既然這樣又為何不用20%或50%,甚至以100%去計算,結果不是更驚人嗎!
報酬率不是愈高愈好,其中還有一項重要的因素未考慮到,就是報酬率愈高,所伴隨的風險就愈大。只是一般都將風險給忽略了,因為風險到底要如何描述,並不是件簡單的事。風險可以用波動程度來描述,也就是偏離平均報酬率的範圍有多大,就是機率學上的標準差。
上述例子如果將波動風險考慮進來,報酬率以平均報酬率12%,標準差15%代替,每月也是投入1萬元,40年後的淨值就不再是固定的1億1,765萬元,而是落在1億1,765萬元上下,有95%的機會落在2,344萬元及1億6,906萬元之間。也就是說運氣好的話,有可能高到1億6,906萬,比平均值還要高,運氣背的話可能只剩2,344萬。
如果只談標準差並不容易理解,投資者最關心的是知道未來淨值可能會有多少,也就是加入標準差的因素之後,單筆或者是定期定額之投資,期末淨值波動程度會有多少呢?兩種方式可以回答這個問題,一個是使用機率學精確計算,另一種方法就是使用Excel的模擬功能。機率學太複雜了,使用Excel試算表的模擬功能比較親民,可以讓使用者一眼就看懂,所以本篇提供一個試算表,以及製作的方式,讓讀者除了可以看到波動後的淨值變化,也可以學到如何製作這樣的試算表。
使用者可以輸入不同的金額,以及報酬率、標準差,就可以立即看到模擬後的不同年度的淨值及走勢。至於一項投資的「報酬率」及「標準差」該設定多少,請參考《Smart智富月刊》199期56頁的怪老子專欄「算出基金虧損的機率」,該篇有詳細說明。每一次試算後,只要按一次F9鍵就會重新再模擬一次。
儲存格B5的報酬率模式提供「固定」及「模擬」兩種選擇,當報酬率設定「固定」模式時,「期末淨值明細表」的「月報酬率」欄位使用固定的數值(平均報酬率/12),期末淨值會呈現一條平滑往上的曲線,因為每一個月的報酬率都一樣,也相當於零波動或零風險的意思。
而在「模擬」的模式下,每月的報酬率透過Excel的NORM.INV及RAND兩個函數,以(平均報酬率/12)當平均值及(標準差/120.5)當標準差,模擬出常態分配的月報酬率,每月報酬率都會在月平均報酬率上下變動,每月都不一樣。只要將標準差(B4)的數值調大或調小,可以看到波動的程度也會跟著變化,當標準差接近於0時(例如0.0001%),得到的結果幾乎跟「固定」一樣,因為波動為0。
提醒:標準差的數值不得為0,否則會發生錯誤。
試算表分成四大部分:變數區(A1:B5)、期末淨值明細表(A19:D500)、不同年數之期末淨值(A8:D16)、以及期末淨值走勢圖。
變數區
使用者可以調整的變數,總共有5個儲存格,B1為期初投入的單筆金額,B2為每月持續投入的金額,B3為年平均報酬率,B4為報酬率的標準差,B5則是報酬率模式,有「固定」或「模擬」兩種選擇。
期末淨值明細表
列出從期初至40年的計算明細,每月為一期總共480期。「投入金額」欄位列出了每月投入金額。「月報酬率」欄位列出每月的報酬率。「期末淨值」欄位則等於上一個月的期末淨值乘上(1+當月報酬率),再加上這個月的投入金額,所以相當於複利的計算方式。
不同年數之期末淨值
挑選所要觀察年數的期末淨值,雖然「期末淨值明細表」已經列出了40年每一個月的期末淨值,若想知道第10年及20年的期末淨值,就得分別查看第120月及240個月的期末淨值,過程會有些許不方便。只要在觀察年數的欄位中,利出想觀察的年數,一眼就可以看到多個觀察點的期末淨值。
期末淨值走勢圖
期末淨值走勢圖從期初~480月的淨值以折線圖的方式表現,水平軸為月數,垂直軸為淨值。
點選下列Excel圖示可下載試算表:
步驟 |
說明 |
---|---|
1 | 儲存格A1:A5分別鍵入文字「期初投入」、「每月投入」、「平均報酬率」、「標準差」、「報酬率模式」。儲存格B1:B4分別鍵入數值0、10000、12.0%、15.0%。 |
2 | 儲存格B5鍵入清單,首先滑鼠點選儲存格B5,(圖示 1)接著點選資料索引標籤,(圖示
2)再點選資料驗證中的資料驗證,就會出現資料驗證小視窗,(圖示 3)於「儲存格內允許」的下拉選單中選取「清單」,「來源」填入“模擬,固定”。 |
3 | 將儲存格範圍B1:B5定義成A1:A5的名稱。(圖示 1)滑鼠選取A1:B5,(圖示
2)點選「公式」索引標籤,(圖示 3)再點選「從選取範圍建立」,(圖示 4)就會跳出「以選取範圍建立名稱」的小視窗勾選最左欄,再按確定按鈕即可。 |
步驟 |
說明 |
---|---|
1 | 儲存格A18鍵入粗體字“期末淨值明細表”,且在儲存格範圍A19:D19分別鍵入文字:月數、投入金額、月報酬率、期末淨值。 |
2 | 「月數」欄位的第一列(A20)鍵入文字“期初”,其他列數(A21:A500)以數列方式填滿1~480的數字。 |
3 | 設定成表格, (圖示 1)選取儲存格範圍A19:D500, (圖示 2)點選「插入」索引標籤,再選擇「表格」後會出現建立表格小視窗,
(圖示 3)確認儲存格範圍正確及勾選有標題表格後按確定。 |
4 | 將表格命名,方法是滑鼠點選表格中任一儲存格(例如B22), (圖示 1)點選資料表工具的「設計」的索引標籤,
(圖示 2)在表格名稱欄位中輸入“期末淨值明細表”。 |
5 | 「投入金額」欄位的第1列(B20),鍵入公式【=期初投入】,第2列(B21) 鍵入公式【=每月投入】,再將儲存格B21複製至儲存格B22:B500。 |
6 | 「月報酬率」欄位的第1列(C20)留空白,第2列(C21)鍵入公式【=IF(報酬率模式="固定",平均報酬率/12,NORM.INV(RAND(),平均報酬率/12,標準差/12^0.5))】,然後將C21複製至C22:C500。 |
7 | 「期末淨值」欄位的第1列(D20)鍵入公式【=[@投入金額]】,第2列(D21) 鍵入公式【=D20*(1+[@月報酬率])+[@投入金額]】,然後將D21複製至D22:D500。 |
步驟 |
說明 |
---|---|
1 | 儲存格A7鍵入文字“不同年數之期末淨值”。 |
2 | 儲存格A8:B8分別鍵入文字“觀察年數”及“期末淨值”。然後將A8:B16設定成表格,方式同步驟二之第3項,表格名稱使用預設值即可。 |
3 | 「觀察年數」的欄位以數列填滿鍵入5~40的數字,每列間隔5。 |
4 | 「期末淨值」欄位的第1列(B9)鍵入公式【=INDEX(期末淨值明細表[期末淨值],[@觀察年數]*12+1)】。然後將儲存格B9複製至B10:B16。 |
步驟 |
說明 |
---|---|
1 | 滑鼠選取儲存格範圍D19:D500,點選「插入」索引標籤,再選擇圖表中的折線圖 |
2 | 圖表的水平座標軸鍵入A20:A500。 |