Ghi nhật ký bằng Excel: công thức tính lãi/lỗ sau phí và thuế
Nội dung bài5 phần
Nhật ký giao dịch Excel chỉ cần một bảng tính với 14 cột, trong đó 10 cột bạn nhập tay và 4 cột chạy bằng công thức. Làm vậy bạn không phải tính lãi lỗ bằng tay sau mỗi lệnh, và không sai số học. Các công thức dưới đây dùng được cả trong Excel lẫn Google Sheets.
Bài này đi từ khung bảng, đến bốn công thức tính kết quả, rồi đến bảng thống kê. Ví dụ dùng 20 lệnh giả định với mã minh họa. Phí môi giới giả định là 0,15% mỗi chiều. Thuế thu nhập cá nhân là 0,1% trên giá trị bán theo khoản 2 Điều 13 Luật 109/2025/QH15, có hiệu lực từ 01/07/2026. Dữ liệu đến 02/10/2026.
Khung bảng nhật ký giao dịch Excel

Tạo hai trang tính. Trang thứ nhất tên NhatKy chứa các lệnh. Trang thứ hai tên Thongso chứa hai tham số: ô B1 là phí mỗi chiều (0,15%), ô B2 là thuế bán (0,1%). Đặt tham số riêng để khi công ty chứng khoán đổi biểu phí, bạn chỉ sửa một ô.
Trên trang NhatKy, hàng 1 là tiêu đề và dữ liệu bắt đầu từ hàng 2. Các cột như sau.
Cột | Nội dung | Loại |
|---|---|---|
A | Mã | Nhập tay |
B | Ngày mua | Nhập tay |
C | Giá mua | Nhập tay |
D | Khối lượng | Nhập tay |
E | Giá dừng lỗ | Nhập tay |
F | Lý do vào | Nhập tay, danh sách chọn |
G | Ngày bán | Nhập tay |
H | Giá bán | Nhập tay |
I | Lý do thoát | Nhập tay, danh sách chọn |
J | Phí và thuế | Công thức |
K | Lãi lỗ | Công thức |
L | Số ngày giữ | Công thức |
M | Bội số R | Công thức |
N | Đúng kế hoạch | Nhập tay, Có hoặc Không |
Cột F, I và N nên dùng danh sách chọn. Trong Excel, vào Dữ liệu, chọn Xác thực dữ liệu, rồi chọn Danh sách. Danh sách cố định giúp bạn thống kê chính xác về sau.
Bốn công thức lãi lỗ sau phí thuế

