FV.

Presentation on theme: "FV."— Presentation transcript:

1 FV

2 Excel 函數-FV未來值 讀完本篇後，不了解Excel裡的FV函數應用也難。未來值或終值是屬於貨幣的時間價值之一環，Excel也提供了一個相對函數FV (Future Value)來相呼應。相信許多人或多或少都知道什麼是未來值，但是當使用Excel FV函數時，卻常常碰到一些問題，尤其是正負號部分常常讓使用者頭疼。本篇主要介紹該函數的意義及其應用，除了理論介紹以外，還附有範例供讀者參考。

3 何謂未來值 未來值的英文為Future Value (FV)，另一個名稱為終值，就是當一筆金額經過一段時間的複利成長後，於未來具有的貨幣價值。簡單說現在的100元和未來的100元是不同價值的，這100元於未來的價值就稱未來值。未來值既然是以複利成長，那麼利率是多少，以及多久複利一次，就會影響到貨幣的未來價值。

4 在現實世界之財務狀況除了單獨的一筆金額需要求未來值外，常常也年金也有這需求。Excel的FV函數，主要是計算期初(pv)發生的單筆金額，以及年金(pmt)的現金流量一起考慮，然後計算其未來的價值。
未來值的應用非常廣泛，例如100存入銀行，年利率3%，3年後會拿回多少錢。又如現在100元一碗牛肉麵，假若通貨膨脹率每年2%，20年後一碗牛肉麵會變成多少錢。這些都是很典型的未來值應用範例。

5 單筆的未來值公式 單筆的未來值是計算期初(pv)有一筆金額，然後每期以利率(rate)複利成長，經過(nper)期後，其未來值(FV)公式如下： FV = pv*(1+ rate)nper

6

7 例如期初存入銀行10,000元，年利率10%，每年複利計算一次，請問6年後可領回多少？這是典型未來值的計算： pv = 10000；nper = 6；rate = 10%； 所以未來值 fv = 10000*(1+10%)6 以Excel公式表示：=10000*(1+10%)^6 = 17,716

8 年金之未來值 年金的意義是於期間內共分幾期(nper)，每期均於期末產生一筆金額(pmt)，每一筆金額都會以每期利率(rate)複利成長，若每期之金額pmt都相等。到了最後一期末時這些金額所累計的未來值的公式為： 詳細年金觀念請參考：年金-理論篇

9 上圖黃色長條代表年金現金流量。

10 例如每年均於年終時存入銀行1000元，總共存了6年，年利率10%，每年複利計算一次，到了第六年終時，總共可領回多少錢？ pmt = 1000；nper = 6；rate = 10%； 所以未來值 fv = 1000*((1+10%)6-1)/10% 以Excel公式表示：=1000*((1+10%)^6-1)/10% = 7,716

11 現金流量於期初發生 前述的年金現金流量均發生於期末，若同樣的現金流量都發生於期初，這樣的狀況只需將期末未來值再乘上(1+rate)就可以了，公式如下：

12

13 例如每年均於『年初』時存入銀行1000元，總共存了6年，年利率10%，每年複利計算一次，到了第六年終時，總共可領回多少錢？ pmt = 1000；nper = 6；rate = 10% 所以未來值 fv = 1000*(1+10%)*((1+10%)6-1)/10% 以Excel公式表示：=1000*(1+10%)*((1+10%)^6-1)/10% = 8,487

14 如何使用Excel的FV函數 上述算法都是一般財務書籍所使用的方式，可是Excel的 FV函數所使用的計算方式有一些不同，就是這一點點的不一樣，把許多的使用者弄得不知所措。 Excel的FV函數具有三大特色： 單筆以及年金混合使用 單筆及年金的現金流量，可各自使用正負值來代表 未來值也是以正負值代表

15 FV函數的參數 FV函數的參數定義如下： =FV(rate, nper, pmt, pv, type)
下表列出了參數的意義以及與單筆或年金相關的參數，可以看出利率rate及期數nper單筆或年金都會用到。pv為單筆之期初金額；pmt為年金之每期金額，type參數則只跟年金有關，說明年金是於期末(預設值)或期初發生。前面三項參數 rate、 nper、pmt是一定要有的參數不可以省略，後面兩項pv及type是可有可無，若這兩項沒有輸入，FV函數會採用預設值pv = 0; type = 0(期末)。

16 參數 意義 必要參數 單筆相關 年金相關 rate 每期利率 必要 ● nper 期數 pmt 年金之每期金額 pv 單筆之期初金額 選項 type 期初或期末 0：期末(預設) 1：期初

17 pv及pmt的正負值 FV函數可計算： 現值(pv)的未來值 年金(pmt)的未來值

18 又如銀行房貸，以貸款者角度來看：期初時銀行會撥一筆款項進來，這筆金額便是現金流入貸款者(正值)，之後每一月(期)均必須繳之攤還金額，是現金流出貸款者(負值)。反過來看，一個一模一樣的房貸，但從銀行角度來切入，期初時銀行會撥一筆款項給貸款者，這筆金額便是現金從銀行流出(負值)，之後每一月(期)均收到貸款者的還款金額，這屬於現金流入銀行(正值)。所以說相同一件貸款，期初的撥款動作，對貸款者是現金流入，對銀行卻是現金流出。兩者之分別，端看以何種角度來看。

19 FV計算結果的正負值 計算出來的未來值FV也是以正負值來表示。咦~~~這就奇怪了，正的未來值很容易理解，負的未來值又代表何意義呢？其實FV函數所計算出來的結果等於『現值(pv)的未來值』加上『年金(pmt)未來值』的補數。也就是說： 現值(pv)的未來值 + 年金(pmt)的未來值 + FV = 0

20 這又代表啥意義呢？為簡化說明起見，當年金(pmt)未來值等於零，這時就只剩下單筆未來值。公式簡化為：現值(pv)的未來值 + FV = 0也就是：
FV = - 單筆(pv)未來值。

21 若pv為正值，FV就是負值，代表一個若是現金流出，另一個就必須是現金流入，反之亦然。這樣才會平衡，這也是等號右項為零的意義。
例如期初時單筆拿出100元(pv=-100)去存銀行，年利率10%，3年後的未來值 = -100*(1+10%)^3 = 元，這時FV函數的結果 = -(-133.1) = 133.1元。白話的說就是拿出100元存銀行，3年後該拿回133.1元。這樣才會出入兩相平衡，好似期初(pv)拿出去100元，期末(FV)應該拿回來133.1元，這樣帳才會打平的意思。

22 假若現值(pv)的未來值等於零，這時：年金(pmt)的未來值 + FV = 0，FV = - 年金(pmt)的未來值。
FV = - (現值(pv)的未來值 + 年金(pmt)未來值)。

23 可是pv及pmt都各自可能為正值或負值。若pv及pmt均為正值(兩者都是現金流入)，那麼FV就肯定是負值(現金流出)。相反的，若pv及pmt均為負值，那麼FV肯定是正值。若pv及pmt正負號各自相反時，『現值(pv)的未來值』及『年金(pmt)未來值』會有部分自行相互相抵消，那麼FV就只要平衡這兩者之差額了。若差額為 0，代表『現值(pv)的未來值』跟『年金(pmt)未來值』兩者是相等的。

24 範例 由以上這些敘述可以看出Excel的FV函數不只是一個單純的未來值而已，其功能是非常大的，也難怪不容易弄清楚。我想用一些範例加上圖解的方式來解釋，讀者會應該比較容易理解。 範例Excel 以下的範例也提供下載。

25 單筆借款 James跟朋友借一筆10萬元的金額，雙方同意以年利率10%計息，借期2年以複利計算，請問到期後James該還朋友多少錢？
=FV(10%, 2, 0, ) = -121,000 以James角度來看，因為是借款，期初有一筆現金10萬元流入James，所以pv = 100,000。算出來的答案是-121,000，代表James必須拿出(現金流出)121,000還朋友，這筆帳才會平衡。

26

27 零存整付之定存 Lisa每月於期初均存入銀行一萬元，年利率2%，每月計算複利一次，請問一年後可以拿回多少錢？
=FV(2%/12, 12, , 0 , 1) = 121,308 Lisa每月拿出10,000元(現金流出 pmt = -10,000)，而且是期初拿出(type = 1)，所以期末時(FV)當然要拿回121,308(現金流入)，所以當然是正值了。

28

29 退休規劃 Michael現年35歲，現有資產200萬元，預計每年可結餘30萬元，若將現有資產200萬及每年結餘30萬均投入5%報酬率的商品，請問60歲退休時可拿回多少錢？ =FV(5%, 25, , ) = 21,090,840 這是單筆(pv)及年金(pmt)都是負值的例子，期初時拿出200萬元(pv=-2,000,000)，而且每年於期末(type = 0)還拿出30萬元(pmt = -300,000)，那麼期末當然是要拿回21,090,840元，這帳才會平衡。

