EXCEL在金融理财中的应用14730.pdf

《EXCEL在金融理财中的应用14730.pdf》由会员分享，可在线阅读，更多相关《EXCEL在金融理财中的应用14730.pdf（9页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。

1、理工大学 城市学院学生实验报告 实验课程名称：金融市场学 开课实验室：德阳楼 306 2012 年 6 月 12 日 专业班级 财务管理 1013 班 学号 09 春秀 成绩 实验项目名称 EXCEL 在金融理财中的应用 指导教师 吴永兴 教师评语 教师签名：年 月 日 在学了一个学期的金融市场学之后，我们虽然掌握了理论知识，但对于把这些理论运用到实践还是有一定难度，不能够把理论和实践很好地结合起来。经过 EXCEL 在金融理财中的应用模拟实验，我们能够更好的了解市场金融学这门课程的容，知道了学习这门课程的重要性，对我们以后的生活中有很大的帮助。一、实验目的 通过本模拟实验，使我们能够掌握 E

2、XCEL 在投资理财中的基本应用，领会各种财务函数、储蓄、贷款的偿还方式，掌握等额摊还法、等额本金法两种还款方式的差别以及在贷款中的应用。会设计贷款计算器。通过实验课，实现由感性认识到理性认识的升华，并在此规程中，培养我们独立完成业务的能力，使之掌握金融工具的基本计算，为今后走上工作岗位做准备。二、实验原理 通过模拟实验，同学们应能够比较全面地了解 EXCEL 的主要容以及财务函数的使用，加强学生对金融工具理论的理解和基本方法的运用，强化基本技能的训练和职业习惯的养成，将理论知识与实务紧密结合起来，把抽象、复杂的理论通过具体、有形的载体表现出来，可以增强学生继续深造专业课的兴趣和欲望，提高学生

3、学习的积极性，切实体现高职本科学生动手能力强的办学特色。三、实验步骤 1.某企业向银行贷款 200 万元，年利率 8%，期限 5 年，如果企业与银行商定每年末等额还本付息，为该企业编制还款计划表。如果企业与银行商定每月末还本付息，那么每月末的等额还款额是多少？第二个半年累计支付的利息和偿还的本金各是多少？2.某人从银行取得个人汽车消费贷款 10 万元，年利率为 8%，贷款期限 5 年，与银行商定采用等额本金还本付息，还款时间在每年的年末。为此人编制还款计划表。3.王先生刚刚与银行签订一份商业住房贷款合同，贷款额 45 万元，贷款年利率5.31%，期限 30 年，每月末等额还款。试计算等额摊还法

4、与等额本金还款法利率的差别。四、实验过程原始记录（数据、图表、计算等）例题 1 1、新建 EXCEL 表格，在 EXCEL 表格中输入基本的数据。之后计算第一年的等额还本付息额，利用财务公式：PTM（rate，nper，pv，fv，type）,把题目中的数据带入计算的到 PTM（8%，5，2000000，0，0）=500912.91 元，根据相同的方法可以计算出全部五年的数据都为 500912.91 元，所以五年的一共还款金额为 500912.91*5=2504564.55 元，其中本金为 2000000 元，支付的为504564.55 元。而如果进行每月还本付息的话，可以运用相同的公式：PT

5、M（rate，nper，pv，fv，type）,带入数据可以计算得出 PTM（0.667%，60，2000000，0，0）=40552.79 元，可以算出五年共用了 40552.79*60=2433167.4 元。2、计算第二个半年累计支付的利息和偿还的本金各为多少，要用到的公式为：CUMIPMT（CUMIPMT 函数，返回一笔贷款在给定的两个时间累计偿还的利息数额）函数，CUMIPTM（rate，nper，pv，start-period，end-period，type）其中有 6 个参数，分别为 Rate 为利率。Nper 为总付款期数。Pv 为现值。Start period 为计算中的首期

6、，付款期数从 1 开始计数。End period 为计算中的末期。Type 为付款时间类型。令第二个半年累计支付的利息=-CUMIPMT(D2/12，B3*12，B2，7，12，0)=70499.5207。3、计算第二个半年累计偿还的本金，引入 CUMPRINC（返回一笔贷款在给定的开始到结束期间累计偿还的本金数额）。在该 EXCEI 表中第二个半年累计偿还的本金 =-CUMPRINC(D2/12，B3*12，B2，7，12，0)=172817.2108。通过以上步骤的计算最终得出如下表格:贷款基本数据 付款全额(元)2000000 贷款年利率 8%贷款期限(年)5 还本付息方式 等额偿还法

