第 16 章 投資理財試算 著作權所有 © 旗標出版股份有限公司.

Presentation on theme: "第 16 章 投資理財試算 著作權所有 © 旗標出版股份有限公司."— Presentation transcript:

1 第 16 章 投資理財試算 著作權所有 © 旗標出版股份有限公司

2 應用實例說明 您還在每個月按著計算機, 計算自己的銀行存款嗎？還是對於許多有興趣的投資方案卻遲遲不敢跟進呢？本章將會介紹幾個生活中常見的理財、投資範例, 包括計算儲蓄投資計劃的成本、零存整付的存款總和,還要教您如何挑選貸款銀行, 甚至連最熱門的共同基金也不放過。現在就讓我們用 Excel 將這些數字算清楚吧！

3 應用實例說明

4 本章提要 計算投資成本 計算零存整付的存款總和 計算存款本利合 找出最合適的貸款方案 定期定額基金理財試算表 投資方案評估

5 計算投資成本 市面上有不少業者推出誘人的儲蓄投資計劃, 標榜著只要投資一筆錢, 即可在日後特定的期限內, 持續領回投資的金額。雖然這樣的方案很吸引人, 但是到底值不值得我們投資？或是只要用更少的金額, 就可以享有同樣的報酬呢？在投資前您可得睜大雙眼看仔細了, 本節就以『子女教育基金投資方案』為例, 教您如何計算投資成本, 評估該方案是否值得投資。

6 計算投資成本 旗旗銀行近日推出一項名為『向日葵教育基金』的投資計劃, 只要花 30 萬參加此方案, 即可在未來 10 年內, 每年領回 $32,500 元的子女教育基金 (年利率為 2%)。參加此方案後, 未來 10 年您就不必擔心經濟不景氣、失業時, 無法負擔子女的學費了。

7 計算投資成本 建立資料 PV 函數 計算投資現值

8 建立資料 乍看之下, 這個投資計劃蠻吸引人, 但還是得精打細算一番, 才能做出正確的決定。首先, 我們要建立此方案的相關資料, 請開啟範例檔案 Ch16-01, 如下圖填入上述中的各項數字：

9 PV 函數 此範例可利用 PV 函數來計算。PV 函數是用來計算一段期間內,連續支出固定金額的現值。 PV 函數的格式為：

10 PV 函數 Rate：各期的利率。 Nper：付款的總期數。 Pmt：各期給付的固定金額。
Fv：最後一次付款後, 所能獲得的現金餘額。若不填則以 0 代替。 Type：為一邏輯值, 判斷付款日為期初或期末。當 Type 為 1 時, 代表每期期初付款；為 0 時, 代表每期期末付款。若不填則以 0 代替。

11 計算投資現值 瞭解 PV 函數後, 我們就可以在 B7 儲存格算出答案了。請接著進以下的練習。

12 計算投資現值

13 計算投資現值 按下確定鈕後,在函數引數交談窗中,分別填入各個引數相對應的儲存格：

14 計算投資現值 按下確定鈕, 即可在 B7 得到計算結果：

15 計算投資現值 結果計算出來了, 此投資方案的現值只有 291,934 表示我們只要付出 291,934 元, 即可享有這樣的投資報酬率, 並不如預期的那麼好, 但您可以評估個人的花費習慣來決定是否要投資。

16 計算零存整付的存款總和 零存整付的存款方式, 是指在特定的期間內, 持續存入固定的金額,待約定的期限到了, 再將本金及利息一併付給存款人。由於銀行推出的零存整付方案, 通常會有較一般存款優惠的利率, 所以許多人會選擇這種存款方式；也有些人希望藉由這種方式, 來強迫自己存款。這一節我們就要告訴您如何計算零存整付的存款總和。

17 計算零存整付的存款總和 小漫終於從學校畢業, 成為社會新鮮人了。最近她找到一份還算滿意的工作, 每個月也擁有固定的收入, 於是對人生開始了新的規劃。小漫決定選擇旗旗銀行推出的零存整付專案, 每月將薪水的三分之一 (10,000 元) 存入銀行, 為期 2 年, 並享有 1.5% 的年利率。現在就來幫小漫算算 2 年後會有多少存款吧！

18 計算零存整付的存款總和 建立資料 FV函數 計算存款總和

19 建立資料 請開啟範例檔案 Ch16-02, 並將資料填入儲存格中：

