Tính lợi tức chứng khoán trả lãi khi đáo hạn thủ công trong Excel thường mất 10-15 phút cho mỗi phép tính. Bạn phải tính số ngày giữa các mốc thời gian, nhân với lãi suất, chia cho số ngày trong năm, rồi điều chỉnh theo giá mua. Hàm YIELDMAT làm tất cả trong một công thức duy nhất.
Hàm YIELDMAT là gì và khi nào cần dùng
Hàm YIELDMAT tính toán lợi tức hàng năm của một chứng khoán trả lãi khi đáo hạn, dựa trên giá mua thực tế. Khác với hàm YIELD dành cho trái phiếu trả lãi định kỳ, YIELDMAT được thiết kế cho các chứng khoán trả lãi một lần khi đến hạn.
Bạn cần hàm này khi phân tích các loại chứng khoán như tín phiếu kho bạc, trái phiếu chiết khấu ngắn hạn, hoặc giấy tờ có giá trả lãi khi đáo hạn. Hàm này thuộc nhóm hàm tài chính và có sẵn từ Excel 2007 trở đi.
Cú pháp và giải thích từng đối số
Công thức đầy đủ của hàm YIELDMAT có dạng:
=YIELDMAT(ngày_thanh_toán, ngày_đáo_hạn, ngày_phát_hành, lãi_suất, giá, [cơ_sở_ngày])Các đối số bắt buộc:
ngày_thanh_toán: Ngày bạn mua chứng khoán hoặc ngày giao dịch được thực hiện. Đây là ngày sau ngày phát hành khi chứng khoán được chuyển giao cho người mua. Phải nhập bằng hàm DATE hoặc tham chiếu ô chứa ngày.
ngày_đáo_hạn: Ngày chứng khoán hết hạn và trả lãi. Ngày này phải sau ngày thanh toán, nếu không hàm trả về lỗi NUM.
ngày_phát_hành: Ngày chứng khoán được phát hành lần đầu tiên. Ngày này phải trước ngày thanh toán.
lãi_suất: Lãi suất hàng năm của chứng khoán tính theo phần trăm. Ví dụ lãi suất 6% thì nhập 0.06 hoặc 6%. Nếu nhập số âm, hàm trả về lỗi NUM.
giá: Giá mua chứng khoán tính trên mỗi 100 đơn vị mệnh giá. Nếu bạn mua với giá 95 trên mỗi 100 đơn vị thì nhập 95. Giá phải lớn hơn 0.
Đối số tùy chọn:
cơ_sở_ngày: Qui ước tính số ngày trong năm, mặc định là 0. Có 5 giá trị:
- 0 hoặc bỏ qua: 30 ngày mỗi tháng, 360 ngày mỗi năm theo chuẩn Mỹ
- 1: Số ngày thực tế trong tháng và năm
- 2: Số ngày thực tế trong tháng, 360 ngày mỗi năm
- 3: Số ngày thực tế trong tháng và năm
- 4: 30 ngày mỗi tháng, 360 ngày mỗi năm theo chuẩn châu Âu
Ví dụ thực tế với số liệu cụ thể
Giả sử bạn mua tín phiếu kho bạc với các thông tin:
- Ngày phát hành: 15/03/2025
- Ngày thanh toán (ngày mua): 15/06/2025
- Ngày đáo hạn: 15/03/2026
- Lãi suất hàng năm: 6.5%
- Giá mua: 97.5 trên mỗi 100 đơn vị
Cách nhập công thức:
Tại ô cần tính, nhập:
=YIELDMAT(DATE(2025,6,15), DATE(2026,3,15), DATE(2025,3,15), 6.5%, 97.5, 0)Kết quả trả về: 0.0893 hoặc 8.93% – đây là lợi tức hàng năm thực tế của khoản đầu tư này.
Cách hiểu kết quả: Mặc dù lãi suất công bố là 6.5%, nhưng do bạn mua với giá 97.5 thấp hơn mệnh giá 100, lợi tức thực tế tăng lên 8.93%. Chênh lệch 2.5 điểm giá cộng với lãi suất 6.5% trong khoảng 9 tháng tạo ra lợi tức hàng năm này.
Sử dụng tham chiếu ô:
Cách tốt hơn là đặt các giá trị vào các ô riêng:
- A1: 15/06/2025 (ngày thanh toán)
- A2: 15/03/2026 (ngày đáo hạn)
- A3: 15/03/2025 (ngày phát hành)
- A4: 0.065 (lãi suất)
- A5: 97.5 (giá)
Công thức trở thành:
=YIELDMAT(A1, A2, A3, A4, A5, 0)Cách này giúp thay đổi số liệu dễ dàng và kiểm tra các kịch bản khác nhau.
Lỗi thường gặp và cách khắc phục
Lỗi NUM xuất hiện nhiều nhất:
Lỗi NUM có nghĩa là một trong các đối số không hợp lệ. Kiểm tra lại:
- Ngày đáo hạn phải sau ngày thanh toán
- Ngày thanh toán phải sau ngày phát hành
- Lãi suất không được âm
- Giá phải lớn hơn 0
- Cơ sở ngày phải từ 0 đến 4
Lỗi VALUE khi nhập ngày:
Nếu bạn nhập ngày dạng văn bản như “15/06/2025” trực tiếp vào công thức, Excel có thể không nhận diện. Luôn dùng hàm DATE hoặc tham chiếu ô đã định dạng Date.
Thay vì:
=YIELDMAT("15/06/2025", "15/03/2026", ...)Dùng:
=YIELDMAT(DATE(2025,6,15), DATE(2026,3,15), ...)Kết quả hiển thị dạng số thập phân thay vì phần trăm:
Hàm YIELDMAT trả về kết quả dạng số thập phân. Nếu thấy 0.0893, bạn cần định dạng ô thành phần trăm. Nhấn chuột phải ô kết quả, chọn Format Cells, chọn Percentage với 2 chữ số thập phân.
Số ngày trong năm ảnh hưởng đến kết quả:
Đối số cơ sở ngày thay đổi cách Excel tính số ngày, ảnh hưởng trực tiếp đến lợi tức. Với chứng khoán Việt Nam, thường dùng cơ sở 1 (số ngày thực tế). Với chứng khoán Mỹ, dùng cơ sở 0 hoặc 30/360.
So sánh với các hàm tài chính khác
YIELDMAT so với YIELD:
YIELD dành cho trái phiếu trả lãi định kỳ theo quý hoặc nửa năm. YIELDMAT dành cho chứng khoán trả lãi một lần khi đáo hạn. Nếu chứng khoán của bạn trả lãi 6 tháng một lần, dùng YIELD. Nếu chỉ trả khi đáo hạn, dùng YIELDMAT.
YIELDMAT so với YIELDDISC:
YIELDDISC tính lợi tức cho chứng khoán chiết khấu không trả lãi như tín phiếu kho bạc chiết khấu. YIELDMAT dành cho chứng khoán có trả lãi khi đáo hạn. Nếu chứng khoán của bạn không có lãi suất công bố mà chỉ bán dưới mệnh giá, dùng YIELDDISC.
Mẹo sử dụng hàm YIELDMAT hiệu quả
Tạo bảng so sánh nhiều kịch bản:
Đặt các mức giá khác nhau ở cột A từ 95 đến 100, dùng YIELDMAT với tham chiếu tuyệt đối cho các đối số cố định và tham chiếu tương đối cho giá. Kéo công thức xuống để xem lợi tức thay đổi như thế nào theo giá mua.
Kết hợp với hàm IF để đánh giá:
=IF(YIELDMAT(A1,A2,A3,A4,A5,0) > 0.08, "Đầu tư tốt", "Xem xét lại")Công thức này tự động đánh giá liệu lợi tức có đạt ngưỡng 8% hay không.
Sử dụng Named Ranges cho rõ ràng:
Thay vì A1, A2, A3, đặt tên các ô là NgayThanhToan, NgayDaoHan, NgayPhatHanh. Công thức trở thành:
=YIELDMAT(NgayThanhToan, NgayDaoHan, NgayPhatHanh, LaiSuat, Gia, 0)Cách này giúp công thức dễ đọc và bảo trì.
Phiên bản Excel tương thích và lưu ý
Hàm YIELDMAT có sẵn trong Excel 2007 trở lên bao gồm Excel 2010, 2013, 2016, 2019, 2021, và Excel 365. Hàm này cũng hoạt động trên Excel for Mac và Excel Online.
Nếu làm việc với file Excel cần mở ở phiên bản cũ hơn 2007, hàm YIELDMAT sẽ trả về lỗi NAME vì phiên bản đó không hỗ trợ. Trong trường hợp này, bạn cần tính toán thủ công hoặc nâng cấp Excel.
Khi chia sẻ file với người khác, đảm bảo họ dùng Excel 2007 trở lên. Nếu không chắc, có thể thêm cột ghi chú giải thích cách tính hoặc cung cấp công thức thay thế cho phiên bản cũ.
