Excel在貨幣時間價值計算中的應(yīng)用

會計學(xué)1Excel在貨幣時間價值計算中的應(yīng)用2023/1/18杜茂寶2

第一節(jié)貨幣時間價值一、貨幣的時間價值貨幣的時間價值有兩種表示方式：一種是絕對方式，即利息，它是一定量貨幣——稱為本金，在一定時間內(nèi)產(chǎn)生增值的絕對數(shù)額；另一種是相對方式，即利率，它是用百分比表示的貨幣隨時間推移所產(chǎn)生增值與本金之間的比率。按照計算基數(shù)的不同，利息的計算有以下兩種形式：單利和復(fù)利。第1頁/共59頁2023/1/18杜茂寶3

㈠單利每次計算利息時，都以本金作為計算基數(shù)。

I=P·r·n㈡復(fù)利每次計算利息時，都以上期期末的本利和作為計算基數(shù)。這時不僅要計算本金的利息，還要計算利息的利息，俗稱“利滾利”。

I=P·[(1+r)n?1]第2頁/共59頁2023/1/18杜茂寶4

二、貨幣時間價值的計算㈠時間價值計算公式第二節(jié)貨幣時間價值函數(shù)在Excel中，有一組用于時間價值計算的函數(shù)，這組函數(shù)之間是相互關(guān)聯(lián)的，并且可以互為參數(shù)。他們通過一個公式聯(lián)系起來，組成有內(nèi)在聯(lián)系的函數(shù)群。理解它們之間的這種內(nèi)在聯(lián)系，對于正確使用這些函數(shù)有很大幫助。第3頁/共59頁2023/1/18杜茂寶5

一、函數(shù)功能及其參數(shù)關(guān)于時間價值函數(shù)的參數(shù)說明：rate：每期利率

nper：年金處理中的總期數(shù)

pmt：每期固定支付或收入的數(shù)額，即年金

pv：初始值，為一選擇性參數(shù)。如果此參數(shù)省略，則假設(shè)其值為0

fv：終值，為一選擇性參數(shù)。如果此參數(shù)省略，則假設(shè)其值為0

type：年金類型。其值可以為0或1，0代表普通年金；1代表先付年，默認(rèn)值為0第4頁/共59頁2023/1/18杜茂寶6

㈠Excel中的常用時間價值函數(shù)的語法、參數(shù)和功能。

1、FV()函數(shù)——終值

語法參數(shù)：FV(rate,nper,pmt,pv,type)

功能：計算現(xiàn)金流量的終值。如果pv不為零，則表明有初始現(xiàn)金流量。2、PV()函數(shù)——現(xiàn)值語法參數(shù)：PV(rate,nper,pmt,fv,type)

功能：計算現(xiàn)金流量的現(xiàn)值。如果fv不為零，則表明最后一期期末有現(xiàn)金流量。第5頁/共59頁2023/1/18杜茂寶7

3、RATE()函數(shù)——利率

語法參數(shù)：

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

功能：計算利率。如果計算結(jié)果不收斂，可以用guess參數(shù)給定估計值重新計算。guess默認(rèn)值為10%。

4、NPER()函數(shù)——期數(shù)

語法參數(shù)：NPER(rate,pmt,pv,fv,type)功能：計算現(xiàn)金流量期數(shù)第6頁/共59頁2023/1/18杜茂寶8

5、PMT()函數(shù)——年金語法參數(shù)：PMT(rate,nper,pv,fv,type)

功能：計算年金每期現(xiàn)金流量。6、PPMT()函數(shù)——本金語法參數(shù)：PPMT(rate,per,nper,pv,fv,type)

功能:計算年金第per的現(xiàn)金流量中本金部分7、IPMT()函數(shù)——利息語法參數(shù)：IPMT(rate,per,nper,pv,fv,type)

功能：計算年金第per期的現(xiàn)金流量中的利息部分。第7頁/共59頁2023/1/18杜茂寶9

8、CUMPRINC()函數(shù)——累計本金

語法參數(shù)：CUMPRINC(rate,nper,pv,start_period,end_period,type)功能：計算年金每期現(xiàn)金流量累加結(jié)果中的本金部分。9、CUMIPMT()函數(shù)——累計利息語法參數(shù)：CUMIPMT(rate,nper,pv,start_period,end_period,type)功能：計算年金每期現(xiàn)金流量累加結(jié)果中的 利息部分。第8頁/共59頁2023/1/18杜茂寶10

