會計學1Excel在貨幣時間價值計算中的應用詳解2023/1/182

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

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

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

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

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

一、函數功能及其參數關于時間價值函數的參數說明：rate：每期利率

nper：年金處理中的總期數

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

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

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

type：年金類型。其值可以為0或1，0代表普通年金；1代表先付年金，默認值為0第4頁/共60頁2023/1/186

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

1、FV()函數——終值

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

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

功能：計算現金流量的現值。如果fv不為零，則表明最后一期期末有現金流量。第5頁/共60頁2023/1/187

3、RATE()函數——利率

語法參數：

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

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

4、NPER()函數——期數

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

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

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

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

功能：計算年金第per期的現金流量中的利息部分。第7頁/共60頁2023/1/189

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

語法參數：CUMPRINC(rate,nper,pv,start_period,end_period,type)功能：計算年金每期現金流量累加結果中的本金部分。9、CUMIPMT()函數——累計利息語法參數：CUMIPMT(rate,nper,pv,start_period,end_period,type)功能：計算年金每期現金流量累加結果中的 利息部分。第8頁/共60頁2023/1/1810

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

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

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

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

當對應參數完全相同時，后4個函數有如下關系：PPMT()函數、IPMT()函數上面兩個函數是分解PMT()函數得出的，即IPMT()＋PPMT()＝PMT()CUMPRINC()函數、CUMIPMT()函數上面兩個函數合起來計算在多期里的現金流量。CUMIPMT()等于IPMT()函數的累加；CUMPRINC()等于PPMT()函數的累加；CUMIPMT()＋CUMPRINC()＝n×PMT()注：n為時間價值計算的期數第12頁/共60頁2023/1/1814第三節時間價值函數的示例說明一、時間價值函數的聯系Excel的這組時間價值函數之間存在一個內在的關系式，稱為時間價值基本公式：

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

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

FV=P(1+r)n。

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

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

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

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

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

二、終值和現值的計算

㈠一次性收付的終值和現值的計算

1、終值函數FV()

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

功能：在已知期數、利率、年金和現值的條件下，返回年金終值數額。

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

例3-1：設計不同利率、期數現值的終值。設計過程：第一步：設計如圖3－1終值計算模型；

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

第二步：單擊插入函數向導，單擊財務類函數FV()，其對話窗見圖3-2。

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

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

功能：在已知期數、利率、每期付款額及終值的條件下，返回現值數額。

例3－2:設計不同利率、期數、終值的現值。

設計過程：第一步：設計如圖3－3終值計算模型；圖3-3第19頁/共60頁2023/1/1821滾動條、微調項等的設計：功能區有開發工具的，直接點擊開發工具——控件——插入功能區沒有開發工具的，文件——選項——自定義功能區——勾選開發工具——確定，在功能區就會出現開發工具。第20頁/共60頁2023/1/1822第二步：單擊插入函數向導，單擊財務類函數FV()，其對話窗見圖3-4。

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

㈡年金的現值、終值計算

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

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

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

第三步：單擊[開發工具]功能模塊→[控件]→[插入]→[滾動條]項，在適當的位置劃出滾動條按鈕，按圖3-7設置，通過[滾動條]控制期數圖3-7滾動調按鈕第25頁/共60頁2023/1/1827

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

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

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

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

該模型的設計過程同例3-3。區別在于本例時間價值函數的類型是PV()。見圖3-10。第28頁/共60頁2023/1/1830圖3-10第29頁/共60頁2023/1/1831

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

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

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

計算過程：

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

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

每期貸款余額＝上期余額－本期本金償還額題解過程及結果如圖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、年金中的利息函數IPMT()

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

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

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

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

PMT()=PPMT()+IPMT()

例3－6：

資料同例3－5。求解過程見圖3-11。第35頁/共60頁2023/1/1837圖3-12第36頁/共60頁2023/1/1838

㈣利率函數RATE()

語法：

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

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

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

㈤期數函數NPER（）

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

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

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

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

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

第二步：在B8:B13單元格區域中輸入公式”=B6”，按數組生成鍵，產生數組。

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

第五步：在D8:D13單元格區域中輸入公式”=C8:C13*B4”，按數組生成鍵，計算當年利息第六步：在E8:E13單元格區域中輸入公式”=C8:C13+D8:D13”，按數組生成鍵，計算年終金額。第44頁/共60頁2023/1/1846

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

二、養老金

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

第二步：選取C4:D9，選擇公式功能模塊→根據所選內容創建→最左列

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

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

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

第一問的設計過程如下：

第一步：設計如圖3-20所示的模型結構。圖3-20第52頁/共60頁2023/1/1854

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

第四步：選C8單元格，輸入公式”=PMT(利率,償還期限,畢業時的終值)”，計算畢業后每年還款額。第五步：選C11:C20，輸入公式”=PPMT(利率,B11:B20,償還期限,畢業時的終值)”,生成數組，計算每年償還的本金數額。第六步：選D11:D20，輸入公式”=IPMT(利率,B11:B201,償還期限,畢業時的終值)”,生成數組，計算每年償還的利息部分。第七步：選E11:E20，輸入公式”=F10:F19+C11:C20”,生成數組，計算每年償還額。第53頁/共60頁2023/1/1855

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

例3－12：

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