第3章-Excel在资金时间价值计算中的应用

PAGEPAGE35第3章Excel在资金时间价值计算中的应用 23.1终值与现值的计算 23.1.1单利终值与现值 23.1.2复利终值与现值 33.2年金的终值与现值 73.2.1普通年金的终值与现值 73.2.2先付年金的终值与现值 123.2.3延期年金的终值与现值 153.3 名义利率与实际利率 193.3.1Excel实际年利率的计算 203.3.2Excel名义年利率的计算 213.4实际应用举例 223.4.1贷款年利率的计算 223.4.2贷款偿还期的计算 233.4.3等额分期付款方式下贷款年偿还额的计算 243.4.4现金流不规则分布情况下的现值计算 303.5其他几个资金时间价值的计算函数及其应用 323.5.1CUMIPMT函数 333.5.2CUMPRINC函数 343.5.3FVSCHEDULE函数 35第3章Excel在资金时间价值计算中的应用3.1终值与现值的计算终值是指现在的一笔资金按给定的利率计算所得到的未来某一时刻的价值，也称为未来值。现值是指未来的一笔资金按给定的利率计算所得到的现在时刻的价值。3.1.1单利终值与现值单利是指仅对本金计算利息，以前各期产生的利息不再计算利息的利息计算方式。（1）单利终值假设：P为现在投入的一笔资金，is单利年利率，n为计息年数，Fs为n年末的单利终值。现在投入的一笔资金，n年末的终值相当于n年末的本利之和，则：利用Excel计算单利终值非常简单，只需要在相应的单元格中输入上述计算公式即可。例如，某人现在存入银行1000元，单利年利率5%，则5年后的本利和为1250元，详见下图：（2）单利现值如果已知一笔现在的存款一定时期后按单利计息的终值，则可求出其等值的现值。由终值求现值又叫贴现或折现，贴现时所使用的利率称为贴现率或折现率，其计算公式为：利用Excel计算单利终值非常简单，只需要在相应的单元格中输入上述计算公式即可。例如，某人打算在5年后从银行取出1000元，单利年利率5%，则现在需要存入银行的金额为800元，详见下图：3.1.2复利终值与现值复利是指不仅对本金计算利息，而且对以前各期所产生的利息也计算利息的利息计算方式。（1）复利终值复利终值是指一笔资金按一定的利率复利计息时，未来某一时刻的本利和。假设：P为现在投入的一笔资金，i为复利年利率，n为计息年数，F为n年末的复利终值，则复利终值的计算公式为：利用Excel计算单利终值非常简单，只需要在相应的单元格中输入上述计算公式即可。例如，某人现在存入银行1000元，复利年利率5%，则5年后的本利和为1276.282元，详见下图：此外，复利终值还可以利用FV函数计算FV函数：基于固定利率及等额分期付款方式，返回某项投资的未来值。语法：FV(rate,nper,pmt,pv,type)Rate：为各期利率。Nper：为总投资期，即该项投资的付款期总数。Pmt：为各期所应支付的金额，其数值在整个年金期间保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。如果忽略pmt，则必须包括pv参数。Pv：为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。如果省略PV，则假设其值为零，并且必须包括pmt参数。Type：数字0或1，用以指定各期的付款时间是在期初（1）还是期末（0）。如果省略type，则假设其值为0。说明：•应确认所指定的rate和nper单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。•在所有参数中，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。上例中，用FV函数计算如下图所示：【例3-1】试利用Excel制作复利终值系数表。制作方法如下：①首先设计复利终值系数表格，如下图所示：本例只假设复利年利率从1%计算到10%（存放在单元格B2:K2），计息年数从1年到10年（存放在单元格A3:A12）。②选取单元格区域B3:K12，在公式编辑栏中输入计算公式“=(1+B2:K2/100)^A3:A12”，同时按下【Ctrl+Shift+Enter】，即可得到不同年利率和计息年数下的复利终值系数表，如下图所示：可用工具栏中“增加小数位数按钮”和“减少小数位数”按钮调整小数位数。（2）复利现值复利现值是指未来时期的一笔资金按复利贴现的现在时刻的价值。贴现是复利的反过程。在已知复利终值、贴现率和贴现期数的条件下，可求得复利现值为：利用Excel计算复利终值非常简单，只需要在相应的单元格中输入上述计算公式即可。例如，某人打算在5年后从银行取出1000元，复利年利率5%，则现在需要存入银行的金额为783.53元，详见下图：此外，复利现值还可以利用PV函数计算PV函数：返回投资的现值。现值为一系列未来付款的当前值的累积和。语法：PV(rate,nper,pmt,fv,type)Rate：为各期利率。Nper：为总投资期，即该项投资的付款期总数。Pmt：为各期所应支付的金额，其数值在整个年金期间保持不变。通常pmt包括本金和利息，但不包括其他费用及税款。如果忽略pmt，则必须包括Fv参数。Fv：为未来值，或在最后一次支付后希望得到的现金余额，如果省略fv，则假设其值为零（一笔贷款的未来值即为零）。并且必须包括pmt参数。Type：数字0或1，用以指定各期的付款时间是在期初（1）还是期末（0）。如果省略type，则假设其值为0。说明：•应确认所指定的rate和nper单位的一致性。例如，同样是四年期年利率为12%的贷款，如果按月支付，rate应为12%/12，nper应为4*12；如果按年支付，rate应为12%，nper为4。•在所有参数中，支出的款项，如银行存款，表示为负数；收入的款项，如股息收入，表示为正数。上例中，用PV函数计算如下图所示：【例3-2】利用【例3-1】制作复利终值系数表的方法，来制作复利现值系数表，格式如下图：选取单元格B3:K12，输入公式“=1/(1+B2:K2/100)^A3:A12”，同时按【Ctrl+Shift+Enter】，即可得到不同年利率和计息年数下的复利现值系数表，如下图所示：3.2年金的终值与现值年金是指一定期限内每期都有的一系列等额的收付款项。年金可按发生的时间和期限不同划分为四种类型：一是普通年金，又称后付年金，指一定期限内每期期末发生的等额款项；二是先付年金，指一定期限内每期期初发生的等额款项；三是永续年金，即无期限发生的普通年金；四是延期年金，即一定时期以后才发生的普通年金。3.2.1普通年金的终值与现值（1）普通年金的终值年金终值系数年金终值系数计算普通年金终值，可以利用Excel中提供的FV函数。FV函数的功能是基于固定利率及等额分期付款方式，返回某项投资的未来值。【例3-3】若某人在10年的期限内每年年末等额地向银行存入1000元，银行按5%复利计息，那么，此人在第10年的年末可一次性从银行取出本息多少钱？打开例3-3文件，插入FV函数，参数如下图所示：按【确定】按钮后，得到结果如下图所示：【例3-4】试利用Excel制作年金终值系数表。可直接利用年金终值计算公式，也可利用FV函数制作年金终值系数表。格式如下图：在B3中插入FV函数，参数如下图：年金终值系数反映了1元年金的终值，故此处FV函数的参数Pmt取值-1，负号表示年金现金流方向与终值方向相反。单击【确定】按钮，剪切B3公式，选取单元格B3:K12，粘贴公式到编辑栏，，同时按【Ctrl+Shift+Enter】，即可得到不同年利率和计息年数下的年金终值系数表，如下图所示：（2）普通年金的现值计算普通年金现值，可以利用Excel中提供的PV函数。PV函数的功能是返回未来若干期资金的现值。【例3-5】某人打算在今后的4年中每年等额从银行取出2000元，在银行按10%的年利率复利计息的情况下，此人现在应一次性存入银行多少钱？打开例3-5文件，插入PV函数，参数如下图所示：按【确定】按钮后，得到结果如下图所示：【例3-6】试利用Excel制作年金现值系数表。可直接利用年金现值计算公式，也可利用PV函数制作年金现值系数表。格式如下图：在B3中插入PV函数，参数如下图：年金现值系数反映了1元年金的终值，故此处PV函数的参数Pmt取值-1，负号表示年金现金流方向与现值方向相反。单击【确定】按钮，剪切B3公式，选取单元格B3:K12，粘贴公式到编辑栏，同时按【Ctrl+Shift+Enter】，即可得到不同年利率和计息年数下的年金终值系数表，如下图所示：3.2.2先付年金的终值与现值（1）先付年金的终值先付年金发生在每期期初。计算先付年金的终值利用终值函数FV。【例3-7】某人准备在今后的5年中每年年初等额存入银行8000元钱，如果银行按4%的年利率复利计息，那么第5年末此人可一次性从银行取出多少钱？在Excel中，设计表格如下：插入FV函数，参数设置如下：此处，FV函数的参数Pv省略，Type为1，表示是先付年金。单击【确定】按钮，得到如下结果：（2）先付年金的现值计算先付年金的现值利用现值函数PV。【例3-8】某企业准备在今后的3年期限内租用一台设备，按租赁合同的约定每年年初需要支付租金6000元，若贴现率为10%，那么全部租金的现值是多少？在Excel中，设计表格如下：插入PV函数，参数设置如下：此处，PV函数的参数Fv省略，Type为1，表示是先付年金。单击【确定】按钮，得到如下结果：3.2.3延期年金的终值与现值延期年金是一定时期以后才开始有的年金，其终值可直接根据普通年金终值公式计算。延期年金现值可采用两种不同的方法计算。（1）延期年金现值方法1假设前m期没有年金，m+1至m+n期有n期普通年金A，可根据普通年金现值公式先将n期的普通年金折算为m年末时刻的价值，然后再向前贴现m期，即可得到延期年金的现值。【例3-9】某人准备现在存入银行一笔钱，希望能够在第6年至第10年末每年等额从银行取出1000元钱。如果银行存款的年利率为8%，且复利计息，那么此人现在应当一次性存入银行多少钱？设计如下所示的表格：在B4插入PV函数，计算第m年末的值，参数如下：单击【确定】按钮，结果如下：在B7中插入PV函数，参数设置如下：单击【确定】按钮，结果如下：即此人现在应当一次性存入银行2717.37元钱。（2）延期年金现值方法2先假设前m期也有普通年金A，这样可得到（m+n）期的普通年金。根据普通年金现值公式计算（m+n）期普通年金的现值，再减去虚设的前m期普通年金的现值，也可得到延期年金的现值。设计如下所示的表格：在B5插入PV函数，计算“m+n期年金现值”，参数如下：单击【确定】按钮，结果如下：在B7中插入B5-PV函数，参数设置如下：单击【确定】按钮，结果如下：名义利率与实际利率在计算资金的时间价值过程中，通常情况下给定了年利率，并且以年为计息周期，每年计息一次。但在实际经济活动中，有时会出现以半年、季度、月度或更短的时间为计息周期，即每年2次、4次或12次计算复利等情况。如果每年计息的次数超过一次，那么给定的年利率仅是名义利率，按一年的实际年利息与本金之比计算的实际年利率会与给定的名义年利率不一致。（1）实际年利率与名义年利率之间的关系如下：实际年利率＝（1+名义年利率/年复利次数）年复利次数-1（2）【例】现在存1万元，银行利率8％，复利计算，5年后的本利和是多少？（每年计息一次）F=10000×（1+8％）5＝14693如果每季度计息一次：名义年利率=8%实际年利率＝（1+8％/4）4-1＝8.24％F=10000×（1+8％/4）20＝148593.3.1Excel实际年利率的计算当给定名义利率和一年内计息次数，可以利用EFFECT函数计算实际年利率：EFFECT函数：利用给定的名义年利率和每年的复利期数，计算实际的年利率。语法：EFFECT(nominal_rate,npery)Nominal_rate为名义利率。Npery为每年的复利期数。如果该函数不可用，请安装并加载“分析工具库”加载宏【例3-10】给定的名义利率为12%，按日计息，即一年内365次计息，则实际年利率的计算步骤如下：设计如下所示的表格：在B3插入财务函数EFFECT，参数如下：单击【确定】按钮，结果如下：3.3.2Excel名义年利率的计算当给定实际年利率和一年内计息次数，可以利用NOMINAL函数计算名义年利率：NOMINAL函数：基于给定的实际利率和年复利期数，返回名义年利率。NOMINAL(effect_rate,npery)Effect_rate为实际利率。Npery为每年的复利期数。如果该函数不可用，请安装并加载“分析工具库”加载宏【例3-11】给定的实际年利率为15%，按月计息，即一年内12次计息，则实际年利率的计算步骤如下：设计如下所示的表格：在B3插入财务函数NOMINAL，参数如下：单击【确定】按钮，结果如下：3.4实际应用举例在计算资金的时间价值过程中，如果给定的年利率是1%的整数倍，年限是整数年时，往往可以直接通过查表找到所需要的有关系数。但有时可能会出现给定的年利率不是1%的整数倍，年限不是整数年的情况，或反过来，已知现值、终值、年金等，需求未知的利率或相应的年限，则借助于Excel的有关函数及工具可以方便地解决类似的问题。3.4.1贷款年利率的计算【例3-12】某人向银行贷款10万元购买房子，在今后的5年中，每年年末要向银行交还2.34万元，问银行贷款的年利率是多少？解决这个问题的方法有很多，如利用RATE函数、规划求解方程法、单变量求解方程法等。下面用RATE函数进行求解。RATE函数：返回年金的各期利率。函数RATE通过迭代法计算得出，并且可能无解或有多个解。如果在进行20次迭代计算后，函数RATE的相邻两次结果没有收敛于0.0000001，函数RATE将返回错误值#NUM!。语法：RATE(nper,pmt,pv,fv,type,guess)Nper

