财务实验报告Word下载.docx
《财务实验报告Word下载.docx》由会员分享，可在线阅读，更多相关《财务实验报告Word下载.docx（26页珍藏版）》请在冰豆网上搜索。
B
C
D
E
2
终值、现值计算表
3
年限
利率
终值（元）
现值（元）
年金（元）
4
5
6%
10000
1000
现值
----------
6
终值
7
8
（2）在上表的A4：
E4中输入原始数据。
（3）参考下表所示，利用FV、PV函数在上表中进行计算。
单元格
函数
D5
=PV（B4，A4，0，-C4）
C6
=FV（B4，A4，0，-D4）
E7
=PV（B4，A4，-E4，0）
E8
=FV（B4，A4，-E4，0）
（4）计算结果，如下表所示。
7472.58
13382.26
4212.36
5637.09
A．在单元格中插入RATE函数，输入内容为：
=RATE（5，，-20000，50000）
运算结果为20.11%
B．在单元格中插入RATE函数，输入内容为：
=RATE（25,-1600,20000）
运算结果为6.24%
A．在单元格中插入NPER函数，输入内容为：
=NPER（12%,,-50000,100000）
运算结果为6.12
B．在单元格中插入NPER函数，输入内容为：
=NPER（0.06,1600,-20000）
运算结果为23.79
实验一习题（资金时间价值）
1.在15年前由于A企业的发展遇到一些问题，急需大笔资金，王先生借给A企业150万元现金用于发展生产，考虑到风险水平，双方约定的利率为14％，A企业可以选择在15年内的任意时间点还钱，但是该企业一直没有足够的资金偿还，本年度是A企业还款的最后一年，A企业终于准备还款，请计算还款额。
=FV（14%，15，0，-1500000）=10,706,906.97
2.如果你突然收到一张事先不知道的1255亿美元的账单，你一定会大吃一惊。
而这件事却发生在瑞士的田纳西镇的居民身上。
该问题源于1966年的一笔存款。
斯兰黑不动产公司在田纳西镇的一个银行存入6亿美元的存款。
存款协议要求银行按1%的周利率复利付息（难怪该银行第二年破产！
）。
1994年，纽约布鲁克林法院做出判决：
从存款日到田纳西镇对该银行进行清算的7年中，这笔存款应按每周1%的复利计息，而在银行清算后的21年中，每年按8.54%的复利计息。
请说明1255亿美元如何计算出来的并思考该案例的启示。
=FV（8.54%,21,0,-FV（1%，365，0，-6））=1,267.18
或FV1=FV（1%，365，0，-6）
则FV=FV（8.54%,21,0,-FV1）
3.某人持有一份10年公司债券，在取得的时候忘记了支付的金额，但是已知本年度截至日可以取得的本金和利息之和为124800元，票面利率为9.2％，求该公司债券的票面金额。
=PV（9.2%,10,0,-124800）=51,759.28
4.王先生准备与朋友投资一个新项目，按照规定，王先生提供项目所需的150万元资金，但是不享受公司股份，只享受未来公司给其分配的固定收益，收益额为每年30万元，期限为8年，已知王先生的如果不做这个项目，可以获得的最低投资报酬率为18％，问王先生是否该投资此项目。
=RATE（8,300000,-1500000）=12%
5.基金中有一种购买方式为定投型基金，是约定银行每月按期定额从制定帐户扣除现金用于购买基金的一种产品。
如果A家庭现在开始使用定投方式购买基金，月投入额为1500元，问15年后可以取出多少钱。
（基金长期的年投资报酬率假设为24％）
=FV（2%,180,-1500,0）=2,574,062.35
6.小王准备储存40万元用于子女将来的出国费用，现在开始向银行存款，已知存款的利率为7.85％，如果每年存款额为3万元，问几年小王可以达到40万的存款额？
=-NPER（7.85%,30000,400000）=9.48
7.如果你手中拥有100万元的现金，有人愿意向你借钱，其承诺未来10年每年还款17万元，而同期你可以获得的年平均收益率为12％（一分利），不考虑借款人的风险差异，问是否应该借钱给他？
=RATE（10,170000,-1000000）=11%
8.某人10年中每年年末存入银行1000元,10年后取出时终值为30000,求利率。
=RATE（10,-1000,0,30000）=22.92%
某人现在存入银行3000元,以后10年中每年年末存1000元,10年后取出时终值为30000,求利率。
=RATE（10,-1000,-3000,30000）=13.76%
公式为＝RATE（NPER,-PMT,-PV,FV）
9.再参考年金终值系数表编制一个先付年金终值系数表（可以用数组公式）
（1）横轴为利率1％——10％,纵轴为年限1年——30年
（2）再参考年金现值系数表编制一个先付年金现值系数表
横轴为利率1％——10％,纵轴为年限1年——30年
实验二筹资预测与决策
通过实验，使学生了解资金预测时需要考虑的因素和主要方法，能够使用EXCEL软件中的SLOPE函数,INTERCEPT函数进行回归分析，能利用EXCEL进行本量利分析，计算资本成本率、财务杠杆系数和营业杠杆系数。
1.资本需求量的预测：
在利用回归分析法进行预测时，是按照历史数据，按照数学上最小平方法的原理，确定能够正确反映自变量和因变量之间具有最小误差平方和的回归方程作为预测模型进行预测的方法。
主要使用的函数有：
SLOPE函数,INTERCEPT函数。
（LINEST为另外一种计算斜率、截距和相关系数的函数）
SLOPE函数：
=SLOPE（known_y’s,known_x’s）
计算线性回归直线的斜率
INTERCEPT函数：
=INTERCEPT（known_y’s,known_x’s）计算直线与y轴的截距
PEARSON函数：
=PEARSON（array1,array2）计算y和x的相关系数r，也可以使用另外一个函数=INDEX（LINEST）计算计算y和x的相关系数r2，相对复杂，具体使用方法参考后面的实验。
LINEST函数和后面一元二次回归方法一样，可以一次计算a和b的数值，INDEX是在所计算的结果中去定位具体a和b的位置。
2.资本成本率的计算：
（1）长期债券成本。
计算公式为：
Kb=Ib（1-T）/[B（1-Fb）]
（2）长期借款成本。
Kl=Il（1-T）/[L（1-Fl）]
（3）普通股成本。
Knc=D/[P（1-Fc）]
+G
（4）留存收益成本。
Ks=D/P+G
（5）综合资本成本。
Kw=∑KjWj
1.资金需要量预测
案例一、一元回归分析
某企业在过去5年中销售收入与资金占用量的有关数据如图所示，则利用回归分析法预测资金需要量的具体步骤如下（假设销售收入与资金占用量之间为线性关系）：
（1）在单元格B10中输入公式“=INTERCEPT（C3:
C7,B3:
B7）”，计算a截距
或者输入公式“=INDEX（LINEST（C3:
B7,,TRUE）,1,2）”
或者“=INDEX（LINEST（C3:
B7,TRUE,TRUE）,1,2）”
B7）,1,2）”
B7）,2）”
（2）在单元格B11中输入公式“=SLOPE（C3:
B7）”,计算系数b（斜率）；
B7,,TRUE）,1,1）”
B7）,1,1）”
B7）,1）”
（3）在单元格B12中输入公式“=INDEX（LINEST（C3:
B7,,TRUE）,3,1）”,计算相关系数r2，相关系数达0.9503，故资金占用量与销售收入之间的线性关系较为显著，可以利用直线回归方程进行预测；
（由于本题已经假设y和x存在线性关系，本步骤可以省略）也可以使用PEARSON函数“=PEARSON（C3:
B7）”，得到的结果为r，有别于上面计算的结果r2。
（4）在单元格B15中输入公式“=B10+B11*B14”,得到2004年的预计资金需要量为160.82万元。
已知条件（金额单位：
万元）
年度
销售收入（x）
资金总额（y）
1999
256
126
2000
271
139
2001
264
132
2002
295
148
2003
316
154
回归方程有关参数的计算
9
回归方程
y=a+bx
10
a=
11
b=
12
r=
13
2004年资金需要量预测（万元）
14
预计销售收入
327
15
预计资金需要量
通常在相关系数r在0.8以上时，就可以认为自变量和因变量的相关关系成立，否则相关关系不成立，可以采用线性回归分析法，否则只能采用其他方法。
实验结果：
13.34
0.45
97.49%
160.82
当然还有很多其他的函数可以进行预测分析，例如forecast函数，trend函数
如上题使用forecast函数可以不需要计算斜率和截距，直接计算当销售收入等于327的时候对应的资金需要量，B15单元格输入内容：
=forecast（B14,C3:
B7）
Trend函数和forecast函数类似，也不需要计算斜率和截距，直接计算当销售收入等于327的时候对应的资金需要量，B15单元格输入内容：
=trend（C3:
B7,B14）
下面解释下INDEX和LINEST函数的意思。
LINEST函数可以同时计算a、b、r，INDEX函数可以从前面结果中读取a、b、r
步骤：
由于是回归分析，在10行以下预留不少于三行，二列空白单元格，多则不限
选择多个预留空白单元格，输入数组公式=LINEST（C3:
B7,TRUE,TRUE），或者输入“=LINEST（C3:
B7,,1）”，然后按CTRL+SHIFT+ENTER，生成如下数据，相应位置的数据会表示确定的变量值。
黑色底纹单元格不同位置代表不同的数据结果，b位于第1行第1列，a位于第1行第2列，r2位于第3行第1列,因此如果不想先计算上面的结果，直接求a，b和r2可以使用复合函数。
比如直接在A10单元格输入公式“=INDEX（LINEST（C3:
B10单元格输入公式“=INDEX（LINEST（C3:
C10单元格输入公式“=INDEX（LINEST（C3:
B7,,TRUE）,3,1）”
b=A11
a=B11
r2=A13
0.451011
13.33651
#N/A
0.059521
16.74121
0.950345
2.936013
57.41644
494.9395
25.86051
案例二、二元回归分析
某企业在本年度1-9月份总成本与机器工时、人工小时的有关数据如图所示，则利用回归分析法预测线性函数（y=a+bx1+cx2）的具体步骤如下（假设销售收入与机器工时、人工小时之间为线性关系）：
月份
总成本y
机器工时x1
人工小时x2
50
100
1120
55
128
1230
62
145
1350
70
180
1450
75
200
1580
78
235
1660
80
250
1800
92
280
1900
300
120
350
y=a+bx1+cx2
c=A14
b=B14
a=C14
XX
16
17
18
19
r2=A16
标准差=B16
20
10月总成本
由于是二元回归分析，在13行以下预留不少于三行，三列空白单元格
选择多个预留空白单元格，输入数组公式“=LINEST（B2:
B10,C2:
D10,TRUE,TRUE）”
或者输入“=LINEST（B2:
D10,,1）”
然后按CTRL+SHIFT+ENTER，生成如下数据，相应位置的数据会表示确定的变量值。
需要注意x1和x2的位置在已知条件中和结果中处于不同的顺序。
3.61653
3.4323459
471.43655
0.36776
1.560862
43.41869856
0.9989
11.779178
2719.98
754790
832.49418
2149.11
因此二元回归模型为y=471.44+3.43x1+3.62x2
如果题目要求计算当十月份机器工时和人工小时分别是120和350时的总成本，直接代入即可计算。
或者使用TREND函数：
B20单元格输入=TREND（B2：
B10，C2：
D10，C11：
D11）
2.资本成本的计算
实验资料：
某公司拟筹资4000万元，其中按面值发行长期债券1000万元，票面利率10%，期限4年，筹资费率1%，每年末付息一次，到期一次还本；
长期借款600万元，3年期，年利率11%；
发行普通股2000万元，筹资费率4%，预计第一年股利率12%，以后每年按照4%的增长率递增。
此外，公司保留盈余400万元，所得税税率33%。
要求：
计算该公司的综合资本成本。
实验步骤
（1）创建工作表，如下表所示
个别资本成本及综合资本成本
筹资种类
长期债券
长期借款
普通股
留存收益
筹资金额（万元）
600
400
票面利率
10%
11%
——
期限
股利率
12%
年增长率
4%
筹资费率
1%
所得税税率
33%
个别资本成本
筹资总额（万元）
个别资本成本所占比重
综合资本成本
（2）在上表的B3：
E9单元格区域内输入原始数据。
（3）参考下表所示，利用相应公式在上表中进行计算。
公式或函数
B10
=B4*（1-B9）/（1-B8）
C10
=C4*（1-B9）/（1-C8）
D10
=D6/（1-D8）+D7
E10
=E6+E7
B11
=SUM（B3：
E3）
B12：
E12
=B3：
E3/$B$11
B13
=B10*B12+C10*C12+D10*D12+E10*E12
或者=SUMPRODUCT（B10:
E10,B12:
E12）
6.77%
7.37%
16.50%
16.00%
4000
25%
15%
50%
12.65%
思考题：
非固定增长股利模型
某股票筹资总额为1000万元，筹资费率3%，第一年股利率（相对筹资总额）10%，第二年---五年都是按照10%递增，从第六年开始保持200万不变，直到第十年，公司运营结束，求资本成本率。
（不考虑公司到期后公司剩余资产）
创建表格如下：
筹资额
前五年股利固定增长率
3%
后五年股利额
第一年股利率
期数
股利
假设资本成本率
净现值
7%
升级会员 