Ghi nhật ký bằng Excel: công thức tính lãi/lỗ sau phí và thuế

Triều Tô5 phút đọc
Nội dung bài5 phần
  1. Khung bảng nhật ký giao dịch Excel
  2. Bốn công thức lãi lỗ sau phí thuế
  3. Thống kê nhật ký giao dịch Excel
  4. Mẹo giữ bảng nhật ký gọn và đúng
  5. Kết quả thống kê 20 lệnh giả định

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

Nhật ký giao dịch Excel: 10 cột nhập tay và 4 cột công thức

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 tính kết quả của một lệnh trong nhật ký giao dịch Excel

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:

Phí và thuế=KL×Gmua×p+KL×Gbán×p+KL×Gbán×t\text{Phí và thuế} = \text{KL} \times G_{\text{mua}} \times p + \text{KL} \times G_{\text{bán}} \times p + \text{KL} \times G_{\text{bán}} \times t

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ế:

Lãi lỗ=KL×(Gbán−Gmua)−Phí và thuế\text{Lãi lỗ} = \text{KL} \times (G_{\text{bán}} - G_{\text{mua}}) - \text{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:

R=Lãi lỗKL×(Gmua−Gdừng)R = \dfrac{\text{Lãi lỗ}}{\text{KL} \times (G_{\text{mua}} - G_{\text{dừng}})}

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

=COUNT(NhatKy!K2:K200)

Số lệnh lãi

=COUNTIF(NhatKy!K2:K200,">0")

Tỷ lệ lệnh lãi

số lệnh lãi chia số lệnh

Tổng lãi lỗ

=SUM(NhatKy!K2:K200)

Lãi bình quân mỗi lệnh lãi

=AVERAGEIF(NhatKy!K2:K200,">0")

Lỗ bình quân mỗi lệnh lỗ

=AVERAGEIF(NhatKy!K2:K200,"<0")

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

=AVERAGE(NhatKy!K2:K200)

Tổng phí và thuế

=SUM(NhatKy!J2:J200)

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.

Watchlist

Đang tải…