《财务管理》Excel函数应用之财务函数

Exce1函数应用之财务函数 像统计函数、工程函数一样,在Exce中还提供了许多财务函数。财务函数可以进行一般的 财务计算,如确定贷款的支付额、投资的未来值或净现值,以及债券或息票的价值。这些财务函 数大体上可分为四类:投资计算函数、折旧计算函数、偿还率计算函数、债券及其他金融函数。 它们为财务分析提供了极大的便利。使用这些函数不必理解高级财务知识,只要填写变量值就可 以了。在下文中,凡是投资的金额都以负数形式表示,收益以正数形式表示 在介绍具体的财务函数之前,我们首先来了解一下财务函数中常见的参数 未来值(fv)-在所有付款发生后的投资或贷款的价值。 期间数(nper)-为总投资(或贷款)期,即该项投资(或贷款)的付款期总数 付款(pmt)--对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通 常pmt包括本金和利息,但不包括其他费用及税款 现值(pv)-在投资期初的投资或贷款的价值。例如,贷款的现值为所借入的本金数额。 利率(rate)-投资或贷款的利率或贴现率。 类型(type)--付款期间内进行支付的间隔,如在月初或月末,用0或1表示。 日计数基准类型(bass)--为日计数基准类型。 Bas is为0或省略代表US(NASD) 30/360,为1代表实际天数/实际天数,为2代表实际天数/360,为3代表实际天数/365, 为4代表欧洲30/360 接下来,我们将分别举例说明各种不同的财务函数的应用。在本文中主要介绍各类型的典型 财务函数,更多的财务函数请参看附表及相关书籍。如果下文中所介绍的函数不可用,返回错误 值#NAME?,请安装并加载"分析工具库"加载宏。操作方法为: 1、在"工具"菜单上,单击"加载宏"。 2、在"可用加载宏"列表中,选中"分析工具库"框,再单击"确定 投资计算函数 投资计算函数可分为与未来值fv有关,与付款pmt有关,与现值pv有关,与复利计算有 关及与期间数有关几类函数。 1、与未来值fv有关的函数--FV、 FVSCHEDULE 2、与付款pmt有关的函数--IPMT、 ISPMT、PMT、PPMT

Excel 函数应用之财务函数 像统计函数、工程函数一样，在 Excel 中还提供了许多财务函数。财务函数可以进行一般的 财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。这些财务函 数大体上可分为四类：投资计算函数、折旧计算函数、偿还率计算函数、债券及其他金融函数。 它们为财务分析提供了极大的便利。使用这些函数不必理解高级财务知识，只要填写变量值就可 以了。在下文中，凡是投资的金额都以负数形式表示，收益以正数形式表示。 在介绍具体的财务函数之前，我们首先来了解一下财务函数中常见的参数： 未来值 (fv)--在所有付款发生后的投资或贷款的价值。 期间数 (nper)--为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。 付款 (pmt)--对于一项投资或贷款的定期支付数额。其数值在整个年金期间保持不变。通 常 pmt 包括本金和利息，但不包括其他费用及税款。 现值 (pv)--在投资期初的投资或贷款的价值。例如，贷款的现值为所借入的本金数额。 利率 (rate)--投资或贷款的利率或贴现率。 类型 (type)--付款期间内进行支付的间隔，如在月初或月末，用 0 或 1 表示。 日计数基准类型（basis）--为日计数基准类型。Basis 为 0 或省略代表 US (NASD) 30/360 ，为 1 代表实际天数/实际天数 ，为 2 代表实际天数/360 ，为 3 代表实际天数/365 ， 为 4 代表欧洲 30/360。 接下来，我们将分别举例说明各种不同的财务函数的应用。在本文中主要介绍各类型的典型 财务函数，更多的财务函数请参看附表及相关书籍。如果下文中所介绍的函数不可用，返回错误 值 #NAME?，请安装并加载"分析工具库"加载宏。操作方法为： 1、在"工具"菜单上，单击"加载宏"。 2、在"可用加载宏"列表中，选中"分析工具库"框，再单击"确定"。 一、投资计算函数 投资计算函数可分为与未来值 fv 有关，与付款 pm t 有关，与现值 pv 有关，与复利计算有 关及与期间数有关几类函数。 1、与未来值 fv 有关的函数--FV、FVSCHEDULE 2、与付款 pmt 有关的函数--IPMT、ISPMT、PMT、PPMT

