Hàm RATE trong Excel: các ví dụ công thức để tính lãi suất

Hướng dẫn này giải thích cách tính lãi suất tiền gửi định kỳ trong Excel bằng cách sử dụng hàm RATE.

Các quyết định tài chính là một yếu tố quan trọng của chiến lược và kế hoạch kinh doanh. Trong cuộc sống hàng ngày, chúng ta cũng có khá nhiều quyết định về tài chính. Ví dụ, bạn sẽ đăng ký một khoản vay để mua một chiếc ô tô mới. Chắc chắn sẽ rất hữu ích khi biết chính xác mức lãi suất bạn sẽ phải trả cho ngân hàng của mình. Đối với những trường hợp như vậy, Excel cung cấp hàm RATE được thiết kế đặc biệt để tính lãi suất cho một thời kỳ cụ thể.

Hàm RATE trong Excel

RATE là một hàm tài chính Excel tìm lãi suất trong một khoảng thời gian nhất định của niên kim. Hàm tính toán theo phép lặp và có thể không có hoặc nhiều hơn một nghiệm.

Hàm có sẵn trong tất cả các phiên bản Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 và Excel 2007.

Cú pháp như sau:

RATE(nper, pmt, pv, [fv], [type], [guess])

Ở đâu:

  • Nper (bắt buộc) – tổng số kỳ thanh toán như năm, tháng, quý, v.v.
  • Pmt (bắt buộc) – số tiền thanh toán cố định mỗi kỳ không thể thay đổi trong suốt thời gian tồn tại của niên kim. Thông thường, nó bao gồm tiền gốc và lãi, nhưng không có thuế.
  • Pv (bắt buộc) – giá trị hiện tại, tức là giá trị hiện tại của khoản vay hoặc khoản đầu tư.
  • Fv (tùy chọn) – giá trị tương lai, tức là số dư tiền mặt bạn muốn có sau lần thanh toán cuối cùng. Nếu bỏ qua, nó sẽ mặc định là 0.
  • Loại (tùy chọn) – cho biết thời điểm thanh toán được thực hiện:
    • 0 hoặc bị bỏ qua (mặc định) – khoản thanh toán đến hạn vào cuối kỳ
    • 1 – đến hạn thanh toán vào đầu kỳ
  • Đoán (tùy chọn) – giả định của bạn về tỷ lệ có thể là bao nhiêu. Nếu bỏ qua, nó mặc định là 10%.

6 điều bạn nên biết về hàm RATE trong Excel

Để sử dụng hiệu quả các công thức RATE trong trang tính của bạn, hãy chú ý đến các lưu ý sử dụng sau:

  1. Hàm RATE tính toán thông qua thử và sai. Nếu nó không thể hội tụ thành một giải pháp sau 20 lần lặp, lỗi #NUM! lỗi được trả lại.
  2. Theo mặc định, lãi suất được tính cho mỗi kỳ thanh toán . Nhưng bạn có thể tính lãi suất hàng năm bằng phép nhân như trong ví dụ này.
  3. Sử dụng số dương để biểu thị tiền mặt mà bạn nhận được (dòng vào) và số âm để biểu thị tiền mặt mà bạn trả ra (dòng ra).
  4. Mặc dù cú pháp RATE mô tả pv là đối số bắt buộc, nó thực sự có thể bị bỏ qua nếu bạn bao gồm đối số fv . Cú pháp như vậy thường được sử dụng để tính lãi suất trên tài khoản tiết kiệm .
  5. Đối số đoán có thể bị bỏ qua trong hầu hết các trường hợp vì nó chỉ là giá trị bắt đầu cho một thủ tục lặp.
  6. Khi tính RATE cho các khoảng thời gian khác nhau, hãy đảm bảo rằng bạn nhất quán với các giá trị được cung cấp cho nper và đoán . Ví dụ: nếu bạn thanh toán hàng năm cho khoản vay 3 năm với lãi suất 8% hàng năm, hãy sử dụng 3 cho nper và 8% cho phỏng đoán . Nếu bạn định thanh toán hàng tháng cho cùng một khoản vay, thì hãy sử dụng 3 * 12 cho nper và 8% / 12 cho phỏng đoán .

Công thức RATE cơ bản trong Excel

Trong ví dụ này, chúng ta sẽ xem xét cách tạo công thức RATE ở dạng đơn giản nhất để tính lãi suất trong Excel.

