Hàm Yield trong Excel là một công cụ mạnh mẽ giúp chúng ta tính lãi suất đáo hạn YTM của trái phiếu. Sau khi đọc bài viết, này bạn sẽ biết hàm Yield là gì, công thức, ý nghĩa của hàm Yield và cách sử dụng hàm Yield trong Excel để tính lãi suất đáo hạn của trái phiếu. Cuối cùng chúng ta sẽ giải đáp một số bài tập tính YTM thường gặp.
Hàm Yield Là Gì?
Hàm Yield là một hàm tài chính trong Excel giúp tính toán lợi suất đáo hạn của một chứng khoán trả lãi định kỳ.
Trong bài viết này, chúng ta hãy cùng tìm hiểu sơ lược về lãi suất đáo hạn của trái phiếu coupon và cách sử dụng hàm Yield trong Excel để tính lãi suất đáo hạn này.
Lưu ý: Đối với trái phiếu trả lãi khi đáo hạn và trái phiếu chiết khấu (trái phiếu giảm giá) chúng ta cần dùng hàm Yieldmat và Yielddisc.
Lãi suất đáo hạn (YTM – Yield to Maturity) của trái phiếu coupon là tỷ suất lợi nhuận thực tế mà nhà đầu tư sẽ có được nếu nắm giữ trái phiếu đến ngày đáo hạn.
Vậy, nếu nói một cách gần gũi thì hàm Yield có ý nghĩa là hàm tính tỷ suất lợi nhuận thực tế mà chúng ta sẽ nhận được nếu “ôm” trái phiếu đến ngày đáo hạn.
Khi bạn mua trái phiếu, bạn sẽ quan tâm đến tỷ suất sinh lợi của trái phiếu kể từ ngày bạn mua đến khi đáo hạn. Để tính được suất sinh lợi này chúng ta cần tính giá trị hiện tại của những dòng tiền sẽ nhận được trong tương lai sao cho giá trị hiện tại này bằng với giá trái phiếu mà bạn mua.
Công Thức Tính YTM của trái phiếu được cho trong hình sau:
Trong công thức trên, r chính là YTM của trái phiếu, YTM chính là suất chiết khấu để giá trị hiện tại của dòng tiền nhận được bằng với giá thị trường của trái phiếu.
Dòng tiền nhận được này bao gồm dòng tiền lãi định kỳ hàng năm và giá trị hoàn trả khi đáo hạn (thường thì giá trị hoàn trả sẽ bằng mệnh giá).
Chúng ta có 4 cách để tính YTM của trái phiếu coupon, đó là:
1- Sử Dụng Công Thức Tính YTM Gần Đúng
Công thức tính YTM gần đúng là:
2- Giải phương trình bậc n trong hình trên.
3- Sử dụng phương pháp nội suy
4- Dùng hàm Yield trong Excel.
Xem thêm: Hàm PPMT Là Gì? Cách Dùng Hàm Tài Chính PPMT Trong Excel
Cách Tính YTM Của Trái Phiếu Bằng Hàm Yield Trong Excel
Trong phần này, Lodongxu sẽ hướng dẫn bạn cách tính YTM của trái phiếu bằng hàm Yield trong Excel.
Để sử dụng hàm Yield đúng cách, chúng ta cần tìm hiểu công thức, ý nghĩa của những đối số trong hàm và một số lưu ý khi dùng hàm.
Chúng ta có 3 loại trái phiếu nếu xét theo phương thức trả lãi, đó là Trái phiếu Coupon, Trái phiếu Chiết khấu và Trái phiếu Gộp.
Hàm Yield chỉ sử dụng được nếu các khoản lãi được chi trả định kỳ (tức Trái phiếu Coupon), nếu bạn cần tính YTM của Trái phiếu có khoản lãi được chi trả khi đáo hạn (Trái phiếu Gộp) thì bạn cần dùng hàm Yieldmat, đối với Trái phiếu Chiết khấu bạn hãy dùng hàm Yielddisc.
Công Thức Hàm Yield Trong Excel
Hàm Yield có công thức là: = Yield(Settlement, Maturity, Rate, Pr, Redemption, Frequency, [Basis])
Trong đó:
– Settlement là ngày thanh toán, hay ngày kết toán. Bạn có thể hiểu đây là ngày mà bạn bỏ tiền ra để mua trái phiếu. Ngày thanh toán phải là ngày sau ngày phát hành trái phiếu. Settlement là đối số bắt buộc trong hàm Yield.
– Maturity là ngày đáo hạn của trái phiếu. Maturity là đối số bắt buộc phải có trong hàm Yield.
– Rate là lãi suất hàng năm của trái phiếu, hay còn gọi là lãi suất coupon. Nhà phát hành sẽ tính toán lợi tức hàng năm dựa trên lãi suất này để chi trả định kỳ cho người nắm giữ trái phiếu. Rate cũng là đối số bắt buộc trong hàm Yield.
– Pr là giá của trái phiếu, Pr được tính bằng tỷ lệ phần trăm của mệnh giá trái phiếu. Ví dụ, mệnh giá trái phiếu là 100.000 VND và Pr bằng 95% mệnh giá thì chúng ta hiểu là giá của trái phiếu bằng 95.000 VND. Nhưng khi nhập vào hàm Yield, bạn hãy nhập số 95. Pr là đối số bắt buộc.
– Redemption là giá trị hoàn trả khi đáo hạn trái phiếu, giá trị này cũng được tính bằng tỷ lệ phần trăm của mệnh giá trái phiếu.
Như bình thường thì chúng ta có thể hiểu Redemption chính là mệnh giá của trái phiếu (Face Value), vì chúng ta hay gặp giá trị hoàn trả bằng đúng mệnh giá.
Nhưng nếu nói chính xác thì Redemption phải bằng “bao nhiêu phần trăm trên mệnh giá”, bởi vì có nhiều loại trái phiếu đặc biệt có giá trị hoàn trả cao hơn hoặc thấp hơn mệnh giá trái phiếu.
Redemption là đối số bắt buộc của hàm Yield.
– Frequency là số lần thanh toán lợi tức trái phiếu hàng năm. Nếu thanh toán hàng năm thì Frequency = 1, nếu thanh toán nửa năm một lần (tức 2 lần/năm) thì Frequency = 2, nếu thanh toán theo quý thì Frequency = 4.
Với trái phiếu nước ta thì thường là 1 lần/năm. Frequency cũng là đối số bắt buộc trong hàm Yield.
– Basis là hệ đếm ngày. Hệ đếm ngày sẽ quy ước cách mà thị trường hay tổ chức tài chính sử dụng để tính lãi. Basis là đối số tùy chọn trong hàm Yield. Chúng ta có 5 hệ đếm ngày được thể hiện trong bảng sau:
| Giá trị của Basis | Hệ đếm ngày |
| 0 hoặc bỏ qua | US (NASD) 30/360 |
| 1 | Thực tế/Thực tế (Actual/Actual) |
| 2 | Thực tế/360 (Actual/360) |
| 3 | Thực tế/365 (Actual/365) |
| 4 | European 30/360 |
Diễn giải hệ đếm ngày:
+ Hệ đếm US (NASD) 30/360. Đây là hệ đếm ngày của Mỹ, tức một tháng có 30 ngày dù tháng đó có 28 hay 31 ngày, một năm có 360 ngày cho dù năm đó có 365 hay 366 ngày.
+ Hệ đếm Thực tế/Thực tế (Actual/Actual). Hệ đếm này tính số ngày của tháng và số ngày của năm giống như trong lịch thực tế, ví dụ như với năm nhuận thì tính là 366 ngày.
+ Hệ đếm Thực tế/360 (Actual/360). Hệ đếm này tính số ngày trong tháng theo như lịch trong thực tế còn một năm có 360 ngày.
+ Hệ đếm Thực tế/365 (Actual/365). Hệ đếm này tính số ngày trong tháng giống như lịch trong thực tế còn một năm có 365 ngày.
+ Hệ đếm European 30/360. Đây là hệ đếm ngày của Châu Âu, tương tự như hệ đếm của Mỹ nhưng hệ đếm kiểu Mỹ thì phổ biến hơn. Hai hệ đếm này có một số khác biệt, đó là:
Trong hệ đếm của Châu Âu, nếu ngày bắt đầu hay ngày kết thúc rơi vào ngày 31 của tháng thì sẽ được chuyển thành ngày 30 của tháng đó.
Trong hệ đếm của Mỹ, nếu ngày bắt đầu rơi vào ngày 31 của tháng thì sẽ chuyển thành ngày 30 của tháng đó.
Nếu ngày kết thúc rơi vào ngày 31 của tháng và ngày bắt đầu rơi vào (hoặc trước) ngày 30 thì ngày kết thúc được xem là ngày 1 của tháng tiếp theo.
Nếu ngày kết thúc rơi vào ngày 31 của tháng và ngày bắt đầu rơi vào ngày 31 của một tháng thì ngày kết thúc được xem là ngày 30 của tháng kết thúc đó.
- Xem thêm:
Hàm XNPV Là Gì? Cách Sử Dụng Hàm XNPV Trong Excel
Hàm MIRR Là Gì? Cách Tính MIRR Bằng Excel Nhanh Chóng
Lưu Ý Khi Dùng Hàm Yield Để Tính YTM Trái Phiếu
Khi dùng hàm Yield để tính lãi suất đáo hạn trái phiếu trong Excel, bạn hãy chú ý một số điểm sau nhé:
– Để tránh nhầm lẫn, bạn nên sử dụng hàm Date theo cú pháp = Date(Year, Month, Day) để nhập ngày vào hàm Yield.
– Giá trái phiếu Pr mà chúng ta dùng trong hàm Yield là giá yết chứ không phải giá thanh toán trong thực tế. Giá yết còn được gọi là giá sạch (Clean Price), là giá được niêm yết trên sàn chứng khoán.
Giá yết là giá chưa cộng thêm lãi tích tụ (nếu có), còn giá thanh toán thực tế (hay giá bẩn) là bằng giá yết cộng với lãi tích tụ.
Giá Thanh Toán Thực Tế = Giá Yết + Lãi Tích Tụ (Nếu có)
Ví dụ, người bán nắm giữ trái phiếu trong 100 ngày, sau đó bạn mua lại thì bạn nắm giữ 265 ngày. Bạn sẽ được hưởng lãi của 265 ngày còn người bán được hưởng lãi trong 100 ngày trước đó. Số tiền lãi trong 100 ngày này được gọi là lãi tích tụ.
Như vậy, khi thanh toán bạn sẽ thanh toán bằng giá thực tế, còn khi tính hàm Yield thì chúng ta dùng giá yết. Nếu bạn nhập giá thanh toán vào hàm Yield thì chúng ta sẽ có kết quả sai lệch.
– Hàm Yield sẽ báo lỗi #Value! Nếu đối số Settlement hoặc Maturity chứa những ngày không hợp lệ, ví dụ như ngày 31/02, ngày 31/11,…
– Hàm Yield sẽ báo lỗi #NUM! Trong một số trường hợp như:
+ Giá trái phiếu (Pr) hoặc Giá trị hoàn trả (Redemption) nhỏ hơn hoặc bằng 0
+ Lãi suất Rate < 0
+ Số lần trả lãi (Frequency) có giá trị khác với 1; 2; 4
+ Ngày thanh toán mua trái phiếu (Settlement) đến sau ngày đáo hạn (Maturity)
+ Giá trị Basis không phải là các số 0; 1; 2; 3; 4.
Bài Tập Tính YTM Bằng Hàm Yield Trong Excel
Bài Tập 1: Tính YTM Vào Ngày Phát Hành
Trái phiếu X được phát hành vào ngày 15/03/2130 và đáo hạn vào ngày 15/03/2140. Trái phiếu có mệnh giá là 100.000 VND, Lãi suất coupon hàng năm là 10%, trả lãi đều đặn vào ngày 15/03 hàng năm và trả nợ gốc một lần khi đáo hạn. Hãy tính lãi suất đáo hạn của trái phiếu X khi bạn mua vào ngày phát hành và nắm giữ đến khi đáo hạn? Biết giá trái phiếu tại thời điểm phát hành là 95.000 VNĐ.
Lời Giải
Vì chúng ta mua tại thời điểm phát hành nên giá yết trên sàn chứng khoán chính là giá mà thực tế bạn bỏ ra thanh toán, là 95 ngàn đồng.
Vì trong hàm Yield, giá trái phiếu và giá trị hoàn trả được tính bằng tỷ lệ phần trăm trên mệnh giá, nên chúng ta cần nhập 95 vào ô C7 và 100 vào ô C8. Lưu ý là những con số này vốn đã mang ý nghĩa là “tỷ lệ phần trăm” rồi nên bạn chỉ cần nhập số 95 và 100 là được, ban đừng nhập 95000 hay 100% nhé.
Chúng ta tính được YTM của trái phiếu X là 10.843%. vậy, nếu bạn mua trái phiếu X và nắm giữ đến ngày đáo hạn, bạn sẽ được hưởng một suất sinh lợi bình quân là 10.843%.
Bài Tập 2: Tính YTM Khi Có Lãi Tích Tụ
Trái phiếu Y được phát hành vào ngày 22/08/2090 và đáo hạn vào ngày 22/08/2100. Mệnh giá trái phiếu là 100.000 VNĐ, lãi suất hàng năm là 9%, trả lãi một lần vào ngày 22/08 hàng năm và trả nợ gốc vào ngày đáo hạn. Bạn mua trái phiếu này vào ngày 05/03/2095 với giá là 105.000 VNĐ và nắm giữ đến khi đáo hạn . Hãy tính YTM của trái phiếu Y!
Lời Giải
Vì bạn mua trái phiếu này vào ngày khác với ngày trả lãi thường niên nên sẽ có lãi tích tụ bên trong giá thanh toán. Đồng thời giá cần nhập vào hàm Yield là giá yết cho nên chúng ta cần tìm lãi tích tụ trước, sau đó tìm giá yết bằng cách lấy giá thanh toán trừ đi lãi tích tụ, cuối cùng chúng ta mới tính YTM.
Bạn hãy tính lãi tích tụ bằng công thức: A = CF*d/D/f
Với A là lãi tích tụ, CF là tiền lãi hàng năm, d là số ngày người bán nắm giữ trong kỳ tính lãi, D là số ngày trong kỳ tính lãi, f là tần suất trả lãi trong năm.
Chúng ta sẽ tính d bằng hàm Coupdaybs:
= Coupdaybs(Ngày thanh toán, Ngày đáo hạn, Số lần thanh toán lãi, Hệ đếm ngày)
d chính là khoảng cách từ ngày 22/08/2094 đến ngày 05/03/2095.
Để tìm D, bạn hãy dùng hàm Coupdates:
= Coupdates(Ngày thanh toán, Ngày đáo hạn, Số lần thanh toán lãi, Hệ đếm ngày)
Sau khi tính được lãi tích tụ rồi, chúng ta tính giá yết để nhập vào hàm Yield như hình trên và tìm ra được YTM của trái phiếu là 8.931%.
- Đừng bỏ lỡ:
Cách Tính Giá Trái Phiếu Bằng Hàm Price Trong Excel (Từ A – Z)
Hàm IPMT Là Gì? Cách Dùng Hàm IPMT Trong Excel
Kết Luận
Chúng ta có thể thấy rằng, trong 4 phương pháp tính lãi suất đáo hạn của trái phiếu coupon thì cách dùng hàm Yield trong Excel là cách cho kết quả chính xác và nhanh chóng nhất.
Mong rằng sau bài viết này, bạn đã biết được hàm Yield là gì, công thức của hàm Yield, cách sử dụng hàm Yield trong Excel để tính lãi suất đáo hạn của trái phiếu và giải đáp được một số bài tập tính YTM thường gặp để vận dụng hiệu quả vào việc học tập của mình. Chúc bạn thành công.
