excel在财务管理中的运用常用财务函数课件

1、excel在财务管理中的运用常用财务函 数1 ExcelExcel在财务管理中的应用在财务管理中的应用 第4章 常用财务函数 excel在财务管理中的运用常用财务函 数2 学习目标 4.1 终值函数 4.2 现值函数 4.3 年金、本金和利息函数 4.4 期数函数NPER( ) 4.5 折现率函数 4.6 折旧计算函数 本章小结 主要概念和观念 目录 excel在财务管理中的运用常用财务函 数3 学习目标 通过本章的学习，了解常用财务函数的种类，熟悉常用财 务函数的使用方法，学会使用财务函数计算货币资金的时 间价值、固定资产折旧等财务指标，为以后学习投资分析 等内容打下基础。 excel在财务

2、管理中的运用常用财务函 数4 财务管理基本知识回顾 货币的时间价值是货币随着时间的推移而形成的增值。 1、复利的计算 （1）复利终值：（利滚利） 例：现在存1万元，银行利率8，复利计算，5年后的本利和是多少？ F=10000（1+8）514690 复利终值系数( ) 可查阅“复利终值系数表”取得 F=10000*1.46914690 excel在财务管理中的运用常用财务函 数5 （2）复利现值 例：要想5年后得到10万元，年利率10，现在应存入的本金是多 少？ F=100000/（1+10）562090 复利现值系数( ) 可查阅“复利现值系数表”取得 P=1000000.620962090

3、excel在财务管理中的运用常用财务函 数6 2021-7-126 2.年金的计算 （ （1）普通年金每期期末收付 A(1+i)n-2 A(1+i)n-1 A A A A 0 1 2 n A: 每年现金流 i . . . 现在现在 A(1+i) excel在财务管理中的运用常用财务函 数7 2021-7-127 普通年金终值 FA=A+A(1+i)+A(1+i)2+A(1+i)n-1 = A (1+i)n-1 i 例：若每年年末存例：若每年年末存1万元，利率为万元，利率为8，5年后的本利年后的本利 和是多少？和是多少？ = 10000 (1+8)5-1 8 FA = 58667 年金终值系数年

4、金终值系数 F/AF/A，i,ni,n 若查阅年金终值系数表，可得：若查阅年金终值系数表，可得： FA=100005.866658666 excel在财务管理中的运用常用财务函 数8 2021-7-128 普通年金现值 A(1+i)2 A(1+i)n-1 A(1+i) A A A A 0 1 2 n A: 每年现金流 i . . . A(1+i)n PA=A/(1+i)+A/(1+i)2+A/(1+i)n-1 +A/(1+i)n excel在财务管理中的运用常用财务函 数9 2021-7-129 PA= A 1-(1+i)-n i 例：若某企业现在有一项投资选择例：若某企业现在有一项投资选择投

5、资投资15000元，对元，对 方承诺未来每年年末返还给企业方承诺未来每年年末返还给企业2000元，连续给元，连续给10年，年， 企业是否该进行这项投资？假设社会平均回报率为企业是否该进行这项投资？假设社会平均回报率为8 = 2000 1-(1+8)-10 8 PA = 13420 年金现值系数年金现值系数 P/AP/A，i,ni,n 若查阅年金现值系数表，可得：若查阅年金现值系数表，可得： P20006.7101342015000，所以选择不投资，所以选择不投资 意义：意义： excel在财务管理中的运用常用财务函 数10 2021-7-1210 (2)预付年金每期期初收付 预付年金终值 A

6、A A A 0 1 2 n A: 每年现金流 i . . . 现在现在 A(1+i)n-2 A(1+i)n-1 A(1+i) A(1+i)n excel在财务管理中的运用常用财务函 数11 2021-7-1211 FA=A(1+i)+A(1+i)2+A(1+i)n-1 +A(1+i)n 例：若每年例：若每年存存1万元，利率为万元，利率为8，5年后的本利年后的本利 和是多少？和是多少？ = 63359 预付年金终值系数和普通预付年金终值系数和普通 年金终值系数的关系年金终值系数的关系: : 期数期数+1+1，系数，系数-1-1 若查阅年金终值系数表（若查阅年金终值系数表（P618)，可得：，可得

