财务函数应用

Excel 计算年金定期支付金额：PMT函数详解

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

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

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

已知储蓄存款的年利率、计划储蓄的年数、18年内计划储蓄的数额，要求计算的金额为18年后最终得到70 000，每个月应存的数额。打开“PMT函数.xlsx”工作簿，本例中的原始数据如图19-102所示。具体的操作步骤如下。

选中A6单元格，在编辑栏中输入公式“=PMT(A2/12,A3*12,0,A4)”，然后按“Enter”键返回，即可计算出每个月应存的数额，如图19-103所示。

图19-102　原始数据

图19-103　应存数额计算结果

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

Excel 计算初始本金未来值：FVSCHEDULE函数

FVSCHEDULE函数用于基于一系列复利返回本金的未来值。函数FVSCHEDULE用于计算某项投资在变动或可调利率下的未来值。FVSCHEDULE函数的语法如下：

FVSCHEDULE(principal,schedule)

其中，principal参数为现值，schedule参数为利率数组。下面通过实例详细讲解该函数的使用方法与技巧。

打开“FVSCHEDULE函数.xlsx”工作簿，需要计算的数据说明如图19-100所示。具体的计算步骤如下所示。

选中A2单元格，在编辑栏中输入公式“=FVSCHEDULE(1,{0.09,0.11,0.1})”，然后按“Enter”键返回，即可计算出基于复利率数组{0.09,0.11,0.1}返回本金1的未来值，如图19-101所示。

schedule参数中的值可以是数字或空白单元格；其他任何值都将在函数FVSCHEDULE的运算中产生错误值“#VALUE!”。空白单元格被认为是0（没有利息）。

图19-100　原始数据

图19-101　未来值计算结果

Excel 计算未来值：FV函数详解

FV函数可以基于固定利率及等额分期付款方式，计算某项投资的未来值。FV函数的语法如下：

FV(rate,nper,pmt,pv,type)

其中，rate参数为各期利率。nper参数为总投资期，即该项投资的付款期总数。pmt参数为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt参数包括本金和利息，但不包括其他费用或税款。如果省略pmt参数，则必须包括pv参数。pv参数为现值，或一系列未来付款的当前值的累积和。如果省略pv参数，则假设其值为零，并且必须包括pmt参数。type参数为数字0或1，用以指定各期的付款时间是在期初还是期末。如果省略type参数，则假设其值为零。下面通过实例详细讲解该函数的使用方法与技巧。

打开“FV函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-98所示。该工作表中记录了某项投资的年利率、付款期总数、各期应付金额、现值等信息，要求计算在这些条件下投资的未来值。具体的操作步骤如下。

选中A8单元格，在编辑栏中输入公式“=FV(A2/12,A3,A4,A5,A6)”，用于计算在上述条件下投资的未来值，输入公式后按“Enter”键返回计算结果，如图19-99所示。

图19-98　原始数据

图19-99　计算投资的未来值

应确认所指定的rate参数和nper参数单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate参数应为12%/12，nper参数应为4*12；如果按年支付，rate参数应为12%，nper参数为4。对于所有参数，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。

Excel 将小数价格转分数价格：DOLLARFR函数

DOLLARFR函数用于将按小数表示的价格转换为按分数表示的价格。使用函数DOLLARFR可以将小数表示的金额数字，如证券价格，转换为分数型数字。DOLLARFR函数的语法如下：

DOLLARFR(decimal_dollar,fraction)

其中，decimal_dollar参数为小数。fraction参数为分数中的分母，为一个整数。下面通过实例详细讲解该函数的使用方法与技巧。

打开“DOLLARFR函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-95所示。要求将以小数表示的价格转换为以分数表示的价格。具体的操作步骤如下。

图19-95　原始数据

STEP01：选中A2单元格，在编辑栏中输入公式“=DOLLARFR(1.125,16)”，用于将按小数表示的数1.125转换为按分数表示的数（读作一又十六分之二），输入公式后按“Enter”键返回计算结果，如图19-96所示。

STEP02：选中A3单元格，在编辑栏中输入公式“=DOLLARFR(1.125,32)”，用于将按小数表示的数1.125转换为按分数表示的数（读作一又八分之一），输入公式后按“Enter”键返回计算结果，如图19-97所示。

图19-96　分母为16时的结果

图19-97　分母为32时的结果

如果fraction参数不是整数，将被截尾取整。如果fraction参数小于0，函数DOLLARFR返回错误值“#NUM!”。如果fraction参数为0，函数DOLLARFR返回错误值“#DIV/0!”。

Excel 将分数价格转换小数价格：DOLLARDE函数

DOLLARDE函数用于将按分数表示的价格转换为按小数表示的价格，使用函数DOLLARDE可以将分数表示的金额数字，如证券价格，转换为小数表示的数字。DOLLARDE函数的语法如下：

DOLLARDE(fractional_dollar,fraction)

其中，fractional_dollar参数为以分数表示的数字，fraction参数为分数中的分母，为一个整数。下面通过实例详细讲解该函数的使用方法与技巧。

打开“DOLLARDE函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-92所示。要求将以分数表示的价格转换为以小数表示的价格。具体的操作步骤如下。

