Hàm MDURATION Trong Excel: Công Cụ Tài Chính Ẩn Mà 90% Người Dùng Không Biết

Excel có hơn 400 hàm tích hợp, nhưng một số công cụ mạnh nhất lại bị ẩn trong danh sách tài chính. Hàm MDURATION là một trong số đó. Ngay cả người dùng Excel nhiều năm cũng chưa chắc biết hàm này tồn tại, dù nó giải quyết bài toán phức tạp trong phân tích trái phiếu chỉ bằng một công thức đơn giản.

Hàm MDURATION tính toán rủi ro lãi suất tự động

Trong phân tích tài chính, modified duration đo lường mức độ nhạy cảm của giá chứng khoán khi lãi suất thay đổi. Công thức tính bằng tay cần 7-8 bước với nhiều biến số. MDURATION thực hiện toàn bộ phép tính này trong một hàm duy nhất.

Modified Macauley duration khác với Macauley duration thông thường ở chỗ nó điều chỉnh theo tần suất thanh toán coupon. Giá trị này cho biết giá trái phiếu sẽ giảm bao nhiêu phần trăm khi lãi suất tăng 1%. Ví dụ modified duration là 5.2 nghĩa là nếu lãi suất tăng 1%, giá trái phiếu giảm khoảng 5.2%.

Hàm này có sẵn từ Excel 2007 trở đi, hoạt động trên Excel 365, Excel 2021, Excel 2019 và các phiên bản cũ hơn. Tôi dùng nó thường xuyên khi phân tích danh mục trái phiếu thay vì tính toán thủ công hoặc dùng calculator riêng.

Cú pháp hàm MDURATION chi tiết

Cấu trúc cơ bản của hàm là:

=MDURATION(settlement, maturity, coupon, yld, frequency, [basis])

Các tham số bắt buộc:

Settlement là ngày thanh toán chứng khoán, tức ngày bạn mua trái phiếu. Nên nhập bằng hàm DATE thay vì nhập trực tiếp text. Ví dụ: DATE(2025,1,15) cho ngày 15/01/2025.

Maturity là ngày đáo hạn, khi trái phiếu được hoàn trả. Cũng dùng hàm DATE để đảm bảo định dạng đúng. Excel lưu ngày dưới dạng số nên nhập sai định dạng sẽ báo lỗi.

Coupon là lãi suất phiếu lãi hàng năm của chứng khoán, nhập dưới dạng số thập phân. Trái phiếu lãi suất 6% thì nhập 0.06 chứ không phải 6.

Yld là lợi tức hàng năm của chứng khoán, cũng nhập dạng thập phân. Đây là tỷ suất sinh lợi mà nhà đầu tư mong đợi. Nếu yld cao hơn coupon, trái phiếu được bán dưới mệnh giá.

Frequency là số lần thanh toán coupon mỗi năm. Nhập 1 cho thanh toán hàng năm, 2 cho nửa năm, 4 cho hàng quý. Trái phiếu Việt Nam thường thanh toán nửa năm nên dùng 2.

Tham số tùy chọn:

Basis xác định cách tính ngày trong năm. Mặc định là 0 (US NASD 30/360). Các giá trị khác: 1 (thực tế/thực tế), 2 (thực tế/360), 3 (thực tế/365), 4 (châu Âu 30/360). Với trái phiếu chính phủ Việt Nam thường dùng basis 1.

XEM THÊM: Hàm AMORLINC Trong Excel: Hướng Dẫn Tính Khấu Hao Tài Sản Từng Kỳ Kế Toán Chi Tiết

Ví dụ thực tế với trái phiếu chính phủ

Giả sử bạn mua trái phiếu chính phủ với thông tin sau:

Ngày thanh toán: 15/01/2025
Ngày đáo hạn: 15/01/2030
Lãi suất coupon: 6% năm
Lợi tức yêu cầu: 5.5% năm
Thanh toán lãi: Nửa năm một lần
Cơ sở tính ngày: Thực tế/thực tế

Công thức trong Excel:

=MDURATION(DATE(2025,1,15), DATE(2030,1,15), 0.06, 0.055, 2, 1)