30

31 貸款餘額 Peter有一筆100萬元的10年期貸款，年利率10%，每月支付13,215.074元，請問於第5年底貸款餘額為多少？
=FV(10%/12, 12*5, , ) = -621,972 這也是單筆(pv)加年金(pmt)的例子，只是pv為正值，pmt為負值。Peter於期初時拿入100萬元(pv = 1,000,000)，每月繳納13, (pmt = -13, )，到第5年時，pv及pmt兩相平衡後尚差-62,1972，也就是說期末時還必須拿出621,972，這筆帳才會平衡，代表期末貸款餘額尚差這金額。 同樣一個公式，如果將期數nper由5年(12*5)改為10年(12*10)，FV一定會等於0，代表這貸款還清了。 =FV(10%/12, 12*10, , ) = 0

32

33 Excel的通用公式 喜歡數學的朋友看到下列式子一定很高興，這是從微軟Excel的FV函數的說明裡面節錄下來的。這個公式寫得非常漂亮，一個簡潔的公式道盡了所有貨幣時間價值的真諦。這等式左邊的第一項，就是單筆(pv)未來值的公式，第二項就是年金(pmt)未來值的公式，只不過多乘上(1+rate*type)來解決期初、期末的問題。第三項就是未來值FV了。等式的右邊項是零，代表說這個帳要打平。這真是個漂亮的公式，一個式子適用於所有的狀況。

34

35 以FV函數計算未來值因子(FVIF及FVIFA)
了解Excel FV函數的應用之後，應該不難理解FV函數也可以用來計算單筆未來值因子，及年金未來值因子。欲更深入了解的讀者可參考：現值及未來值因子表。 未來值因子 Excel FV公式 FVIF(r, n) =FV(rate, nper, 0, -1) 期末 FVIFA(r, n) =FV(rate, nper, -1) 期初 FVIFA(r, n)

36 不適用的年金 Excel的FV函數只適用每期金額大小都一致的年金。這種年金的現金流量每期一樣，所以可以使用公式來計算。若年金每期發生的現金流量金額都不一樣，如變額年金、每期金額以一定百分比成長(例如5%)之年金等，就無法使用Excel的FV函數了。這時便必須將每期所發生的現金流量，個別視為單筆，一一計算每一筆之未來值，然後再全部加總即可。

37 PV

38 Excel 函數-PV現值 讀完本篇後，不了解Excel裡的PV函數應用也難。現值是屬於貨幣的時間價值的一環，Excel也提供了一個相對函數PV (Present Value)來呼應。相信許多人或多或少都知道什麼是現值，但是當使用Excel PV函數時，卻常常碰到一些問題，尤其是正負號部分常常讓使用者頭疼。本篇主要介紹該函數的意義及其應用，除了理論介紹以外，還附有範例供讀者參考。

39 何謂現值 現值的英文為Present Value (PV)，就是未來之一筆金額經過一段時間以複利折現(Discount)後，相當於現在的貨幣價值。簡單舉個例說，若利率為5%，5年後的100元，經過折現後相當於現值78.4元。也就是說現在只要78.4元，以5%的複利成長，剛好會等於5年後的100元。現值也相當於現在該準備多少，才足以支付未來的現金流量。想要5年後有100元，那現在就得存78.4元。

40 現值既然是以複利折現，那麼利率是多少，以及多久複利一次，就會影響到現值。在現實世界之財務狀況是複雜的，除了單筆金額外，常常也會有年金的應用，Excel的PV函數，除了單筆金額外，年金部分也一併考慮。現值的應用非常廣泛，例如預計5年後可以擁有100萬，若年利率為3%，現在應該存多少錢。又如貸款年利率為4%，期限為3年，每月都繳本息一萬元，相當於向銀行貸了多少錢？這些都是很典型的現值應用範例。

41 單筆的現值公式 單筆的現值於期末時，有一筆金額(fv)，然後每期以利率(rate)複利折現，經過(nper)期後，其現值(PV)公式如下：
PV = fv / (1+ rate)nper

42

43 例如年利率10%，每年複利計算一次，請問現在要存多少錢，6年後可領回17,716？這是典型現值計算： fv = 17716；nper = 6；rate = 10%； 所以未來值 pv = 17716/(1+10%)6 以Excel公式表示：=17716/(1+10%)^6 = 10,000

44 年金的意義是於期間內共分幾期(nper)，每期均於期末產生一筆金額(pmt)，每一筆金額都會以每期利率(rate)複利折現，若每期之金額pmt都相等。這些金額所累計的現值公式為：
一般年金之現值 年金的意義是於期間內共分幾期(nper)，每期均於期末產生一筆金額(pmt)，每一筆金額都會以每期利率(rate)複利折現，若每期之金額pmt都相等。這些金額所累計的現值公式為：

45

46 例如Peter向Jason借錢，約定每月均於月初時還本息1000元，總共6個月，月利率1%，每月複利一次，問Jason願意借多少錢給Peter？ pmt = 1000；nper = 6；rate = 1%；type =1 所以現值 pv = 1000*(1+1%)*(1-1/(1+1%)6)/1% 以Excel公式表示：=1000*(1+1%)*(1-1/(1+1%)^6)/1% = 5,853

47 如何使用Excel的PV函數 Excel的PV函數具有三大特色： 單筆以及年金混合使用 單筆及年金的現金流量，可各自使用正負值來代表
未來值也是以正負值代表

48 PV函數的參數 PV函數的參數定義如下： =PV(rate, nper, pmt, fv, type)
下表列出了參數的意義以及與單筆或年金相關的參數，可以看出利率rate及期數nper單筆或年金都會用到。fv為單筆之期末金額；pmt為年金之每期金額，type參數則只跟年金有關，說明年金是於期末(預設值)或期初發生。前面三項參數 rate、 nper、pmt是一定要有的參數不可以省略，後面兩項fv及type是可有可無，若這兩項沒有輸入，PV函數會採用預設值fv = 0; type = 0(期末)。

49 PV函數的參數定義如下： =PV(rate, nper, pmt, fv, type)
下表列出了參數的意義以及與單筆或年金相關的參數，可以看出利率rate及期數nper單筆或年金都會用到。fv為單筆之期末金額；pmt為年金之每期金額，type參數則只跟年金有關，說明年金是於期末(預設值)或期初發生。前面三項參數 rate、 nper、pmt是一定要有的參數不可以省略，後面兩項fv及type是可有可無，若這兩項沒有輸入，PV函數會採用預設值fv = 0; type = 0(期末)。

50 參數 意義 必要參數 單筆相關 年金相關 rate 每期利率 必要 ● nper 期數 pmt 年金之每期金額 fv 單筆之期末金額 選項 type 期初或期末 0：期末(預設) 1：期初

51 fv及pmt的正負值 PV函數可計算： 未來值(fv)的現值 年金(pmt)的現值

52 例如定期存款：投資者將錢存入銀行，就是現金從投資者流出(負值)，到期後收到銀行支付之本利和，就是現金流入投資者口袋(正值)。又如銀行房貸，以貸款者角度來看：期初時銀行會撥一筆款項進來，這筆金額便是現金流入貸款者(正值)，之後每一月(期)均必須繳之攤還金額，是現金流出貸款者(負值)。反過來看，一個一模一樣的房貸，但從銀行角度來切入，期初時銀行會撥一筆款項給貸款者，這筆金額便是現金從銀行流出(負值)，之後每一月(期)均收到貸款者的還款金額，這屬於現金流入銀行(正值)。所以說相同一件貸款，期初的撥款動作，對貸款者是現金流入，對銀行卻是現金流出。兩者之分別，端看以何種角度來看。

53 PV計算結果的正負值 計算出來的現值PV也是以正負值來表示。咦~~~這就奇怪了，正的現值很容易理解，負的現值又代表何意義呢？其實PV函數所計算出來的結果等於『終值的現值』加上『年金的現值』的補數。也就是說： 終值(fv)的現值+ 年金(pmt)的現值 + PV = 0

54 這又代表啥意義呢？為簡化說明起見，先不考慮年金現值(pmt=0)，公式簡化為：終值(fv)的現值 + PV = 0，也就是：
PV = - 終值(fv)的現值。 若fv為正值，PV就是負值，代表一個若是現金流出，另一個就必須是現金流入，反之亦然。

