Hai năm trước khi bắt đầu làm phân tích đầu tư, tôi tính lợi nhuận trái phiếu bằng công thức thủ công. Mỗi khoản đầu tư chiết khấu tốn 10 phút để tính toán, dễ nhầm lẫn với các loại basis khác nhau. Sau khi phát hiện hàm RECEIVED trong Excel, tôi tính 50 khoản đầu tư trong 15 phút với độ chính xác tuyệt đối.
Cách tính thủ công phức tạp và dễ sai
Trước khi biết hàm RECEIVED, tôi dùng công thức dài để tính số tiền nhận được khi đáo hạn. Công thức cơ bản là: Số tiền nhận được = Đầu tư / (1 – (Chiết khấu × Số ngày / Số ngày trong năm)).
Vấn đề lớn nhất là tính số ngày giữa hai mốc thời gian. Excel lưu ngày tháng dưới dạng số serial, nên tôi phải trừ ngày đáo hạn cho ngày thanh toán, sau đó tính basis. Có 5 loại basis khác nhau (30/360, thực tế/360, thực tế/365, thực tế/thực tế, 30E/360) và mỗi loại cho kết quả khác nhau.
Khi phân tích 20 trái phiếu khác nhau trong báo cáo, tôi mắc lỗi ít nhất 3 lần mỗi tuần. Sai basis, sai số ngày, hoặc quên chia cho 100 khi nhập tỷ lệ chiết khấu dạng phần trăm.
Hàm RECEIVED xử lý mọi thứ tự động
Hàm RECEIVED tính số tiền nhận được khi đáo hạn cho chứng khoán đầu tư với giá chiết khấu. Excel tự động xử lý tính toán ngày, basis, và áp dụng công thức tài chính chuẩn.
Cú pháp cơ bản:
=RECEIVED(settlement, maturity, investment, discount, [basis])Ý nghĩa các tham số:
Settlement: Ngày thanh toán chứng khoán (ngày bạn mua)
Maturity: Ngày đáo hạn (ngày nhận tiền)
Investment: Số tiền đầu tư ban đầu
Discount: Tỷ lệ chiết khấu hàng năm (nhập 0.045 cho 4.5%)
Basis: Loại tính ngày (0 = 30/360, 1 = thực tế/thực tế, 2 = thực tế/360, 3 = thực tế/365, 4 = 30E/360)
Tham số basis là tùy chọn, mặc định là 0 nếu bỏ trống.
Ví dụ thực tế với trái phiếu 5 năm
Giả sử tôi mua trái phiếu ngày 15 tháng 3 năm 2024 với giá 95 triệu đồng. Ngày đáo hạn là 15 tháng 3 năm 2029. Tỷ lệ chiết khấu là 4.25% hàng năm.
Cách nhập dữ liệu:
Ô A2: Nhập ngày thanh toán 15/03/2024
Ô B2: Nhập ngày đáo hạn 15/03/2029
Ô C2: Nhập số tiền đầu tư 95000000
Ô D2: Nhập tỷ lệ chiết khấu 0.0425
Ô E2: Nhập công thức =RECEIVED(A2,B2,C2,D2,0)
Kết quả hiển thị 115.840.278 đồng. Đây là số tiền tôi nhận được sau 5 năm, tương đương lợi nhuận 21.94%.
Lưu ý quan trọng:
- Nhập ngày trực tiếp vào ô, không nhập dưới dạng text
- Tỷ lệ chiết khấu phải ở dạng thập phân (4.25% = 0.0425)
- Nếu nhập 4.25 thay vì 0.0425, kết quả sẽ sai hoàn toàn
Các loại basis ảnh hưởng đến kết quả
Tham số basis quyết định cách Excel tính số ngày trong năm. Sự khác biệt này quan trọng với các khoản đầu tư dài hạn.
Basis 0 (30/360 Mỹ): Mỗi tháng tính 30 ngày, mỗi năm 360 ngày. Đây là chuẩn chung cho trái phiếu doanh nghiệp Mỹ.
Basis 1 (Thực tế/thực tế): Tính số ngày thực giữa hai mốc, chia cho số ngày thực trong năm. Năm nhuận có 366 ngày, năm thường 365 ngày.
Basis 2 (Thực tế/360): Số ngày thực chia cho 360. Dùng cho các công cụ tài chính ngắn hạn.
Basis 3 (Thực tế/365): Số ngày thực chia cho 365. Không phân biệt năm nhuận.
Basis 4 (30E/360 Châu Âu): Tương tự 30/360 nhưng xử lý ngày 31 khác.
Với cùng một khoản đầu tư 100 triệu, chiết khấu 5%, kỳ hạn 2 năm, kết quả thay đổi từ 110.2 triệu (basis 0) đến 110.5 triệu (basis 1). Chênh lệch 300 nghìn cho một khoản đầu tư, nhân với 50 khoản trong danh mục thì chênh 15 triệu.
Tránh lỗi thường gặp
Sau 2 năm dùng hàm RECEIVED hàng ngày, tôi gặp 4 lỗi phổ biến và cách fix.
Lỗi #VALUE! (Giá trị không hợp lệ):
Xuất hiện khi ngày thanh toán hoặc ngày đáo hạn không phải định dạng ngày hợp lệ. Excel lưu ngày dưới dạng số serial bắt đầu từ 01/01/1900 = 1.
Cách fix: Nhập ngày trực tiếp vào ô, hoặc dùng hàm DATE. Ví dụ =DATE(2024,3,15) thay vì text "15/3/2024".
Lỗi #NUM! (Số không hợp lệ):
Có 3 nguyên nhân:
- Số tiền đầu tư nhỏ hơn hoặc bằng 0
- Tỷ lệ chiết khấu nhỏ hơn hoặc bằng 0
- Ngày thanh toán lớn hơn hoặc bằng ngày đáo hạn
Cách fix: Kiểm tra investment > 0, discount > 0, và settlement < maturity. Nếu nhập ngược settlement với maturity, đổi vị trí hai ô.
Lỗi Basis ngoài phạm vi:
Nếu nhập basis là 5, 6, hoặc số âm, Excel trả về #NUM!. Chỉ chấp nhận 0, 1, 2, 3, hoặc 4.
Kết quả âm không hợp lý:
Nếu tỷ lệ chiết khấu quá cao (ví dụ nhập 45 thay vì 0.45), công thức trả về số âm. Luôn kiểm tra discount ở dạng thập phân, không phải phần trăm.
So sánh với tính thủ công
Để thấy rõ hiệu quả, tôi so sánh thời gian tính 20 khoản đầu tư trái phiếu.
Cách thủ công:
- Tính số ngày giữa settlement và maturity: 2 phút
- Xác định loại basis và số ngày trong năm: 1 phút
- Áp dụng công thức chiết khấu: 3 phút
- Kiểm tra lại tính toán: 2 phút
- Tổng: 8 phút cho 1 khoản, 160 phút cho 20 khoản
Dùng hàm RECEIVED:
- Nhập 4 ô dữ liệu: 30 giây
- Copy công thức xuống 20 dòng: 10 giây
- Tổng: 13 phút cho 20 khoản
Tiết kiệm 147 phút (2 giờ 27 phút) mỗi lần phân tích. Trong 1 tháng với 4 báo cáo, tiết kiệm gần 10 giờ.
Ứng dụng trong phân tích danh mục đầu tư
Hàm RECEIVED đặc biệt hữu ích khi so sánh nhiều khoản đầu tư với kỳ hạn và tỷ lệ chiết khấu khác nhau.
Thiết lập bảng so sánh:
Cột A: Tên trái phiếu
Cột B: Ngày mua
Cột C: Ngày đáo hạn
Cột D: Số tiền đầu tư
Cột E: Tỷ lệ chiết khấu
Cột F: Công thức =RECEIVED(B2,C2,D2,E2,1)
Cột G: Lợi nhuận =(F2-D2)/D2
Cột H: Lợi nhuận hàng năm =G2/(YEARFRAC(B2,C2,1))
Bảng này tự động cập nhật khi tôi thay đổi tỷ lệ chiết khấu hoặc thêm khoản đầu tư mới. Sắp xếp theo cột H để xem khoản nào sinh lời cao nhất theo năm.
Kết hợp với các hàm tài chính khác
RECEIVED hoạt động tốt với hàm DISC để tính ngược lại tỷ lệ chiết khấu khi biết giá mua và giá bán.
Ví dụ: Mua trái phiếu giá 93 triệu ngày 01/01/2024, bán giá 105 triệu ngày 01/01/2027. Tính tỷ lệ chiết khấu:
=DISC(DATE(2024,1,1),DATE(2027,1,1),93000000/105000000,105000000,1)
Sau đó dùng RECEIVED để verify số tiền nhận được với tỷ lệ vừa tính.
Hàm PV (Present Value) tính ngược lại, cho biết số tiền cần đầu tư hôm nay để nhận số tiền nhất định trong tương lai. Kết hợp PV với RECEIVED để lập kế hoạch đầu tư đạt mục tiêu cụ thể.
Lưu ý về phiên bản Excel
Hàm RECEIVED có sẵn trong tất cả phiên bản Excel từ 2007 trở đi. Các phiên bản cũ hơn (Excel 2003, 2000) không hỗ trợ.
Kiểm tra tính năng: Mở file Excel mới, nhập =RECEIVED( và nhấn Ctrl+A. Nếu hộp thoại Function Arguments xuất hiện, phiên bản của bạn hỗ trợ hàm này.
Excel cho web (Office 365 online) hỗ trợ đầy đủ hàm RECEIVED. Google Sheets không có hàm tương đương, cần viết công thức thủ công.
Các trường hợp không dùng RECEIVED
Hàm RECEIVED chỉ phù hợp với chứng khoán chiết khấu thuần túy không trả lãi định kỳ. Nếu trái phiếu trả coupon (lãi suất định kỳ), dùng hàm PRICE hoặc PRICEDISC thay thế.
Dấu hiệu nhận biết:
- Trái phiếu không trả lãi suất trong thời gian nắm giữ → Dùng RECEIVED
- Trái phiếu trả lãi 6 tháng một lần hoặc hàng năm → Dùng PRICE
- Trái phiếu kho bạc (T-bills) → Dùng TBILLPRICE hoặc RECEIVED
Với tín phiếu kho bạc Việt Nam kỳ hạn dưới 1 năm, RECEIVED cho kết quả chính xác vì không có coupon.
Kinh nghiệm sau 2 năm sử dụng
Hàm RECEIVED tiết kiệm thời gian, nhưng quan trọng hơn là giảm sai sót. Trước đây tôi mắc lỗi tính toán 5-7 lần mỗi tháng, ảnh hưởng đến báo cáo phân tích. Hai năm qua với RECEIVED, số lỗi giảm xuống 0.
Mẹo nhỏ: Tạo template Excel với các cột định dạng sẵn và công thức RECEIVED ở dòng đầu tiên. Khi cần phân tích danh mục mới, copy template và điền dữ liệu. Template của tôi có thêm format ngày, format tiền tệ, và conditional formatting để highlight các khoản lợi nhuận dưới 5% hàng năm.
Hàm này hoạt động tốt nhất khi kết hợp với bảng tính có cấu trúc rõ ràng. Đặt tên cho các cột, dùng Excel Tables (Ctrl+T) để công thức tự động áp dụng cho dòng mới.
Tài liệu tham khảo thêm
Microsoft cung cấp tài liệu chi tiết về hàm RECEIVED tại trang hỗ trợ Office chính thức. Phần Financial Functions Reference liệt kê đầy đủ 50+ hàm tài chính khác với ví dụ.
Đối với người mới bắt đầu với Excel tài chính, nên học theo thứ tự: PV và FV (giá trị hiện tại và tương lai), sau đó NPV và IRR (giá trị hiện tại ròng và tỷ suất hoàn vốn nội bộ), cuối cùng là RECEIVED và các hàm chứng khoán phức tạp hơn.
Cộng đồng Excel Việt Nam trên các diễn đàn có nhiều bài hướng dẫn về ứng dụng hàm tài chính trong bối cảnh thị trường Việt Nam, đặc biệt hữu ích cho tính toán trái phiếu doanh nghiệp và tín phiếu kho bạc.
