常用函数.docx

常用函数.docx

《常用函数.docx》由会员分享，可在线阅读，更多相关《常用函数.docx（13页珍藏版）》请在冰豆网上搜索。

常用函数

常用函数

一、投资函数

（一）PV

1．含义：

返回投资的现值。

现值为一系列未来付款当前值的累积和。

例如，借人方的借人款即为贷出方贷款的现值。

2．语法：

PV（rate,nper,pmt,fv,type）。

其中：

rate为各期利率。

例如，如果按10％的年利率借人一笔贷款来购买汽车，并按月偿还贷款，则月利率为0.83%（即10%/12）。

可以在公式中输人10%/12或0.83％或0.0083作为rate的值。

nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。

例如，对于一笔4年期按月偿还的汽车贷款，共有48（即4x12）个偿款期次。

可以在公式中输人48作为nper的值。

pmt为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变。

通常pmt包括本金和利息，但不包括其他费用及税款。

例如，10000元的年利率为12％的4年期汽车贷款的月偿还额为263.33元。

可以在公式中输人263.33作为pmt的值。

fv为未来值或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零（一笔贷款的未来值即为零）。

例如，如果需要在18年后支付50000元，则50000元就是未来值。

可以根据保守估计的利率来决定每月的存款额。

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

如果省略type，则假设其值为零，期末付款。

说明：

应确认所指定的rate和nper单位的一致性。

例如，同样是4年期年利率为12％的贷款，如果按月支付，rat。

应为12%/12,nper应为48（即4x12）；如果按年支付，rate应为12%，nper为4。

3．示例。

假设要购买一项保险年金，该保险可以在今后20年内于每月未回报500元。

此项气金的购买成本为60000元，假定投资回报率为8。

现在可以通过函数PV计算一下这笔投资是否值得。

该项年金的现值为：

PV（0.08/12,12＊20,5000）＝一59777.15（元）

结果为负值，因为这是一笔付款，亦即支出现金流。

年金59777.15元的现值小于实际支付的60000元。

因此，这不是一项合算的投资。

（二）NPV

1．含义：

基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。

投资的净现值是指未来各期支出（负值）和收人（正值）的当前值的总和。

2．语法：

NPV（rate,value1,value2,…）。

其中：

rate为各期贴现率，是一固定值。

value1,value2,…代表1-29笔支出及收人的参数值。

（1）value1,value2,…所属各期间的长度必须相等，而且支付及收人的时间都发生在期末。

（2）NPV按次序使用value1,value2,？

来注释现金流的次序。

所以一定要保证支出和收人的数额按正确的顺序输人。

（3）如果参数是数值、空白单元格、逻辑值或表示数值的文字表达式，则都会计算在内；如果参数是错误值或不能转化为数值的文字，则被忽略。

（4）如果参数是一个数组或引用，只有其中的数值部分计算在内。

忽略数组或引用中的空白单元格、逻辑值、文字及错误值。

3．说明：

＿

（1）函数NPV假定投资开始于value1现金流所在日期的前一期，并结束于最后一笔现金流的当期。

函数NPV依据未来的现金流计算。

如果第一笔现金流发生在第一个周期的期初，则第一笔现金必须加人到函数NPV的结果中，而不应包含在values参数中。

详细内容请参阅下面的实例。

（2）如果n是values参数表中的现金流的次数，则NPV的公式如下：

（3）函数NPV与函数PV（现值）相似。

PV与NPV之间的主要差别在于：

函数PV允许现金流在期初或期末开始；而且，PV的每一笔现金流数额在整个投资中必须是固定的；而函数NPV的现金流数额是可变的。

有关年金与财务函数的详细内容，请参阅函数PV.

（4）函数NPV与函数IRR钩部收益率）也有关，函数IRR是使NPV等于零的比率：

NPV（IRR（...）,---）=00

4．示例：

假设第一年投资10000元，而未来3年梦年的收入分别为3000元，4200元和6800元。

假定每年的贴现率是10%，则投资的净现值是：