7、还款计划表（每年末等额偿还）年末 年偿还额 支付利息 偿还本金 剩余本金 0 2000000 1￥500,912.91￥160,000.00￥340,912.91￥1,659,087.09 2￥500,912.91￥132,726.97￥368,185.94￥1,290,901.15 3￥500,912.91￥103,272.09￥397,640.82￥893,260.33 4￥500,912.91￥71,460.83￥429,452.08￥463,808.25 5￥500,912.91￥37,104.66￥463,808.25￥0.00 合计￥2,003,651.64￥504,564.55￥

8、2,000,000.00￥4,508,216.18 每个月末等额偿还的情况 单位（元）每个月末等额偿￥40,552.79 例题 2、1、新建 EXCEL 表格，在 EXCEL 表格中输入基本的数据。个人汽车消费贷款 10 万元，年利率为 8%，贷款期限 5 年，与银行商定采用等额本金还本付息，还款时间在每年的年末。在 EXCEL 中，设 A2 为贷款金额，A3 贷款期限，A4 年利率，A5 还本付息方式。2、设计还款计划表，在还款计划表中设计四个主要项目：年偿还额、支付利息、偿还本金、剩余本金。在还款计划表过引用财务函数，同问题以类似对于年偿还额、支付利息、偿还本金分别引入 PMT、IPMT、

9、PPMT。（1）每年还款额=贷款本金/贷款期年数+（本金-已归还本金累计额）年利率，例如：令第一年年还款额=PMT（$B$4，$B$3，-$B$2）=28000。应用绝对引用可依此得出第二年的年偿还额 26400 元，第三年的年偿还额 24800 元,第四年的年偿还额 23200 元，第五年的年偿还额 21600 元,共计 25228.23 元。（2）支付利息=贷款金额年利率，令第一年支付利息=IPMT($D$2，A9，$B$3，-$B$2)=8000 元；第二年 6400 元，第三年 4800 元，第四年 3200 元，第五年 1600 元，共计 24000 元。（3）偿还本金=年偿还额-每

10、一年支付利息，令第一年偿还本金=PPMT($B$4，A9，$B$3，-$B$2)=20000 元。也可以由贷款金额/贷款期限，即100000/5=20000 元，依次可以求出其后四年偿还本金均是 20000 元。（4）剩余本金=贷款金额-上一年偿还金额，例如：令第一年剩余本金=(E8-D9)=80000 元，第二年 60000 元，第三年 40000 元，第四年 20000 元，最第二个半年累计支付利息￥70,499.5207 第二个半年累计偿还本金￥172,817.2108 后一年（第五年）剩余即为 0，具体数据如下表：贷款基本数据 贷款金额（元）100000 贷款期限（年）5 贷款年利率（

11、%）8%还本付息方式 等额本金 还款计划表 单位（元）年 年偿还额 支付利息 偿还本金 剩余本金 0 100000 1 28000 8000 20000 80000 2 26400 6400 20000 60000 3 24800 4800 20000 40000 4 23200 3200 20000 20000 5 21600 1600 20000 0 合计 124000 24000 100000 例题 3、1、新建 EXCEL 表格，在 EXCEL 表格中输入基本的数据。贷款额 45 万元，贷款年利率 5.31%，期限 30 年，每月末等额还款。试计算等额摊还法与等额本金还款法利率的差别。

12、在 EXCEL 表格中设 B4 为贷款金额，B5 为期限，B6 年利率，B7 还款时间：每个月末。等额偿还法下：B10 支付利息，C10 偿还本金，D10 月偿还额，等额本金法下：E10 支付利息，F10 偿还本金，G10 月偿还额。之后开始数据分析。2、等额本金偿还法（等额本金还款，贷款人将本金分摊到每个月，同时付清上一交易日至本次还款日之间的利息。）的计算：（1）支付利息：支付利息引入 IPMT(rate，per，nper，pv，fv，type)函数，IPMT 是指某一期应付利息之金額。每月支付利息=贷款金额月利率，月利率=年利率12。令第一月支付利息=IPMT($B$6/12，A11，$

13、B$5*12，-$B$4)，例如第一月支付利息=4500005.31%=1991.25 元.第二月支付利息=（贷款额第一月偿还本金）月利率，以此类推即可得出第 1、2、3360 月要支付的利息。（2）偿还本金：引人 PPM 函数计算偿还本金，PPMT(rate，per，nper，pv，fv，type)。令第一个月偿还本金=PPMT($B$6/12，A11，$B$5*12，-$B$4)=510.42 元，同理：每月偿还本金=月偿还额-每一月支付利息。例如：第一个月偿还本金=2501.67-1991.25=510.42；第二年偿还本金=2501.67-1988.99=512.67；依次可得出第 3