55 例如一筆100元貸款(fv=-100)，約定3年後還款，年利率10%，所以PV = -(-100/(1+10%)^3) = 75
例如一筆100元貸款(fv=-100)，約定3年後還款，年利率10%，所以PV = -(-100/(1+10%)^3) = 75.1元。白話的說就是借入75.1元，3年後該還人家100元。好似期初(PV)拿進75.1元，期末(fv)應該拿出100元。 假若 fv=0 ，只剩下年金(pmt)現值，這時：年金(pmt)現值 + PV = 0，PV = - 年金(pmt)現值，也是相同的意義。 當終值及年金同時都有時：終值(fv)的現值 + 年金(pmt)的現值 + PV = 0。也就是說 PV = - (終值(fv)的現值 + 年金(pmt)的現值)

56 等於兩項相加之負值。可是fv及pmt都各自可能為正值或負值，問題就複雜了。若fv及pmt均為正值(兩者都是現金流入)，那麼PV就肯定是負值(必須現金流出才會平衡)。相反的，若fv及pmt均為負值，那麼PV肯定是正值。 當fv及pmt都同為正值或負值時，PV負責平衡單筆及年金兩者現值之加總；若fv及pmt正負號各自相反時，『單筆現值』及『年金現值』會有部分自行相互抵消，那麼PV就只要平衡這兩者之差額了。若差額為 0，代表『單筆現值』跟『年金現值』兩者是相互平衡或相等。

57 範例 由以上這些敘述可以看出Excel的PV函數不只是一個單純的現值而已，其功能是非常大的，也難怪不容易弄清楚。我想用一些範例加上圖解的方式來解釋，讀者會應該比較容易理解。 範例Excel 以下的範例也提供下載。

58

59 銀行貸款 Lisa於每月底必須繳交貸款本息一萬元，年利率2%、每月計算複利一次、期限為一年。請問Lisa跟銀行貸了多少錢？
=PV(2%/12, 12, ) = 118,710 Lisa每個月底拿出10,000元(所以是年金式的現金流出 pmt = -10,000)，那麼期初(PV)當然要拿到118,710的銀行撥款(現金流入)，這樣才划算。

60

61 退休規劃 Michael現年35歲，預計60歲退休，目前『每年』可結餘30萬元，均投入5%報酬率的商品。希望退休時可有擁有2000萬的退休金，請問Michael現在必須已經擁有多少存款才有辦法達到這目標？ =PV(5%, 25, , ) = -1,677,872 這是單筆(fv)為正值、年金(pmt)為負值的例子，希望期末時拿到2000萬元(fv=20,000,000)，而且每年於期末(type = 0)還拿出30萬元(pmt = -300,000)，那麼期初必須拿出-1,677,872元，這帳才會平衡。也就是尚欠1,677,872元。

62 如果完全相同的一個例子，但是每年結餘由30萬元變成50萬元，結果又是不一樣了： =PV(5%, 25, , ) = 1,140,917 現值由負轉正喔，每年結餘30萬(pmt = -300,000)時，還缺約168萬。當每年結餘50萬(pmt = -500,000)時，就多了約114萬。當PV=0的那個pmt，就是剛好所需要的每年結餘，一定是介於30萬~50萬之間，答案是-419,049。

63

64 貸款餘額 Peter有一筆10年期貸款，年利率10%，每月支付13,215.07元，第5年底貸款餘額還剩621,972元，請問Perter貸了多少錢？ =PV(10%/12, 12*5, , ) = 1,000,000 這也是單筆(fv)加年金(pmt)的例子，只是fv、pmt均為負值。Peter每月繳納13,125.07(pmt = )，到第5年時，還必須拿出62萬多(fv = )，也就是說期初時貸款1,000,000，這筆帳才會平衡。

65 Excel的通用公式 PV函數其實就是當rate、nper、pmt、fv、type為已知時，解下列函數的PV值。

66 以PV函數計算未來值因子(PVIF及PVIFA)
了解Excel PV函數的應用之後，應該不難理解PV函數也可以用來計算單筆現值因子，及年金現值因子。欲更深入了解的讀者可參考：現值及未來值因子表。 未來值因子 Excel PV公式 PVIF(r, n) =PV(rate, nper, 0, -1) 期末 PVIFA(r, n) =PV(rate, nper, -1) 期初 PVIFA(r, n) =PV(rate, nper, -1, 0, 1)

67 不適用的年金 Excel的PV函數只適用每期金額大小都一致的年金。這種年金的現金流量每期一樣，所以可以使用公式來計算。若年金每期發生的現金流量金額都不一樣，如變額年金、每期金額以一定百分比成長(例如5%)之年金等，就無法使用Excel的PV函數了。這時便必須將每期所發生的現金流量，個別視為單筆，一一計算每一筆之現值，然後再全部加總即可。有關這部份可參考： 年金-理論篇

68 PMT

69 Excel函數-PMT PMT就是年金的意思，用途相當廣泛，諸如銀行貸款，年金保險等都會用到。但是Excel的PMT更是把年金的應用發揮的淋漓盡致。 何謂年金 如果讀者對年金尚未了解，請先參考『年金-理論篇』及『年金-應用篇』。

