第六章 函數的應用.

Presentation on theme: "第六章 函數的應用."— Presentation transcript:

1 第六章 函數的應用

2 財務函數 實例應用 實例應用 1 實例應用 2 實例應用 實例應用 1 實例應用 2 實例應用 3 實例應用 4 PV 函數－計算現值
FV 函數－計算未來值 PMT 函數－計算每期的數值 實例應用 1 實例應用 2 RATE 函數－計算利率 NPER 函數－計算期數 實例應用 IRR 函數－計算內部報酬率 折舊函數 實例應用 1 實例應用 2 實例應用 3 實例應用 4

3 PV 函數－計算現值 PV 函數是用來求算現值的函數。透過此函數, 可以反推在某種獲利條件下, 所需要的本金,以便評估某項投資是否值得。PV 函數的格式為：

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

5 實例應用 假設郵局推出一種儲蓄理財方案：年利率為 2.5%, 只要您現在先繳 120,000 元, 就可在未來的 10 年內, 每年領回 13,500 元, 這時候我們就可以利用 PV 函數來評估此項方案是否值得投資。

6 實例應用 由上述說明可知 Rate 為 2.5%, Nper 為 10 期, Pmt 為 13,500。
帶入函數計算：PV (2.5%, 10, 13500) = -118, (由於是反推成本, 所以會出現負數),表示我們大約只須繳 118,153 元, 即可享有此投資報酬率, 並不需要繳到 120,000 元這麼多, 因此評估結果為不值得投資。

7 FV 函數－計算未來值 FV 函數是用來計算未來值的函數。透過它, 可評估參與某種投資時最後可獲得的淨值。FV函數的格式為：

8 FV 函數－計算未來值 Rate 為各期的利率。 Nper 為付款的總期數。 Pmt 為各期所應給付的固定金額。
Pv 為年金淨現值。此欄若不填則以 0 代替。 Type 為一邏輯值, 當為 1 時, 代表每期期初付款；當為 0 時, 代表每期期末付款。此欄若不填則以 0 代替。

9 實例應用 假設銀行年利率為 2%, 您從現在起, 每月固定存款 8,000 元, 那麼在 5 年後, 您一共存了多少錢呢？
由上述說明可知 Rate 為 2%/12 (2% 是年利率, 每月存款所以要除以 12), Nper為 5*12 ( 一年 12 期, 持續 5 年), Pmt 為-8000 (由於是付款, 故帶入負數)：

10 實例應用 帶入函數計算結果：FV (2%/12,5*12,-8000) = $504,378.85, 代表 5 年後您將會有這麼多的存款。

11 PMT 函數－計算每期的數值 PMT 函數可幫我們計算在固定期數、固定利率的情況下, 每期要償還的錢。對於想向銀行貸款的購屋或購車族來說, 是相當實用的一個函數。PMT 函數的格式如下：

12 PMT 函數－計算每期的數值 Rate 為各期的利率。 Nper 為付款的總期數。 Pv 為未來各期年金的總淨值, 即貸款總金額。
Fv 為最後一次付款以後, 所能獲得的現金餘額。此欄若不填則以 0 代替。 Type 為一邏輯值, 當為 1 時, 代表每期期初付款；當為 0 時, 代表每期期末付款。此欄若不填則以 0 代替。

13 實例應用 1 假設旗旗銀行提供申請購屋貸款的優惠方案, 貸款年利率為 7%, 可借得 3,000,000 元, 期限為 20 年, 這時候您就可以透過 PMT 函數, 算算每月必須負擔多少貸款？

14 實例應用 1 帶入函數求解：PMT (7%/12,20*12, ) = -$23,258.97, 現在知道如果申請此購屋貸款, 每個月必須負擔約二萬多元, 您可以依據這個結果加上自備款衡量自己的購屋能力。

15 實例應用 2 假設您想在 4 年後存滿 800,000 元做為留學基金, 現今的年利率為 2%, 則每個月應存多少錢才能達成這個目標呢？
由上圖得知：PMT (2%/12,4*12,0, ) = $16,022.77, 也就是說您只要每個月固定存入 $16,023 元, 4 年後就可以順利的出國留學了。

16 RATE 函數－計算利率 RATE 函數可以幫我們計算借了一筆錢, 在固定期數、每期要償還固定金額下, 算出其利率為何。RATE 函數的格式為：

17 RATE 函數－計算利率 Nper 為付款的總期數。 Pmt 為各期所應給付的固定金額。 Pv 為未來各期年金現值的總合。
Fv 為最後一次付款後, 所能獲得的現金餘額。此欄若不填則以 0 代替。 Type 為一邏輯值, 當值為 1 時, 代表每期期初付款；當值為 0 時, 代表每期期末付款。此欄若不填則以 0 代替。

