投资学实验指导说明书

1CAPMAPTVaRBlack-Scholes2Excel,WindowsExcelMacauley1DurationNCtt=1,2,,N-1N-1CNrP111(1)(1)NNttttttCtCPDurationrPr====++∑∑2111111(1)(1)(1)NttNNtttNttttttCttCrDwtCPrr====×+===×++∑∑∑∑32r1111(1)(1)(1)1NNtttttttCtCdPDPdrrrrr+==−==−=−++++∑∑//D/(1)/(1)dPPdPPdrrdrr==−++,PrdPdrPDPr/(1r)1dPdrDPr=−×∆∆∆≅××∆++-3BabcockBabcockNii=1(1)(,)*(1),yN1(,)N1+r)yyDNPVIFrNrrrPVIFrN=−++=∑=1+r1-y/ry/rBabcock3DrMDD/1+r.1,dpMDprPPrMDPdr=−∆∆∆≅−×∆×4EXCELEXCELDurationDuration(Settlement,Maturity,Coupon,Yield,Frequency,Basis),SettlementMaturityCouponYieldFrequencyBasis360/4MDuration1+=/(1)(),PDPrrPrMduration∆≅−××∆+=−×∆×MDuration5120(1)11(1)2(1)ntttCttyVPy=++=⋅⋅+∑VnCttP020()mPDyVyP∆=−⋅∆+⋅∆Excel1A10008%10B100012%1510ABA1B3B15B5B14EXCELNPVA2C5=A5*B5/($B$16*(1+$B$2)^A5),C5C143C16[]/[][SUM][Number1]C5C144[]C16BAB10AB52A10008%10B5100012%15108%8.01%1AB2AB61NPVAB28%8.01%8%AB16=NPVB3B6B158.01%AB19=NPVB4B6B15B16B19=NPVB3D6D15=NPVB4D6D153ABB20,=B19-B16/B16AD20=D19-D16/D16B4=B4-B3/1+B3AB21=-B20/D4BD21=-D20/D43BabcockABA1EXCELB3B52BB6=PV(B4,B3,-B5*1000)+1000/(1+B4)^B3,3B7=B5*1000/B64PVB8=-PV(B4,B3,1),EXCELPVIFr,NPV5BabcockB9=B3*(1-B7/B4)+B7/B4*B8*(1+B4),PVPV074EXCEL1B3B6C62B7[Duration]5A10008%10B100012%151010%8%8.8%AB1AA2C18=C16/1+B281C5C62AB5=-PV(8.8%,10,80)+1000/(1.088)^10-(-PV(8%,10,80)+1000/(1.088)^10),93BB6=-PV(8.8%,10,120)+1000/(1.088)^10-(-PV(8%,10,120)+1000/(1.088)^10),(4)AF5=-C5*D5*E6/1.085BF6=-C6*D6*E6/1.086EXCEL1C4C5C9C102AC3[Mduration]3AC6=-C5*C4*C3C11B10712[][][][][]D4[][]0300001$D$4,C4=D4/10000.1%3D7$D$7C7=D7/10004A11=DURATIONDATE200011DATE2000+B511B4B7B65C11=DURATIONDATE200011DATE2000+B511C4C7B66A13=A11/1+B7C13117B15U1512208B16=DURATIONDATE200011DATE2000+B511$C$4$C$7$B$6C16U16(9)B17=DURATIONDATE200011DATE2000+B5110$C$7$B$6C17:U17,10B15U17[][XY]8200681010201681015%121A9A240%1%2%15%2B9=DURATION$B$2,$B4A9$B$5,1,B10B243A9B24[][XY]200412108%10%1A8A20510154060801001502002B8=DURATION($B$2,DATE(2004+A8,12,10),$B$3,$B$51),B9B203A8B20[][XY]130200581010%2020258101A9A250%1%2%15%20%2B9=DURATION($B$2,$B$4,$B$5,A9,1),B10B253A9B25[][XY]910008%101000149%1D2=RATEB4B2*B3-B5B22D3=DURATIONDATE200011DATE2000+B411B3D213D4=D3/1+D24D5=B7-D25B10K10123106B11=$B$2*$B$3,B11J11K11=B2+J117B12K12=B11K11/1+D2^B10:K10,L12=SUMB12K12(8)B13=1/2*SUMPRODUCT(B12:K12,B10:K10,B10:K10+1)/L12*1/1+D2^29D6=-D4*D5+B13*D5^2,10D7=B5*1+D615Excel,WindowsExcel1ImmunizationRedington,1995351000055678%10%100NQNQ0,(1)NQPrr=+161,MM(1)MtBtttCPCr==+∑0000011101111BPP[](1)(1)NQ[](1)(1)1P(1)NMtBBBBBttMtBNtttttrrrdPNQPrPrdrrtCdPPPPrPrdrrtCNQPrPrrrtCr++=++=+=+∆−+∆≈+×∆=+∆+−+∆≈+×∆=+∆+−−+∆=+∆+++∑∑1MN=∑21122112211111()()1,0()()kkkkkiikkkiiiiiiiiiDxBxBxBxDBxDBxDBxwhenxxDxBxDB====+++=+++=⇒=∑∑∑∑320222211B22(1)(1),(1)(1)(1)1N(N+1)=,P(1)1=MtBNttMtttdPttCPNNQdrrdrrttCrdPConvexityPdr++==++==++++=×∑∑2d417B11BB(1)11N(N+1)=,P(1)P(1)MMttttttttPtCNrr==+=++∑∑Excel151000055678%10%1001B1B5B9D92B6=B1/1+B3^B2,(3)B10=-PV($B$3,B9,$B$5*$B$4)+$B$5/(1+$B$3)^B9,5C10D10674B11=DURATIONDATE200411DATE2004+B911$B$4,$B$3,1,5C11:D11675P0B12=$B$6/B10,5C12D12676B12D12B13D137B14=B13*B10-$B$6B14D1408[]567A15A30A14D30[]/[][]B3[]567189A15D30[]64.94035619251000055678%10%100575x71-xEXCEL[]1E11=B11*B16+D11*1-B16[]/[][]E11[]5[]$B$1652[]5x=0.4301.3B17=1-B1670.5699.4[]5765B19D34[]5766%10%576203567351000055678%10%1005673VBAsecondDuration(numberPayments,couponRate,YTM)bondprice(numberPayments,couponRate,YTM)11(1),(1)MtBttttCPr=++∑1[][]2[VisualBasic][MicrosoftVisualBasic][]/[][-1]21FunctionsecondDur(numberPayments,couponRate,YTM)ForIndex=1TonumberPaymentsIfIndexnumberPaymentsThensecondDur=couponRate*Index*(Index+1)_/(1+YTM)^Index+secondDurElsesecondDur=(couponRate+1)*Index*(Index+1)_/(1+YTM)^Index+secondDurEndIfsecondDur=secondDurNextIndexEndFunctionFunctionbondprice(numberPayments,couponRate,YTM)bondprice=1/(1+YTM)^numberPaymentsForIndex=1TonumberPaymentsbondprice=couponRate/(1+YTM)^Index+bondpriceNextIndexEndFunction223EXCELMMULT12E11=B11*B16+C11*B17+D11*B183E14=B16*B13*B10+B17*C13*C10+B18*D13*D10-B63E20=B16*B20+B17*C20+B18*D20E11E200E14-6209.21323B19[]/[][][]/[bondprice]B9$B$4,$B$3.4[]5bondpriceC19D195B20=secondDurB9$B$4,$B$3/B19,C20D206MMULT7B16B18=MMULTMINVERSEG16I18K16K18[Shift+Ctrl+Enter]3E115E20NN+1=308[]63923310%636430015%ABA10%5B8%201B11=B3/B4B12=1+B4/B42D12=DURATIONDATE200011,DATE(2000+D5,1,1),D4,D7,D6,E12AB3F12=SUMPRODUCTD12E12D13E134F13=SUMD13E135[][][][]$F$13;[]1[]$D$13:$E$13;[]$F$12=$B$12,$D$13:$E$13=0,[]A4.81%,B95.19%.2430025Excel,ExcelWindowsExcelNiwiW12(,,,)TN=W1()()()()NTwiiiTwErwErWErVarrWSW====∑E(r)SN-1212(,)TCov=Excel-320011220031225EXCEL1/2-31=0.20.40.4,2=0.50.30.2,4-EXCELB3D27326ABCD12ABC320011215.795425.848323.392342002118.109627.129624.033652002217.222826.217723.503962002316.393124.393822.458172002415.563426.082821.391382002516.259925.853522.345892002616.835424.477721.1025102002718.015325.731322.7998112002819.438528.096822.5169122002919.612127.519821.94341320021019.820929.182622.28541420021118.978129.008620.97451520021220.140628.776523.4447162003118.382730.398223.6745172003218.236631.0425.1686182003319.405630.9