为总投资期，即该项投资的付款期总数。Pmt

为各期付款额，其数值在整个投资期内保持不变。通常pmt包括本金和利息，但不包括其他费用或税金。如果忽略了pmt，则必须包含fv参数。Pv

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

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

数字0或1，用以指定各期的付款时间是在期初（1）还是期末（0）。如果省略type，则假设其值为0。求解步骤如下：设计如下所示的表格：在B4插入财务函数RATE，参数如下：单击【确定】按钮，结果如下：3.4.2贷款偿还期的计算【例3-13】某公司拟对原有的一台设备进行更新改造，预计现在一次支付10万元，可使公司每年成本节约2.5万元。若现在银行复利年利率为6%，那么这项更新设备至少使用几年才合算？本题可以使用投资偿还期函数NPER求解。NPER函数：基于固定利率及等额分期付款方式，返回某项投资的总期数。语法：NPER(rate,pmt,pv,fv,type)Rate为各期利率，是一固定值。Pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常，pmt包括本金和利息，但不包括其他的费用及税款。Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。Fv为未来值，或在最后一次付款后希望得到的现金余额。如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。Type数字0或1，用以指定各期的付款时间是在期初（1）还是期末（0），默认为期末。求解步骤如下：设计如下所示的表格：在B4插入财务函数NPER，参数如下：单击【确定】按钮，调整B4单元格格式后，结果如下：3.4.3等额分期付款方式下贷款年偿还额的计算3.4.3.1等额分期付款方式下贷款年偿还总额的计算【例3-14】某企业有一笔贷款，复利年利率12%，5年后到期，若到期一次还本付息，需偿还100万，那么：（1）如果每年末等额偿债，每期应偿还多少？（2）如果每年初等额偿债，每期应偿还多少？对于以上两个问题，实际上分别是普通年金终值和先付年金终值的反计算过程，都可利用PMT函数求解，求解步骤如下：设计如下所示的表格：在B4插入财务函数PMT，参数如下：单击【确定】按钮，调整B4单元格格式后，结果如下：在B5插入财务函数PMT，参数如下：单击【确定】按钮，调整B5单元格格式后，结果如下：即：（1）如果每年末等额偿债，每期应偿还15.74万；（2）如果每年初等额偿债，每期应偿还14.05万。【例3-15】某企业向银行取得借款400万元，期限4年，复利年利率8%，与银行约定每年等额偿付借款，那么：（1）如果每年末等额偿债，每期应偿还多少？（2）如果每年初等额偿债，每期应偿还多少？本题解法与上题相同，都可利用PMT函数求解，把【例3-14】中单元格中的参数改为相应值（注意400万为现值），表格的计算结果如下所示：即：（1）如果每年末等额偿债，每期应偿还120.77万；（2）如果每年初等额偿债，每期应偿还111.82万。3.4.3.2等额分期付款方式下贷款年本金偿还额的计算等额分期付款方式下贷款的年本金偿还额的计算可使用PPMT函数。PPMT函数：基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。语法：PPMT(rate,per,nper,pv,fv,type)Rate为各期利率。Per用于计算其本金数额的期数，必须介于1到nper之间。Nper为总投资期，即该项投资的付款期总数。Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款当前值的累积和，也称为本金。Fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零，也就是一笔贷款的未来值为零。Type数字0或1，用以指定各期的付款时间是在期初（1）还是期末（0），默认为期末。【例3-16】某企业向银行取得借款6000万元，期限5年，年利率10%，与银行约定以等额分期付款方式每期期末偿付借款，则每年的本金偿还额为多少？可以利用PPMT函数制作各年本金偿还额计划表，步骤如下：设计如下所示的表格：在B5单元格中插入财务函数PPMT，参数如下：单击【确定】按钮，剪切B5公式，选中B5:B9，将公式粘贴到编辑栏，按【Ctrl+Shift+Enter】，结果如下：即：第1年的本金偿还额为982.78万；第2年的本金偿还额为1081.06万；……3.4.3.3等额分期付款方式下贷款年利息的计算等额分期付款方式下贷款年利息的计算可使用IPMT函数。IPMT：基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额。语法：IPMT(rate,per,nper,pv,fv,type)Rate为各期利率。Per用于计算其利息数额的期数，必须在1到nper之间。Nper为总投资期，即该项投资的付款期总数。Pv为现值，即从该项投资开始计算时已经入帐的款项，或一系列未来付款的当前值的累积和，也称为本金。Fv为未来值，或在最后一次付款后希望得到的现金余额。如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零）。Type数字0或1，用以指定各期的付款时间是在期初（1）还是期末（0），默认为期末。【例3-17】在【例3-16】中，用IPMT函数计算各年的利息偿还额，步骤如下：在C5单元格中插入财务函数IPMT，参数如下：单击【确定】按钮，剪切C5公式，选中C5:C9，将公式粘贴到编辑栏，按【Ctrl+Shift+Enter】，结果如下：即：第1年的利息偿还额为600万；第2年的本金偿还额为501万；……3.4.4现金流不规则分布情况下的现值计算3.4.4.1不规则现金流在某年内均匀发生的情况【例3-18】某企业在第1~5年的现金流分别为300元、600元、400元、800元和500元，年利率10%，但按年、半年、季、月和天计息，且各期现金流在某年内均匀发生时，各种情况下的现值如何？此时，由于各期现金流在某年内均匀发生，它们可以被均匀分割，故现值计算公式为：式中，PV为不同计息周期时的现值；Ft为第t年的现金流；n为年数；m为每年计息的次数；i为年利率。这里认为各年的现金流及利率被均匀分割，即第t年每个计息期的现金流为Ft/m，利率为i/m。由于各年的现金流不同，故不能直接用现值函数PV计算。而必须对上述公式进行简化，得：利用Excel计算不同情况下的现值，步骤如下：设计如下所示的表格：在B5单元格中插入现值计算公式“=(1-(1+$G$2/B4)^-B4)/$G$2*SUMPRODUCT($B$2:$F$2,(1+$G$2/B4)^-(B4*($B$1:$F$1-1)))”，同时按【Ctrl+Shift+Enter】，其中SUMPRODUCT参数如下：复制B5公式到C5:F5，结果如下：即：按年计息的现金流现值为1925.99元，按半年计息的现金流现值为1960.46元，……3.4.4.2现金流在某期末一次性发生的情况【例3-19】以例3-18为例，假设各期现金流在某年末一次性发生，计算过程如下：设计如下所示的表格：在C3单元格中插入财务函数PV，参数如下：单击【确定】按钮，剪切C3公式，选中C3:G7，将公式粘贴到编辑栏，按【Ctrl+Shift+Enter】，结果如下：在H3中插入求和公式“=SUM(C3:G3)”，复制到H4:H7中，结果如下：即：按年计息的现金流现值为1925.99元，按半年计息的现金流现值为1912.64元，……3.5其他几个资金时间价值的计算函数及其应用除了前面已经介绍的PV函数、FV函数、EFFECT函数、NOMINAL函数、RATE函数、NPER函数、PMT函数、PPMT函数、IPMT函数外，还有几个常用的资金时间价值的计算函数，下面分别介绍。3.5.1CUMIPMT函数CUMIPMT：返回一笔贷款在给定的start-period到end-period期间累计偿还的利息数额。如果该函数不可用，并返回错误#NAME?，请安装并加载“分析工具库”加载宏。语法：CUMIPMT(rate,nper,pv,start_period,end_period,type)Rate为利率。Nper为总付款期数。Pv为现值。Start_period为计算中的首期，付款期数从1开始计数。End_period为计算中的