20 FV函數 利用 FV 函數, 即可算出未來存款的總和。 FV 是一個用來計算未來值的函數, 我們可藉由它來評估某項投資最後可獲得的淨值。

21 FV函數 Rate：各期的利率。 Nper：付款的總期數。 Pmt：各期給付的固定金額。 Pv：年金淨現值, 若不填則以 0 代替。
Type：為一邏輯值, 判斷付款日為期初或期末。當 Type 為 1 時, 代表每期期初付款；為 0 時, 代表每期期末付款。若不填則以 0 代替。

22 計算存款總和 接著, 我們要在 B6 儲存格計算出存款總和： 請選定 B6 儲存格, 並按下資料編輯列的 鈕 ：

23 計算存款總和 按下確定鈕後, 輸入各個引數對應的儲存格：

24 計算存款總和 在計算時, 利率和期數必須是相同的單位。以上例來說, 由於是每月存款一次, 所以必須將年利率除以 12, 換算成月利率來計算。

25 計算存款總和 按下確定鈕, 即可在 B6 得到存款總和：

26 計算存款本利合 如果想將一筆錢存入銀行, 但又想知道在不同銀行、利率所得的存款總和, 可以利用單變數運算列表及雙變數運算列表來算出不同利率, 甚至不同金額下的存款總和。

27 計算存款本利合 單變數運算列表 建立 FV 公式 建立運算列表 雙變數運算列表

28 單變數運算列表 單變數運算列表是指公式中只有一個變數值, 只要將此變數輸入, 即可列出該數值變化後所有的計算結果。例如小漫想要固定每個月將 10,000 元存入銀行, 共存 2 年, 在選擇銀行時, 我們發現每家銀行所提供的利率不同, 但小漫的存款金額是相同的, 於是此例中的唯一變數就是各家銀行的利率。

29 單變數運算列表 我們只要將利率填入工作表中, 再經過一番計算, 即可求得存款金額在不同利率下的存款總和, 也就是本利和！

30 單變數運算列表 請開啟範例檔案 Ch16-03, 並如下圖建立資料：

31 建立 FV 公式 在建立運算列表之前, 我們必需先建立公式, 讓 Excel 知道這些數字該如何運算。
請選定 C6 儲存格, 然後利用 FV 函數, 運算出在年利率 1.8% 條件下的存款總和：

32 建立 FV 公式

33 建立運算列表 接著, 我們就可以建立運算列表了, 請接續上例進行以下的操作：

34 建立運算列表

35 建立運算列表

36 建立運算列表 單變數運算列表最左上角的儲存格並無任何作用, 所以只要保持空白即可；另外, 在設定欄變數時, 設定公式的儲存格必須位於變數值欄的右邊, 且高於第一個變數儲存格一列；設定列變數儲存格時, 設定公式的儲存格則必須位於變數值列的下一列, 且位於第一個列變數的左欄。

37 無法變更部份資料表 建立運算列表後, 若想變更列表範圍 C7：C13 的資料內容, 會出現如下的提示訊息：

38 雙變數運算列表 單變數運算列表可計算有一個變數的公式；雙變數運算列表當然就是可以計算有兩個變數的公式囉！以上例來說, 如果小漫想知道在這幾家銀行, 每月分別存入 5, 000 元、8,000 元、10,000 元, 以及 12,000 元的情況下, 兩年後分別可獲得多少存款總和？那麼各個銀行的利率是第 1 個變數；每月存款的金額則是第 2 個變數。

39 雙變數運算列表 請開啟範例檔案 Ch16-04, 並如下圖輸入所需的資料：

40 建立運算列表 建立雙變數運算列表時, 同樣請先選定列表的範圍 B6：F13：

41 建立運算列表

42 建立運算列表

43 建立運算列表 建立雙變數運算列表時, 必須將公式儲存格建立在最左上角的儲存格中, 也就是上圖中 B6 的儲存格位置。
雖然我們知道將存款存入利率愈高的銀行, 可以賺得的利息相對的愈多, 但是在選擇銀行時, 有些因素也會列入我們考慮的範圍, 像是交通是否方便、手續是否簡單、自動櫃員機的據點多不多…等等。因此, 我們可以參考運算列表在各個利率下的存款總和, 再仔細評選銀行, 找出最適合自己的銀行。

