2023年Ecel五大財(cái)務(wù)函數(shù)匯整

Excel五大同矜函數(shù)麋整

日期：2011-01-19

目金條

現(xiàn)金流量型魅

未來(lái)值函數(shù)-FV

現(xiàn)值函數(shù)-PV

年金函數(shù)-PMT

期數(shù)函數(shù)-NPER

幸員酬率函數(shù)-RATE

內(nèi)部輟酬率函數(shù)-IRR

現(xiàn)金流量型熊

EXCEL有五大財(cái)導(dǎo)務(wù)函數(shù)，分另?偏FV'PV'PMT、RATE、NPER?而道

五彳固函數(shù)之^都有相互居國(guó)馨。整颼概念如下：FV、PV及PMT分別描

述一品且現(xiàn)金流量的大小及彝生B寺^黠，PV是彝生在期初的軍肇現(xiàn)金流

量，F(xiàn)V是彝生在期末的罩肇現(xiàn)金流量，PMT即是彝生在每一期的重未復(fù)

性現(xiàn)金流量。而NPER函數(shù)描述道現(xiàn)金流量的期數(shù)。因貨整具有B寺^^

值?RATE函數(shù)是描述道些現(xiàn)金流量之^的輟酬率居射系。

FV

公式

造一條且現(xiàn)金流量相互之^-雒持一彳固平衡及封等的居射系■其居前系式如下

所示，只要知道其中四他參數(shù)，富然就可以^算出剩下的那一彳固。

.、“”r/(I+rate)nper—1\

pvX(1+rate)nper4-pmtx(1+ratextype)xI-----------------------)+fv=(

\rate/

要阜畬如何活用道五大函數(shù)■只要阜畬如何描述一條且現(xiàn)金流量?也就是

^只要符所碰到的財(cái)矜冏題■用現(xiàn)金流量的型魅呈現(xiàn)出來(lái)，然彳度用造些

函數(shù)的夢(mèng)萋好俞入，就可以得到所要的答案。不謾道五他^獰務(wù)函數(shù)她不是

每一槿現(xiàn)金流量的型熊都可逾用?必須符合下列脩件的現(xiàn)金流量才可以

使用：

?PV：罩肇金額，只有在期初樊生一次

?FV:罩肇金額，只有在期末彝生一次

?PMT:每一期都畬彝生的年金-不可以某些期數(shù)有?某些期數(shù)沒(méi)

有，而且金額大小都必須一致

PV、FV及PMT都可以是正值或負(fù)值，分別代表現(xiàn)金流量之方向?水

平軸以下的卷翼值，代表是現(xiàn)金流出。水平軸以上的卷正值，代表是現(xiàn)

金流入。再加上期數(shù)NPER?以及逾用的輟酬率RATE?共同^合成造

五大函數(shù)。

期初年金

PMT所彝生的日寺^黑占-有期初及期末之分，都在每一期末的就耦卷普

通年金，type=0、也是予船殳值。彝生在每一期的期初，就耦卷期初年

金,type的值必須三殳卷1°現(xiàn)金流量如下I1所示：

回目^

未來(lái)值函數(shù)FV

FV函數(shù)主要用來(lái)^算未來(lái)值?只要符希望得到的現(xiàn)金流量之金額大小

及彝生的日寺^黑占?以rate,nper,pmt,pv,type參數(shù)描述，就可以得到

未來(lái)值的金額?格式如下：

FV(rate,nper,pmt,[pv],[type])

意羲

rate輟酬率

nper期數(shù)

pmt每一期之金額

[pv]期初軍肇金額

Pmt的樊生畤黠

[type]0：期末^值)

1：期初

It肇借款

James跟朋友借一肇10離元的金額■曼方同意以年利率10%tt息，借

期2年以禊利tt算,^冏到期彳爰James吉亥逮朋友多少^?

PV=100,000nper=2

pmt=0

rate=10%

FV(rate,nper,pmt,[pv],[type])

