Excel在经济管理中的应用课件第10章Excel在金融理财中的应用

1、 第十章 Excel在金融理财中的应用 2掌握FV、PV、PMT、RATE等财务函数的使用方法, 达到举一反三处理问题的能力理解复利、现值、未来值、等额本金、等额本息等相关概念熟练掌握银行储蓄、金融贷款中的相关概念，并能就实际问题提出解决方案能正确运用EXCEL的数据模拟分析工具分析解决实际问题本章学习目标2掌握FV、PV、PMT、RATE等财务函数的使用方法, 达3 目前Excel工具被广泛应用在金融计算中，Excel的公式、函数、图表、假设分析、数据分析等功能已成为计算货币时间价值、投资决策指标、债券，以及评价投资风险与收益等方面的不可缺少的工具。本节通过金融理财的实际案例，主要介绍Exc

2、el工具在银行储蓄收益计算方面的应用。10.1 银行储蓄收益计算10.1.1 定额定投型储蓄收益 在银行提供的众多理财方案中，像零存整取、教育储蓄、养老保险等都属于定额定投型储蓄方案，客户在进行投资时都希望预先知道未来值，从而选择最优方案，本节将介绍使用EXCEL函数FV计算未来值的方法。下面介绍FV函数相关知识要点。3 目前Excel工具被广泛应用在金融计算中，Exce4案例要求老王从银行购买了一款保险，约定每月向银行存入2000元，连续缴存5年，期满银行一次性给付，年利率为6%，5年后老王可以拿到多少储蓄收益。案例10-1计算定额定投型储蓄收益FV格式：FV(rate,nper,pmt,p

3、v,type)功能：FV函数是基于固定利率及等额分期付款方式，计算某项投资在将来某个日期的价值，即未来值的函数。在日常工作和生活中，合理利用FV函数，可以帮助我们进行一些有计划、有目的、有效益的投资。4案例要求老王从银行购买了一款保险，约定每月向银行存入2510.1.2 复利计算储蓄收益 对于比较常见的复利计息问题，我们可以使用公式和函数两种方法进行计算。第一种方法，需先计算出每年的利息，并和期初余额相加，才能得到下一年的期初余额，再使用相同方法计算本年利息，直到期满后的本利合计。第二种方法，使用EXCEL提供的FV函数进行计算，可以直接得出期满后的本利合计。比较两种方法，第一种方法相对繁琐，

4、但容易理解，第二种方法简单快捷，但需要理解函数中的相关参数。510.1.2 复利计算储蓄收益 对于比较常见的复利计案例要求某公司向银行存入10万元，存款年限为10年，年利率为5%，银行按照复利计息原则计算利息，10年后该公司可以拿到多少储蓄收益。分析题意可以得到利率（rate=5%），期数（nper=10），现值（pv=100000），根据以上条件求10年后的终值（fv）。案例10-2复利计算储蓄收益案例要求某公司向银行存入10万元，存款年限为10年，年利710.1.3 单笔投资计算复利现值 在给定的利率下，已知一笔资金在未来的终值，求出该资金目前的价值，这就是现值。现值是未来的资金按照一定利

5、率折算而成的当前值。计算现值的利率称为贴现率。在投资分析领域，这种用贴现的方法计算投资方案现金流量现值的方法是一种基本的分析方法。通常称为贴现现金流量法。 现值计算是终值计算的反向过程，故两者之间存在着对应关系。同时现值和终值还受利率和期数两个因素的影响。在Excel中提供了专门计算现值的PV函数，下面介绍该函数相关知识要点。710.1.3 单笔投资计算复利现值 在给定的利率下，8PV格式：PV(rate,nper,pmt,fv,type)功能：PV函数用来计算某项投资的现值。现值就是未来各期年金现在价值的总和，如果投资回收的当前价值大于投资的价值，则这项投资是有收益的。说明：参数rate，n