㈡使用這些各函數(shù)時要注意其參數(shù)1、pmt參數(shù)作為每期發(fā)生的現(xiàn)金流量，在整個年金期間其值保持不變；2、type=0或省略表示各期現(xiàn)金流量發(fā)生在期末，即普通年金；type=1表示各期現(xiàn)金流量發(fā)生在期初，即預(yù)付年金；應(yīng)確認(rèn)所指定的rate和nper單位的一致性。在所有參數(shù)中，支出的款項表示為負(fù)數(shù)，收入的款項表示為整數(shù)。

CUMIPMT()和CUMPRINC()函數(shù)中的參數(shù)PV必須為正，且返回的結(jié)果為負(fù)。第9頁/共59頁2023/1/18杜茂寶11

二、函數(shù)之間的關(guān)系如果用時間線來對現(xiàn)金流量進行分析，則上述9個函數(shù)可以歸納到一幅圖上，從而表示出個函數(shù)之間的相互關(guān)系。見下圖。PVFVNPERRATEPMTIPMTPPMTCUMPRINCCUMIPMT終值現(xiàn)值利率期數(shù)現(xiàn)金流第10頁/共59頁2023/1/18杜茂寶12

從圖中可以看出：5個最基本的函數(shù)：PV()函數(shù)—現(xiàn)值FV()函數(shù)—終值RATE()函數(shù)—利率PMT()函數(shù)—每期現(xiàn)金流量NPER()函數(shù)—期數(shù)，它們分布在時間線上第11頁/共59頁2023/1/18杜茂寶13

當(dāng)對應(yīng)參數(shù)完全相同時，后4個函數(shù)有如下關(guān)系：PPMT()函數(shù)、IPMT()函數(shù)上面兩個函數(shù)是分解PMT()函數(shù)得出的，即IPMT()＋PPMT()＝PMT()CUMPRINC()函數(shù)、CUMIPMT()函數(shù)上面兩個函數(shù)合起來計算在多期里的現(xiàn)金流量。CUMIPMT()等于IPMT()函數(shù)的累加；CUMPRINC()等于PPMT()函數(shù)的累加；CUMIPMT()＋CUMPRINC()＝n×PMT()注：n為時間價值計算的期數(shù)第12頁/共59頁2023/1/18杜茂寶14第三節(jié)時間價值函數(shù)的示例說明一、時間價值函數(shù)的聯(lián)系Excel的這組時間價值函數(shù)之間存在一個內(nèi)在的關(guān)系式，稱為時間價值基本公式：

真正理解著這公式，才能掌握有關(guān)函數(shù)的具體用法，否則就會造成理解上的困難和應(yīng)用上的迷惑。對上面的公式進行分析可以看出，該式左側(cè)共包含3項：第13頁/共59頁2023/1/18杜茂寶15

第1項符合普通年金或預(yù)付年金終值的計算公式：普通年金終值公式：預(yù)付年金終值公式：第2項就是一般的復(fù)利終值計算公式：

FV=P(1+r)n。

第3項是終值第14頁/共59頁2023/1/18杜茂寶16

上式的左側(cè)實際上分別是現(xiàn)值的終值和年金終值之和，他們與第三項相加為零。于是，形成了現(xiàn)值、各期現(xiàn)金流量與終值之間的一體化關(guān)系。這也就是為什么現(xiàn)值、終值符號相反的原因。根據(jù)這三項的關(guān)系，在應(yīng)用中共有三種情況：

fv＝0：可以在每期現(xiàn)金流量(pmt)與現(xiàn)值(pv)之間相互計算，但兩者正負(fù)相反；

pv=0：可以在每期現(xiàn)金流量(pmt)與終值(fv)之間相互計算，但兩者正負(fù)相反；

pmt=0：可以在一筆固定現(xiàn)金額的現(xiàn)值(pv)與終值(fv）之間計算，但兩者正負(fù)相反。第15頁/共59頁2023/1/18杜茂寶17

二、終值和現(xiàn)值的計算

㈠一次性收付的終值和現(xiàn)值的計算

1、終值函數(shù)FV()

注意：在參數(shù)使用上，若為付出金額，則以“負(fù)”數(shù)表示；若為收入則以“正”表示語法：FV（rate,nper,pmt,pv,type)

