葉兆輝.

葉兆輝

P N F R (A=PMT) 當題目是沒有A時(如1-5題)，則給定F、P 、r、n 其中的三個，可求第四個。當題目是有A時(如6-15題)，則給定A、F、r、n 其中的三個，可求第四個(如6-9題)。則給定A、P、r、n其中的三個，可求第四個(如10-15題)。附註： 1. EXCEL 的符號Pmt=A，Nper=n，Rate=r。 2. 五個變數中，只要給定其中三個，即可求解。

理財發財買豪宅葉教授老師又來了來幹什麼來教各位理財理財幹什麼發財阿發財幹什麼買豪宅阿因此理財發財買豪宅理財投資不投機股市變幻莫測唯一不變的是訊息萬變有人問我說長線好還是短線好我說都不好內線最好又有人問我說技術面好還是基本面好我說都不好有朋友在裡面最好讀萬卷書不如行萬里路行萬里路不如有名師來開路俗話說的好，買對股票讓你上天堂、住洋房，買錯股票讓你住套房。買對房子幸福一輩子。

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

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

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

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

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

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

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

例如每年均於年終時存入銀行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

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

例如每年均於『年初』時存入銀行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

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

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

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

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

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

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

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

若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元，這樣帳才會打平的意思。

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

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

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

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

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

退休規劃 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元，這帳才會平衡。

貸款餘額 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

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

以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)

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

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

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

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

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

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

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

例如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

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

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

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

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

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

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

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

例如一筆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)的現值)

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

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

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

退休規劃 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元。

如果完全相同的一個例子，但是每年結餘由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。

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

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

以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)

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

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

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

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

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

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

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

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

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

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：期初

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

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

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

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

房屋貸款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

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

退休規劃 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

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

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

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

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

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

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

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

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

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

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

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

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

基金年化報酬率(二) 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。

房屋貸款 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%

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

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

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

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

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

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

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

範例貸款規劃 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個月才能繳清貸款，亦就是不到十年就還完了。

退休規劃 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歲就可退休了。

提早償還貸款 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個月後即可還清貸款。

IRR、XIRR、MIRR

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

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

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

如何反推呢，所用的方法是將每筆現金流量以利率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，必須靠電腦程式去找。這個內部報酬率又和銀行所提供的利率是一樣的意思。

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

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

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

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

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

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

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

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

所以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%)

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

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

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

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

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

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

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

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

從這投資案的經營者來說，期初拿到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

期初拿出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%

投資者期間內所拿回的現金(正值)，再投資的報酬率，會影響整體投資的實際報酬率。同一個例子，假若另一位投資者的再投資報酬率為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內部報酬率是假設再投資報酬率等於內部報酬率。

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

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

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

投資理財之應用

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

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

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

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

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

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

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

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

如果我們想計算年利率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。

參數介紹下表所列是本文所要介紹的函數功能及參數：函數功能參數 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])

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

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

整存整付定存以“整存整付定存”存入銀行一百萬，每月複利一次計算，年利率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

零存整付定存每月期初均存款一萬元至銀行，年利率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

預估投資收益現年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

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

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

退休金的現值預期五年後可以拿到的兩百萬退休金，假使通貨膨脹率每年以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萬。

債券的價值債券每半年領息三萬元，三年半後到期，到期領回一百萬，若以年利率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，才可換得此債券未來之利息及本金。

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

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

投資年化報酬率 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%成長

投資年化報酬率 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才會成為年利率)

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

本息定額償還貸款向銀行房屋貸款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元 (因為是現金流出所以是負值)

退休規劃目前擁有存款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

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

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

退休規劃目前擁有存款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年可達成目標

貸款相關

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

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

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

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

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

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

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

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

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的數值一模一樣。

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

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

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分別代表所要計算起始期數及結束期數。

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

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

例如範例的攤還表，如果想知道第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元(如下圖粉紅色儲存格)。

同樣的，如果想知道第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元(如下圖粉紅色儲存格)。

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

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

-$13,300.03 -$1,433.21 -$11,930.67 -20733 PMT IPMT PPMT CUMIPMT CUMPRINC 如何計算總利息 PMT= IPMT+

葉兆輝.

Similar presentations

Presentation on theme: "葉兆輝."— Presentation transcript:

Similar presentations

About project

反馈

请登录

Auth with social network:

葉兆輝.

Similar presentations

Presentation on theme: "葉兆輝."— Presentation transcript:

Similar presentations

About project

反馈