EXC基础及其应用 9

项目五利率与期限的计算《Excel在财务中的应用》（第五版）参考教材：《Excel在财务中的应用》高等教育出版社（第五版）目录CONTENTS01任务一利率的计算02任务二实际利率与名义利率的计算03任务三期限的计算04课后实训（附参考答案）01任务一利率的计算学习目标与任务描述任务一·利率的计算知识目标●掌握RATE函数的使用，在已知P、F、n、A等要素的情况下，能够利用该函数求出i的值。●理解财务函数中数值必须有负数的规则，能查找出不能计算的原因。素养目标●善用底线思维思考与决策，理解实现预期目标的最低控制要求。●关注国家货币政策，了解央行利率调控对经济社会发展的影响。任务描述（学习资料）江南公司为了推销一种新产品，采用“还本送利”策略，向顾客承诺5年后按“一比一”返还销售额。促销活动结束后，公司共收到20000元销售额，若不考虑产品销售成本，想让这笔款项经过5年后升值到40000元。那么，这笔款项的投资报酬率应达到多少才能实现目标？一、利率计算的基本原理任务一·利率的计算计算利率时，首先要计算出相关的资金时间价值系数，然后查阅相应的系数表（如复利终值系数表、复利现值系数表、年金终值系数表、年金现值系数表等）。若表中存在该系数，对应的利率即为所求利率；若表中没有该系数，则需查找与该系数最接近的一大一小两个系数，并通过插值法计算利率。插值法计算过程已知终值、现值、期限求利率，依据复利终值公式：20000×（F/P，i，5）＝40000，即（F/P，i，5）＝2查复利终值系数表，找出期数为5、系数最接近2的一大一小两个系数：（F/P，15%，5）＝2.0114（F/P，14%，5）＝1.9254说明报酬率介于15%与14%之间，则：（15%－i）÷（15%－14%）＝（2.0114－2）÷（2.0114－1.9254）i＝14.87%利率复利终值系数15%2.0114i214%1.9254结论：投资报酬率达到14.87%时，这笔销售款才能在5年后增长一倍。二、利用RATE函数计算预期利率任务一·利率的计算在Excel中，上述的利率推算将变得很简单。根据任务要求，选中B5单元格，输入下列公式，按回车键即可得出计算结果14.87%。=RATE(5,,20000,-40000,,8%)图5-1RATE函数的使用二、利用RATE函数计算预期利率任务一·利率的计算知识链接5-1RATE函数该函数返回年金的各期利率。其语法为：RATE(nper,pmt,pv,[fv],[type],[guess])。●参数nper、pmt、pv、fv、type的含义及使用方法同PV等函数；guess为预期利率，如果省略预期利率，则假设该值为10%。●需确认guess和nper参数单位的一致性。对于年利率为12%的4年期贷款：若按月支付，guess为12%/12，nper为4×12；若按年支付，guess为12%，nper为4。操作提示5-1RATE函数中有关参数设置的要求●输入的公式中的参数8%是一个预期利率，可以省略。●公式中既有现值，又有未来值，还有每期等额的资金收付，需假设有资金流入与流出：资金流入用正数表示，流出用负数表示。本例将未来值作为资金流出取负值，否则将无法得出计算结果。同步训练任务一·利率的计算同步训练5-1求央行票据利率某证券报报道：“央行公告显示，1年期票据发行价格为人民币96.16元，对应参考收益率为3.9933%。”请用RATE函数计算验证。（注：央行票据面值为100元）参考答案：3.9933%，=RATE(1,,96.16,-100,,)同步训练5-2有pv参数时的RATE函数计算某人现在在银行存入3600元，以后每月月末都存入800元，计划5年后存款达到60000元用于捐建希望小学，其年理财收益率应达到多少才能实现目标？参考答案：5.60%，=RATE(5×12,-800,-3600,60000,0)×12同步训练5-3nper与pmt参数的口径要对应某同学拟在毕业5年后还清50000元的助学贷款，从现在起每月月末在银行等额存入716.64元，则银行的年利率应达到多少才能实现其目标？参考答案：6.00%，=RATE(5×12,716.64,,-50000,,)×12同步训练5-4type参数的运用江南公司从银行取得一笔5年期贷款，到期一次还本付息需偿还100万元。若每年年初等额还款17.50万元，则贷款利率为多少？参考答案：4.48%，=RATE(5,-17.5,,100,1)02任务二实际利率与名义利率的计算学习目标与任务描述任务二·实际利率与名义利率的计算知识目标●掌握EFFECT函数的使用，能够利用该函数根据名义利率计算实际利率并用于财务决策。●掌握NOMINAL函数的使用，能够利用该函数根据实际利率计算名义利率并用于财务决策。素养目标●培养透过现象看本质的科学思维，理解名义与实际之间的辩证关系。●增强审慎意识，在决策中客观识别各类数据的真实意义。任务描述（学习资料）江南公司因项目投资需要，向银行借款。其中，A银行贷款年利率为6.10%，但要按季计息；B银行贷款年利率为6.20%，可以按年付息。请问公司该向哪家银行申请贷款？思考：两家银行的名义利率不同、计息方式也不同，直接比较年利率并不客观，需要换算为实际利率后再作决策。一、实际利率的计算原理任务二·实际利率与名义利率的计算名义利率与实际利率通常假定利率均为年利率，以“年”作为基本计息期，每年计算一次复利；但实际计息期不一定是1年，可能按季、按月或按日计算，如有的企业债券每半年计息一次，有的抵押贷款每季度计息一次。●当每年的复利次数超过一次时，给出的年利率为名义利率（nominalinterestrate）；●按短于1年的计息期计算复利，并将全年利息额除以年初本金，得到的利率为实际利率（effectiveinterestrate）。若一年计息一次，实际利率等于名义利率；若一年内多次计息，实际利率大于名义利率。实际利率计算公式推导设名义利率为r，实际利率为i，一年内的复利次数为m，全年利息额为I，本金为P，则：