44 找出最合適的貸款方案 這一節我們要教您利用分析藍本找出最合適的貸款方案。各家銀行推出的貸款方案都不盡相同, 如何從其中找出最適合自己的方案, 也是您在貸款前必須先學會的重要課題。 Excel 的分析藍本可以讓我們分別輸入各個方案的數值, 由 Excel 加以計算後列出結果, 方便我們做分析比較。假設小漫想要向銀行貸款,分別有 3 家銀行推出不同的方案：

45 找出最合適的貸款方案 旗旗銀行：可貸款額度 2,000,000, 年利率 4.6%, 需在 20 年內付清。
第三銀行：可貸款額度 1,500,000, 年利率 5.0%, 償還年限是 15 年。 富幫銀行：可貸款額度 1,800,000, 年利率 4.2%, 需在 15 年內還清。

46 找出最合適的貸款方案 在以上三家銀行中, 利率最低的是富幫銀行, 不過, 償還的年限較短；旗旗銀行可貸款的額度最高, 償還年限也較長。到底要選擇哪一家銀行才好呢？現在我們就利用分析藍本為小漫找出最合適的方案吧！

47 找出最合適的貸款方案 定義儲存格名稱 建立計算公式 PMT 函數 建立公式 建立分析藍本 定義儲存格名稱的好處 製作分析藍本摘要報告

48 定義儲存格名稱 在建立公式及分析藍本之前, 我們要先教您定義儲存格名稱的技巧, 讓日後建立分析藍本的工作更方便, 請開啟範例檔案 Ch16-05：

49 定義儲存格名稱

50 定義儲存格名稱 請仿上述步驟, 分別將儲存格 B4、B5 定義為付款期數、年利率。
至於定義儲存格名稱對建立分析藍本的影響, 我們將在稍後做說明。

51 PMT 函數 在選擇貸款方案時, 我們可用每個月需償還的金額, 做為衡量的基準。底下我們使用 PMT 函數來求得在固定期數、利率的情況下, 每期要償還的貸款金額。 PMT 函數的格式為：

52 PMT 函數 Rate：各期的利率。 Nper：付款的總期數。 Pv：未來各期年金的總淨值, 即貸款總金額。
Fv：最後一次付款後, 所能獲得的現金餘額。若不填則以 0 代替。

53 PMT 函數 Type：為一邏輯值, 判斷付款日為期初或期末。當 Type 為 1 時, 代表每期期初付款；為 0 時, 代表每期期末付款。若不填則以 0 代替。

54 建立公式 請接續上例, 或切換到 Ch16-05 的 Sheet2 工作表, 然後選定 B7 儲存格, 再輸入如下的公式：

55 建立分析藍本 接著, 我們就可以分別建立三家銀行的分析藍本了： 請執行『工具/分析藍本』命令, 此時會開啟分析藍本 管理員交談窗 ：

56 建立分析藍本 按下分析藍本管理員交談窗的新增鈕：

57 建立分析藍本 按下確定鈕, 再依序填入旗旗銀行的貸款金額、期數及利率：

58 定義儲存格名稱的好處 當我們在輸入各家銀行的貸款金額、期數及利率時, 由於之前將變數儲存格設定在 B3：B5, 且這個範圍我們已定義好名稱了, 所以在輸入分析藍本變數值時, 會直接顯示定義好的名稱, 方便我們輸入相關資料。但是如果沒有定義儲存格的名稱, 則建立分析藍本變數值時會顯示成：

59 定義儲存格名稱的好處 所以此範例中定義儲存格名稱最大的好處, 就是在建立分析藍本時, 能清楚明白的知道每個變數的涵意, 方便我們輸入正確的數值。

60 建立分析藍本 輸入完成後, 按下新增鈕, 繼續建立第三銀行的相關資料：

61 建立分析藍本 按下確定鈕後, 同樣輸入第三銀行的相關資料, 並請繼續建立富幫銀行的分析藍本, 待輸入完富幫銀行的各項相關資料後, 按下確定鈕：

62 建立分析藍本

63 建立分析藍本 建立好分析藍本後, 只要按下你想查看的銀行名字, 再按下顯示鈕,計算結果就出來了：

64 建立分析藍本 此時分析藍本管理員交談窗並不會關閉, 您可能需要移動分析藍本管理員交談窗才能看到顯示的結果。
檢視完後可以再選擇任何一家銀行來分析比較：

65 建立分析藍本