3、与现值p有关的函数-NPV、PV、ⅩNPV 4、与复利计算有关的函数- EFFECT.、 NOMINAL 5、与期间数有关的函数-NPER 在投资计算函数中,笔者将重点介绍FV、NPV、PMT、P∨函 (一)求某项投资的未来值FV 在日常工作与生活中,我们经常会遇到要计算某项投资的未来值的情况,此时利用 Excel 函数FV进行计算后,可以帮助我们进行一些有计划、有目的、有效益的投资。FV函数基于固 定利率及等额分期付款方式,返回某项投资的未来值 语法形式为Fv(rate,nper;pmt,pv,type)。其中rate为各期利率,是一固定值,nper为 总投资(或贷款)期,即该项投资(或贷款)的付款期总数,p为各期所应付给(或得到)的 金额,其数值在整个年金期间(或投资期内)保持不变,通常PV包括本金和利息,但不包括其 它费用及税款,pⅴ为现值,或一系列未来付款当前值的累积和,也称为本金,如果省略p,则 假设其值为零,type为数字0或1,用以指定各期的付款时间是在期初还是期末,如果省略t, 则假设其值为零 例如:假如某人两年后需要一笔比较大的学习费用支出,计划从现在起每月初存入2000元, 如果按年利2.25%,按月计息(月利为2.25%/12),那么两年以后该账户的存款额会是多少 公式写为:FV(225%/12,24,-2000,0,1) 数据 说明 123456 2.25%年利率 数 24付款期总类 206各期应付金额 现值 1各期的支付时间在期初 公式 说明(结果〕 FV(A2/12,A3,A4,A5,A6)在上述条件下投资的未来值 (49,141.34) 图1 (二)求投资的净现值NPV NP∨函数基于一系列现金流和固定的各期贴现率,返回一项投资的净现值。投资的净现值 是指未来各期支出(负值)和收入(正值)的当前值的总和

3、与现值 pv 有关的函数--NPV、PV、XNPV 4、与复利计算有关的函数--EFFECT、NOMINAL 5、与期间数有关的函数--NPER 在投资计算函数中，笔者将重点介绍 FV、NPV、PMT、PV 函数。 （一） 求某项投资的未来值 FV 在日常工作与生活中，我们经常会遇到要计算某项投资的未来值的情况，此时利用 Excel 函数 FV 进行计算后，可以帮助我们进行一些有计划、有目的、有效益的投资。FV 函数基于固 定利率及等额分期付款方式，返回某项投资的未来值。 语法形式为 FV(rate,nper,pmt,pv,type)。其中 rate 为各期利率，是一固定值，nper 为 总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv 为各期所应付给（或得到）的 金额，其数值在整个年金期间（或投资期内）保持不变，通常 Pv 包括本金和利息，但不包括其 它费用及税款，pv 为现值，或一系列未来付款当前值的累积和，也称为本金，如果省略 pv，则 假设其值为零，type 为数字 0 或 1，用以指定各期的付款时间是在期初还是期末，如果省略 t， 则假设其值为零。 例如：假如某人两年后需要一笔比较大的学习费用支出，计划从现在起每月初存入 2000 元， 如果按年利 2.25%，按月计息（月利为 2.25%/12），那么两年以后该账户的存款额会是多少 呢？ 公式写为：FV(2.25%/12, 24,-2000,0,1) 图 1 （二） 求投资的净现值 NPV NPV 函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。投资的净现值 是指未来各期支出（负值）和收入（正值）的当前值的总和

语法形式为:NPV(rate, value1,vaue2,…)其中,rate为各期贴现率,是一固定值 value1 value2,代表1到29笔支出及收入的参数值, value1, value2,…所属各期间的长度 必须相等,而且支付及收入的时间都发生在期末。需要注意的是:NP∨按次序使用 va|ue1, value2,来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入 如果参数是数值、空白单元格、逻辑值或表示数值的文字表示式,则都会计算在内:如果参数是 错误值或不能转化为数值的文字,则被忽略,如果参数是一个数组或引用,只有其中的数值部分 计算在内。忽略数组或引用中的空白单元格、逻辑值、文字及错误值 例如,假设开一家电器经销店。初期投资￥200,000,而希望未来五年中各年的收入分别为 ￥20,000、￥40,000、￥50,000、￥80,000和￥120,000。假定每年的贴现率是8%(相 当于通贷膨胀率或竞争投资的利率),则投资的净现值的公式是: NPV(A2, A4: A8)+A3 在该例中,一开始投资的￥200,000并不包含在V参数中,因为此项付款发生在第一期的 期初。假设该电器店的营业到第六年时,要重新装修门面,估计要付出￥40,000,则六年后书 店投资的净现值为 =NPV(A2,A4:A8,A9)+A3 如果期初投资的付款发生在期末,则投资的净现值的公式是 NPV(A2, A3: A8) 数据 说明 8%年贴现率。可表示整个投 资的通货膨胀率或利率 3 200,000初期投资 20,000第一年的收益 第二年的收益 0,000第三年的收空 80,000第四年的收益 120,000第五年的收益 40,000第六年装修费 公式 说明〔结果 ￥32,976.06该投资的净现值 32,976.06) ￥7,769.27该授资的净现值,包括第 六年中40,00的装修费 ￥30,53338该授资的净现值 〔30,533.38) 图2

