Seminar Assignments - Excel Assignment #5 PDF

Preview

Summary

Excel Assignment #5 for the course...

Description

Question #1 Part A

Security A Security B Security C

YTM 4.706% 3.731% 4.250%

Part B Wa 0.542011019284 Wb 0.457988980716 MVp $36,300,000 Duration 6.7037 Dollar Duration $2,433,446.92 PVBP $23,804.29 1 bps change in yield Security A $19,659,353.79 Security B $16,616,841.93

MV $19,675,000 $16,625,000 $19,700,000

Macaulay Duration 8.1433 5.0000 6.1665

Dollar Duration $1,602,196.92 $831,250.00 $1,214,805.38

PVBP $15,646.21 $8,158.07 $11,891.10

Question #2

Security A Security B Security C

YTM 4.706% 3.731% 4.250%

MV $19,675,000 $16,625,000 $36,300,000

Long Portfolio 0.542011019284 0.457988980716

Wa Wb

Long-Short Portfolio W_long 100.0% W_short -100%

6.7037 $36,300,000

Duration MV

Macaulay Duration 8.1433 5.0000 6.1665

MV Duration

Short Portfolio Duration 6.1665 MV -$36,300,000 Par Value $36,852,791.88

$0.00 0.537

$36,299,980.49

2 Years Passed

Security A Security B Security C

Wa Wb

YTM 5.250% 4.350% 4.750%

Long Portfolio 0.519840308922 0.480159691078

Duration MV

4.9596 $36,608,035

Actual Value

$36,608,035

Duration MV

Short Portfolio 4.5724 -$35,635,424

Price 0.951516612704 0.8788851402452 0.9669667511232

MV $19,030,332 $17,577,703 $35,635,424

Macaulay Duration 6.7696 3.0000 4.5724

Long-Short Portfolio W_long 102.7% W_short -100% MV Duration

$972,610.63 0.523

6.769612

Duration estimated value of the equally weighted portfolio 9/1/2015 2 Years Passed 9/1/2023 Security A Security B Security C

Estimated Value Actual Value

YTM 4.706% 3.731% 4.750%

$35,580,194.08 $36,608,035

Price 0.986425068 0.89503289 0.966966751

MV $19,030,332 $17,577,703 $19,339,335

Macaulay Duration 6.7696 3.0000 4.5724

Question #3 N 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39

CFs $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000

PVCF 9972.99 #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?

INITIAL VALUES Present Value of the Annuity $553,097.27 Macaulay Duration 2.4741 ADURATION 2.4741 2 YEARS HAVE PASSED Present Value of the Annuity $342,565.29 Macaulay Duration 1.5173 ADURATION 1.5173

40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60

$10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000 $10,000

#NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME?

Question #4 Security A Coupon Term Price Par YTM

5% 20 103.88% $10,000,000 4.699%

Method 1: Excel Function Settlement 10/5/2015 Maturity 10/5/2035 Duration

13.0119176313026

Method 2: Table of CFs Duration 13.01191763

Method 3: Closed Form Solution Duration 13.0119176313026

N 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38

CF $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0 $250,000.0

39 40

$250,000.0 $10,250,000.0

PVCF $244,261.04 $238,653.81 $233,175.31 $227,822.57 $222,592.71 $217,482.90 $212,490.40 $207,612.50 $202,846.58 $198,190.06 $193,640.44 $189,195.26 $184,852.12 $180,608.68 $176,462.65 $172,411.80 $168,453.94 $164,586.94 $160,808.70 $157,117.20 $153,510.44 $149,986.48 $146,543.41 $143,179.38 $139,892.57 $136,681.22 $133,543.59 $130,477.98 $127,482.74 $124,556.27 $121,696.97 $118,903.32 $116,173.79 $113,506.92 $110,901.27 $108,355.44 $105,868.05 $103,437.75

$101,063.25 $4,048,473.57

Question #5

N 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

Change in Interest Interest Rates Rate Path 0.000% 0.04699 0.018% 4.717% -0.088% 4.629% 0.033% 4.663% 0.019% 4.681% 0.007% 4.688% -0.010% 4.678% -0.029% 4.650% 0.077% 4.727% -0.033% 4.694% -0.022% 4.672% 0.030% 4.702% -0.045% 4.657% 0.031% 4.688% 0.027% 4.715% -0.060% 4.655% -0.128% 4.528% -0.034% 4.494% -0.013% 4.481% -0.033% 4.449% -0.059% 4.390% -0.039% 4.351% 0.093% 4.444% -0.014% 4.430% -0.043% 4.387% 0.017% 4.404% -0.066% 4.338% 0.037% 4.375% 0.022% 4.397% 0.015% 4.412% 0.043% 4.455% 0.076% 4.531% 0.093% 4.624% 0.018% 4.643% 0.002% 4.645% -0.048% 4.596%