66 製作分析藍本摘要報告 顯示分析藍本固然方便,但每次只能顯示一份資料, 要記住每一家銀行的計算結果,實在有點累人。因此 Excel 提供一個可以將所有分析藍本彙整成一份摘要報告的功能哦！ 請開啟範例檔案 Ch16-06, 其中我們已經將三家銀行的分析藍本建立好了, 請執行『工具/分析藍本』命令：

67 製作分析藍本摘要報告

68 製作分析藍本摘要報告

69 製作分析藍本摘要報告

70 製作分析藍本摘要報告 只要從中考量自己每個月的償還能力, 以及需要貸款的金額, 就可以找出最適合自己的貸款方案囉！

71 定期定額基金理財試算表 建立工作表 建立運算公式 建立日期 建立其它計算公式

72 定期定額基金理財試算表 有了穩定收入和存款之後, 小漫開始想要做一些投資了, 但是放眼望去, 靠銀行存款賺取利息速度太慢、跟會風險大、對股票又一知半解,於是她決定選擇風險較低、報酬率還不錯的共同基金。

73 定期定額基金理財試算表 共同基金是基金公司集合眾多投資人的金錢, 經由投資專家代為操作,為投資人賺取利潤。投資共同基金時, 只要付給基金公司手續費等管理費用, 一旦投資的項目 (例如股票、債券等) 賺取到利潤, 基金公司就會將利潤分配給投資人。

74 定期定額基金理財試算表 不過投資都是有風險的, 有賺取利潤的機會,當然也會有血本無歸的可能, 只不過共同基金是由非常有經驗的顧問、專家來替投資人操作, 風險自然會比毫無經驗又胡亂投資的方式來得小！

75 建立工作表 小漫選擇的是很受大眾歡迎的定期定額方案, 固定每月的 16 日投資 5,000 元來購買基金。雖然每個月只要花 5,000 元, 就能有理財專家替你投資、賺錢, 但是對於自己的投資可不能不聞不問哦, 應該要有基本的認識才行。 請開啟一份空白的活頁簿, 建立如下圖的工作表欄位 (您也可以開啟範例檔案 Ch16-07) , 我們先來說明這個工作表各個欄位所代表的意義：

76 建立工作表 日期：指購買基金的日期。若是定期定額的投資方案, 則會選擇每月固定的一天做為扣款日期。

77 建立工作表 投入金額：即購買基金的金額, 可選擇單筆購買或定期定額的投資方式, 此範例選擇後者。
基金淨值：基金公司當日公佈的淨值。若賣出的淨值高於買進時的淨值, 則可獲利；反之若是賣出時的淨值比買入時低, 就是虧損。

78 建立工作表 購買單位：該次購買的基金單位數。 累積單位：目前累積的基金單位數。 獲利：藉由基金淨值變化所獲得的利潤或損失。
成本：至目前為止, 已投資的金額。 獲利率：根據獲利 (或損失) 及成本推算該投資的獲利率。

79 建立運算公式 接著, 我們要建立欄位的計算公式。請利用範例檔案 Ch16-07, 來進行以下的練習： 建立日期 建立其它計算公式

80 建立日期

81 建立日期

82 建立其它計算公式 投入金額：請在 B2 輸入每月投資的金額, 以小漫為例,每次固定投入 5,000 元, 所以請輸入 5,000, 並填滿至最後一次投資日 － 2004/12/16。

83 建立其它計算公式

84 建立其它計算公式 基金淨值：此欄的資料可至銀行或基金公司的網站查詢。只要連上網站, 再輸入要查詢的基金名稱, 通常都可查詢到該基金公告的淨值。此處請如右圖輸入我們假設的基金淨值：

85 建立其它計算公式

86 建立其它計算公式 購買單位：購買的單位數是由投入金額除以基金淨值得來, 所以請在 D2 儲存格輸入公式 "=B2/C2", 並將公式複製到 D3：D13 的儲存格範圍。

87 建立其它計算公式

88 建立其它計算公式 累積單位：只要購買的基金單位沒有賣出, 則將每個月購買的單位加總起來, 就是目前的累積單位。 請如下輸入公式：

89 建立其它計算公式

90 建立其它計算公式

91 建立其它計算公式 到目前為止, 我們已經建立了一部份欄位的公式了, 由於步驟較繁複, 如果您沒有跟著實際建立公式, 也可以切換至 Sheet2 工作表, 繼續底下的操作練習。 計算完基本的日期、單位後, 接下來我們就要來算算基金到底能為我們賺得多少利潤了：