70 PMT與其他函數之關係 Excel提供了下列非常實用的財務函數：PV(現值)、PMT(年金)、FV(未來值或終值)、RATE(利率)、NPER(期數。這五個函數相互之間是息息相關的，也可以說連成一體。只要知道任何中四個，便可求出另外的那一個。那麼函數之間的關係又是如何？是如何互動的。從觀念上來看，這幾個函數架構了一個平衡系統，可以用一個『蹺蹺板』的概念(如下圖)清楚表達出來。

71

72 蹺蹺板的左邊有刻度，NPER就是分成幾段的意思，RATE決定了每一刻度的長度。那麼蹺蹺板位置又如何分配呢？左邊給PV(單筆現值)及PMT(年金的每期金額)，右邊就是FV(未來值或終值)。當期數愈多、RATE愈大，代表左邊長度愈長，也就是『槓桿』愈大。當然這『槓桿』代表著複利的意義。只要長度夠長，小小的PV反應在蹺蹺板右邊的力量就很大了。整個概念就是：RATE及NPER決定左邊蹺蹺板的刻度及長度，當PV、PMT及FV坐上去時必須維持平衡。

73 公式 PV、PMT、FV、RATE、NPER之間的關係，若以數學公式表達如下：

74 現金流向 PV、PMT以及FV的現金流向都是有方向性的，現金流出以及現金流入。在圖上是以箭頭向上及向下來表示，只是箭頭方向在蹺蹺板兩邊剛好相反，如下表： 這又是何原因，蹺蹺板左邊箭頭往下代表現金流入，此時反應在蹺蹺板右邊會往上蹺，若是要蹺蹺板平衡，右邊就必須有往下的力量，也就是現金流出(左邊現金流入，右邊就應該是現金流出才會兩邊平衡)。所以蹺蹺板右邊箭頭往下代表現金流出，箭頭往上代表現金流入。也就是箭頭方向位於蹺蹺板兩邊剛好流向相反。 箭頭方向 天平左邊 天平右邊 箭頭向下 現金流入 現金流出 箭頭向上

75

76 PV、PMT相同流向 接著看看PV及PMT的方向。PV、PMT方向若相同，兩者力量是相加的，反應在蹺蹺板右邊的力量當然也是相加。只是當PV及PMT都往上時，那蹺蹺板是相反的，那就請讀者運動運動，倒立著看這圖了。

77

78 PV、PMT流向相反 PV、PMT方向若相反，兩者力量是相減的，反應在蹺蹺板右邊的力量當然也是變小了。而且不只FV會比PV、PMT任一個還小，方向還得看哪個大。當然當FV等於零時，代表PV及PMT剛好力量兩相抵消，剛好是平衡的。

79

80 Excel PMT函數 有了這些基本觀念，接下來就簡單了。利率RATE及期數NPER確定後，只要知道PV及FV的值，就可以求出PMT了。

81 PMT的參數 PMT函數的參數定義如下： =PMT(rate, nper, pv, fv, type)
下表列出了參數的意義，前面三項參數 rate、 nper、pv是一定要有的參數不可以省略，後面兩項fv及type是可有可無。type參數說明年金是發生於期末(預設值)或期初，若這兩項沒有輸入，PMT函數會採用預設值fv = 0; type = 0(期末)。 參數 意義 必要參數 rate 每期利率 必要 nper 期數 pv 單筆之期初金額 fv 單筆之期末金額 選項 type 期初或期末 0：期末(預設) 1：期初

82 範例 存錢買車 Jeff預計5年後買一輛新車60萬元，若目前年利率3%，從現在起於每月需存多少錢。
以這例子每期為一個月，PV=0，NPER =12*5，rate = 3%/12，FV =600,000。 =PMT(3%/12, 12*5, 0, ) =-9,281

83 也就是每月就須拿出9,281元

84 房屋貸款 Susan向銀行貸款100萬元，利率5%、期限20年，本息均攤請問月繳款多少元？
這例子相當於一個月為一期，總共有240期(NPER=240)，每期利率=5%/12，Susan期初跟銀行拿了100萬(PV=1,000,000)，那麼每月必需繳多少錢，期末餘額才會等於零(FV=0)？ =PMT(5%/12,240, ) =-6,600

85 也就是每月得繳本息6,600元

86 房屋貸款2 Susan向銀行貸款100萬元，利率5%，到第3年底(36期)時，尚有餘額905,717，問Susan每月繳款的金額是多少元？
這例子相當於一個月為一期，總共有36期(NPER=36)，每期利率=5%/12，Susan期初跟銀行拿了100萬(PV=1,000,000)，那麼每月必需繳多少錢，期末餘額才會等於905,717(FV=-905,717)？ =PMT(5%/12,36, , ) =-6,600

87 Suan的月繳款金額是6,600元。

88 退休規劃 Peter現有存款200萬，希望15年後退休可達1500萬，若Peter的投資報酬率每年有8%，每年需要另存多少錢？
存款投資200萬(PV=-2,000,000)，每年為一期，總共有15期(NPER=15)，每期報酬率為8%(RATE=8%)，15年後拿回1500萬(FV=15,000,000)，那麼每年還需投資： =PMT(8%,15, , ) =-318,784

89 也就是每年還得拿出31.8萬元去投資，15年後連同那200萬元的單筆投資，總共可得1,500萬元。

90 RATE

91 Excel函數-RATE 我們生活週遭充滿了跟『利率』或『報酬率』相關的例子，舉凡房屋貸款、基金報酬率以及儲蓄險，全部都跟報酬率有關。例如房屋貸款100萬、20年期，銀行跟我說每月得繳款6,600元，那麼年利率到底是多少呢？投資基金也是另一個例子：3年前買了一檔股票基金，現在有20%的報酬率，那相當於每年幾趴的報酬率呢？Excel提供了一個全能的函數，可以立即得到答案。

92 RATE與其他函數之關係 了解RATE函數之前，得先了解RATE跟其他函數之間的關係。Excel提供了五個非常實用的財務函數：PV(現值)、PMT(年金)、FV(未來值或終值)、RATE(利率)、NPER(期數。每個函數相互之間是息息相關的，也可以說連成一體。只要知道任何中四個，便可求出另外的那一個。那麼函數之間的關係又是如何？是如何互動的。從觀念上來看，這幾個函數架構了一個平衡系統，可以用一個『蹺蹺板』的概念(如下圖)清楚表達出來。

93

94 蹺蹺板的左邊有刻度，NPER就是分成幾段的意思，RATE決定了每一刻度的長度。那麼蹺蹺板位置又如何分配呢？左邊給PV(單筆現值)及PMT(年金的每期金額)，右邊就是FV(未來值或終值)。當期數愈多、RATE愈大，代表左邊長度愈長，也就是『槓桿』愈大。當然這『槓桿』代表著複利的意義。只要長度夠長，小小的PV反應在蹺蹺板右邊的力量就很大了。整個概念就是：RATE及NPER決定左邊蹺蹺板的刻度及長度，當PV、PMT及FV坐上去時必須維持平衡。

95 公式 PV、PMT、FV、RATE、NPER之間的關係，若以數學公式表達如下：

96 現金流向 PV、PMT以及FV的現金流向都是有方向性的，現金流出以及現金流入。在圖上是以箭頭向上及向下來表示，只是箭頭方向在蹺蹺板兩邊剛好相反，如下表： 箭頭方向 天平左邊 天平右邊 箭頭向下 現金流入 現金流出 箭頭向上

97 這又是何原因，蹺蹺板左邊箭頭往下代表現金流入，此時反應在蹺蹺板右邊會往上蹺，若是要蹺蹺板平衡，右邊就必須有往下的力量，也就是現金流出(左邊現金流入，右邊就應該是現金流出才會兩邊平衡)。所以蹺蹺板右邊箭頭往下代表現金流出，箭頭往上代表現金流入。也就是箭頭方向位於蹺蹺板兩邊剛好流向相反。

98

99 PV、PMT相同流向 接著看看PV及PMT的方向。PV、PMT方向若相同，兩者力量是相加的，反應在蹺蹺板右邊的力量當然也是相加。只是當PV及PMT都往上時，那蹺蹺板是相反的，那就請讀者運動運動，倒立著看這圖了。

100

101 PV、PMT流向相反 PV、PMT方向若相反，兩者力量是相減的，反應在蹺蹺板右邊的力量當然也是變小了。而且不只FV會比PV、PMT任一個還小，方向還得看哪個大。當然當FV等於零時，代表PV及PMT剛好力量兩相抵消，剛好是平衡的。

102

103 Excel RATE函數 有了這些基本觀念，接下來就簡單了。RATE函數就是傳回：已知的期數(NPER)的情況下，多少的『期利率』會使得PV、PMT及FV相互得到平衡。

104

105 RATE的參數 RATE函數的參數定義如下： =RATE(nper, pmt, pv, fv, type, guess)

106 參數 意義 必要參數 nper 期數 必要 pmt 年金之每期金額 pv 單筆之期初金額 fv 單筆之期末金額 選項 type 年金發生於期初或期末 0：期末(預設) 1：期初 guess 猜測利率可能之落點

107 type參數說明年金是發生於期末(預設值)或期初，若沒有輸入，RATE函數會採用預設值 type = 0(期末)。fv是期末終值(預設值 = 0)，至於guess這參數就得費點功夫說明。

108 guess真是個有趣的參數，RATE函數的任務不就是要解出報酬率的值嗎，怎會要我們自己猜測報酬率的落點呢？這不是很奇怪嗎，Excel計算功能那麼強，難道RATE函數無法直接解出來？沒錯，如果看看上面的公式，當PV、PMT、FV、NPER為已知時，看如何直接解出RATE的值？這就難了，還好雖然無法直接求解，Excel使用代入逼近法，先假設一個可能的rate(預設值為10%)，然後代入上面式子看看是否吻合，如果不是就變動rate的值，然後慢慢逼近、反覆計算，直到誤差小於 ％ 為止。如果真正的解和預設值差距過遠，運算超過20次還是無法求得答案，RATE 函數會傳回錯誤值 #NUM!。這時使用者就必須使用較接近的 guess 值，然後再試一次。

109 所以guess參數只是RATE函數開始尋找答案的起始點而已，跟找到的答案是無關。例如下面範例『基金年化報酬率』下面三個RATE公式，其他參數都一樣，但是guess都不同，結果答案卻都一樣是4.14%。 =RATE(10, 0, , , 0, 10%) =RATE(10, 0, , , 0, 8%) =RATE(10, 0, , , 0, 4%)

110 範例 基金年化報酬率(一) Peter於10年前，以10萬元買了一個基金，現在該基金淨值15萬元，請問這樣相當於多少的年報酬率？
以這例子每年為一期，PV=-100,000，NPER =10，PMT=0，FV =150,000。 =RATE(10, 0, , , 0, 10%) = 4.14%

111

112 基金年化報酬率(二) Peter於10年前，以10萬元買了一個基金，而且每月定期定額2,000元買相同之基金，現在該基金淨值65萬元，請問這樣相當於多少的年報酬率？ 以這例子每月為一期，PV=-100,000，PMT=-2,000，NPER =120，FV =650,000。 =RATE(120, -2000, , , 0, 1%)*12 = 9.4% 註：因為這例子每月為一期，所以RATE是傳回『月報酬率』，年報酬率必須再乘上12。

113

114 房屋貸款 Susan向銀行貸款100萬元，期限20年，每月本息攤還6,600元，問這貸款年利率是多少？
以這例子每月為一期，總共240期(NPER =240)，期初拿到100萬(PV=1,000,000)，每期繳款6,600(PMT=-6,600)，期末還清(FV = 0)。 =RATE(240, -6600, , 0, 0, 1%)*12 = 5.0%

115

116 NPER

117 Excel函數-NPER 在財務的應用上，常常需要知道要多久可以達到投資目標，或是需要幾個月可以還清貸款。Excel提供了一個非常實用的函數：NPER (Number of Period)，可以輕易的算出我們所需要的答案。

118 NPER與其他函數之關係 了解NPER函數之前，得先了解NPER跟其他函數之間的關係。Excel提供了五個非常實用的財務函數：PV(現值)、PMT(年金)、FV(未來值或終值)、RATE(利率)、NPER(期數。每個函數相互之間是息息相關的，也可以說連成一體。只要知道任何中四個，便可求出另外的那一個。那麼函數之間的關係又是如何？是如何互動的。從觀念上來看，這幾個函數架構了一個平衡系統，可以用一個『蹺蹺板』的概念(如下圖)清楚表達出來。

119

120 蹺蹺板的左邊有刻度，NPER就是分成幾段的意思，RATE決定了每一刻度的長度。那麼蹺蹺板位置又如何分配呢？左邊給PV(單筆現值)及PMT(年金的每期金額)，右邊就是FV(未來值或終值)。當期數愈多、RATE愈大，代表左邊長度愈長，也就是『槓桿』愈大。當然這『槓桿』代表著複利的意義。只要長度夠長，小小的PV反應在蹺蹺板右邊的力量就很大了。整個概念就是：RATE及NPER決定左邊蹺蹺板的刻度及長度，當PV、PMT及FV坐上去時必須維持平衡。

121 公式 PV、PMT、FV、RATE、NPER之間的關係，若以數學公式表達如下：

122 現金流向 PV、PMT以及FV的現金流向都是有方向性的，現金流出以及現金流入。在圖上是以箭頭向上及向下來表示，只是箭頭方向在蹺蹺板兩邊剛好相反，如下表： 箭頭方向 天平左邊 天平右邊 箭頭向下 現金流入 現金流出 箭頭向上

123 這又是何原因，蹺蹺板左邊箭頭往下代表現金流入，此時反應在蹺蹺板右邊會往上蹺，若是要蹺蹺板平衡，右邊就必須有往下的力量，也就是現金流出(左邊現金流入，右邊就應該是現金流出才會兩邊平衡)。所以蹺蹺板右邊箭頭往下代表現金流出，箭頭往上代表現金流入。也就是箭頭方向位於蹺蹺板兩邊剛好流向相反。

124

125 PV、PMT相同流向 接著看看PV及PMT的方向。PV、PMT方向若相同，兩者力量是相加的，反應在蹺蹺板右邊的力量當然也是相加。只是當PV及PMT都往上時，那蹺蹺板是相反的，那就請讀者運動運動，倒立著看這圖了。

126

127 PV、PMT流向相反 PV、PMT方向若相反，兩者力量是相減的，反應在蹺蹺板右邊的力量當然也是變小了。而且不只FV會比PV、PMT任一個還小，方向還得看哪個大。當然當FV等於零時，代表PV及PMT剛好力量兩相抵消，剛好是平衡的。

128

129 Excel NPER函數 有了這些基本觀念，接下來就簡單了。NPER函數就是傳回：已知的利率(RATE)的情況下，多少『期』會使得PV、PMT及FV相互得到平衡。

130

131 NPER的參數 NPER函數的參數定義如下： =NPER(rate, pmt, pv, fv, type)

132 type參數說明年金是發生於期末(預設值)或期初，若沒有輸入，NPER函數會採用預設值 type = 0(期末)。fv是期末終值(預設值 = 0)。
意義 必要參數 rate 每期利率 必要 pmt 年金之每期金額 pv 單筆之期初金額 fv 單筆之期末金額 選項 type 期初或期末 0：期末(預設) 1：期初

133 範例 貸款規劃 Queena買了一間房子，希望跟銀行貸款300萬元、利率2.2%，每月有能力繳本息30,000元，請問要多久可以繳清貸款？
以這例子每月為一期，PV=3,000,000，RATE =2.2%/12，PMT=-30000，FV = 0。 =NPER(2.2%/12, , , 0) = 110.6 需要110個月才能繳清貸款，亦就是不到十年就還完了。

134

135 退休規劃 Pete目前30歲，擁有存款200萬元，每年底可結餘30萬元，均投資年報酬率8%的商品，希望退休時可以擁有1,500萬元，那麼幾歲可以退休？ 以這例子每年為一期，PV=-2,000,000，PMT=-300,000，RATE =8%，FV =15,000,000。 =NPER(8%, , , ) =15.4 哇~~~真好，Peter大約15年後、45歲就可退休了。

136

137 提早償還貸款 Susan向銀行貸款100萬元，利率3%、期限20年，每月本息攤還5,546元。已經繳了5年，還剩本金餘額803,088元，目前Susan剛好有一筆業務獎金30萬元進帳，想提早還款。Susan希望往後每月還是繳相同的錢，多久以後可以還清貸款？ 以這例子每月為一期，目前本金還有503,088(PV=803, ,000)，年利率3%(RATE=3%/12)，每期繳款5,546(PMT=-5,546)，期末還清(FV = 0)。 =NPER(3%/12, -5546, ) =103 也就是103個月後即可還清貸款。

138

139 IRR、XIRR、MIRR

140 Excel 財務函數-IRR、XIRR、MIRR
IRR(Internal Rate of Return)稱為內部報酬率，其應用非常廣大，是學習財務管理不可或缺的工具。Excel也提供了一個相對函數IRR 來呼應。本篇也包含IRR相關的其他兩個函數XIRR以及MIRR，讓讀者可以清楚知道每個函數的應用時機。

141 現金流量(Cash Flow) 閱讀本篇之前讀者必須先了解現金流量，才會知道IRR的意義。對現金流量還不了解的讀者，怪老子網站的『現金流量』篇有詳細介紹。

142 IRR函數 一個投資案會產生一序列的現金流量，IRR簡單說：就是由這一序列的現金流量中，反推一個投資案的內部報酬率。

143 如何反推呢，所用的方法是將每筆現金流量以利率rate折現，然後令所有現金流量的淨現值(NPV)等於零。若C0、C1、C2、C3
如何反推呢，所用的方法是將每筆現金流量以利率rate折現，然後令所有現金流量的淨現值(NPV)等於零。若C0、C1、C2、C3...Cn分別代表為期初到n期的現金流量，正值代表現金流入，負值代表現金流出。 0 = C0 + C1/(1+rate)1 + C2/(1+rate)2 + C3/(1+rate) Cn/(1+rate)n 找出符合這方程式的rate，就稱為內部報酬率。問題是這方程式無法直接解出rate，必須靠電腦程式去找。這個內部報酬率又和銀行所提供的利率是一樣的意思。

144 IRR函數的參數定義如下： =IRR(Values, guess) 參數 意義 必要參數 Values 現金流量 必要 guess

145 IRR的參數有兩個，一個是Values也就是『一序列』現金流量；另一個就是猜個IRR最可能的落點。那麼Value的值又該如何輸入？有兩種方式可輸入一序列的現金流量：
儲存格的範圍：例如=IRR(B2:B4)，範圍中每一儲存格代表一期

146 那麼 =IRR({-100, 7, 107}) 或 =IRR(B2:B4) 都會得到同樣答案：7%

147 使用者定義期間長短 IRR的參數並沒有絕對日期，只有『一期』的觀念。每一期可以是一年、一個月或一天，隨著使用者自行定義。如果每一格是代表一個『月』的現金流量，那麼傳回的報酬率就是『月報酬率』；如果每一格是代表一個『年』的現金流量，那麼傳回的報酬率就是『年報酬率』。 例如{-100, 7, 107}陣列有3個數值，敘述著第0期(期初)拿出100元，第1期拿回7元，第2期拿回107元。第一個數值代表0期，也是期初的意思。至於每一期是多久，使用者自己清楚，IRR並不需要知道，因為IRR傳回的是『一期的利率』。當然如果使用月報酬率，要轉換成年報酬率就得乘上12了。

148 一年為一期 例如期初拿出100元存銀行，1年後拿到利息7元，2年後拿到本利和107元，那麼現金流量是{-100, 7, 107}。很清楚的這現金流量的每期間隔是『一年』，所以=IRR({-100, 7, 107}) = 7%傳回的就是『年報酬率』。

149 一個月為一期 換個高利貸公司的例子來看，期初借出100元，1個月後拿到利息7元，2個月拿到本利和107元，整個現金流量還是{-100, 7, 107}喔，不一樣的是每期間隔是『一個月』。那麼IRR傳回的 7%就是『月報酬率』，年報酬率必須再乘上12，得到84%的年化報酬率。所以每一期是多久只有使用者知道，對IRR而言只是傳回『每期』的報酬率。

150 guess-猜測報酬率可能的落點 guess真是個有趣的參數，IRR函數的任務不就是要解出報酬率的值嗎，怎會要我們自己猜測報酬率的落點呢？這不是很奇怪嗎，Excel計算功能那麼強，難道IRR函數無法直接解出來？沒錯IRR是無法解的。以{-100, -102, -104, -106, 450}這現金流量為例，等於得求出下列方程式中rate的解： 0 = /(1+rate)1 -104/(1+rate)2 -106/(1+rate) /(1+rate)4

151 這就難了！因為有4次方。假若現金流量的期數更多，那就更複雜了，而且使用者會輸入幾期還不知道哩。還好雖然無法直接求解，Excel使用代入逼近法，先假設一個可能的rate(10%)，然後代入上面式子看看是否吻合，如果不是就變動rate的值，然後慢慢逼近、反覆計算，直到誤差小於 ％ 為止。如果真正的解和預設值差距過遠，運算超過20次還是無法求得答案，IRR 函數會傳回錯誤值 #NUM!。這時使用者就必須使用較接近的 guess 值，然後再試一次。

152 所以guess參數只是IRR函數開始尋找答案的起始點而已，跟找到的答案是無關。下面三個IRR公式，同樣的現金流量，但是guess參數都不同，結果答案卻都一樣是3.60%。 =IRR({-100, -102, -104, -106, 450}) =IRR({-100, -102, -104, -106, 450}, 1%) =IRR({-100, -102, -104, -106, 450}, 2%)

153 guess是選項參數 guess參數可以省略不輸入，這時Excel會使用預設值10%。通常這是一年為一期報酬率都落在這附近，如果要計算月報酬率最好輸入1%，依此類推。

154 XIRR函數 若要利用IRR函數來計算報酬率，現金流量必須是以『一期』為單位，也就是輸入的現金流量必須有期數的觀念。但是常常有些應用，現金流量並非定期式的。例如一個投資案，現金流量如下表： 可以看到現金流量發生日期是不定期的，並非以一期為單位。XIRR就是專為這類型的現金流量求報酬率，其他觀念和IRR函數沒有差別。XIRR傳回來的報酬率已經是年報酬率。 日期 金額 2007/8/15 -100,000 2007/11/6 23,650 2008/3/4 25,000 2009/6/8 82,500

155 XIRR參數 XIRR(values, dates, guess) 參數 意義 必要參數 Values 現金流量的值 必要 dates
現金流量發生日期 guess 猜測XIRR可能的落點 選項

156 和IRR函數的差別是多了一個日期(dates)參數，此日期參數(dates)必須跟現金流量(Value)成對。例如上面的例子可以如下圖的方式來完成。儲存格B7的公式 =XIRR(A2:A5,B2:B5)，算出來這投資案相當於每年24.56%的報酬率。

157 需要開啟分析工具箱 使用XIRR函數必須安裝「分析工具箱」，否則會傳回#NAME? 錯誤。
1) 工具/增益集 2) 將分析工具箱打勾 3) 按確定