7、： FA=100006.335963359 = A (1+i)n-1 i FA = 10000 (1+8%)5+1-1 8% -1 excel在财务管理中的运用常用财务函 数12 2021-7-1212 预付年金现值 A A A A 0 1 2 n A: 每年现金流 i . . . A(1+i)2 A(1+i)n-1 A(1+i) PA=A+A/(1+i)+A/(1+i)2+A/(1+i)n-1 = A 1-(1+i)-(n i 预付年金现值系数和普通预付年金现值系数和普通 年金现值系数的关系年金现值系数的关系: : 期数期数1 1，系数，系数1 1 excel在财务管理中的运用常用财务函 数

8、13 2021-7-1213 例：若某企业年初准备改进工艺引入一台新设备，公司有2种选择：直接购 买和融资租赁，直接购买需要马上支付现金13000元，租赁则需要每年年 初支付2000元，连续租赁10年，企业应如何选择？假设社会平均回报率 为8 = 2000 1-(1+8)-(10 8 PA = 14494 若查阅年金现值系数表，期数若查阅年金现值系数表，期数1，系数，系数1可得：可得： P20007.246914494，所以选择购买。，所以选择购买。 excel在财务管理中的运用常用财务函 数14 2021-7-1214 （3）递延年金第一次支付（取得）发生在第2期或以后的年金 递延年金终值F

9、A=A*（F/A，i,n） 递延年金的终值大小和递延期没有关系 递延年金现值PAA*（P/A，i,n）*（P/F，i,m） 递延年金现值在计算普通年金现值后还要进行复利贴现 例：某企业打算上马一个新项目，项目建设期3年，预计每年年初需 要投资100万，3年建设期满后即可投入生产，预计生产的10年内 每年可为企业增加50万的收益，该企业是否要投资这个项目？假 设平均投资回报率为8。 excel在财务管理中的运用常用财务函 数15 2021-7-1215 -100 -100 -100 50 50 50 0 1 2 3 12 13 8% . . . 预付年金现值（预付年金现值（8 8,3,3） 10

10、01002.7833=278.332.7833=278.33 普通年金现值普通年金现值(8%,10)(8%,10) 50506.7101=335.5056.7101=335.505 复利现值复利现值(8%,3)(8%,3) 335.505335.5050.7938=266.320.7938=266.32 对对 比比 excel在财务管理中的运用常用财务函 数16 2021-7-1216 (4)永续年金存本取息 终值：无到期日，因此无终值 现值： PA= A 1-(1+i)-n i 当n, (1+i)-n0,因此因此PAAI 例：某生物学会欲建立专项奖励基金，每年打算拿出例：某生物学会欲建立专项

11、奖励基金，每年打算拿出 1.6万元作为奖金，若存款利率为万元作为奖金，若存款利率为8，该学会应存，该学会应存 入多少元的基本基金？入多少元的基本基金？ PAAI160008200000元元 excel在财务管理中的运用常用财务函 数17 4.1 终值函数 FV(rate,nper,pmt,pv,type) rate各期利率 nper付款期总数 pmt各期应付（或得到）的金额（年金） pv现值，缺省为0 Type_0为期末，可缺省；1为期初。 excel在财务管理中的运用常用财务函 数18 4.1.1整收整付款项的复利终值 4.1.2年金终值 1）普通年金终值 2）先付年金终值 3)递延年金终值

12、 excel在财务管理中的运用常用财务函 数19 4.2 现值函数 PV(rate,nper,pmt,fv,type) rate各期利率 nper付款期总数 pmt各期应付（或得到）的金额（年金） fv终值，缺省为0 Type_0为期末，可缺省；1为期初。 excel在财务管理中的运用常用财务函 数20 4.2.1 PV函数 1) 整收整付款项的复利现值 2） 年金现值 excel在财务管理中的运用常用财务函 数21 4.2.2 NPV函数 1净现值= 项目价值投资现值 = 产出投入 = 未来现金净流量现值原始投资额现值 将未来现金流入一一折现，同当前的现金流出作比较 若净现值为，则投资项目