18 實例應用 1 假設古堡銀行推出全新的百萬儲蓄計劃, 強調每月只要儲蓄 7,500 元, 10 年後保證領回 100萬元, 那到底這個百萬儲蓄計劃的年利率是多少呢？

19 實例應用 1 此時計算結果為 2%, 但是利率通常我們會精準到小數點之後的 2 或 3 位數, 為了確認小數點之後是否還有數字, 請切換至常用頁次, 再連按 3 下增加小數位數鈕, 就會看到結果為2.230 (建議您增加小數位至 3 位或小數是 0 為止)：

20 實例應用 1 帶入函數計算的結果：RATE (10,7500*12,, )=2.320%, 比起目前銀行定存約 2%的利率還要高一些, 倒是一個值得考慮的儲蓄計劃哦！

21 實例應用 2 假設古堡銀行提出個人小額信用貸款方案, 借款 30 萬, 每月只要還款 16000, 2 年即可還清。咦！怎麼沒有說明貸款利率。沒關係, 我們自行計算一下這個貸款利率到底是多少吧！ 帶入函數得知：RATE (2,16000*12, )=18.163%, 哇！和信用卡循環利息一樣高耶,還是划不來。

22 NPER 函數－計算期數 NPER 函數是指每期投入相同金額, 在固定利率的情形下, 計算欲達到某一投資金額的期數。

23 NPER 函數－計算期數 Rate 為各期的利率。 Pmt 為各期所應給付的固定金額。 Pv 為未來各期年金現值的總合。
Fv 為最後一次付款後, 所能獲得的現金餘額。此欄若不填則以 0 代替。 Type 為一邏輯值, 當值為 1 時, 代表每期期初付款；當為 0 時, 代表每期期末付款。此欄若不填則以 0 代替。

24 實例應用 小風想買一間需自備款 60 萬元的小套房, 目前小風每個月可以存 17,000 元, 而定存年利率為 2.05%, 小風需要存多久才能存夠小套房的頭期款呢？ 帶入函數計算結果：NPER(2.05%/12,17000,, ,1)= , 表示小風只要存 35個月就可湊足小套房的頭期款了。

25 IRR 函數－計算內部報酬率 IRR 函數可以用來計算某一連續期間的內部報酬率。其中要注意的是, 投入資金必須以負值表示, 才可以用 IRR 來計算。IRR 函數的格式為： Values 要計算報酬率的現金流量數值。 Guess 預測利率, 若不填則以10% 為預設值來計算。

26 實例應用 假設瑪琦朵行動咖啡館提出一投資方案, 投資者只要投入資金 80 萬, 便可以在 6 年內可分別領回：15 萬、17.5 萬、20 萬、21 萬、22 萬及 23 萬, 求此投資的內部報酬率： 由上圖可知其內部報酬率為 11.61%。

27 折舊函數 一般來說, 公司的資產, 像是車輛、生財設備、機器... 都會毀損或貶值。而在會計原則中, 可以將這些設備的耗損視為公司支出以達到減稅的目的。這些設備的毀損或貶值有一定的計算方法, 稱為折舊。 計算折舊的方法有很多種, 通常會依公司習慣的方式來提列。由於使用不同的折舊函數, 所需用到的參數亦有些許差異, 我們先介紹共通的部份：

28 折舊函數 Cost 採購設備或資產所花費的成本。 Salvage 殘值, 亦即此設備或資產過了耐用年限時可回收的價值。
Life 耐用年限, 亦即此設備或資產的可用年限或生產數量。

29 實例應用 1 永福公司採購一生財設備花了 60 萬元, 預估可以使用 5 年, 殘值餘 4,500 元。若以直線法來攤為費用, 則可使用直線法折舊函數 SLN, 其格式如下：

30 實例應用 1 請開啟範例檔案 Ch09-02, 將插入移至 B4 儲存格, 接著輸入公式 "=SLN($B$1,$D$1,$F$1)"：

31 實例應用 2 承上例, 假設永福公司想要以定率遞減法 (DB) 來計算每年需攤提的費用, 則須採用 DB 函數, 其格式為：

32 實例應用 2 在 B5 拉曳填滿控點至 F5, 即可求得定率遞減法各年度的折舊費用。這是初期折舊的費用較高, 然後逐年遞減的一種加速折舊法。

33 實例應用 3 承實例應用 1, 若永福公司想要以倍率遞減法 (DDB) 來計算每年需攤提的費用, 則可使用DDB 函數, 其格式如下：

34 實例應用 3 拉曳 B6 填滿控點至 F6, 即可求得各年度的折舊費用。

35 實例應用 4 承實例應用1, 若要改以年數合計法 (SYD) 來計算每年需攤提的費用, 則可用 SYD 函數來計算。其格式如下：

36 實例應用 4 然後拉曳 B7 填滿控點至 F7。若想要了解這 4 個應用實例的結果, 可開啟範例檔案Ch09-02 切換到折舊-OK 工作表來查看。

