Cách tính lợi suất và rủi ro danh mục trong Excel: ma trận hiệp phương sai, MMULT và lỗi thường gặp
Lợi suất của danh mục là bình quân gia quyền lợi suất các tài sản, nhưng rủi ro của danh mục (đo bằng độ lệch chuẩn) thì không, vì còn phụ thuộc vào việc các tài sản biến động cùng chiều đến đâu. Để tính rủi ro danh mục trong Excel cần ba thứ: chuỗi lợi suất của từng tài sản, ma trận hiệp phương sai và vector tỷ trọng. Bài viết hướng dẫn từng bước trên một danh mục ba tài sản, kiểm tra lại kết quả bằng hai cách độc lập và chỉ ra các lỗi khiến Excel cho ra con số sai mà không báo lỗi.
1. Rủi ro danh mục được tính thế nào?
Rủi ro danh mục thường được đo bằng độ lệch chuẩn lợi suất của danh mục, tính từ tỷ trọng, độ lệch chuẩn của từng tài sản và hiệp phương sai giữa mọi cặp tài sản. Với tài sản, tỷ trọng , lợi suất trung bình và hiệp phương sai :
Trong đó là ma trận hiệp phương sai (covariance matrix) kích thước : đường chéo là phương sai của từng tài sản (), các ô ngoài đường chéo là hiệp phương sai từng cặp. là vector tỷ trọng, tổng bằng 100%. Độ lệch chuẩn danh mục là căn bậc hai của .
Dạng ma trận là lý do Excel cần hàm MMULT: với 3 tài sản, tổng kép có 9 số hạng; với 20 tài sản, có 400 số hạng, không thể viết tay. Vì sao tương quan làm rủi ro danh mục thấp hơn rủi ro bình quân được giải thích trong bài Đa dạng hóa danh mục đầu tư là gì?. Bài này chỉ tập trung vào cách tính trong bảng tính.
2. Chuẩn bị dữ liệu lợi suất
Đầu vào cần có là chuỗi lợi suất theo kỳ của từng tài sản, cùng tần suất, cùng giai đoạn, xếp mỗi tài sản một cột. Nếu chỉ có giá, lợi suất đơn của kỳ tính bằng =B3/B2-1 (giá kỳ này chia giá kỳ trước, trừ 1), sau đó kéo xuống cho các kỳ sau. Nhập lợi suất ở dạng thập phân (0,042) và định dạng ô kiểu phần trăm, không nhập số 4,2 rồi tự hiểu là phần trăm: khi đó phương sai và hiệp phương sai tính theo %², lớn gấp 10.000 lần, rất dễ đọc nhầm hoặc ghép sai với các ô khác.
Ví dụ xuyên suốt bài: Quỹ YX (quỹ giả định) nắm ba thành phần: nhóm cổ phiếu ngân hàng 50%, nhóm cổ phiếu tiêu dùng 30% và trái phiếu 20%. Lợi suất tháng của từng thành phần trong 12 tháng nằm ở ô B2:D13, tỷ trọng ở hàng B17:D17. Bố cục bảng tính như Hình 1. Số liệu chỉ để minh họa cách tính; 12 quan sát là quá ít cho một ước lượng thực tế (xem mục 10).
Bài dùng lợi suất đơn vì lợi suất danh mục trong một kỳ đúng bằng bình quân gia quyền lợi suất đơn của các tài sản; lợi suất log không có tính chất cộng theo tài sản như vậy. Khác biệt giữa hai loại lợi suất được trình bày trong bài Volatility là gì?
Công thức trong bài viết theo máy đặt định dạng vùng Việt Nam, dùng dấu chấm phẩy ngăn các đối số. Excel định dạng tiếng Anh (Mỹ) dùng dấu phẩy, ví dụ =COVARIANCE.S(B2:B13,C2:C13).
3. Bước 1: Lợi suất trung bình và độ lệch chuẩn từng tài sản
Lợi suất trung bình dùng hàm AVERAGE, độ lệch chuẩn dùng hàm STDEV.S. Ô B15 là =AVERAGE(B2:B13), ô B16 là =STDEV.S(B2:B13), rồi kéo sang cột C và D.
| Chỉ tiêu (theo tháng) | Cổ phiếu ngân hàng | Cổ phiếu tiêu dùng | Trái phiếu |
|---|---|---|---|
| Lợi suất trung bình (AVERAGE) | 0,942% | 0,608% | 0,517% |
| Độ lệch chuẩn (STDEV.S) | 4,629% | 2,441% | 0,310% |
Microsoft mô tả STDEV.S là hàm "ước lượng độ lệch chuẩn dựa trên một mẫu" và tính theo phương pháp chia cho . STDEV.P chia cho , dùng khi dữ liệu là toàn bộ tổng thể. Chuỗi lợi suất lịch sử được xem là một mẫu của quá trình tạo ra lợi suất, nên quy ước phổ biến là dùng STDEV.S. Điều quan trọng hơn chọn .S hay .P là dùng cùng một quy ước cho mọi bước (mục 9.2).
4. Bước 2: Lập ma trận hiệp phương sai
Ma trận hiệp phương sai được lập bằng hàm COVARIANCE.S cho từng cặp tài sản. Ô G2 là =COVARIANCE.S(B2:B13;B2:B13) (ngân hàng với chính nó), ô H2 là =COVARIANCE.S(B2:B13;C2:C13), và cứ thế cho 9 ô G2:I4. Hiệp phương sai của một chuỗi với chính nó là phương sai, nên đường chéo bằng bình phương các độ lệch chuẩn ở bước 1 (ví dụ ).
| Hiệp phương sai | Ngân hàng | Tiêu dùng | Trái phiếu |
|---|---|---|---|
| Ngân hàng | 0,002143 | 0,000730 | −0,000060 |
| Tiêu dùng | 0,000730 | 0,000596 | −0,000027 |
| Trái phiếu | −0,000060 | −0,000027 | 0,000010 |
Ma trận đối xứng qua đường chéo: ô H2 bằng ô G3. Nếu hai ô này khác nhau, công thức đang tham chiếu sai vùng. Một cách kiểm tra khác dùng hàm CORREL: hiệp phương sai bằng hệ số tương quan nhân hai độ lệch chuẩn. Tương quan giữa ngân hàng và tiêu dùng là 0,646, nên , khớp ô H2. Cách đọc hệ số tương quan được trình bày trong bài Hệ số tương quan là gì?
Với Microsoft 365 hoặc Excel 2021 trở lên (có mảng động), cả ma trận có thể tính bằng một công thức đặt ở ô góc trên trái vùng kết quả:
=MMULT(TRANSPOSE(B2:D13-B15:D15);B2:D13-B15:D15)/(ROWS(B2:D13)-1)
Công thức lấy ma trận độ lệch so với trung bình (12 × 3), nhân chuyển vị của nó với chính nó (3 × 12 nhân 12 × 3), rồi chia cho . Khi chạy thử trên Excel 2024 cho Windows (phiên bản 16.0), kết quả trùng 9 ô COVARIANCE.S đến mọi chữ số. Cách này tiện khi danh mục có nhiều tài sản, nhưng xử lý ô trống khác COVARIANCE.S (mục 9.3).