13、若净现值为，则投资项目 -100 -100 -100 50 50 50 0 1 2 3 12 13 8% . . . excel在财务管理中的运用常用财务函 数22 例 012 -200001180013240 项目价值=11800(P/F,10%,1)+13240(P/F,10%,2)=21669（元） 投资现值=20000（元） 净现值=2166920000=1669（元） 决策标准：净现值大于或等于零，项目财务上可行。 运用净现值法进行互斥选择投资决策时，净现值越 大的方案相对优越；运用净现值法进行选择是否投 资决策时，若NVP0，则项目应予接受；若NVP0， 则项目应予拒绝。 00 (

14、1)(1) nn kk kk kk IO ii 净 现 值 excel在财务管理中的运用常用财务函 数23 NPV(rate,value1, value2, valueN) 要求： value1, valueN，所属各期间的长度必须相 等，而且支付及收入的时间都发生在期末。 excel在财务管理中的运用常用财务函 数24 4.3 年金、本金和利息函数 4.3.1年金函数PMT( ) PMT(rate,nper,pv, fv,type) rate各期利率 nper付款期总数 pv现值， fv终值，缺省为0 Type_0为期末，可缺省；1为期初。 excel在财务管理中的运用常用财务函 数25 某

15、企业借入长期借款某企业借入长期借款100000100000元，期限为元，期限为5 5年，借款年，借款 利率为固定利率，年利率为利率为固定利率，年利率为6 6： 年限本期还款额利息本金借款余额 0100,000 123,739.446,00017739.4482260.52 223,739.444935.6318803.8163456.75 323,739.443807.4119932.0343524.72 423,739.442611.4821127.9622396.76 523,739.441342.6822396.760 合计118,697.218697.2100,000 excel在财务

16、管理中的运用常用财务函 数26 4.3.2 年金中的本金函数PPMT() PPMT(rate,nper,pv, fv,type) rate各期利率 nper付款期总数 pv现值， fv终值，缺省为0 Type_0为期末，可缺省；1为期初。 excel在财务管理中的运用常用财务函 数27 4.3.3 年金中的利息函数IPMT() IPMT(rate,nper,pv, fv,type) rate各期利率 nper付款期总数 pv现值， fv终值，缺省为0 Type_0为期末，可缺省；1为期初。 excel在财务管理中的运用常用财务函 数28 4.4 期数函数NPER( ) NPER(rate,pm

17、t,pv,fv,type) excel在财务管理中的运用常用财务函 数29 4.5 折现率函数 4.5.1利率函数RATE( ) RATE(nper,pmt,pv,fv,type,guess) excel在财务管理中的运用常用财务函 数30 4.5.2 内含报酬率函数IRR（ ） 内含报酬率内含报酬率 内含报酬率（内含报酬率（IRRIRR）是指一个投资方案在其寿命周期内，）是指一个投资方案在其寿命周期内， 按现值计算的实际投资报酬率。根据这个报酬率，对方案按现值计算的实际投资报酬率。根据这个报酬率，对方案 寿命周期内各年现金流量进行贴现，未来报酬的总现值正寿命周期内各年现金流量进行贴现，未来报