功能：在已知期數(shù)、利率、年金和現(xiàn)值的條件下，返回年金終值數(shù)額。

第16頁/共59頁2023/1/18杜茂寶18

例3-1：設(shè)計不同利率、期數(shù)現(xiàn)值的終值。設(shè)計過程：第一步：設(shè)計如圖3－1終值計算模型；

圖3－1第17頁/共59頁2023/1/18杜茂寶19

第二步：單擊插入函數(shù)向?qū)В瑔螕糌攧?wù)類函數(shù)FV()，其對話窗見圖3-2。

第三步：按函數(shù)參數(shù)的要求，填入相應(yīng)參數(shù)值后，單擊［確定］按鈕。圖3-2第18頁/共59頁2023/1/18杜茂寶20

2、現(xiàn)值的計算語法：PV(rate,nper,pmt,fv,type)

功能：在已知期數(shù)、利率、每期付款額及終值的條件下，返回現(xiàn)值數(shù)額。

例3－2:設(shè)計不同利率、期數(shù)、終值的現(xiàn)值。

設(shè)計過程：第一步：設(shè)計如圖3－3終值計算模型；圖3-3第19頁/共59頁2023/1/18杜茂寶21第二步：單擊插入函數(shù)向?qū)?，單擊財?wù)類函數(shù)FV()，其對話窗見圖3-4。

第三步：按函數(shù)參數(shù)的要求，填入相應(yīng)參數(shù)值后，單擊［確定］按鈕圖3-4第20頁/共59頁2023/1/18杜茂寶22

㈡年金的現(xiàn)值、終值計算

1、年金（普通、先付）的終值計算

例3－3：趙女士今年30歲，計劃為自己設(shè)立一個風(fēng)險保障賬戶，從今年開始，每年年末（初）往該賬戶存入10000元，設(shè)年利率為6％，計算到趙女士60歲時，這筆保障金為多少？建立計算模型具體過程為：

第一步：如圖3-5所示，建立模型結(jié)構(gòu)第21頁/共59頁2023/1/18杜茂寶23圖3-5第二步：選取H4單元格，單擊[插入函數(shù)]按鈕，在“邏輯”類別中選擇函數(shù)IF()，并按要求輸入函數(shù)參數(shù)，見圖3-6。第22頁/共59頁2023/1/18杜茂寶24圖3-6第23頁/共59頁2023/1/18杜茂寶25

第三步：單擊[視圖]菜單→[工具]→[窗體]→[滾動條]項，在適當(dāng)?shù)奈恢脛澇鰸L動條按鈕，按圖3-7設(shè)置，通過[滾動條]控制期數(shù)圖3-7滾動調(diào)按鈕第24頁/共59頁2023/1/18杜茂寶26第四步：按第三步的做法單擊[窗體]中的[微調(diào)]項，并畫出微調(diào)按鈕，按圖3-7設(shè)置，通過[微調(diào)]按鈕控制每期復(fù)利的次數(shù)。圖3-8微調(diào)按鈕第25頁/共59頁2023/1/18杜茂寶27

第五步：按第三步的操作，單擊[組合框]項，劃出組合框按鈕，按圖3-9設(shè)置。組合框圖3-9第26頁/共59頁2023/1/18杜茂寶28

通過組合框按鈕，選擇年金的類型，通過邏輯函數(shù)IF()判斷計算相應(yīng)的終值，計算結(jié)果見圖3-5。2、年金的現(xiàn)值計算

例3-4：某公司購買一臺設(shè)備，預(yù)計可使用10年，每年可節(jié)約費用50000元。假設(shè)貼現(xiàn)率為10%，按普通年金和先付年金分別計算所節(jié)約費用的現(xiàn)值。

該模型的設(shè)計過程同例3-3。區(qū)別在于本例時間價值函數(shù)的類型是PV()。見圖3-10。第27頁/共59頁2023/1/18杜茂寶29圖3-10第28頁/共59頁2023/1/18杜茂寶30

㈢年金、年金中的本金、年金中的利息1、年金函數(shù)PMT()語法：PMT(rate,per,nper,pv,fv,type)