158 MIRR函數 MIRR參數 MIRR(values, finance_rate, reinvest_rate) 參數 意義 必要參數
現金流量的值 必要 finance_rate 融資利率 reinvest_rate 再投資報酬率

159 MIRR是Modified Internal Rate of Return的縮寫，意思是改良式的IRR。IRR到底有何缺點，需要去修正呢？主要的原因是IRR並未考慮期間領回現金再投資問題！IRR的現金流量裡可分為正值及負值兩大類，正值部分屬於投資期中投資者拿回去的現金，這些期中拿回去的現金該如何運用，會影響報酬率的。負值部分屬於投資期中額外再投入的資金，這些資金的取得也有融資利率方面要考慮。 MIRR使用的方式是將期間所有的現金流入，全部以『再投資利率』計算終值FV。期間所有的現金流出，全部以『再投資利率』計算現值PV。那麼MIRR的報酬率： =(FV/PV)1/n - 1

160

161 再投資報酬率 以例子來解說會較為清楚，一個投資案的現金流量如下：{-10000, 500, 500, 10500}這現金流量一期為一年，期初拿出10,000元，第1年底拿回500元，第2年底也拿回500元，第3年底拿回10500元。將現金流量代入IRR求內部報酬率： =IRR({-10000, 500, 500, 10500}) = 5%