NPV（10%，一10000,3000,4200,6800）＝1188.44（元）

上述的例子中，将开始投资的10000元作为value参数的一部分。

这是因为付款发生在第一个周期的期末。

下面考虑在第一个周期的期初投资的计算方式。

假如要购买一家鞋店，投资成本为40000元，并且希望前5年的营业收人如下：

8000元，9200元，10000元，12000元和14500元。

每年的贴现率为8%（相当于通货膨胀率或竞争投资的利率）。

如果鞋店的成本及收人分别存储在B1--B6中，下面的公式可以计算出鞋店投资的净现值：

NPV（8%,B2:

B6）＋BI＝1922.06（元）

在上面的例子中，一开始投资的40000元并不包含在values参数中，因为此项付款发生在第一期的期初。

假设鞋店的屋顶在营业的第6年倒塌，估计这一年的损失为9000元，则6年后鞋店投资的净现值为：

NPV（8%,B2:

B6，一9000）＋BI＝一3749.47（元）

（三）FV

1．含义：

基于固定利率及等额分期付款方式，返回某项投资的未来值。

2．语法：

FV（rate,nper,pmt,pv,type）。

有关函数FV中各参数以及其他年金函数的详细内容，请参阅函数PV.

，rate为各期利率，是一固定值。

nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。

pmt为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变。

通常pmt包括本金和利息，但不包括其他费用及税款。

pv为现值，即从该项投资（或贷款）开始计算时已经人账的款项，或一系列未来付款当前值的累积和，也称为本金。

如果省略PV，则假设其值为零。

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

如果省略type，则假设其值为零，期末付款。

3．说明：

（1）应确认所指定的rate和nper单位的一致性。

例如，同样是4年期年利率为12％的贷款．如果按月支付，rate应为1%（即12%/12）,nper应为48（即4x12）；如果按年支付，rate应为12，nper为4。

（2）在所有参数中，支出的款项，如银行存款，表示为负数；收人的款项，如股息收人，表示为正数。

·

4．示例：

FV（0．5％，10，一200，一500,1）＝2581.40（元）

FV（1％，12，一1000）＝12682-50（元）

FV（11％/12,35，一2000，1）＝82846.25（元）

假设需要为1年后的某个项目预筹资金，现在将1000元以年利6%，按月计息（月利6%/12或0.5%）存人储蓄存款账户中，并在以后12个月的每个月初存人100元，则1年后该账户的存款额等于多少？

FV（0.5%,12，一100，一1000,1）=2301.40（元）

《四）PMT

1．含义：

基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。

2．语法：

PMT（rate,nper,pv,fv,type）。

有关函数PMT中参数的详细描述，请参阅函数PV.

rate为各期利率，是一固定值。

nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。

PV为现值，即从该项投资（或贷款）开始计算时已经人账的款项或一系列未来付款当前值的累积和，也称为本金。

fv为未来值或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。

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

如果省略type，则假设其值为零，期末付款。

3．说明：

（1）PMT返回的支付款项包括本金和利息，但不包括税款、保留支付或某些与贷款有关的费用。

