EXCEL在金融理财中的应用.doc

《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中我们可以根据已经计算出的支付利息+偿

15、还的本金算出。如：第一月偿还金额=（E11=F11）=3241.25元。数据、表格如下：例题：王先生与银行签订了一份商业住房贷款合同，贷款额45万，贷款年利率5.13%，期限30年，每月末等额偿还。请你分析使用哪种贷款法可以节约费用?已知条件贷款额（元）450000期限（年）30年利率5.31%还款时间每月末计算分析期限（月） 等额偿还法（元） 等额本金法（元）支付利息偿还本金月还款额支付利息偿还本金月还款额1￥1,991.25￥510.42￥2,501.671991.2512503241.252￥1,988.99￥512.67￥2,501.671985.7187512503235.71875

16、3￥1,986.72￥514.94￥2,501.671980.187512503230.18754￥1,984.44￥517.22￥2,501.671974.6562512503224.656255￥1,982.16￥519.51￥2,501.671969.12512503219.125358￥32.92￥2,468.75￥2,501.6716.5937512501266.59375359￥21.99￥2,479.67￥2,501.6711.062512501261.0625360￥11.02￥2,490.65￥2,501.675.5312512501255.53125合计￥450,599.8

17、2￥450,000.00￥2,501.67￥900,599.82￥359,420.63￥809,420.63等额摊还法比等额本金法多￥91,179.19五、 实验结果及分析等额本金偿还法最后偿还的总额=900599.82，等额本息偿还法最偿还的总额=809420.625。两者相比=900599.82-809420.625=91179.19。相比较之下，等额本息偿还法比等额本金偿还法偿还的金额要少。相同条件按下等额本金偿还法较划算。六、 心得体会通过此次模拟实验，我了解EXCEL的主要内容以及财务函数的使用，解了EXCEL的操作要求和会计应用步骤方法，拓宽了对金融工具理论的理解和基本方法的运用。

18、利用EXCEL尽管过程复杂繁琐，但是在老师的指导下我们按时按量顺利完成实验内容，我们编制了还款计划表，在表中就能清晰的反映各种还款方式的利弊，我们选择财务函数是计算正确与否的关键，在此次模拟实验中我们把从书本上学到的知识应用于实际的会计实物操作中去。此次模拟实验，学到了好多关于利息的计算方法，还了解了绝对引用和相对引用，能够掌握EXCEL在投资理财中的基本应用，领会各种财务函数、储蓄、贷款的偿还方式，掌握等额摊还法、等额本金法两种还款方式的差别以及在贷款中的应用。此次模拟实验，为我们深入社会，体验生活提供了难得的机会，让我们在实际的模拟实验中感受生活，了解在社会中生存所应该具备的各种能力。10

19、 力种备具生会了，中实际们让的了供体会们我拟此应的贷差方种金等法等，还的贷、务各用基财资在 掌用引和绝还，的于好学验次。作实际用知学上从我实次键与确数务择，式还映的能表划款了我验成利按我的在但琐程 用运方和的具金对法步计和要 解的务以要主 验模体算较偿额按同少额偿还金法还本之比 . 0相。 还最偿额 .0=还偿还分果 ,法金额 0 0 000 ,合 . 0 0. . 0 0 . . 0 . . . . . . . . . . 款本利款本利元法额元法还月分月时 利 年00元（条用约以贷种分你额月，限%.利贷 款，贷住一签银王下格据数 （还月如。的+利出计已以 ：还月元 = 00=偿目例数的额贷金

20、偿。相还月中偿额金偿少法偿额金的二，计额 第。 .=) *)$0$ $ $， 利支令件立，用中 在因杂公利支：计。结算金贷初利月，本还的按每即还息元 .=偿个 还本等， .+ 额个一利额金归-（数/本=款时，+ =还利支额偿：额的 得；. -. 金偿第； . 0 金个：例付一每月本还：， - ， 金偿一。 ， ，金计数 ：还息的要 、第可此率利本月贷（利第 0 支第，)- $ $ 利支第 年=月款=付每金利应指 ) 引利息付算的。间之本易上时，个分本款还额法金本析析开后还 金还 ，0 下等还月 还0息付 下额。月间 限为，贷为设 。率利金等摊算。还月年0，.利贷元额。数入中 格 00000合0

21、00000000000000000000本本利还元（ 划本方息 %率年（00元（数本基：下数，0余年（后，0年，00第00年00 金剩第如额偿上额=本元0 金偿后出可元0 /0即限贷款以可0 )$- $ =金偿令，年-还金本元00计0 ，0 第0年，元年；0=$-$， =付一率款息支元 共0 额的第0 额的第, 额年的第 年年得可对应。 =$ （ 额年第例率累本归金数款金贷额年 、 金偿利付还对以同，财过表还金剩、付额偿项个设表款表计式息还，率，限 额款 。的在款息还本用行银 贷 利， 费汽个的本格 ， 建 0 . 本偿年0 .利支累 额额元位 情额 00,0 . 0合0 , ,0 ,0.,

22、. 0, 0 0 , ., ,0. 00, 00本本利还年还额末每还方息年年 利00元元数本格表得最骤以。 =， ， ( 金计半第表 该额本的累结到的贷回 入金还偿半算 0 0， *，/ -的计个令。款 末计 数始 数款的计 。现 数付 率 ，个中其 -， ， 函额息计内两定贷回， 为的用多金的息付累半第元 * 0用五算元. =，00 . 出计据带） （ 式相以，付还每如 . 的000本其 00金还五，元 0 的部算可方同元 0000，% 到入数中,） ， ， ：务利，还的第后据本入表 在表 建、题等算表据记程别的法还等还额计。末月 限 年， 款同贷业订银刚表计制人为年间还，还额定商年 ， 率， 费人取银少是本偿利支累二少额还的月息还月行业果。款业为息还年商与果，期 率万0行向步色学办力生职体切极学学，欲课造深强可来表载有过的复把来密实识理养惯和练本强运方和理具金学，使函以要的 了全比应，拟原备做作上为计的工掌，力成独们培规，的认识感实课过算款设。款及别式款金本还等握还偿贷数财各用本中资 握我验实目助的大生以们性程这学知的课融市的够我实模中财金 经来结践论够不定是实用些把，论理然我后市期一 月 ：签 评兴教用应财融 名目绩春姓00 学 0 班 0 0 室验实场称程报实生大大