=FV(10%,2,0,100000)=-121,000

零存整付之定存

Lisa每月於期初均存入金艮行一離元，年利率2%?每月言十算禊利一次-

言青冏一年彳爰可以拿回多少^?

nper=12

rate=2%/12

type=1

pmt=10,000

FV(rate,nper,pmt,[pv],[type])

=FV(2%/12,12,-10000,0,1)

=121,308

Lisa每月拿出10,000元(現(xiàn)金流出pmt=-10,000)?而且是期初拿出

(type=1)，所以期末日寺(FV)富然要拿回121,308(現(xiàn)金流入)，所以富然

是正值了。

退休燒副

Michael現(xiàn)年35it，現(xiàn)有資崖200離元?予居十每年可結(jié)繪30K元?

若聘現(xiàn)有資崖200K及每年結(jié)繪30離均投入5%幸反酬率的商品，

60蕨退休日寺可拿回多少金菱？

pmt=300,000

pv=2,000,000

FV(rate,nper,pmt,[pv],[type])

=FV(5%,25,-300000,-2000000)=21,090,840

道是罩肇(pv)及年金(pmt)都是負(fù)值的例子?期初日寺拿出200離元

(pv=-2,000,000)?而且每年於期末(type=0)逮拿出30離元(pmt=

-300,000)?那麼期末常然是要拿回21,090,840元，道巾展才畬平衡。

貸款繪額

Peter有一肇100離元的10年期貸款?年利率10%?每月支付

13,215.074元，3青冏於第5年底貸款繪額懸多少？

FV(rate,nper,pmt,[pv],[type])

=FV(10%/12,12*5,-13215.074,1000000)

=-621,972

IS也是罩ii(pv)加年金(pmt)的例子，只是pv卷正值■pmt卷負(fù)值。

Peter於期初B寺拿入100離元(pv=1,000,000)-每月%瞬內(nèi)

13,215.074(pmt=-13,215,074)?到第5年日寺?pv及pmt雨相平衡

彳度尚差-62,1972，也就是^期末日寺逮必須拿出621,972，道肇幔才曾平

衡，代表期末貸款繪額尚差造金額。

同檬一偃I公式?如果聘期數(shù)nper由5年(12*5)改懸10年(12*10)-FV

一定畬等於0，代表造貸款逮清了。

=FV(10%/12,12*10,-13215.074,1000000)

=0

回目金條

II值函數(shù)PV

PV函數(shù)主要用來(lái)者十算現(xiàn)值，只要招希望得到的現(xiàn)金流量之金額大小及

君菱生的日寺^黑占，以rate,nper,pmt,fv,type參數(shù)描述?就可以得到鷹

言亥有的現(xiàn)值?格式如下:

PV(rate,nper,pmt,[fv],[type])

參數(shù)意羲

rate甄酬率

nper期數(shù)

pmt每一期之金答真

[Fv]期末軍肇金額

Pmt的彝生疇黠

[type]0：期末(f0S值)

1：期初

罩肇借款

James想跟朋友借一肇款項(xiàng)?曼方同意以年利率10%言十息?借期2年、

一年禊利一次?到期遢款10離元。與青冏James可以借到多少金卷?

PV=?nper=2

rate=10%

~?2]

FV=10,000

PV(rate,nper,pmt,[fv],[type])

=PV(10%,2,0,-100000)=82,645

以James角度來(lái)看，因卷是借款期末有一肇現(xiàn)金10離元流出James-

所以fv=-100000。算出來(lái)的答案是82,645，代表James鷹者亥拿到借

款(現(xiàn)金流入)82,645-道肇幔才畬平衡。

備艮行貸款

Lisa於每月底必須繳交貸款本息一K元，年利率2%、每月tl■算禊利一

次、期限懸一年。Lisa跟金艮行貸了多少金菱?

PV(rate,nper,pmt,[fv],[type])

=PV(2%/12,12,-10000)

=118,710