5. Bước 3: Tính lợi suất danh mục bằng SUMPRODUCT
Lợi suất danh mục là tổng tích tỷ trọng với lợi suất trung bình, tính bằng =SUMPRODUCT(B17:D17;B15:D15). Kết quả cho Quỹ YX:
Theo Microsoft, SUMPRODUCT yêu cầu các mảng có cùng kích thước, nếu không sẽ trả lỗi #VALUE!, và coi mọi ô không phải số là 0. Hai vùng B17:D17 và B15:D15 cùng là hàng 1 × 3 nên hợp lệ. Trước khi dùng kết quả, nên kiểm tra =SUM(B17:D17) bằng 100%.
6. Bước 4: Tính phương sai và độ lệch chuẩn danh mục bằng MMULT
Phương sai danh mục là tích ba ma trận: hàng tỷ trọng, ma trận hiệp phương sai và cột tỷ trọng. Đây là bước dễ sai nhất vì Excel yêu cầu đúng chiều của từng ma trận.
6.1. Công thức MMULT và TRANSPOSE
Ô J8 là =MMULT(MMULT(B17:D17;G2:I4);TRANSPOSE(B17:D17)), cho phương sai 0,000793 và độ lệch chuẩn danh mục 2,817%/tháng (ô J9 là =SQRT(J8)).
Microsoft quy định số cột của mảng thứ nhất trong MMULT phải bằng số hàng của mảng thứ hai. Công thức trên đi qua hai phép nhân:
MMULT(B17:D17;G2:I4): hàng 1 × 3 nhân ma trận 3 × 3, cho hàng 1 × 3 là 0,001278; 0,000538; −0,000036.- Nhân tiếp với
TRANSPOSE(B17:D17), cột 3 × 1, cho một số duy nhất: .
Tỷ trọng đặt theo hàng nên phải chuyển vị ở vế cuối. Nếu tỷ trọng đặt theo cột (ví dụ B20:B22), công thức đổi thành =MMULT(MMULT(TRANSPOSE(B20:B22);G2:I4);B20:B22). Trong Microsoft 365 và Excel 2021 trở lên, nhấn Enter là đủ; Excel bản cũ cần nhập dạng công thức mảng bằng Ctrl+Shift+Enter.
Một cách viết không cần MMULT là =SUMPRODUCT(TRANSPOSE(B17:D17)*B17:D17*G2:I4): tích TRANSPOSE(B17:D17)*B17:D17 tạo ma trận 3 × 3 các tích , nhân từng ô với ma trận hiệp phương sai rồi cộng lại. Kết quả cũng là 0,000793.
6.2. Kiểm tra bằng cách tách từng số hạng
Bảng dưới viết tổng kép ở mục 1 thành từng số hạng, đơn vị là phần trăm bình phương (%²) để dễ đọc; 0,000793 tương đương 7,934 %².
| Số hạng | Cách tính | Giá trị (%²) |
|---|---|---|
| Phương sai ngân hàng | 5,357 | |
| Phương sai tiêu dùng | 0,536 | |
| Phương sai trái phiếu | 0,004 | |
| Ngân hàng và tiêu dùng | 2,189 | |
| Ngân hàng và trái phiếu | −0,120 | |
| Tiêu dùng và trái phiếu | −0,032 | |
| Tổng | 7,934 |
Căn bậc hai của 7,934 là 2,817%, khớp với MMULT. Bảng cũng cho thấy số hạng hiệp phương sai giữa hai nhóm cổ phiếu (2,189) lớn gấp khoảng bốn lần số hạng phương sai của nhóm tiêu dùng (0,536).
6.3. Kiểm tra bằng chuỗi lợi suất danh mục
Cách kiểm tra thứ hai không cần ma trận: tính lợi suất danh mục từng tháng ở cột E bằng =SUMPRODUCT(B2:D2;$B$17:$D$17), kéo xuống đến hàng 13, rồi lấy =STDEV.S(E2:E13). Kết quả là 2,817%, đúng bằng ô J9.
Lưu ý: Hai cách chỉ cho cùng kết quả khi tỷ trọng được giữ cố định mỗi kỳ, tức danh mục được tái cân bằng về 50/30/20 đầu mỗi tháng. Nếu mua một lần rồi để tỷ trọng tự trôi theo giá, lợi suất thực tế của danh mục sẽ khác và công thức với tỷ trọng ban đầu chỉ là xấp xỉ.
7. Bước 5: Quy đổi lợi suất và rủi ro theo năm
Với dữ liệu tháng, lợi suất trung bình nhân 12 và độ lệch chuẩn nhân . Quỹ YX có lợi suất kỳ vọng khoảng 9,08%/năm (=J7*12) và độ lệch chuẩn khoảng 9,76%/năm (=J9*SQRT(12)).
Cách nhân này giả định lợi suất các tháng độc lập và có cùng phân phối: khi đó phương sai cộng dồn theo thời gian, nên phương sai năm bằng 12 lần phương sai tháng và độ lệch chuẩn năm bằng lần. Nếu lợi suất có tự tương quan, kết quả quy đổi sẽ lệch. Với dữ liệu ngày, hệ số là 252 và ; lý do và giả định đi kèm được phân tích trong bài Volatility là gì?
Nhân 12 là xấp xỉ theo lãi đơn. Nếu tính gộp lãi kép, . Hai con số trả lời hai câu hỏi khác nhau, nên khi so sánh nhiều danh mục cần dùng cùng một cách quy đổi.
8. Tài sản nào tạo ra nhiều rủi ro nhất?
Tài sản tạo ra nhiều rủi ro nhất được xác định bằng tỷ trọng đóng góp vào phương sai (risk contribution) của từng tài sản, tính từ chính kết quả trung gian của MMULT ở mục 6.1:
Trong Excel 365, một công thức trả về cả ba giá trị theo cột: =TRANSPOSE(B17:D17)*MMULT(G2:I4;TRANSPOSE(B17:D17))/J8. Với Quỹ YX:
| Thành phần | Tỷ trọng vốn | Đóng góp vào phương sai | |
|---|---|---|---|
| Cổ phiếu ngân hàng | 50% | 0,000639 | 80,6% |
| Cổ phiếu tiêu dùng | 30% | 0,000161 | 20,4% |
| Trái phiếu | 20% | −0,000007 | −0,9% |
| Tổng | 100% | 0,000793 | 100% |
Nhóm ngân hàng chiếm một nửa số vốn nhưng tạo ra khoảng 80% phương sai của danh mục, vì vừa biến động mạnh nhất vừa tương quan dương với nhóm tiêu dùng. Trái phiếu có đóng góp âm: tương quan âm với cả hai nhóm cổ phiếu nên phần hiệp phương sai (mang dấu âm) của nó lớn hơn phần phương sai riêng về độ lớn, và tăng nhẹ tỷ trọng trái phiếu (lấy từ các thành phần còn lại) sẽ làm giảm rủi ro danh mục.