92 建立其它計算公式 成本：在計算利潤之前, 我們要先計算出投資的成本。此處和累積單位的公式相同, 請在 G2 儲存格輸入公式 "=B2"：

93 建立其它計算公式

94 建立其它計算公式 獲利：將累積單位乘以基金淨值, 可計算出在該淨值下賣出所有單位後, 可獲得的金額。至於實際的獲利或是損失, 則要再減去投入的成本, 如果得到的是正數即是獲利；得到負數則是損失。因此我們可在 F3 輸入如下的公式, 並複製到 F4：F13 的儲存格範圍：

95 建立其它計算公式

96 建立其它計算公式 獲利率：最後我們要計算的是獲利率, 將獲利 (或損失) 除以成本, 即可得到獲利率, 請在儲存格 H3 輸入公式 "= F3/G3" , 並複製到 H4：H13 的儲存格範圍：

97 建立其它計算公式

98 建立其它計算公式 日後, 只要依續輸入查詢到的基金淨值, 就可以算出想要得知的資訊了。另外, 由於大部份的人會把基金當作一種中長期的投資, 不會在短期內賣出。若持續進行投資, 可在工作表下方繼續新增日期、淨值等資訊, 並將公式複製到對應的儲存格, 計算出想要的資訊；若持有基金,但沒有繼續投資的話, 只要在投入金額的 B 欄輸入 0, 一樣可以算出目前想知道的相關資訊。

99 建立其它計算公式 這個工作表只是教您簡單的基金計算方式, 並不考慮手續費、匯率(購買海外基金時) …等較複雜的問題, 所以實際的計算方式, 還是要依據您目前的情況來著手才行。 建議您在投資、選擇基金時, 對基金要有多一點的認識, 千萬不要盲目或跟隨潮流來進行投資, 才是正確的投資理財觀念哦！

100 投資方案評估 如果投資方案並不是在固定的日期內進行投資, 付出的投資也沒有固定的金額, 實在很難一眼看出該投資方案到底是不是值得繼續投資。還好 Excel 可以根據以往投資的金額、得到的報酬, 以及日期來算出該投資案的現值, 作為是否投資的參考。

101 投資方案評估 建立資料 XNPV 函數 後記

102 建立資料 小漫的姐姐在 2003 年 12 月開了一間花店, 成了名副其實的老闆娘, 在這期間也陸續不定期的投入資本, 當然也從中賺得不少的利潤。現在她想邀小漫加入他的花店成為股東, 於是小漫向姐姐要來從開店以來所投入資本及獲利的記錄, 想從中算出這個投資案的現值, 做為是否投資的參考。

103 建立資料 請開啟範例檔案 Ch16-08, 其中我們已經將花店的投資、報酬記錄輸入到工作表中了：

104 XNPV 函數 此範例可利用 XNPV 函數來計算。

105 XNPV 函數 Rate：現金流動折價率。 Values：投入的資金或得到的報酬。 Dates：投入資金或得到報酬的日期。

106 安裝『分析工具箱』功能 在使用 XNPV 函數前, 必須先確定 Excel 增益集功能中的分析工具箱是否已開啟, 否則輸入函數後會出現如下的錯誤提示：

107 安裝『分析工具箱』功能 此時, 請放入 Office 的安裝光碟, 然後執行『工具/增益集』命令：

108 安裝『分析工具箱』功能 若確定已安裝過此項增益集功能, 則日後只須確定該項目有被勾選 (即為開啟狀態) 即可。

109 安裝『分析工具箱』功能 當您開啟了分析工具箱功能後, 儲存格中的錯誤顯示仍會存在,必須按下 鍵讓其重新計算一次, 或是再重新輸入一次函數才會顯示出正確的函數計算結果。

110 XNPV 函數 我們假設現金流動的折價率為 1.5%, 所以請在範例檔案 Ch16-08 的 B13 輸入 "1.5%", 然後選定 B14 儲存格, 再如下圖建立計算公式：

111 XNPV 函數

112 XNPV 函數 雖然計算出來的現值是負數, 但是如果您覺得花店將來很有發展的空間, 還是可以放心大膽的投資, 而且花店才剛開幕不滿一年, 能有這樣的成績實在不容易, 或是姐姐實在太需要你的支持, 花一筆小錢當個股東也不錯啊！如果你不看好這家花店, 即使計算出來的現值是正數, 也要認真考慮是否值得投資。無論如何, 現值只是給您一個投資的參考數值, 至於投資與否, 還是得靠您仔細評估才行。