Giả sử bạn đã vay 10.000 đô la, số tiền này sẽ được trả đầy đủ trong ba năm tới. Bạn đang có kế hoạch trả 3 lần hàng năm, mỗi lần $ 3,800. Lãi suất hàng năm sẽ là bao nhiêu?

Để tìm ra nó, chúng tôi xác định các đối số sau cho hàm RATE trong Excel:

  • Nper trong C2 (số lần thanh toán): 3
  • Pmt ở C3 (số tiền thanh toán): -3,800
  • Pv trong C4 (số tiền cho vay): 10.000

Vui lòng lưu ý rằng chúng tôi chỉ định thanh toán hàng năm ( pmt ) là số âm vì đó là tiền mặt chuyển đi.

Giả định rằng khoản thanh toán sẽ được thực hiện vào cuối mỗi năm, vì vậy chúng tôi có thể bỏ qua đối số [ type ] hoặc đặt nó thành giá trị mặc định (0). Hai đối số tùy chọn khác [ fv ] và [ đoán ] cũng bị bỏ qua.

Kết quả là, chúng tôi nhận được công thức đơn giản sau:

=RATE(C2, C3, C4)

Hàm RATE trong Excel

Nếu yêu cầu thanh toán phải được nhập dưới dạng số dương , thì hãy đặt dấu trừ trước đối số pmt trực tiếp trong công thức:

=RATE(C2, -C3, C4)

Hàm RATE trong Excel
Cách tính lãi suất trong Excel – ví dụ công thức

Bây giờ bạn đã biết những điều cơ bản của việc sử dụng RATE trong Excel, hãy cùng khám phá một vài trường hợp sử dụng cụ thể.

Cách tính lãi suất hàng tháng cho khoản vay

Vì hầu hết các khoản vay trả góp đều được trả hàng tháng nên việc biết lãi suất hàng tháng có thể hữu ích phải không? Đối với điều này, bạn chỉ cần cung cấp một số kỳ thanh toán thích hợp cho hàm RATE.

Giả sử khoản vay được trả dần hàng tháng trong vòng 3 năm. Để có tổng số lần thanh toán, chúng tôi nhân 3 năm với 12 tháng (3 * 12 = 36).

Các thông số khác được hiển thị bên dưới:

  • Nper trong C2 (số tiết): 36
  • Pmt ở C3 (thanh toán hàng tháng): -300
  • Pv trong C4 (số tiền cho vay): 10.000

Giả sử khoản thanh toán đến hạn vào cuối mỗi tháng, bạn có thể tìm lãi suất hàng tháng bằng cách sử dụng công thức quen thuộc:

=RATE(C2, C3, C4)

So với ví dụ trước, sự khác biệt chỉ nằm ở các giá trị được sử dụng cho các đối số RATE. Bởi vì hàm trả về lãi suất trong một khoảng thời gian thanh toán nhất định, chúng tôi nhận được lãi suất hàng tháng do kết quả:

Tính lãi suất hàng tháng trong Excel

Nếu dữ liệu nguồn của bạn bao gồm số năm mà khoản vay phải được hoàn trả, bạn có thể thực hiện phép nhân bên trong đối số nper :

=RATE(C2*12, C3, C4)

 Một cách khác để tìm lãi suất hàng tháng

Cách tính lãi suất hàng năm trong Excel

Lấy ví dụ của chúng tôi xa hơn một chút, làm thế nào để bạn tìm thấy lãi suất hàng năm cho các khoản thanh toán hàng tháng ? Chỉ cần nhân kết quả RATE với số kỳ mỗi năm, trong trường hợp của chúng tôi là 12:

=RATE(C2, C3, C4) * 12

Ảnh chụp màn hình dưới đây cho phép bạn so sánh lãi suất hàng tháng trong C7 và lãi suất hàng năm trong C9:
 Tính lãi suất hàng năm trong Excel

Điều gì sẽ xảy ra nếu các khoản thanh toán được thực hiện vào cuối mỗi quý ?

Đầu tiên, bạn chuyển đổi tổng số kỳ thành hàng quý:

Nper : 3 (năm) * 4 (quý mỗi năm) = 12

Sau đó, sử dụng hàm RATE để tính lãi suất hàng quý (C7):

=RATE(C2, C3, C4)

Và nhân kết quả với 4 để có lãi suất hàng năm (C9):

=RATE(C2, C3, C4) * 4

 Tính lãi suất hàng quý hàng năm

Cách tìm lãi suất trên tài khoản tiết kiệm

