4.2 计算利率和资产
例 计算资产在给定期间内的折旧值
如果需要计算一笔资产在给定期间内的折旧值,可通过“DB”函数实现。“DB”函数的语法为:=DB(cost,salvage, life,period,month),各参数的含义介绍如下。
·Cost:资产原值。
·salvage:资产在折旧期末的价值,也称为资产残值。
·life:折旧期限(有时也称作资产的使用寿命)。
·period:需要计算折旧值的期间,Period参数必须使用与life参数相同的单位。
·month:第一年的月份数,若省略则假设为“12”。
假设资产原值为100万元,使用6年后资产残值为10万元,计算资产在第一年7个月内的折旧值。
01 在B1:B3单元格中区域中分别输入资产原值、资产残值、折旧期限等数据。
02 在需要显示结果的单元格中输入公式:=DB (B1,B2,B3,1,7),然后按下“Enter”键确认即可。
例 计算有效的年利率
如果需要利用给定的名义年利率和每年的复利期数,计算有效的年利率,可通过“EFFECT”函数实现。“EFFECT”函数的语法为:= EFFECT(nominal_rate,npery),各参数的含义介绍如下。
·nominal_rate:名义利率。
·npery:每年的复利期数。
下面举例计算指定条件下的有效年利率。
01 在“B1”单元格中输入名义利率,在“B2”单元格中输入每年的复利期数。
02 在要显示结果的单元格中输入公式:=EFFECT(B1, B2),按下“Enter”键确认。
例 计算某项投资的未来值
如果需要知道某项投资的未来收益情况,如N年后的存款总额,可通过“FV”函数实现。FV函数的语法为:=FV(rate,nper,pmt,pv,type),各参数的含义介绍如下。
·Rate:各期利率。
·Nper:总投资期,即该项投资的付款期总数。
·Pmt:各期所应支付的金额,其数值在整个年金期间保持不变,通常pmt包括本金和利息,但不包括其他费用及税款,如果忽略pmt,则必须包括pv参数。
·Pv:现值,即从该项投资开始计算时已经入帐的款项,或一系列未来付款的当前值的累积和,也称为本金,如果省略PV,则假设其值为零,并且必须包括pmt参数。
·Type:数字0或1,用以指定各期的付款时间是在期初还是期末。如果省略type,则假设其值为零。
注意:由于投资是先付出金额,因此在输入计算公式时,参数“Pmt”和参数“Pv”应为负数,这样得出的计算结果才为正数,即未来的收益金额。
下面举例说明。给定条件:年利率为6%,总投资期为10年,各期应付500元,现值为500元,计算未来值。
01 在B1:B4单元格区域中分别输入年利率、付款期数、各期应付金额、现值等数据。
02 在需要显示结果的单元格中输入公式:=FV(B1/12, B2, B3,B4),按下“Enter”键确认即可。
例 计算一组现金流的内部收益率
如果需要计算由数值代表的一组现金流的内部收益率,可通过“IRR”函数实现。IRR函数的语法为:=IRR(values,guess),各参数的含义介绍如下。
·Values:为数组或单元格的引用,包含用来计算返回的内部收益率的数字。
·Guess:为对函数IRR计算结果的估计值。
下面分别举例计算投资n年后不包含估计值和包含估计值的内部收益率。
1.计算投资四年后的内部收益率
01 在“B1:B5”单元格区域中分别输入初期成本和第一年到第四年的净收入。
02 在单元格中输入公式:=IRR(B1:B5),按下“Enter”键确认即可。
2.计算两年后的内部收益率(需包含估计值)
01 在“B1:B3”单元格区域中分别输入初期成本和第一年到第二年的净收入,在“B4”单元格中输入一个估计值。
02 在需要显示结果的单元格中输入公式:=IRR(B1:B3,B4),按下“Enter”键确认即可。
例 计算连续期间内现金流的修正内部收益率
如果需要计算某一连续期间内现金流的修正内部收益率,可通过“MIRR”函数实现。MIRR 函数的语法为:=MIRR(values,finance_rate,reinvest_rate),其中各参数的含义介绍如下。
·Values:一个数组或对包含数字的单元格的引用,代表着各期的一系列支出(负值)及收入(正值)。
·Finance:现金流中使用的资金支付的利率。
·Rate:年利率。
·reinvest_rate:将现金流再投资的收益率。
下面给定资产原值和三年的收益额,计算三年后现金流的修正收益率。
01 在“B1:B4”单元格区域中分别输入资产原值,以及第一年到第三年的收益,在“B5”单元格中输入年利率,在“B6”单元格中输入再投资的收益率。
02 在单元格中输入公式:=MIRR(B1:B4,B5,B6),然后按下“Enter”键确认即可。
例 计算名义年利率
如果需要计算某条件下的名义利率,可通过“NOMINAL”函数实现。NOMINAL函数的语法为:=NOMINAL(effect_rate,npery) ,各参数的含义介绍如下。
·effect_rate:实际利率。
·npery:每年的复利期数。
假设指定实际利率为5.35%,每年的复利期数为4,计算名义年利率。
01 在“B1”单元格中指定实际利率,在“B2”单元格中指定每年的复利期数。
02 在单元格中输入公式:=NOMINAL(B1, B2),然后按下“Enter”键确认即可。
例 计算投资总期数
如果需要基于固定利率及等额分期付款方式,计算某项投资的总期数,可通过“NPER”函数实现。NPER函数的语法为:=NPER(rate,pmt,pv,fv,type),其中各参数的含义介绍如下。
·Rate:各期利率,为固定值。
·Pmt:各期所应支付的金额,其数值在整个年金期间保持不变。通常情况下,pmt包括本金和利息,但不包括其他的费用及税款。
·Pv:现值。
·Fv:未来值。
·Type:用以指定各期的付款时间是在期初(1)还是期末(0)。
下面分别计算包含未来值和不包含未来值的某项投资的总期数。
1.计算指定条件下投资的总期数(包含未来值)
01 在“B1:B5”单元格区域中分别指定年利率、各期所付金额、现值、未来值以及各期的付款时间。
02 在单元格中输入公式:=NPER(B1/12,B2,B3,B4,1),然后按下“Enter”键确认即可。
2.计算指定条件下投资的总期数(不包括未来值)
01 在“B1:B5”单元格区域中分别指定年利率、各期所付金额、现值、未来值以及各期的付款时间。
02 在单元格中输入公式:=NPER(B1/12, B2,B3),然后按下“Enter”键确认即可。
例 计算投资净现值
如果需要通过使用贴现率以及一系列未来支出(负值)和收入(正值),计算一项投资的净现值,可通过“NPV”函数实现。
NPV函数的语法为:=NPV(rate,value1,value2,…),其中各参数的含义介绍如下。
·Rate:某一期间的贴现率,为固定值。
·value1,value2,…:为1到29个参数,代表支出及收入。
下面根据表格中的指定条件,计算投资的净现值。
01 在“B1:B5”单元格区域中分别指定年贴现率、一年前的初期投资以及第一年至第三年的收益。
02 在需要显示结果的单元格中输入公式:=NPV(B1,B2, B3,B4,B5),按下“Enter”键确认即可。
例 计算年金的各期利率
如果需要计算年金的各期利率,可通过“RATE”函数实现。RATE函数的语法为:=RATE(nper,pmt,pv,fv,type, guess),其中各参数的含义介绍如下。
·Nper:总投资期。
·Pmt:各期付款额。
·Pv:现值。
·Fv:未来值。
·Type:用以指定各期的付款时间是在期初(1)还是期末(0)。
·Guess:预期利率。
假设贷款金额为1万元,贷款期限为10年,每月还款500元,下面分别计算贷款的月利率和年利率。
1.计算贷款的月利率
01 在“B1:B3”单元格区域中分别输入贷款期限、每月支付额和贷款额。
02 在单元格中输入公式:=RATE(B1*12,B2,B3),然后按下“Enter”键确认即可。
2.计算贷款的年利率
01 在“B1:B3”单元格区域中分别输入贷款期限、每月支付额和贷款额。
02 在单元格中输入公式:=RATE(B112,B2,B3)12,然后按下“Enter”键确认即可。
例 计算资产的线性折旧值
如果需要计算某项资产在一个期间中的线性折旧值,可通过“SLN”函数实现。SLN函数的语法为:=SLN(cost, salvage,life),其中各参数的含义介绍如下。
·Cost:资产原值。
·Salvage:资产在折旧期末的价值(也称为资产残值)。
·Life:折旧期限(也称资产的使用寿命)。
假设一项资产的原值为3万元,使用10年后,残值为7500元,计算该资产每年的折旧值。
01 在“B1:B3”单元格区域中分别输入资产原值、资产残值和折旧期限。
02 在单元格中输入公式:=SLN(B1,B2,B3),然后按下“Enter”键确认即可。
例 按年限折旧法计算折旧值
如果需要计算某项资产按年限总和折旧法计算的指定期间的折旧值,可通过“SYD”函数实现。SYD函数的语法为:=SYD(cost,salvage,life,per),其中各参数的含义介绍如下。
·Cost:资产原值。
·Salvage:资产在折旧期末的价值。
·Life:折旧期限。
·Per:期间。
假设一项资产的原值为3万元,使用10年后,残值为7500元,下面分别计算第一年和第十年的折旧值。
1.计算第一年的折旧值
01 在“B1:B3”单元格区域中分别输入资产原值、资产残值和折旧期限。
02 在单元格中输入公式:=SYD(B1,B2,B3,1),按下“Enter”键确认。
2.计算第十年的折旧值
01 在“B1:B3”单元格区域中分别输入资产原值、资产残值和折旧期限。
02 在单元格中输入公式:=SYD(B1,B2,B3,10),按下“Enter”键确认即可。
例 计算任何期间内的折旧值
如果需要使用双倍余额递减法或其他指定的方法,计算指定的任何期间内的资产折旧值,可通过“VDB”函数实现。VDB函数的语法为:=VDB(cost,salvage,life, start_period,end_period,factor,no_switch),其中各参数的含义介绍如下。
·Cost:资产原值。
·Salvage:资产在折旧期末的价值(也称为资产残值)。
·Life:折旧期限(也称作资产的使用寿命)。
·Start_Period:进行折旧计算的起始期间。
·end_period:进行折旧计算的截止期间。
·factor:余额递减速率(折旧因子)。
·no_switch:逻辑值。
1.计算第一天的折旧值
01 在“B1:B3”单元格区域中分别输入资产原值、资产残值和折旧期限。
02 在单元格中输入公式:=VDB(B1,B2,B3*365,0,1),然后按下“Enter”键确认即可。
2.计算第6个月与第18个月间的折旧值
01 在“B1:B3”单元格区域中分别输入资产原值、资产残值和折旧期限。
02 在单元格中输入公式:=VDB(B1,B2,B3*12,6,18),按下“Enter”键确认。
例 计算现金流的净现值
如果需要计算一组现金流的净现值,可通过“XNPV”函数实现。XNPV函数的语法为:= XNPV(rate,values,dates),各参数含义介绍如下。
·Rate:应用于现金流的贴现率;
·Values:与dates中的支付时间相对应的一系列现金流转;
·Dates:与现金流支付相对应的支付日期表,第一个支付日期代表开始。
假设指定贴现率及一系列的现金流,并输入现金流对于的支付日期,计算成本和收益的投资净现值。
01 在“B1”单元格中输入贴现率,在“B2:B6”单元格区域中输入一系列现金流,在“C2:C6”单元格区域中输入现金流对应的支付日期。
02 在单元格中输入公式:=XNPV(B1,B2:B6,C2:C6),然后按下“Enter”键确认即可。