Excel在金融方面的应用Word格式.docx
- 文档编号:22460055
- 上传时间:2023-02-04
- 格式:DOCX
- 页数:40
- 大小:419.03KB
Excel在金融方面的应用Word格式.docx
《Excel在金融方面的应用Word格式.docx》由会员分享,可在线阅读,更多相关《Excel在金融方面的应用Word格式.docx(40页珍藏版)》请在冰豆网上搜索。
3
年利率i
3.50%
4
存款年数n
5
存款本金PV
1000.00
6
第n年末可取出本息FV
1187.69
=B5*(1+B3)^B4
[例2]已知年利率为3.78%,在第1年初存入1500元,在第3年末存入2000元,问,到第10年末时一共可以从银行中取出多少钱?
根据题意,第10年末可以从银行中取出的总额为:
3.78%
第1次存款年数n1
10
第1次存款金额PV1
1500.00
第2次存款年数n2
7
第2次存款金额PV2
2000.00
8
第10年末可取出本息FV
4766.98
=B5*(1+B3)^B4+B7*(1+B3)^B6
[例3]某人用信用卡消费了500元,如果在1个月内归还这消费的500元,则银行不收利息,如果在1个月之后归还,则从第2个月开始每月按复利加收0.
5%的利息。
问,
(1)如果在消费后的第3月末归还该笔消费,归需要归还多少元?
(3)如果在消费后的第3年初归还该笔消费,归需要归还多少元?
根据题意,因第1个月不计息,所以第3月末归还需要计息2次,而在第3年初归还则需要计息23次。
消费金额
500.00
月利率i
0.50%
第3月末应归还本息和
505.01
=B3*(1+B4)^2
第3年初应归还本息和
560.78
=B3*(1+B4)^23
2、现值计算问题
[例4]已知年利率为5%,如果在第5年末需要18000元钱,问,现在需要存多少钱?
根据题意,现在需要存的金额可用以下公式进行计算:
现值计算
5.00%
第n年末需要的金额FV
18000.00
现在应存入的金额PV
14103.47
=B5/(1+B3)^B4
[例5]已知年利率为5%,如果在第5年末需要18000元钱,在第7年初需要8000元钱,问,现在需要存多少钱?
注意,第7年初需要的钱,从现在开始存钱到第7年初存款时间只有6年。
第5年末需要的金额
第7年初需要的金额
8000.00
33693.83
=B4*(1+B3)^5+B5*(1+B3)^6
3、存款时间长度问题
[例6]现在存入16000元,如果存款利率为3.5%,问,需要经过多少时间才能从银行取出20000元。
根据:
有:
两边取对数并移项后得:
存款时间计算
16000
积累值FV
20000
应存款时间(年)
6.49
=(LN(B5)-LN(B4))/LN(1+B3)
4、利率计算问题
[例7]现在存款15680元,在第5年初可以取出20000元钱,问,存款利率是多少?
开方并移项后有:
15680.00
20000.00
存款时间n
存款利率i
4.99%
=(B4/B3)^(1/B5)-1
[例8]某大学生在进校时贷款15000元,第2年初贷款5000元,在其4年大学毕业后的第1年末归还贷款本息8000元,毕业后的第2年末归还贷款本息16000元,至此还清所有贷款的本息。
问贷款利率为多少?
解:
这个题需要用到货币时间价值理论,不同时间点的货币价值就该按时一定的利率折算到某一时间点的价值,才能比较不同时间点不同数量货币的价值量的大小。
有在一定的贷款利率下,贷款的价值应该等于还款的价值,以该学生进校时为基准时间,则应有:
设贷款年利率为i,可以得到方程:
所求的年利率就是该方程的根。
求解方程的根一般可以运用迭代法求方程的近似根。
迭代法求方程近似根的基本原理是,
对于方程:
,(c为常量),首先假设方程的根为x0(一个具体的值,称为x的初始值),然后重复做如下的工作:
(1)计算
的值
(2)如果
,(ε为给定的误差限),则此时x0就是方程的近似根,否则,按照某种方式修改x0的值。
理论上,如果方程有实根,这个迭代过程一定会求得方程的根,也就是说这个过程一定会结束。
手工计算
的值和修改x0的值是很繁重的工作,可以用Excel的“单变量求解”命令来帮助完成这一过程,其求方程近似根的步骤为:
(1)选定一个单元格用于存放x0的初始值(这个单元格称为可变单元格);
(2)选定一个单元格用于存放
的计算公式(这个单元格称为目标单元格);
(3)使用“单变量求解”命令,并在命令对话框中输入“可变单元格”的坐标和“目标单元格”坐标,以及目标值c。
完成这些步骤后,在可变单元格中就得到方程的近似根。
可以看出用excel求解方程近似根要求方程的左边包含未知量,而方程的右边仅为常数。
因此,本题的方程变形并化简为:
其中,
C=15
Excel解题过程如下所示:
(1)选定B3单元格为可变单元格,并在其中输入年利率i的假设值(初始值)3.00%,选定B4单元格作为目标单元格,并在单元格中输入方程右边的计算公式。
(2)使用excel的“单变量求解”求,并在对话框中输入可变单元格地址B3、目标单元格地址B4,及目标值15。
(3)完成上述操作,鼠标单击“确定”按钮后,得到计算结果,即实际年利率为3.42%。
第二节年金
(一)基本概念
年金就是一系列按照相等的时间间隔支付的款项。
例如,贷款购房后,其后10年每月等额归还贷款本息就构成一个年金。
退休后每月领取等额的退休金也构成一个年金。
年金分为期初年金和期末付年金两种。
期末付年金是每个付款期末付款,期初付年金是在每个付款期初付款。
(二)年金现值、积累值
1、年金现值、积累值的概念
对于年金来说,就是要计算年金的现值和积累值。
年金现值是指在整个支付期内每期支付的款项按复利计算原则折现到第1次支付期开始时的资金总额。
其含义相当于计算如果贷款购房,以后每期归还P元,共归还n期,求现在可贷贷款多少元的问题。
或如果以后希望每次领取P元养老金,共领取n期,那么现在需要存入多少钱。
年金积累值是指在整个支付期内每期支付的款项按复利计算原则积累到第n期末时的资金总额。
其含义是相于计算如果每期存款P元,那么到第n期末一共可收回多少本息。
2、年金现值、积累值的理论计算公式
假设年金的每期计息利率为i,则年金的现值和积累值可用如下示意图表示,并据此得到相应的计算公式。
(1)期末付年金的现值
每期支付P元共支付n期的期末付年金现值:
(2)期初付年金的现值
每期支付P元共支付n期的期初付年金现值:
(3)期末付年金的积累值
每期支付P元共支付n期的期末付年金积累值:
(4)期初付年金的积累值
每期支付P元共支付n期的期初付年金积累值:
(三)excel中年金计算的主要公式
在实际中年金的应用非常广泛,在在各种应用中,需要计算年金的现值、积累值、每期支付额、总支付期数,每期计息利率等,而年金的计算公式又比较复杂,因此excel设计了很多有关年金计算问题方面的函数,应用这些函数可以解决各种年金计算问题。
下面首先介绍各函数中的函数名及参数的含义:
PV——年金的现值;
FV——年金的积累值;
Rate——年金的每期计息利率;
NPER——年金的总支付期数;
Pmt——年金的每期支付额;
Type——年金类型,Type=0,表示期末付年金,Type=1表示期初付年金。
1、年金现值计算函数
格式:
PV(Rate,NPER,Pmt,0,Type)
功能:
计算每期计息利率为Rate,每期支付额为Pmt,共NPER期年金的现值。
2、年金积累值计算函数
FV(Rate,NPER,Pmt,0,Type)
计算每期计息利率为Rate,每期支付额为Pmt,共NPER期年金的积累值。
3、年金每期支付额计算函数
格式I:
PMT(Rate,NPER,PV,0,Type)
已知年金现值PV,计算每期计息利率为Rate,共NPER期年金的每期支付额。
格式II:
PMT(Rate,NPER,0,FV,Type)
已知年金积值FV,计算每期计息利率为Rate,共NPER期年金的每期支付额。
4、年金总支付期数计算函数
NPER(Rate,Pmt,PV,0,Type)
已知年金现值PV,计算每期计息利率为Rate,每期支付额为Pmt的年金的总支付期数。
已知年金积累值FV,计算每期计息利率为Rate,每期支付额为Pmt的年金的总支付期数。
5、年金每期计息利率计算函数
RATE(NPER,Pmt,PV,0,Type,[Guess])
已知年金现值PV,计算每期支付额为Pmt,共NPER期年金的每期计息利率。
RATE(NPER,Pmt,0,FV,Type,[Guess])
已知年金积累值FV,计算每期支付额为Pmt,共NPER期年金的每期计息利率。
其中,Guess是使用者估计的每期计息利率,在计算时由使用者给定,可以省略,如果省略了,则excel自动假设Guess=10%。
注意:
(1)在PV函数、FV函数及PMT函数中,PV、FV的值与Pmt值的符号是相反的,即如果如果Pmt的值为正数,则PV、FV的值就是负数,如果如果Pmt的值为负数,则PV、FV的值就是正数。
(2)函数中PV、FV的值与Pmt的值符号必须相反,即如果PV或FV的值为正,则Pmt就必须为负,如果PV或FV的值为负,则Pmt就必须为正。
(四)年金现值计计算问题
[例1]已知每期计息利率为5%,每期支付额为1000元,共10期的年金的现值。
年金现值计算
每期计息利率Rate
每期支付额Pmt
总支付期数NPER
期末付年金现值(Type=0)
7721.73
=PV(B3,B5,-B4,0,0)
期初付年金现值(Type=1)
8107.82
=PV(B3,B5,-B4,0,1)
[例2]如果某大学生在校时每月需生活费1000元,家长打算在该生进校时在银行存一笔钱,使其在校4年期间可以每月初从银行取出当月生活费,已知银行存款年利率为3.2%,请计算应该存多少钱。
每月领取的生活费构成一个4*12期的年金,计息周期为月,所以每期的计息利率为月利率,
在计算时应将年利率转换为月利率。
3.20%
月计息利率rate
0.27%
=B3/12
领取年数n
每月领取金额pmt
月初领取应存入金额
45118.76
=PV(B4,B5*12,-B6,0,1)
月末领取应存入金额
44998.77
=PV(B4,B5*12,-B6,0,0)
[例3]某人现年40岁,拟现在存一笔钱,使其能在60岁至80岁的20年间,每年初能从银行支取8000元,如果存款年利率为6.5%,问现在需要存入多少钱
这个问题可以理解为60岁开始的20年期的年金,用年金现值函数计算得到的年金的现值是60岁时的现值,而存款是在40岁是,所以还要将这个年金的现值再折现到40岁时的价值,折现年利率为6.5%,折现20年,所以应用存入的金额为:
年利率rate
6.50%
60岁-80岁每年支付额pmt
20
40岁时应存入金额
26642.21
=PV(B3,B5,-B4,0,1)/(1+B3)^20
[例4]某人购买一处住宅,价值160万元,拟首期付款A元,其余部分向银行贷款,贷款自下月起每月月初偿还10000元,共付10年,设年计息12次的年名义利率为8.7%。
问:
(1)可以从银行贷款多少元?
(2)购房首期付款额A是多少?
从下月初开始偿还贷款,也就是从本月末开始偿还贷款,每月偿还10000元构成一个10*12=120期的期末付年金,这个年金的现值就是贷款金额,总房价减去贷款额就是首付额。
总房价
1600000.00
8.70%
月利率rate
0.73%
=B4/12
偿还年数n
每月偿还额pmt
10000.00
贷款金额PV
799629.61
=PV(B5,B6*12,-B7,0,0)
9
首付金额A
800370.39
=B3-B8
(五)年金积累值计算问题
[例5]已知年利率为5.2%,如果每月在银行存入1000元,问,到第10年末时,这些存款的本息和是多少?
年金积累值计算
每期计息利率rate
5.20%
总期数NPER
期末付年金积累值FV1
12695.93
=FV(B3,B4,-B5,0,0)
期末付年金积累值FV2
13356.12
=FV(B3,B4,-B5,0,1)
[例6]已知年利率为3.4%,某人拟从现在开始,前3年每月末在银行存入1000元,,第4年初至第5年末每月末存入1500元,问到第5年末可从银行取出本息共多少?
将第4年初至第5年末每月末存入的1500元折分成两部分1000+500,前3年每月末存的1000元与后2年折分出来的1000元,构成一个每期末支付1000元,共60期的年金,而后2年每月剩余部分构成一个每期末支付500元共24期的年金,2个年金的结束时间均为第5年末,所以计算出来的两个年金的积累值可以直接相加等于5年所有存款的本息和。
年金示意图
3.40%
0.28%
=C3/12
每月末存款额Pmt1
存款月数NPER1
60
至第5年末的积累值FV1
65301.16
=FV(C4,C6,-C5,0,0)
每月末存款额Pmt2
存款月数NPER2
24
至第5年末的积累值FV2
12399.25
=FV(C4,C9,-C8,0,0)
11
至第5年末所有存款的
积累值
77700.41
=C7+C10
(六)年金每期支付额计算问题
[例题7]某人向银行贷款10万,贷款年计息12次的名义利率为7%,拟在3年时间里每月等额偿还贷款本息,问每月的偿还额是多少?
年金每期支付额计算
7.00%
0.58%
还款年数n
贷款额PV
100,000.00
月初还款每月应偿还Pmt1
3,069.80
=PMT(B4,B5*12,-B6,0,1)
月末还款每月应偿还Pmt2
3,087.71
=PMT(B4,B5*12,-B6,0,0)
[例题8]某人想在第5年末购买一辆100万元的钱,5年中每月末等额在银行中存入一笔钱,如果年利率为4.5%,问,
(1)每月应存入多少钱?
(2)如果某人现在已经有20万,那么每月又应该存入多少元钱?
对于第1个问题,每月存款的金额构成一个60期的年金,这个年金的积累值应该等于100万,据此可以得到年金的每月的存款额Pmt1。
对于第2个问题,现在已有20万按月利率4.5%/12存到第5年末,将积累为
,那么每月的存款额构成的年金到第5年末的积累值只需要
就够了,所以第2个问题应以此为年金的积累值来计算每月的存款额Pmt2。
如果现有的20万元是按年计息,那么年金的积累值应该为
,据此可以计算每月存款额Pmt3。
4.50%
0.38%
第n年末需要的总金额
1,000,000.00
现有资金为0每月末需要存款的金额Pmt1
14,893.02
=PMT(B4,5*12,0,-B5,0)
现有资金
200,000.00
现有资金按月计息存到第n年末的值
250,359.16
=B8*(1+B4)^(B6*12)
现有资金按月计息每月末需要存款的金额Pmt2
11,164.42
=PMT(B4,5*12,0,-(B5-B9),0)
现有资金按年计息存到第n年末的值
249,236.39
=B8*(1+B3)^B6
12
现有资金按年计息每月末需要存款的金额Pmt3
11,181.14
=PMT(B4,B6*12,0,-(B5-B11),0)
(七)年金总支付期数计算问题
[例9]某人贷款50万元购房,每月末等额偿还6500元,已知贷款年利率为8.7%,问需要多少多少个月才能还清贷款。
年金支付总期数计算
贷款年利率i
500,000.00
每月偿还额Pmt
6,500.00
需要偿还月数NPER
112.92
=NPER(B4,-B6,B5,0,0)
需要偿还年数
9.41
=B7/12
[例10]已知存款年利率为3.5%,如果每月初存款2000元,问需要多少时间(月)才能存够20万元。
0.29%
积累值金额FV
每月存款额Pmt
2,000.00
需要存款月数NPER
87.65
=NPER(B4,-B6,0,B5,1)
需要存款年数
7.30
(八)年金每期计息利率计算问题
[例11]如果从现在开始,每月存款1000元,到第3年末可从银行取出本息和共40000元,问月计息利率是多少?
年金每期计息利率计算
1,000.00
40,000.00
月初存款的月利率
0.56%
=RATE(B3*12,-B4,0,B5,1)
月末存款的月利率
0.59%
=RATE(B3*12,B4,0,-B5,0,1%)
在RATE()函数的参数中,年金现值PV或年金积累值FV的符号与每期支付额Pmt的符号必须相反;
参数Guess可以省略也可以不省略。
[例12]某人向金融机构贷款10万元,条件是每年末偿还1万元,共偿还15年,问贷款利率是多少?
100,000.00
每年末存款额Pmt
10,000.00
偿还年数NPER
15
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- Excel 金融 方面 应用