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

會計學(xué)1Excel在貨幣時間價值計算中的應(yīng)用詳解2023/1/182

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

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

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

第2頁/共60頁2023/1/184

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

一、函數(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代表先付年金，默認值為0第4頁/共60頁2023/1/186

㈠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頁/共60頁2023/1/187

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

語法參數(shù)：

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

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

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

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

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頁/共60頁2023/1/189

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頁/共60頁2023/1/1810

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

CUMIPMT()和CUMPRINC()函數(shù)中的參數(shù)PV必須為正，且返回的結(jié)果為負。第9頁/共60頁2023/1/1811

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

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

當(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頁/共60頁2023/1/1814第三節(jié)時間價值函數(shù)的示例說明一、時間價值函數(shù)的聯(lián)系Excel的這組時間價值函數(shù)之間存在一個內(nèi)在的關(guān)系式，稱為時間價值基本公式：

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

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

FV=P(1+r)n。

第3項是終值第14頁/共60頁2023/1/1816

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

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

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

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

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

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

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

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

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

第16頁/共60頁2023/1/1818

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

圖3－1第17頁/共60頁2023/1/1819

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

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

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頁/共60頁2023/1/1821滾動條、微調(diào)項等的設(shè)計：功能區(qū)有開發(fā)工具的，直接點擊開發(fā)工具——控件——插入功能區(qū)沒有開發(fā)工具的，文件——選項——自定義功能區(qū)——勾選開發(fā)工具——確定，在功能區(qū)就會出現(xiàn)開發(fā)工具。第20頁/共60頁2023/1/1822第二步：單擊插入函數(shù)向?qū)?，單擊財?wù)類函數(shù)FV()，其對話窗見圖3-4。

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

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

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

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

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

第三步：單擊[開發(fā)工具]功能模塊→[控件]→[插入]→[滾動條]項，在適當(dāng)?shù)奈恢脛澇鰸L動條按鈕，按圖3-7設(shè)置，通過[滾動條]控制期數(shù)圖3-7滾動調(diào)按鈕第25頁/共60頁2023/1/1827

第四步：按第三步的做法單擊[開發(fā)工具]功能模塊→[控件]→[插入]→[數(shù)值調(diào)節(jié)鈕]項，并畫出微調(diào)按鈕，按圖3-7設(shè)置，通過[微調(diào)]按鈕控制每期復(fù)利的次數(shù)。圖3-8微調(diào)按鈕第26頁/共60頁2023/1/1828

第五步：按第三步的操作，單擊開發(fā)工具功能區(qū)[控件]——[插入]——[組合框]項，劃出組合框按鈕，按圖3-9設(shè)置。組合框圖3-9第27頁/共60頁2023/1/1829

通過組合框按鈕，選擇年金的類型，通過邏輯函數(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。第28頁/共60頁2023/1/1830圖3-10第29頁/共60頁2023/1/1831

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

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

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

計算過程：

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

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

每期貸款余額＝上期余額－本期本金償還額題解過程及結(jié)果如圖3-11-1、2所示第32頁/共60頁2023/1/1834圖3-11-1第33頁/共60頁2023/1/1835圖3-11-2第34頁/共60頁2023/1/1836

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。第35頁/共60頁2023/1/1837圖3-12第36頁/共60頁2023/1/1838

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

語法：

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

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

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

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

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

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

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

求解過程見圖3-14。第39頁/共60頁2023/1/1841圖3-14第40頁/共60頁2023/1/1842第三節(jié)應(yīng)用示例一、教育儲蓄

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

第二步：在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ù)組生成鍵，計算年終金額。第44頁/共60頁2023/1/1846

結(jié)余金額為零的設(shè)計：單擊數(shù)據(jù)功能模塊[模擬分析]→[單變量求解]→在[目標單元格]鍵入E20→[目標值]為0→[可變單元格]選取B6，如圖3-17所示。最后單擊[確定]按鈕。圖3-17第45頁/共60頁2023/1/1847

二、養(yǎng)老金

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

第二步：選取C4:D9，選擇公式功能模塊→根據(jù)所選內(nèi)容創(chuàng)建→最左列

，見圖3-19，最后單擊[確定]按鈕。圖3-19第三步：在D7單元格中輸入公式”=PV(利率,支取養(yǎng)老金的年限,-每年支取金額,,1)”，計算滿足20年支出的賬戶總額。第49頁/共60頁2023/1/1851

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

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

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

第一步：設(shè)計如圖3-20所示的模型結(jié)構(gòu)。圖3-20第52頁/共60頁2023/1/1854

第三步：選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ù)組，計算每年償還額。第53頁/共60頁2023/1/1855

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

例3－12：

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