功能：在已知期數(shù)、利率、現(xiàn)值或終值的條件下，返回年金，即投資（或貸款）的每期付款額（包括本金和利息）。第29頁/共59頁2023/1/18杜茂寶31

例3－5：假定A公司貸款1000元，必須在未來3年每年年底償還相同的金額，而銀行按貸款余額的6％收取利息。請你編制如下的還本付息表：年度支付額利息本金償還額貸款余額123合計還款付息表第30頁/共59頁2023/1/18杜茂寶32

計算過程：

每期利息＝每期貸款余額×6％；

每期本金償還額＝每期支付額－每期利息

每期貸款余額＝上期余額－本期本金償還額題解過程及結(jié)果如圖3-11-1、2所示第31頁/共59頁2023/1/18杜茂寶33圖3-11-1第32頁/共59頁2023/1/18杜茂寶34圖3-11-2第33頁/共59頁2023/1/18杜茂寶35

2、年金中的利息函數(shù)IPMT()

語法：IPMT(rate,per,nper,pv,fv,type)

功能：在已知期數(shù)、利率及現(xiàn)值的條件下，返回投資或貸款的每期付款額中所含的利息。3、年金中的本金函數(shù)PPMT()

語法：PPMT(rate,per,nper,pv,fv,type)

功能：在已知期數(shù)、利率及現(xiàn)值的條件下，返回投資或貸款的每期付款額中所含的本金。以上三個函數(shù)的關(guān)系：

PMT()=PPMT()+IPMT()

例3－6：

資料同例3－5。求解過程見圖3-11。第34頁/共59頁2023/1/18杜茂寶36圖3-12第35頁/共59頁2023/1/18杜茂寶37

㈣利率函數(shù)RATE()

語法：

RATE(rate,pmt,pv,fv,type,guess)

功能：在已知期數(shù)、每期付款額及現(xiàn)值的條件下，返回年金的每期利率。參數(shù)：guess是對利率的猜測。如果guess省略，將假設(shè)它是10％

例3－7：某企業(yè)租用一設(shè)備，租金36000元，若租賃期5年，每年末支付租金9016元，求貼現(xiàn)利率為多少？求解過程見圖3-12。第36頁/共59頁2023/1/18杜茂寶38圖3-13第37頁/共59頁2023/1/18杜茂寶39

㈤期數(shù)函數(shù)NPER（）

語法：NPER(rate,pmt,pv,fv,type)

功能：返回每期付款金額即利率固定的某項投資或貸款的期數(shù)。

例3-8：某企業(yè)租用一設(shè)備，租金36000元，年利率為8％，每年末支付租金。若每年支付9016元，需要多少年支付完租金。

求解過程見圖3-14。第38頁/共59頁2023/1/18杜茂寶40圖3-14第39頁/共59頁2023/1/18杜茂寶41第三節(jié)應(yīng)用示例一、教育儲蓄

例3－9：父母準(zhǔn)備為其子女將來的求學(xué)進行儲蓄。他們計劃從孩子的12歲起到他17歲，每年年初存入一定金額，從孩子18歲上大學(xué)開始到21歲，每年年初支取一定金額作為學(xué)費。首先，假定在孩子12歲到17歲每年初存款6000元，利率為3％。然后，從18歲開始每年年初至取10000元，到21歲的年終。問到21歲年終時該存款的帳面余額為多少？如果其他條件不變，要求計算前6年每年初存款為多少時，可以到21歲年終收支平衡，即結(jié)余金額為零。第40頁/共59頁2023/1/18杜茂寶42第一步：建立如圖3-14所示的模型結(jié)構(gòu)圖3-15第41頁/共59頁2023/1/18杜茂寶43結(jié)構(gòu)中公式如圖3-16所示圖3-16第42頁/共59頁2023/1/18杜茂寶44

第二步：在B8:B13單元格區(qū)域中輸入公式”=B6”，按數(shù)組生成鍵，產(chǎn)生數(shù)組。

第三步：選取C8單元格，輸入公式”=B8”，計算第一年的年初金額。第四步：在C9:C13單元格區(qū)域中輸入公式”=B9:B13+E8:E12”，按數(shù)組生成鍵，計算后5年年初金額。

第五步：在D8:D13單元格區(qū)域中輸入公式”

