财务函数使用技巧

Excel 实战：年数总和法计算固定资产折旧

年数总和法是常用的一种计算固定资产折旧的方法，又称为“合计年限法”。年数总和法是用固定资产的原值减去预计净残值后得到的余额，再乘以每年的折旧率。年数总和法的计算公式为。

年折旧率=尚可使用寿命／预计使用寿命的年数总和

月折旧率=年折旧率/12

月折旧额=（固定资产原值-预计净残值）×月折旧率

例如，使用年数总和法计算的每年折旧额和累计折旧如表17-4所示。

表17-4　使用年数总和法计算的每年折旧额和累计折旧

假设某公司购进一台固定资产价值为100万元，预计残值为10万元，使用年限为5年，下面在Excel中使用年限总和法计算固定资产折旧。基础数据如图17-107所示。

步骤1：打开例子工作簿“固定资产折旧.xlsx”。

步骤2：单击B5单元格，然后单击公式编辑栏上的“插入函数”按钮，打开“插入函数”对话框。

步骤3：单击“或选择类别”右侧列表中的“财务”类别，然后在“选择函数”列表中选择“SYD”函数，如图17-108所示。

图17-107　基础数据

图17-108　选择“SYD”函数

步骤4：单击“确定”按钮，打开“函数参数”对话框，输入各项参数，如图17-109所示。

步骤5：单击“确定”按钮，可以看到出现计算结果，即第1年的折旧额为30万元，公式为“=SYD（B1，B2，B3，1）”，如图17-110所示。

图17-109　输入各项参数

图17-110　计算结果

步骤6：保持B5单元格的选中状态，按“Ctrl+C”复制单元格，然后选中B6单元格，按“Ctrl+V”粘贴，并在公式编辑栏中将公式改为“=SYD（B1，B2，B3，2）”。

步骤7：用同样的方法在B7、B8、B9单元格中粘贴并修改公式，分别为“=SYD（B1，B2，B3，3）”、“=SYD（B1，B2，B3，4）”、“=SYD（B1，B2，B3，5）”。计算结果如图17-111所示。

图17-111　计算结果

Excel 应用RECEIVED函数计算完全投资型证券在到期日收回的金额

RECEIVED函数用于计算一次性付息的有价证券到期收回的金额。RECEIVED函数的语法如下。

RECEIVED(settlement,maturity,investment,discount,basis)

其中参数settlement为证券的结算日，即在发行日之后，证券卖给购买者的日期。maturity为有价证券的到期日，即有价证券有效期截止时的日期。investment为有价证券的投资额。discount为有价证券的贴现率。basis为日计数基准类型。

典型案例

已知债券的成交日、到期日、投资额、贴现率等，计算在这些条件下债券到期收回的总金额。基础数据如图17-105所示。

步骤1：打开例子工作簿“RECEIVED.xlsx”。

步骤2：在单元格A8中输入公式“=RECEIVED（A2，A3，A4，A5，A6）”，用于计算在这些条件下债券到期收回的总金额。计算结果如图17-106所示。

图17-105　基础数据

图17-106　计算结果

使用指南

settlement、maturity和basis数值若非整数将被截尾取整。如果settlement或maturity不是合法日期，函数RECEIVED将返回错误值“#VALUE！”；如果investment≤0或discount≤0，函数RECEIVED返回错误值“#NUM！”；如果basis<0或basis>4，函数RECEIVED将返回错误值“#NUM！”；如果settlement≥maturity，函数RECEIVED将返回错误值“#NUM！”。函数RECEIVED的计算公式如下。

式中：

B为一年之中的天数，取决于年基准数。

DIM为发行日与到期日之间的天数。

Excel 应用PMT函数计算年金的定期支付金额

PMT函数用作基于固定利率及等额分期付款方式，计算贷款的每期付款额。PMT函数的语法如下。

PMT(rate,nper,pv,fv,type)

其中参数rate为贷款利率。nper为该项贷款的付款总数。pv为现值，或一系列未来付款的当前值的累积和，也称为本金。fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零，也就是一笔贷款的未来值为零。type为数字0或1，用以指定各期的付款时间是在期初还是期末。

