Excel在財務(wù)中的應(yīng)用第04章常用財務(wù)函數(shù)簡介

1、Excel在財務(wù)中的應(yīng)用第4章 常用財務(wù)函數(shù)簡介本章學(xué)習(xí)目標學(xué)習(xí)目標通過本章的學(xué)習(xí)，讀者應(yīng)該學(xué)會投資計算函數(shù)、折舊計算函數(shù)和償還率計算函數(shù)等常用財務(wù)函數(shù)的使用方法，為以后學(xué)習(xí)財務(wù)決策等其他知識打下基礎(chǔ)。目錄投資計算函數(shù)折舊計算函數(shù)償還率計算函數(shù)4.14.24.3投資計算函數(shù)4.14.1 投資計算函數(shù)投資計算函數(shù)年金函數(shù)PMT（）本金函數(shù)PPMT（）利息函數(shù)IPMT（）現(xiàn)值函數(shù)PV(） 終值函數(shù)FV（）凈現(xiàn)值函數(shù)NPV（）期數(shù)函數(shù)NPER（）4.1.1終值函數(shù)FV（）含義終值(Future Value)，又稱將來值或本利和，是指現(xiàn)在一定量的資金在未來某一時點上的價值。終值函數(shù)FV（）的功能是基于

2、固定利率及等額分期付款方式，返回某項投資的未來值。說明公式：=FV(Rate, Nper, Pmt, Pv, Type)（1） Rate：各期利率；（2） Nper：總投資期；（3） Pmt：各期應(yīng)支付的金額；（4） Pv：從該投資開始計算時已經(jīng)入賬的款項，即本金，若省略Pv，則假設(shè)值為0；（5）Type：數(shù)字0或1，用以指定各期的付款時間是在期初還是期末，0表示期末，1表示期初，若省略Type，則假設(shè)值為0。4.1.1終值函數(shù)FV（）應(yīng)用 【例4-1】某公司向銀行借款1000萬元，貸款期限5年，年利率為5%，復(fù)利計息，該公司5年后應(yīng)向銀行償還的本利和是多少？【例4-2】某父母在銀行存入一筆教

3、育基金，期初將50000元存入銀行，以后每年年末存入5000元，假設(shè)按照年利率3%復(fù)利計息，那么10年后該筆教育基金的本利和是多少？4.1.2現(xiàn)值函數(shù) PV（）含義現(xiàn)值（ Present Value）是如今和將來(或過去)的一筆支付或支付流在當(dāng)今的價值。或理解為: 成本或收益的價值以今天的現(xiàn)金來計量時，稱為現(xiàn)值。現(xiàn)值函數(shù)PV（）的功能是返回某項投資的現(xiàn)值。說明公式：=PV（Rate, Nper, Pmt, Fv, Type）（1）Rate：各期利率；（2）Nper：總投資期；（3）Pmt：各期應(yīng)支付的金額；（4）Fv：最后一次支付后希望得到的現(xiàn)金余額，即終值，若省略Fv，則假設(shè)值為0；（5）T

4、ype：數(shù)字0或1，用以指定各期的付款時間是在期初還是期末，0表示期末，1表示期初，若省略Type，則假設(shè)值為0。4.1.2現(xiàn)值函數(shù) PV（）應(yīng)用 【例4-3】張某計劃5年后購買一輛價值20萬元的汽車，假定銀行存款年利率為5%，按年復(fù)利計息，張某現(xiàn)在應(yīng)至少一次性存入銀行多少錢？【例4-4】某人想通過貸款進行一項投資，其能承受的每月還款額為5000元，貸款年利率為4.9%，貸款期限為10年，計算其最多能承受的貸款的總額。4.1.3終值函數(shù)FV（）和現(xiàn)值函數(shù)PV（）在年金中的應(yīng)用年金含義年金(Annuity)是指一定時期內(nèi)每次等額收付的系列款項，通常用A來表示。年金的形式包括保險費、養(yǎng)老金、直線法

5、下計提的折舊、租金、等額分期收款、等額分期付款等，年金具有等額性和連續(xù)性特點，但年金的間隔期不一定是一年。年金分類永續(xù)年金又稱先付年金、即付年金、期初年金，指在每期期初支付的年金又稱延期年金，是指在最初若干期沒有收付款項的情況下，后面若干期等額的系列收付款項，它是普通年金的特殊形式無限期定額收付的年金，稱為永續(xù)年金永續(xù)年金沒有終止的時間，也就沒有終值遞延年金預(yù)付年金普通年金指每期末收付等額款項的年金，也稱后付年金普通年金終值理論詳解含義普通年金終值是指最后一次支付時的本利和，它是每次支付的復(fù)利終值之和。按復(fù)利換算到最后一期期末的終值，然后加總，就是該年金終值?？梢允褂肍V（）函數(shù)計算，此時參數(shù)

6、PV=0，參數(shù)Type=0。應(yīng)用說明舉例【例4-5】李先生每年年末存入銀行10000元作為女兒的教育基金，若銀行存款利率為5%，按年復(fù)利計息，那么15年后該教育基金的本利和是多少？普通年金現(xiàn)值理論詳解含義普通年金現(xiàn)值是一定時期內(nèi)按相同時間間隔在每期期末收付的相等金額折算到第一期初的現(xiàn)值之和。即現(xiàn)金流量發(fā)生在每期期末，現(xiàn)值按發(fā)生第一筆現(xiàn)金流量那一期的期初計算。可以使用PV（）函數(shù)計算，此時FV=0，Type=0應(yīng)用說明舉例【例4-6】某公司計劃今后10年中每年年末從銀行留取10萬元用于人才培訓(xùn)，如果銀行存款年利率為5%，現(xiàn)在應(yīng)一次性存入銀行多少錢？先付年金終值理論詳解含義先付年金終值是指某一時期

7、內(nèi)各期期初連續(xù)發(fā)生的一系列相等數(shù)額的收付款的復(fù)利終值之和，可以使用FV（）函數(shù)計算，此時參數(shù)PV=0，參數(shù)Type=1。應(yīng)用說明舉例【例4-7】李先生每年年初存入銀行10000元作為女兒的教育基金，若銀行存款利率為5%，按年復(fù)利計息，那么15年后該教育基金的本利和是多少？先付年金現(xiàn)值理論詳解含義先付年金現(xiàn)值是一定時期內(nèi)按相同時間間隔在每期期初收付的相等金額折算到第一期初的現(xiàn)值之和。即現(xiàn)金流量發(fā)生在每期期初，現(xiàn)值按發(fā)生第一筆現(xiàn)金流量那一期的期初計算?？梢允褂肞V（）函數(shù)計算，此時FV=0，Type=1應(yīng)用說明舉例【例4-8】某公司計劃今后10年中每年年初從銀行留取10萬元用于人才培訓(xùn)，如果銀行存

8、款年利率為5%，現(xiàn)在應(yīng)一次性存入銀行多少錢？遞延年金終值理論詳解含義遞延年金終值是指前M期沒有年金，M+N時期內(nèi)各期期末連續(xù)發(fā)生的一系列相等數(shù)額的收付款的復(fù)利終值之和，即假設(shè)前M期沒有年金，從M+1到M+N期有年金，共計N期普通年金，則M+N期末的終值可以按照N期的普通年金終值公式計算，可以使用FV（）函數(shù)計算。應(yīng)用說明舉例【例4-9】李先生3年后每年年末存入銀行10000元作為女兒的教育基金，若銀行存款利率為5%，按年復(fù)利計息，那么存款15年后該教育基金的本利和是多少？遞延年金現(xiàn)值理論詳解含義第一種方法：把遞延年金視為N期普通年金，求出遞延期末的現(xiàn)值，然后再將現(xiàn)值調(diào)整到第一期期初（遞延M期）

9、。第二種方法：假設(shè)遞延期也進行收付，先求出（M+N）期的年金現(xiàn)值，然后扣除實際并未收付的遞延期（M）的年金現(xiàn)值，即可得出最終結(jié)果。采用這兩種方法進行計算，均可使用PV（）函數(shù)。應(yīng)用說明舉例【例4-10】某公司計劃在5年后的連續(xù)10年中每年年末從銀行留取10萬元用于人才培訓(xùn)，如果銀行存款年利率為5%，現(xiàn)在應(yīng)一次性存入銀行多少錢？永續(xù)年金理論詳解含義無限期定額收付的年金，稱為永續(xù)年金（Perpetual Annuities），永續(xù)年金沒有終止的時間，也就沒有終值。永續(xù)年金的現(xiàn)值可以通過普通年金推導(dǎo)，即永續(xù)年金現(xiàn)值=年金/年利率，故在計算永續(xù)年金時，只需要在某一單元格直接輸入計算公式即可。應(yīng)用說明舉

10、例【例4-11】某企業(yè)計劃設(shè)立一項教育基金，用于在職職工的培訓(xùn)，預(yù)計每年的培訓(xùn)費用支出50000元，假設(shè)年利率為5%，計算該企業(yè)現(xiàn)在應(yīng)一次性存入銀行多少錢？ 4.1.4 凈現(xiàn)值函數(shù)NPV（）含義及說明凈現(xiàn)值函數(shù)NPV（）是指通過使用貼現(xiàn)率以及一系列未來支出（負值）和收入（正值），返回一項投資的凈現(xiàn)值。凈現(xiàn)值函數(shù)NPV（）公式為：=NPV（Rate, Value1, Value2, ）各參數(shù)的含義：（1）Rate：各期折現(xiàn)率，是固定值；（2）Value1，Value2，：代表1到29筆支出及收入的參數(shù)值，支出為負數(shù)，收入為正數(shù)。說明：Value1，Value2，所屬各期間的長度必須相等，且支付及

11、收入的時間都發(fā)生在期末。4.1.4 凈現(xiàn)值函數(shù)NPV（）應(yīng)用 【例4-12】某公司有一投資項目，假定第一年年末投資200000元，在投資后的5年的預(yù)計收益分別為78000元、100000元、159000元、182000元、18000元，假設(shè)年利率為5%，求該投資項目的凈現(xiàn)值。 4.1.5 年金函數(shù)PMT（）含義及說明PMT函數(shù)即年金函數(shù)，該函數(shù)是基于固定利率及等額分期付款方式，返回投資或貸款的每期付款額。年金函數(shù)PMT（）的公式為：= PMT(Rate,Nper, Pv, Fv, Type)各參數(shù)的含義：（1）Rate：各期利率（期利率）; （2）Nper：該項投資或貸款的付款總期數(shù)(總年數(shù)或

12、還租期數(shù)）； （3）Pv：現(xiàn)值，或一系列未來付款的當(dāng)前值的累積和，也稱為本金； （4）Fv：為未來值（余值），或在最后一次付款后希望得到的現(xiàn)金余額，如果省略Fv，則假設(shè)其值為0，也就是一筆貸款的未來值為0； （5）Type：數(shù)字0或1，用以指定各期的付款時間是在期初還是期末，0表示期末，1表示期初，若省略Type，則假設(shè)值為0。4.1.5 年金函數(shù)PMT（）應(yīng)用 【例4-13】張先生向中國建設(shè)銀行貸款50萬元，貸款期限為10年，貸款利率為6%，現(xiàn)要求計算每年和每月償還金額。 4.1.6 本金函數(shù)PPMT（）含義及說明本金函數(shù)PPMT（）指定根據(jù)定期定額支付和固定利率計算的年金在給定期間內(nèi)的本金

13、償付額。本金函數(shù)PPMT（）公式為：=PPMT（Rate, Per, Nper, Pv, Fv, Type）其中參數(shù)Per是用于計算其本金數(shù)額的期數(shù)，它必須介于1和付款總次數(shù)Nper之間，其他參數(shù)的含義與PMT（）函數(shù)的參數(shù)含義相同。4.1.6 本金函數(shù)PPMT（）應(yīng)用 【例4-14】承接【例4-13】資料，計算前10個月每月還款的本金。4.1.7 利息函數(shù)IPMT（）含義及說明利息函數(shù)IPMT（）指定根據(jù)定期定額支付和固定利率計算的年金在給定期間內(nèi)的利息償付額。利息函數(shù)IPMT（）公式為：=IPMT（Rate, Per, Nper, Pv, Fv, Type）其參數(shù)的含義與PPMT（）函數(shù)的

14、參數(shù)含義相同。4.1.7 利息函數(shù)IPMT（）應(yīng)用 【例4-15】承接【例4-13】資料，計算前10個月每月還款的利息。4.1.8 期數(shù)函數(shù)NPER（）含義及說明NPER函數(shù)（）的功能是基于固定利率及等額分期付款方式，返回某項投資或貸款的總期數(shù)。期數(shù)函數(shù)NPER（）公式為：=NPER（Rate, Pmt, Pv, Fv, Type）其參數(shù)的含義與PMT（）函數(shù)等的參數(shù)含義相同。4.1.8 期數(shù)函數(shù)NPER（）應(yīng)用【例4-16】某公司向銀行貸款300萬元，貸款年利率為6%，若該公司采取每年年末等額還款方式，每年年末還款50萬元，計算該公司需要多少年才能全部償還該筆貸款。4.2折舊計算函數(shù)SLN（

15、）函數(shù)DB（）函數(shù)DDB（）函數(shù)VDB（）函數(shù)SYD（）函數(shù)分類4.2 折舊計算函數(shù) DDB（）函數(shù) VDB（）函數(shù) SYD（）函數(shù) SLN（）函數(shù)DB（）函數(shù)4.2.1 SLN（）函數(shù)含義及說明年限平均法，又稱直線法，是按固定資產(chǎn)的使用年限平均計提折舊的方法。按此計算方法所計算的每年的折舊額是相同的，因此，在各年使用資產(chǎn)情況相同時，采用直線法比較恰當(dāng)。在Excel 2010中，可以采用SLN（）函數(shù)計算折舊額。SLN（）函數(shù)公式為：=SLN（Cost, Salvage, Life）各參數(shù)含義：（1）Cost：資產(chǎn)原值；（2）Salvage：資產(chǎn)在折舊期末的價值（有時也稱為資產(chǎn)殘值）；（3）L

16、ife：資產(chǎn)的折舊期數(shù)（有時也稱作資產(chǎn)的使用壽命）。4.2.1 SLN（）函數(shù)應(yīng)用【例4-17】某企業(yè)購進一臺價值1000萬元的大型設(shè)備，該設(shè)備使用期限為10年，預(yù)計凈殘值為50萬元，該企業(yè)采用年限平均法計提折舊，計算該設(shè)備的年折舊額。4.2.2 DB（）函數(shù)含義及說明固定余額遞減折舊法指在預(yù)計使用年限內(nèi)，將后期折舊的一部分移到前期，使前期折舊額大于后期折舊額，是加速折舊法的一種。在Excel 2010中，可以采用DB（）函數(shù)計算固定余額遞減折舊法下的折舊額。DB（）函數(shù)公式為：=DB（Cost, Salvage, Life, Period, Month）各參數(shù)含義：（1）Period：需要計

17、算折舊值的期間，Period必須使用與Life相同的單位；（2）Month：為第一年的月份數(shù)，如省略，則假設(shè)為12。其余參數(shù)的含義與SLN（）函數(shù)的參數(shù)含義相同。4.2.2 DB（）函數(shù)應(yīng)用【例4-18】承接【例4-17】資料，該企業(yè)采用固定余額遞減法計提折舊，要求計算該設(shè)備每年折舊額。4.2.3 DDB（）函數(shù)含義及說明固定余額遞減折舊法指在預(yù)計使用年限內(nèi)，將后期折舊的一部分移到前期，使前期折舊額大于后期折舊額，是加速折舊法的一種。在Excel 2010中，可以采用DB（）函數(shù)計算固定余額遞減折舊法下的折舊額。DB（）函數(shù)公式為：=DB（Cost, Salvage, Life, Period

18、, Month）各參數(shù)含義：（1）Period：需要計算折舊值的期間，Period必須使用與Life相同的單位；（2）Month：為第一年的月份數(shù)，如省略，則假設(shè)為12。其余參數(shù)的含義與SLN（）函數(shù)的參數(shù)含義相同。4.2.3 DDB（）函數(shù)應(yīng)用【例4-19】承接【例4-17】資料，現(xiàn)采用雙倍余額遞減法計算各年折舊額。 4.2.4 VDB（）函數(shù)含義及說明可變余額遞減折舊法是指以不同的余額遞減法計算一個時期內(nèi)折舊額的方法。在Excel 2010中，可采用VDB（）函數(shù)計算可變余額遞減折舊法下的折舊額。VDB（）函數(shù)公式為：=VDB（Cost, Salvage, Life, Start_ per

19、iod, End_ period, Factor, No_ switch）各參數(shù)含義：（1）Start_period：為進行折舊計算的起始期間，Start_ period必須與Life的單位相同。（2）End_ period：為進行折舊計算的截止期間，End_ period必須與Life的單位相同。（3）No_ switch：為邏輯值，指定當(dāng)折舊值大于余額遞減計算值時，是否轉(zhuǎn)用直線折舊法；如果No_ switch為true，即使折舊值大于余額遞減計算值，Excel 2010也不轉(zhuǎn)用直線折舊法；如果No_ switch為false或被忽略，且折舊值大于余額遞減計算值時，Excel 2010將轉(zhuǎn)用

20、直線折舊法。其余參數(shù)的含義與DDB（）函數(shù)的參數(shù)含義相同。4.2.4 VDB（）函數(shù)應(yīng)用【例4-20】承接【例4-17】資料，現(xiàn)采用可變余額遞減折舊法計提折舊，要求計算第一天折舊額、第151天至第458天折舊額、第一個月折舊額、第一年折舊額，第六個月與第二十四個月的折舊額。 4.2.5 SYD（）函數(shù)含義及說明年限總和法，是指用固定資產(chǎn)原值減去預(yù)計殘值后的凈額，乘以一個逐年遞減的分數(shù)(稱為折舊率)，計算折舊額的一種加速折舊的方法，又稱總和年限法、折舊年限積數(shù)法、年數(shù)比率法、級數(shù)遞減法或年限合計法。在Excel 2010中，可以采用SYD（）函數(shù)計算年限總和法下的折舊額。SYD（）函數(shù)公式為：=

21、SYD（Cost, Salvage, Life, Per）其中參數(shù)Per為需要計算折舊值的期間，Per必須使用與Life相同的單位。其余參數(shù)的含義與SLN（）函數(shù)的參數(shù)含義相同。4.2.5 SYD（）函數(shù)應(yīng)用【例4-21】承接【例4-17】資料，現(xiàn)采用年限總和法計算每年折舊額。4.3償還率計算函數(shù)利率函數(shù)RATE（）內(nèi)含報酬率函數(shù)IRR（）修正內(nèi)含報酬率MIRR（）4.3 償還率計算函數(shù)RATE（）函數(shù)基于等額分期付款的方式，返回某項投資或貸款的實際利率。RATE（）函數(shù)通過迭代法計算得出結(jié)果，并且可能無解或有多個解。 1.利率函數(shù)RATE（）IRR（）函數(shù)是返回由數(shù)值代表的一組現(xiàn)金流的內(nèi)部報

22、酬率。這些現(xiàn)金流不一定為均衡的，但作為年金，它們必須按固定的間隔發(fā)生。2.內(nèi)含報酬率函數(shù)IRR（）修正內(nèi)含報酬率函數(shù)MIRR（）是計算某一連續(xù)期間內(nèi)現(xiàn)金流的修正內(nèi)部收益率，該函數(shù)同時考慮了投資的成本和現(xiàn)金再投資的收益率。3.修正內(nèi)含報酬率MIRR（）分類4.3.1 利率函數(shù)RATE（）公式RATE（）函數(shù)公式為：=RATE（Nper, Pmt, Pv, Fv, Type,Guess）各參數(shù)含義：（1）Nper：年金的付款總期數(shù)。（2）Pmt：各期所應(yīng)支付的金額，其數(shù)值在整個年金期間保持不變。通常，Pmt包括本金和利息，但不包括其他費用或稅款。如果省略Pmt，則必須包含F(xiàn)v參數(shù)。（3）Pv：現(xiàn)值

23、，即一系列未來付款現(xiàn)在所值的總金額。（4）Fv：未來值，或在最后一次付款后希望得到的現(xiàn)金余額。如果省略Fv，則假設(shè)其值為 0（例如，一筆貸款的未來值即為 0）。（5）Type：數(shù)字0或1，用以指定各期的付款時間是在期初還是期末，0表示期末，1表示期初，若省略Type，則假設(shè)值為0。（6）Guess：預(yù)期利率，如果省略預(yù)期利率，則假設(shè)該值為10%；如果RATE（）函數(shù)不收斂，則需修改 Guess的值再試。通常當(dāng)Guess位于0到1之間時，RATE（）函數(shù)是收斂的，Guess和Nper單位應(yīng)保持一致性。 4.3.1 利率函數(shù)RATE（）應(yīng)用【例4-22】假設(shè)某房產(chǎn)的價格為600000元，首付3成，

24、銀行提供15年按揭貸款，每月月初還款3600元，計算相應(yīng)的貸款利率。4.3.2 內(nèi)含報酬率函數(shù)IRR（）公式IRR（）函數(shù)公式為：=IRR（Values, Guess）各參數(shù)含義：（1）Values：為數(shù)組或單元格的引用，必須包括至少一個正數(shù)和一個負數(shù)，以計算內(nèi)部報酬率；函數(shù)IRR（）根據(jù)數(shù)值的順序來解釋現(xiàn)金流的順序，故應(yīng)確定按需要的順序輸入了支付和收入的數(shù)值；如果數(shù)組或引用包含文本、邏輯值或空白單元格，這些數(shù)值將被忽略。（2）Guess：對IRR（）函數(shù)計算結(jié)果的估計值，Excel 2010使用迭代法計算IRR（）函數(shù)，從Guess開始，IRR（）函數(shù)進行循環(huán)計算，直至結(jié)果的精度達到0.00001%。如果IRR（）函數(shù)經(jīng)過20次迭代仍未找到結(jié)果，則返回錯誤值#NU