Lisa每偃1月底拿出10,000元(所以是年金式的現(xiàn)金流出pmt=

-10,000)?那麼期初(PV)常然要拿到118,710的金艮行接款(現(xiàn)金流入)，

造檬才劃算。

退休燒副

Michael現(xiàn)年35蕨?予直言十60蕨退休，目前『每年』可結(jié)繪30K元，

均投入5%甄酬率的商品。希望退休畤可挑有2,000離的退休金?

Michael現(xiàn)在必須已^^有多少存款才有辨法連到道目襟?

nper=60-35=25

rate=5%

type=0

PV(rate,nper,pmt,[fv],[type])

=PV(5%,25,-300000,20000000)=-1,677,872

道是罩肇(fv)卷正值'年金(pmt)卷負(fù)值的例子?希望期末日寺拿到2,000

M7U(fv=20,000,000)?而且每年於期末(type=0)遢拿出30K元(pmt

=-300,000)?那麼期初必須拿出-1,677,872元，道巾辰才畬平衡。也就

是尚欠L677,872元。

如果完全相同的一他1例子，但是每年結(jié)繪由30離元燮成50離元,結(jié)

果又是不一檬了:

=PV(5%,25,-500000,20000000)=1,140,917

剜直由差事正喔每年結(jié)繪30匐pmt=-300,000州寺逮缺%勺168S。

可是常每年結(jié)繪增慎卷50M0e(pmt=-500,000)-就多了名勺114?。

Wpv=0的那偃Ipmt?就是剛好所需要的每年結(jié)繪?一定是介於30S

~50h之^?答案是-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

道也是IMI(fv)加年金(pmt)的例子■只是fv'pmt均卷負(fù)值。Peter每

月鬻嫌內(nèi)13,215.07(pmt=-13215.07)?到第5年日寺?遢必須拿出62S

多(fv=-621972)也就是^期初日寺:t款1,000,000蔣肇幔才畬平衡。

回目金條

年金函數(shù)PMT

PMT函數(shù)主要用來(lái)者十算年金的金額，只要招希望得到的現(xiàn)金流量之金

額大小及彝生的B寺^黑占?以rate,nper,pv,fv,type參數(shù)描述?就可以

得到年金的金額，格式如下：

PMT(rate,nper,pv,[fv],[type])

意羲

rate甄酬率

nper期數(shù)

PV期初軍肇金額

[fv]期末軍肇金額

Pmt的彝生畤黠

[type]0：期末值）

1：期初

存隼

Jeff5年彳麥黃一輛新事60離元，若目前年利率3%，彳能現(xiàn)在起於

每月底需存多少^。

以迄例子每期懸一彳固月?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

也就是每月鄉(xiāng)直拿出9,281兀

房屋貸款

Susan向金艮行貸款100離元?利率5%、期限20年?本息均腹儲(chǔ)青冏月

繳款多少元？

nper=12*20=240

pv=1,000,000rate=596/12

口type=0

j12)3,415?//236.2371238.239.240-

牛力t『。山小)：m7

PMT=?

造例子相常於一他I月卷一期/恩共有240期(NPER=240)-每期利率

=5%/12-Susan期初跟余艮行拿了100M(pv=1,000,000)-那麼每月必

需繳多少^■期末繪額才畬等於零(fv=0)?

PMT(rate,nper,pv,[fv],[type])

=PMT(5%/12,240,1000000)

=-6,600

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

房屋貸款2

Susan向金艮行貸款100離元?利率5%，到第3年底(36期州寺，尚有繪

II905,717，冏Susan每月繳款的金額是多少元？

pv=1,000,000nper=12*3=36

nrate=5%/12

type=0

III1i2i3i4i5iif32i33i34i35i36i

1rrrrUhuluu|

PMT=?

M905,717

造例子相富於一他1月懸一期/恩共有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現(xiàn)有存款200離，希望15年彳度退休可逢1,500??若Peter的

投資輟酬率每年有8%?每年需要另存多少^?

nper=15

rate=8%

type=0

j1i2?3?4?5I11?12j13?14?1511

一「m山》『『「『口

PMT=?

pv=200,

存款投資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,500k元。

回目^

期數(shù)函數(shù)NPER

NPER函數(shù)可以用來(lái)言十算所需要的期數(shù)，只要符希望得到的現(xiàn)金流量之

金額大小及彝生的B寺^黠，以rate,pmt,pv,fv,type參數(shù)描述，就可以

得到)《右亥有的期數(shù)?格式如下：