Trong các ví dụ trên, chúng tôi đã xử lý các khoản vay và tính lãi suất dựa trên ba thành phần chính: thời hạn cho vay, số tiền thanh toán mỗi kỳ và số tiền vay.

Một kịch bản phổ biến khác là tìm lãi suất trên một loạt các dòng tiền định kỳ mà chúng ta biết giá trị tương lai, không phải giá trị hiện tại.

Ví dụ: hãy tính lãi suất cần thiết để tiết kiệm 100.000 đô la trong 5 năm, với điều kiện bạn thực hiện thanh toán 1.500 đô la vào cuối mỗi tháng với khoản đầu tư ban đầu bằng không.

Để hoàn thành việc này, chúng tôi xác định các biến sau:

  • Nper trong C2 (tổng số lần thanh toán): 5 * 12
  • Pmt ở C3 (thanh toán hàng tháng): -1,500
  • Fv trong C4 (giá trị mong muốn trong tương lai): 100.000

Để tính lãi suất hàng tháng , công thức trong câu C6 là:

=RATE(C2*12, C3, ,C4)

Xin lưu ý rằng C2 chứa số năm. Để có tổng số kỳ thanh toán, chúng tôi nhân nó với 12.

Để có lãi suất hàng năm , chúng ta nhân lãi suất hàng tháng với 12. Vì vậy, công thức trong C8 là:

=RATE(C2*12, C3, ,C4) * 12

Các công thức để tìm lãi suất hàng tháng và hàng năm trên tài khoản tiết kiệm

Cách tìm tỷ lệ tăng trưởng kép hàng năm trên vốn đầu tư

Hàm RATE trong Excel cũng có thể được sử dụng để tính toán tỷ lệ tăng trưởng kép hàng năm (CAGR) trên một khoản đầu tư trong một khoảng thời gian nhất định.

Giả sử bạn muốn đầu tư 100.000 đô la trong 5 năm và nhận được 200.000 đô la cuối cùng. Đầu tư của bạn sẽ tăng trưởng như thế nào về CAGR? Để tìm hiểu điều đó, bạn thiết lập các đối số sau cho hàm RATE:

  • Nper (C2): 5
  • Pv (C3): -100.000
  • Fv (C4): 200.000

Xin lưu ý rằng đối số pmt không được sử dụng trong trường hợp này, vì vậy chúng tôi để trống trong công thức:

=RATE(C2, ,C3, C4)

Kết quả là, hàm RATE trong Excel cho chúng ta biết rằng khoản đầu tư của chúng tôi đã đạt được tỷ lệ tăng trưởng kép hàng năm 14,87% trong 5 năm.

 Sử dụng hàm RATE để tính CAGR trên đầu tư

Tạo máy tính lãi suất trong Excel

Như bạn có thể nhận thấy, các ví dụ trước tập trung vào việc giải quyết các nhiệm vụ cụ thể. Lần này, mục tiêu của chúng tôi là tạo ra một công cụ tính lãi suất chung cho niên kim, là một loạt các khoản thanh toán bằng nhau được thực hiện đều đặn.

Vì chúng ta sẽ sử dụng công thức RANK trong Excel ở dạng đầy đủ, chúng ta cần cung cấp các ô cho tất cả các đối số, bao gồm cả các đối số tùy chọn:

  • Tổng số lần thanh toán ( nper) – C2
  • Số tiền thanh toán ( pmt ) – C3
  • Giá trị hiện tại hàng năm ( pv ) – C4
  • Giá trị tương lai hàng năm ( fv ) – C5
  • Loại niên kim ( loại ) – C6
  • Lãi suất ước tính ( đoán ) – C7
  • Số kỳ mỗi năm – C8

Để kiểm tra máy tính của chúng tôi trong thực tế, chúng ta hãy thử tìm lãi suất hàng tháng và hàng năm trên tài khoản tiết kiệm đảm bảo 100.000 đô la vào cuối 5 năm với khoản thanh toán hàng tháng là 1.500 đô la vào đầu mỗi kỳ.

Chúng tôi nhập các biến vào các ô tương ứng như được hiển thị trong hình dưới đây và nhập các công thức sau:

Trong C10, trả lại lãi suất định kỳ :

=RATE(C2, C3, C4, C5, C6, C7)

Trong C11, đưa ra lãi suất hàng năm :

=RATE(C2, C3, C4, C5, C6, C7) * C8

Đối với dữ liệu mẫu của chúng tôi, kết quả như sau:

 Máy tính lãi suất trong Excel