语法形式为：NPV(rate,value1,value2, ...) 其中，rate 为各期贴现率，是一固定值； value1,value2,...代表 1 到 29 笔支出及收入的参数值，value1,value2,...所属各期间的长度 必须相等，而且支付及收入的时间都发生在期末。需要注意的是：NPV 按次序使用 value1,value2，来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入。 如果参数是数值、空白单元格、逻辑值或表示数值的文字表示式，则都会计算在内；如果参数是 错误值或不能转化为数值的文字，则被忽略，如果参数是一个数组或引用，只有其中的数值部分 计算在内。忽略数组或引用中的空白单元格、逻辑值、文字及错误值。 例如，假设开一家电器经销店。初期投资￥200,000，而希望未来五年中各年的收入分别为 ￥20,000、￥40,000、￥50,000、￥80,000 和￥120,000。假定每年的贴现率是 8%（相 当于通贷膨胀率或竞争投资的利率），则投资的净现值的公式是： =NPV(A2, A4:A8)+A3 在该例中，一开始投资的￥200,000 并不包含在 v 参数中，因为此项付款发生在第一期的 期初。假设该电器店的营业到第六年时，要重新装修门面，估计要付出￥40,000，则六年后书 店投资的净现值为： =NPV(A2, A4:A8, A9)+A3 如果期初投资的付款发生在期末，则 投资的净现值的公式是： =NPV(A2, A3:A8) 图 2

(三)求贷款分期偿还额PMT PMT函数基于固定利率及等额分期付款方式,返回投资或贷款的每期付款额。PMT函数可 以计算为偿还一笔贷款,要求在一定周期内支付完时,每次需要支付的偿还额,也就是我们平时 所说的"分期付款"。比如借购房贷款或其它贷款时,可以计算每期的偿还额。 其语法形式为:PMT(rate,nper; pv, fv, type)其中,rate为各期利率,是一固定值,nper 为总投资(或贷款)期,即该项投资(或贷款)的付款期总数,pv为现值,或一系列未来付款 当前值的累积和,也称为本金,fv为未来值,或在最后一次付款后希望得到的现金余额,如果 省略fv,则假设其值为零(例如,一笔贷款的未来值即为零),type为0或1,用以指定各期 的付款时间是在期初还是期末。如果省略type,则假设其值为零。 例如,需要10个月付清的年利率为8%的￥10,000贷款的月支额为: MT(8%/12,10,10000)计算结果为:-￥1,037.03。 (四)求某项投资的现值PV P∨函数用来计算某项投资的现值。年金现值就是未来各期年金现在的价值的总和。如果投 资回收的当前价值大于投资的价值,则这项投资是有收益的 其语法形式为:PV(rate,nper,pmt, fv, type)其中Rate为各期利率。Nper为总投资(或 贷款)期,即该项投资(或贷款)的付款期总数。Pmt为各期所应支付的金额,其数值在整个 年金期间保持不变。通常pmt包括本金和利息,但不包括其他费用及税款。Fv为未来值,或 在最后一次支付后希望得到的现金余额,如果省略fv,则假设其值为零(一笔贷款的未来值即 为零)。Type用以指定各期的付款时间是在期初还是期末 例如,假设要购买一项保险年金,该保险可以在今后二十年内于每月末回报￥600。此项年 金的购买成本为80,000,假定投资回报率为8%。那么该项年金的现值为 PV(0.08/12,12*20,600,0)计算结果为:￥-71,732.58。 负值表示这是一笔付款,也就是支出现金流。年金(￥-71,732.58)的现值小于实际支付 的(￥80,000)。因此,这不是一项合算的投资

（三） 求贷款分期偿还额 PMT PMT 函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。PMT 函数可 以计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付的偿还额，也就是我们平时 所说的"分期付款"。比如借购房贷款或其它贷款时，可以计算每期的偿还额。 其语法形式为：PMT(rate,nper,pv,fv,type) 其中，rate 为各期利率，是一固定值，nper 为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv 为现值，或一系列未来付款 当前值的累积和，也称为本金，fv 为未来值，或在最后一次付款后希望得到的现金余额，如果 省略 fv，则假设其值为零（例如，一笔贷款的未来值即为零），type 为 0 或 1，用以指定各期 的付款时间是在期初还是期末。如果省略 type，则假设其值为零。 例如，需要 10 个月付清的年利率为 8%的￥10,000 贷款的月支额为： PMT（8%/12,10,10000） 计算结果为：-￥1,037.03。 （四） 求某项投资的现值 PV PV 函数用来计算某项投资的现值。年金现值就是未来各期年金现在的价值的总和。如果投 资回收的当前价值大于投资的价值，则这项投资是有收益的。 其语法形式为：PV(rate,nper,pmt,fv,type) 其中 Rate 为各期利率。Nper 为总投资（或 贷款）期，即该项投资（或贷款）的付款期总数。Pmt 为各期所应支付的金额，其数值在整个 年金期间保持不变。通常 pm t 包括本金和利息，但不包括其他费用及税款。Fv 为未来值，或 在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零（一笔贷款的未来值即 为零）。Type 用以指定各期的付款时间是在期初还是期末。 例如，假设要购买一项保险年金，该保险可以在今后二十年内于每月末回报￥600。此项年 金的购买成本为 80,000，假定投资回报率为 8%。那么该项年金的现值为： PV(0.08/12, 12*20,600,0) 计算结果为：￥-71,732.58。 负值表示这是一笔付款，也就是支出现金流。年金（￥-71,732.58）的现值小于实际支付 的（￥80,000）。因此，这不是一项合算的投资

