07 財務函數應用 投資理財試算 計算零存整付到期本利和－FV函數設定 目標搜尋 計算貸款每月應償還金額－PMT函數設定 運算列表 分析藍本

Presentation on theme: "07 財務函數應用 投資理財試算 計算零存整付到期本利和－FV函數設定 目標搜尋 計算貸款每月應償還金額－PMT函數設定 運算列表 分析藍本"— Presentation transcript:

1 07 財務函數應用 投資理財試算 計算零存整付到期本利和－FV函數設定 目標搜尋 計算貸款每月應償還金額－PMT函數設定 運算列表 分析藍本
試算保險利率－RATE函數設定 試算保險淨現值－NPV函數設定

2 計算零存整付到期本利和－FV函數設定 01 02 03 在「B2」儲存格中輸入每月欲存入的金額「10000」。

3 04 將滑鼠游標移至「B5」儲存格，點選「公式→函數程式庫」群組中的「插入函數」按鈕。或者也可以直接按下資料編輯列上的「 」工具鈕，開啟「插入函數」對話方塊。

4 05 06 在「選取類別」中選擇函數「財務」類別，再至「選取函數」中選擇「FV」，選擇好後按下「確定」按鈕。
在開啟的「函數引數」對話方塊中設定引數。Rate引數是用來設定每一期的利率，也就是「B4」儲存格。但要注意1.75%為「年」利率，而小華是按「月」繳交的，所以每期利率應將1.75%再除以12個月，才是實際每期的計算利率。

5 07 08 接著在Nper引數欄位中設定定存的總期數，在此輸入「B3」儲存格。
Pmt引數欄位是用來設定每期存款金額，在此輸入「B2」儲存格。在此要特別注意：由於每期按月繳付$10000，所以要在「B2」前加上「-」號，表示支付金額。

6 09 最後在Type引數欄位中輸入「1」，表示「期初給付」每期金額，按下「確定」鈕就完成設定囉！

7 FV函數 語法： FV(Rate,Nper,Pmt,Pv,Type) Rate：為各期的利率。 Nper：為年金的總付款期數。 Pmt：係指分期付款的金額；不得在年金期限內變更。 Pv：係指現在或未來付款的目前總額。 Type：為0或1的數值，用以界定各期金額的給付時點。1表示期初給付；0或省略未填，則表示期末給付。 要特別注意的是：在設定引數數值時，若欲代表所付出的金額（如每期存款），須以負值代表引數。

8 10 回到工作表中，就計算出三年後到期時，小華可以領回共計$369,880的本利和。
為了使範例的學習更流暢，在本書書附光碟中所提供的本章範例檔案，已事先將B2：B5欄位的儲存格格式，依照欄位需求，設定為「貨幣」或「百分比」等格式。如果想要自己動手建立類似的表格，可別忘了要另外修改儲存格的格式喔！

9 目標搜尋　　　　　　　　　　　 01 選取要達成目標的儲存格，也就是本利和必須為$1,000,000的「B5」儲存格，然後點選「資料→資料工具」群組中的「模擬分析」下拉鈕，在選單中選擇「目標搜尋」。

10 02 03 04 在「目標搜尋」對話方塊中，「目標儲存格」即為「到期本利和」，也就是「B5」儲存格。
而「目標值」欄位，則設定三年後要存得的金額「 」。 「變數儲存格」欄位為推算每月存入金額的欄位，也就是「B2」儲存格。設定完成後，按下「確定」鈕。

11 05 工作表中會出現「目標搜尋狀態」的視窗，顯示已完成計算。而工作表中的各儲存格也依照所設定的目標自動改變，變數儲存格「B2」則自動搜尋相對應的數值，計算出小華每月必須存入「$27,036」，才能在三年後存得一百萬元。按下「確定」按鈕即完成目標搜尋結果。

12 如果這時在「目標搜尋狀態」視窗中按下「確定」按鈕，目標儲存格及變數儲存格會自動替換成搜尋後的數值；若按下「取消」鈕，則工作表會回復原來的模樣，所有的數值都不會改變。