Xin lưu ý rằng:

  • Đối với nper , chúng tôi nhập 60 (5 năm * 12 tháng = 60 kỳ thanh toán).
  • Đối với loại , chúng tôi nhập 1 (khoản thanh toán đến hạn đầu kỳ). Để tránh sai lầm, bạn nên tạo một danh sách thả xuống trong C6 để chỉ cho phép các giá trị 0 và 1 cho đối số kiểu .
  • Nếu pv là 0 hoặc không được xác định (như trong ví dụ này), hãy nhớ chỉ định đối số fv .

Hàm RATE trong Excel không hoạt động

Hàm càng phức tạp thì khả năng xảy ra lỗi càng lớn. Cú pháp RATE khá đơn giản, nhưng nó vẫn để lại chỗ cho những sai lầm, đặc biệt nếu bạn có ít kinh nghiệm với các hàm tài chính trong Excel. Dưới đây, chúng tôi sẽ chỉ ra một vài lỗi thường gặp và cách khắc phục.

#NUM! lỗi

Lý do : xảy ra khi hàm RANK không tìm ra giải pháp.

Thông thường, điều này xảy ra bởi vì các số dương được sử dụng để đại diện cho các dòng tiền đi ra. Hãy nhớ đặt dấu trừ trước bất kỳ số tiền nào được thanh toán:

 Hàm RATE dương trả về lỗi #NUM vì số dương được sử dụng cho dòng tiền gửi đi

Trong một số trường hợp, bạn có thể cần giúp hàm RANK hội tụ thành một giải pháp bằng cách đưa ra phỏng đoán ban đầu :

 Để hàm RANK hội tụ thành một giải pháp, hãy đưa ra phỏng đoán ban đầu.

Khi tính lãi suất với giá trị hiện tại ( pv ) không xác định hoặc bằng không , hãy nhớ chỉ định giá trị tương lai ( fv ):

Khi giá trị hiện tại bằng 0 hoặc không được xác định, hãy cung cấp giá trị tương lai.

#GIÁ TRỊ! lỗi

Lý do : xảy ra khi một hoặc nhiều đối số không phải là số.

Để sửa lỗi, hãy kiểm tra kỹ các giá trị được sử dụng cho các đối số RANK và đảm bảo các số không được định dạng dưới dạng văn bản .

Hàm RATE trả về kết quả không chính xác

Triệu chứng : Kết quả của công thức RANK của bạn là một tỷ lệ phần trăm tiêu cực, hoặc nhiều giảm hoặc cao hơn dự kiến.

Lý do : Khi tính toán các khoản thanh toán hàng tháng hoặc hàng quý, bạn đã quên quy đổi số năm thành tổng số kỳ thanh toán. Hoặc lãi suất định kỳ không được chuyển đổi thành lãi suất hàng năm.

Để giải quyết vấn đề này, hãy sử dụng các phép tính sau để biểu thị đối số nper theo các đơn vị thích hợp :

Thanh toán hàng tháng: nper = năm * 12

Thanh toán hàng quý: nper = năm * 4

Để có lãi suất hàng năm , hãy nhân lãi suất định kỳ do hàm trả về với số kỳ mỗi năm.

Thanh toán hàng tháng: lãi suất hàng năm = RATE () * 12

Thanh toán hàng quý: lãi suất hàng năm = RATE () * 4

 Hàm RATE trả về kết quả không chính xác vì số năm khoản vay không được chuyển đổi thành tổng số kỳ thanh toán.

Công thức RATE trả về không phần trăm

Triệu chứng : Kết quả của công thức xuất hiện như zero tỷ lệ phần trăm không có chữ số thập phân (0%).

Lý do : Lãi suất được tính nhỏ hơn 1%. Bởi vì ô công thức được định dạng để không hiển thị vị trí thập phân, giá trị được hiển thị được “làm tròn” thành không.

Để giải quyết vấn đề này, chỉ cần áp dụng định dạng Phần trăm có hai hoặc nhiều chữ số thập phân cho ô chứa công thức của bạn.

 Kết quả của công thức RATE xuất hiện dưới dạng phần trăm 0 không có chữ số thập phân.

Đó là cách sử dụng hàm RATE trong Excel để tính lãi suất. Dịch vụ kế toán tại Hà Nội – ketoanmvb cảm ơn bạn đã đọc và hy vọng sẽ gặp bạn trên blog của chúng tôi vào tuần tới!

Source link

Trả lời