+1(978)310-4246 credencewriters@gmail.com
  

If applicable, students are expected to develop “user-friendly” and entirely original spreadsheet files of professional quality capable of performing one of the tasks listed below (or a satisfactory alternative as discussed with the instructor). The selected file should be original and not have been used before in any other class, and not a file adapted from one on the Internet or from another project. The spreadsheet files should include appropriate documentation, VBA code and/or macros as is necessary to run the file in a very user-friendly setting. Students should discuss their projects with the course instructor, particularly students not selecting one of the sample projects listed below. Students are encouraged to assist each other, to test each others’ programs and to “work out bugs” in each others’ programs. The spreadsheet files should be usable in Excel from MS Office 2007 or Office 2019. I emphasize that the package should be user-friendly with detailed on-screen instructions, macros and/or visual basic routines as necessary to ensure this. The project should include adequate documentation for its use by anyone familiar with the analytical technique and the financial problem. It should be assumed that the user has no familiarity working with the group’s spreadsheet and will not remember any verbal instructions given by students.

Fixed Income Portfolio Immunization

This is a more quantitatively-oriented project, enabling the student to create a practical and usable automated spreadsheet application package. The user of this Excel spreadsheet package should be able to input relevant price and contractual information from coupon or pure discount bonds to hedge against a fixed cash flow stream. The file should be able to handle data for up to thirty bonds making interest payments on either an annual or semiannual basis. Portfolios of bonds will have their durations and convexities matched against durations and convexities of a series of cash outflows associated with institutional liabilities. Allowing for matrix inversion, if it is necessary for your configuration, with potentially many sizes of matrices may require a little creativity (VBA or the Excel Offset function might be helpful here). Students are expected to develop “user-friendly” and entirely original spreadsheet files of professional quality capable of accomplishing this task.

Bond Valuation Example
We wish to value a 30-year 10% coupon bond with a face value equal to $1000. Assume a discount rate equal to 6%.
This worksheet can also be used to value other bonds with n between 1 and 53 by changing values in the yellow table.
Bond Details
t PV[CF(t)]
F
1000
c
0.1
1 94.33962 94.33962
k
0.06
2 88.99964 183.3393
n
30
3 83.96193 267.3012
4 79.20937 346.5106
PV Bond 1550.593
5 74.72582 421.2364
PV Bond 1550.593
6 70.49605 491.7324
7 66.50571 558.2381
B9 is the sum of
8 62.74124 620.9794
discounted cash
9 59.18985 680.1692
flows on from the
10 55.83948 736.0087
bond.
11 52.67875 788.6875
B10 is based on
12 49.69694 838.3844
the PV Annuity
13 46.8839 885.2683
as applied to bond
14 44.2301 929.4984
valuation.
15 41.72651 971.2249
16 39.36463 1010.59
17 37.13644 1047.726
18 35.03438 1082.76
19 33.0513 1115.812
20 31.18047 1146.992
21 29.41554 1176.408
22 27.75051 1204.158
23 26.17973 1230.338
24 24.69785 1255.036
25 23.29986 1278.336
26
21.981 1300.317
27 20.7368 1321.053
28 19.56301 1340.616
29 18.45567 1359.072
30 191.5211 1550.593
31
0 1550.593
32
0 1550.593
33
0 1550.593
34
0 1550.593
35
0 1550.593
36
0 1550.593
37
0 1550.593
38
0 1550.593
39
0 1550.593
40
0 1550.593
41
0 1550.593
42
0 1550.593
43
0 1550.593
44
0 1550.593
45
0 1550.593
46
0 1550.593
47
48
49
50
51
52
53
0
0
0
0
0
0
0
1550.593
1550.593
1550.593
1550.593
1550.593
1550.593
1550.593
Yield to Maturity Example
Enter Bond data into Column B of the Yellow Region. Revise your y estimate until you are sufficiently close to the bond’s correc
Bond Details
F
1000
c
0.1
Guess for y
0.15
n
30
Initial Bond Price
1000
PV Bond
671.701
DECREASE YOUR y ESTIMATE
Note: This calculator assumes that coupon payments are made annually beginning in one year.
Fixed Income Arbitrage
Bond A
P0=1000
F=1000
c=.04
n=2
t=1
t=2
t=3
Bond B
P0=1055.5
F=1000
c=.06
n=3
Bond C
P0=889
F=1000
c=0
n=3
Use Bonds A, B
and C to replicate
Bond D:
Bond D
P0 = 1360
c = .20
F = 1000
n=3
P0(D) should be:
1444
Bond A
Bond B
Bond C
From Column F
40
60
0
0
1040
60
0
3.333333333
0
1060
1000
-2.333333333
^Original Cash Flow Matrix^
^Weights^
-0.001
0.001
0
200
0
0.0173333 -0.0006667
0
200
3.333333333
-0.018373 0.00070667
0.001
1200
-2.333333333
^Inverse of Cash Flow Matrix^
^Bond D CFs^ ^Weights^
-1000
-1055.5
-889
-1360
0
40
60
0
200
-3.333333333
1040
60
0
200
2.333333333
0
1060
1000
1200
1
^Cash Flow of each bond^
^Arb.Port.Weights^
200
200
1200
Solve for Bond D CFs
84 CF Time 0
2.842E-14 CF Time 1
2.842E-14 CF Time 2
0 CF Time 3
Fixed Income Portfolio Dedication
Bond A
P0=1000
F=1000
c=.04
n=2
Bond B
P0=1055.5
F=1000
c=.06
n=3
Bond C
P0=889
F=1000
c=0
n=3
Fixed Income Arbitrage
40
60
0
1040
60
0
0
1060
1000
^Fixed Income Portfolio Cash Flows^
40
60
0
2000
12000000
Purchase answer to see full
attachment

  
error: Content is protected !!