NPER(rate,pmt,pv,[fv],[type])

參數(shù)意羲

rate輟酬率

pmt每一期之金？H

pv期初軍肇金額

[fv]期末軍肇金額

Pmt的彝生疇黠

[type]0：期末^值)

1：期初

貸款規(guī)副

Queena黃了一^房子，希望跟金艮行:K款300離元、利率2.2%■每月

有相關(guān)能力繳本息30,000元-要多久可以繳清貸款？

fv=0

以道例子每月懸一期"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目前30itT雍有存款200離元，每年底可結(jié)繪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.4

哇~~~真好，Peter大東勺15年彳度、45it就可退休了。

提早慎遢貸款

Susan向金艮行貸款100K元利率3%、期限20年每月本息腹&逮5,546

元。已余皆繳了5年，遢剩本金繪容直803,088元，目前Susan剛好有一

肇棠矜樊金30離元迤幔?想提早遢款。Susan希望往彳爰每月逮是繳相

同的^?多久以彳度可以逮清貸款？

fv=0

pv=503,088rate=31096/12

I-.type=0

NPER=?

pmt=5,546

以迄例子每月懸)一期，目前本金遢有503,088(pv=803,088-300,000)-

年利率3%(rate=3%/12)，每期繳款5,546(pmt=-5,546)，期末逮清(fv

=0)°

NPER(rate,pmt,pv,[fv],[type])

=NPER(3%/12,-5546,803088-300000)

=103

也就是103他1月彳爰(8年7彳固月)?即可遢清貸款。

回目^

幸員酬率函數(shù)RATE

RATE函數(shù)可以用來(lái)割?算甄酬率或利率-只要符現(xiàn)金流量之金額大小及

彝生的日寺^黠■以nper,pmt,pv,fv,type參數(shù)描述，就可以得到投資

輟酬率的值，格式如下：

RATE(nper,pmt,pv,[fv],[type],[guess])

意羲

nper期數(shù)

pmt每一期之金答直

pv期初罩肇金額

[fv]期末軍肇金額

Pmt的彝生疇黠

[type]0：期末值)

1：期初

猜測(cè)輟酬率可能落罷占

因懸RATE是用代入法求得答案，先假^一值I輟酬率

[guess]值10階，然彳爰代入公式，再看看是否符合。如

果者吳差在容言午SS圉內(nèi)，值就是答案，否即就就著增

加或減少幸艮酬率，看哪一他方向較卷接近，然彳爰往^

方向前暹。然彳菱一直反覆道遇程，直到找答案卷止。

只是若答案蹄覆^值太速，在20次反覆建算之彳發(fā)，

依然瓢法收斂到0.0000001以內(nèi)畤，就曾傅回金昔言吳值

#NUM!。道畤使用者就必須更改Guess參數(shù)，然彳爰重

新搜再一次。依撼我值1人^如果言十算的是月利率

的方舌，最好guess參數(shù)^定卷設(shè)，比較不畬出金昔。

基金年化甄酬率（一）

Peter於10年前以10離元黃了一彳固基金現(xiàn)在^基金浮值15離元?

道檬相富於多少的年甄酬率？

以迄例子每年禍一期-pv=-100,000nper=10-pmt=0-fv=150,000。

RATE(nper,pmt,pv,[fv],[type],[guess])

=RATE(10,0,-100000,150000)

=4.14%

基金年化甄酬率（二）

Peter於10年前以10K元黃了一彳固基金而且每月底定期定額2,000