STEP01：选中A2单元格，在编辑栏中输入公式“=DOLLARDE(1.5,16)”，用于将按分数表示的价格1.5（读作四又十六分之二）转换为按小数表示的价格，输入公式后按“Enter”键返回计算结果，如图19-93所示。

图19-92　目标数据

图19-93　将分数1.5转化为小数

STEP02：选中A3单元格，在编辑栏中输入公式“=DOLLARDE(1.2,32)”，用于将按分数表示的价格1.2（读作一又三十二分之二十）转换为按小数表示的价格，输入公式后按“Enter”键返回计算结果，如图19-94所示。

图19-94　将分数1.2转化为小数

如果fraction参数不是整数，将被截尾取整。如果fraction参数小于0，函数DOLLARDE返回错误值“#NUM!”。如果fraction参数为0，函数DOLLARDE返回错误值“#DIV/0!”。

Excel 计算投资期数：NPER函数详解

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

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

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

打开“NPER函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-88所示。该工作表中记录了某项投资的年利率、各期所付的金额、现值、未来值等信息，要求计算在这些条件下的总期数。具体的操作步骤如下。

STEP01：选中A8单元格，在编辑栏中输入公式“=NPER(A2/12,A3,A4,A5,1)”，然后按“Enter”键返回，即可计算出投资的总期数，如图19-89所示。

图19-88　原始数据

图19-89　计算投资的总期数

STEP02：选中A9单元格，在编辑栏中输入公式“=NPER(A2/12,A3,A4,A5)”，然后按“Enter”键返回，即可计算出投资的总期数（不包括在期初的支付），如图19-90所示。

STEP03：选中A10单元格，在编辑栏中输入公式“=NPER(A2/12,A3,A4)”，然后按“Enter”键返回，即可计算出投资的总期数（不包括未来值0），如图19-91所示。

图19-90　计算出投资的总期数（不包括在期初的支付）

图19-91　计算投资的总期数（不包括未来值0）

Excel 计算修正期限：MDURATION函数

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

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

其中，settlement参数为证券的结算日，结算日是在发行日之后，证券卖给购买者的日期。maturity参数为有价证券的到期日，到期日是有价证券有效期截止时的日期。coupon参数为有价证券的年息票利率。yld参数为有价证券的年收益率。frequency参数为年付息次数，如果按年支付，frequency=1；按半年期支付，frequency=2；按季支付，frequency=4。basis参数为日计数基准类型。下面通过实例详细讲解该函数的使用方法与技巧。

打开“MDURATION函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-86所示。该工作表中记录了一组债券数据，具体包括债券的结算日、到期日、息票利率、收益率等信息，要求根据给定的数据计算出债券的修正期限。具体的操作步骤如下。

选中A9单元格，在编辑栏中输入公式“=MDURATION(A2,A3,A4,A5,A6,A7)”，然后按“Enter”键返回，即可计算出债券的修正期限，如图19-87所示。

图19-86　原始数据

图19-87　计算债券的修正期限

如果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!”。修正期限的计算公式如下：

Excel 计算定期支付利息债券每年期限：DURATION函数

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

DURATION(settlement, maturity, coupon, yld, frenguency, basis)

打开“DURATION函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-84所示。该工作表中记录了一组有价证券数据，具体包括有价证券的结算日、到期日、息票利率、收益率等信息，要求根据给定的数据计算出有价证券的修正期限。具体的操作步骤如下。

选中A9单元格，在编辑栏中输入公式“=DURATION(A2,A3,A4,A5,A6,A7)”，然后按“Enter”键返回，即可计算出有价证券的修正期限，如图19-85所示。

图19-84　原始数据

图19-85　计算有价证券的修正期限

如果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，用以指定各期的付款时间是在期初还是期末。

下面通过实例详细讲解该函数的使用方法与技巧。

打开“PV函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-82所示。该工作表中记录了一组数据，具体包括每月底一项保险年金的支出、投资收益率、付款的年限，要求根据给定的数据计算在这些条件下年金的现值。具体的操作步骤如下。

选中A6单元格，在编辑栏中输入公式“=PV(A3/12,12*A4,A2,,0)”，然后按“Enter”键返回，即可计算出年金的现值，如图19-83所示。

图19-82　原始数据

图19-83　计算年金的现值

应确认所指定的rate参数和nper参数单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate参数应为12%/12，nper参数应为4*12；如果按年支付，rate参数应为12%，nper参数为4。

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

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

如果rate为0，则：

(pmt*nper)+pv+fv=0

Excel 计算现金净现值：XNPV函数

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

XNPV(rate,values,dates)

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

打开“XNPV函数.xlsx”工作簿，切换至“Sheet1”工作表，本例的原始数据如图19-80所示。该工作表中记录了一组数据，要求根据给定的数据计算出现金流的净现值。具体的操作步骤如下。

选中A8单元格，在编辑栏中输入公式“=XNPV(0.09,A2:A6,B2:B6)”，然后按“Enter”键返回，即可计算出现金流的净现值，如图19-81所示。

图19-80　原始数据

图19-81　计算现金净现值

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

式中：

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

d₁=第0个支付日期。

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