162 從這投資案的經營者來說，期初拿到10,000元，然後每年支付5%報酬500元，到了第3年底還本10000元，這投資案確實是每年發放5%的報酬沒錯。可是若從投資者角度來看，假若每年底拿到的500元只會放定存2%，也就是投資者期中拿回來的金額，到期末只有2%的報酬率，那麼投資者到第3年底時，實際拿到的總金額為： = 500*(1+2%) + 500*(1+2%) ( 以Excel 表示 =500*(1+2%)+500*(1+2%)^ ) = 11,530

163 期初拿出10,000元，3年後拿回11,530，這樣相當於年化報酬率： = (11530/10000)1/3-1 (Excel 表示 =(11530/10000)^(1/3)-1 ) = 4.86% 這可解讀為拿出10,000元，以複利4.86%成長，3年後會拿回11,530元。 MIRR可以不用那麼麻煩，只需輸入再投資報酬率2%，便可輕易得到實際報酬率： = MIRR(({-10000, 500, 500, 10500}), 0, 2%) = 4.86%

164

165 投資者期間內所拿回的現金(正值)，再投資的報酬率，會影響整體投資的實際報酬率。同一個例子，假若另一位投資者的再投資報酬率為4%，那麼實際報酬率修正為： = MIRR(({-10000, 500, 500, 10500}), 0, 4%) = 4.95% 如果投資者的再投資報酬率為5%，實際報酬率等於： = MIRR(({-10000, 500, 500, 10500}), 0, 5%) = 5.00% 可以看到當再投資報酬率為5%時，MIRR = IRR = 5%。這也同時說明了，IRR內部報酬率是假設再投資報酬率等於內部報酬率。

166 融資利率 如果有第0期以外的現金投入(負現金流量)，這些資金是在未來的期數才會使用。只要在期初準備這些資金的現值。就足以支付未來的這些金額。所以將所有的現金流出均以『融資利率』折現(PV)，代表未來所有的投資額，都相當於期初投資PV的金額。 舉個例子：一個儲蓄險，頭兩年、年初繳保費5萬元。第二年底開始，往後四年均領回30,000元，若再投資利率為3%，融資利率為5%，求MIRR為多少？ = MIRR({-50000, , 30000, 30000, 30000, 30000}, 5%, 3%) = 5.15% 這是利率MIRR函數直接套入公式算出來的，為了解其中道理，將這些正負現金流量分開來，以分解動作求取終值及現值，再算投資報酬率。這樣讀者就很清楚MIRR是如何計算的。

167

168 總共有兩筆現金流出(負值部份)，但是只有第2期的資金流出需要折現，所以用『融資利率』5%，總現值(PV)： = /(1+5%) =97,619 這意思是說：若融資利率為5%，只需要在期初準備97,619，就足以支付前兩期各5萬所需的現金支出。 總共有4筆現金流入(正值部份)，以『再投資利率』為3%，求取所有現金流入的終值(FV) = *(1+3%) *(1+3%)^ *(1+3%)^3 =125,508

169 那麼期初投入97,619，期末拿回125,508，期間為5年，這樣的年化投資報酬率： = (125508/97619)^(1/5)-1 = 5.15%
可以看到和直接用MIRR函數所計算出來的報酬率一模一樣都是5.15%。 『融資利率』是微軟的說明所使用的名詞，我認為較為適當的應該是『資金報酬率』(finance_rate)，也就是資金存放標的之報酬率。

170 投資理財之應用

171 Excel投資理財之應用 在投資的規劃上，我們常常需要一些計算，例如我們想知道向銀行貸款兩百萬，分20年本息定額償還，那麼每月應該支付多少錢。或者有一個基金，五年的報酬率為96%，那麼年化報酬率是多少？諸如此類問題，Excel是個非常實用的工具，可以快速算出所需要的答案。我相信很多人都會使用Excel，但是除了財務專業人士外，能把財務函數用的很熟練的似乎也不多。在本篇文章裡，我只介紹和投資相關的五個財務函數FV、PV、RATE、PMT及NPER，試著用範例的方式呈現出來，讓非專業人士都能容易上手。

172 基本概念 我們都知道金錢的價值會隨著時間成長，而且是以複利成長。例如將一百萬存放於銀行一年，一年後這一百萬的價值當然不只一百萬，而是一百萬再加上利息，至於利息有多少是由利率來決定，以及經過多少期的複利過程。

173 單筆及定期投資 投資種類依投入次數可分為單筆投資以及定期重複性投資。以銀行的『整存整付式定存』就是最好的單筆投資的例子，在期初時投入一筆金額，期中都沒有再投入，等到期末後領回一筆本利和。『零存整付式定存』便是定期重複性的投資型態，定期於每一期固定投入一筆金額，等到期末後再領回本利和。下圖是這兩種投資型態的圖示。

174 Excel的財務函數是將這兩種混合在一起，所以參數看起來較為複雜容易用錯，但只要了解以後用起來是既簡潔又方便。

175 定期投資的期初及期末 定期投資部份依照每期金額的投入點，又可分為期初及期末兩種(TYPE)，如下圖所示：

176 現金流入及流出 Excel財務函數金額部分有正負號之分，正值代表金額流入，負數代表金額流出。例如你要計算整存整付定存的期末領回本利和。期初金額(PV)是由你交給銀行(現金流出所以是負值)，期滿後銀行會給付本利和的金額(現金流入所以是正值)。相反的如果是以銀行的角度來看，期初金額(PV)因為存款流入所以是正值，期滿算出的未來值(FV)應該是負值，代表要給付存款人的金額。

