Cách Vẽ Hàm Markowitz Trong Excel: Hướng Dẫn Chi Tiết Từ A Đến Z

Giới thiệu chung về mô hình Markowitz

Mô hình Markowitz, còn được gọi là Mô hình tối ưu hoá danh mục đầu tư (Portfolio Optimization), là một trong những nền tảng quan trọng nhất của tài chính hiện đại. Được Harry Markowitz phát triển vào những năm 1950, mô hình này giúp nhà đầu tư lựa chọn danh mục đầu tư tối ưu bằng cách cân bằng giữa rủi ro (độ lệch chuẩn) và lợi nhuận kỳ vọng.

Các khái niệm cơ bản

1. Lợi nhuận kỳ vọng (Expected Return): Giá trị trung bình kỳ vọng của lợi nhuận tài sản trong một khoảng thời gian nhất định.
2. Rủi ro (Risk): Độ biến động của lợi nhuận, thường được đo bằng độ lệch chuẩn (Standard Deviation).
3. Hệ số tương quan (Correlation Coefficient): Đánh giá mức độ liên quan giữa hai tài sản.
4. Biên giới hiệu quả (Efficient Frontier): Tập hợp các danh mục đầu tư mang lại lợi nhuận kỳ vọng cao nhất cho mỗi mức độ rủi ro, hoặc rủi ro thấp nhất cho mỗi mức lợi nhuận kỳ vọng.

Mục tiêu cuối cùng của Markowitz là tìm ra các trọng số (weights) cho từng tài sản sao cho danh mục đầu tư nằm trên biên giới hiệu quả.

Tại sao nên dùng Excel để vẽ hàm Markowitz?

Excel là công cụ phổ biến, dễ tiếp cận và có khả năng tính toán mạnh mẽ thông qua các hàm thống kê, công cụ Solver và các đồ thị. Đối với những nhà đầu tư hoặc sinh viên chưa có nền tảng lập trình, Excel cung cấp một môi trường trực quan để:

• Thu thập và xử lý dữ liệu giá cổ phiếu (hoặc các tài sản khác).
• Tính toán lợi nhuận kỳ vọng, độ lệch chuẩn, ma trận hiệp phương sai.
• Sử dụng Solver để tối ưu hoá danh mục.
• Vẽ biểu đồ biên giới hiệu quả (Efficient Frontier).

Dưới đây là hướng dẫn chi tiết từng bước để vẽ hàm Markowitz trong Excel, từ việc chuẩn bị dữ liệu đến khi có được biểu đồ cuối cùng.

Bước 1: Chuẩn bị dữ liệu lịch sử

Có thể bạn quan tâm: Cách Vẽ Hàm F(x): Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

1.1 Lựa chọn tài sản

Bạn cần ít nhất ba tài sản để mô hình có ý nghĩa (trong thực tế, thường là 5-20 tài sản). Ví dụ:

Ticker	Tên công ty
AAPL	Apple Inc.
MSFT	Microsoft Corp.
GOOG	Alphabet Inc.
AMZN	Amazon.com Inc.
JPM	JPMorgan Chase & Co.

1.2 Thu thập dữ liệu giá đóng cửa

Bạn có thể tải dữ liệu từ các nguồn miễn phí như Yahoo Finance, Google Finance hoặc Bloomberg (nếu có bản quyền). Đối với Excel, cách đơn giản là:

1. Mở Excel, vào Data → From Web (hoặc Data → Get Data → From Other Sources → From Web).
2. Dán URL của trang lịch sử giá (ví dụ: https://finance.yahoo.com/quote/AAPL/history?p=AAPL).
3. Chọn bảng chứa dữ liệu, nhấp Load.

Lưu ý: Lấy dữ liệu hàng ngày trong khoảng 2-5 năm để có đủ quan sát.

1.3 Tính lợi nhuận hàng ngày

Giả sử giá đóng cửa nằm ở cột B, bắt đầu từ ô B2 (tiêu đề ở B1). Sử dụng công thức:

=LN(B3/B2)

Công thức này tính lợi nhuận logarit (log return) cho ngày thứ 2 so với ngày thứ 1. Sao chép công thức xuống hết dữ liệu. Lặp lại cho tất cả các tài sản, mỗi tài sản ở một cột riêng (C, D, E,…).

Bước 2: Tính các tham số thống kê

Có thể bạn quan tâm: Cách Vẽ Hàm E^x: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

2.1 Lợi nhuận kỳ vọng

Cho mỗi tài sản, tính trung bình lợi nhuận:

=AVERAGE(C2:C1000) // Nếu cột C là lợi nhuận của tài sản 1

Kết quả là lợi nhuận kỳ vọng hàng ngày. Để chuyển sang lợi nhuận hàng năm, nhân với số ngày giao dịch trong năm (khoảng 252):

=AVERAGE(C2:C1000)252

2.2 Độ lệch chuẩn (Risk)

=STDEV.S(C2:C1000) // Độ lệch chuẩn hàng ngày

Nhân với SQRT(252) để chuyển sang độ lệch chuẩn hàng năm:

=STDEV.S(C2:C1000)SQRT(252)

2.3 Ma trận hiệp phương sai (Covariance Matrix)

Giả sử lợi nhuận của các tài sản được đặt trong các cột C, D, E, F, G. Để tạo ma trận hiệp phương sai:

1. Chọn một vùng trống, ví dụ J2:L6 (5×5 cho 5 tài sản).
2. Nhập công thức mảng (đối với Excel 365, công thức động) trong ô J2:

=COVARIANCE.S(OFFSET($C$2,0,0,ROWS($C$2:$C$1000),1), OFFSET($C$2,0,0,ROWS($C$2:$C$1000),1))

1. Thay đổi tham chiếu cột cho từng ô trong ma trận (hoặc sử dụng hàm MMULT và TRANSPOSE).
2. Nhân toàn bộ ma trận với 252 để đưa về mức độ hiệp phương sai hàng năm.

Một cách nhanh hơn: sử dụng Data Analysis Toolpak → Covariance để tự động tạo ma trận hiệp phương sai.

Bước 3: Xây dựng mô hình tối ưu với Solver

3.1 Khởi tạo trọng số (Weights)

Trong một cột mới (ví dụ M2:M6), nhập các trọng số ban đầu, ví dụ đều là 0.2 (đối với 5 tài sản). Đặt tên cho vùng này là Weights (bạn có thể dùng Formulas → Define Name).

Có thể bạn quan tâm: Cách Vẽ Hàm Bậc Hai: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

3.2 Ràng buộc tổng trọng số = 1

Trong một ô (ví dụ N2), tính tổng trọng số:

=SUM(Weights)

3.3 Tính lợi nhuận danh mục

Trong ô O2, tính lợi nhuận danh mục (hàng năm):

=MMULT(TRANSPOSE(Weights), ExpectedReturns)

Trong đó ExpectedReturns là vùng chứa lợi nhuận kỳ vọng năm của từng tài sản (ví dụ P2:P6).

3.4 Tính rủi ro danh mục (độ lệch chuẩn)

Trong ô P2, tính phương sai danh mục:

=MMULT(MMULT(TRANSPOSE(Weights), CovMatrix), Weights)

Trong đó CovMatrix là ma trận hiệp phương sai đã nhân 252 (ví dụ Q2:U6). Độ lệch chuẩn là căn bậc hai:

=SQRT(P2)

3.5 Cài đặt Solver

1. Vào Data → Solver. (Nếu chưa có, vào File → Options → Add-ins → Manage Excel Add-ins → Go → tick Solver Add-in).
2. Set Objective: Chọn ô lợi nhuận danh mục O2 (hoặc ô độ lệch chuẩn nếu muốn tối thiểu hoá rủi ro).
3. To: Chọn Max (để tối đa hoá lợi nhuận) hoặc Min (để tối thiểu hoá rủi ro).
4. By Changing Variable Cells: Chọn vùng trọng số Weights (M2:M6).
5. Subject to the Constraints:
6. Thêm ràng buộc N2 = 1 (tổng trọng số bằng 1).
7. Nếu muốn không cho phép bán khống, thêm Weights >= 0.
8. Select Solving Method: Chọn GRG Nonlinear (đối với mô hình phi tuyến).
9. Nhấn Solve. Solver sẽ trả về bộ trọng số tối ưu.

Bước 4: Xây dựng Biên Giới Hiệu Quả (Efficient Frontier)

4.1 Tạo dải mức rủi ro mục tiêu

Chọn một dải giá trị rủi ro (độ lệch chuẩn) từ 0 đến mức rủi ro cao nhất (ví dụ 0% – 30%). Đặt các giá trị này trong một cột (ví dụ R2:R22, mỗi ô cách nhau 1.5%).

Có thể bạn quan tâm: Cách Vẽ Hàm Bậc Nhất: Hướng Dẫn Chi Tiết Từ Cơ Bản Đến Nâng Cao

4.2 Tối ưu hoá cho mỗi mức rủi ro

Đối với mỗi mức rủi ro mục tiêu, thực hiện các bước sau:

1. Mở Solver.
2. Set Objective: Lợi nhuận danh mục O2 → Max.
3. Changing Variable Cells: Trọng số Weights.
4. Constraints:
5. N2 = 1 (tổng trọng số = 1).
6. P2 <= TargetRisk (độ lệch chuẩn ≤ rủi ro mục tiêu).
7. Weights >= 0 (nếu không cho bán khống).
8. Nhấn Solve và lưu lợi nhuận tối đa cho mức rủi ro đó vào một cột (ví dụ S2).

Lặp lại cho tất cả các mức rủi ro. Để tự động hoá, bạn có thể ghi macro VBA hoặc sử dụng Data Table (bảng dữ liệu) kết hợp với Solver (công cụ “Solver Table” trong một số add-in như Frontline Solver).

4.3 Vẽ biểu đồ

1. Chọn hai cột Risk (R) và Return (S).
2. Vào Insert → Scatter → Scatter with Smooth Lines.
3. Đặt tiêu đề: “Biên Giới Hiệu Quả (Efficient Frontier)”.
4. Thêm các series khác nếu muốn so sánh với danh mục hiện tại (ví dụ danh mục có trọng số bằng nhau).

Bước 5: Phân tích và đưa ra quyết định đầu tư

Sau khi có biểu đồ, bạn có thể:

• Xác định danh mục tối ưu dựa trên mức độ chấp nhận rủi ro cá nhân (đọc từ biểu đồ).
• So sánh danh mục hiện tại với biên giới hiệu quả: Nếu danh mục hiện tại nằm dưới đường biên giới, nó không tối ưu và cần điều chỉnh.
• Xem xét các yếu tố phi tài chính (đánh giá chất lượng công ty, tin tức, v.v.) để quyết định có nên thay đổi trọng số hay không.

Mẹo và lưu ý khi dùng Excel cho Markowitz

Mẹo	Mô tả
Sử dụng Excel 365	Các hàm động (Dynamic Arrays) giúp tính ma trận nhanh hơn (=MMULT, =TRANSPOSE).
Kiểm tra dữ liệu thiếu	Loại bỏ hoặc thay thế các giá trị NA (#N/A) để tránh lỗi tính toán.
Giới hạn bán khống	Đặt ràng buộc Weights >= 0 nếu không muốn bán khống; nếu muốn, cho phép giá trị âm.
Thêm ràng buộc tối đa trọng số	Để tránh tập trung quá lớn vào một tài sản, thêm Weights <= 0.3 (30%).
Sử dụng Solver Table	Add-in như Frontline Solver có tính năng tạo bảng Solver tự động, giảm thời gian lặp lại.
Kiểm tra độ hội tụ	Nếu Solver không hội tụ, thử thay đổi phương pháp (Evolutionary) hoặc khởi tạo trọng số khác.
Kiểm định ổn định	Thực hiện backtesting bằng cách áp dụng trọng số tối ưu trên dữ liệu mới (out-of-sample).

Ví dụ thực tế: Ứng dụng cho 5 cổ phiếu công nghệ

Giả sử bạn đã tải dữ liệu 5 năm cho AAPL, MSFT, GOOG, AMZN, JPM. Sau các bước trên, bạn thu được:

Tài sản	Lợi nhuận kỳ vọng (%/năm)	Độ lệch chuẩn (%/năm)
AAPL	12.5	22.3
MSFT	11.8	20.7
GOOG	13.2	23.5
AMZN	14.0	27.1
JPM	8.5	15.4

Ma trận hiệp phương sai (đã nhân 252) cho 5 tài sản được tính và nhập vào Excel. Khi chạy Solver với ràng buộc không bán khống, bạn nhận được trọng số tối ưu:

Tài sản	Trọng số
AAPL	0.25
MSFT	0.30
GOOG	0.20
AMZN	0.15
JPM	0.10

Lợi nhuận danh mục tối ưu: 13.1%/năm
Độ lệch chuẩn danh mục: 21.0%/năm

Biên giới hiệu quả được vẽ, cho thấy nếu bạn chấp nhận rủi ro 18% thì lợi nhuận kỳ vọng có thể đạt 11.8%, trong khi rủi ro 25% cho lợi nhuận 14.5%.

Kết luận

Việc vẽ hàm Markowitz trong Excel không chỉ giúp bạn hiểu sâu hơn về mối quan hệ giữa rủi ro và lợi nhuận, mà còn cung cấp một công cụ thực tiễn để xây dựng danh mục đầu tư tối ưu. Các bước từ thu thập dữ liệu, tính toán thống kê, sử dụng Solver và vẽ biểu đồ biên giới hiệu quả đều có thể thực hiện hoàn toàn trong Excel mà không cần đến phần mềm chuyên sâu.

Những điểm quan trọng cần nhớ

1. Dữ liệu chất lượng: Đảm bảo dữ liệu lịch sử đầy đủ, không có giá trị thiếu.
2. Ràng buộc thực tế: Áp dụng các giới hạn như không bán khống, trọng số tối đa cho từng tài sản.
3. Kiểm tra tính ổn định: Thực hiện backtesting để xác nhận mô hình không chỉ phù hợp với dữ liệu quá khứ.
4. Cập nhật thường xuyên: Thị trường thay đổi, vì vậy cần cập nhật dữ liệu và tái tối ưu danh mục mỗi quý hoặc mỗi năm.

Cuối cùng, dù mô hình Markowitz là một công cụ mạnh mẽ, nó vẫn không thể dự đoán mọi rủi ro (ví dụ rủi ro hệ thống, sự kiện bất thường). Do đó, hãy kết hợp với các phương pháp quản trị rủi ro khác và luôn duy trì một chiến lược đầu tư phù hợp với mục tiêu tài chính và khả năng chịu đựng rủi ro của bản thân.

Bài viết này cung cấp hướng dẫn chi tiết để bạn tự tin vẽ hàm Markowitz trong Excel, từ khởi tạo dữ liệu tới phân tích kết quả, giúp tối ưu hoá danh mục đầu tư một cách khoa học và hiệu quả.