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

《第三章Excel在货币时间价值计算中的应用详解.ppt》由会员分享，可在线阅读，更多相关《第三章Excel在货币时间价值计算中的应用详解.ppt（60页珍藏版）》请在得力文库 - 分享文档赚钱的网站上搜索。

1、第三章第三章 EXCEL在资金时间价值计算中的应用 货币的时间价值，是财务管理的基础。这一章除了对基本理论和公式进行归纳，还重点介绍了Excel中的年金计算函数，它们是全书乃至全部金融计算的通用工具。最后设计了一组有现实背景的简单示例。2023/3/102023/3/101 1第三章第三章 货币时间价值货币时间价值 第一节 货币时间价值 一、货币的时间价值 货币的时间价值有两种表示方式：一种是绝对方式，即利息，它是一定量货币称为本金，在一定时间内产生增值的绝对数额；另一种是相对方式，即利率，它是用百分比表示的货币随时间推移所产生增值与本金之间的比率。按照计算基数的不同，利息的计算有以下两种形式

2、：单利和复利。2023/3/102023/3/102 2第三章第三章 货币时间价值货币时间价值 单利 每次计算利息时，都以本金作为计算基数。I=Prn 复利 每次计算利息时，都以上期期末的本利和作为计算基数。这时不仅要计算本金的利息，还要计算利息的利息，俗称“利滚利”。2023/3/102023/3/103 3第三章第三章 货币时间价值货币时间价值 二、货币时间价值的计算 时间价值计算公式时间价值计算公式第二节 货币时间价值函数 在Excel中，有一组用于时间价值计算的函数，这组函数之间是相互关联的，并且可以互为参数。他们通过一个公式联系起来，组成有内在联系的函数群。理解它们之间的这种内在联系

3、，对于正确使用这些函数有很大帮助。2023/3/102023/3/104 4第三章第三章 货币时间价值货币时间价值 一、函数功能及其参数 关于时间价值函数的参数说明：rate：每期利率 nper：年金处理中的总期数 pmt：每期固定支付或收入的数额，即年金 pv：初始值，为一选择性参数。如果此参数省略，则假设其值为0 fv：终值，为一选择性参数。如果此参数省略，则假设其值为0 type：年金类型。其值可以为0或1，0代表普通年金；1代表先付年金，默认值为02023/3/102023/3/105 5第三章第三章 货币时间价值货币时间价值 Excel中的常用时间价值函数的语法、参数和功能。1、FV

4、()FV()函数函数终值终值 语法参数：FV(rate,nper,pmt,pv,type)功能：计算现金流量的终值。如果pv不为零，则表明有初始现金流量。2、PV()函数现值 语法参数：PV(rate,nper,pmt,fv,type)功能：计算现金流量的现值。如果fv不为零，则表明最后一期期末有现金流量。2023/3/102023/3/106 6第三章第三章 货币时间价值货币时间价值 3、RATE()函数利率 语法参数：语法参数：RATE(nper,pmt,pv,fv,type,guess)功能：计算利率。如果计算结果不收敛，可以用guess参数给定估计值重新计算。guess默认值为10%。

5、4、NPER()函数期数 语法参数：NPER(rate,pmt,pv,fv,type)功能：计算现金流量期数2023/3/102023/3/107 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期的现金流量中的利息部分。2023/3/

6、102023/3/108 8第三章第三章 货币时间价值货币时间价值 8、CUMPRINC()函数累计本金 语法参数：CUMPRINC(rate,nper,pv,start_period,end_period,type)功能：计算年金每期现金流量累加结果中的本金部分。9、CUMIPMT()函数累计利息 语法参数：CUMIPMT(rate,nper,pv,start_period,end_period,type)功能：计算年金每期现金流量累加结果中的利息部分。2023/3/102023/3/109 9第三章第三章 货币时间价值货币时间价值 使用这些各函数时要注意其参数 1、pmt参数作为每期发生的

7、现金流量，在整个年金期间其值保持不变；2、type=0或省略表示各期现金流量发生在期末，即普通年金；type=1表示各期现金流量发生在期初，即预付年金；应确认所指定的rate和nper单位的一致性。在所有参数中，支出的款项表示为负数，收入的款项表示为正数。CUMIPMT()和CUMPRINC()函数中的参数PV必须为正，且返回的结果为负。2023/3/102023/3/101010第三章第三章 货币时间价值货币时间价值 二、函数之间的关系 如果用时间线来对现金流量进行分析，则上述9个函数可以归纳到一幅图上，从而表示出个函数之间的相互关系。见下图。PVFVNPERRATEPMTIPMTPPMTC