MU r 0.00001% SIGMA r 0.05%

Change in Interest Rates 0.150%

0.100%

0.050%

0.000%

0

2

4

6

8

10

12

14

16

18

20

22

24

26

28

30

-0.050%

-0.100%

-0.150%

Interest Rate Path 0.048 0.047 0.046 0.045 0.044 0.043 0.042 0.041

1 2 3 4 5 6 7 8 9 10111213141516171819202122232425262728293031

36 37 38 39 40

0.062% -0.029% 0.048% 0.028% -0.043%

4.659% 4.630% 4.678% 4.706% 4.662%

0

32

34

36

38

40

132333435363738394041

N 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

Sim 1 -0.0255% 0.0023% -0.0207% -0.0306% 0.0482% -0.0010% -0.0109% 0.0909% 0.0088% -0.0486% -0.0528% -0.0120% -0.0387% 0.0317% 0.0761% 0.0453% -0.0792% -0.0085% -0.0206% 0.0040% 0.0682% 0.0174% -0.0039% 0.0275% -0.0531% 0.0164% -0.0936% -0.0219% 0.0122% 0.0459% 0.0922% -0.0102% -0.0725% 0.0306% -0.0090% -0.0282%

Sim 2 -0.0192% -0.0122% 0.0756% 0.0404% -0.0388% 0.0314% 0.0456% -0.0451% 0.0013% -0.0577% 0.0143% -0.0378% 0.0024% 0.0139% -0.0340% -0.0012% 0.0256% 0.0508% 0.0526% -0.0438% -0.0234% 0.0911% -0.0406% -0.0833% 0.0307% -0.0105% -0.0072% 0.0064% 0.0881% -0.0148% 0.0806% -0.0033% -0.0028% -0.0240% 0.0810% 0.1002%

Sim 3 -0.0582% 0.0350% -0.0610% -0.0414% 0.0742% -0.0067% 0.0184% -0.0583% 0.0133% -0.0691% 0.0269% 0.0879% -0.0029% -0.0285% 0.0079% 0.0539% 0.0035% 0.0416% 0.0062% 0.0049% -0.0919% 0.0289% 0.0130% -0.0780% 0.0093% 0.0121% -0.0394% -0.0615% 0.0636% 0.0169% 0.0642% -0.0232% 0.0107% 0.0251% -0.0218% 0.1156%

Sim 4 -0.0160% 0.0106% 0.0118% 0.0313% 0.0940% 0.0450% -0.0818% 0.0626% -0.0017% -0.0231% 0.0491% -0.0104% -0.0483% -0.0160% -0.0186% -0.0647% -0.0300% 0.0491% -0.0186% -0.0533% 0.0410% 0.0004% 0.0279% -0.0615% -0.0604% -0.0585% -0.0049% 0.0349% -0.0595% 0.0366% -0.0654% -0.0191% -0.1275% -0.0186% -0.0311% 0.0374%

Sim 5 -0.0416% 0.0095% -0.0406% 0.0156% -0.0344% 0.0567% 0.0203% -0.0836% -0.0387% -0.0064% 0.0914% -0.0976% -0.0053% 0.0774% 0.0560% 0.0914% -0.0068% -0.0323% 0.0118% -0.0307% 0.0455% 0.0286% -0.0277% 0.0754% -0.0330% -0.0489% -0.0025% -0.0541% 0.0308% 0.0345% 0.0248% 0.0048% 0.0866% -0.0702% -0.0911% -0.0873%

Sim 6 0.0168% 0.0687% 0.0849% 0.0578% 0.0619% -0.0469% 0.1135% 0.0069% -0.0209% 0.0957% 0.0532% 0.0083% -0.0281% -0.0273% 0.0246% -0.0295% -0.0105% -0.0181% -0.0331% -0.0106% -0.0328% 0.0724% 0.1113% 0.0255% -0.0193% 0.0942% 0.0571% 0.0145% 0.0404% 0.0720% -0.0411% -0.0093% -0.0723% -0.0375% -0.0920% 0.0481%

37 38 39 40

0.0390% -0.0074% 0.0614% 0.0577%

0.0577% -0.0797% -0.0137% 0.0000%

0.0172% 0.0174% 0.0456% -0.0093%

-0.0342% -0.0010% 0.0064% -0.0039%

0.0209% 0.0127% 0.0123% -0.0291%

0.0034% -0.0085% 0.0978% -0.0279%