（2）应确认所指定的rate和`nper单位的一致性。

例如，同样是4年期年利率为12％的贷款，如果按月支付，rate应为1%（即12%/12）,nper应为48（即4x12）；如果按年支付，rate应为12%，nper为4。

（3）如果要计算一笔款项的总支付额，请用PMT返回值乘以npero

4．示例：

下面的公式将返回需要10个月付清的年利率为8％的10000元贷款的月支付额：

PMT（8%/12,10,10000）＝一1037.03（元）

对于同一笔贷款，如果支付期限在每期的期初，支付额应为：

PMT（8%/12,10，10000,0,1）＝一1030．16（元）

如果以12％的利率贷出5000元，并希望对方在5个月内还清，下列公式将返回每月所得款数：

PMT（12%/12,5，一5000）＝1030.20（元）

除了用于贷款之外，函数PM'I，还可以计算出别的以年金方式付款的支付额。

例如，如果需要以按月定额存款方式在18年中存款50000元，假设存款年利率为6%，则函数PMT可以用来计算月存款额：

PMT（6%/12，18X12,0,50000）＝一129.08（元）

即向年利率为6％的存款账户中每月存人129.08元，18年后可获得50000元。

（五）IPMT

1．含义：

基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期间内的利息偿还额。

有关函数IPMT的参数和年金函数的详细内容，请参阅函数PV.

2．语法：

IPMT（rate,per,nper,pv,fv,type）。

其中：

rate为各期利率，是一固定值。

per用于计算其利息数额的期次，必须在1至nper之间。

nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。

pv为现值，即从该项投资（或贷款）开始计算时已经人账的款项或一系列未来付款当前值的累积和，也称为本金。

fv为未来值或在最后一次付款后希望得到的现金余额。

如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。

type为数字0或1。

用以指定各期的付款时间是在期初还是期末。

如果省略type，则假设其值为零，期末付款。

3．说明：

（1）应确认所指定的rate和nper单位的一致性。

例如，同样是4年期年利率为12％的贷款，如果按月支付，rate应为1%（即12%/12）,nper应为48

（即4X12）；如果按年支付，rate应为12%，nper为4。

（2）在所有参数中，支出的款项，如银行存款，表示为负数；收人的款项，如股息收人，表示为正数。

4．示例：

下面的公式可以计算出3年期，本金8000元，年利10％的银行贷款的第一个月的利息：

IPMT（0．1/12，1,36,8000）＝一66.67（元）

下面的公式可以计算出3年期，本金8000元，年利10％且按年支付的银行贷款的第3年的利息：

IPMT（0．1,3,3,8000）＝一292.45（元）

（六）PPMT

1．含义：

基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期间内的本金偿还额。

2．语法：

PPMT（rate,per,nper,pv,fv,type）。

有关函数PPMT中参数的详细内容，请参阅函数PV.

rate为各斯利率，是一固定值。

per用于计算其本金数额的期次，必须在1至nper之间。

nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。

pv为现值，即从该项投资（感贷款）开始计算时已经人账的款项或一系列未来付款当前值的累积和，也称为本金。

fv为未来值或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。

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

如果省略type，则假设其值为零，期末付款。

3．说明：

应确认所指定的rate和nper单位的一致性。

例如，同样是4年期年利率为12％的贷款，如果按月支付，rate应为1%（即12%/12）,nper应为48（即4X12）；如果按年支付，rate应为12%，nper为40

4．示例：

下列公式将返回2000元的年利率为10％的两年期贷款的第一个月的本金支付额：

PPMT（10%/12，1,24,2000）＝一75．62（元）

下面的公式将返回200000元的年利率为8％的10年期贷款的最后一年的本金支付额：

PPMT（80,6，10，10,200000）＝一27598.05（元）

（七）NPER

1．含义：

基于固定利率及等额分期付款方式，返回某项投资（或贷款）的总期数。

2．语法：

NPER（rate,pmt,pv,fv,type）。

有关函数NPER中各参数的详细说明及有关年金函数的详细内容，请参阅函数PV.

rate为各期利率，是一固定值。

pmt为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变。

通常pmt包括本金和利息，但不包括其他的费用及税款。

PV为现值，即从该项投资（或贷款）开始计算时已经人账的款项或一系列未来付款当前值的累积和，也称为本金。

fv为未来值或在最后一次付款后希望得到的现金余额。

如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。

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

如果省略type，则假设其值为零，期末付款。

3．示例：

NPER（12%/12，一100，一1000,10000，1）＝60

NPER（1％，一100，一1000,10000）＝60

NPER（1％，一100,1000）＝11

二、偿还率函数

（一）RATE

1．含义：

返回年金的各期利率。

函数RATE通过迭代法计算得出，并且可能无解或有多个解。

如果在进行20次迭代计算后，函数RATE的相邻两次结果没有收敛于0.000.0001，函数RATE返回错误值＃NUM!

.

2．语法：

RATE（nper,pmt,PV,fv,type,guess）。

有关参数nper、pmt、pv,fv及type的详细描述，请参阅函数PV。

nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。

pmt为各期付款额，其数值在整个投资期内保持不变。

通常pmt包括本金和利息，但不包括其他费用或税金。

pv为现值，即从该项投资（或贷款）开始计算时已经人账的款项，或一系列未来付款当前值的累积和，也称为本金。

fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。

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

如果省略type，则假设其值为零，期末付款。

guess为预期利率（估计值）：

（1）如果省略预期利率，则假设该值为10%.

（2）如果函数RATE不收敛，请改变guess的值。

通常当guess位于0~1之间时，函数RATE是收敛的。

3．说明：

应确认所指定的guess和nper单位的一致性，对于年利率为12%的4年期贷款，如果按月支付，guess为1%（即12%/12）,nper为48（即4X12）；如果按年支付，guess为12%，nper为4.

4．示例：

金额为8000元的4年期贷款，月支付额为200元，该笔贷款的利率为：

RATE（48，一200,8000）＝0.77%

因为按月计息，故结果为月利率，年利率为9.24%（即0.77%x12）.

（二）IRR

1．含义：

返回由数值代表的一组现金流的内部收益率。

这些现金流不一定要均衡，但作为年金，它们必须按固定的间隔发生，如按月或按年。

内部收益率为投资的回收利率，其中包含定期支付（负值）和收入（正值）。

2．语法：

IRR（values,guess）。

其中：

values为数组或单元格的引用，包含用来计算内部收益率的数字，values必须包含至少一个正值和一个负值，以计算内部收益率。

（1）函数IRR根据数值的顺序来解释现金流的顺序。

故应确定按需要的顺序输人了支付和收人的数值。

（2）如果数组或引用包含文本、逻辑值或空中单元格，这些数值将被忽略。

guess为对函数IRR计算结果的估计值：

（1）MicrosoftExcel使用迭代法计算函数IRR。

从guess开始，函数IRR不断修正收益率，直至结果的精度达到0.00001%。

如果函数IRR经过20次迭代，仍未找到结果，则返回错误值＃NUM!

.

（2）在大多数情况下，并不需要为函数IRR的计算提供guess值。

如果省略guess，假设它为0.1（即10%）。

（3）如果函数IRR返回错误值＃NUM!

，或结果没有靠近期望值，可以给guess换一个值再试一下。

3．说明：

函数IRR与函数NPV（净现值函数）的关系十分密切。

函数IRR计算出的收益率即为净现值为0时的利率。

下面的公式显示了函数NPV和函数IRR的相互关系：

NPV（IRR（Bl:

BO,Bl:

136）＝3．60E一08

在函数IRR计算的精度要求之中，数值3.60E一08可以当做0的有效值。

4一示例：

假设要开办一家饭店。

估计需要70000元的投资，并预期今后5年的净收益为12000元、15000元、18000元、21000元和26000元。

B1:

B6分别包含下面的数值：

一70000元、12000元、15000元、18000元、21000元和26000元。

计算此项投资4年后的内部收益率：

IRR（Bl:

B5）＝一2．1296

计算此项投资5年后的内部收益率：

IRR（B1:

B6）＝8．66%

计算2年后的内部收益率，必须在函数中包含guess

IRR（BI:

B3，一10）＝一44．35％

（三）NRR

1．含义：

返回某一连续期间内现金流的修正内部收益率。

函数MIRR同时考虑了投资的成本和现金再投资的收益率。

2．语法：

MIRR（values,finance-rate,reinvest＿rate）。

其中：

values为一个数组或对数字单元格区的引用。

这些数值代表着各期支出（负值）及收人（正值）。

（1）参数、ａｌ蕊中必须至少包含一个正值和一个负值，才能计算修正后的内部收益率，否则函数MIRR会返回错误值＃DIV/0!

o

（2）如果数组或引用中包括文字串、逻辑值或空白单元格，这些值将被忽略；但包括数值零的单元格计算在内。

finance＿rate为投人资金的融资利率。

reinvest＿rate为各期收人净额再投资的收益率。

3．说明：

函数MIRR根据输人值的次序来注释现金流的次序。

所以，务必按照实际的顺序输人支出和收人数额，并使用正确的正负号（现金流人用正值，

现金流出用负值）。

4．示例：

假设您正在从事商业性捕鱼工作，现在已经是第5个年头了。

5年前以年利率10％借款120000元买了一艘捕鱼船，这5年每年的收人分别为39000元、30000元、21000元、37000元和46000元。

其间又将所获利润用于重新投资，每年报酬率为12%，在工作表的单元格B1中输人贷款总数120000元，而这5年的年利润输人在单元格B2:

B6中。

开业5年后的修正收益率为：

MIRR（Bl:

B6，10%，12%）＝12．61％

开业3年后的修正收益率为：

MIRR（Bl:

B4，10%，12%）＝一4．80%

若以14％的reinvest＿rate计算，则5年后的修正收益率为：

MIRR（131:

B6，10%，14%）＝13.48%

三、折旧函数

（一）SLN

1．含义：

返回一项资产每期的直线折旧费。

2．语法：

SLN（cost,salvage,life）。

其中：

cost为资产原值。

salvage为资产在折旧期未的价值（也称为资产残值）。

life为折旧期限（有时也称作资产的生命周期）。

3．示例：

假设购买了一辆价值30000元的卡车，其折旧年限为10年，残值为7500元，则每年的折旧额为：

SLN（30000,7500,10）＝2250（元）

（二）DDB

1，含义：

使用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值。

2．语法：

DDB（cost,salvage,life,period,factor）。

其中：

cost为资产原值。

salvage为资产在折旧期末的价值（也称为资产残值）。

life为折旧期限（有时也可称作资产的生命周期）。

period为需要计算折旧值的期间。

period必须使用与life相同的单位。

factor为余额递减速率。

如果factor被省略，则假设为2（双倍余额递减法）。

这五个参数都必须为正数。

3．说明：

双倍余额递减法以加速速率计算折旧。

第一个期间的折旧最大，

在以后的期间依次降低。

函数DDB使用下列计算公式计算某个周期的折旧值：

\

cost一salvage（前期折旧总值）Xfactor/life

如果不想使用双倍余额递减法，可以更改factor值。

4．示例：

假定某工厂购买了一台新机器。

价值为2400元，使用期限为10年，残值为300元。

下面的例子给出几个期间内的折旧值。

结果保留两位小数。

DDB（2400,300,3650,1）＝1．32（元）

即为第一天的折旧值。

MicrosoftExcel自动设定factor为20

DDB（2400,300,120,1,2）=40.00（元）

即为第一个月的折旧值。

DDB（2400,300，10，1,2）二480.00（元）

即为第一年的折旧值。

DDB（2400,300，10,2,1.5）＝306.00（元）

即为第二年的折旧。

这里没有使用双倍余额递减法，factor为1.50

DDB（2400,300,10,10）＝22．12（元）

即为第十年的折旧值。

MicrosoftExcel自动设定factor为2.

（三）VDB

1．使用双倍递减余额法或其他指定的方法，返回指定期间内或某一时间段内的资产折旧额。

函数VDB代表可变余额递减法。

2．语法：

VDB（cost,salvage,life,start＿period,end＿period,factor,no_switch）。

其中：

cost为资产原值。

salvage为资产在折旧期未的价值（也称为资产残值）。

life为折旧期限（有时也称作资产的生命周期）。

start_period为进行折旧计算的起始期次，start_period必须与life的单位相同。

end_period为进行折旧计算的截止期次end_period必须与life的单位相同。

factor为余额递减折旧因子，如果省略参数factor，则函数假设factor为2（双倍余额递减法）。

如果不想使用双倍余额法，可改变参数factor的值。

有关双倍余额递减法的详细描述，请参阅函数DDB.

no＿switch为一逻辑值，指定当折旧值大于余额递减计算值时，是否转到直线折旧法。

（1）如果no＿swit