6、per，pmt，type参见FV函数，参数fv为投资的未来值或最后一次支付（或收入）后的希望得到的现金余额，如果省略 fv，则假设其值为零。案例要求小张计划5年后投资60万元买房，现在投资收益率为5%，那现在需要准备多少本金？分析题意可以得到利率（rate=5%），期数（nper=5），5年后的终值（fv=600000），根据以上条件求现值（pv）。案例10-3单笔投资计算复利现值8PV案例要求小张计划5年后投资60万元买房，现在投资收910.1.4 定额定期计算复利付款 本节案例和案例3给出了相同的已知条件，看似没有区别，实则一个是整存整取，一个是零存整取。一个是求现值（pv），一个是求每期

7、付款额（pmt）。在Excel中提供了专门计算每期付款额的PMT函数，下面介绍该函数相关知识要点。PMT格式：PMT(rate,nper,pmt,fv,type)功能：PMT函数是基于固定利率及等额分期付款方式，根据贷款利率、定期付款和贷款金额返回投资或贷款的每期付款额。说明：参数参见FV、PV 函数。910.1.4 定额定期计算复利付款 本节案例和案例310案例要求小张计划10年后投资60万元买房，现在投资收益率为5%，如果采取定额定期的方式投资，每月需要向银行存入多少钱。分析题意可以得到利率（rate=5%），期数（nper=1012），10年后的终值（fv=600000），根据以上条件求

8、各期支付金额（pmt）。案例10-4定额定期计算复利付款10.1.5 浮动利率储蓄收益 针对利率会随着社会的经济状况以及政策调整出现浮动的情况，我们仍然可以使用公式逐年计算，最终得到未来值。在Excel中提供了专门计算浮动利率未来值的FVSCHEDULE函数，使我们可以快速计算出浮动利率下的未来值，下面介绍该函数相关知识要点。10案例要求小张计划10年后投资60万元买房，现在投资收11FVSCHEDULE格式：FVSCHEDULE (principal, schedule)功能：FVSCHEDULE函数用于计算某项投资在变动利率或可调利率下的未来值。说明：参数principal为投资或贷款的期

9、初价值。参数schedule为利率数组，通常为变动利率单元格区域。11FVSCHEDULE1210.2.1 等额本金分期还款计算 银行贷款的还款方式主要包括等额本金还款和等额本息还款。10.2 贷款方案计算 目前，在现实的经济生活中，贷款消费已经成为人们个人理财的重要内容。例如，助学贷款、购房贷款、购车贷款以及装修贷款等方面。针对人们的不同贷款需求，各金融机构推出了不同种类的贷款业务，人们如何根据自己的还款能力，和金融机构贷款业务的政策，以及需求情况来优选贷款方案，是本节要解决的问题。1210.2.1 等额本金分期还款计算 银行贷款的还款131.等额本金等额本金是指一种贷款的还款方式，是在还款

10、期内把贷款数总额等分，每月偿还同等数额的本金和剩余贷款在该月所产生的利息。2.等额本息把贷款的本金总额与利息总额相加，然后平均分摊到还款期限的每个月中。131.等额本金14案例要求小张购房向银行贷款30万元，贷款利率为6%，还款期限为5年，还款方式为等额本金，小张在贷款期间的期初余额、还款本金、利息、本金余额、每月还款数各是多少。分析题意可以得到期数（nper=512），现值（pv=300000），利率（rate=6%），根据以上条件求期初余额、还款本金、利息、本金余额、每月还款数。案例10-6等额本金分期还款计算14案例要求小张购房向银行贷款30万元，贷款利率为6%，1510.2.3 等额本

11、息分期还款计算 等额本息还款方式，要求每期还款额是一样的，但由于期满后的未来值未知，故不能简单的使用公式来进行计算。在Excel中提供了专门计算贷款的一组函数，下面介绍相关知识要点。1. PPMT格式：PPMT(rate,per,nper,pv,fv,type)功能：PPMT函数是基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。说明：参数Rate，nper，pv，fv，type参见FV、PV函数。参数Per是用于计算其本金数额的期数，必须介于 1 到 nper 之间。1510.2.3 等额本息分期还款计算 等额本息还款方162. CUMPRINC格式：CUMPRINC(r

12、ate，nper，pv，start_period，end_period，type)功能：CUMPRINC函数返回一笔贷款在给定的start-period到end-period期间累计偿还的本金数额。说明：参数rate，nper，pv， type参见FV、PV函数。 参数Start_period 为计算本金（或利息）的首期期数，付款期数从 1 开始计数。 参数End_period 为计算本金（或利息）的末期期数。该值小于等于nper参数值。3. IPMT格式：IPMT(rate,per,nper,pv,fv,type)功能：IPMT函数基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿

13、还额。说明：参数Rate，per，nper，pv，fv，type参见FV、PV函数。162. CUMPRINC174. CUMIPMT格式：CUMIPMT(rate, nper, pv, start_period, end_period, type)功能：CUMIPMT函数用于计算一笔贷款在给定的 start_period 到 end_period 期间累计偿还的利息数额。说明：参数rate，nper，pv，start_period，end_period，type参见FV、PV、CUMPRINC函数。174. CUMIPMT18案例要求小张购房向银行贷款30万元，贷款利率为6%，还款期限为5年

14、，还款方式为等额本息，小张在贷款期间的期初余额、还款本金、利息、本金余额、每月还款数各是多少。分析题意可以得到期数（nper=512），现值（pv=300000），利率（rate=6%），根据以上条件求期初余额、还款本金、利息、本金余额、每月还款数。案例10-7等额本息分期还款计算18案例要求小张购房向银行贷款30万元，贷款利率为6%，19Nper格式：nper (rate,pmt,pv,fv,type)功能：nper函数指定定期定额支付且利率固定的年金总期数。说明：参数Rate，pmt，pv，fv，type参见FV、PV函数。10.2.3 等额本息还款期数计算 本节将介绍给定贷款金额、贷款利

15、率、每月还款数等信息，如何计算还款期数。此种情况在实际生活中很常见，可以根据本人的实际经济情况，合理做出调配，选择最佳的贷款方案。在Excel中提供了专门计算贷款期数的Nper函数，下面介绍该函数相关知识要点。19Nper10.2.3 等额本息还款期数计算 本节将20案例要求小张购房向银行贷款10万元，贷款利率为6%，计划每月还款2000元，还款方式为等额本息，小张需要还贷多少期，在贷款期间的期初余额、还款本金、利息、本金余额各是多少。分析题意可以得到现值（pv=300000），利率（rate=6%），每期还款数（pmt=2000），根据以上条件求期数（nper）。案例10-8等额本息还款期数

16、计算20案例要求小张购房向银行贷款10万元，贷款利率为6%，21RATE格式：rate (nper,pmt,pv,fv,type,guess)功能：RATE函数用于计算投资或贷款的各期利率。可以计算连续分期等额投资或贷款的利率，也可以计算一次性偿还的投资或贷款利率。说明：参数nper，pmt，pv，fv，type参见FV、PV函数。参数guess为预期利率。如果省略预期利率，则假设该值为10%。10.2.4 贷款利率计算 Excel常用的利率函数有RATE、IRR、MIRR、XIRR，这些函数用于计算不同形式的利率。RATE常用于计算投资或贷款的各期利率，下面介绍该函数相关知识要点。21RAT

17、E10.2.4 贷款利率计算 Excel常用22案例要求小张装修房屋向银行贷款3万元，贷款期限为2年，每月还款2000元，该笔贷款的月利率和年利率各是多少。分析题意可以得到现值（pv=30000），贷款期限（nper=2），每期还款数（pmt=2000），根据以上条件求利率（rate）。案例10-9贷款利率计算22案例要求小张装修房屋向银行贷款3万元，贷款期限为2年2310.3.1 不同年限存款比较 银行贷款的还款方式主要包括等额本金还款和等额本息还款。10.3 理财方案比较 本节将通过3个案例对银行存款方案、投资方案和购房贷款方案等理财方案进行比较，为客户做出决策判断提供参考。案例要求老王到银行存款，银行员工向他推荐了几种方案，但由于年限不同，利率也不相同，导致得到的利息也不一样，老王希望设计一张表进行比较。案例10-10不同年限存款比较2310.3.1 不同年限存款比较 银行贷款的还款方式2410.3.2 投资方案比较 银行贷款的还款方式主要包括等额本金还款和等额本息还款。案例要求老王想从银行购买一款保险，购买成本为10万元，且在今后20年内每月可以回报800元，假设投资收益率为8%，帮老王算算这是一个合算的