13 計算貸款每月應償還金額－PMT函數設定 　　　　　　　　　　　　　　　　　　　　01 首先計算「政府首購貸款」的每月應償還金額。滑鼠游標點選工作表中的「E2」儲存格，點選「公式→函數程式庫」群組中的「插入函數」按鈕，開啟「插入函數」對話方塊。

14 02 在「插入函數」對話方塊中的「財務」類別選單中，選擇「PMT」函數，按下「確定」按鈕。

15 在開啟的「函數引數」對話方塊中，Rate引數須輸入「各期利率」。所以在此輸入「C2」儲存格。但要注意因為1.72為「年」利率，而貸款是按「月」償還的，所以必須要將1.72再除以12個月，才是實際每期的計算利率。 Nper引數用來設定「貸款總償還期數」，因此輸入「D2」儲存格。同樣由於按月償還的關係，所以償還年限要再乘以12，才是償還總期數。 Pv引數是用來設定「貸款金額」，因此輸入「B2」儲存格。由於償還貸款為支付金額，所以還要在B2前加上「-」號。

16 06 最後在Type引數欄位中輸入「1」，代表為「期初償還」，然後按下「確定」鈕完成函數設定。

17 PMT函數 語法： PMT(Rate,Nper,Pv,Fv,Type) PMT函數主要用來計算貸款的攤還金額。其引數說明如下： Rate：為各期的利率。 Nper：為年金的總付款期數。 Pv：係指未來每期年金現值的總和。 Fv：為最後一次付款完成後，所能獲得的現金餘額。若省略不填，則預設值為0。 Type：為0或1的數值，用以界定各期金額的給付時點。若為0或省略未填，表示為期末給付；若為1，則表示為期初給付。 在設定引數數值時，若欲代表所付出的金額（如每期存款），須以負值代表引數。

18 07 再回到工作表中，已經計算出「政府首購貸款」的每月償還金額為$9,840。接著拖曳「E2」儲存格的填滿控點至「E3」儲存格，將計算公式複製至「E3」儲存格，就可以計算出「大方銀行房貸」的每月償還金額。

19 08 接著點選「E4」儲存格，按下「公式→函數程式庫」群組中的「自動加總」按鈕，框選出「E2：E3」儲存格後，直接按下 Enter 鍵，就可以算出每個月應繳交的房貸金額共為「$21,899」。

20 運算列表　　　　　　　　　 首先建立「每月償還金額」的運算公式。滑鼠游標點選工作表中的「B7 」儲存格， 點選「公式→ 函數程式庫」群組中的「插入函數」按鈕，開啟「插入函數」對話方塊。 請利用「PMT」函數，在「B7」儲存格中建立計算每月償還金額的運算公式。函數引數的設定方式如下：

21 03 按下「確定」鈕後，「B7」儲存格會以上方表格的大方銀行房貸條件：貸款金額「$2,000,000」、利率「3.98%」、償還年限「20年」計算並顯示每月償還金額為$12,059，而接下來列表中的儲存格也都會以「B7」儲存格的公式為基礎，修改不同的貸款條件後進行運算。

22 04 接著選取列表範圍「B7：G12」，再點選「資料→資料工具」群組中的「模擬分析」下拉鈕，在選單中選擇「運算列表」。

23 在建立運算列表時，必須將公式儲存格，在本例中也就是計算每月償還金額的B7儲存格，建立在列表最左上角的儲存格中。而當選取列表範圍時，只要將含有運算條件的儲存格以及運算區選取起來即可，可別框選標題列喔！

24 05 由於空白的列表中，列欄位所顯示的是「償還年限」，故在「運算列表」對話方塊中，設定列變數儲存格為「D3」儲存格；而欄欄位所顯示的是不同的「貸款金額」，故設定欄變數儲存格為「B3」儲存格。 由於運算列表所對照的「D3」及「B3」儲存格位置是固定的，所以須設定為絕對位置。此處若使用「 」按鈕進行儲存格選取設定，便會自動加上「$」符號。

