# Đọc file Excel
data <- read_excel("D:/TIỂU LUẬN C5/DULIEU.xlsx")
# Chuyển Date về dạng Date
data$Date <- as.Date(data$Date, format = "%d/%m/%Y")
# Sắp xếp tăng dần theo thời gian
data <- data %>% arrange(Date)
# Lãi suất phi rủi ro hằng ngày (~252 ngày giao dịch/năm)
rf_annual <- 0.03 # 3%/năm
rf_daily <- 0.03 / 252
# Tính lợi suất đơn giản hàng ngày
returns <- data %>%
mutate(across(-Date, ~ (.x - lag(.x)) / lag(.x))) %>%
slice(-1) # bỏ dòng NA đầu tiên
# Excess return cho thị trường (VNI)
returns <- returns %>%
mutate(Excess_Market = VNI - rf_daily)
# Danh sách cổ phiếu (trừ VNI)
stocks <- setdiff(names(returns), c("Date", "VNI", "Excess_Market"))
# Khởi tạo các bảng rỗng
capm_table <- data.frame()
test_table <- data.frame()
expected_return_table <- data.frame()
# 1️⃣ Thống kê mô tả (returns)
desc_table <- summary(returns)
# Vòng lặp cho từng cổ phiếu
for (stock_name in stocks) {
df <- returns %>%
mutate(Excess_Stock = .data[[stock_name]] - rf_daily)
model <- lm(Excess_Stock ~ Excess_Market, data = df)
# 1️⃣ Bảng kết quả CAPM
capm_res <- tidy(model) %>%
mutate(Stock = stock_name,
R2 = summary(model)$r.squared)
capm_table <- bind_rows(capm_table, capm_res)
# 2️⃣ Bảng kiểm định mô hình
shapiro_p <- shapiro.test(residuals(model))$p.value
bp_p <- bptest(model)$p.value
dw_p <- dwtest(model)$p.value
test_res <- data.frame(
Stock = stock_name,
Shapiro_p = shapiro_p,
BP_p = bp_p,
DW_p = dw_p
)
test_table <- bind_rows(test_table, test_res)
# 3️⃣ Bảng tỷ suất sinh lời kỳ vọng theo CAPM
beta_val <- coef(model)["Excess_Market"]
mean_market_return <- mean(returns$VNI, na.rm = TRUE) * 252 # Quy đổi ra năm
expected_return <- rf_annual + beta_val * (mean_market_return - rf_annual)
expected_return_table <- bind_rows(
expected_return_table,
data.frame(
Stock = stock_name,
Beta = beta_val,
Expected_Annual_Return = expected_return
)
)
}
# Xuất kết quả
desc_table
## Date DGW MWG
## Min. :2018-08-02 Min. :-0.290149 Min. :-0.0699704
## 1st Qu.:2020-05-07 1st Qu.:-0.013224 1st Qu.:-0.0084722
## Median :2022-02-07 Median : 0.000000 Median : 0.0000000
## Mean :2022-02-04 Mean : 0.001614 Mean : 0.0007757
## 3rd Qu.:2023-11-06 3rd Qu.: 0.015834 3rd Qu.: 0.0105578
## Max. :2025-08-12 Max. : 0.070009 Max. : 0.0699582
## PNJ MSN PSD
## Min. :-0.0699704 Min. :-0.1892879 Min. :-0.100012
## 1st Qu.:-0.0082348 1st Qu.:-0.0106019 1st Qu.:-0.011851
## Median : 0.0000000 Median : 0.0000000 Median : 0.000000
## Mean : 0.0005218 Mean : 0.0002463 Mean : 0.001168
## 3rd Qu.: 0.0095713 3rd Qu.: 0.0110738 3rd Qu.: 0.012509
## Max. : 0.0699905 Max. : 0.0699263 Max. : 0.100014
## FRT PET VNI
## Min. :-0.070001 Min. :-0.142573 Min. :-0.0667689
## 1st Qu.:-0.011628 1st Qu.:-0.010753 1st Qu.:-0.0041887
## Median : 0.000000 Median : 0.000000 Median : 0.0011050
## Mean : 0.001358 Mean : 0.001204 Mean : 0.0003653
## 3rd Qu.: 0.013252 3rd Qu.: 0.013488 3rd Qu.: 0.0062911
## Max. : 0.070000 Max. : 0.070009 Max. : 0.0676597
## Excess_Market
## Min. :-0.0668879
## 1st Qu.:-0.0043078
## Median : 0.0009860
## Mean : 0.0002462
## 3rd Qu.: 0.0061721
## Max. : 0.0675406
capm_table
## term estimate std.error statistic p.value Stock
## 1 (Intercept) 0.0011535697 0.0005771529 1.9987247 4.579224e-02 DGW
## 2 Excess_Market 1.3875906210 0.0480638329 28.8697454 2.810462e-150 DGW
## 3 (Intercept) 0.0003621018 0.0003703052 0.9778471 3.282848e-01 MWG
## 4 Excess_Market 1.1964752900 0.0308380802 38.7986309 2.800819e-238 MWG
## 5 (Intercept) 0.0001615769 0.0003612536 0.4472672 6.547374e-01 PNJ
## 6 Excess_Market 0.9795714559 0.0300842911 32.5608954 2.627098e-182 PNJ
## 7 (Intercept) -0.0001369115 0.0004279751 -0.3199053 7.490783e-01 MSN
## 8 Excess_Market 1.0727534024 0.0356406833 30.0991256 8.168252e-161 MSN
## 9 (Intercept) 0.0009807778 0.0007826280 1.2531852 2.103054e-01 PSD
## 10 Excess_Market 0.2774603890 0.0651752831 4.2571413 2.180083e-05 PSD
## 11 (Intercept) 0.0009610190 0.0005827624 1.6490752 9.931129e-02 FRT
## 12 Excess_Market 1.1276358042 0.0485309781 23.2353818 2.433084e-104 FRT
## 13 (Intercept) 0.0007790202 0.0005770274 1.3500576 1.771717e-01 PET
## 14 Excess_Market 1.2440961188 0.0480533846 25.8898749 1.960422e-125 PET
## R2
## 1 0.32211570
## 2 0.32211570
## 3 0.46185321
## 4 0.46185321
## 5 0.37673492
## 6 0.37673492
## 7 0.34059093
## 8 0.34059093
## 9 0.01022686
## 10 0.01022686
## 11 0.23535768
## 12 0.23535768
## 13 0.27648789
## 14 0.27648789
test_table
## Stock Shapiro_p BP_p DW_p
## BP...1 DGW 3.592813e-27 0.400209480 6.090746e-05
## BP...2 MWG 4.703643e-22 0.278969251 7.288717e-05
## BP...3 PNJ 2.804249e-21 0.121782344 5.432667e-01
## BP...4 MSN 1.858962e-25 0.008577482 3.202574e-04
## BP...5 PSD 2.412804e-27 0.478907181 1.000000e+00
## BP...6 FRT 8.118794e-20 0.988917952 7.513008e-10
## BP...7 PET 1.991432e-23 0.227228312 3.747127e-07
expected_return_table
## Stock Beta Expected_Annual_Return
## Excess_Market...1 DGW 1.3875906 0.11609468
## Excess_Market...2 MWG 1.1964753 0.10423670
## Excess_Market...3 PNJ 0.9795715 0.09077865
## Excess_Market...4 MSN 1.0727534 0.09656024
## Excess_Market...5 PSD 0.2774604 0.04721535
## Excess_Market...6 FRT 1.1276358 0.09996548
## Excess_Market...7 PET 1.2440961 0.10719139