177 如果要計算債券的現值(PV)，因為債券每期會支付利息給你，屬於定期金額所以用PMT，而且是現金流入所以要用正值。到期領回金額(FV)亦屬於現金流入都要用正值。可是算出來的現值(PV)會是負值，代表要付出PV(現金流出)的金額去換得該債券未來的本息。

178 利率(RATE)及期數(NPER) 財務函數的利率(RATE)不是固定使用常用的年利率，而是視每一期的時間來決定利率。多久為一期則要看應用，每一種應用都不一樣，但是利率的單位一定和每一期的時間長短一致。如果每月為一期，那就要用月利率。每年為一期，就要用年利率，依此類推。 例如銀行的存放款都是以一個月為一期，所以代入的利率參數(RATE)就要用月利率(年利率/ 12)。至於期數就看整體投資期間有多長再除以每一期的時間就可以了。

179 如果我們想計算年利率2.26%的整存整付定存，一年後本利和的金額，因為一個月為一期，一年有12個月所以期數NPER =12。利率必須用月利率RATE = 2.26% / 12。
如果我問：銀行整存整付定存1,000,000元，年利率2.26%一年後可以拿回多少？相信很多人會答說$1,022,600，可是實際上銀行會支付$1,022,836。雖然和預期的金額不符，但是因為銀行付的比較多，大部份的人是不會追究原因的，其實是因為銀行是每月為一期(每月複利一次)，所以等於分成12期來計算，每期的利率為月利率計算，等於2.26% / 12 = %， 代入公式 =FV(2.26%/12, 12, 0, ) = $1,022,836。

180 參數介紹 下表所列是本文所要介紹的函數功能及參數： 函數 功能 參數 FV 到期後未來值(終值)
=FV(rate, nper, pmt, [pv], [type]) PV 單筆的現值 =PV(rate, nper, pmt, [fv], [type]) RATE 每期的利率或報酬率 =RATE(nper, pmt, pv, [fv], [type], [guess]) PMT 每期投資金額 =PMT(rate, nper, pv, [fv], [type]) NPER 期數 =NPER(rate, pmt, pv, [fv], [type])

181 這五個函數其實是跟金錢的時間價值息息相關的五個參數。當你知道任何其中四個就可以求出剩下的那一個。例如已知每期利率RATE、期初投資金額PV、每期投入PMT以及期數NPER，就可得知期末本利和FV。
參數部份有打中括號的代表可以省略的參數，其餘部分則是必須有的參數。 下載下列範例之Excel檔案 這Excel檔案有5個工作表：分別為未來值(FV)、現值(PV)、利率(RATE)、每期投資金額(PMT)、期數(NPER)。一個函數一個工作表，方便讀者參考。

182 未來值Future Value(FV) 當利率RATE、期數NPER、期初投資PV及每期投資PMT均為已知時，所求得的未來本利和FV。
公式 =FV(rate, nper, pmt, [pv], [type]) 詳細請參考：Excel函數-FV未來值

183 整存整付定存 以“整存整付定存”存入銀行一百萬，每月複利一次計算，年利率5%，期間為半年。到期後本利和為多少？
RATE = 5% / 12 (每月為一期，月利率 = 年利率 / 12) NPER = 6(半年分6期) PMT = 0 (只有單筆所以設定0) PV = -1,000,000(存入銀行一百萬，現金流出所以為負值) =FV(5%/12, 6, 0, ) = $1,025,262 期末領回本利和$1,025,262

184 零存整付定存 每月期初均存款一萬元至銀行，年利率4.5%，一年後(12期)會領回多少錢？
RATE = 4.5% / 12 (每月為一期，月利率 = 年利率 / 12) NPER = 12(分12期) PMT = -10,000(每月定期一萬元，因為現金流出所以負值) PV = 0 (其初沒有單筆投入，所以等於零) =FV(4.5%/12, 12, , 0, 1) = $122,966。 期末領回$122,966

185 預估投資收益 現年37歲擁有存款兩百萬元可投資，每月扣除生活開銷外尚有餘錢三萬元可做投資運用，假如預計60歲退休，每年平均投資報酬率設定為6%，到退休時，我會擁有多少錢？ RATE = 6%/12(每月為一期，月利率 = 年利率 / 12) NPER = 12*(60-37)(投資期數 276期) PMT = -30,000(每月投資3萬元) PV = -2,000,000(期初投200萬元) =FV(6%/12, 12*(60-37), , , 1) = $25,778,895