Kết quả trả về khoảng 4.35, nghĩa là nếu lãi suất thị trường tăng 1%, giá trái phiếu này sẽ giảm khoảng 4.35%.

Phân tích kết quả:

Modified duration 4.35 cho thấy trái phiếu này có độ nhạy cảm trung bình với lãi suất. Trái phiếu ngắn hạn thường có duration dưới 3, trái phiếu dài hạn có thể lên đến 15-20. Con số này giúp so sánh rủi ro giữa các trái phiếu khác nhau trong danh mục.

Khi so sánh hai trái phiếu cùng kỳ hạn, trái phiếu nào có modified duration thấp hơn sẽ ít biến động giá hơn khi lãi suất thay đổi. Đây là thông tin quan trọng để đa dạng hóa danh mục.

Ứng dụng trong phân tích danh mục đầu tư

Tính duration trung bình của danh mục:

Nếu có danh mục 5 trái phiếu, tính MDURATION cho từng trái phiếu rồi nhân với tỷ trọng trong danh mục. Tổng các giá trị này cho duration trung bình của toàn bộ danh mục.

Ví dụ: Trái phiếu A chiếm 40% với duration 3.5, trái phiếu B chiếm 60% với duration 6.2. Duration danh mục = 0.4 × 3.5 + 0.6 × 6.2 = 5.12. Nếu lãi suất tăng 1%, giá trị danh mục giảm khoảng 5.12%.

So sánh kịch bản lãi suất:

Tạo bảng với nhiều mức yield khác nhau để thấy duration thay đổi thế nào. Khi yield tăng, duration giảm vì thời gian hoàn vốn nhanh hơn. Điều này giúp dự đoán giá trái phiếu trong các tình huống khác nhau.

Hedging rủi ro lãi suất:

Biết modified duration giúp tính toán số lượng hợp đồng tương lai cần để hedge. Nếu danh mục có duration 8 và giá trị 10 tỷ, khi lãi suất tăng 0.5%, giá trị giảm khoảng 400 triệu. Có thể dùng hợp đồng tương lai trái phiếu để bù đắp rủi ro này.

Lỗi thường gặp và cách khắc phục

Lỗi NUM xuất hiện khi:

XEM THÊM: Tính Chiết Khấu Trái Phiếu Chính Xác Với Hàm DISC Trong 5 Phút

Yield hoặc coupon âm. Excel trả về NUM vì không thể tính duration với lãi suất âm trong mô hình này. Kiểm tra lại các ô chứa yld và coupon, đảm bảo là số dương.

Settlement lớn hơn hoặc bằng maturity. Trái phiếu phải có kỳ hạn còn lại, không thể tính duration cho trái phiếu đã đáo hạn. Đổi ngày settlement về trước maturity.

Frequency không phải 1, 2 hoặc 4. Excel chỉ chấp nhận ba giá trị này. Nếu nhập frequency 3 hoặc 6 sẽ báo lỗi NUM.

Basis nằm ngoài khoảng 0-4. Nếu nhập basis là 5 hoặc -1, hàm trả về NUM. Để trống tham số này nếu không chắc chắn, Excel sẽ dùng mặc định 0.

Lỗi VALUE xuất hiện khi:

Ngày tháng nhập sai định dạng. Nhập “15/01/2025” dưới dạng text có thể gây lỗi VALUE. Luôn dùng hàm DATE hoặc đảm bảo ô được format là Date.

Tham số là text thay vì số. Nếu coupon được nhập là “6%” dạng text, hàm báo lỗi. Phải nhập 0.06 dưới dạng số hoặc tham chiếu ô chứa số.

Xử lý nhanh:

Nhấn F2 trên ô có lỗi để xem công thức. Kiểm tra từng tham số xem có đúng kiểu dữ liệu không. Dùng hàm ISNUMBER để test xem các tham số có phải số hay không.

Nếu settlement và maturity được nhập dạng text, chuyển sang dùng hàm DATE ngay trong công thức MDURATION. Cách này đáng tin cậy hơn việc format ô.