25 06 最後按下「確定」鈕，就可以在列表中看到不同的貸款金額以及不同償還年限之下，所對照出來的每月償還金額囉！

26 分析藍本　　　　　　　　　　　 01 建立分析藍本 在應用「分析藍本」功能時，須先將游標移至比較的目標儲存格上，由於比較原則為每月償還總金額約為$25,000，所以目標儲存格就是「每月償還總金額」，也就是「E4」儲存格，接著點選「資料→資料工具」群組中的「模擬分析」下拉鈕，在選單中選擇「分析藍本管理員」。

27 02 在開啟的「分析藍本管理員」對話方塊中，按下「新增」按鈕。

28 03 在「編輯分析藍本」對話方塊中，於「分析藍本名稱」中輸入第一個貸款方案「200萬貸款20年」，接著按下「變數儲存格」欄位的「 」鈕，選取可變動的數值：「B3」和「D3」儲存格。選擇好了之後，按下「 」回到「編輯分析藍本」對話方塊中，再按下「確定」鈕。 在使用「 」按鈕進行儲存格選取設定時，可按下鍵盤上的Ctrl鈕，來選取不相鄰的兩個儲存格。

29 04 建立分析藍本後，就可以在「分析藍本變數值」對話方塊中，輸入該方案的變數值。在代表貸款金額的B3變數儲存格中，輸入「 」；在代表償還年限的「D3 」變數儲存格中， 輸入「2 0 」。按下「確定」按鈕。 也可直接點選「新增」按鈕，繼續進行下一個分析藍本的設定。

30 05 回到「分析藍本管理員」中，就可以看到剛剛新增的分析藍本「200萬貸款20年」，再按下「新增」按鈕，繼續增加下一個分析藍本。

31 06 輸入第2個分析藍本名稱「200萬貸款25年」，這裡的變數儲存格會保留第一次設定的值「B3」及「D3」，所以我們不須重新設定，只須按下「確定」按鈕即可。

32 07 接著設定第2個分析藍本的變數值，分別輸入「 」和「25」，輸入後按下「確定」鈕。

33 08 09 在「分析藍本管理員」對話方塊中，按「新增」鈕繼續增加第三個分析藍本。
輸入第3個分析藍本名稱「250萬貸款25年」，同樣保留這裡的變數儲存格為「B3」以及「D3」，按下「確定」按鈕。

34 10 11 設定第3個分析藍本的變數值，分別輸入「2500000」和「25」，輸入後按下「確定」鈕。
在「分析藍本管理員」對話方塊中，再按下「新增」鈕，建立第四個分析藍本。

35 12 13 輸入最後一個分析藍本名稱「250萬貸款30年」，同樣保留這裡的變數儲存格為「B3」以及「D3」，再按下「確定」按鈕。
設定第4個分析藍本的變數值，分別輸入「 」和「30 」，輸入後按下「確定」鈕，完成所有分析藍本的設定。

36 14 在「分析藍本管理員」對話方塊中，可以看到我們剛剛新增的四個分析藍本。

37 15 若想要看到以第三個分析藍本「250萬貸款25年」所計算出來的每月償還金額，則在分析藍本選單中點選「250萬貸款25年」，再按下「顯示」按鈕，就可以在工作表上看到以這個條件來計算的每月償還金額了。

38 01 02 以分析藍本摘要建立報表 點選「資料→資料工具」群組中的「模擬分析」下拉鈕，在選單中選擇「分析藍本管理員」。
在開啟的「分析藍本管理員」對話方塊中，按下「摘要」按鈕。

39 03 04 在「報表類型」選項中，點選「分析藍本摘要」選項。
「目標儲存格」就是當分析藍本設定的變數儲存格改變時，會受到影響而跟著改變的儲存格，通常Excel會自動尋找。現在它所搜尋到的儲存格位置為「E4」，正好是小華所要考慮的「貸款總計金額」，所以直接按下「確定」鈕就可以了。

40 05 再回到工作表中，已自動建立一個「分析藍本摘要」的工作表標籤頁，工作表內容也就是所有分析藍本的摘要資料喔！
在產生「分析藍本摘要」後，其左側會有一些「 」或「 」的大綱符號，用來隱藏或顯示摘要中的內容。可利用這些按鈕，來決定摘要中所要顯示的資訊。