8、UMPRINCCUMIPMT终值现值利率期数现金流2023/3/102023/3/101111第三章第三章 货币时间价值货币时间价值 从图中可以看出：5个最基本的函数：PV()函数现值 FV()函数终值 RATE()函数利率 PMT()函数每期现金流量 NPER()函数期数，它们分布在时间线上2023/3/102023/3/101212第三章第三章 货币时间价值货币时间价值 当对应参数完全相同时，后4个函数有如下 关系：PPMT()函数、IPMT()函数 上面两个函数是分解PMT()函数得出的，即 IPMT()PPMT()PMT()CUMPRINC()函数、CUMIPMT()函数 上面两个函数

9、合起来计算在多期里的现金流量。CUMIPMT()等于IPMT()函数的累加；CUMPRINC()等于PPMT()函数的累加；CUMIPMT()CUMPRINC()nPMT()注：n为时间价值计算的期数2023/3/102023/3/101313第三章第三章 货币时间价值货币时间价值第三节 时间价值函数的示例说明 一、时间价值函数的联系 Excel的这组时间价值函数之间存在一个内在的关系式，称为时间价值基本公式：真正理解着这公式，才能掌握有关函数的具体用法，否则就会造成理解上的困难和应用上的迷惑。对上面的公式进行分析可以看出，该式左侧共包含3项：2023/3/102023/3/101414第三章

10、第三章 货币时间价值货币时间价值 第1项符合普通年金或预付年金终值的计算公式：普通年金终值公式：预付年金终值公式：第第22项就是一般的复利终值计算公式：项就是一般的复利终值计算公式：FV=P(1+r)FV=P(1+r)nn。第第33项是终值项是终值2023/3/102023/3/101515第三章第三章 货币时间价值货币时间价值 上式的左侧实际上分别是现值的终值和年金终值之和，他们与第三项相加为零。于是，形成了现值、各期现金流量与终值之间的一体化关系。这也就是为什么现值、终值符号相反的原因。根据这三项的关系，在应用中共有三种情况：fv0：可以在每期现金流量(pmt)与现值(pv)之间相互计算，

11、但两者正负相反；pv=0：可以在每期现金流量(pmt)与终值(fv)之间相互计算，但两者正负相反；pmt=0：可以在一笔固定现金额的现值(pv)与终值(fv）之间计算，但两者正负相反。2023/3/102023/3/101616第三章第三章 货币时间价值货币时间价值 二、终值和现值的计算 一次性收付的终值和现值的计算 1、终值函数FV()注意：在参数使用上，若为付出金额，则以“负”数表示；若为收入则以“正”表示 语法：FV（rate,nper,pmt,pv,type)功能：在已知期数、利率、年金和现值的条件下，返回年金终值数额。2023/3/102023/3/101717第三章第三章 货币时间

12、价值货币时间价值 例3-1：设计不同利率、期数现值的终值。设计过程：第一步：设计如图31终值计算模型；图312023/3/102023/3/101818第三章第三章 货币时间价值货币时间价值 第二步：单击插入函数向导 ，单击财务类函数FV()，其对话窗见图3-2。第三步：按函数参数的要求，填入相应参数值后，单击确定按钮。图3-22023/3/102023/3/101919第三章第三章 货币时间价值货币时间价值 2、现值的计算 语法：PV(rate,nper,pmt,fv,type)功能：在已知期数、利率、每期付款额及终值的条件下，返回现值数额。例例3 32:2:设计不同利率、期数、终值的现值。

13、设计过程：第一步：设计如图33终值计算模型；图3-32023/3/102023/3/102020第三章第三章 货币时间价值货币时间价值滚动滚动条、条、微调微调项项等等的的设计：设计：n n 功能区功能区有有开发工具开发工具的，的，直接直接点击点击开发工具开发工具控件控件插入插入n n 功能区功能区没有没有开发工具开发工具的，的，文件文件选项选项自定义自定义功能功能区区勾选勾选开发工具开发工具确定，确定，在功能在功能区区就会就会出现出现开发工开发工具。具。2023/3/102023/3/102121第三章第三章 货币时间价值货币时间价值 第二步：单击插入函数向导 ，单击财务类函数FV()，其对话

