fundamentals of finance - course project problem 1cc.oulu.fi/~jope/fof/cp/p1.pdfcourse project {...

Course project – Problem 1

Problem 1

1

2

3

60

61

62

63

64

65

66

67

I J K L M N O P Q R

1. Calculate the arithmetic returns of the five stocks.

J2: = B2/B1−1

0.2000 0.2653 0.4459 0.1593 0.3714

0.0076 0.0323− 0.0227 0.0666 0.1917

0.0337− 0.0416− 0.0801 0.0294− 0.1194

0.3152− 0.1469− 0.1851− 0.1345− 0.0733−

Jukka Perttunen Fundamentals of finance


Problem 1

1

2

3

60

61

62

63

64

65

66

67

I J K L M N O P Q R


J2: = B2/B1−1

0.2000

0.2653 0.4459 0.1593 0.3714

0.0076 0.0323− 0.0227 0.0666 0.1917

0.0337− 0.0416− 0.0801 0.0294− 0.1194

0.3152− 0.1469− 0.1851− 0.1345− 0.0733−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

I J K L M N O P Q R


J2: = B2/B1−1

0.2000 0.2653 0.4459 0.1593 0.3714

0.0076 0.0323− 0.0227 0.0666 0.1917

0.0337− 0.0416− 0.0801 0.0294− 0.1194

0.3152− 0.1469− 0.1851− 0.1345− 0.0733−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

2. Calculate the logarithmic returns of the five stocks.

P2: = LN(B2)−LN(B1)

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X


P2: = LN(B2)−LN(B1)

0.1823

0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X


P2: = LN(B2)−LN(B1)

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

U V W X Y Z AA AB AC AD

3. Calculate the logarithmic returns of the market portfolio.

V2: = LN(G2)−LN(G1)

0.0960

0.0280

0.0026

0.2035−



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.0960

0.0280

0.0026

0.2035−

4. Calculate the logarithmic returns of the risk-free portfolio.

X2: = LN(H2)−LN(H1)

0.0017

0.0017

0.0037

0.0038



Problem 1

1

2

3

60

61

62

63

64

65

66

67

Y Z AA AB AC AD AE AF AG AH

5. Calculate the initial number of shares in the portfolio.

Z1: = 200000/B1

Here we assume that e200’000 of e1 million is allocated to each of the five stocks in the portfolio.

9091 136054 13115 12346 114286



Problem 1

1

2

3

60

61

62

63

64

65

66

67



Z1: = 200000/B1


9091

136054 13115 12346 114286



Problem 1

1

2

3

60

61

62

63

64

65

66

67



Z1: = 200000/B1


9091 136054 13115 12346 114286



Problem 1

1

2

3

60

61

62

63

64

65

66

67


9091 136054 13115 12346 114286

6. The number of shares is kept constant over the rest of the holding period.

Z2: = Z1

9091 136054 13115 12346 114286

9091 136054 13115 12346 114286

9091 136054 13115 12346 114286

9091 136054 13115 12346 114286



Problem 1

1

2

3

60

61

62

63

64

65

66

67


9091 136054 13115 12346 114286


Z2: = Z1

9091

136054 13115 12346 114286

9091 136054 13115 12346 114286

9091 136054 13115 12346 114286

9091 136054 13115 12346 114286



Problem 1

1

2

3

60

61

62

63

64

65

66

67


9091 136054 13115 12346 114286


Z2: = Z1

9091 136054 13115 12346 114286

9091 136054 13115 12346 114286

9091 136054 13115 12346 114286

9091 136054 13115 12346 114286



Problem 1

1

2

3

60

61

62

63

64

65

66

67

AE AF AG AH AI AJ AK AL AM AN

7. Calculate the values of the stock positions in the value-weighted portfolio.

AF1: = Z1*B1

200000 200000 200000 200000 200000

240000 253061 289180 231852 274286

241818 244898 295738 247284 326857

415091 972789 371279 277284 171429

284273 829932 302557 240000 158857



Problem 1

1

2

3

60

61

62

63

64

65

66

67



AF1: = Z1*B1

200000

200000 200000 200000 200000

240000 253061 289180 231852 274286

241818 244898 295738 247284 326857

415091 972789 371279 277284 171429

284273 829932 302557 240000 158857



Problem 1

1

2

3

60

61

62

63

64

65

66

67



AF1: = Z1*B1

200000 200000 200000 200000 200000

240000 253061 289180 231852 274286

241818 244898 295738 247284 326857

415091 972789 371279 277284 171429

284273 829932 302557 240000 158857



Problem 1

1

2

3

60

61

62

63

64

65

66

67


200000 200000 200000 200000 200000

240000 253061 289180 231852 274286

241818 244898 295738 247284 326857

415091 972789 371279 277284 171429

284273 829932 302557 240000 158857

8. Calculate the value of the value-weighted portfolio.

AL1: = AF1+AG1+AH1+AI1+AJ1

1000000

1288379

1356595

2207871

1815619



Problem 1

1

2

3

60

61

62

63

64

65

66

67

AM AN AO AP AQ AR AS AT AU AV

9. Calculate the weights of the stocks in the value-weighted portfolio.

AN2: = AF1/$AL1

0.200 0.200 0.200 0.200 0.200

0.186 0.196 0.224 0.180 0.213

0.193 0.456 0.154 0.128 0.069

0.188 0.441 0.168 0.126 0.078



Problem 1

1

2

3

60

61

62

63

64

65

66

67



AN2: = AF1/$AL1

0.200

0.200 0.200 0.200 0.200

0.186 0.196 0.224 0.180 0.213

0.193 0.456 0.154 0.128 0.069

0.188 0.441 0.168 0.126 0.078



Problem 1

1

2

3

60

61

62

63

64

65

66

67



AN2: = AF1/$AL1

0.200 0.200 0.200 0.200 0.200

0.186 0.196 0.224 0.180 0.213

0.193 0.456 0.154 0.128 0.069

0.188 0.441 0.168 0.126 0.078



Problem 1

1

2

3

60

61

62

63

64

65

66

67

AS AT AU AV AW AX AY AZ BA BB

10. Calculate the arithmetic returns of the value-weighted portfolio.

AT2: = AN2*J2+AO2*K2+AP2*L2+AQ2*M2+AR2*N2

0.2884

0.0529

0.0086−0.1777−



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.2884

0.0529

0.0086−0.1777−

11. Calculate the logarithmic returns of the value-weighted portfolio.

AU2: = LN(1+AT2)

0.2534

0.0516

0.0087−0.1956−



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.2884

0.0529

0.0086−0.1777−

0.2534

0.0516

0.0087−0.1956−

12. Calculate the arithmetic returns of the equally-weighted portfolio.

AW2: = SUM(J2:N2)/5

0.2884

0.0512

0.0190

0.1710−



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.2884

0.0529

0.0086−0.1777−

0.2534

0.0516

0.0087−0.1956−

0.2884

0.0512

0.0190

0.1710−

13. Calculate the logarithmic returns of the equally-weighted portfolio.

AX2: = LN(1+AW2)

0.2534

0.0500

0.0188

0.1875−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

AY AZ BA BB BC BD BE BF BG BH

14. Calculate the excess returns of the five stocks.

AZ2: = P2−$X2

0.1806 0.2336 0.3670 0.1460 0.3141

0.0058 0.0345− 0.0207 0.0627 0.1736

0.0380− 0.0462− 0.0733 0.0336− 0.1091

0.3823− 0.1626− 0.2084− 0.1482− 0.0799−



Problem 1

1

2

3

60

61

62

63

64

65

66

67



AZ2: = P2−$X2

0.1806

0.2336 0.3670 0.1460 0.3141

0.0058 0.0345− 0.0207 0.0627 0.1736

0.0380− 0.0462− 0.0733 0.0336− 0.1091

0.3823− 0.1626− 0.2084− 0.1482− 0.0799−



Problem 1

1

2

3

60

61

62

63

64

65

66

67



AZ2: = P2−$X2

0.1806 0.2336 0.3670 0.1460 0.3141

0.0058 0.0345− 0.0207 0.0627 0.1736

0.0380− 0.0462− 0.0733 0.0336− 0.1091

0.3823− 0.1626− 0.2084− 0.1482− 0.0799−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

BE BF BG BH BI BJ BK BL BM BN

15. Calculate the excess returns of the market portfolio.

BF2: = V2−$X2

0.0943

0.0263

0.0011−0.2073−



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.0943

0.0263

0.0011−0.2073−

16. Calculate the excess returns of the value-weighted portfolio.

BH2: = AU2−$X2

0.2516

0.0498

0.0124−0.1994−



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.0943

0.0263

0.0011−0.2073−

0.2516

0.0498

0.0124−0.1994−

17. Calculate the excess returns of the equally-weighted portfolio.

BJ2: = AX2−$X2

0.2516

0.0482

0.0151

0.1913−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−

18. Calculate the cumulative logarithmic returns of the five stocks.

P63: = 100*SUM(P2:P61)

35.16 142.30 41.40 18.23 23.03−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−


P63: = 100*SUM(P2:P61)

35.16

142.30 41.40 18.23 23.03−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−


P63: = 100*SUM(P2:P61)

35.16 142.30 41.40 18.23 23.03−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−

35.16 142.30 41.40 18.23 23.03−

19. Calculate the average annual arithmetic returns of the five stocks.

P64: = 100*(EXP(P63/100/5)−1)

P64: = 100*(EXP(12*AVERAGE(P2:P61))−1)

7.29 32.92 8.63 3.71 4.50−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−

35.16 142.30 41.40 18.23 23.03−


P64: = 100*(EXP(P63/100/5)−1)

P64: = 100*(EXP(12*AVERAGE(P2:P61))−1)

7.29

32.92 8.63 3.71 4.50−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−

35.16 142.30 41.40 18.23 23.03−


P64: = 100*(EXP(P63/100/5)−1)

P64: = 100*(EXP(12*AVERAGE(P2:P61))−1)

7.29 32.92 8.63 3.71 4.50−



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−

35.16 142.30 41.40 18.23 23.03−7.29 32.92 8.63 3.71 4.50−

20. Calculate the volatilities of the five stocks.

P65: = 100*SQRT(12)*STDEV.S(P2:P61)

38.70 41.84 34.07 25.30 37.51



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−

35.16 142.30 41.40 18.23 23.03−7.29 32.92 8.63 3.71 4.50−


P65: = 100*SQRT(12)*STDEV.S(P2:P61)

38.70

41.84 34.07 25.30 37.51



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−

35.16 142.30 41.40 18.23 23.03−7.29 32.92 8.63 3.71 4.50−


P65: = 100*SQRT(12)*STDEV.S(P2:P61)

38.70 41.84 34.07 25.30 37.51



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−

35.16 142.30 41.40 18.23 23.03−7.29 32.92 8.63 3.71 4.50−

38.70 41.84 34.07 25.30 37.51

21. Calculate the betas of the five stocks.

P66: = COVARIANCE.S(P2:P61;$V2:$V61)/VAR.S($V2:$V61)

1.10 1.38 0.88 0.55 0.56



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−

35.16 142.30 41.40 18.23 23.03−7.29 32.92 8.63 3.71 4.50−

38.70 41.84 34.07 25.30 37.51



1.10

1.38 0.88 0.55 0.56



Problem 1

1

2

3

60

61

62

63

64

65

66

67

O P Q R S T U V W X

0.1823 0.2353 0.3687 0.1478 0.3159

0.0075 0.0328− 0.0224 0.0644 0.1754

0.0342− 0.0424− 0.0771 0.0298− 0.1128

0.3786− 0.1588− 0.2047− 0.1444− 0.0762−

35.16 142.30 41.40 18.23 23.03−7.29 32.92 8.63 3.71 4.50−

38.70 41.84 34.07 25.30 37.51



1.10 1.38 0.88 0.55 0.56



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.0960

0.0280

0.0026

0.2035−

0.0017

0.0017

0.0037

0.0038

22. For the market portfolio . . . .

V63: = 100*SUM(V2:V61)

39.99

V64: = 100*(EXP(V63/100/5)−1)

8.33

V65: = 100*SQRT(12)*STDEV.S(V2:V61)

20.23

V66: = COVARIANCE.S(V2:V61;$V2:$V61)/VAR.S($V2:$V61)

1.00



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.0960

0.0280

0.0026

0.2035−

0.0017

0.0017

0.0037

0.0038

39.99

8.33

20.23

1.00

23. For the risk-free portfolio . . . .

X63: = 100*SUM(X2:X61)

14.96

X64: = 100*(EXP(X63/100/5)−1)

3.04

X65: = 100*SQRT(12)*STDEV.S(X2:X61)

0.28

X66: = COVARIANCE.S(X2:X61;$V2:$V61)/VAR.S($V2:$V61)

0.00



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.2884

0.0529

0.0086−0.1777−

0.2534

0.0516

0.0087−0.1956−

0.2884

0.0512

0.0190

0.1710−

0.2534

0.0500

0.0188

0.1875−

24. For the value-weighted portfolio . . .

AU63: = 100*SUM(AU2:AU61)

59.64

AU64: = 100*(EXP(AU63/100/5)−1)

12.67

AU65: = 100*SQRT(12)*STDEV.S(AU2:AU61)

26.53

AU66: = COVARIANCE.S(AU2:AU61;$V2:$V61)/VAR.S($V2:$V61)

0.97



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.2884

0.0529

0.0086−0.1777−

0.2534

0.0516

0.0087−0.1956−

0.2884

0.0512

0.0190

0.1710−

0.2534

0.0500

0.0188

0.1875−

59.64

12.67

26.53

0.97

25. For the equally-weighted portfolio . . .

AX63: = 100*SUM(AX2:AX61)

59.73

AX64: = 100*(EXP(AX63/100/5)−1)

12.69

AX65: = 100*SQRT(12)*STDEV.S(AX2:AX61)

24.72

AX66: = COVARIANCE.S(AX2:AX61;$V2:$V61)/VAR.S($V2:$V61)

0.90



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.1806 0.2336 0.3670 0.1460 0.3141

0.0058 0.0345− 0.0207 0.0627 0.1736

0.0380− 0.0462− 0.0733 0.0336− 0.1091

0.3823− 0.1626− 0.2084− 0.1482− 0.0799−

26. Calculate the Sharpe ratios of the five stocks.

AZ63: = 12*AVERAGE(AZ2:AZ61)/(SQRT(12)*STDEV.S(AZ2:AZ61))

0.10 0.61 0.15 0.03 0.20−



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.1806 0.2336 0.3670 0.1460 0.3141

0.0058 0.0345− 0.0207 0.0627 0.1736

0.0380− 0.0462− 0.0733 0.0336− 0.1091

0.3823− 0.1626− 0.2084− 0.1482− 0.0799−



0.10

0.61 0.15 0.03 0.20−



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.1806 0.2336 0.3670 0.1460 0.3141

0.0058 0.0345− 0.0207 0.0627 0.1736

0.0380− 0.0462− 0.0733 0.0336− 0.1091

0.3823− 0.1626− 0.2084− 0.1482− 0.0799−



0.10 0.61 0.15 0.03 0.20−



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.0943

0.0263

0.0011−0.2073−

0.2516

0.0498

0.0124−0.1994−

0.2516

0.0482

0.0151

0.1913−

27. For the market portfolio . . .

BF63: = 12*AVERAGE(BF2:BF61)/(SQRT(12)*STDEV.S(BF2:BF61))

0.25



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.0943

0.0263

0.0011−0.2073−

0.2516

0.0498

0.0124−0.1994−

0.2516

0.0482

0.0151

0.1913−

0.25

28. For the value-weighted portfolio . . .

BH63: = 12*AVERAGE(BH2:BH61)/(SQRT(12)*STDEV.S(BH2:BH61))

0.34



Problem 1

1

2

3

60

61

62

63

64

65

66

67


0.0943

0.0263

0.0011−0.2073−

0.2516

0.0498

0.0124−0.1994−

0.2516

0.0482

0.0151

0.1913−

0.25 0.34

29. For the equally-weighted portfolio . . .

BJ63: = 12*AVERAGE(BJ2:BJ61)/(SQRT(12)*STDEV.S(BJ2:BJ61))

0.36


fundamentals of finance - course project problem 1cc.oulu.fi/~jope/fof/cp/p1.pdfcourse project {...

Documents