14、12 月的金额。（3）月偿还额：月偿还额=每月支付利息+偿还本金=（B11+C11），同时每月还款额=贷款本金/贷款期月数+（本金-已归还本金累计额）月利率。第一个月偿还额=1991.25+510.42=2501.67 元，根据等额本金偿还法，1-360 个月月偿还额=2501.67 元。3、等额本息偿还法（即借款人每月按相等的金额偿还贷款本息，其每月贷款利息按月初剩余贷款本金计算并逐月结清。）的计算：（1）支付利息：公式较为复杂，因此在 EXCEL 中，利用绝对引用，设立条件函数令第一个月支付利息=IF(A11=1，$B$4*$B$6/12，($B$4-SUM($F10：$F$11)*$B$

15、6/12)=1991.25 元。依次第二个月=1985.71875，等额本息法计算，从第二个月偿还的金额比等额本金偿还法较少。（2）偿还本金：等额本息偿还法中每个月的偿还本金数额相同。每个月偿还本金=贷款额总的贷款月数。例如题目中;偿还本金=450000360=1250 元。（3）月偿还金额：在 EXCEL 中我们可以根据已经计算出的支付利息+偿还的本金算出。如：第一月偿还金额=（E11=F11）=3241.25 元。数据、表格如下：例题：王先生与银行签订了一份商业住房贷款合同，贷款额 45 万，贷款年利率5.13%，期限 30 年，每月末等额偿还。请你分析使用哪种贷款法可以节约费用?已知条件

16、 贷款额（元）450000 期限（年）30 年利率 5.31%还款时间 每月末 计算分析 期限（月）等额偿还法（元）等额本金法（元）支付利息 偿还本金 月还款额 支付利息 偿还本金 月还款额 1￥1,991.25￥510.42￥2,501.67 1991.25 1250 3241.25 2￥1,988.99￥512.67￥2,501.67 1985.71875 1250 3235.71875 3￥1,986.72￥514.94￥2,501.67 1980.1875 1250 3230.1875 4￥1,984.44￥517.22￥2,501.67 1974.65625 1250 3224.65

17、625 5￥1,982.16￥519.51￥2,501.67 1969.125 1250 3219.125 358￥32.92￥2,468.75￥2,501.67 16.59375 1250 1266.59375 359￥21.99￥2,479.67￥2,501.67 11.0625 1250 1261.0625 360￥11.02￥2,490.65￥2,501.67 5.53125 1250 1255.53125 合计￥450,599.82￥450,000.00￥2,501.67￥900,599.82￥359,420.63￥809,420.63 等额摊还法比等额本金法多￥91,179.19

18、五、实验结果及分析 等额本金偿还法最后偿还的总额=900599.82，等额本息偿还法最偿还的总额=809420.625。两者相比=900599.82-809420.625=91179.19。相比较之下，等额本息偿还法比等额本金偿还法偿还的金额要少。相同条件按下等额本金偿还法较划算。六、心得体会 通过此次模拟实验，我了解 EXCEL 的主要容以及财务函数的使用，解了EXCEL 的操作要求和会计应用步骤方法，拓宽了对金融工具理论的理解和基本方法的运用。利用 EXCEL 尽管过程复杂繁琐，但是在老师的指导下我们按时按量顺利完成实验容，我们编制了还款计划表，在表中就能清晰的反映各种还款方式的利弊，我们选择财务函数是计算正确与否的关键，在此次模拟实验中我们把从书本上学到的知识应用于实际的会计实物操作中去。此次模拟实验，学到了好多关于利息的计算方法，还了解了绝对引用和相对引用，能够掌握 EXCEL在投资理财中的基本应用，领会各种财务函数、储蓄、贷款的偿还方式，掌握等额摊还法、等额本金法两种还款方式的差别以及在贷款中的应用。此次模拟实验，为我们深入社会，体验生活提供了难得的机会，让我们在实际的模拟实验中感受生活，了解在社会中生存所应该具备的各种能力。