14、窗见图3-4。第三步：按函数参数的要求，填入相应参数值后，单击确定按钮图3-42023/3/102023/3/102222第三章第三章 货币时间价值货币时间价值 年金的现值、终值计算 1、年金（普通、先付）的终值计算 例33：赵女士今年30岁，计划为自己设立一个风险保障账户，从今年开始，每年年末（初）往该账户存入10000元，设年利率为6，计算到赵女士60岁时，这笔保障金为多少？建立计算模型具体过程为：第一步：如图3-5所示，建立模型结构2023/3/102023/3/102323第三章第三章 货币时间价值货币时间价值图3-5 第二步：选取H4单元格，单击公式功能模块插入函数按钮，在“逻辑”类

15、别中选择函数IF()，并按要求输入函数参数，见图3-6。2023/3/102023/3/102424第三章第三章 货币时间价值货币时间价值图3-62023/3/102023/3/102525第三章第三章 货币时间价值货币时间价值 第三步：单击开发工具功能模块控件插入滚动条项，在适当的位置划出滚动条按钮，按图3-7设置，通过滚动条控制期数图3-7滚动调按钮2023/3/102023/3/102626第三章第三章 货币时间价值货币时间价值 第四步：按第三步的做法单击第四步：按第三步的做法单击 开发工具开发工具 功能功能模模块块 控件控件 插入插入 数值数值调节钮调节钮 项项，并画出微调并画出微调按

16、钮，按图按钮，按图3-73-7设置，通过设置，通过 微调微调 按钮控制每期复利按钮控制每期复利的次数。的次数。图3-8微调按钮2023/3/102023/3/102727第三章第三章 货币时间价值货币时间价值 第五步：按第三步的操作，单击第五步：按第三步的操作，单击开发工具开发工具功能区功能区 控件控件 插入插入 组合框组合框 项，划出组合框按钮，项，划出组合框按钮，按图按图3-93-9设置。设置。组合框图3-92023/3/102023/3/102828第三章第三章 货币时间价值货币时间价值 通过组合框按钮，选择年金的类型，通过逻辑函数IF()判断计算相应的终值，计算结果见图3-5。2、年金

17、的现值计算 例3-4：某公司购买一台设备，预计可使用10年，每年可节约费用50000元。假设贴现率为10%，按普通年金和先付年金分别计算所节约费用的现值。该模型的设计过程同例3-3。区别在于本例时间价值函数的类型是PV()。见图3-10。2023/3/102023/3/102929第三章第三章 货币时间价值货币时间价值图 3-102023/3/102023/3/103030第三章第三章 货币时间价值货币时间价值 年金、年金中的本金、年金中的利息 1、年金函数PMT()语法：PMT(rate,nper,pv,fv,type)功能：在已知期数、利率、现值或终值的条件下，返回年金，即投资（或贷款）的

18、每期付款额（包括本金和利息）。2023/3/102023/3/103131第三章第三章 货币时间价值货币时间价值 例35：假定A公司贷款1000元，必须在未来3年每年年底偿还相同的金额，而银行按贷款余额的6收取利息。请你编制如下的还本付息表：年度年度支付额支付额利息利息本金偿还额本金偿还额 贷款余额贷款余额123合计还款付息表还款付息表2023/3/102023/3/103232第三章第三章 货币时间价值货币时间价值 计算过程：每期利息每期贷款余额6；每期本金偿还额每期支付额每期利息 每期贷款余额上期余额本期本金偿还额 题解过程及结果如图3-11-1、2所示2023/3/102023/3/10

19、3333第三章第三章 货币时间价值货币时间价值图3-11-12023/3/102023/3/103434第三章第三章 货币时间价值货币时间价值图3-11-22023/3/102023/3/103535第三章第三章 货币时间价值货币时间价值 2、年金中的利息函数IPMT()语法：IPMT(rate,per,nper,pv,fv,type)功能：在已知期数、利率及现值的条件下，返回投资或贷款的每期付款额中所含的利息。3、年金中的本金函数PPMT()语法：PPMT(rate,per,nper,pv,fv,type)功能：在已知期数、利率及现值的条件下，返回投资或贷款的每期付款额中所含的本金。以上三个

