日期:2014-09-01
在《理財基本功 用Excel算複利》一文中,教導讀者如何用Excel試算小孩的教育基金。該試算表比較簡單,只能用於小孩的高等教育基金,大學及研究所的費用均必須相同,而且人數限定一個小孩,這樣的試算表確實有些限制。
下圖就是這個試算表,圖中的設定小孩16年後開始上大學,假若大學一年所需費用以目前物價30萬計算,在通貨膨漲率2%,投資報酬率5%的情況下,現今就得投入105萬4,158元。
這不是一筆小數目,如果已經擁有這麼多錢,當然一點問題都沒有。但是少有父母在年輕時就擁有這麼多錢,大部分的教育基金都是透過分期投資的方式來籌措。那麼,小孩的父母親最想知道,每年或每月需要投入多少金額來籌措這筆教育基金。
其實,每月分期投入的金額,跟分成多少年的時間投入,以及投資標的報酬率有關。若分期年數一定,投資報酬率愈高,每月所需投入的金額就愈少,投資報酬率愈低,金額就需要愈高。在相同投資報酬率的情況下,籌資期間愈短,總投入的金額愈少。
每年所需投入的金額,Excel公式如下。可以清楚看出,每年投入金額跟目前所需金額、分期年數以及投資報酬率有關。
每年投入金額 =PMT(投資報酬率, 分期年數, -目前所需金額)
例如目前需要105萬4,158元,希望分5年來籌措,預備投入每年可以有5.0%的標的,那麼每年就須投入24萬3,484元【=PMT(5%, 5 ,-1054158)】,相當於每月2萬290元。
如果每月無法負擔這麼高的金額,只好將分期年數延長。因為大學費用於16~21年後才會用到,所以最長可以有21年的準備時間。若是分21年投入,同樣是5%報酬率,每年只需投入8萬2,220元,等於每月6,852元。因為貨幣有時間價值,未來所需要的金額不變,愈早分攤完畢,所需投入的總金額就愈少。下表整理了不同年數及投資報酬率的情況下,每月所需投入的金額。
分期年數 | 投資報酬率 | 每月投入金額 | 投入總金額 |
5 | 5% | 20,290 | 1,217,420 |
21 | 5% | 6,852 | 1,726,620 |
如果將Excel修改如下,每年的大學費用(以目前物價估算),可以每一年都不同,只要按需求年數填入「每年費用(現值)」的欄位,修改後能適用的範圍就更廣泛了。小孩所需的教育費用不是只有大學及研究所,從保母費、幼稚園直到研究所畢業,都可以用這張表一一列出來。
除此之外,若是有兩個以上的小孩,當然每年支出的教育費用更是不同,這張試算表也一樣可以適用,只是一年的費用只有一個儲存格可以填,所以必須自己將每一位小孩所需費用先行加總。
例如有兩個小孩,歲數剛好間隔兩年,第一位也是16年底要上大學,兩個所需費用如下表所示:
年數 | 16 | 17 | 18 | 19 | 20 | 21 | 22 | 23 |
第一位小孩 | 30萬 | 30萬 | 30萬 | 30萬 | 30萬 | 30萬 | ||
第二位小孩 | 30萬 | 30萬 | 30萬 | 30萬 | 30萬 | 30萬 | ||
總計 | 30萬 | 30萬 | 60萬 | 60萬 | 60萬 | 60萬 | 30萬 | 30萬 |
如果現在上大學一年需要總費用為30萬,那麼只需在16~23年度分別填入30萬、30萬、60萬、60萬、60萬、60萬、30萬、30萬,立即就可以知道現在得準備204萬8,939元,若預備以21年分期投入,每月得準備1萬3,317元,假若通貨膨漲率為2%,投資報酬率為5%。
點選下列Excel圖示,可以下載檔案
步驟一:做好下列表格。
步驟二:將每年需求金額填入每年費用(現值)的欄位。
步驟三:輸入通膨調整後公式,在儲存格C10填入公式【=B10*(1+$B$1)^A10】,注意B1必須寫成$B$1,複製時位置才不會跑掉。
步驟四:輸入「現值」欄位公式,在儲存格D10填入公式【=C10/(1+$B$2)^A10】。
步驟五:將公式複製至所有年度。
用滑鼠選到C10:D10,然後按CTRL-C複製,再貼上C11:D35。
步驟六:設定儲存格
B3公式【=SUM(D10:D35)】
B5公式【=PMT(B2,B4,-B3)】
B6公式【=B5/12】
B7公式【=B5*B4 】
這試算表不只是適用於小孩的教育基金,只要未來有現金需求的情況,都可以使用。例如籌措退休金,或者是買房頭期款也都可以用,範圍相當廣泛。例如預備10年後買房,預估目前自備款約300萬,那麼每月該投資多少才夠。只要在10年那個欄位填入300萬,立即可以知道結果。在投資報酬率5%的情況下,每月必須投入2萬4,229元才夠。
驗算表試算表的右方有個藍色的試算表格,用於驗算得到的數字是否正確。如果分期投入,依照所訂的投資報酬率,可以清楚看出每年投資收益、提領金額、結餘,這樣使用起來才會有信心。有了這些資料,才能做好財務規劃,只要按計畫進行,生活才會過得輕鬆愉快。