典型案例

已知储蓄存款的年利率、计划储蓄的年数、18年内计划储蓄的数额，计算为18年后最终得到70，000，每个月应存的数额。基础数据如图17-103所示。

图17-103　基础数据

步骤1：打开例子工作簿“PMT.xlsx”。

步骤2：在单元格A6中输入公式“=PMT（A2/12，A3*12，0，A4）”，用于计算为18年后最终得到70，000，每个月应存的数额。计算结果如图17-104所示。

图17-104　计算结果

使用指南

PMT返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。应确认所指定的rate和nper单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。如果要计算贷款期间的支付总额，则需要用PMT返回值乘以nper。

Excel 应用NPER函数计算投资的期数

NPER函数用作基于固定利率及等额分期付款方式计算某项投资的总期数。NPER函数的语法如下。

NPER(rate,pmt,pv,fv,type)

其中参数rate为各期利率。pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt包括本金和利息，但不包括其他费用或税款。pv为现值，或一系列未来付款的当前值的累积和。fv为未来值，或在最后一次付款后希望得到的现金余额。如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。type为数字0或1，用以指定各期的付款时间是在期初还是期末。

【典型案例】已知某项投资的年利率、各期所付的金额、现值、未来值等信息，计算在这些条件下的总期数。基础数据如图17-101所示。

步骤1：打开例子工作簿“NPER.xlsx”。

步骤2：在单元格A8中输入公式“=NPER（A2/12，A3，A4，A5，1）”，用于计算在上述条件下投资的总期数。

步骤3：在单元格A9中输入公式“=NPER（A2/12，A3，A4，A5）”，用于计算在上述条件下投资的总期数（不包括在期初的支付）。

步骤4：在单元格A10中输入公式“=NPER（A2/12，A3，A4）”，用于计算在上述条件下投资的总期数（不包括未来值0）。计算结果如图17-102所示。

图17-101　基础数据

图17-102　计算结果

Excel 应用MDURATION函数计算面值为￥100的有价证券Macauley修正期限

MDURATION函数用于计算假设面值为￥100的有价证券的Macauley修正期限。MDURATION函数的语法如下。

MDURATION(settlement,maturity,coupon,yld,frequency,basis)

其中参数settlement为证券的结算日，即在发行日之后，证券卖给购买者的日期。maturity为有价证券的到期日，即有价证券有效期截止时的日期。coupon为有价证券的年息票利率。yld为有价证券的年收益率。frequency为年付息次数。如果按年支付，frequency=1；按半年期支付，frequency=2；按季支付，frequency=4。basis为日计数基准类型。

典型案例

已知债券的结算日、到期日、息票利率、收益率等信息，计算在这些条件下的债券的修正期限。基础数据如图17-99所示。

步骤1：打开例子工作簿“MDURATION.xlsx”。

步骤2：在单元格A9中输入公式“=MDURATION（A2，A3，A4，A5，A6，A7）”，用于计算在上述条件下债券的修正期限。计算结果如图17-100所示。

图17-99　基础数据

图17-100　计算结果

使用指南

settlement、maturity、frequency和basis数值若非整数将被截尾取整。如果settlement或maturity不是合法日期，函数MDURATION返回错误值“#VALUE！”；如果yld<0或coupon<0，函数MDURATION返回错误值“#NUM！”；如果frequency不是数字1、2或4，函数MDURATION返回错误值“#NUM！”；如果basis<0或basis>4，函数MDURATION返回错误值“#NUM！”；如果settlement≥maturity，函数MDURATION返回错误值“#NUM！”。修正期限的计算公式如下。

Excle 应用DURATION函数计算定期支付利息的证券的每年期限

DURATION函数用于计算假设面值￥100的定期付息有价证券的修正期限。期限定义为一系列现金流现值的加权平均值，用于计量债券价格对于收益率变化的敏感程度。DURATION函数的语法如下。

DURATION(number1,number2,...)

典型案例

已知有价证券的结算日、到期日、息票利率、收益率等信息，计算在上述条件下有价证券的修正期限。基础数据如图17-97所示。