41 試算保險利率－RATE函數設定 先點選「保險比較」工作表標籤頁，我們先計算「中華人壽」保單的保單利率。滑鼠游標點選工作表中的「B5」儲存格，按下「公式→函數程式庫」群組中的「插入函數」按鈕。

42 02 在「插入函數」對話方塊中的「財務」類別選單中，選擇「RATE」函數，按下「確定」按鈕。

43 03 04 05 在「函數引數」對話方塊中，Nper引數是用來設定應繳總期數，也就是期間年限「B3」儲存格。
而Pmt引數則是設定定存總期數，也就是「B2」儲存格。 Fv引數是設定到期所能領回的金額，也就是「B4」儲存格。但在這裡要注意：當設定「Fv」引數欄位時，到期領回的金額對於小華而言，為「收回」支付的金額，所以必須在B4前再加上「-」號。

44 06 06 最後在Type引數欄位中輸入「1」，表示為期初給付。設定完成後，按「確定」按鈕。

45 RATE函數 語法： RATE(Nper,Pmt,Pv,Fv,Type,Guess) RATE函數用來計算固定年金每期的利率。其引數說明如下： Nper：為年金的總付款期數。 Pmt：為各期所應給付（或所能取得）的固定金額。 Pv：為未來各期年金現值的總和。 Fv：為最後一次付款完成後，所能獲得的現金餘額。若省略不填，則預設值為0。 Type：為0或1的數值，用以界定各期金額的給付時點。若為0或省略未填，表示為「期末給付」；若為1，則表示為「期初給付」。 Guess：為期利率的猜測數。若省略不填，則預設為10%。

46 07 再回到工作表中，就計算出「中華人壽」的這份保單，其保單利率為1.82%。最後拖曳「B5」儲存格的填滿控點至「D5」儲存格，就可以計算出每張保單的保單利率囉。

47 08 保單的利率越高， 表示該保單對於投保者越有利。在這三張保單中，「中華人壽」的保單利率1.82%比起「美麗人壽」及「青山人壽」的保單利率1.44%、1.38%都來得高，表示「中華人壽」的保費方案是較有利的。而且以目前的三年定存利率1.75％來估算，在這三張保單中，也只有「中華人壽」的保單是優於目前定存利率的。小華便可藉此得知，就利率計算而言，「中華人壽」的保單內容是較值得投保的。

48 試算保險淨現值－NPV函數設定 01 點選「保險現值」工作表，先計算「保單現值」，滑鼠游標點選工作表中的「B15」儲存格，按下「公式→函數程式庫」群組中的「插入函數」按鈕。

49 02 03 在「插入函數」對話方塊中的「財務」類別選單中，選擇「NPV」函數，按下「確定」按鈕。
在「函數引數」對話方塊中，Rate引數是用來設定年度折扣率，也就是「B2」儲存格。

50 04 Value1引數則是用來設定未來各期的現金流量，在此按下引數欄位旁的「 」，在工作表中框選「B5：B14」儲存格，再按下「 」回到「函數引數」對話方塊中。最後按下「確定」鈕完成設定。

51 NPV函數 語法： NPV(Rate,Value1,Value2,...) NPV函數是使用折扣率及未來各期支出（負值）和收入（正值）來計算某項投資的淨現值，其引數說明如下： Rate： 用以將未來各期現金流量折算成現值的利率。 Value1, Value2：為未來各期現金流量。每一期的時間必須相同，且發生於每一期期末。

52 NPV與PV函數很類似，它們之間的主要差別有二點：

53 05 06 回到工作表中就計算出保單現值為「$157,314」。接著計算「保單淨現值」，在「B16」儲存格中建立公式「=B15-B3」。
計算出的「保單淨現值」為「-$11,566」，表示在加入物價指數的計算之後，利率保單利率已經完全被過高的通貨膨脹率抵消了，所以這份保單對目前來說並不值得投資。

54