Excel 应用RATE函数计算年金的各期利率

RATE函数用于计算年金的各期利率。函数RATE通过迭代法计算得出,并且可能无解或有多个解。如果在进行20次迭代计算后,函数RATE的相邻两次结果没有收敛于0.0000001,函数RATE将返回错误值“#NUM!”。RATE函数的语法如下。


RATE(nper,pmt,pv,fv,type,guess)

有关参数nper、pmt、pv、fv及type的详细说明,可以参阅函数PV。参数nper为总投资期,即该项投资的付款期总数。pmt为各期所应支付的金额,其数值在整个年金期间保持不变。通常,pmt包括本金和利息,但不包括其他费用或税款。如果忽略pmt,则必须包含fv参数。pv为现值,即从该项投资开始计算时已经入账的款项,或一系列未来付款当前值的累积和,也称为本金。

fv为未来值,或在最后一次付款后希望得到的现金余额。如果省略fv,则假设其值为零。type为数字0或1,用以指定各期的付款时间是在期初还是期末。guess为预期利率。如果省略预期利率,则假设该值为10%。如果函数RATE不收敛,则需要改变guess的值。通常当guess位于0到1之间时,函数RATE是收敛的。

典型案例

已知贷款期限、每月支付额和贷款额,计算这些条件下的贷款月利率和年利率。基础数据如图17-19所示。

步骤1:打开例子工作簿“RATE.xlsx”。

步骤2:在单元格A6中输入公式“=RATE(A2*12,A3,A4)”,用于计算在上述条件下贷款的月利率。

步骤3:在单元格A7中输入公式“=RATE(A2*12,A3,A4)*12”,用于计算在上述条件下贷款的年利率。计算结果如图17-20所示。

图17-19 基础数据

图17-20 计算结果

使用指南

应确认所指定的guess和nper单位的一致性,对于年利率为12%的四年期贷款,如果按月支付,guess为12%/12,nper为4*12;如果按年支付,guess为12%,nper为4。

Excel 计算投资回收率函数RATE

函数RATE的功能是返回某项投资各期的利率(或回收率)。它的语法格式为:

RATE(nper,pmt,pv,fv,type)

它一共有5个参数,nper表示偿还期或投资期,pmt为各期所应支付的金额,其数值在整个年金期间保持不变,通常包括本金和利息,但不包括其他费用和税款;pv表示现值,即从该项投资开始计算时已经入账的款项,或一系列未来付款的当前值的累积和,也称为本金;fv表示在最后一次付款后希望得到的现金余额;type用以指定各期的付款时间是在期初还是期末。

例如,某公司欲进行一项投资,一次性投入资金约为20万元,如果要求在5年投资回收期中使资本增值为50万元,则年投资回收率应该不低于多少?

步骤01:新建一工作簿,在工作表中创建如图4-39所示的表格并输入数据。

图4-39 创建表格

步骤02:在单元格B5中输入公式“=RATE(B3,0,-B2,B4,0)”,按下Enter键后,计算结果为20.11%,如图4-40所示。意思是只有在回收率为20.11%的情况下,投资20万元在5年后才可能增值为50万。

图4-40 输入公式计算回收率

高手支招:在Excel中显示公式详细计算过程

对于Excel中的公式,特别是对于一些较为复杂的公式,在对公式进行错误检查时,可以使用“公式求值”命令查看公式中每一步的求值过程,它有助于用户读懂复杂的公式运算。

步骤01:选中要求值的公式所在的单元格,在“公式”选项卡中的“公式审核”组中单击“公式求值”按钮打开“公式求值”对话框。在该对话框中的“求值”框中带下画线的部分代表当前要求值的表达式,单击“求值”按钮对带下画线的表达式进行求值,如图4-41所示。

图4-41 单击“求值”按钮

步骤02:下画线会移到新的表达式,再次单击“求值”按钮,直到得到最终的结果,如图4-42所示。

图4-42 显示表达式求值结果

Excel 计算年金的各期利率:RATE函数

如果需要计算年金的各期利率,可通过“RATE”函数实现。RATE函数的语法为:=RATE(nper,pmt,pv,fv,type, guess),其中各参数的含义介绍如下。

※ nper:总投资期。

※ pmt:各期付款额。

※ pv:现值。

※ fv:未来值。

※ type:用以指定各期的付款时间是在期初(1)还是期末(0)。

※ guess:预期利率。

假设贷款金额为1万元,贷款期限为10年,每月还款500元,下面分别计算贷款的月利率和年利率。

计算贷款的月利率

01 在“B1:B3”单元格区域中分别输入贷款期限、每月支付额和贷款额。

02 在单元格中输入公式:=RATE(B1*12,B2,B3),然后按下“Enter”键确认即可。

alt

计算贷款的年利率

01 在“B1:B3”单元格区域中分别输入贷款期限、每月支付额和贷款额。

02 在单元格中输入公式:=RATE(B1*12,B2,B3)*12,然后按下“Enter”键确认即可。

alt

Excel 计算年金各期利率:RATE函数

RATE函数用于计算年金的各期利率。函数RATE通过迭代法计算得出,并且可能无解或有多个解。如果在进行20次迭代计算后,函数RATE的相邻两次结果没有收敛于0.0000001,函数RATE将返回错误值“#NUM!”。RATE函数的语法如下:


RATE(nper,pmt,pv,fv,type,guess)

nper参数为总投资期,即该项投资的付款期总数。pmt参数为各期所应支付的金额,其数值在整个年金期间保持不变;通常,pmt参数包括本金和利息,但不包括其他费用或税款;如果忽略pmt参数,则必须包含fv参数。pv参数为现值,即从该项投资开始计算时已经入账的款项,或一系列未来付款当前值的累积和,也称为本金。fv参数为未来值,或在最后一次付款后希望得到的现金余额;如果省略fv参数,则假设其值为零。type参数为数字0或1,用以指定各期的付款时间是在期初还是期末。guess参数为预期利率,如果省略,则假设该值为10%。如果函数RATE不收敛,则需要改变guess参数的值。通常当guess参数为0~1时,函数RATE是收敛的。

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

打开“RATE函数.xlsx”工作簿,切换至“Sheet1”工作表,本例的原始数据如图19-24所示。该工作表中记录了一组贷款数据,包括贷款期限、每月支付额和贷款额,要求根据给定的数据计算这些条件下的贷款月利率和年利率。具体的操作步骤如下。

图19-24 原始数据

STEP01:选中A6单元格,在编辑栏中输入公式“=RATE(A2*12,A3,A4)”,然后按“Enter”键返回,即可计算出贷款的月利率,如图19-25所示。

STEP02:选中A7单元格,在编辑栏中输入公式“=RATE(A2*12,A3,A4)*12”,然后按“Enter”键返回,即可计算出贷款的年利率,如图19-26所示。

图19-25 计算月利率

图19-26 计算年利率

应确认所指定的guess参数和nper参数单位的一致性,对于年利率为12%的4年期贷款,如果按月支付,guess参数为12%/12,nper参数为4*12;如果按年支付,guess参数为12%,nper参数为4。