一、实际利率的计算原理任务二·实际利率与名义利率的计算按上述公式回答任务描述中的问题，实质是比较名义利率与实际利率的大小。江南公司向A银行借款的实际利率为：

银行名义利率计息方式实际利率A银行6.10%按季计息（m=4）6.24%B银行6.20%按年付息（m=1）6.20%结论：A银行实际利率6.24%＞B银行实际利率6.20%，江南公司应选择B银行借款。显然，在一年内多次计息的情况下，实际利率将高于名义利率。二、利用EFFECT函数进行实际利率的计算任务二·实际利率与名义利率的计算在Excel中，实际利率的计算将变得很简单。在A6单元格中输入下列公式，按回车键即可得出答案6.24%，如图5-2所示。=EFFECT(6.1%,4)式中，6.1%为年名义利率，4为每年的计息次数。数学列式验证：(1+6.1%/4)^4-1＝6.24%，与函数结果一致。还可用IF函数完成贷款银行的判断：=IF(B7>B8,"B银行","A银行")。图5-2用EFFECT函数计算实际利率二、利用EFFECT函数进行实际利率的计算任务二·实际利率与名义利率的计算知识链接5-2EFFECT函数该函数利用给定的年名义利率和每年的复利期数，计算有效的年利率。其语法为：EFFECT(nominal_rate,npery)。nominal_rate为年名义利率，npery为每年的复利期数。需注意两者的时间口径要一致：若nominal_rate为年名义利率，则npery为每年的复利次数；若nominal_rate为季名义利率，则npery为每季的复利次数。同步训练5-5年实际利率的计算假设你考虑的一项投资年名义利率为5%，每月付息，想将名义利率转换为实际利率。因为一个年度有12个月，确定npery参数时应定义为12。参考答案：=EFFECT(5%,12)，实际年利率为5.12%。同步训练5-6选择付款方式某人想买车，经销商提供按月计息、年名义利率5.9%的5年期贷款；银行5年期存单年存款利率为6%。应使用车贷还是放弃存单用现金购车？参考答案：6%是实际利率，5.9%是名义利率，两者不可比。=EFFECT(5.9%,12)→6.0622%＞6%，应用现金付款，放弃存单收益。二、利用EFFECT函数进行实际利率的计算任务二·实际利率与名义利率的计算知识链接5-3NOMINAL函数该函数基于给定的年实际利率和年复利期数，返回年名义利率。其语法为：NOMINAL(effect_rate,npery)。effect_rate为年实际利率，npery为每年的复利期数；若npery为非整数，将被截尾取整。同步训练5-7若npery为非整数，将被截尾取整假设年名义利率为6.25%，每年复利5次，年实际利率为多少？若每年复利5.5次，年实际利率又为多少？参考答案：两项均为6.4082%。若npery为非整数，将被截尾取整。同步训练5-8NOMINAL函数的应用1．季度实际利率1.3543%、每季复利3次，求季度名义利率。参考答案：1.3482%，=NOMINAL(1.3543%,3)2．年实际利率5.3543%、每年复利7次，求年名义利率。参考答案：5.2354%，=NOMINAL(5.3543%,7)；3．复利8次，参考答案：5.2329%，=NOMINAL(5.3543%,8)4．年实际利率5.3543%、每年复利8.6次，参考答案：5.2329%（npery为非整数被截尾取整为8，结果与上题相同）03任务三期限的计算学习目标与任务描述任务三·期限的计算知识目标●掌握NPER函数的使用，能够在已知P、F、i、A等要素的情况下，求出n的值。素养目标●理解时间底线，明确达成目标所需的最短必要时间。任务描述（学习资料）江南公司为了推销一种新产品，采用“还本送利”策略，向顾客承诺5年后按“一比一”返还销售额。促销活动结束后，共收到20000元销售额，若不考虑产品销售成本，请让这笔款项经过5年后升值到40000元。假设江南公司当前的资产报酬率为10%，在制定“还本送利”销售策略时，确定多长的返还期限较为合理？一、期限的计算原理任务三·期限的计算在已知P、F、i、A等要素的情况下，能求出n的值，实务中可采用插值法求解。根据任务描述，这是已知终值、现值、利率求期限的问题。插值法计算过程依据复利终值公式：20000×（F/P，10%，n）＝40000，即（F/P，10%，n）＝2查复利终值系数表，找出利率为10%、系数最接近2的一大一小两个系数：（F/P，10%，7）＝1.9487（F/P，10%，8）＝2.1436对应期数为7、8，即实际期限大于7年、小于8年，则：（7－n）÷（7－8）＝（1.9487－2）÷（1.9487－2.1436）n＝7.26（年）期数复利终值系数71.9487n282.1436结论：当存期达到7.26年时，销售款才能增长一倍，采用“还本送利”策略的时间定为7.26年比较合适。二、利用NPER函数计算期限任务三·期限的计算在Excel中，上述计算将变得很简单。在B5单元格中输入下列公式，按回车键即得出计算结果7.2725，如图5-3所示。=NPER(10%,,20000,-40000,)NPER函数的计算结果7.2725年比插值法的7.26年更为精确。利率与期限的计算可互相验证：已知期限7.2725年，用=RATE(7.2725,,20000,-40000,)可反求出报酬率为10.00%。图5-3利用NPER函数计算期限二、利用NPER函数计算期限任务三·期限的计算知识链接5-4NPER函数该函数基于固定利率及等额分期付款方式，返回某项投资的总期数。其语法为：NPER(rate,pmt,pv,[fv],[type])。式中相关参数的含义及其使用方法同PV函数。操作提示5-2必须有数值为负NPER函数公式中既有现值，又有未来值，还有每期等额的资金收付。要假设资金有流入与流出，应遵从“必须有数值为负”的规则。本例将未来值作为资金流出取负值，否则不能得出计算结果。同步训练5-9动态回收期的计算若南部公司投资C方案，该方案每年带来的现金流量为4600万元，初始投资为12000万元，在资产报酬率为10%的情况下，计算公司需要多少年才能收回投资？参考答案：3.17年，=NPER(10%,-4600,12000,,)同步训练任务三·期限的计算同步训练5-10NPER函数的应用（1）若南部公司投资A方案，该方案每月给公司带来的现金流量为5万元，初始投资为120万元，在年资产报酬率为10%的情况下，计算需要多少个月才能收回投资？参考答案：26.8886月，=NPER(10%/12,5,-120,,)同步训练5-10NPER函数的应用（2）同上方案，项目投资终结后将有20万元的残值变现收入，计算公司需要多少个月才能收回投资？参考答案：22.8035月，=NPER(10%/12,5,-120,20,)，需将年资产报酬率换算为月资产报酬率，并注意资金流入流出方向。同步训练5-10NPER函数的应用（3）同上方案，项目投资终结后将有20万元的净清理支出，计算公司需要多少个月才能收回投资？参考答案：30.8398月，=NPER(10%/12,5,-120,-20,)，需要注意资金流入流出方向。04课后实训一、函数基础（判断正误）二、函数应用（附参考答案）一、函数基础（判断正误）课后实训·附参考答案参考答案以粉色标注于题后括号内（第1～8题）。1．RATE函数中的nper、pmt、pv、fv、guess参数为必选项，不能省略，否则将不能正确计算出结果。（×）2．RATE函数中，guess参数为预期利率，如果省略预期利率，则假设该值为10%。（√）3．在单元格中输入“=RATE(5,,20000,-40000,,12%)”，能得到计算结果。（√）4．在单元格中输入“=RATE(5,,20000,40000,,12%)”，能得到计算结果。（×）5．在单元格中输入“=RATE(5,,20000,-40000,,)”，能得到计算结果。（√）6．在单元格中输入“=RATE(5,,20000,-40000,,)”，不能得到计算结果。（×）7．NPER函数可以用于计算固定利率及等额分期付款方式下收回某项投资所需的总期数。（√）8．在EFFECT(nominal_rate,npery)函数中，nominal_rate为年名义利率，npery为每年的复利期数。（√）一、函数基础（判断正误）课后实训·附参考答案参考答案以粉色标注于题后括号内（第9～15题）。9．若季度利率为2%，每月付息1次，则实际利率的表达式为EFFECT(2%,3)。（√）10．在EFFECT(nominal_rate,npery)函数中，若nominal_rate为季度名义利率，则npery为每季的复利次数。（√）11．EFFECT(nominal_rate,npery)中的nominal_rate参数为实际利率。（×）12．EFFECT(nominal_rate,npery)中的npery参数在其为非整数时，将被截尾取整。（√）13．若季度实际利率为2%，每季复利期数为3次，则可以用表达式“=NOMINAL(2%,3)”正确计算其季度名义利率。（√）14．表达式“=NOMINAL(15%,8.2)”与“=NOMINAL(15%,8.8)”，两者计算出的名义利率是一样的。（√）15．表达式“=EFFECT(15%,8.2)”与“=EFFECT(15%,8.8)”，两者计算出的结果是一样的。（√）二、函数应用（第1～3题）课后实训·附参考答案第1题北方公司拟对一台设备进行更新改造，预计现在一次性支付10万元，更新后每年可为公司节约成本3万元，期望报酬率为6%。（1）这项更新至少需要使用几年才合算？参考答案：3.83年，=NPER(6%,3,-10,,)；（2）若设备报废时还有残值变现收入1万元，至少需要使用几年才合算？参考答案：3.48年，=NPER(6%,3,-10,1,)第2题根据NPER函数各参数使用的基本要求，设计一个项目投资回收期的计算模型。要求：①能录入NPER函数各种参数；②计算结果保留两位小数；③格式美观、大方；④用第1题的数据验证模型的正确性。实训结果以“××××（学号）-5-1.xls”命名保存到“E:\××（班级）\”文件夹中。参考答案：模型应设置rate、pmt、pv、fv、type参数录入区与NPER计算区，用第1题数据验证结果为3.83年（含残值时为3.48年）。第3题江南公司从银行取得一笔5年期贷款，到期一次还本付息需偿还100万元。若每年年末等额存款15.741万元，存款利率至少要达到多少才能实现还款目标？参考答案：12.00%，=RATE(5,-15.741,,100,,)，注意type参数的正确设置。二、函数应用（第4～6题）课后实训·附参考答案第4题江南公司从银行取得一笔5年期贷款，到期一次还本付息需偿还100万元。若每月月初等额存款1.2123万元，存款利率至少要达到多