Neil biết cách tính tỷ suất hoàn vốn nội bộ đơn giản bằng cách sử dụng hàm IRR. Tuy nhiên, anh ấy tự hỏi, làm thế nào để tính IRR trong một giao dịch phức tạp hơn, chẳng hạn như khoản thanh toán hàng tháng với kỳ hạn 36 tháng, trong đó 12 tháng đầu tiên được miễn lãi và 24 tháng tiếp theo có lãi suất 5%.

Sẽ rất hữu ích nếu bạn hiểu ý nghĩa của tỷ suất hoàn vốn nội bộ (IRR). Bản thân đây là lãi suất (lợi tức) được tính trung bình trong suốt thời gian của khoản vay. Do đó, lãi suất tính cho khoản vay không thực sự có tác dụng. (Điều này được chứng minh bằng thực tế là các tham số cho hàm IRR của Excel không bao gồm tham số lãi suất.) Điều duy nhất quan trọng là tiền gốc, thời hạn và thời điểm hoàn trả.

Vì Neil đang nhận các khoản hoàn trả hàng tháng đều đặn, nên hàm IRR thực sự là hàm cần sử dụng. Excel cũng bao gồm các biến thể trên hàm IRR, nhưng chúng có thể không phù hợp. Hàm MIRR tính IRR khi bạn tái đầu tư các khoản hoàn trả. Nó cho phép bạn chỉ định một lãi suất, nhưng tỷ lệ này được áp dụng cho việc tái đầu tư các khoản hoàn trả; nó không phải là lãi suất bạn tính cho người trả nợ cho bạn.

Một hàm Excel khác liên quan đến IRR là hàm XIRR. Điều này không phù hợp với nhu cầu của Neil vì nó được sử dụng để tính IRR khi các khoản hoàn trả không thường xuyên. Ví dụ, bạn chỉ có thể được trả nợ vào những tháng nhất định trong năm. Vì Neil đang nhận các khoản thanh toán hàng tháng đều đặn, nên chức năng IRR vẫn là tốt nhất để sử dụng.

Sau đó, hãy xem điều này sẽ hoạt động như thế nào trong trường hợp của Neil. Giả sử anh ta cho ai đó vay 50.000 đô la với lãi suất 5%. Lãi suất này chỉ áp dụng cho 24 tháng cuối cùng của khoản vay, vì Neil chỉ ra rằng 12 tháng đầu tiên không tính lãi. Do đó, các khoản thanh toán trong 12 tháng đầu tiên sẽ là 1.388,89 đô la mỗi tháng, được tính bằng cách đơn giản chia 50.000 đô la cho 36.

Sau năm đầu tiên đó, các khoản thanh toán sẽ là 1.462,38 đô la vì người đó về cơ bản nhận được khoản vay 33.333,32 đô la (số dư còn lại, bằng 2/3 số tiền vay ban đầu) với lãi suất 5% trong 24 tháng.

Khi tính IRR, bạn có 36 khoản thanh toán được tính toán — 12 trong số đó là 1.388,89 đô la và 24 trong số đó là 1.462,38 đô la. Đặt giá trị ban đầu vào ô A1 (dưới dạng giá trị âm) và các khoản thanh toán (dưới dạng giá trị dương) vào các ô A2: A37. Sau đó, bạn có thể áp dụng hàm IRR cho phạm vi 37 giá trị. (Xem Hình 1.)

image

Hình 1. Tính IRR dựa trên khoản vay 50.000 đô la.

Lưu ý rằng IRR là 0,19%. Vì dòng tiền đại diện cho các khoản thanh toán hàng tháng, đây là IRR mỗi tháng. Để điều chỉnh nó để hiển thị dưới dạng tỷ lệ hàng năm, chỉ cần nhân nó với 12 và bạn sẽ có IRR hàng năm là 2,24%. Đây là số tiền mà Neil thực sự kiếm được từ khoản vay 50.000 đô la của mình. Đây là tiền lãi trung bình thu được trong suốt thời gian vay. Nó chỉ ra là thấp hơn một chút so với lãi suất 5% vì một phần ba thời hạn cho vay thực sự không có lãi suất.

Cần lưu ý rằng tất cả các tính toán này đều dựa trên lưu ý là thực sự miễn lãi trong 12 tháng đầu tiên. Nếu không phải như vậy — nếu tiền lãi chỉ đơn giản là trả chậm trong 24 tháng qua — thì kết quả hoàn toàn khác, nhưng các phép tính vẫn giống nhau. Người trả khoản vay sẽ vẫn chỉ phải trả 1.388,89 đô la mỗi tháng trong 12 tháng đầu tiên. Tuy nhiên, vì lãi suất vẫn được tính, sau năm đầu tiên, số dư chưa thanh toán là $ 35,504,12.

Số dư chưa thanh toán này sau đó được sử dụng để xác định khoản thanh toán thường xuyên cho 24 tháng còn lại, sẽ là khoản thanh toán hàng tháng là 1.557,62 đô la. Kết hợp các số liệu đó vào cùng một lịch trình trả nợ sẽ tính được IRR mới là 0,42%, được tính hàng năm chính xác là 5%. (Xem Hình 2.)

image

Hình 2. Tính IRR dựa trên khoản vay 50.000 đô la, với lãi suất trả chậm.

ExcelTips là nguồn của bạn để đào tạo Microsoft Excel hiệu quả về chi phí.

Mẹo này (4359) áp dụng cho Microsoft Excel 2007, 2010, 2013 và 2016.