So sánh với hàm DURATION

Excel có hai hàm tính duration: DURATION và MDURATION. DURATION tính Macauley duration gốc, MDURATION tính modified duration. Macauley duration đo thời gian trung bình có trọng số để nhận dòng tiền, measured theo năm. Modified duration là Macauley duration chia cho (1 + yield/frequency).

Công thức chuyển đổi: Modified Duration = Macauley Duration / (1 + yield/frequency)

Trong thực tế, modified duration hữu ích hơn vì trực tiếp cho biết biến động giá theo phần trăm. Macauley duration cho biết thời gian hoàn vốn trung bình nhưng không trực tiếp cho biết rủi ro giá.

Khi phân tích rủi ro lãi suất, dùng MDURATION. Khi muốn biết thời gian hoàn vốn trung bình, dùng DURATION. Hầu hết báo cáo đầu tư và risk management dùng modified duration làm chỉ số chính.

Tính toán nhanh với bảng dữ liệu

Thiết lập một bảng template với các cột: Tên trái phiếu, Settlement, Maturity, Coupon, Yield, Frequency, Basis, Modified Duration. Copy công thức MDURATION xuống toàn bộ cột Modified Duration để tính cho nhiều trái phiếu cùng lúc.

XEM THÊM: Tôi Tính Sai Nợ Gốc Trong 6 Tháng Cho Đến Khi Biết Hàm CUMPRINC Trong Excel

Khi cập nhật yield hoặc ngày thanh toán, modified duration tự động tính lại. Không cần calculator riêng hoặc công thức phức tạp. Tôi có template này lưu sẵn, mỗi khi cần phân tích trái phiếu mới chỉ nhập thông tin cơ bản.

Thêm conditional formatting để highlight các trái phiếu có duration quá cao, ví dụ trên 10. Điều này cảnh báo những trái phiếu có rủi ro lãi suất lớn cần xem xét kỹ hơn trong danh mục.

Khi nào nên dùng MDURATION

Hàm này phù hợp với phân tích trái phiếu có coupon cố định và thanh toán định kỳ. Không áp dụng được cho trái phiếu zero-coupon, trái phiếu có lãi suất thả nổi, hoặc các cấu trúc phức tạp như convertible bond.

MDURATION yêu cầu Excel 2007 trở lên. Với Excel 2003 hoặc cũ hơn, phải dùng công thức thủ công hoặc add-in. Hàm hoạt động trên cả Excel desktop và Excel web, nhưng Excel mobile có thể không hỗ trợ đầy đủ các hàm tài chính.

Nếu làm việc với bond portfolio lớn, cân nhắc dùng Power Query hoặc VBA để tự động hóa việc tính toán cho hàng trăm trái phiếu. MDURATION vẫn là core formula nhưng automation giúp scale up.

Các quỹ đầu tư trái phiếu chuyên nghiệp dùng MDURATION như công cụ screening ban đầu, sau đó dùng phần mềm chuyên dụng cho phân tích sâu hơn. Với nhà đầu tư cá nhân và analyst, Excel với MDURATION đủ cho hầu hết nhu cầu.

Kết luận

Hàm MDURATION giải quyết bài toán phức tạp trong phân tích rủi ro lãi suất bằng một công thức đơn giản. Modified duration là chỉ số quan trọng trong quản lý danh mục trái phiếu, giúp định lượng rủi ro và so sánh các chứng khoán khác nhau.

Công thức này có sẵn trong Excel từ 2007 nhưng ít được nhắc đến so với các hàm phổ biến như VLOOKUP hay SUMIF. Với sáu tham số cơ bản, bạn có thể tính modified duration cho bất kỳ trái phiếu coupon nào. Kết hợp với các hàm DATE và conditional formatting tạo thành công cụ phân tích mạnh mẽ mà không cần phần mềm đắt tiền.

Bắt đầu với ví dụ đơn giản như trên, sau đó mở rộng sang danh mục nhiều trái phiếu. MDURATION đặc biệt hữu ích khi so sánh các kịch bản lãi suất khác nhau hoặc tính toán hedging ratio cho risk management.