Phân tách này cho biết tỷ trọng vốn không phản ánh phân bổ rủi ro. Đây cũng là bước chuẩn bị cho việc tìm tỷ trọng có rủi ro thấp nhất bằng Solver, được trình bày cùng khái niệm danh mục hiệu quả trong bài Đường biên hiệu quả (Markowitz) là gì?
9. Lỗi thường gặp khi tính rủi ro danh mục trong Excel
Phần lớn lỗi dưới đây không làm Excel báo lỗi mà cho ra một con số trông hợp lý. Kết quả chạy thử nêu trong mục này dùng Excel 2024 cho Windows (phiên bản 16.0).
9.1. Lấy bình quân gia quyền độ lệch chuẩn
=SUMPRODUCT(B17:D17;B16:D16) cho 3,109%/tháng, cao hơn kết quả đúng 2,817% khoảng 10%. Bình quân gia quyền độ lệch chuẩn chỉ đúng khi mọi cặp tài sản tương quan hoàn toàn (hệ số tương quan bằng 1). Độ lệch chuẩn danh mục thấp hơn bình quân gia quyền 9,4%; đó là lợi ích đa dạng hóa mà cách tính này bỏ qua.
9.2. Trộn hàm mẫu và hàm tổng thể
Công cụ Covariance trong Analysis ToolPak (lệnh Data Analysis trên thẻ Data) tính hiệp phương sai tổng thể như hàm COVARIANCE.P, chia cho chứ không phải , theo tài liệu của Microsoft. Dùng ma trận này cho Quỹ YX được độ lệch chuẩn danh mục 2,697%/tháng, thấp hơn 4,3% so với 2,817%. Nếu đem 2,697% so với độ lệch chuẩn từng tài sản tính bằng STDEV.S, lợi ích đa dạng hóa có vẻ là 13,2% thay vì 9,4%. Với 12 quan sát, tỷ số giữa hai kết quả là ; dữ liệu càng ngắn, chênh lệch càng lớn. Cách tránh: dùng .S cho mọi hàm, hoặc .P cho mọi hàm.
9.3. Ô trống trong chuỗi lợi suất
Theo Microsoft, COVARIANCE.S, CORREL và STDEV.S bỏ qua ô trống, còn ô có giá trị 0 vẫn được tính. Khi chạy thử, xóa lợi suất tháng 4 của nhóm tiêu dùng (−4,2%) thì COVARIANCE.S không báo lỗi mà bỏ luôn cả cặp số liệu tháng đó: hiệp phương sai ngân hàng và tiêu dùng giảm từ 0,000730 xuống 0,000449, và độ lệch chuẩn danh mục giảm từ 2,817% xuống 2,637%. Tháng bị mất lại là tháng cả hai nhóm cùng giảm mạnh, nên rủi ro bị đánh giá thấp. Công thức MMULT một bước ở mục 4 thì coi ô trống là 0 khi trừ trung bình, nên cho kết quả khác nữa (0,000472).
Lưu ý: Mỗi ô hiệp phương sai có thể được tính trên một tập tháng khác nhau khi dữ liệu có ô trống, và ma trận ghép từ các ô đó có thể không còn hợp lệ về mặt toán học (phương sai danh mục tính ra có thể âm với một số tỷ trọng). Trước khi tính, nên kiểm tra mọi cột có cùng số quan sát bằng =COUNT(B2:B13).9.4. Sai chiều vector trong MMULT
Quên TRANSPOSE ở vế cuối, như =MMULT(MMULT(B17:D17;G2:I4);B17:D17), là nhân hàng 1 × 3 với hàng 1 × 3. Số cột của mảng thứ nhất (3) khác số hàng của mảng thứ hai (1), nên Excel trả lỗi #VALUE!. Lỗi này dễ thấy; trường hợp nguy hiểm hơn là thứ tự tài sản trong vector tỷ trọng khác thứ tự hàng và cột của ma trận hiệp phương sai, Excel vẫn tính bình thường nhưng gán sai tỷ trọng cho tài sản.
9.5. Nhân độ lệch chuẩn với 12
Nhân 2,817% với 12 cho 33,80%/năm, gấp khoảng 3,5 lần con số đúng 9,76%. Chỉ lợi suất trung bình và phương sai được nhân với số kỳ; độ lệch chuẩn nhân với căn bậc hai của số kỳ.
10. Hạn chế của cách tính
Kết quả của bảng tính chỉ đáng tin bằng dữ liệu đầu vào, và ba giới hạn sau thường quan trọng hơn mọi chi tiết công thức.
- Mẫu ngắn cho ước lượng kém chính xác. Với 12 quan sát, một tháng bất thường có thể thay đổi đáng kể hiệp phương sai, như ví dụ ở mục 9.3. Dữ liệu tháng thường cần nhiều năm để ước lượng tương quan ổn định hơn.
- Ma trận lịch sử không phải ma trận tương lai. Tương quan thay đổi theo trạng thái thị trường và có xu hướng tăng khi thị trường giảm mạnh, nên độ lệch chuẩn danh mục tính từ giai đoạn bình thường có thể thấp hơn mức xảy ra trong giai đoạn căng thẳng.
- Độ lệch chuẩn không mô tả đuôi phân phối. Hai danh mục cùng độ lệch chuẩn có thể có mức lỗ trong tháng xấu nhất rất khác nhau. Khi cần đo tổn thất ở đuôi, người phân tích thường bổ sung VaR, mức sụt giảm lớn nhất hoặc kiểm tra theo kịch bản.
11. Câu hỏi thường gặp
Có cần cài Analysis ToolPak để tính rủi ro danh mục không?
Không. Mọi bước trong bài chỉ dùng hàm có sẵn: AVERAGE, STDEV.S, COVARIANCE.S, CORREL, SUMPRODUCT, MMULT, TRANSPOSE và SQRT. ToolPak tiện để lập nhanh ma trận cho nhiều tài sản, nhưng kết quả được ghi ra dưới dạng giá trị chứ không phải công thức, nên phải chạy lại công cụ khi dữ liệu đổi, và theo quy ước tổng thể như mục 9.2.
Danh mục có 2 tài sản thì có cần ma trận không?
Không bắt buộc. Với hai tài sản, có thể viết thẳng =SQRT(w1^2*s1^2+w2^2*s2^2+2*w1*w2*cov12), thay các tên bằng ô tương ứng. Từ ba tài sản trở lên, số hạng tăng theo bình phương số tài sản, nên dùng MMULT ít sai hơn.
Kết luận
Tính rủi ro danh mục trong Excel gồm bốn bước: tính lợi suất trung bình và độ lệch chuẩn từng tài sản, lập ma trận hiệp phương sai bằng COVARIANCE.S, tính lợi suất danh mục bằng SUMPRODUCT, và tính phương sai danh mục bằng MMULT với vector tỷ trọng đúng chiều. Kết quả nên được kiểm tra bằng chuỗi lợi suất danh mục và bằng phân tách đóng góp rủi ro. Các lỗi đáng chú ý nhất là trộn hàm mẫu với hàm tổng thể, để ô trống trong dữ liệu và quy đổi theo năm sai cách, vì chúng không làm Excel báo lỗi.
Bài viết mang tính giáo dục, không phải khuyến nghị đầu tư. Quỹ YX và mọi số liệu ví dụ trong bài là giả định.
Nguồn tham khảo
- Microsoft Support, AVERAGE function: https://support.microsoft.com/en-us/office/average-function-047bac88-d466-426c-a32b-8f33eb960cf6
- Microsoft Support, STDEV.S function: https://support.microsoft.com/en-us/office/stdev-s-function-7d69cf97-0c1f-4acf-be27-f3e83904cc23
- Microsoft Support, COVARIANCE.S function: https://support.microsoft.com/en-us/office/covariance-s-function-0a539b74-7371-42aa-a18f-1f5320314977
- Microsoft Support, COVARIANCE.P function: https://support.microsoft.com/en-us/office/covariance-p-function-6f0e1e6d-956d-4e4b-9943-cfef0bf9edfc
- Microsoft Support, CORREL function: https://support.microsoft.com/en-us/office/correl-function-995dcef7-0c0a-4bed-a3fb-239d7b68ca92
- Microsoft Support, SUMPRODUCT function: https://support.microsoft.com/en-us/office/sumproduct-function-16753e75-9f68-4874-94ac-4d2145a2fd2e
- Microsoft Support, MMULT function: https://support.microsoft.com/en-us/office/mmult-function-40593ed7-a3cd-4b6b-b9a3-e4ad3c7245eb
- Microsoft Support, TRANSPOSE function: https://support.microsoft.com/en-us/office/transpose-function-ed039415-ed8a-4a81-93e9-4b6dfac76027
- Microsoft Support, Use the Analysis ToolPak to perform complex data analysis: https://support.microsoft.com/en-us/office/use-the-analysis-toolpak-to-perform-complex-data-analysis-6c67ccf0-f4a9-487c-8dec-bdb5a2cefab6
- CFA Institute, "Portfolio Risk and Return: Part I" (Refresher Reading 2026): https://www.cfainstitute.org/insights/professional-learning/refresher-readings/2026/portfolio-risk-return-part-1
Đọc tiếp: Đường biên hiệu quả (Markowitz) là gì? Cách xác định, ví dụ và hạn chế