186 如果想知道每年平均投資報酬率改為8%，結果會變成什麼？只需要將上述公式6%改成8%： =FV(8%/12, 12
我們立即知道相差約一千萬元，這可不是個小數目！

187 現值Present Value (PV) 當利率RATE、期數NPER、期末金額FV及每期投資PMT均為已知時，所求得的現值PV。
公式 =PV(rate, nper, pmt, [fv], [type]) 詳細請參考：Excel函數-PV現值

188 退休金的現值 預期五年後可以拿到的兩百萬退休金，假使通貨膨脹率每年以2%成長，相當於現在多少的價值？
RATE = 2%(一期為一年，每年以2%成長) NPER = 5(一年一期，所以期數等於5) PAYMENT = 0(只有單筆，所以為0) FV = 2,000,000(期末拿到兩百萬退休金) =PV(2%, 5, 0, ) = -1,811,461 這代表五年後的兩百萬，如果計算通貨膨脹後，只相當於現今的1,811,461。 負值的意義是代表現金流出，現在拿出約181萬，五年後換回200萬。

189 債券的價值 債券每半年領息三萬元，三年半後到期，到期領回一百萬，若以年利率5%計算，相當於現值多少錢？
RATE:= 5% / 2(每半年為一期，每期利率為年利率除以2) NPER = 7 (三年半，每半年一期總共七期) PAYMENT = 30,000(每半年定期領息三萬元) FV = 1,000,000(到期領回一百萬) =PV(5%/2, 7, 30000, ) = -1,031,747 負值代表必須現在拿出-1,031,747，才可換得此債券未來之利息及本金。

190 利率(RATE) 當期數NPER、每期投資PMT、期初投資PV及期末金額FV均為已知時，所求得的等值利率RATE。
公式 =RATE(nper, pmt, pv, [fv], [type], [guess])

191 汽車貸款利率 買一輛新車80萬元整，已付頭期款20萬元，其餘60萬元分3年36期貸款，每期需繳納$19,360，求該貸款年利率為多少？通常用來驗證車商告訴我們的貸款利率是否確實。 NPER = 36 (每月一期分36期支付) PMT= -$19,360(每期支付19,360) PV = 600,000(貸款60萬) =RATE(36, , ) = % 所求出為月利率，年利率必須再乘以12，所以等於10.00%

192 投資年化報酬率 I 有一股票型基金期初投資10萬元經過5年後該基金淨值成長至22萬元，求該基金之年化報酬率？
NPER = 5(每年為一期，分5期計算) PMT = 0(每期沒有投資，所以為0) PV = -$100,000(期初投資10萬元，現金流出) FV = $220,000(期末淨值22萬元，現金流入) =RATE(5, 0, , ) = 17.08% 這相當於每年固定以年利率17.08%成長

193 投資年化報酬率 II 有一股票型基金每月定期定額投資1萬元經過5年後該基金淨值為80萬元，求該基金之年化報酬率？
NPER = 5*12(每月為一期，分60期計算) PMT = -10,000(每月投資10,000，現金流出) PV = 0(期初沒有單筆投資) FV = $800,000(期末淨值80萬元) =RATE(5*12, , 0, ,1)*12 = 10.89% 這相當於每年固定以年利率10.89%成長 (每月為一期，所以RATE計算結果為月利率，必須乘以12才會成為年利率)

194 每期投資金額PAYMENT (PMT) 當期初投資PV、每期利率RATE、期數NPER及期末值FV均為已知時，求得每期該投資多少金額PMT。
公式 =PMT(rate, nper, pv, [fv], [type])

195 本息定額償還貸款 向銀行房屋貸款350萬元，年利率3.5%，以本息定額分20年償還，每月該繳款多少錢？
RATE = 3.5%/12(每月為一期，月利率 = 年利率 / 12) NPER = 20*12(分20*12 = 240期償還) PV = $3,500,000(貸款金額350萬) =PMT(3.5%/12, 20*12, ) = -$20,299 每月必需繳款本息$20,299元 (因為是現金流出所以是負值)

196 退休規劃 目前擁有存款200萬元，希望15年後退休，退休時必需擁有現金1000萬元，如果以年報酬率6%計算，每月該定期定額投資多少錢？
RATE = 6%/12(每月為一期，月利率 = 年利率 / 12) NPER = 15*12(分15*12 = 180期投資) PV = -$2,000,000(期初投資200萬) FV = $10,000,000(期末金額1000萬) =PMT(6%/12, 15*12, , ) = -17,509

197 每月需要投資17,509才有機會達成目標。 如果每月投資金額過大，試著把投資期間改為20年，再看結果 =PMT(6%/12, 20

198 期數(NPER) 當期每期利率RATE、每期投資金額PMT、初投資PV及期末值FV均為已知時，求多少期可以達成目標。
公式 =NPER(rate, pmt, pv, [fv], [type])

199 退休規劃 目前擁有存款200萬元，退休時必需擁有現金1000萬元，如果以年報酬率6%計算，每月有能力投資五萬元，多久後可以退休？
RATE = 6%/12(每月為一期，月利率 = 年利率 / 12) PMT = -50,000(每月投資5萬元) PV = $-2,000,000(已有200萬) FV = $10,000,000(期末金額1000萬) =NPER(6%/12, , , ) = 103期 因為每月為一期，除以12得到約8.5年可達成目標

200 貸款相關

201 Excel財務函數-貸款相關 (PMT/IPMT/PPMT/CUMIPMT/CUMPRINC)
函數名稱 計算下列金額 PMT 每期繳款金額(本金+利息) IPMT 第n期的利息金額 PPMT 第n期的本金金額 CUMIPMT 兩期間(m~n)的利息金額總和 CUMPRINC 兩期間(m~n)的本金金額總和

202 何謂本息平均攤還 本息平均攤還的意思是，貸款者向債權者借了一筆款項，以約定的年利率，在約定的期間內每期以固定金額償還本金及利息。每期除了利息外還償還部分本金，一直到最後一期將本金還清。也就是說第一期到最後一期所繳的金額都一樣。 例如信用貸款30萬元，年利率6.5%，以每月為一期繳納利息及本金，總共分24期(2年)還清。本息平均攤還特色是每期除了繳納利息外，還得償還本金，但是每一期的本金加上利息的金額都一樣。下圖所示24期所繳納的金額，咖啡色部份是利息，青色部份是本金部分，可以看出每期兩者加總的金額都一樣(13,364)，但是利息及本金的比例每期都不一樣，前期利息的份量比較多，愈到後期本金的比例就比較大。

203

204 PMT函數 每期繳款金額可以用PMT函數輕易算出，只要輸入貸款條件當參數，立刻可求出每期該繳的本息平均攤還金額。 PMT的參數如下：
PMT(rate,nper,pv, fv, type) 名稱 意義 範例參數值 rate 每期利率 6.5% / 12 nper 總期數 24 pv 貸款金額 300000 fv 年金終值 省略(0) type 金額的給付時點 省略(期末?付)

205 因為PMT函數是年金通用函數，fv和type於貸款計算時可以省略，所以暫時不提比較不會複雜。所以這例子每月繳款金額：
要注意的是利率要和期數一致，本範例為每月為一期，那麼利率就必須用『月』利率，亦就是年利率除以12，這就是為何rate參數用6.5%/12的原因。

206 製作攤還表 我最喜歡用『反推法』來做攤還表，為何說是反推法呢？就是先用PMT函數算出每期繳款金額(下圖儲存格B4)，然後從第一期所繳的利息及償還本金，一路反算貸款餘額，直到最後一期的貸款餘額為零為止。這種方法驗算方式是：最後一期的貸款餘額若為零，就知道正確無誤。其實這種算法是最符合直覺，也最為讓人接受。下圖便是用反推法算出的攤還表。

207 算法是每一期都以“前期”的貸款餘額為基礎，計算當期該繳的利息。然後將每期繳款金額扣除當期利息後，就是當期所償還的本金了。那麼“前期”的貸款餘額扣除掉當期所償還本金，當然就變成當期的貸款餘額了。然後由第一期開始一路往下算，到最後一期的貸款餘額當然一定是零，否則就是哪裡有錯了。

208 根據這做法： 第一期：利息=300000*(6.5%/12) = 1,625；償還本金= =11,739；貸款餘額= =288,261 第二期：利息=288261*(6.5%/12) = 1,561；償還本金= =11,802；貸款餘額= =276, 依此類推(如下圖所示)

209 可以看出每期的貸款餘額一路下降，到最後一期的貸款餘額(儲存格B31)恰好等於零！也就是算法無誤。

210 IPMT及PPMT函數 除了反推法外，Excel提供了IPMT函數可以立即知道任何一期所繳的利息是多少，以及PPMT函數可以計算任何一期所償還的本金是多少。IPMT及PPMT函數的參數如下： IPMT(rate, per, nper, pv, fv, type) PPMT(rate, per, nper, pv, fv, type) 這兩個函數和PMT函數唯一不一樣的是多了一個第二參數per，就是要算第幾期的意思啦。例如我們要知道上圖攤還表第4期的利息部分金額是多少，可以用IPMT函數來計算： =IPMT(6.5%/12, 4, 24, ) = -1,433 跟上圖儲存格D11的數值一模一樣。

211 同樣很容易的，也可以用PPMT函數求出第4期償還本金部分是多少： =PPMT(6
同樣很容易的，也可以用PPMT函數求出第4期償還本金部分是多少： =PPMT(6.5%/12, 4, 24, ) = -11,931 跟上圖儲存格C11的數值是一樣的。

212 攤還表也可以直接利用IPMT及PPMT，來計算每期所要繳交的利息以及償還本金的金額。讀者可以下載本範例的Excel，裡面有兩個攤還表，一個是反推法做的，另一個是用IPMT及PPMT計算的，可以發現兩者數值是一模一樣。

213 CUMIPMT及CUMPRINC函數 Excel財務函數不只可以計算某一期的本金及利息外，也可計算兩期之間總共繳納的利息是多少，或者償還了多少本金。CUMIPMT可以求出兩期之間總共繳了多少利息；CUMPRINC則可以求出兩期之間總共償還多少本金。這兩個函數的參數分別如下： CUMIPMT(rate,nper,pv,start_period,end_period,type) CUMPRINC(rate,nper,pv,start_period,end_period,type) 這兩個函數所有的參數都必須填入，不可省略。rate, nper, pv, type是貸款條件，start_perido及end_perido分別代表所要計算起始期數及結束期數。

214 參數 意義 備註 rate 每期利率 貸款條件 nper 總期數 pv 貸款金額 貸款條件，pv只能輸入正值，不接受負值 start_period 計算的起始期數 end_period 計算的終止期數 type 0：期末付款 1：期初付款 貸款條件，通常為0

215 還有這兩個函數必須安裝「分析工具箱」，否則會傳回#NAME? 錯誤。
1) 工具/增益集 2) 將分析工具箱打勾 3) 按確定

216 例如範例的攤還表，如果想知道第2期至第6期總共繳交了多少利息，只要將貸款條件月利率6
例如範例的攤還表，如果想知道第2期至第6期總共繳交了多少利息，只要將貸款條件月利率6.5%/12，24期，貸款金額300000以及所要計算的期數2~6期，輸入函數： =CUMIPMT(6.5%/12, 24, , 2, 6, 0) = -7,164 (負值代表現金流出) 便可得到2~6期利息之加總7,164元(如下圖粉紅色儲存格)。

217 同樣的，如果想知道第2期至第6期總共償還了多少本金，只要將貸款條件月利率6
同樣的，如果想知道第2期至第6期總共償還了多少本金，只要將貸款條件月利率6.5%/12，24期，貸款金額300000以及所要計算的期數2~6期，輸入函數： =CUMPRINC(6.5%/12, 24, , 2, 6, 0) = -59,655 (負值代表現金流出) 便可得到2~6期已償還本金之加總59,655元(如下圖粉紅色儲存格)。

218 第n期之貸款餘額 如果我們想知道某一期的貸款餘額，Excel並沒有提供這樣的函數，但是可以配合CUMPRINC函數來計算得到所要的答案。例如想知道第10期末時，貸款餘額還剩多少？這時可以用貸款金額扣除掉1~10期所有償還的本金就可以了。例如本範例第10期之貸款餘額也可以由下列方式求得： = CUMPRINC(6.5%/12, 24, , 1, 10, 0) 因為CUMPRINC結果是負值所以前面用加號 =179,708

219 如何計算總利息 總繳利息計算方式如下： 總繳利息 = 每期本息繳款*總期數 - 貸款金額
例如範例中每期繳款 元，24期總共繳交 =24* = 320,733 扣除掉貸款金額30萬，全部利息等於20,733元。 我們也可以用CUMIPMT函數來計算總利息，只要將起始期數設定為1，終止期數設定為最後一期24就可以了： =CUMIPMT(6.5%/12, 24, , 1, 24, 0) = -20,733 可以看出兩者答案完全相吻合。