Sim 7 -0.0035% -0.0678% -0.0621% 0.0446% 0.0183% 0.0580% -0.0729% -0.0838% 0.0201% -0.0154% 0.0604% 0.0084% -0.0536% -0.0342% 0.0344% 0.0135% 0.0379% 0.0166% -0.0842% 0.0920% 0.0232% 0.0566% 0.0132% -0.0561% 0.0182% -0.0017% 0.1328% 0.0050% -0.0074% -0.0638% -0.0098% -0.0186% -0.0627% 0.0646% 0.0175% -0.0496%

Sim 8 -0.0166% 0.0509% -0.0720% 0.0048% -0.0680% -0.0172% 0.0488% 0.0138% 0.0954% 0.0357% 0.0483% -0.0233% -0.1176% -0.0017% -0.0398% 0.0963% 0.0118% -0.0203% 0.0871% -0.0421% -0.0152% 0.0591% -0.0211% -0.0480% 0.1107% 0.0538% 0.0376% -0.0541% -0.0108% -0.0513% 0.0288% 0.0157% 0.0891% -0.1117% 0.0355% -0.0242%

Sim 9 -0.0172% -0.0050% 0.0306% -0.0882% -0.0438% 0.0310% 0.0988% -0.0347% -0.1433% 0.0090% -0.0052% 0.0769% -0.0355% 0.0214% -0.0528% -0.0560% -0.0357% 0.0309% -0.0209% -0.0512% -0.0902% 0.0337% 0.0344% 0.0658% -0.0235% 0.0057% -0.0559% 0.0433% -0.0368% -0.0055% -0.0256% 0.0085% 0.0811% -0.0309% 0.0239% -0.0413%

Sim 10 0.0941% 0.1186% -0.0133% 0.0015% -0.0034% -0.0365% -0.0788% -0.0492% 0.0403% 0.0863% -0.0101% 0.0038% -0.0525% 0.0245% -0.0174% -0.0884% -0.0253% 0.1010% -0.0237% 0.0518% 0.0881% 0.0245% 0.0116% -0.1028% -0.0213% -0.0475% -0.0446% 0.0069% 0.1608% 0.0228% 0.0061% 0.0122% -0.0222% -0.0500% 0.0039% 0.0710%

Mean Stdev Max Min

Bins -0.150% -0.135% -0.120% -0.105% -0.090% -0.075% -0.060% -0.045% -0.030% -0.015% 0.000% 0.015% 0.030% 0.045% 0.060% 0.075% 0.090% 0.105% 0.120% 0.135% 0.150% 0.165%

0.002862% 0.051300% 0.160829% -0.143276%

Bin Frequency -0.150% 0 -0.135% 1 -0.120% 1 -0.105% 2 -0.090% 7 -0.075% 13 -0.060% 17 -0.045% 29 -0.030% 38 -0.015% 46 0.000% 44 0.015% 51 0.030% 34 0.045% 30 0.060% 29 0.075% 16 0.090% 16 0.105% 18 0.120% 6 0.135% 1 0.150% 0 0.165% 1 More 0

0.0917% 0.0162% -0.0264% -0.0591%

0.0630% 0.0007% -0.0360% 0.0628%

0.0000% 0.1018% -0.0425% 0.0416%

-0.0434% -0.0898% 0.1116% 0.0544%

Histogram 60

50

umulative % 0.00% 0.00% 0.25% 0.25% 0.50% 0.50% 1.00% 1.00% 2.75% 2.75% 6.00% 6.00% 10.25% 10.25% 17.50% 17.50% 27.00% 27.00% 38.50% 38.50% 49.50% 49.50% 62.25% 62.25% 70.75% 70.75% 78.25% 78.25% 85.50% 85.50% 89.50% 89.50% 93.50% 93.50% 98.00% 98.00% 99.50% 99.50% 99.75% 99.75% 99.75% 99.75% 100.00% 100.00% 100.00%

Frequency

40

30

20

10

0 e 0 % 2 0 % 9 0 % 6 0 % 3 0 % 0 0 % 3 0 % 6 0 % 9 0 % 2 0 % 5 0 % or M 0 0 0 1 0 1 0 15 0 0 1 . . . . . . . . . . . 0 0 0 0 0 0 -0 -0 -0 -0 -0

Bin

120.00%

100.00%

80.00%

60.00%

40.00%

20.00%

0.00%

Frequency Cumulative %

Question #6 Security A Coupon Term Price Par YTM Duration

5% 20 103.88% $10,000,000 4.699% 13.0119

MU r SIGMA r

0.00001% 0.0025%

MU p SIGMA p

($13.52) $3,379.03

Prob (Ret < 0%) Prob (Ret > 0.025%)

95% VaR 80% VaR

-$5,571.53 -$2,857.38

50.16%...