元黃相同之基金，現(xiàn)在吉亥基金浮值65離元?道檬相富於多少的年

幸艮酬率?

fv=650,000

nper=10*12=120

type=0

RATE=?

?117I118I1191I2J

OTTr

以迄例子每月卷一期-pv=-100,000-pmt=-2,000，nper=120-fv

=650,000。

RATE(nper,pmt,pv,[fv],[type],[guess])

=RATE(120,-2000,-100000,650000,0,1%)*12

=9.4%

因懸期數(shù)是每月一期，RATE所傅回的是『月輟酬率』，所以年輟酬率

必鄉(xiāng)直再乘上12。

房屋貸款

Susan向金艮行貸款100離元?期限20年?每月本息it逮6,600元，冏

造貸款年利率是多少？

nper=12*20=240

pv=1,000,000fv=0

-itype=0

RATE=?

111,2,314151//236123712381239.240i

TIIIIIIIIII

pmt=6,600

以道例子每月懸一期?^共240期(nper=240)?期初拿到100M

(pv=1,000,000)?每期繳款6,600(pmt=-6,600)?期末遢清fv=0)。

RATE(nper,pmt,pv,[fv],[type],[guess])

=RATE(240,-6600,1000000,0,0,1%)*12

=5.0%

回目^

內(nèi)部輟酬率函數(shù)IRR

一J1投資案一定畬有現(xiàn)金流量筐生?只要列出道工直投資的現(xiàn)金流量表?

就可以^算出整颼投資的幸艮酬率。EXCEL提供了道相常好用的IRR函數(shù)，

只要輸入現(xiàn)金流量?就含It算出投資輟酬率。雖隹然RATE函數(shù)也可以If

算輟酬率?但是每一期金額都不等的現(xiàn)金流量，蒸法^算。但是IRR函

翳I?黑乎任何形式的現(xiàn)金流量都可以^算輟酬率。

差生現(xiàn)金流量

投資案

滉金流量

推定甄酬率

IRR函數(shù)的參數(shù)

IRR(value,[guess])

參數(shù)意羲

現(xiàn)金流量：

value必須至少包含一彳固正數(shù)和一低1負(fù)數(shù)，以言十算內(nèi)部幸艮酬

率

猜測(cè)輟酬率可能落黠

因卷IRR是用代入法求得答案，先假^一他甄酬率（子直

言殳值10%）,然彳爰代入公式，再看看是否符合。如果^

差在容^^圉內(nèi)，言亥值就是答案，否即就就著增加或

減少幸艮酬率，看哪一值1方向較卷接近，然彳發(fā)往^方向

[guess]前迤。然彳灸一直反覆道謾程，直到找答案卷止。只是

若答案蹄51^值太逋，在20次反覆建算之彳爰，依然

輾法收斂到0.0000001以內(nèi)畤，就畬傅回金昔言吳值

#NUM!o造畤使用者就必須更改Guess參矍攵，然彳發(fā)重

新搜尊一次。依獴我他人^如果言十算的是月利率

的言舌，最好guess參數(shù)器殳定懸1%,比較不曾出金昔。

定存股靶例

有一定存股?第一年拿出61,020元黃股票一張?第二年拿回3,580配

息第三年拿回4,686配息第四年拿回5,098配息第五年拿回91,916

配息及股票^回金額，整值1現(xiàn)金流量如下圄所示。如何符道檬的現(xiàn)金流

量余攵述幺合IRR函數(shù)知道呢？有雨槿方式，第一槿方式方法是用大括弧符

現(xiàn)金流量括起來(lái)?常中每一期的現(xiàn)金流量再用逗黑占分隔。

=IRR({-61020,3580,4686,5098,91916))

=15.8%

現(xiàn)金流量也可以放在儲(chǔ)存格貍，然彳爰符I6BI放入IRR的參數(shù)即可。如下

圄所示?儲(chǔ)存格B2:B6就是現(xiàn)金流量的輪圉。

B7