步骤1：打开例子工作簿“DURATION.xlsx”。

步骤2：在单元格A9中输入公式“=DURATION（A2，A3，A4，A5，A6，A7）”，用于计算在上述条件下有价证券的修正期限。计算结果如图17-98所示。

图17-97　基础数据

图17-98　计算结果

使用指南

settlement、maturity、frequency和basis数值若非整数将被截尾取整。如果settlement或maturity不是合法日期，函数DURATION返回错误值“#VALUE！”；如果coupon<0或yld<0，函数DURATION返回错误值“#NUM！”；如果frequency不是数字1、2或4，函数DURATION返回错误值“#NUM！”；如果basis<0或basis>4，函数DURATION返回错误值“#NUM！”；如果settlement≥maturity，函数DURATION返回错误值“#NUM！”。

Excel 应用PV函数计算投资的现值

PV函数用于计算投资的现值。现值为一系列未来付款的当前值的累积和。例如，借入方的借入款即为贷出方贷款的现值。PV函数的语法如下。

PV(rate,nper,pmt,fv,type)

其中参数rate为各期利率。例如，如果按10%的年利率借入一笔贷款来购买汽车，并按月偿还贷款，则月利率为10%/12（即0.83%）。可以在公式中输入10%/12、0.83%或0.0083作为rate的值。

nper为总投资期，即该项投资的付款期总数。例如，对于一笔四年期按月偿还的汽车贷款，共有4×12（即48）个偿款期数。可以在公式中输入48作为nper的值。

pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt包括本金和利息，但不包括其他费用或税款。例如，￥10，000的年利率为12%的四年期汽车贷款的月偿还额为￥263.33。可以在公式中输入-263.33作为pmt的值。如果忽略pmt，则必须包含fv参数。

fv为未来值，或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。例如，如果需要在18年后支付￥50，000，则￥50，000就是未来值。可以根据保守估计的利率来决定每月的存款额。如果忽略fv，则必须包含pmt参数。

type为数字0或1，用以指定各期的付款时间是在期初还是期末。

典型案例

已知每月底一项保险年金的支出、投资收益率、付款的年限，计算在这些条件下年金的现值。基础数据如图17-95所示。

步骤1：打开例子工作簿“PV.xlsx”。

步骤2：在单元格A6中输入公式“=PV（A3/12，12*A4，A2，，0）”，用于计算在上述条件下年金的现值。计算结果如图17-96所示。

图17-95　基础数据

图17-96　计算结果

使用指南

应确认所指定的rate和nper单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。以下函数应用于年金：CUMIPMT、PPMT、CUMPRINC、PVFV、RATE、FVSCHEDULE、XIRR、IPMT、XNPV、PMT。

年金是在一段连续期间内的一系列固定的现金付款。例如汽车贷款或购房贷款就是年金。在年金函数中，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。例如，对于储户来说，￥1000银行存款可表示为参数-1，000，而对于银行来说该参数为1，000。

下面列出的是Excel进行财务运算的公式，如果rate不为0，则：

如果rate为0，则：

Excel 应用XNPV函数计算一组现金流的净现值

XNPV函数用于计算一组现金流的净现值，这些现金流不一定定期发生。如果要计算一组定期现金流的净现值，则需要使用函数NPV。XNPV函数的语法如下。

XNPV(rate,values,dates)

其中参数rate应用于现金流的贴现率。values与dates中的支付时间相对应的一系列现金流。首期支付是可选的，并与投资开始时的成本或支付有关。如果第一个值是成本或支付，则它必须是负值。所有后续支付都基于365天/年贴现。数值系列必须至少要包含一个正数和一个负数。dates与现金流支付相对应的支付日期表。第一个支付日期代表支付表的开始。其他日期应迟于该日期，但可按任何顺序排列。

典型案例

计算一组现金流的净现值。基础数据如图17-93所示。

步骤1：打开例子工作簿“XNPV.xlsx”。