Bốn công thức nằm ở hàng 2 và được kéo xuống các hàng dưới. Giả sử dấu phân cách đối số trên máy bạn là dấu phẩy. Nếu máy dùng dấu chấm phẩy, hãy thay tương ứng.
Phí và thuế ở ô J2 gồm phí mua, phí bán và thuế bán:
Trong đó: KL là khối lượng, G là giá mua hoặc giá bán, p là phí môi giới mỗi chiều (ô B1), t là thuế bán (ô B2).
=IF(H2="","",D2*C2*Thongso!$B$1+D2*H2*Thongso!$B$1+D2*H2*Thongso!$B$2)
Lãi lỗ ở ô K2 bằng chênh lệch giá nhân khối lượng, trừ phí và thuế:
=IF(H2="","",D2*(H2-C2)-J2)
Số ngày giữ ở ô L2 bằng ngày bán trừ ngày mua:
=IF(G2="","",G2-B2)
Bội số R ở ô M2 bằng lãi lỗ chia cho số tiền rủi ro ban đầu:
Trong đó: mẫu số là số tiền rủi ro ban đầu, tức khoản mất nếu lệnh chạm giá dừng lỗ.
=IF(H2="","",K2/(D2*(C2-E2)))
Hàm IF kiểm tra giá bán. Nếu bạn chưa bán, ô giá bán trống và các công thức trả về chuỗi rỗng. Nhờ vậy lệnh đang mở không làm sai bảng thống kê.
Kiểm tra bằng lệnh đầu tiên. Mua 1.000 cổ phiếu ở 25.000 đồng, bán ở 26.500 đồng, giá dừng 24.000 đồng. Phí mua là 37.500 đồng. Phí bán là 39.750 đồng. Thuế là 26.500 đồng. Phí và thuế là 103.750 đồng. Lãi lỗ là 1.500.000 trừ 103.750, bằng 1.396.250 đồng. Số ngày giữ là 14. Số tiền rủi ro là 1.000 nhân 1.000, bằng 1.000.000 đồng, nên bội số R là 1,40.
Thống kê nhật ký giao dịch Excel
Thêm trang tính thứ ba tên ThongKe. Các công thức dưới đây lấy dữ liệu từ NhatKy, giả sử tối đa 200 lệnh.
Chỉ tiêu | Công thức |
|---|---|
Số lệnh |
|
Số lệnh lãi |
|
Tỷ lệ lệnh lãi | số lệnh lãi chia số lệnh |
Tổng lãi lỗ |
|
Lãi bình quân mỗi lệnh lãi |
|
Lỗ bình quân mỗi lệnh lỗ |
|
Tỷ lệ lãi trên lỗ | lãi bình quân chia giá trị tuyệt đối của lỗ bình quân |
Kỳ vọng mỗi lệnh |
|
Tổng phí và thuế |
|
Chuỗi lỗ liên tiếp cần một cột phụ. Ở ô O2 của trang NhatKy nhập =IF(K2<0,N(O1)+1,0) rồi kéo xuống. Hàm N biến tiêu đề chữ ở O1 thành 0. Sau đó =MAX(NhatKy!O2:O200) cho chuỗi lỗ dài nhất.
Muốn thống kê theo lý do vào, dùng SUMIFS và COUNTIFS. Ví dụ tổng lãi lỗ của các lệnh bắt đáy là =SUMIFS(NhatKy!K2:K200,NhatKy!F2:F200,"Bắt đáy").
Mẹo giữ bảng nhật ký gọn và đúng
Có bốn mẹo nhỏ giúp bảng bền hơn. Thứ nhất, biến vùng dữ liệu thành Table bằng phím Ctrl+T trong Excel. Công thức sẽ tự mở rộng khi bạn thêm hàng mới. Thứ hai, nhập ngày đúng định dạng ngày, không gõ thành chữ. Nếu không, phép trừ ngày ở cột L sẽ báo lỗi. Thứ ba, cố định hàng tiêu đề để luôn nhìn thấy tên cột. Thứ tư, sao lưu tệp định kỳ lên dịch vụ lưu trữ đám mây, vì mất nhật ký là mất cả dữ liệu thói quen nhiều tháng.
Kết quả thống kê 20 lệnh giả định
Áp dụng bảng trên cho 20 lệnh giả định trong khoảng tháng 1 đến tháng 9/2026, ta được kết quả sau.
Chỉ tiêu | Kết quả |
|---|---|
Số lệnh | 20 |
Số lệnh lãi | 9, tức 45% |
Tổng lãi lỗ | âm 108.315 đồng |
Lãi bình quân mỗi lệnh lãi | 1.853.692 đồng |
Lỗ bình quân mỗi lệnh lỗ | âm 1.526.504 đồng |
Tỷ lệ lãi trên lỗ | 1,21 |
Kỳ vọng mỗi lệnh | âm 5.416 đồng |
Tổng phí và thuế | 2.058.315 đồng |
Chuỗi lỗ liên tiếp dài nhất | 2 lệnh |
Con số đáng chú ý là kỳ vọng mỗi lệnh gần bằng 0, và tổng phí cùng thuế lớn hơn chênh lệch giá mua bán. Bài phân tích nhật ký tiếp theo đi sâu vào việc tìm điểm yếu từ chính bảng này.
Nếu muốn có dữ liệu giá để thực hành tính lãi lỗ, bài học tải file Excel lịch sử giá cổ phiếu Việt Nam sẽ giúp. Bài nhật ký giao dịch ngày 25/12/2023 cho thấy một nhật ký thực tế.
Nội dung để tham khảo, không phải khuyến nghị đầu tư.
Duyệt bởi Ban biên tập HODL.VN · Cập nhật 02/10/2026
Black Friday · đến hết 29/11
Gói năm Loyalty giảm 50%
Beginner giảm 30%. Cả hai được tặng thêm 1 tháng dùng khi mua gói năm trên web.