18、酬的总现值正 好等于该方案初始投资的现值。因此，内含报酬率是使投好等于该方案初始投资的现值。因此，内含报酬率是使投 资方案的净现值为零的报酬率。在内含报酬率指标的运用资方案的净现值为零的报酬率。在内含报酬率指标的运用 中，任何一项投资方案的内含报酬率必须以不低于资金成中，任何一项投资方案的内含报酬率必须以不低于资金成 本为限度，否则方案不可行。本为限度，否则方案不可行。 excel在财务管理中的运用常用财务函 数31 内含报酬率法 倒求能够使未来现金流入量的现值 等于未来现金流出量的贴现率 内含报酬率= 贴现率（净现值为0的贴现率） 条件：现金流入=现金流出 0 )1 ()1 ( 00 n t

19、 t t n t t t r I r NCF NPV excel在财务管理中的运用常用财务函 数32 内含报酬率函数内含报酬率函数 格式：格式：IRR(values,guess)IRR(values,guess) 功能：返回连续期间的现金流量的内含报酬率。功能：返回连续期间的现金流量的内含报酬率。 说明：说明： （1 1）valuesvalues为数组或单元格的引用，包含用来计算内部收益率的数字。为数组或单元格的引用，包含用来计算内部收益率的数字。 Values Values必须包含至少一个正值和一个负值，以计算内部收益率。必须包含至少一个正值和一个负值，以计算内部收益率。IRRIRR 根据数

20、值的顺序来解释现金流的顺序，故应确定按需要的顺序输入了根据数值的顺序来解释现金流的顺序，故应确定按需要的顺序输入了 支付和收入的数值。如果数组或引用包含文本、逻辑值或空白单元格，支付和收入的数值。如果数组或引用包含文本、逻辑值或空白单元格， 这些数值将被忽略。这些数值将被忽略。 excel在财务管理中的运用常用财务函 数33 （2 2）guessguess为对为对IRRIRR计算结果的估计值。计算结果的估计值。Excel Excel 使用迭代法使用迭代法 计算计算IRRIRR。从。从guessguess开始，开始，IRR IRR 不断修正收益率，直至结不断修正收益率，直至结 果的精度达到果的精

21、度达到0.00001%0.00001%。如果。如果IRRIRR经过经过2020次迭代，仍未次迭代，仍未 找到结果，则返回错误值找到结果，则返回错误值 #NUM!#NUM!。在大多数情况下，并。在大多数情况下，并 不需要为不需要为IRRIRR的计算提供的计算提供 guessguess值。如果省略值。如果省略guessguess，假，假 设它为设它为 0.10.1（10%10%）。如果函数）。如果函数IRR IRR 返回错误值返回错误值 #NUM!#NUM!， 或结果没有靠近期望值，可以给或结果没有靠近期望值，可以给 guess guess 换一个值再试一换一个值再试一 下。下。 excel在财务

22、管理中的运用常用财务函 数34 4.5.3修正内含报酬率函数MIRR（） 修正内含报酬率函数（修正内含报酬率函数（MIRRMIRR） 格式：格式：MIRR(values,finance_rate,reinvest_rate)MIRR(values,finance_rate,reinvest_rate) 功能：返回某连续期间现金流量的修正后的内含报酬率。功能：返回某连续期间现金流量的修正后的内含报酬率。 说明：说明： （1 1）valuesvalues为一个数组，或对数字单元格区的引用。这些数值代表着为一个数组，或对数字单元格区的引用。这些数值代表着 各期支出（负值）及收入（正值）。参数各期支出

23、（负值）及收入（正值）。参数ValuesValues中必须至少包含一中必须至少包含一 个正值和一个负值，才能计算修正后的内部收益率，否则个正值和一个负值，才能计算修正后的内部收益率，否则MIRRMIRR会返会返 回错误值回错误值#DIV/0!#DIV/0!。如果数组或引用中包括文字串、逻辑值或空白单。如果数组或引用中包括文字串、逻辑值或空白单 元格，这些值将被忽略；但包括数值零的单元格计算在内。元格，这些值将被忽略；但包括数值零的单元格计算在内。 excel在财务管理中的运用常用财务函 数35 （2 2）finance_ratefinance_rate为投入资金的融资利率。为投入资金的融资利率

24、。 （3 3）reinvest_ratereinvest_rate为各期收入净额再投资的收益率。为各期收入净额再投资的收益率。 （4 4）MIRRMIRR根据输入值的次序来注释现金流的次序，所以，根据输入值的次序来注释现金流的次序，所以， 务必按照实际顺序输入支出和收入数额，并使用正确的正务必按照实际顺序输入支出和收入数额，并使用正确的正 负号（现金流入用正值，现金流出用负值）。负号（现金流入用正值，现金流出用负值）。 excel在财务管理中的运用常用财务函 数36 4.6 折旧计算函数 4.6.1 SLN()函数 4.6.2 DB()函数 4.6.3 DDB()函数 4.6.4 VDB()函

25、数 4.6.5 SYD（）函数 excel在财务管理中的运用常用财务函 数37 （1 1）平均年限折旧法）平均年限折旧法SLNSLN 平均年限法也称直线折旧法，即将固定资产的价值均衡地分摊到每平均年限法也称直线折旧法，即将固定资产的价值均衡地分摊到每 一期中。因此，每年提取的折旧金额是相等的。其计算公式如下：一期中。因此，每年提取的折旧金额是相等的。其计算公式如下： 年折旧金额年折旧金额= =（固定资产原值（固定资产原值- -预计残值）预计残值）/ /折旧年限折旧年限 公式中的预计残值已经考虑到报废时的清理费用中。公式中的预计残值已经考虑到报废时的清理费用中。 或者采用相对比率，即年折旧率或者

26、采用相对比率，即年折旧率= =（1-1-预计残值率）预计残值率）/ /折旧年限折旧年限 如果采用月折旧金额，则月折旧金额如果采用月折旧金额，则月折旧金额= =固定资产原值固定资产原值* *（年折旧率（年折旧率/12/12） excel在财务管理中的运用常用财务函 数38 在在ExcelExcel中可以使用函数中可以使用函数SLN( )SLN( )计算平均年限法的折旧额，计算平均年限法的折旧额， 其函数格式如下：其函数格式如下： SLN(cost,salvage,life) SLN(cost,salvage,life) 其中，参数其中，参数costcost代表资产的原始价值；代表资产的原始价值；

27、 参数参数salvagesalvage代表在折旧期末时的净残值代表在折旧期末时的净残值 参数参数lifelife代表固定资产的折旧周期。代表固定资产的折旧周期。 excel在财务管理中的运用常用财务函 数39 （2 2）年数总和法）年数总和法SYDSYD 年数总和法是以固定资产的原始成本减预计残值后的余额年数总和法是以固定资产的原始成本减预计残值后的余额 乘以逐年递减的分数作为该期的折旧额。这个分数的分母乘以逐年递减的分数作为该期的折旧额。这个分数的分母 是固定资产使用年限的各年年数之和；分子是固定资产尚是固定资产使用年限的各年年数之和；分子是固定资产尚 可使用的年限。可使用的年限。 exce

28、l在财务管理中的运用常用财务函 数40 尚可使用年数尚可使用年数 预计使用年限预计使用年限（预计使用年限（预计使用年限1）2 =年折旧率年折旧率 某年折旧额某年折旧额=（固定资产原值预计净残值）（固定资产原值预计净残值）该年折旧率该年折旧率 不变不变逐年递减逐年递减 逐年递减逐年递减 年折旧率年折旧率=尚可使用年数尚可使用年数/预计使用年限总和预计使用年限总和 excel在财务管理中的运用常用财务函 数41 在在ExcelExcel中可以使用函数中可以使用函数SYD( )SYD( )来计算年数总和法下的固来计算年数总和法下的固 定资产折旧额。其格式如下：定资产折旧额。其格式如下： SYD(co

29、st,salvage,life,per) SYD(cost,salvage,life,per) 其中，参数其中，参数costcost为资产原值。为资产原值。 参数参数salvagesalvage为资产在折旧期末的价值（也称为资产残为资产在折旧期末的价值（也称为资产残 值）。值）。 参数参数lifelife为折旧期限（有时也称资产的生命周期）。为折旧期限（有时也称资产的生命周期）。 参数参数perper为期间，其单位与为期间，其单位与lifelife相同。相同。 excel在财务管理中的运用常用财务函 数42 （3 3）双倍余额递减折旧法）双倍余额递减折旧法DDBDDB 双倍余额递减折旧法也是一

30、种加速折旧的方法，它在计算双倍余额递减折旧法也是一种加速折旧的方法，它在计算 年折旧率时暂不考虑该固定资产在折旧期末时的残值，并年折旧率时暂不考虑该固定资产在折旧期末时的残值，并 且采用直线折旧法的两倍值作为其年折旧率，然后，用该且采用直线折旧法的两倍值作为其年折旧率，然后，用该 年折旧率乘以该固定资产本期期初账面价值作为本期的年年折旧率乘以该固定资产本期期初账面价值作为本期的年 折旧金额，其计算公式为：折旧金额，其计算公式为： 年折旧额年折旧额= =年初固定资产账面余额年初固定资产账面余额双倍直线折旧率双倍直线折旧率 一般在最后两期已提折旧总额已经超过可折旧总额时应改一般在最后两期已提折旧总

31、额已经超过可折旧总额时应改 用平均年限法计提折旧。用平均年限法计提折旧。 excel在财务管理中的运用常用财务函 数43 固定资产固定资产年初账面净值年初账面净值折旧率折旧率=年折旧额年折旧额 年折旧率年折旧率 = 2 预计使用年限预计使用年限 逐年递减逐年递减不变不变逐年递减逐年递减 excel在财务管理中的运用常用财务函 数44 在在ExcelExcel中，可以使用函数中，可以使用函数DDB()DDB()计算双倍余额递减法的计算双倍余额递减法的 折旧额，其格式如下：折旧额，其格式如下： DDB(cost,salvage,life,period,factor)DDB(cost,salvage,life,period,fac