20、函数的关系：PMT()=PPMT()+IPMT()例36：资料同例35。求解过程见图3-11。2023/3/102023/3/103636第三章第三章 货币时间价值货币时间价值图3-122023/3/102023/3/103737第三章第三章 货币时间价值货币时间价值 利率函数RATE()语法：RATE(nper,pmt,pv,fv,type,guess)功能：在已知期数、每期付款额及现值的条件下，返回年金的每期利率。参数：guess是对利率的猜测。如果guess省略，将假设它是10 例例3 37 7：某企业租用一设备，租金36000元，若租赁期5年，每年末支付租金9016元，求贴现利率为多少

21、？求解过程见图3-12。2023/3/102023/3/103838第三章第三章 货币时间价值货币时间价值图3-132023/3/102023/3/103939第三章第三章 货币时间价值货币时间价值 期数函数NPER（）语法：NPER(rate,pmt,pv,fv,type)功能：返回每期付款金额即利率固定的某项投资或贷款的期数。例3-8：某企业租用一设备，租金36000元，年利率为8，每年末支付租金。若每年支付9016元，需要多少年支付完租金。求解过程见图3-14。2023/3/102023/3/104040第三章第三章 货币时间价值货币时间价值图3-142023/3/102023/3/10

22、4141第三章第三章 货币时间价值货币时间价值第三节 应用示例 一、教育储蓄 例39：父母准备为其子女将来的求学进行储蓄。他们计划从孩子的12岁起到他17岁，每年年初存入一定金额，从孩子18岁上大学开始到21岁，每年年初支取一定金额作为学费。首先，假定在孩子12岁到17岁每年初存款6000元，利率为3。然后，从18岁开始每年年初支取10000元，到21岁的年终。问到21岁年终时该存款的帐面余额为多少？如果其他条件不变，要求计算前6年每年初存款为多少时，可以到21岁年终收支平衡，即结余金额为零。2023/3/102023/3/104242第三章第三章 货币时间价值货币时间价值第一步：建立如图3-

23、14所示的模型结构图3-152023/3/102023/3/104343第三章第三章 货币时间价值货币时间价值 结构中公式如图3-16所示 图3-162023/3/102023/3/104444第三章第三章 货币时间价值货币时间价值 第二步：在B8:B13单元格区域中输入公式”=B6”，按数组生成键，产生数组。第三步：选取C8单元格，输入公式”=B8”，计算第一年的年初金额。第四步：在C9:C13单元格区域中输入公式”=B9:B13+E8:E12”，按数组生成键，计算后5年年初金额。第五步：在D8:D13单元格区域中输入公式”=C8:C13*B4”，按数组生成键，计算当年利息 第六步：在E8:

24、E13单元格区域中输入公式”=C8:C13+D8:D13”，按数组生成键，计算年终金额。2023/3/102023/3/104545第三章第三章 货币时间价值货币时间价值 结余金额为零的设计：单击数据功能模块模拟分析单变量求解在目标单元格键入E20目标值为0可变单元格选取B6，如图3-17所示。最后单击确定按钮。图3-172023/3/102023/3/104646第三章第三章 货币时间价值货币时间价值 二、养老金 例310:如果某人从36岁开始为自己储备养老金到65岁退休，希望从66岁到85岁每年年初支取100000元，利率为8，要求计算他在前面的储蓄中每年年初应等额存入多少金额？如果在85

25、岁时扣除全部已支取的金额外仍然有500000元，则在储蓄中每年年初应等额存入多少？在35岁开始存款时如果将已有的50000元作为本金存入，则在储蓄过程中每年年初应等额存入多少？2023/3/102023/3/104747第三章第三章 货币时间价值货币时间价值第一步：建立如图3-18-1、2所示的结构图。图3-18-12023/3/102023/3/104848第三章第三章 货币时间价值货币时间价值 养老金公式 图3-18-2 2023/3/102023/3/104949第三章第三章 货币时间价值货币时间价值 第二步：选取C4:D9，选择公式公式功能模块功能模块根据所选内容创建 最左列，见图3-

