《⾦融学》课程实验报告
年级专业__投资学__姓名__翁⾟⽥__学号_120152853指导教师__李慧____上海师范⼤学商学院⽬录实验项⽬⼀实验项⽬⼆
实验项⽬⼀ 资⾦时间价值【实验⽬的】
学⽣掌握货币时间价值的计算原理,包括单利、复利、年⾦终值、年⾦现值、永续年⾦、永续增长年⾦、名义利率和实际利率的关系、以及不规则现⾦流的终值与现值计算。掌握单变量求解⽅法计算还款期限或求解利率。不同还款⽅式下本⾦和利息的计算。
【实验原理（或预备知识）】 1、 单利终值单利现值复利终值复利现值
普通年⾦的终值 普通年⾦的现值
先付年⾦的终值：普通年⾦+再向后复利⼀期 先付年⾦的现值：普通年⾦+再向后复利⼀期永续年⾦的现值：
FV 函数的功能是基于固定利率及等额分期付款⽅式，返回某项投资的未来值。公式为= FV （rate ，nper ，pmt ，pv ，type ）FV 函数假定FV 与PV 、PMT 的符号正负相反
PV 函数的功能是返回未来若⼲期资⾦的现值。现值为⼀系列未来付款当前值的累积和。公式为= PV （rate ，nper ，pmt ，fv ，type ）PV 函数假定PV 与 FV 、PMT 的符号正负相反
2、有效年利率与名义年利率之间 )1(n i P n i P P F s s s ?+?=??+=)1/(n i F P s s ?+=n i P F )1(+?=ni F P )1/(+=i i A i A F n n t t1)1()1(1-+?=+?=∑-=n n n t ti i i A i A P )1(1)1()1(11+?-+?=+?=∑=i A V /0=1)1(-+=m miγ
EFFECT 函数的功能是利⽤给定的名义年利率和⼀年中的复利期数，计算有效年利率。公式为：= EFFECT （nominal_rate ，npery ）
NOMINAL 函数的功能是基于给定的有效年利率和年复利期数，返回名义年利率。公式为= NOMINAL （effect_rate ，npery ） 3、不规则现⾦流的终值与现值不规则现⾦流的终值：只能输⼊公式不规则现⾦流的现值：利⽤NPV 函数
NPV 函数的功能是基于⼀系列现⾦流和固定的各期贴现率，返回⼀项投资的净现值。这⾥的投资净现值是指未来各期现⾦流的现值总和。公式为：= NPV(rate,value1,value2,...)
XNPV 函数的功能是返回⼀组现⾦流的净现值，这些现⾦流不⼀定定期发⽣。该函数的公式为=XNPV(rate,values,dates) 217页【例5-17】4、利率的求解⽅法
利⽤RATE 函数求解利率 利⽤IRR 函数求解利率
利⽤XIRR 函数求解⾮定期发⽣的现⾦流量对应的利率 利⽤单变量求解⼯具求解利率
RATE 函数的功能是返回未来款项的各期利率。公式为 = RATE （nper ，pmt ，pv ，fv ，type ，guess ）IRR 函数的功能是返回由数值代表的⼀组现⾦流的内部收益率。公式为 =IRR(values,guess)
XIRR 函数的功能是返回⼀组现⾦流的内部收益率，这些现⾦流不⼀定定期发⽣。公式为=XIRR(values,dates,guess) 【例5-20】
利⽤单变量求解⼯具求解利率 221页【例5-21】5、求解期限
NPER 函数的功能是基于固定利率及等额分期付款⽅式，返回某项投资（或贷款）的总期数。公式为()∑=-+=nt n t i A FV 11
1()∑=+=n t t ti A PV 11
= NPER（rate，pmt，pv，fv，type）222页【例5-22】利⽤单变量求解⼯具求解期限223页【例5-23】6、利⽤PMT函数计算年⾦
PMT函数的功能是基于固定利率及等额分期付款⽅式，返回投资或贷款的每期付款额。公式为= PMT（rate，nper，pv，fv，type）224页【例5-24】7、每期等额还款额中本⾦和利息的计算PPMT函数的功能
基于固定利率及等额分期付款⽅式，返回投资或贷款在某⼀给定期次内的本⾦偿还额。公式为= PPMT（rate，per，nper，pv，fv，type）IPMT函数的功能
基于固定利率及等额分期付款⽅式，返回投资或贷款在某⼀给定期次内的利息偿还额。公式为= IPMT（rate，per，nper，pv，fv，type）225页【例5-25】【实验内容】具体参见EXCEL附件【实验步骤（或实验要求）】1.例5-2 复利终值系数表
1)计算复利终值，在B3单元格内输⼊“=FV(B$2%,$A3,,-1)”，算出B3单元格的值，再拖取，即可得出整个表格。
2.例5-4 复利现值系数表
1)计算复利现值，在B17单元格内输⼊“=PV(B$16%,$A17,,-1)”，算出B17单元格的值，再拖取，即可得出整个表格。
3.例5-6 年⾦终值系数表
1)计算复利终值，在B32单元格内输⼊“=FV(B$31%,$A32,-1)”，算出B32单元格的值，再拖取，即可得出整个表格。
4.例5-8 年⾦现值系数表
1)计算复利终值，在B46单元格内输⼊“=PV(B$45%,$A46,-1)”，算出B46单元格的值，再拖取，即可得出整个表格。
5.例5-15 不规则现⾦流的终值和现值
1)利⽤NPV函数先计算不规则现⾦流的现值,选定D61单元格，利⽤NPV函数，选择B66中的利率及“B61：B65”中数据，公式为
“=NPV(B66,B61:B65)”
2)求终值需要利⽤现值，将现值复利，具体公式为“=NPV(B66,B61:B65)*(1+B66)^5”
6.5-18 ⽤RATE函数求利率
1)选取D3单元格，利⽤RATE函数，输⼊如下公式“=RATE(B4,B5,-B3)”，得出贷款年利率。
7.5-19 ⽤IRR函数求年投资收益率
1)选取D11单元格，通过IRR函数，具体公式为“=IRR(B12:B17)”
8.5-20 ⽤XIRR函数求年投资收益率
1)选取D23单元格，通过XIRR函数，公式为“=XIRR(B23:B27,A23:A27)”
9.单变量求解
1)D3输⼊公式“=NPV(D2,B4:B8)+B3”，求出净现值。2)⽤单变量求解
3)求出最终结果
10.年⾦的计算例 5-24
1)求每年年末等额偿还，公式“=PMT(B4,B5,,-B3)”
2)求每年年初等额偿还，公式“=PMT(B4,B5,,-B3,1)”
11.年⾦的计算例 5-25
1)B13单元格输⼊“=PPMT($B$10,A13,$C$10,-$A$10)”，再下拉。
2)C13单元格输⼊“=IPMT($B$10,A13,$C$10,-$A$10)”，再下拉。
3)选取单元格区域D13:D17，插⼊PMT函数，在PMT函数对话框中输⼊
相关参数，如图所⽰，然后按住Ctrl+Shift+Enter组合键，即得各年等额还款的数额。
12.年⾦的计算补充
1)B26单元格输⼊“= A23/5”，再下拉。
2)在C26单元格中输⼊公式“=($A$23-(A26-1)*$B$26)*$B$23”，在B26:B30
各单元格中分别输⼊公式“=($A$23-(A27-1)*$B$26)*$B$23”、“=($A$23-(A28-1)*$B$26)*$B$23”、“=($A$23-(A29-1)*$B$26)*$B$23”、“=($A$23-(A30-1)*$B$26)*$B$23”，得到各年⽀付利息。3)在D26单元格中输⼊公式“=B26+C26”,下拉。13.本⾦的计算例 5-26
1)D36每⽉等额还款公式“=PMT(B37/12,B38*B39,-B36)”。
2)在单元格D37输⼊“=ABS(CUMIPMT(B37/B39,B38*B39,B36,13,24,0))”，得到第⼆年⽀付的总利息。
3)在单元格D38输⼊“=ABS((CUMPRINC(B37/B39,B38*B39,B36,13,24,0))) 14.2009上海房贷市场1)在D5单元格输⼊“=10000/PV(C5/100,A5*12,-1)”，下拉。15.求解期限例5-22
1)D3单元格输⼊“=NPER(B5,B4,B3)”
16.求解期限例 5-23
1)D12输⼊“=FV(B12,D11,-B11)”2)单变量求解
【实验结果与分析】
通过⼤量的实验，我们通过FV 、PV 、NPV 、IRR 、RATE 、XIRR 、PMT 、PPMT 、IPMT 、NPER 这⼏类函数，能解决⼤多数的关于资⾦时间价值的问题。实验项⽬⼆ 风险与收益【实验⽬的】
本实验主要运⽤excel 计算股票投资收益率、单个债券和债券投资组合的期望收益与风险。并在掌握以上基础知识的前提下构建最优投资组合。
【实验原理（或预备知识）】
1、利⽤RATE 函数计算债券的年投资收益率2、 根据投资时间计算股票的持有期收益率3、 根据收益率的概率分布计算期望收益率 OFFSET 函数
功能是以指定的引⽤为参照系，通过给定偏移量得到新的引⽤。 公式为 =OFFSET(reference,rows,cols,height,width)股票的买⼊价格持有股票的年数投资期的股利收⼊）股票买价（股票卖价年投资收益率÷+-=∑=?=nt itt i R P R 1
4、 证券投资组合的期望收益率5、计算投资组合标准差
【实验内容】具体参见EXCEL 附件【实验步骤（或实验要求）】1. rate 函数【例9-14】
1） 计算债券的年投资收益率，在单元格B10中输⼊公式“=RATE(B5*B7,B3/(B5*B7)+B3*(B4/B7),-B8,,0)”
∑=?=ni i
i P R w R 1
2）计算债券的年投资收益率，在单元格C10中输⼊公式“=RATE(C5*C7,C3/(C5*C7)+C3*(C4/C7),-C8,,0)”
2.股票持有期收益率【例9-15】
1）计算股票的持有期（年），在单元格D2中输⼊公式“=(B4-B2)/365”
2）计算股票的年投资收益率，在单元格D3中输⼊公式“=((B5+B6+B7-B3)/B3)/D2”3.债券期望收益率【例9-16】
1)计算期望收益率，在单元格C11输⼊公式
“=$B$4*C$4+$B$5*C$5+$B$6*C$6+$B$7*C$7+$B$8*C$8”
2)将指针放在在单元格C11右下⾓，当出现⿊⾊⼗字星符号时单击⿏标左键并向右拖拽⾄单元格F11
4.投资组合期望收益率【例9-18】1)重复【例9-16】步骤1、2
2)计算投资组合的期望收益率，在单元格C13输⼊公式“=C11*C12+D11*D12+E11*E12+F11*F12”5.投资组合标准差【例9-19】1)重复【例9-16】步骤1、22)重复【例9-18】步骤2
3)计算⽅差，在单元格C14输⼊公式
“=$B$4*(C11-C4)^2+$B$5*(C11-C5)^2+$B$6*(C11-C6)^2+$B$7*(C11-C7)^ 2+$B$8*(C11-C8)^2”4)将指针放在在单元格C14右下⾓，当出现⿊⾊⼗字星符号时单击⿏标左键并向右拖拽⾄单元格F14
5)计算标准差，在单元格C15输⼊公式“=C14^(1/2)”
6)将指针放在在单元格C15右下⾓，当出现⿊⾊⼗字星符号时单击⿏标左键并向右拖拽⾄单元格F15
7)计算标准离差率，在单元格C16输⼊公式“=C15/C11”
8)将指针放在在单元格C16右下⾓，当出现⿊⾊⼗字星符号时单击⿏标左键并向右拖拽⾄单元格F16
9)计算投资组合的⽅差，在单元格I8输⼊公式
“=SUMPRODUCT(MMULT(C12:F12,H4:K7),C12:F12)”
10)计算投资组合的标准差，在单元格I9输⼊公式“=SQRT(I8)”6.最优投资组合【例9-20】
1)计算期望收益率，在单元格C7输⼊公式
“=$B$2*C$2+$B$3*C$3+$B$4*C$4+$B$5*C$5+$B$6*C$6”
2)将指针放在在单元格C7右下⾓，当出现⿊⾊⼗字星符号时单击⿏标左键并向右拖拽⾄单元格F7
3)计算⽅差，在单元格C8输⼊公式
“=$B$2*(C$2-C$7)^2+$B$3*(C$3-C$7)^2+$B$4*(C$4-C$7)^2+$B$5*(C$5-C $7)^2+$B$6*(C$6-C$7)^2”4)将指针放在在单元格C8右下⾓，当出现⿊⾊⼗字星符号时单击⿏标左键并向右拖拽⾄单元格F8
5)计算标准差，在单元格C9输⼊公式“=C8^(1/2)”
6)将指针放在在单元格C9右下⾓，当出现⿊⾊⼗字星符号时单击⿏标左键并向右拖拽⾄单元格F97)在单元格C12输⼊公式
“=SUMPRODUCT($B$2:$B$6,$C$2:$C$6-$C$7,(OFFSET(C2:C6,0,0)-OFFSET( C7,0,0)))”
8)在单元格C13输⼊公式
“=SUMPRODUCT($B$2:$B$6,$D$2:$D$6-$D$7,(OFFSET(C2:C6,0,0)-OFFSET( C7,0,0)))”
9)在单元格C14输⼊公式
“=SUMPRODUCT($B$2:$B$6,$E$2:$E$6-$E$7,(OFFSET(C2:C6,0,0)-OFFSET( C7,0,0)))”
10)在单元格C15输⼊公式
“=SUMPRODUCT($B$2:$B$6,$F$2:$F$6-$F$7,(OFFSET(C2:C6,0,0)-OFFSET( C7,0,0)))”
11)选取单元格区域C12：C15将，指针放在选区右下⾓，当出现⿊⾊⼗字星符号时单击⿏标左键并向右拖拽⾄单元格F5,得到协⽅差矩阵12)在单元格B17中输⼊公式“=SUM(C17:F17)”13)计算投资组合收益率，在单元格B18输⼊公式“=SUMPRODUCT(C7:F7,C17:F17)”
14)计算投资组合⽅差，在单元格B19输⼊公式
“=SUMPRODUCT(MMULT(C17:F17,C12:F15),C17:F17)”，同时按Shift+Ctrl+Enter组合键。15)计算投资组合标准差，在单元格D19输⼊公式“=SQRT(B19)”
16)选择加载项【规划求解】，出现【规划求解参数】对话框。如图设置。
17)单击【求解】按钮。计算结果如图所⽰
18)求解最佳投资组合，再次打开【规划求解参数】对话框，如图设置。
因篇幅问题不能全部显示，请点此查看更多更全内容