《EXCEL在金融理财中的应用.doc》由会员分享，可在线阅读，更多相关《EXCEL在金融理财中的应用.doc（10页珍藏版）》请在金锄头文库上搜索。
1、昆明理工大学城市学院学生实验报告实验课程名称：金融市场学开课实验室：德阳楼306 2012 年 6 月 12日专业班级财务管理 1013班学号2010328514109姓名杨春秀成绩实验项目名称EXCEL在金融理财中的应用指导教师吴永兴教师评语 教师签名： 年 月 日在学了一个学期的金融市场学之后，我们虽然掌握了理论知识，但对于把这些理论运用到实践还是有一定难度，不能够把理论和实践很好地结合起来。经过EXCEL在金融理财中的应用模拟实验，我们能够更好的了解市场金融学这门课程的内容，知道了学习这门课程的重要性，对我们以后的生活中有很大的帮助。一、 实验目的通过本模拟实验，使我们能够掌握EXCEL
2、在投资理财中的基本应用，领会各种财务函数、储蓄、贷款的偿还方式，掌握等额摊还法、等额本金法两种还款方式的差别以及在贷款中的应用。会设计贷款计算器。通过实验课，实现由感性认识到理性认识的升华，并在此规程中，培养我们独立完成业务的能力，使之掌握金融工具的基本计算，为今后走上工作岗位做准备。二、 实验原理通过模拟实验，同学们应能够比较全面地了解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元。而如果进行每月还本付息的话，可以运用相同的公式：PTM（rate，nper，pv，fv，type）, 带入数据
5、可以计算得出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 为计算中的首期，付款期数从 1 开始计数。 End period 为计算中的末期。 T
6、ype 为付款时间类型。令第二个半年累计支付的利息=-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还本付息方式等额偿还法还款计划表（每年末等额偿还）年末年偿还额支付利息偿还本金剩余本金02000000
7、1￥500,912.91￥160,000.00￥340,912.91￥1,659,087.092￥500,912.91￥132,726.97￥368,185.94￥1,290,901.153￥500,912.91￥103,272.09￥397,640.82￥893,260.334￥500,912.91￥71,460.83￥429,452.08￥463,808.255￥500,912.91￥37,104.66￥463,808.25￥0.00合计￥2,003,651.64￥504,564.55￥2,000,000.00￥4,508,216.18每个月末等额偿还的情况 单位（元）每个月末等额偿￥40
8、,552.79第二个半年累计支付利息￥70,499.5207第二个半年累计偿还本金￥172,817.2108例题2、1、新建EXCEL表格，在EXCEL表格中输入基本的数据。个人汽车消费贷款10万元，年利率为8%，贷款期限5年，与银行商定采用等额本金还本付息，还款时间在每年的年末。在EXCEL中，设A2为贷款金额，A3贷款期限，A4年利率，A5还本付息方式。2、设计还款计划表，在还款计划表中设计四个主要项目：年偿还额、支付利息、偿还本金、剩余本金。在还款计划表中通过引用财务函数，同问题以类似对于年偿还额、支付利息、偿还本金分别引入PMT、IPMT、PPMT。（1）每年还款额=贷款本金/贷款期年
9、数+（本金-已归还本金累计额）年利率，例如：令第一年年还款额=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）偿还本金=年偿还额-每一年支付利息，令第一年偿还本金 =PPMT($B$4，A9，$B$3，-$B$2)
10、= 20000元。也可以由贷款金额/贷款期限，即100000/5=20000元，依次可以求出其后四年偿还本金均是20000元。（4）剩余本金=贷款金额-上一年偿还金额，例如：令第一年剩余本金=(E8-D9)= 80000元，第二年60000元，第三年40000元，第四年20000元，最后一年（第五年）剩余即为 0，具体数据如下表： 贷款基本数据贷款金额（元）100000贷款期限（年）5贷款年利率（%）8%还本付息方式等额本金还款计划表 单位 （元）年年偿还额支付利息偿还本金剩余本金010000012800080002000080000226400640020000600003248004800
11、2000040000423200320020000200005216001600200000合计12400024000100000例题3、1、新建EXCEL表格，在EXCEL表格中输入基本的数据。贷款额45万元，贷款年利率5.31%，期限30年，每月末等额还款。试计算等额摊还法与等额本金还款法利率的差别。在EXCEL表格中设B4为贷款金额，B5为期限，B6年利率，B7还款时间：每个月末。等额偿还法下：B10支付利息，C10偿还本金，D10月偿还额，等额本金法下：E10支付利息，F10偿还本金，G10月偿还额。之后开始数据分析。2、等额本金偿还法（等额本金还款，贷款人将本金分摊到每个月内，同时付
12、清上一交易日至本次还款日之间的利息。）的计算：（1）支付利息：支付利息引入IPMT(rate，per，nper，pv，fv，type)函数，IPMT是指某一期应付利息之金額。每月支付利息=贷款金额月利率，月利率=年利率12。令第一月支付利息=IPMT($B$6/12，A11，$B$5 *12，-$B$4)，例如第一月支付利息=4500005.31%=1991.25元.第二月支付利息=（贷款额第一月偿还本金）月利率，以此类推即可得出第1、2、3360月要支付的利息。（2）偿还本金：引人PPM函数计算偿还本金，PPMT(rate，per，nper，pv，fv，type)。令第一个月偿还本金=PPM
13、T($B$6/12，A11，$B$5*12，-$B$4)= 510.42元，同理：每月偿还本金=月偿还额-每一月支付利息。例如：第一个月偿还本金=2501.67-1991.25=510.42；第二年偿还本金=2501.67-1988.99=512.67；依次可得出第312月的金额。（3）月偿还额：月偿还额=每月支付利息+偿还本金=（B11+C11），同时每月还款额=贷款本金/贷款期月数+（本金-已归还本金累计额）月利率。第一个月偿还额=1991.25+510.42=2501.67元，根据等额本金偿还法，1-360个月月偿还额=2501.67元。3、等额本息偿还法（即借款人每月按相等的金额偿还贷
14、款本息，其每月贷款利息按月初剩余贷款本金计算并逐月结清。）的计算： （1）支付利息：公式较为复杂，因此在EXCEL中，利用绝对引用，设立条件函数令第一个月支付利息=IF(A11=1，$B$4*$B$6/12，($B$4-SUM($F10：$F$11)*$B$6/12)=1991.25元。依次第二个月= 1985.71875，等额本息法计算，从第二个月偿还的金额比等额本金偿还法较少。（2）偿还本金：等额本息偿还法中每个月的偿还本金数额相同。每个月偿还本金=贷款额总的贷款月数。例如题目中;偿还本金=450000360=1250元。（3）月偿还金额：在EXCEL中我们可以根据已经计算出的支付利息+偿还的本金算出。如：第一月偿还金额=（E11=F11）=3241.25元。数据、表格如下：例题：王先生与银行签订了一份商业住房贷款合同，贷款额45万，贷款年利率5.13%，期限30年，每月末等额偿还。请你分析使用哪种