26、19，最后单击确定按钮。图3-19 第三步：在D7单元格中输入公式”=PV(利率,支取养老金的年限,-每年支取金额,1)”，计算满足20年支出的账户总额。2023/3/102023/3/105050第三章第三章 货币时间价值货币时间价值 第四步：在D9单元格中输入公式”=PMT(利率,存入养老金的年限,-养老金总额,1)”，计算满足未来20年每年支取10000元的需要。第二、三问的操作同上述第二至四步。2023/3/102023/3/105151第三章第三章 货币时间价值货币时间价值 三、助学贷款 例311：一名学生在入学时，从银行申请了国家助学贷款40000元。按照贷款合同，该生从毕业起10

27、年内分期偿还这笔贷款，每年年末偿还固定金额，假设贷款利率均为5，要求计算该生每年年末须偿还的贷款金额，以及各年偿还金额中本金和利息各为多少。该项贷款还规定，借款人毕业后可以自己选择每年偿还金额，要求是还款期限必须保证在10年以内且每年偿还金额固定。假如该生拟每年末偿还10000元，要求计算还款期。2023/3/102023/3/105252第三章第三章 货币时间价值货币时间价值 第一问的设计过程如下：第一步：设计如图3-20所示的模型结构。图3-202023/3/102023/3/105353第三章第三章 货币时间价值货币时间价值 第三步：选C5单元格，输入公式”C5=C3”，计算毕业时贷款额

28、。第四步：选C8单元格，输入公式”=PMT(利率,偿还期限,毕业时的终值)”，计算毕业后每年还款额。第五步：选C11:C20，输入公式”=PPMT(利率,B11:B20,偿还期限,毕业时的终值)”,生成数组，计算每年偿还的本金数额。第六步：选D11:D20，输入公式”=IPMT(利率,B11:B201,偿还期限,毕业时的终值)”,生成数组，计算每年偿还的利息部分。第七步：选E11:E20，输入公式”=F10:F19+C11:C20”,生成数组，计算每年偿还额。2023/3/102023/3/105454第三章第三章 货币时间价值货币时间价值 第二问设计过程：按图3-21的结构求还款年限，采用N

29、PER()函数图3-212023/3/102023/3/105555第三章第三章 货币时间价值货币时间价值 四、住房按揭 例312：西安市民赵女士购买了一套总价人民币60万元的新房，首付20万元，贷款总额40万元，其中公积金贷款10万元，期限10年，年利率4.5%，商业贷款30万元，期限20年，年利率6%。如果采用等额本息发还款，公积金贷款、商业贷款每月还款额为多少？利息、本金各多少，每月还款总额是多少？2023/3/102023/3/105656第三章第三章 货币时间价值货币时间价值 第一步：建立如图3-22所示的结构图图3-222023/3/102023/3/105757第三章第三章 货币

30、时间价值货币时间价值 第二步：在C9单元格中输入公式”=PMT(公积金利率/12,公积金年限*12,-公积金贷款)”，并将公式复制到C10:C128。计算公积金贷款的月还款额。第三步：在D9单元格中输入公式”=IPMT(公积金利率/12,B9,公积金年限*12,-公积金贷款)”，并将公式复制到D10:D128。计算公积金贷款的月利息。第四步：在E9单元格中输入公式”=PPMT(公积金利率/12,B9,公积金年限*12,-公积金贷款”，并将公式复制到E10:E128。计算公积金贷款的每月还款本金。2023/3/102023/3/105858第三章第三章 货币时间价值货币时间价值 第五步：在F9单

31、元格中输入公式”=PMT(商业贷款利率/12,商业贷款年限*12,-商业贷款)”，并将公式复制到F10:F248。计算商业贷款的月还款额。第六步：在G9单元格中输入公式”=IPMT(商业贷款利率/12,B9,商业贷款年限*12,-商业贷款)”，并将公式复制到G10:G248。计算商业贷款的月利息。第七步：在H9单元格中输入公式”=PPMT(商业贷款利率/12,B9,商业贷款年限*12,-商业贷款)”，并将公式复制到GH10:GH248。计算商业贷款每月还款本金。2023/3/102023/3/105959第三章第三章 货币时间价值货币时间价值 第八步：选I9:K248单元格区域，输入公式”=C9:E248+F9:H248”，按数组生成键，产生数组，计算每月总的还款额、利息和本金额。第九步：在C249单元格中输入公式”=SUM(C9:C248)”，计算公积金贷款还款合计，并将其复制到D249:K249，分别计算对应项目的合计数，结果见图3-22。2023/3/102023/3/106060