第三章 Excel在货币时间价值计算中的应用

单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,杜茂宝,*,第三章 货币时间价值,第三章,EXCEL,在资金时间价值计算中的应用,货币的时间价值，是财务管理的基础。这一章除了对基本理论和公式进行归纳，还重点介绍了,Excel,中的年金计算函数，它们是全书乃至全部金融计算的通用工具。最后设计了一组有现实背景的简单示例。,计算机财务管理,2024/9/21,1,杜茂宝,第一节 货币时间价值,一、货币的时间价值,货币的时间价值有两种表示方式：一种是绝对方式，即利息，它是一定量货币,称为本金，在一定时间内产生增值的绝对数额；另一种是相对方式，即利率，它是用百分比表示的货币随时间推移所产生增值与本金之间的比率。,按照计算基数的不同，利息的计算,有以下两种形式：单利和复利。,2024/9/21,2,杜茂宝, 单利,每次计算利息时，都以本金作为计算基数。,I = Prn, 复利,每次计算利息时，都以上期期末的本利和作为计算基数。这时不仅要计算本金的利息，还要计算利息的利息，俗称“利滚利”。,I = P(1+r)n1,2024/9/21,3,杜茂宝,二、货币时间价值的计算,时间价值计算公式,第二节 货币时间价值函数,在,Excel,中，有一组用于时间价值计算的函数，这组函数之间是相互关联的，并且可以互为参数。他们通过一个公式联系起来，组成有内在联系的函数群。理解它们之间的这种内在联系，对于正确使用这些函数有很大帮助。,2024/9/21,4,杜茂宝,一、函数功能及其参数,关于时间价值函数的参数说明：,rate：,每期利率,nper,：年金处理中的总期数,pmt：,每期固定支付或收入的数额，即年金,pv：,初始值，为一选择性参数。如果此参数省略，则假设其值为0,fv：,终值，为一选择性参数。如果此参数省略，则假设其值为0,type：,年金类型。其值可以为0或1，,0,代表普通年金；,1,代表先付年，默认值为0,2024/9/21,5,杜茂宝, Excel,中的常用时间价值函数的语法、参数和功能。,1、,FV( ),函数,终值,语法参数：,FV(rate,nper,pmt,pv,type),功能：计算现金流量的终值。如果,pv,不为零，则表明有初始现金流量。,2、,PV( ),函数,现值,语法参数：,PV(rate,nper,pmt,fv,type),功能：计算现金流量的现值。如果,fv,不为零，则表明最后一期期末有现金流量 。,2024/9/21,6,杜茂宝,3、,RATE(),函数,利率,语法参数：,RATE(nper,pmt,pv,fv,type,guess),功能：计算利率。如果计算结果不收敛，可以用,guess,参数给定估计值重新计算。,guess,默认值为10%。,4、,NPER(),函数,期数,语法参数：,NPER(rate,pmt,pv,fv,type),功能：计算现金流量期数,2024/9/21,7,杜茂宝,5、,PMT(),函数,年金,语法参数：,PMT(rate,nper,pv,fv,type),功能：计算年金每期现金流量。,6、,PPMT(),函数,本金,语法参数：,PPMT(rate,per,nper,pv,fv,type),功能,:,计算年金第,per,的现金流量中本金部分,7、,IPMT(),函数,利息,语法参数：,IPMT(rate,per,nper,pv,fv,type),功能：计算年金第,per,期的现金流量中的利息部分。,2024/9/21,8,杜茂宝,8、,CUMPRINC(),函数,累计本金,语法参数：,CUMPRINC(rate,nper,pv,start_period,end_period,type),功能：计算年金每期现金流量累加结果中的本金部分。,9、,CUMIPMT(),函数,累计利息,语法参数：,CUMIPMT(rate,nper,pv,start_period,end_period,type),功能：计算年金每期现金流量累加结果中的利息部分。,2024/9/21,9,杜茂宝, 使用这些各函数时要注意其参数,1、,pmt,参数作为每期发生的现金流量，在整个年金期间其值保持不变；,2、,type=0,或省略表示各期现金流量发生在期末，即普通年金；,type=1,表示各期现金流量发生在期初，即预付年金；,应确认所指定的,rate,和,nper,单位的一致性。,在所有参数中，支出的款项表示为负数，收入的款项表示为整数。,CUMIPMT(),和,CUMPRINC(),函数中的参数,PV,必须为正，且返回的结果为负,。,2024/9/21,10,杜茂宝,二、函数之间的关系,如果用时间线来对现金流量进行分析，则上述9个函数可以归纳到一幅图上，从而表示出个函数之间的相互关系。见下图。,PV,FV,NPER,RATE,PMT,IPMT,PPMT,CUMPRINC,CUMIPMT,终值,现值,利率,期数,现金流,2024/9/21,11,杜茂宝,从图中可以看出：,5,个最基本的函数：,PV(),函数,现值,FV(),函数,终值,RATE(),函数,利率,PMT(),函数,每期现金流量,NPER(),函数,期数，它们分布在时间线上,2024/9/21,12,杜茂宝,当对应参数完全相同时，后,4,个函数有如下,关系：,PPMT(),函数、,IPMT(),函数,上面两个函数是分解,PMT(),函数得出的，即,IPMT(),PPMT(),PMT(),CUMPRINC(),函数、,CUMIPMT(),函数,上面两个函数合起来计算在多期里的现金流量。,CUMIPMT(),等于,IPMT(),函数的累加；,CUMPRINC(),等于,PPMT(),函数的累加；,CUMIPMT(),CUMPRINC()n,PMT(),注：,n,为时间价值计算的期数,2024/9/21,13,杜茂宝,第三节 时间价值函数的示例说明,一、时间价值函数的联系,Excel,的这组时间价值函数之间存在一个内在的关系式，称为时间价值基本公式：,真正理解着这公式，才能掌握有关函数的具体用法，否则就会造成理解上的困难和应用上的迷惑。对上面的公式进行分析可以看出，该式左侧共包含3项：,2024/9/21,14,杜茂宝,第1项符合普通年金或预付年金终值的计算公式：普通年金终值公式：,预付年金终值公式：,第2项就是一般的复利终值计算公式：,FV = P(1 + r),n,。,第3项是终值,2024/9/21,15,杜茂宝,上式的左侧实际上分别是现值的终值和年金终值之和，他们与第三项相加为零。于是，形成了现值、各期现金流量与终值之间的一体化关系。这也就是为什么现值、终值符号相反的原因。根据这三项的关系，在应用中共有三种情况：,fv0：,可以在每期现金流量(,pmt),与现值(,pv),之间相互计算，但两者正负相反；,pv=0：,可以在每期现金流量(,pmt),与终值,(fv),之间相互计算，但两者正负相反；,pmt=0：,可以在一笔固定现金额的现值(,pv),与终值(,fv）,之间计算，但两者正负相反。,2024/9/21,16,杜茂宝,二、终值和现值的计算,一次性收付的终值和现值的计算,1,、终值函数,FV(),注意：在参数使用上，若为付出金额，则以,“,负,”,数表示；若为收入则以,“,正,”,表示,语法：,FV（rate,nper,pmt,pv,type),功能：在已知期数、利率、年金和现值的条件下，返回年金终值数额。,2024/9/21,17,杜茂宝,例,3-1,：设计不同利率、期数现值的终值。,设计过程：,第一步：设计如图,3,1,终值计算模型；,图,3,1,2024/9/21,18,杜茂宝,第二步：,单击插入函数向导 ，单击财务类函数,FV(),，其对话窗见图,3-2,。,第三步：按函数参数的要求，填入相应参数值后，单击,确定按钮。,图,3-2,2024/9/21,19,杜茂宝,2,、现值的计算,语法：,PV(rate,nper,pmt,fv,type),功能：在已知期数、利率、每期付款额及终值的条件下，返回现值数额。,例32:,设计不同利率、期数、终值的现值。,设计过程：,第一步：设计如图,3,3,终值计算模型；,图,3-3,2024/9/21,20,杜茂宝,第二步：,单击插入函数向导 ，单击财务类函数,FV(),，其对话窗见图,3-4,。,第三步：按函数参数的要求，填入相应参数值后，单击,确定,按钮,图,3-4,2024/9/21,21,杜茂宝,年金的现值、终值计算,1,、年金（普通、先付）的终值计算,例,3,3,：赵女士今年,30,岁，计划为自己设立一个风险保障账户，从今年开始，每年年末（初）往该账户存入,10000,元，设年利率为,6,，计算到赵女士,60,岁时，这笔保障金为多少？,建立计算模型具体过程为：,第一步：如图,3-5,所示，建立模型结构,2024/9/21,22,杜茂宝,图,3-5,第二步：选取,H4,单元格，单击,插入函数,按钮，在“逻辑,”,类别中选择函数,IF (),，并按要求输入函数参数，见图,3-6,。,2024/9/21,23,杜茂宝,图,3-6,2024/9/21,24,杜茂宝,第三步：单击,视图,菜单,工具,窗体,滚动条,项，在适当的位置划出滚动条按钮，按图,3-7,设置，通过,滚动条,控制期数,图,3-7,滚动调按钮,2024/9/21,25,杜茂宝,第四步：按第三步的做法单击,窗体,中的,微调,项，并画出微调按钮，按图,3-7,设置，通过,微调,按钮控制每期复利的次数。,图,3-8,微调按钮,2024/9/21,26,杜茂宝,第五步：按第三步的操作，单击,组合框,项，划出组合框按钮，按图,3-9,设置。,组合框,图,3-9,2024/9/21,27,杜茂宝,通过组合框按钮，选择年金的类型，通过逻辑函数,IF(),判断计算相应的终值，计算结果见图,3-5,。,2,、年金的现值计算,例,3-4,：某公司购买一台设备，预计可使用,10,年，每年可节约费用,50000,元。假设贴现率为,10%,，按普通年金和先付年金分别计算所节约费用的现值。,该模型的设计过程同例,3-3,。 区别在于本例时间价值函数的类型是,PV(),。见图,3-10,。,2024/9/21,28,杜茂宝,图,3-10,2024/9/21,29,杜茂宝, 年金、年金中的本金、年金中的利息,1、年金函数,PMT( ),语法：,PMT(rate,per,nper,pv,fv,type),功能：在已知期数、利率、现值或终值的条件下，返回年金，即投资（或贷款）的每期付款额（包括本金和利息）。,2024/9/21,30,杜茂宝,例3,5,：假定,A,公司贷款1000元，必须在未来3年每年年底偿还相同的金额，而银行按贷款余额的6收取利息。请你编制如下的还本付息表：,年度,支付额,利息,本金偿还额,贷款余额,1,2,3,合计,还款付息表,2024/9/21,31,杜茂宝,计算过程：,每期利息每期贷款余额,6；,每期本金偿还额每期支付额每期利息,每期贷款余额上期余额本期本金偿还额,题解过程,及结果如图,3-11-1,、,2,所示,2024/9/21,32,杜茂宝,图,3-11-1,2024/9/21,33,杜茂宝,图,3-11-2,2024/9/21,34,杜茂宝,2、年金中的利息函数,IPMT(),语法：,IPMT(rate,per,nper,pv,fv,type),功能：在已知期数、利率及现值的条件下，返回投资或贷款的每期付款额中所含的利息。,3、年金中的本金函数,PPMT(),语法：,PPMT(rate,per,nper,pv,fv,type),功能：在已知期数、利率及现值的条件下，返回投资或贷款的每期付款额中所含的本金。,以上三个函数的关系：,PMT()=PPMT()+IPMT(),例3,6,：,资料同例3,5,。,求解过程见图,3-11,。,2024/9/21,35,杜茂宝,图,3-12,2024/9/21,36,杜茂宝, 利率函数,RATE( ),语法：,RATE(rate,pmt,pv,fv,type,guess),功能：在已知期数、每期付款额及现值的条,件下，返回年金的每期利率。,参数：,guess,是对利率的猜测。如果,guess,省,略，将假设它是10,例3,7,：某企业租用一设备，租金36000元，若租赁期5年，每年末支付租金9016元，求贴现利率为多少？,求解过程见图,3-12,。,2024/9/21,37,杜茂宝,图,3-13,2024/9/21,38,杜茂宝, 期数函数,NPER（ ）,语法：,NPER(rate,pmt,pv,fv,type),功能：返回每期付款金额即利率固定的某项投资或贷款的期数。,例3-,8,：某企业租用一设备，租金36000元，年利率为8，每年末支付租金。若每年支付9016元，需要多少年支付完租金。,求解过程见图,3-14,。,2024/9/21,39,杜茂宝,图,3-14,2024/9/21,40,杜茂宝,第三节 应用示例,一、教育储蓄,例3,9,：,父母准备为其子女将来的求学进行储蓄。他们计划从孩子的12岁起到他17岁，每年年初存入一定金额，从孩子18岁上大学开始到21岁，每年年初支取一定金额作为学费。,首先，假定在孩子12岁到17岁每年初存款6000元，利率为3。然后，从18岁开始每年年初至取10000元，到21岁的年终。问到21岁年终时该存款的帐面余额为多少？,如果其他条件不变，要求计算前6年每年初存款为多少时，可以到21岁年终收支平衡，即结余金额为零。,2024/9/21,41,杜茂宝,第一步：建立如图,3-14,所示的模型结构图,3-15,2024/9/21,42,杜茂宝,结构中公式如图,3-16,所示 图,3-16,2024/9/21,43,杜茂宝,第二步：在,B8:B13,单元格区域中输入公式,” =B6”,，按数组生成键，产生数组。,第三步：选取,C8,单元格，输入公式,” =B8”,，计算第一年的年初金额。,第四步：在,C9:C13,单元格区域中输入公式,” =B9:B13+E8:E12”,，按数组生成键，计算后,5,年年初金额。,第五步：在,D8:D13,单元格区域中输入公式,”,=C8:C13*B4,”,，按数组生成键，计算当年利息,第六步：在,E8:E13,单元格区域中输入公式,”,=C8:C13+D8:D13,”,，按数组生成键，计算年终金额。,2024/9/21,44,杜茂宝,结余金额为零的设计：,单击,工具,单变量求解,在,目标单元格,键入,E20,目标值,为,0,可变单元格,选取,B6,，如图,3-17,所示。最后单击,确定,按钮。,图,3-17,2024/9/21,45,杜茂宝,二、养老金,例3,10,:,如果某人从36岁开始为自己储备养老金到65岁退休，希望从66岁到85岁每年年初只取100000元，利率为8，要求计算他在前面的储蓄中每年年初应等额存入多少金额？,如果在85岁时扣除全部以支取的金额外仍然有500000元，则在储蓄中每年年初应等额存入多少？,在35岁开始存款时如果将已有的50000元作为本金存入，则在储蓄过程中每年年初应等额存入多少？,2024/9/21,46,杜茂宝,第一步：建立如图,3-18-1,、,2,所示的结构图。,图,3-18-1,2024/9/21,47,杜茂宝,养老金公式 图,3-18-2,2024/9/21,48,杜茂宝,第二步：选取,C4:D9,，单击,插入,名称, ,制定, ,最左列,，见图,3-19,，最后单击,确定,按钮。,图,3-19,第三步：在,D7,单元格中输入公式,” =PV(,利率,支取养老金的年限,-,每年支取金额,1)”,，计算满足,20,年支出的账户总额。,2024/9/21,49,杜茂宝,第四步：在,D9,单元格中输入公式,” =PMT(,利率,存入养老金的年限,-,养老金总额,1)”,，计算满足未来,20,年每年支取,10000,元的需要。,第二、三问的操作同上述第二至四步。,2024/9/21,50,杜茂宝,三、助学贷款,例,3,11,：,一名学生在入学时，从银行申请了国家助学贷款4000,0,元。按照贷款合同，该生从毕业起10年内分期偿还这笔贷款，每年年终偿还固定金额，假设贷款利率均为5，要求计算该生每年年末须偿还的贷款金额，以及各年偿还金额中本金和利息各为多少。,该项贷款还规定，借款人毕业后可以自己选择每年偿还金额，要求是还款期限必须保证在10年以内且每年偿还进而固定。假如该生拟每年初偿还10000元，要求计算还款期。,2024/9/21,51,杜茂宝,第一问的设计过程如下：,第一步：设计如图,3-20,所示的模型结构。,图,3-20,2024/9/21,52,杜茂宝,第三步：选,C5,单元格，输入公式,”C5=C3”,，计算毕业时贷款额。,第四步：选,C8,单元格，输入公式,”,=PMT(利率,偿还期限,毕业时的终值),”,，计算毕业后每年还款额。,第五步：选,C11:C20,，输入公式,” =PPMT(,利率,B11:B20,偿还期限,毕业时的终值,)”,生成数组，计算每年偿还的本金数额。,第六步：选,D11:D20,，输入公式,”=IPMT(,利率,B11:B201,偿还期限,毕业时的终值,)”,生成数组，计算每年偿还的利息部分。,第七步：选,E11:E20,，输入公式,”,=F10:F19+C11:C20,”,生成数组，计算每年偿还额。,2024/9/21,53,杜茂宝,第二问设计过程：,按图,3-21,的结构求还款年限，采用,NPER(),函数,图,3-21,2024/9/21,54,杜茂宝,四、住房按揭,例31,2,：,西安市民赵女士购买了一套总价人民币,60,万元的新房，首付,20,万元，贷款总额,40,万元，其中公积金贷款,10,万元，期限,10,年，年利率,4.5%,，商业贷款,30,万元，期限,20,年，年利率,6%,。如果采用等额本息发还款，公积金贷款、商业贷款每月还款额为多少？ 利息、本金各多少，每月还款总额是多少？,2024/9/21,55,杜茂宝,第一步：建立如图,3-22,所示的结构图,图,3-22,2024/9/21,56,杜茂宝,第二步：在,C9,单元格中输入公式,” =PMT(,公积金利率,/12,公积金年限*,12,-,公积金贷款,)”,，并将公式复制到,C10:C128,。计算公积金贷款的月还款额。,第三步：在,D9,单元格中输入公式,” =IPMT(,公积金利率,/12,B9,公积金年限*,12,-,公积金贷款,)”,，并将公式复制到,D10:D128,。计算公积金贷款的月利息。,第四步：在,E9,单元格中输入公式,” =PPMT(,公积金利率,/12,B9,公积金年限*,12,-,公积金贷款,”,，并将公式复制到,E10:E128,。计算公积金贷款的每月还款本金。,2024/9/21,57,杜茂宝,第五步：在,F9,单元格中输入公式,”=PMT(商业贷款利率/12,商业贷款年限*12,-商业贷款)”,，并将公式复制到,F10:F248,。计算商业贷款的月还款额。,第六步：在,G9,单元格中输入公式,”=IPMT(商业贷款利率/12,B9,商业贷款年限*12,-商业贷款)”,，并将公式复制到,G10:G248,。计算商业贷款的月利息。,第七步：在,H9,单元格中输入公式,”=PPMT(商业贷款利率/12,B9,商业贷款年限*12,-商业贷款)”,，并将公式复制到,GH10:GH248,。计算商业贷款每月还款本金。,2024/9/21,58,杜茂宝,第八步：选,I9:K248,单元格区域，输入公式,”=C9:E248+F9:H248”,，按数组生成键，产生数组，计算每月总的还款额、利息和本金额。,第九步：在,C249,单元格中输入公式,” =SUM(C9:C248)”,，计算公积金贷款还款合计，并将其复制到,D249:K249,，分别计算对应项目的合计数，结果见图,3-20,。,2024/9/21,59,杜茂宝,