EXCEL在金融理财中的应用

昆明理工大学

城市学院学生实验报告

实验课程名称：金融市场学

2012 年 6 月开课实验室：德阳楼306

12日

在学了一个学期的金融市场学之后，我们虽然掌握了理论知识，但对于把这些理论运用到实践还是有一定难度，不能够把理论和实践很好地结合起来。经过EXCEL在金融理财中的应用模拟实验，我们能够更好的了解市场金融学这门课程的内容，知道了学习这门课程的重要性，对我们以后的生活中有很大的帮助。

一、实验目的

通过本模拟实验，使我们能够掌握EXCEL在投资理财中的基本应用，领会各种财务函数、储蓄、贷款的偿还方式，掌握等额摊还法、等额本金法两种还款方式的差别以及在贷款中的应用。会设计贷款计算器。通过实验课，实现由感性认识到理性认识的升华，并在此规程中，培养我们独立完成业务的能力，使之掌握金融工具的基本计算，为今后走上工作岗位做准备。

二、实验原理

通过模拟实验，同学们应能够比较全面地了解EXCEL的主要内容以及财务函数的使用，加强学生对金融工具理论的理解和基本方法的运用，强化基本技能的训练和职业习惯的养成，将理论知识与实务紧密结合起来，把抽象、复杂的理论通过具体、有形的载体表现出来，可以增强学生继续深造专业课的兴趣和欲望，提高学生学习的积极性，切实体现高职本科学生动手能力强的办学特色。

三、实验步骤

1.某企业向银行贷款200万元，年利率8%，期限5年，如果企业与银行商定每

年末等额还本付息，为该企业编制还款计划表。如果企业与银行商定每月末还本付息，那么每月末的等额还款额是多少第二个半年累计支付的利息和偿

还的本金各是多少

2.某人从银行取得个人汽车消费贷款10万元，年利率为8%，贷款期限5年，

与银行商定采用等额本金还本付息，还款时间在每年的年末。为此人编制还款计划表。

3.王先生刚刚与银行签订一份商业住房贷款合同，贷款额45万元，贷款年利

率%，期限30年，每月末等额还款。试计算等额摊还法与等额本金还款法利率的差别。

四、实验过程原始记录（数据、图表、计算等）

例题1

1、新建EXCEL表格，在EXCEL表格中输入基本的数据。之后计算第一年的等额还本付息额，利用财务公式：PTM（rate，nper，pv，fv，type） ,把题目中的数据带入计算的到PTM（8%，5，2000000，0，0）=元，根据相同的方法可以计算出全部五年的数据都为元，所以五年的一共还款金额为*5=元，其中本金为2000000元，支付的为元。而如果进行每月还本付息的话，可以运用相同的公式：PTM（rate，nper，pv，fv，type）, 带入数据可以计算得出PTM（%，60，2000000，0，0）=元，可以算出五年共用了*60=元。

2、计算第二个半年累计支付的利息和偿还的本金各为多少，要用到的公式为：CUMIPMT（CUMIPMT函数，返回一笔贷款在给定的两个时间内累计偿还的利息数额）函数，CUMIPTM（rate，nper，pv，start-period，end-period，type）其中有6个参数，分别为Rate为利率。 Nper为总付款期数。 Pv为现值。Start period 为计算中的首期，付款期数从 1 开始计数。 End period 为计算中的末期。 Type 为付款时间类型。令第二个半年累计支付的利息=-CUMIPMT(D2/12，

B3*12，B2，7，12，0)= 。

3、计算第二个半年累计偿还的本金，引入CUMPRINC（返回一笔贷款在给定的开始到结束期间累计偿还的本金数额）。在该EXCEI表中第二个半年累计偿还的本金 =-CUMPRINC(D2/12，B3*12，B2，7，12，0)= 。通过以上步骤的计算最终得出如下表格:

例题2、

1、新建EXCEL表格，在EXCEL表格中输入基本的数据。个人汽车消费贷款10万元，年利率为8%，贷款期限5年，与银行商定采用等额本金还本付息，还款时间在每年的年末。在EXCEL中，设A2为贷款金额，A3贷款期限，A4年利率，A5还本付息方式。

2、设计还款计划表，在还款计划表中设计四个主要项目：年偿还额、支付利息、偿还本金、剩余本金。在还款计划表中通过引用财务函数，同问题以类似对于年偿还额、支付利息、偿还本金分别引入PMT、IPMT、PPMT。

（1）每年还款额=贷款本金/贷款期年数+（本金-已归还本金累计额）×年利率，例如：令第一年年还款额=PMT（$B$4，$B$3，-$B$2）=28000。应用绝对引用可依此得出第二年的年偿还额26400元，第三年的年偿还额24800元, 第四年的年偿还额23200元，第五年的年偿还额21600元, 共计元。

（2）支付利息=贷款金额×年利率，令第一年支付利息= IPMT($D$2，A9，$B$3，-$B$2)=8000元；第二年6400元，第三年4800元，第四年3200元，第五年1600元，共计24000元。

（3）偿还本金=年偿还额-每一年支付利息，令第一年偿还本金=PPMT($B$4，A9，$B$3，-$B$2)= 20000元。也可以由贷款金额/贷款期限，即100000/5=20000元，依次可以求出其后四年偿还本金均是20000元。

（4）剩余本金=贷款金额-上一年偿还金额，例如：令第一年剩余本金=(E8-D9)= 80000元，第二年60000元，第三年40000元，第四年20000元，最

后一年（第五年）剩余即为 0，具体数据如下表：

例题3、

1、新建EXCEL表格，在EXCEL表格中输入基本的数据。贷款额45万元，贷款年利率%，期限30年，每月末等额还款。试计算等额摊还法与等额本金还款法利率的差别。在EXCEL表格中设B4为贷款金额，B5为期限，B6年利率，B7还款时间：每个月末。等额偿还法下：B10支付利息，C10偿还本金，D10月偿还额，等额本金法下：E10支付利息，F10偿还本金，G10月偿还额。之后开始数据分析。