37 ROUND 函數－將數字四捨五入 ROUND 函數可您依指定的位數, 將數字四捨五入。其格式如下：

38 ROUND 函數－將數字四捨五入 當 Num_digits 大於 0 時, 數字會被四捨五入到指定的小數位數, 例如：ROUND (35.32,1) =35.3。 當 Num_digits 等於 0 時, 數字會被四捨五入到整數, 例如：ROUND (76.82,0) = 77。 當 Num_digits 小於 0 時, 數字將被四捨五入到小數點左邊的指定位數, 例如：ROUND(22.5,-1) = 20 。

39 邏輯函數 Excel 的邏輯類別函數可用來設計判斷式, 幫您判斷出某條件是否成立；或者也可以控制當符合某種條件時, 要執行哪些運算或操作。本節要為您介紹的邏輯函數有：IF 函數、AND 函數和 OR 函數。

40 邏輯函數 IF 函數－判斷條件 實例應用1 實例應用 2 AND 函數－條件全部成立 實例應用 OR 函數－條件之一成立

41 IF 函數－判斷條件 IF 函數用來判斷測試條件是否成立, 如果所傳回的值為 TRUE 時, 就執行條件成立時的作業,反之則執行條件不成立時的作業。IF 函數的格式為：

42 實例應用1 請開啟範例檔案 Ch09-04, 切換到 IF 工作表, 這是一張學生成績列表：

43 實例應用1 現在我們使用 IF 函數做判斷, 如果學生平均成績大於或等於 60 分, 則在最後的 "總評" 欄內填入 "Pass"；若平均低於 60 分, 就填入 "重修"。首先建立第一位學生的判斷式：

44 實例應用1 拉曳 H2 的填滿控點至 H11, 便可得到每位學生的總評結果囉！

45 AND 函數－條件全部成立 AND 函數的所有引數都必須是邏輯判斷式 (可得到 TRUE 或 FALSE 的結果) 或包含邏輯值的陣列、參照位址, 且當所有的引數都成立時才傳回 TRUE, 它的格式為：

46 實例應用 請將範例檔案 Ch09-04 切換到 AND 工作表, 這是某班級的學生成績列表：

47 實例應用 假設有一檢定考試, 必須要國文、英文這兩科的成績都高於 80 分才能報名參加, 這時候我們可以使用 AND 函數並搭配前面的 IF 函數來找出符合報考資格的學生：

48 實例應用 接著拉曳 G2 的填滿控點至 G11, 則到底哪些學生能參加檢定考試就一目了然了：

49 OR 函數－條件之一成立 OR 函數和 AND 函數一樣, 所有引數都必須是邏輯判斷式, 不同的是, 當引數中只要有一個成立就傳回 TRUE, 其格式為：

50 實例應用 請將範例檔案 Ch09-04 切換到 OR 工作表。假設有一檢定考試, 只要其中一科成績低於 60分就不予合格證明, 我們可以使用 OR 函數搭配 IF 函數來找出合格的學生：

51 實例應用 最後一樣拉曳 F2 的填滿控點至 F13, 就可以知道學生的合格情形。

52 日期及時間函數 如果是公司的人事部門, 可能需要計算員工的年資, Excel 函數中也提供了許多可以計算日期與時間的函數, 讓我們來看看怎麼使用吧！ TODAY 函數－傳回現在系統的日期 應用實例 DATEDIF 函數－計算日期間隔 DATEDIF 的差距單位參數

53 TODAY 函數－傳回現在系統的日期 TODAY 函數會傳回現在系統的日期, 可應用於輸入報告完成時間或是用來計算年資、年齡。

54 應用實例 請開啟範例檔案 Ch09-06, 並切換至 TODAY 工作表。美美公司想要在年終獎金的部份, 針對在公司服務滿 10 年的同仁發放年資獎金。我們用 TODAY 這個函數和到職日相減, 所減出來的數字表示天數, 再除上 (每 4 年閏 1 天) 即可算出年資：

55 應用實例

56 應用實例 拉曳 E3 的填滿控點複製公式後, 就可以看出符合年資獎金條件的員工有哪些了：

57 DATEDIF 函數－計算日期間隔 DATEDIF 函數可以幫我們計算兩個日期之間的年數、月數或天數。其格式如下：

58 應用實例 承續上例, 若美美公司想計算員工中從到職日至 95 年10 月31 日止的服務年資, 就可以這麼計算：

59 DATEDIF 的差距單位參數 在 DATEDIF 函數中,可依據您要求算的結果, 搭配使用各種差距單位參數, 列表如右供您參考：

60 DATEDIF 的差距單位參數 假設要計算某人的實際年齡滿幾年、幾月、幾天, 只要輸入如下的公式即可計算出來：