Economics Network CHEER Virtual Edition

Volume 13, Issue 2, 1999

The generation of stock-price/yield data sets for the simulation of the two-asset portfolio model and the CAPM using spreadsheets (continued)

Appendix

The spreadsheet details are shown in the following tables—representative data is shown in Table 1 and the cell functions are displayed in Table 2.(15) Only the portion of the spreadsheet relating to stock-A is shown—a similar set of adjacent cells on the same spreadsheet is used for stock-B.

Table 1. The Input (parameter) section of the spreadsheet is shown at the top, and the first nine rows of the Output section are shown in the bottom half of the Table.

A

B

C

D

E

F

G

H

I

1

Phase-Sensitive Stock-Price Simulator

2

12 Mar 99

3

Stock A

Phase 1

0 degrees

4

Input:

2

45 degrees

5

Periods/cycle (N)

52

3

90 degrees

6

Phase (choose, 1 to 8) (f)

5

4

135 degrees

7

Share price, initial (p0)

$15.00

5

180 degrees

8

Growth rate per period % (g)

0.50%

6

225 degrees

9

Saw tooth multiplier % (s)

120%

7

270 degrees

10

Randomness % (r)

50%

8

315 degrees

11

12

Output:

13

Standard deviation-A

$6.29

14

Correlation coefficient (rA,B)

-0.552

15

Final

16

Period

Offset

ModNorm

Sawtooth

RandNo

Price

17

tn

Fn

Mn

Stn

Rn

pgn

psn

prn

A

18

0

26

0.5000

0.0000

0.0000

$15.00

$0.00

$0.00

$15.00

19

1

27

0.5192

-0.0385

0.1499

$15.08

($0.69)

$1.13

$15.52

20

2

28

0.5385

-0.0769

0.4607

$15.15

($1.38)

$3.49

$17.26

21

3

29

0.5577

-0.1154

0.4307

$15.23

($2.08)

$3.28

$16.43

Table 2. Cell functions in the spreadsheet.

A

B

C

Period

Offset

ModNorm

tn

Fn

Mn

0

=A18+CHOOSE($E$6,0,$E$5/8,$E$5/4,3*$E$5/8,$E$5/2,5*$E$5/8,3*$E$5/4,7*$E$5/8)

=MOD(B18,$E$5)/$E$5

1

=A19+CHOOSE($E$6,0,$E$5/8,$E$5/4,3*$E$5/8,$E$5/2,5*$E$5/8,3*$E$5/4,7*$E$5/8)

=MOD(B19,$E$5)/$E$5

2

=A20+CHOOSE($E$6,0,$E$5/8,$E$5/4,3*$E$5/8,$E$5/2,5*$E$5/8,3*$E$5/4,7*$E$5/8)

=MOD(B20,$E$5)/$E$5

D

E

Sawtooth

RandNo

Stn

Rn

=2*IF(C18<=0.25,C18,(IF(C18<=0.5,0.5-C18,(IF(C18<=0.75,0.5-C18,(IF(C18<=1,-1+C18)))))))

0

=2*IF(C19<=0.25,C19,(IF(C19<=0.5,0.5-C19,(IF(C19<=0.75,0.5-C19,(IF(C19<=1,-1+C19)))))))

=RAND()-0.5

=2*IF(C20<=0.25,C20,(IF(C20<=0.5,0.5-C20,(IF(C20<=0.75,0.5-C20,(IF(C20<=1,-1+C20)))))))

=RAND()-0.5

F

G

H

I

Price growth

Price-sawtooth

Price-random

Final Price

pgn

psn

prn

A

=$E$7

=$E$9*D18*$E$7

=$E$10*E18*F18

=F18+G18+H18

=F18*(1+$E$8)

=$E$9*D19*$E$7

=$E$10*E19*F19

=F19+G19+H19

=F19*(1+$E$8)

=$E$9*D20*$E$7

=$E$10*E20*F20

=F20+G20+H20



(15) Tables 1 and 2 have been edited within the application Word, so that a mix of italics and Greek symbols can be shown. This ensures the data in the Tables correspond more closely to the parameters and functions described in the text.



Top | CHEER Home

Copyright 1989-2007