Excel五大财务函数汇整

Excel五大財務函數彙整日期：2011-01-19目錄現金流量型態未來值函數-FV現值函數-PV年金函數-PMT期數函數-NPER報酬率函數-RATE內部報酬率函數-IRR現金流量型態EXCEL有五大財務函數，分別為FV、PV、PMT、RATE、NPER，而這五個函數之間都有相互關聯。整體概念如下：FV、PV及PMT分別描述一組現金流量的大小及發生時間點，PV是發生在期初的單筆現金流量，FV是發生在期末的單筆現金流量，PMT則是發生在每一期的重複性現金流量。而NPER函數描述這現金流量的期數。因貨幣具有時間價值，RATE函數是描述這些現金流量之間的報酬率關係。公式這一組現金流量相互之間，維持一個平衡及對等的關係，其關係式如下所示，只要知道其中四個參數，當然就可以計算出剩下的那一個。要學會如何活用這五大函數，只要學會如何描述一組現金流量，也就是說只要將所碰到的財務問題，用現金流量的型態呈現出來，然後用這些函數的參數輸入，就可以得到所要的答案。不過這五個財務函數並不是每一種現金流量的型態都可適用，必須符合下列條件的現金流量才可以使用：PV：單筆金額，只有在期初發生一次FV：單筆金額，只有在期末發生一次PMT：每一期都會發生的年金，不可以某些期數有，某些期數沒有，而且金額大小都必須一致PV、FV及PMT都可以是正值或負值，分別代表現金流量之方向，水平軸以下的為負值，代表是現金流出。水平軸以上的為正值，代表是現金流入。再加上期數NPER，以及適用的報酬率RATE，共同組合成這五大函數。期初年金PMT所發生的時間點，有期初及期末之分，都在每一期末的就稱為普通年金，type=0、也是預設值。發生在每一期的期初，就稱為期初年金，type的值必須設為1。現金流量如下圖所示：回目錄未來值函數FVFV函數主要用來計算未來值，只要將希望得到的現金流量之金額大小及發生的時間點，以rate,nper,pmt,pv,type參數描述，就可以得到未來值的金額，格式如下：FV(rate,nper,pmt,[pv],[type])參數意義rate報酬率nper期數pmt每一期之金額[pv]期初單筆金額[type]Pmt的發生時點0：期末(預設值)1：期初單筆借款James跟朋友借一筆10萬元的金額，雙方同意以年利率10%計息，借期2年以複利計算，請問到期後James該還朋友多少錢？FV(rate,nper,pmt,[pv],[type])=FV(10%,2,0,100000)=-121,000零存整付之定存Lisa每月於期初均存入銀行一萬元，年利率2%，每月計算複利一次，請問一年後可以拿回多少錢？FV(rate,nper,pmt,[pv],[type])=FV(2%/12,12,-10000,0,1)=121,308Lisa每月拿出10,000元(現金流出pmt=-10,000)，而且是期初拿出(type=1)，所以期末時(FV)當然要拿回121,308(現金流入)，所以當然是正值了。退休規劃Michael現年35歲，現有資產200萬元，預計每年可結餘30萬元，若將現有資產200萬及每年結餘30萬均投入5%報酬率的商品，請問60歲退休時可拿回多少錢？FV(rate,nper,pmt,[pv],[type])=FV(5%,25,-300000,-2000000)=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(rate,nper,pmt,[pv],[type])=FV(10%/12,12*5,-13215.074,1000000)=-621,972這也是單筆(pv)加年金(pmt)的例子，只是pv為正值，pmt為負值。Peter於期初時拿入100萬元(pv=1,000,000)，每月繳納13,215.074(pmt=-13,215.074)，到第5年時，pv及pmt兩相平衡後尚差-62,1972，也就是說期末時還必須拿出621,972，這筆帳才會平衡，代表期末貸款餘額尚差這金額。同樣一個公式，如果將期數nper由5年(12*5)改為10年(12*10)，FV一定會等於0，代表這貸款還清了。=FV(10%/12,12*10,-13215.074,1000000)=0回目錄現值函數PVPV函數主要用來計算現值，只要將希望得到的現金流量之金額大小及發生的時間點，以rate,nper,pmt,fv,type參數描述，就可以得到應該有的現值，格式如下：PV(rate,nper,pmt,[fv],[type])參數意義rate報酬率nper期數pmt每一期之金額[Fv]期末單筆金額[type]Pmt的發生時點0：期末(預設值)1：期初單筆借款James想跟朋友借一筆款項，雙方同意以年利率10%計息，借期2年、一年複利一次，到期還款10萬元。請問James可以借到多少錢？PV(rate,nper,pmt,[fv],[type])=PV(10%,2,0,-100000)=82,645以James角度來看，因為是借款，期末有一筆現金10萬元流出James，所以fv=-100000。算出來的答案是82,645，代表James應該拿到借款(現金流入)82,645，這筆帳才會平衡。銀行貸款Lisa於每月底必須繳交貸款本息一萬元，年利率2%、每月計算複利一次、期限為一年。請問Lisa跟銀行貸了多少錢？PV(rate,nper,pmt,[fv],[type])=PV(2%/12,12,-10000)=118,710Lisa每個月底拿出10,000元(所以是年金式的現金流出pmt=-10,000)，那麼期初(PV)當然要拿到118,710的銀行撥款(現金流入)，這樣才划算。退休規劃Michael現年35歲，預計60歲退休，目前『每年』可結餘30萬元，均投入5%報酬率的商品。希望退休時可擁有2,000萬的退休金，請問Michael現在必須已經擁有多少存款才有辦法達到這目標？PV(rate,nper,pmt,[fv],[type])=PV(5%,25,-300000,20000000)=-1,677,872這是單筆(fv)為正值、年金(pmt)為負值的例子，希望期末時拿到2,000萬元(fv=20,000,000)，而且每年於期末(type=0)還拿出30萬元(pmt=-300,000)，那麼期初必須拿出-1,677,872元，這帳才會平衡。也就是尚欠1,677,872元。如果完全相同的一個例子，但是每年結餘由30萬元變成50萬元，結果又是不一樣了：=PV(5%,25,-500000,20000000)=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(rate,nper,pmt,[fv],[type])=PV(10%/12,12*5,-13215.07,-621972)=1,000,000這也是單筆(fv)加年金(pmt)的例子，只是fv、pmt均為負值。Peter每月繳納13,215.07(pmt=-13215.07)，到第5年時，還必須拿出62萬多(fv=-621972)，也就是說期初時貸款1,000,000，這筆帳才會平衡。回目錄年金函數PMTPMT函數主要用來計算年金的金額，只要將希望得到的現金流量之金額大小及發生的時間點，以rate,nper,pv,fv,type參數描述，就可以得到年金的金額，格式如下：PMT(rate,nper,pv,[fv],[type])參數意義rate報酬率nper期數pv期初單筆金額[fv]期末單筆金額[type]Pmt的發生時點0：期末(預設值)1：期初存錢買車Jeff預計5年後買一輛新車60萬元，若目前年利率3%，從現在起於每月底需存多少錢。以這例子每期為一個月，pv=0，nper=12*5，rate=3%/12，fv=600,000。PMT(rate,nper,pv,[fv],[type])=PMT(3%/12,12*5,0,600000)=-9,281也就是每月須拿出9,281元房屋貸款Susan向銀行貸款100萬元，利率5%、期限20年，本息均攤請問月繳款多少元？這例子相當於一個月為一期，總共有240期(NPER=240)，每期利率=5%/12，Susan期初跟銀行拿了100萬(pv=1,000,000)，那麼每月必需繳多少錢，期末餘額才會等於零(fv=0)？PMT(rate,nper,pv,[fv],[type])=PMT(5%/12,240,1000000)=-6,600也就是每月得繳本息6,600元房屋貸款2Susan向銀行貸款100萬元，利率5%，到第3年底(36期)時，尚有餘額905,717，問Susan每月繳款的金額是多少元？這例子相當於一個月為一期，總共有36期(NPER=36)，每期利率=5%/12，Susan期初跟銀行拿了100萬(pv=1,000,000)，那麼每月必需繳多少錢，期末餘額才會等於905,717(fv=-905,717)？PMT(rate,nper,pv,[fv],[type])=PMT(5%/12,36,1000000,-905717)=-6,600退休規劃Peter現有存款200萬，希望15年後退休可達1,500萬，若Peter的投資報酬率每年有8%，每年需要另存多少錢？存款投資200萬(pv=-2,000,000)，每年為一期，總共有15期(nper=15)，每期報酬率為8%(RATE=8%)，15年後拿回1500萬(fv=15,000,000)，那麼每年還需投資：PMT(rate,nper,pv,[fv],[type])=PMT(8%,15,-2000000,15000000)=-318,784也就是每年還得拿出31.8萬元去投資，15年後連同期初那200萬元的單筆投資，總共可得1,500萬元。回目錄期數函數NPERNPER函數可以用來計算所需要的期數，只要將希望得到的現金流量之金額大小及發生的時間點，以rate,pmt,pv,fv,type參數描述，就可以得到應該有的期數，格式如下：NPER(rate,pmt,pv,[fv],[type])參數意義rate報酬率pmt每一期之金額pv期初單筆金額[fv]期末單筆金額[type]Pmt的發生時點0：期末(預設值)1：期初貸款規劃Queena買了一間房子，希望跟銀行貸款300萬元、利率2.2%，每月有能力繳本息30,000元，請問要多久可以繳清貸款？以這例子每月為一期，pv=3,000,000，rate=2.2%/12，pmt=-30000，fv=0。NPER(rate,pmt,pv,[fv],[type])=NPER(2.2%/12,-30000,3000000,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(rate,pmt,pv,[fv],[type])=NPER(8%,-300000,-2000000,15000000)=15.