步骤2：在单元格A8中输入公式“=XNPV（0.09，A2：A6，B2：B6）”，用于计算在上面的成本和收益下的投资净现值。现金流的贴现率为9%。计算结果如图17-94所示。

图17-93　基础数据

图17-94　计算结果

使用指南

dates中的数值若非整数将被截尾取整。如果任一参数为非数值型，函数XNPV返回错误值“#VALUE！”；如果dates中的任一数值不是合法日期，函数XNPV返回错误值“#VALUE”；如果dates中的任一数值先于开始日期，函数XNPV返回错误值“#NUM！”；如果values和dates所含数值的数目不同，函数XNPV返回错误值“#NUM！”。函数XNPV的计算公式如下。

式中：

d_i为第i个或最后一个支付日期。

d₁为第0个支付日期。

P_i为第i个或最后一个支付金额。

Excel 应用NPV函数计算基于一系列定期的现金流和贴现率计算的投资的净现值

NPV函数用于通过使用贴现率以及一系列未来支出（负值）和收入（正值），计算一项投资的净现值。NPV函数的语法如下。

NPV(rate,value1,value2,...)

其中参数rate为某一期间的贴现率，是一固定值。value1，value2，…代表支出及收入的1到254个参数。value1，value2，…在时间上必须具有相等间隔，并且都发生在期末。NPV使用value1，value2，…的顺序来解释现金流的顺序，所以务必保证支出和收入的数额按正确的顺序输入。如果参数为数值、空白单元格、逻辑值或数字的文本表达式，则都会计算在内；如果参数是错误值或不能转化为数值的文本，则被忽略。如果参数是一个数组或引用，则只计算其中的数字，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略。

典型案例

已知某项投资的年贴现率、一年前的初期投资、第一年的收益、第二年的收益、第三年的收益，计算该投资的净现值。基础数据如图17-91所示。

步骤1：打开例子工作簿“NPV.xlsx”。

步骤2：在单元格A8中输入公式“=NPV（A2，A3，A4，A5，A6）”，用于计算该投资的净现值。计算结果如图17-92所示。

图17-91　基础数据

图17-92　计算结果

使用指南

函数NPV假定投资开始于value1现金流所在日期的前一期，并结束于最后一笔现金流的当期。函数NPV依据未来的现金流来进行计算。如果第一笔现金流发生在第一个周期的期初，则第一笔现金必须添加到函数NPV的结果中，而不应包含在values参数中。如果n是数值参数表中的现金流的次数，则NPV的公式如下。

函数NPV与函数PV（现值）相似。PV与NPV之间的主要差别在于函数PV允许现金流在期初或期末开始。与可变的NPV的现金流数值不同，PV的每一笔现金流在整个投资中必须是固定的。函数NPV与函数IRR（内部收益率）也有关，函数IRR是使NPV等于零的比率，即有NPV（IRR（…），…）=0。

Excel 应用DISC函数计算证券的贴现率

DISC函数用于计算有价证券的贴现率。DISC函数的语法如下。

DISC(settlement,maturity,pr,redemption,basis)

其中参数settlement为证券的结算日，即在发行日之后，证券卖给购买者的日期。maturity为有价证券的到期日，即有价证券有效期截止时的日期。pr为面值￥100的有价证券的价格。redemption为面值￥100的有价证券的清偿价值。basis为日计数基准类型。

典型案例

已知有价证券的结算日、到期日、价格、清偿价值，计算在这些条件下有价证券的贴现率。基础数据如图17-89所示。

步骤1：打开例子工作簿“DISC.xlsx”。

步骤2：在单元格A8中输入公式“=DISC（A2，A3，A4，A5，A6）”，用于计算在这些条件下有价证券的贴现率。计算结果如图17-90所示。

图17-89　基础数据

图17-90　计算结果

使用指南

settlement、maturity和basis若非整数将被截尾取整。如果settlement或maturity不是合法日期，函数DISC返回错误值“#VALUE！”；如果pr≤0或redemption≤0，函数DISC返回错误值“#NUM！”；如果basis<0或basis>4，函数DISC返回错误值“#NUM！”；如果settlement≥maturity，函数DISC返回错误值“#NUM！”。