数据 说明 600每月底一项保险年金的支出 234567 8%披资收益率 20付款的年限 公式 说明〔结果〕 ￥-71,732.58在上述条件下年金的现值 (-71,732.58)。 图3 、折旧计算函数 折旧计算函数主要包括 AMORDEGRO、 AMORLINC、DB、DDB、SLN、SYD、VDB。这 些函数都是用来计算资产折旧的,只是采用了不同的计算方法。这里,对于具体的计算公式不再 赘述,具体选用哪种折旧方法,则须视各单位情况而定 、偿还率计算函数 偿还率计算函数主要用以计算内部收益率,包括IRR、MIRR、RATE和XIRR几个函数 (一)返回内部收益率的函数--IRR IRR函数返回由数值代表的一组现金流的内部收益率。这些现金流不一定必须为均衡的,但 作为年金,它们必须按固定的间隔发生,如按月或按年。内部收益率为投资的回收利率,其中包 含定期支付(负值)和收入(正值)。 其语法形式为IRR( values, guess)其中 values为数组或单元格的引用,包含用来计算内 部收益率的数字, values必须包含至少一个正值和一个负值,以计算内部收益率,函数IRR根 据数值的顺序来解释现金流的顺序,故应确定按需要的顺序输入了支付和收入的数值,如果数组 或引用包含文本、逻辑值或空白单元格,这些数值将被忽略: guess为对函数IRR计算结果的 估计值,exce使用迭代法计算函数IRR从 guess开始,函数IRR不断修正收益率,直至结果 的精度达到0.00001%,如果函数IRR经过20次迭代,仍未找到结果,则返回错误值#NUM!, 在大多数情况下,并不需要为函数IRR的计算提供 guess值,如果省略 guess,假设它为0.1 (10%)。如果函数IRR返回错误值#NUM!,或结果没有靠近期望值,可以给 guess换 个值再试一下 例如,如果要开办一家服装商店,预计投资为￥110,000,并预期为今后五年的净收益为:

图 3 二、 折旧计算函数 折旧计算函数主要包括 AMORDEGRC、AMORLINC、DB、DDB、SLN、SYD、VDB。这 些函数都是用来计算资产折旧的，只是采用了不同的计算方法。这里，对于具体的计算公式不再 赘述，具体选用哪种折旧方法，则须视各单位情况而定。 三、偿还率计算函数 偿还率计算函数主要用以计算内部收益率，包括 IRR、MIRR、RATE 和 XIRR 几个函数。 （一） 返回内部收益率的函数--IRR IRR 函数返回由数值代表的一组现金流的内部收益率。这些现金流不一定必须为均衡的，但 作为年金，它们必须按固定的间隔发生，如按月或按年。内部收益率为投资的回收利率，其中包 含定期支付（负值）和收入（正值）。 其语法形式为 IRR(values,guess) 其中 values 为数组或单元格的引用，包含用来计算内 部收益率的数字，values 必须包含至少一个正值和一个负值，以计算内部收益率，函数 IRR 根 据数值的顺序来解释现金流的顺序，故应确定按需要的顺序输入了支付和收入的数值，如果数组 或引用包含文本、逻辑值或空白单元格，这些数值将被忽略；guess 为对函数 IRR 计算结果的 估计值，excel 使用迭代法计算函数 IRR 从 guess 开始，函数 IRR 不断修正收益率，直至结果 的精度达到 0.00001%，如果函数 IRR 经过 20 次迭代，仍未找到结果，则返回错误值#NUM！， 在大多数情况下，并不需要为函数 IRR 的计算提供 guess 值，如果省略 guess，假设它为 0.1 （10%）。如果函数 IRR 返回错误值#NUM！，或结果没有靠近期望值，可以给 guess 换一 个值再试一下。 例如，如果要开办一家服装商店，预计投资为￥110,000，并预期为今后五年的净收益为：