Mô hình phân tích thị trường bằng công cụ Excel

Chia sẻ: Anhvu Anhvu | Ngày: | Loại File: XLS | Số trang:60

1
1.518
lượt xem
806
download

Mô hình phân tích thị trường bằng công cụ Excel

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Dùng các hàm excel sẵn có để phân tích danh mục đầu tư chứng khoán.

Chủ đề:
Lưu

Nội dung Text: Mô hình phân tích thị trường bằng công cụ Excel

  1. LẬP MÔ HÌNH TÀI CHÍNH Mã chứng khoán Đơn vị: 1.000 đồng Số quan sát( tháng) AGF BBC BTC CAN DPC GIL GMD HAP HAS 04/2004 26.49 11.51 17.40 11.92 10.23 18.27 42.16 10.97 12.96 05/2004 26.91 10.22 16.50 12.67 9.16 18.22 45.27 11.03 13.41 06/2004 27.04 10.09 15.70 12.07 9.07 19.02 41.60 11.27 13.23 07/2004 26.43 9.64 15.80 11.38 8.90 17.90 39.90 11.06 13.41 08/2004 24.42 9.32 15.50 10.46 8.72 15.42 39.05 10.36 11.91 09/2004 25.64 10.14 15.00 10.69 8.72 16.29 39.62 10.63 12.59 10/2004 25.76 10.86 15.50 11.00 8.54 16.51 39.34 11.00 13.14 11/2004 24.89 10.79 15.00 10.84 8.01 16.46 36.23 11.30 13.53 12/2004 24.83 10.86 15.00 11.00 8.01 16.79 37.58 11.85 13.77 01/2005 25.27 11.05 14.50 11.00 8.01 17.84 38.77 11.75 13.07 02/2005 24.52 11.32 15.00 10.84 7.92 18.86 39.05 11.75 13.22 03/2005 24.45 11.56 15.00 11.62 7.92 18.69 39.34 12.09 13.27 04/2005 25.54 11.28 13.80 11.35 8.18 18.23 40.47 12.14 13.58 05/2005 24.58 11.01 10.30 11.35 7.92 17.84 38.49 11.63 13.43 06/2005 23.75 11.08 9.20 11.66 8.01 17.55 38.49 11.78 13.12 07/2005 23.87 11.59 8.90 11.50 8.01 17.67 38.21 12.14 13.63 08/2005 23.48 11.88 8.50 11.74 8.63 17.38 37.24 12.09 13.53 09/2005 23.81 14.48 8.20 11.74 8.72 18.48 37.92 12.14 13.89 10/2005 31.23 17.38 8.20 14.41 8.81 23.91 39.90 12.78 15.32 11/2005 33.46 16.56 8.20 14.01 12.19 24.99 45.84 12.73 17.15 12/2005 31.56 16.11 8.30 13.93 11.30 21.39 48.38 12.45 16.85 01/2006 30.21 16.19 8.10 13.68 10.85 21.98 48.95 12.94 17.51 02/2006 32.28 17.40 8.20 17.00 12.19 24.54 52.06 14.75 20.14 03/2006 42.28 24.05 12.70 20.24 14.43 26.64 66.48 19.12 25.35 04/2006 50.90 33.57 18.50 31.92 25.25 37.16 75.81 36.40 34.55 05/2006 56.42 35.11 25.80 29.77 26.72 36.83 80.05 32.23 36.94 06/2006 54.35 28.85 17.90 25.70 18.52 31.44 73.26 30.56 33.67 07/2006 52.63 27.53 16.20 23.30 17.16 27.10 69.18 24.79 29.28 08/2006 55.04 27.22 15.30 20.73 15.99 28.98 67.45 22.41 28.58 09/2006 62.08 32.77 16.00 26.39 19.99 33.87 79.96 28.34 36.32 10/2006 64.56 32.14 15.50 29.79 20.18 36.89 86.86 27.65 37.96 11/2006 75.00 31.70 14.00 28.44 18.14 36.22 110.59 26.13 39.08 12/2006 107.50 38.13 11.30 25.26 21.16 35.89 154.00 24.68 42.05 01/2007 108.33 38.75 11.20 24.68 33.74 39.57 189.00 28.76 49.50 02/2007 117.50 47.77 20.20 29.40 49.00 53.66 183.00 49.93 61.41 03/2007 111.67 58.04 30.30 43.87 53.50 59.03 171.00 70.32 77.23 04/2007 95.00 46.43 22.00 27.68 35.60 43.26 158.00 49.93 56.24 05/2007 101.67 56.00 22.00 28.46 34.00 50.64 168.00 55.91 65.99 06/2007 107.00 54.50 26.40 28.46 37.00 53.66 156.00 60.83 78.73 07/2007 101.00 60.00 29.00 27.78 32.80 49.50 140.00 55.20 71.61 08/2007 97.00 73.00 28.80 26.01 29.00 46.20 127.00 57.67 65.99 09/2007 96.00 88.00 29.80 27.70 31.50 49.30 128.00 63.29 80.00 10/2007 103.00 109.00 34.10 31.90 35.90 59.00 145.00 88.00 92.50 11/2007 91.00 103.00 54.00 27.20 38.50 50.00 139.00 88.00 85.00 TÍNH TOÁN TỶ SUẤT SINH LỢI AGF BBC BTC CAN DPC GIL GMD HAP HAS 05/2004 1.61% -11.17% -5.17% 6.37% -10.43% -0.29% 7.38% 0.56% 3.52% 06/2004 0.45% -1.26% -4.85% -4.79% -0.97% 4.43% -8.12% 2.21% -1.36% 07/2004 -2.25% -4.46% 0.64% -5.66% -1.96% -5.93% -4.08% -1.89% 1.38%
  2. 08/2004 -7.60% -3.33% -1.90% -8.14% -2.00% -13.81% -2.13% -6.34% -11.22% 09/2004 5.00% 8.77% -3.23% 2.22% 0.00% 5.63% 1.45% 2.65% 5.75% 10/2004 0.49% 7.10% 3.33% 2.90% -2.04% 1.35% -0.71% 3.44% 4.35% 11/2004 -3.39% -0.60% -3.23% -1.41% -6.25% -0.33% -7.91% 2.77% 2.96% 12/2004 -0.25% 0.61% 0.00% 1.43% 0.00% 2.00% 3.75% 4.80% 1.79% 01/2005 1.76% 1.81% -3.33% 0.00% 0.00% 6.26% 3.16% -0.83% -5.08% 02/2005 -2.96% 2.37% 3.45% -1.41% -1.11% 5.71% 0.73% 0.00% 1.16% 03/2005 -0.26% 2.19% 0.00% 7.14% 0.00% -0.90% 0.72% 2.92% 0.39% 04/2005 4.45% -2.41% -8.00% -2.36% 3.37% -2.42% 2.87% 0.43% 2.31% 05/2005 -3.76% -2.47% -25.36% 0.00% -3.26% -2.17% -4.89% -4.24% -1.13% 06/2005 -3.39% 0.63% -10.68% 2.76% 1.12% -1.59% 0.00% 1.33% -2.28% 07/2005 0.52% 4.64% -3.26% -1.34% 0.00% 0.64% -0.73% 3.06% 3.89% 08/2005 -1.65% 2.53% -4.49% 2.06% 7.78% -1.62% -2.52% -0.45% -0.75% 09/2005 1.40% 21.88% -3.53% 0.00% 1.03% 6.31% 1.82% 0.44% 2.64% 10/2005 31.18% 20.00% 0.00% 22.76% 1.02% 29.38% 5.22% 5.26% 10.29% 11/2005 7.15% -4.70% 0.00% -2.81% 38.38% 4.54% 14.88% -0.42% 12.00% 12/2005 -5.69% -2.69% 1.22% -0.58% -7.30% -14.40% 5.55% -2.15% -1.79% 01/2006 -4.27% 0.46% -2.41% -1.74% -3.94% 2.75% 1.17% 3.95% 3.94% 02/2006 6.85% 7.49% 1.23% 24.26% 12.30% 11.61% 6.35% 13.92% 15.04% 03/2006 30.98% 38.22% 54.88% 19.05% 18.40% 8.58% 27.70% 29.63% 25.86% 04/2006 20.39% 39.55% 45.67% 57.72% 75.00% 39.51% 14.03% 90.39% 36.27% 05/2006 10.84% 4.61% 39.46% -6.75% 5.79% -0.88% 5.59% -11.45% 6.92% 06/2006 -3.67% -17.84% -30.62% -13.65% -30.66% -14.64% -8.48% -5.17% -8.85% 07/2006 -3.17% -4.56% -9.50% -9.35% -7.37% -13.81% -5.57% -18.89% -13.04% 08/2006 4.59% -1.12% -5.56% -11.03% -6.82% 6.93% -2.49% -9.59% -2.37% 09/2006 12.78% 20.36% 4.58% 27.30% 25.00% 16.90% 18.54% 26.44% 27.08% 10/2006 4.00% -1.91% -3.13% 12.90% 0.98% 8.91% 8.63% -2.44% 4.51% 11/2006 16.17% -1.39% -9.68% -4.56% -10.14% -1.82% 27.31% -5.50% 2.94% 12/2006 43.33% 20.28% -19.29% -11.19% 16.67% -0.93% 39.26% -5.56% 7.62% 01/2007 0.78% 1.64% -0.88% -2.29% 59.45% 10.28% 22.73% 16.56% 17.70% 02/2007 8.46% 23.27% 80.36% 19.14% 45.25% 35.59% -3.17% 73.59% 24.06% 03/2007 -4.96% 21.50% 50.00% 49.22% 9.18% 10.00% -6.56% 40.85% 25.78% 04/2007 -14.93% -20.00% -27.39% -36.91% -33.46% -26.70% -7.60% -29.00% -27.18% 05/2007 7.02% 20.62% 0.00% 2.84% -4.49% 17.05% 6.33% 11.97% 17.33% 06/2007 5.25% -2.68% 20.00% 0.00% 8.82% 5.96% -7.14% 8.80% 19.32% 07/2007 -5.61% 10.09% 9.85% -2.41% -11.35% -7.75% -10.26% -9.25% -9.05% 08/2007 -3.96% 21.67% -0.69% -6.36% -11.59% -6.67% -9.29% 4.46% -7.85% 09/2007 -1.03% 20.55% 3.47% 6.50% 8.62% 6.71% 0.79% 9.76% 21.24% 10/2007 7.29% 23.86% 14.43% 15.16% 13.97% 19.68% 13.28% 39.04% 15.63% 11/2007 -11.65% -5.50% 58.36% -14.73% 7.24% -15.25% -4.14% 0.00% -8.11% GIÁ TRỊ TB 3.45% 6.01% 4.76% 3.08% 4.75% 3.13% 3.34% 6.65% 5.20% PHƯƠNG SAI 1.22% 1.72% 5.13% 2.52% 3.83% 1.62% 1.17% 4.43% 1.53% ĐỘ LỆCH CHUẨN 11.06% 13.12% 22.64% 15.89% 19.58% 12.72% 10.82% 21.04% 12.37% BETA 0.8729 0.8392 0.6202 0.7243 1.2319 0.7615 0.8202 1.1139 0.7821 MA TRẬN PHƯƠNG SAI - HIỆP PHƯƠNG SAI SỬ DỤNG HÀM OFFSET AGF BBC BTC CAN DPC GIL GMD HAP 0 1 2 3 4 5 6 7 AGF 0 0.0122 0.0085 0.0045 0.0066 0.0092 0.0078 0.0091 0.0077 BBC 1 0.0085 0.0172 0.0159 0.0144 0.0140 0.0118 0.0054 0.0196
  3. BTC 2 0.0045 0.0159 0.0513 0.0188 0.0242 0.0135 0.0012 0.0328 CAN 3 0.0066 0.0144 0.0188 0.0252 0.0186 0.0157 0.0039 0.0274 DPC 4 0.0092 0.0140 0.0242 0.0186 0.0383 0.0173 0.0102 0.0312 GIL 5 0.0078 0.0118 0.0135 0.0157 0.0173 0.0162 0.0044 0.0215 GMD 6 0.0091 0.0054 0.0012 0.0039 0.0102 0.0044 0.0117 0.0050 HAP 7 0.0077 0.0196 0.0328 0.0274 0.0312 0.0215 0.0050 0.0443 HAS 8 0.0076 0.0117 0.0157 0.0158 0.0183 0.0130 0.0063 0.0209 KHA 9 0.0076 0.0156 0.0236 0.0226 0.0222 0.0177 0.0041 0.0313 LAF 10 0.0026 0.0108 0.0238 0.0143 0.0143 0.0110 -0.0004 0.0231 PMS 11 0.0076 0.0176 0.0231 0.0296 0.0304 0.0186 0.0065 0.0360 REE 12 0.0118 0.0122 0.0117 0.0111 0.0192 0.0121 0.0106 0.0190 SAM 13 0.0151 0.0105 0.0014 0.0058 0.0127 0.0074 0.0143 0.0085 SGH 14 0.0101 0.0160 0.0185 0.0227 0.0353 0.0215 0.0079 0.0381 TS4 15 0.0057 0.0093 0.0159 0.0110 0.0144 0.0094 0.0048 0.0147 VTC 16 0.0069 0.0137 0.0283 0.0183 0.0215 0.0169 0.0027 0.0325
  4. KHA LAF PMS REE SAM SGH TS4 VTC VN-INDEX 10.44 9.82 11.47 19.99 22.89 12.11 9.12 25.44 256.05 10.84 9.82 11.75 20.29 22.80 12.11 8.67 25.12 260.23 10.97 9.50 11.54 20.29 22.80 12.65 8.62 26.08 253.54 10.75 9.32 10.99 20.19 22.42 12.29 8.47 24.47 242.24 9.93 9.32 9.67 18.32 21.50 12.56 8.30 19.64 221.00 10.61 9.41 9.81 19.33 22.32 12.83 8.51 21.70 231.97 12.29 9.68 9.81 19.53 22.03 12.65 8.30 20.74 232.16 12.06 12.56 10.09 19.89 21.74 13.01 8.46 21.06 226.65 13.02 14.59 10.50 19.84 22.03 13.38 8.46 21.19 232.06 13.98 14.41 11.23 20.14 22.23 12.83 9.08 21.08 236.07 14.04 14.23 11.00 19.99 22.38 12.71 8.98 21.41 235.36 14.67 15.31 10.92 20.28 22.62 12.71 9.46 22.59 237.96 14.73 14.41 10.84 21.85 22.97 12.99 10.43 24.13 246.83 14.50 14.50 10.69 21.27 22.92 12.99 10.11 23.59 242.45 14.84 14.81 10.77 21.69 23.42 13.28 10.00 22.19 246.07 15.36 15.31 10.77 21.85 23.27 13.37 9.89 21.45 248.94 14.77 16.48 10.84 22.34 23.32 13.65 9.68 20.98 248.24 14.83 17.07 11.00 24.77 24.22 17.61 9.73 20.78 265.49 16.80 21.21 11.91 27.25 29.57 19.58 12.01 24.13 310.20 15.71 20.89 11.91 26.65 29.06 17.42 11.40 22.79 317.06 14.91 19.14 11.99 26.38 28.06 16.95 14.46 22.45 314.08 12.39 17.56 11.66 26.55 29.47 17.04 14.18 21.36 308.94 12.63 17.38 11.99 30.05 33.89 18.30 14.29 22.93 327.67 14.42 21.25 14.14 38.47 48.09 18.69 16.07 32.52 427.59 23.11 27.79 30.76 56.27 59.51 46.48 20.19 46.18 561.09 23.29 23.51 31.26 61.88 61.98 40.09 32.04 46.18 590.25 19.10 21.96 23.98 55.25 61.36 32.34 28.07 51.39 537.48 18.86 20.04 21.99 50.84 59.08 34.08 25.81 46.30 494.31 17.59 17.90 19.35 51.94 55.66 33.43 20.47 36.11 460.34 22.21 19.20 23.57 53.60 67.28 32.93 23.70 40.37 509.32 21.49 19.00 28.94 58.95 72.68 34.02 22.57 38.89 526.34 21.01 15.90 25.22 63.59 84.72 44.83 21.82 35.19 563.09 20.45 14.30 23.57 96.05 152.82 46.70 21.82 27.59 775.56 20.77 14.10 28.53 121.33 186.88 69.80 28.59 30.46 947.15 31.49 20.40 29.02 164.67 204.17 90.49 35.37 55.56 1083.25 42.72 25.30 43.82 172.00 182.50 90.00 42.89 58.33 1065.52 27.34 18.10 30.30 149.33 165.83 76.57 36.12 41.02 983.00 30.27 18.50 30.20 172.00 185.00 106.41 43.64 40.28 1054.69 32.14 19.20 30.40 159.00 165.00 179.99 46.65 44.50 1048.19 30.19 19.50 29.00 146.00 154.00 147.18 43.27 44.50 995.83 26.70 19.50 27.10 131.00 146.00 127.29 41.90 40.00 915.34 28.50 20.40 28.20 133.00 145.00 148.00 46.00 40.50 934.74 35.50 27.90 33.90 159.00 158.00 161.00 54.00 54.00 1105.21 30.10 38.80 30.50 142.00 142.00 139.00 46.50 51.00 1002.71 KHA LAF PMS REE SAM SGH TS4 VTC VN-INDEX 3.73% 0.00% 2.41% 1.49% -0.42% 0.00% -4.95% -1.27% 1.63% 1.22% -3.21% -1.76% 0.00% 0.00% 4.48% -0.58% 3.85% -2.57% -2.02% -1.90% -4.79% -0.49% -1.67% -2.86% -1.74% -6.17% -4.46%
  5. -7.58% 0.00% -11.95% -9.28% -4.09% 2.21% -1.94% -19.74% -8.77% 6.82% 0.97% 1.43% 5.53% 3.82% 2.16% 2.50% 10.49% 4.96% 15.80% 2.87% 0.00% 1.05% -1.30% -1.41% -2.44% -4.45% 0.08% -1.84% 29.77% 2.82% 1.82% -1.31% 2.86% 1.88% 1.55% -2.37% 7.98% 16.13% 4.11% -0.25% 1.33% 2.78% 0.00% 0.61% 2.39% 7.39% -1.23% 6.89% 1.53% 0.88% -4.05% 7.36% -0.51% 1.73% 0.39% -1.25% -2.04% -0.75% 0.67% -0.95% -1.14% 1.56% -0.30% 4.51% 7.59% -0.69% 1.44% 1.11% 0.00% 5.39% 5.50% 1.10% 0.39% -5.88% -0.70% 7.77% 1.54% 2.22% 10.23% 6.82% 3.73% -1.56% 0.62% -1.41% -2.64% -0.22% 0.00% -3.09% -2.22% -1.77% 2.38% 2.17% 0.71% 1.97% 2.16% 2.17% -1.06% -5.97% 1.49% 3.49% 3.34% 0.00% 0.74% -0.64% 0.71% -1.08% -3.32% 1.17% -3.88% 7.65% 0.71% 2.22% 0.21% 2.11% -2.17% -2.19% -0.28% 0.40% 3.55% 1.41% 10.86% 3.89% 28.97% 0.58% -0.96% 6.95% 13.31% 24.27% 8.33% 10.01% 22.06% 11.23% 23.43% 16.13% 16.84% -6.47% -1.49% 0.00% -2.18% -1.70% -11.06% -5.09% -5.56% 2.21% -5.10% -8.41% 0.64% -1.01% -3.47% -2.70% 26.83% -1.47% -0.94% -16.94% -8.24% -2.76% 0.61% 5.03% 0.56% -1.92% -4.88% -1.64% 1.99% -1.03% 2.84% 13.20% 15.01% 7.39% 0.78% 7.37% 6.06% 14.15% 22.28% 17.93% 28.01% 41.89% 2.12% 12.45% 41.79% 30.49% 60.26% 30.75% 117.54% 46.29% 23.75% 148.70% 25.61% 42.02% 31.22% 0.80% -15.41% 1.61% 9.96% 4.15% -13.75% 58.68% 0.00% 5.20% -17.99% -6.59% -23.28% -10.70% -1.00% -19.32% -12.39% 11.28% -8.94% -1.25% -8.71% -8.28% -7.99% -3.72% 5.39% -8.04% -9.91% -8.03% -6.75% -10.70% -12.03% 2.17% -5.80% -1.93% -20.70% -22.00% -6.87% 26.24% 7.26% 21.80% 3.19% 20.89% -1.47% 15.81% 11.79% 10.64% -3.23% -1.04% 22.81% 9.99% 8.02% 3.28% -4.76% -3.67% 3.34% -2.22% -16.32% -12.86% 7.87% 16.57% 31.79% -3.33% -9.52% 6.98% -2.65% -10.06% -6.56% 51.04% 80.38% 4.17% 0.00% -21.58% 37.73% 1.56% -1.40% 21.09% 26.33% 22.28% 49.47% 31.03% 10.40% 22.12% 51.60% 44.68% 1.70% 35.71% 9.25% 29.64% 23.68% 82.37% 14.37% 35.66% 24.02% 51.00% 4.45% -10.61% -0.55% 21.28% 5.00% -1.64% -36.00% -28.46% -30.85% -13.18% -9.13% -14.92% -15.79% -29.68% -7.74% 10.71% 2.21% -0.33% 15.18% 11.56% 38.96% 20.83% -1.81% 7.29% 6.18% 3.78% 0.66% -7.56% -10.81% 69.16% 6.90% 10.48% -0.62% -6.08% 1.56% -4.61% -8.18% -6.67% -18.23% -7.26% 0.00% -5.00% -11.56% 0.00% -6.55% -10.27% -5.19% -13.51% -3.16% -10.11% -8.08% 6.74% 4.62% 4.06% 1.53% -0.68% 16.27% 9.79% 1.25% 2.12% 24.56% 36.76% 20.21% 19.55% 8.97% 8.78% 17.39% 33.33% 18.24% -15.21% 39.07% -10.03% -10.69% -10.13% -13.66% -13.89% -5.56% -9.27% 3.72% 4.29% 3.98% 5.50% 5.28% 8.31% 4.79% 3.05% 3.74% 2.75% 2.31% 4.73% 1.96% 2.45% 7.66% 2.12% 3.44% 1.16% 16.59% 15.20% 21.76% 13.99% 15.64% 27.67% 14.56% 18.54% 10.77% 0.8642 0.4392 1.0887 1.2064 1.2758 1.3285 0.6831 0.8790 1.0000 HAS KHA LAF PMS REE SAM SGH TS4 VTC 8 9 10 11 12 13 14 15 16 0.0076 0.0076 0.0026 0.0076 0.0118 0.0151 0.0101 0.0057 0.0069 0.0117 0.0156 0.0108 0.0176 0.0122 0.0105 0.0160 0.0093 0.0137
  6. 0.0157 0.0236 0.0238 0.0231 0.0117 0.0014 0.0185 0.0159 0.0283 0.0158 0.0226 0.0143 0.0296 0.0111 0.0058 0.0227 0.0110 0.0183 0.0183 0.0222 0.0143 0.0304 0.0192 0.0127 0.0353 0.0144 0.0215 0.0130 0.0177 0.0110 0.0186 0.0121 0.0074 0.0215 0.0094 0.0169 0.0063 0.0041 -0.0004 0.0065 0.0106 0.0143 0.0079 0.0048 0.0027 0.0209 0.0313 0.0231 0.0360 0.0190 0.0085 0.0381 0.0147 0.0325 0.0153 0.0165 0.0096 0.0190 0.0115 0.0082 0.0203 0.0108 0.0152 0.0165 0.0275 0.0166 0.0278 0.0144 0.0066 0.0276 0.0134 0.0235 0.0096 0.0166 0.0231 0.0161 0.0070 0.0014 0.0121 0.0055 0.0198 0.0190 0.0278 0.0161 0.0473 0.0170 0.0082 0.0427 0.0152 0.0197 0.0115 0.0144 0.0070 0.0170 0.0196 0.0178 0.0217 0.0100 0.0140 0.0082 0.0066 0.0014 0.0082 0.0178 0.0245 0.0109 0.0059 0.0061 0.0203 0.0276 0.0121 0.0427 0.0217 0.0109 0.0766 0.0142 0.0220 0.0108 0.0134 0.0055 0.0152 0.0100 0.0059 0.0142 0.0212 0.0134 0.0152 0.0235 0.0198 0.0197 0.0140 0.0061 0.0220 0.0134 0.0344
  7. MÔ HÌNH DANH MỤC ĐẦU TƯ CHỨNG KHOÁN MA TRẬN PHƯƠNG SAI - HIỆP PHƯƠNG SAI SỬ DỤNG HÀM OFFSET AGF BBC BTC CAN DPC GIL GMD 0 1 2 3 4 5 6 AGF 0 0.0122 0.0085 0.0045 0.0066 0.0092 0.0078 0.0091 BBC 1 0.0085 0.0172 0.0159 0.0144 0.0140 0.0118 0.0054 BTC 2 0.0045 0.0159 0.0513 0.0188 0.0242 0.0135 0.0012 CAN 3 0.0066 0.0144 0.0188 0.0252 0.0186 0.0157 0.0039 DPC 4 0.0092 0.0140 0.0242 0.0186 0.0383 0.0173 0.0102 GIL 5 0.0078 0.0118 0.0135 0.0157 0.0173 0.0162 0.0044 GMD 6 0.0091 0.0054 0.0012 0.0039 0.0102 0.0044 0.0117 HAP 7 0.0077 0.0196 0.0328 0.0274 0.0312 0.0215 0.0050 HAS 8 0.0076 0.0117 0.0157 0.0158 0.0183 0.0130 0.0063 KHA 9 0.0076 0.0156 0.0236 0.0226 0.0222 0.0177 0.0041 LAF 10 0.0026 0.0108 0.0238 0.0143 0.0143 0.0110 -0.0004 PMS 11 0.0076 0.0176 0.0231 0.0296 0.0304 0.0186 0.0065 REE 12 0.0118 0.0122 0.0117 0.0111 0.0192 0.0121 0.0106 SAM 13 0.0151 0.0105 0.0014 0.0058 0.0127 0.0074 0.0143 SGH 14 0.0101 0.0160 0.0185 0.0227 0.0353 0.0215 0.0079 TS4 15 0.0057 0.0093 0.0159 0.0110 0.0144 0.0094 0.0048 VTC 16 0.0069 0.0137 0.0283 0.0183 0.0215 0.0169 0.0027 TỶ TRỌNG DANH MỤC 1 0.1111 0.1046 0.0980 0.0915 0.0850 0.0784 0.0719 TỶ TRỌNG DANH MỤC 2 0.0065 0.0131 0.0196 0.0261 0.0327 0.0392 0.0458 CHUYỂN ĐẢO TỪ HÀNG SANG CỘT TSSL TRUNG BÌNH TỶ TRỌNG DM1 TỶ TRỌNG DM2 DANH MỤC 1 AGF 3.45% 0.1111 0.0065 GIÁ TRỊ TB BBC 6.01% 0.1046 0.0131 PHƯƠNG SAI BTC 4.76% 0.0980 0.0196 CAN 3.08% 0.0915 0.0261 HIỆP PHƯƠNG SAI DPC 4.75% 0.0850 0.0327 HỆ SỐ TƯƠNG QUAN GIL 3.13% 0.0784 0.0392 GMD 3.34% 0.0719 0.0458 TÍNH TOÁN TSSL TỪ SỰ KẾT HAP 6.65% 0.0654 0.0523 TỶ TRỌNG DANH MỤC 1 HAS 5.20% 0.0588 0.0588 GIÁ TRỊ TSSL BÌNH QUÂN KHA 3.72% 0.0523 0.0654 PHƯƠNG SAI CỦA TSSL LAF 4.29% 0.0458 0.0719 ĐỘ LỆCH CHUẨN CỦA TSSL PMS 3.98% 0.0392 0.0784 REE 5.50% 0.0327 0.0850 SAM 5.28% 0.0261 0.0915 SGH 8.31% 0.0196 0.0980 TS4 4.79% 0.0131 0.1046 VTC 3.05% 0.0065 0.1111 TỔNG 1.0000 1.0000 BẢNG THỂ HIỆN CÁC GIÁ TRỊ TSSL ( DATA/TABLE)
  8. TỶ TRỌNG ĐL CHUẨN GIÁ TRỊ TB 12.63% 4.70% -0.5 13.58% 4.99% -0.4 13.46% 4.96% TSSL CỦA DANH MỤC CÓ -0.3 13.34% 4.93% 5.20% -0.2 13.22% 4.89% -0.1 13.11% 4.86% 5.00% 0 13.00% 4.83% TSSL BÌNH QUÂN 0.1 12.90% 4.79% 4.80% 0.2 12.80% 4.76% 0.3 12.72% 4.73% 4.60% 0.4 12.63% 4.70% 4.40% 0.5 12.55% 4.66% 0.6 12.48% 4.63% 4.20% 0.7 12.42% 4.60% 0.8 12.36% 4.57% 4.00% 0.9 12.30% 4.53% 12.00% 12.20% 12.40% 12.60% 12.80% 1 12.26% 4.50% ĐỘ L Ệ CH 1.1 12.22% 4.47% 1.2 12.18% 4.43% 1.3 12.15% 4.40% 1.4 12.13% 4.37% 1.5 12.12% 4.34%
  9. HAP HAS KHA LAF PMS REE SAM SGH TS4 VTC 7 8 9 10 11 12 13 14 15 16 0.0077 0.0076 0.0076 0.0026 0.0076 0.0118 0.0151 0.0101 0.0057 0.0069 0.0196 0.0117 0.0156 0.0108 0.0176 0.0122 0.0105 0.0160 0.0093 0.0137 0.0328 0.0157 0.0236 0.0238 0.0231 0.0117 0.0014 0.0185 0.0159 0.0283 0.0274 0.0158 0.0226 0.0143 0.0296 0.0111 0.0058 0.0227 0.0110 0.0183 0.0312 0.0183 0.0222 0.0143 0.0304 0.0192 0.0127 0.0353 0.0144 0.0215 0.0215 0.0130 0.0177 0.0110 0.0186 0.0121 0.0074 0.0215 0.0094 0.0169 0.0050 0.0063 0.0041 -0.0004 0.0065 0.0106 0.0143 0.0079 0.0048 0.0027 0.0443 0.0209 0.0313 0.0231 0.0360 0.0190 0.0085 0.0381 0.0147 0.0325 0.0209 0.0153 0.0165 0.0096 0.0190 0.0115 0.0082 0.0203 0.0108 0.0152 0.0313 0.0165 0.0275 0.0166 0.0278 0.0144 0.0066 0.0276 0.0134 0.0235 0.0231 0.0096 0.0166 0.0231 0.0161 0.0070 0.0014 0.0121 0.0055 0.0198 0.0360 0.0190 0.0278 0.0161 0.0473 0.0170 0.0082 0.0427 0.0152 0.0197 0.0190 0.0115 0.0144 0.0070 0.0170 0.0196 0.0178 0.0217 0.0100 0.0140 0.0085 0.0082 0.0066 0.0014 0.0082 0.0178 0.0245 0.0109 0.0059 0.0061 0.0381 0.0203 0.0276 0.0121 0.0427 0.0217 0.0109 0.0766 0.0142 0.0220 0.0147 0.0108 0.0134 0.0055 0.0152 0.0100 0.0059 0.0142 0.0212 0.0134 0.0325 0.0152 0.0235 0.0198 0.0197 0.0140 0.0061 0.0220 0.0134 0.0344 0.0654 0.0588 0.0523 0.0458 0.0392 0.0327 0.0261 0.0196 0.0131 0.0065 0.0523 0.0588 0.0654 0.0719 0.0784 0.0850 0.0915 0.0980 0.1046 0.1111 DANH MỤC 1 DANH MỤC 2 GIÁ TRỊ TB 4.50% GIÁ TRỊ TB 4.83% PHƯƠNG SAI 1.50% PHƯƠNG SAI 1.69% HIỆP PHƯƠNG SAI 0.0156 HỆ SỐ TƯƠNG QUAN 0.9762 TÍNH TOÁN TSSL TỪ SỰ KẾT HỢP DANH MỤC 1 &2 TỶ TRỌNG DANH MỤC 1 0.4 GIÁ TRỊ TSSL BÌNH QUÂN 4.70% PHƯƠNG SAI CỦA TSSL 1.60% ĐỘ LỆCH CHUẨN CỦA TSSL 12.63%
  10. TSSL CỦA DANH MỤC CÓ 17 CHỨNG KHOÁN % % % % % % % 12.00% 12.20% 12.40% 12.60% 12.80% 13.00% 13.20% 13.40% 13.60% 13.80% ĐỘ L Ệ CH CHUẨ N
  11. 1.0000
  12. TÍNH TOÁN DANH MỤC ĐẦU TƯ CHỨNG KHOÁN HIỆU QUẢ TRƯỜNG HỢP CÓ BÁN KHỐN MA TRẬN PHƯƠNG SAI - HIỆP PHƯƠNG SAI SỬ DỤNG HÀM OFFSET AGF BBC BTC CAN DPC GIL GMD 0 1 2 3 4 5 6 AGF 0 0.0122 0.0085 0.0045 0.0066 0.0092 0.0078 0.0091 BBC 1 0.0085 0.0172 0.0159 0.0144 0.0140 0.0118 0.0054 BTC 2 0.0045 0.0159 0.0513 0.0188 0.0242 0.0135 0.0012 CAN 3 0.0066 0.0144 0.0188 0.0252 0.0186 0.0157 0.0039 DPC 4 0.0092 0.0140 0.0242 0.0186 0.0383 0.0173 0.0102 GIL 5 0.0078 0.0118 0.0135 0.0157 0.0173 0.0162 0.0044 GMD 6 0.0091 0.0054 0.0012 0.0039 0.0102 0.0044 0.0117 HAP 7 0.0077 0.0196 0.0328 0.0274 0.0312 0.0215 0.0050 HAS 8 0.0076 0.0117 0.0157 0.0158 0.0183 0.0130 0.0063 KHA 9 0.0076 0.0156 0.0236 0.0226 0.0222 0.0177 0.0041 LAF 10 0.0026 0.0108 0.0238 0.0143 0.0143 0.0110 -0.0004 PMS 11 0.0076 0.0176 0.0231 0.0296 0.0304 0.0186 0.0065 REE 12 0.0118 0.0122 0.0117 0.0111 0.0192 0.0121 0.0106 SAM 13 0.0151 0.0105 0.0014 0.0058 0.0127 0.0074 0.0143 SGH 14 0.0101 0.0160 0.0185 0.0227 0.0353 0.0215 0.0079 TS4 15 0.0057 0.0093 0.0159 0.0110 0.0144 0.0094 0.0048 VTC 16 0.0069 0.0137 0.0283 0.0183 0.0215 0.0169 0.0027 HẰNG SỐ 0.01 GTTB TSSL TRỪ TRUNG BÌNH HẰNG SỐ Z X Z Y AGF 3.45% 2.83% 3.0388 0.4979 2.71 0.57 BBC 6.01% 5.39% 2.9063 0.4762 2.69 0.57 BTC 4.76% 4.14% -1.7235 -0.2824 -1.64 -0.35 CAN 3.08% 2.46% -0.2375 -0.0389 -0.48 -0.1 DPC 4.75% 4.13% -1.3326 -0.2184 -1.19 -0.25 GIL 3.13% 2.51% -4.9256 -0.8071 -5.01 -1.06 GMD 3.34% 2.72% 0.7429 0.1217 -0.3 -0.06 HAP 6.65% 6.03% 5.0181 0.8223 5.35 1.13 HAS 5.20% 4.58% 5.1383 0.8420 5.17 1.09 KHA 3.72% 3.10% -3.6321 -0.5952 -3.45 -0.73 LAF 4.29% 3.67% 4.1804 0.6850 3.66 0.77 PMS 3.98% 3.36% -3.2973 -0.5403 -3.17 -0.67 REE 5.50% 4.88% 4.0830 0.6690 3.75 0.79 SAM 5.28% 4.66% -3.8646 -0.6333 -3.08 -0.65 SGH 8.31% 7.69% 1.1231 0.1840 1.04 0.22 TS4 4.79% 4.17% 2.9055 0.4761 2.74 0.58 VTC 3.05% 2.43% -4.0204 -0.6588 -4.07 -0.86 TÍNH TOÁN ĐƯỜNG BIÊN HIỆU QUẢ TÍNH TOÁN CHO MỘT DANH MỤC TỶ LỆ VỐN ĐẦU TƯ VÀO X 0.3 TSSL TRUNG BÌNH CỦA DANH MỤC 12.09%
  13. ĐỘ LỆCH CHUẨN CỦA DANH MỤC 15.14% DATA TABLE ĐỒ THỊ ĐƯỜNG BIÊN HIỆU QUẢ TỶ TRỌNG ĐL CHUẨN TSSL 15.14% 12.09% -0.5 17.51% 13.89% -0.4 17.21% 13.66% 13.66% DANH MỤC W -0.3 16.91% 13.44% ĐƯỜ -0.2 16.61% 13.21% 15.00% -0.1 16.32% 12.99% 14.00% 0 16.02% 12.76% 12.76% DANH MỤC Y 13.00% TSSL BÌNH QUÂN 0.1 15.72% 12.54% 0.2 15.43% 12.31% 12.31% DANH MỤC Z 12.00% 0.3 15.14% 12.09% 11.00% 0.4 14.84% 11.86% 10.00% Column C 0.5 14.55% 11.64% 9.00% 0.6 14.26% 11.41% 8.00% 0.7 13.98% 11.19% 7.00% 0.8 13.69% 10.96% 20.00% 0.9 13.41% 10.74% 10.00% 1 13.13% 10.52% 10.52% DANH MỤC X 1.1 12.85% 10.29% ĐỘ LỆCH CHUẨN 1.2 12.57% 10.07% 1.3 12.30% 9.84% 1.4 12.02% 9.62% 1.5 11.75% 9.39% 1.6 11.49% 9.17% 1.7 11.22% 8.94% 1.8 10.96% 8.72% 1.9 10.71% 8.49% 2 10.46% 8.27% 8.27% DANH MỤC Q
  14. TRƯỜNG HỢP CÓ BÁN KHỐNG HAP HAS KHA LAF PMS REE SAM SGH TS4 VTC 7 8 9 10 11 12 13 14 15 16 0.0077 0.0076 0.0076 0.0026 0.0076 0.0118 0.0151 0.0101 0.0057 0.0069 0.0196 0.0117 0.0156 0.0108 0.0176 0.0122 0.0105 0.0160 0.0093 0.0137 0.0328 0.0157 0.0236 0.0238 0.0231 0.0117 0.0014 0.0185 0.0159 0.0283 0.0274 0.0158 0.0226 0.0143 0.0296 0.0111 0.0058 0.0227 0.0110 0.0183 0.0312 0.0183 0.0222 0.0143 0.0304 0.0192 0.0127 0.0353 0.0144 0.0215 0.0215 0.0130 0.0177 0.0110 0.0186 0.0121 0.0074 0.0215 0.0094 0.0169 0.0050 0.0063 0.0041 -0.0004 0.0065 0.0106 0.0143 0.0079 0.0048 0.0027 0.0443 0.0209 0.0313 0.0231 0.0360 0.0190 0.0085 0.0381 0.0147 0.0325 0.0209 0.0153 0.0165 0.0096 0.0190 0.0115 0.0082 0.0203 0.0108 0.0152 0.0313 0.0165 0.0275 0.0166 0.0278 0.0144 0.0066 0.0276 0.0134 0.0235 0.0231 0.0096 0.0166 0.0231 0.0161 0.0070 0.0014 0.0121 0.0055 0.0198 0.0360 0.0190 0.0278 0.0161 0.0473 0.0170 0.0082 0.0427 0.0152 0.0197 0.0190 0.0115 0.0144 0.0070 0.0170 0.0196 0.0178 0.0217 0.0100 0.0140 0.0085 0.0082 0.0066 0.0014 0.0082 0.0178 0.0245 0.0109 0.0059 0.0061 0.0381 0.0203 0.0276 0.0121 0.0427 0.0217 0.0109 0.0766 0.0142 0.0220 0.0147 0.0108 0.0134 0.0055 0.0152 0.0100 0.0059 0.0142 0.0212 0.0134 0.0325 0.0152 0.0235 0.0198 0.0197 0.0140 0.0061 0.0220 0.0134 0.0344 CHUYỂN ĐẢO X 0.4979 0.4762 -0.2824 -0.0389 -0.2184 -0.8071 0.1217 0.8223 CHUYỂN ĐẢO Y 0.5735 0.5680 -0.3462 -0.1016 -0.2513 -1.0581 -0.0624 1.1316 GTTB (X) 0.1052 GTTB (Y) 0.1276 VAR (X) 0.0172 VAR (Y) 0.0257 ĐỘ LỆCH CHUẨN (X) 0.1313 ĐỘ LỆCH CHUẨN (Y) 0.1602 HIỆP PHƯƠNG SAI (X,Y) 0.0209 ĐỘ LỆCH CHUẨN 0.9945
  15. ĐƯỜNG BIÊN HIỆU QUẢ .00% .00% .00% .00% .00% .00% Column C 10.52% 12.76% 12.31% 13.66% 8.27% .00% .00% .00% 20.00% 10.00% Ộ LỆCH CHUẨN
  16. 0.8420 -0.5952 0.6850 -0.5403 0.6690 -0.6333 0.1840 0.4761 -0.6588 1.0916 -0.7300 0.7727 -0.6690 0.7931 -0.6502 0.2206 0.5781 -0.8605
  17. TÌM DANH MỤC THỊ TRƯỜNG: ĐƯỜNG THỊ TRƯỜNG VỐN CML Gọi M là danh mục hiểu quả tuân theo phương trình: R - rf = Sz Zi Mi = N ∑Z i=1 i HẰNG SỐ 0.01 TSSL TSSL TRUNG BÌNH CHUYỂN ĐẢO M 0.5735 TRUNG BÌNH ĐIỀU CHỈNH Z M 3.45% 2.83% 2.7138 0.5735 VẼ ĐƯỜNG CML 6.01% 5.39% 2.6879 0.5680 ĐL CHUẨN GIÁ TRỊ TB 4.76% 4.14% -1.6383 -0.3462 0.00% 0.62% 3.08% 2.46% -0.4806 -0.1016 16.02% 12.76% 4.75% 4.13% -1.1889 -0.2513 25.00% 19.57% 3.13% 2.51% -5.0069 -1.0581 3.34% 2.72% -0.2954 -0.0624 6.65% 6.03% 5.3546 1.1316 5.20% 4.58% 5.1651 1.0916 3.72% 3.10% -3.4540 -0.7300 4.29% 3.67% 3.6563 0.7727 3.98% 3.36% -3.1655 -0.6690 5.50% 4.88% 3.7530 0.7931 5.28% 4.66% -3.0764 -0.6502 8.31% 7.69% 1.0437 0.2206 4.79% 4.17% 2.7354 0.5781 3.05% 2.43% -4.0719 -0.8605 GTTB 12.76% ĐL CHUẨN 16.02% DATA TABLE ĐỒ THỊ ĐƯỜNG BIÊN HIỆU QUẢ TỶ TRỌNG ĐL CHUẨN TSSL 15.27% 12.19% -0.5 17.80% 14.10% -0.4 17.48% 13.86% -0.3 17.16% 13.62% -0.2 16.84% 13.38% -0.1 16.52% 13.14% ĐƯỜNG BIÊN HIỆU QU 0 16.21% 12.90% 25.00% 0.1 15.89% 12.67% 20.00% BÌNH QUÂN 15.00% Column C Linear Regr 10.00%
  18. ĐƯỜNG BIÊN HIỆU QU 25.00% 0.2 15.58% 12.43% 20.00% TSSL BÌNH QUÂN 0.3 15.27% 12.19% 0.4 14.95% 11.95% 15.00% Column C 0.5 14.64% 11.71% 0.6 14.34% 11.47% Linear Regr 10.00% 0.7 14.03% 11.23% 0.62% 0.8 13.73% 10.99% 0.9 13.43% 10.75% 5.00% 1 13.13% 10.52% 1.1 12.83% 10.28% 0.00% 1.2 12.53% 10.04% 0.00% 100.00% 200.00% 1.3 12.24% 9.80% ĐỘ LỆCH CHUẨN 1.4 11.95% 9.56% 1.5 11.67% 9.32% 1.6 11.39% 9.08% 1.7 11.11% 8.84% 1.8 10.83% 8.60% 1.9 10.56% 8.36% 2 10.30% 8.13%

CÓ THỂ BẠN MUỐN DOWNLOAD

Đồng bộ tài khoản