=C8:C13*B4”，按數(shù)組生成鍵，計算當(dāng)年利息第六步：在E8:E13單元格區(qū)域中輸入公式”

=C8:C13+D8:D13”，按數(shù)組生成鍵，計算年終金額。第43頁/共59頁2023/1/18杜茂寶45

結(jié)余金額為零的設(shè)計：單擊[工具]→[單變量求解]→在[目標(biāo)單元格]鍵入E20→[目標(biāo)值]為0→[可變單元格]選取B6，如圖3-17所示。最后單擊[確定]按鈕。圖3-17第44頁/共59頁2023/1/18杜茂寶46

二、養(yǎng)老金

例3－10:⑴如果某人從36歲開始為自己儲備養(yǎng)老金到65歲退休，希望從66歲到85歲每年年初只取100000元，利率為8％，要求計算他在前面的儲蓄中每年年初應(yīng)等額存入多少金額？⑵如果在85歲時扣除全部以支取的金額外仍然有500000元，則在儲蓄中每年年初應(yīng)等額存入多少？⑶在35歲開始存款時如果將已有的50000元作為本金存入，則在儲蓄過程中每年年初應(yīng)等額存入多少？第45頁/共59頁2023/1/18杜茂寶47第一步：建立如圖3-18-1、2所示的結(jié)構(gòu)圖。圖3-18-1第46頁/共59頁2023/1/18杜茂寶48養(yǎng)老金公式圖3-18-2第47頁/共59頁2023/1/18杜茂寶49

第二步：選取C4:D9，單擊[插入]→[名稱]→[制定]→[最左列]，見圖3-19，最后單擊[確定]按鈕。圖3-19第三步：在D7單元格中輸入公式”=PV(利率,支取養(yǎng)老金的年限,-每年支取金額,,1)”，計算滿足20年支出的賬戶總額。第48頁/共59頁2023/1/18杜茂寶50

第四步：在D9單元格中輸入公式”=PMT(利率,存入養(yǎng)老金的年限,,-養(yǎng)老金總額,1)”，計算滿足未來20年每年支取10000元的需要。第二、三問的操作同上述第二至四步。第49頁/共59頁2023/1/18杜茂寶51

三、助學(xué)貸款例3－11：⑴一名學(xué)生在入學(xué)時，從銀行申請了國家助學(xué)貸款40000元。按照貸款合同，該生從畢業(yè)起10年內(nèi)分期償還這筆貸款，每年年終償還固定金額，假設(shè)貸款利率均為5％，要求計算該生每年年末須償還的貸款金額，以及各年償還金額中本金和利息各為多少。⑵該項貸款還規(guī)定，借款人畢業(yè)后可以自己選擇每年償還金額，要求是還款期限必須保證在10年以內(nèi)且每年償還進而固定。假如該生擬每年初償還10000元，要求計算還款期。第50頁/共59頁2023/1/18杜茂寶52

第一問的設(shè)計過程如下：

第一步：設(shè)計如圖3-20所示的模型結(jié)構(gòu)。圖3-20第51頁/共59頁2023/1/18杜茂寶53

第三步：選C5單元格，輸入公式”C5=C3”，計算畢業(yè)時貸款額。

第四步：選C8單元格，輸入公式”

=PMT(利率,償還期限,畢業(yè)時的終值)”，計算畢業(yè)后每年還款額。第五步：選C11:C20，輸入公式”=PPMT(利率,B11:B20,償還期限,畢業(yè)時的終值)”,生成數(shù)組，計算每年償還的本金數(shù)額。第六步：選D11:D20，輸入公式”=IPMT(利率,B11:B201,償還期限,畢業(yè)時的終值)”,生成數(shù)組，計算每年償還的利息部分。第七步：選E11:E20，輸入公式”

=F10:F19+C11:C20”,生成數(shù)組，計算每年償還額。第52頁/共59頁2023/1/18杜茂寶54

第二問設(shè)計過程：按圖3-21的結(jié)構(gòu)求還款年限，采用NPER()函數(shù)圖3-21第53頁/共59頁2023/1/18杜茂寶55四、住房按揭

例3－12：

西安市民趙女士購買了一套總價人民幣60萬元的新房，首付20萬元，貸款總額40萬元，其中公積金貸款10萬元，期限10