Trong bài viết này, Học Excel Online sẽ  hướng dẫn cho bạn công thức tính lãi kép trong Excel và cung cấp các ví dụ về làm thế nào để tính giá trị tương lai của khoản đầu tư theo lãi suất kép hàng năm, hàng tháng hoặc hàng ngày. Bạn cũng sẽ tìm thấy các bước chi tiết để tạo công thức tính lãi kép trong Excel của riêng bạn.

Lãi kép là một trong những phần cơ bản trong ngành ngân hàng và là một trong những lực lượng tài chính mạnh nhất để xác định kết quả đầu tư của bạn.

Nếu bạn không phải là một sinh viên kế toán, nhà phân tích tài chính hoặc một nhà đầu tư có kinh nghiệm, thì bạn có thể sẽ rất khó khăn để nắm bắt khái niệm từ những cuốn sách và sổ tay tài chính chuyên biệt. Mục đích của bài viết này là biến nó trở nên dễ dàng hơn 🙂 Bạn cũng sẽ học cách sử dụng một công thức lãi suất kép trong Excel và tạo ra một máy tính lãi suất kép phổ biến cho các bảng tính của riêng bạn.

Lãi kép là gì?

Có thể dễ dàng hơn khi bắt đầu với lãi suất đơn – chỉ được tính trên số tiền chính. Ví dụ: bạn đặt 10 đô-la vào tài khoản ngân hàng. Giá trị khoản tiền gửi của bạn trong một năm tới với mức lãi suất hàng năm 7% là bao nhiêu? Câu trả lời là 10,7 đô-la và lãi suất kiếm được của bạn là 0,7 đô-la.

Trong trường hợp lãi suất kép, khoản tiền gốc trong từng khoảng thời gian khác nhau thì khác nhau. Ngân hàng sẽ không trả lãi suất cho bạn, thay vào đó họ sẽ cộng thêm nó vào khoản tiền gốc đầu tư của bạn. Số tiền tăng lên này sẽ trở thành khoản gốc cho khoảng thời gian tiếp theo (tính lãi kép) và cũng tiếp tục có lãi. Nói cách khác, bạn không chỉ quan tâm đến số tiền gốc mà còn quan tâm đến lợi ích kiếm được trong mỗi giai đoạn tính lãi.

Trong ví dụ của chúng tôi, ngoài số tiền gốc là 10 đô-la, lãi suất kiếm được là 0,7 đô-la cũng sẽ có lãi trong năm tới. Vì vậy, khoản tiền gửi $ 10 của bạn sẽ là bao nhiêu sau 2 năm với mức lãi suất hàng năm là 7%? Câu trả lời là $ 11.45 và bạn có thể tính toán nó bằng nhiều cách, như được trình bày dưới đây:

Làm thế nào để tính lãi kép trong Excel:

Đầu tư dài hạn có thể là một chiến lược hiệu quả để tăng sự giàu có của bạn, và thậm chí các khoản tiền gửi nhỏ có thể tạo ra sự khác biệt lớn theo thời gian. Công thức tính lãi suất kép của Excel được giải thích thêm phía sau sẽ giúp bạn có chiến lược tiết kiệm của riêng mình trong công việc. Cuối cùng, chúng ta sẽ làm ra một công thức chung để tính toán giá trị tương lai của khoản đầu tư với bất kỳ mức lãi suất kép nào – hàng ngày, hàng tuần, hàng tháng, hàng quý, hoặc hàng năm.

Tính lãi kép hàng năm trong Excel:

Để hiểu ý tưởng về lãi suất kép tốt hơn, hãy bắt đầu bằng một ví dụ rất đơn giản được thảo luận ở phần đầu của bài hướng dẫn này – viết một công thức tính  lãi kép hàng năm trong Excel. Như bạn nhớ, bạn đang đầu tư $ 10 với lãi suất hàng năm là 7% và muốn biết làm thế nào mà tăng lãi suất hàng năm lại làm tăng tiết kiệm của bạn.

Mức lãi suất kép hàng năm – Công thức 1:

Một cách dễ dàng và đơn giản để tính toán số tiền kiếm được với lãi suất kép mỗi năm là sử dụng công thức:

Tăng lượng tiền theo tỷ lệ %  = Lượng tiền * (1 + %)

Trong ví dụ của chúng tôi, công thức “= A2 * (1 + $B2)” trong đó A2 là khoản tiền gửi ban đầu của bạn và B2 là lãi suất hàng năm. Vui lòng chú ý rằng cố định tham chiếu đến cột B bằng cách sử dụng dấu $.

Như bạn đã biết, 1% là một phần trăm, tức là 0,01, vì vậy 7% là 0,07 và đây là cách tỷ lệ phần trăm được lưu trữ trong Excel. Lưu ý rằng bạn có thể xác minh kết quả trả về từ công thức bằng cách tính đơn giản là 10 * (1 + 0,07) hoặc 10 * 1,07 và chắc chắn rằng số dư của bạn sau 1 năm sẽ là 10,70 đô-la.

Và bây giờ, chúng ta hãy tính toán tài khoản sau 2 năm. Với tiền gửi $10 sẽ có giá trị như thế nào trong hai năm với lãi suất hàng năm là 7%? Câu trả lời là $ 11.45 và bạn có thể lấy nó bằng cách sao chép cùng một công thức vào cột D.

Để tính toán số tiền bạn sẽ tìm thấy trong tài khoản ngân hàng của bạn vào cuối 3 năm, chỉ cần sao chép cùng một công thức với cột E và bạn sẽ nhận được $ 12,25.

Những bạn có kinh nghiệm làm việc với Excel hẳn đã nhẫn ra rằng những gì công thức trên thực sự làm là nhân số tiền gửi ban đầu 10$ với lãi suất hàng năm 1,07 ba lần:

= 10 * 1,07 * 1,07 * 1,07 = 12,25043

Làm tròn 2  chữ số thập phân cho kết quả và bạn sẽ nhận được số giống như bạn thấy trong ô E2 trong hình ở trên – $ 12.25. Đương nhiên, bạn có thể trực tiếp tính toán số dư sau 3 năm sử dụng công thức này:

= A2 * 1,07 * 1,07 * 1,07

Xem thêm: Học excel cơ bản uy tín tại Hà Nội

Tính Lãi kép hàng năm – Công thức 2

Một cách khác để tạo ra công thức tính lãi kép hàng năm là tính toán lãi suất kiếm được cho mỗi năm và sau đó thêm nó vào khoản tiền gửi ban đầu.

Giả sử khoản tiền gửi ban đầu (Initial deposit) của bạn nằm trong ô B1 và lãi suất mỗi năm (Annual interest rate) trong ô B2, thì công thức sau đây đã giải quyết nó: = B1 + B1 * $B$2.

Để công thức làm việc chính xác, xin lưu ý các chi tiết sau:

• Sửa tiêu chí tham chiếu đến ô Annual Interest Rate (B2 trong trường hợp của chúng ta) bằng cách thêm dấu $, nó phải được cố định cả cột và dòng, như $B$2.
• Cho năm thứ 2 (B6) và tất cả những năm tiếp theo, thay đổi công thức thành:

Year 1 balance + Year 1 balance * Interest Rate       (Số dư năm 1 + Số dư năm 1 * Lãi suất)

Trong ví dụ này, bạn nhập công thức = B5 + B5 * $B$2 vào ô B6 và sau đó sao chép nó xuống các hàng khác, như thể hiện trong hình dưới đây.

Để tìm hiểu mức lãi suất mà bạn thực sự kiếm được bằng lãi suất hợp nhất hàng năm, hãy nhập công thức = B5-B1 (Số dư sau 1 năm – Tiền gửi ban đầu) trong ô C5.

Sau đó nhập = B6-B5 (Số dư sau 2 năm – Số dư sau 1 năm) trong ô C6 và kéo công thức xuống các ô khác.

Bạn sẽ thấy sự tăng trưởng từ tiền lãi thu được như trong hình dưới đây cho thấy sức mạnh thực sự của lãi suất kép.

Các ví dụ trên làm tốt công việc minh hoạ cho ý tưởng tính lãi kép, phải không? Nhưng không có công thức nào đủ tốt để được gọi là công thức tính lãi suất chung cho Excel. Thứ nhất, bởi vì họ không cho phép bạn chỉ định một giai đoạn tính lãi kép, và thứ hai, bởi vì bạn phải xây dựng một toàn bộ bảng chứ không chỉ đơn giản là nhập vào một khoảng thời gian nhất định và lãi suất.

Vâng, chúng ta hãy đi trước một bước và tạo ra một công thức lãi suất chung cho Excel – có thể tính toán được bao nhiêu tiền bạn sẽ kiếm được – ứng với tỷ lệ lãi suất hàng năm, hàng quý, hàng tháng, hàng tuần hoặc hàng ngày.

Công thức tổng quát về lãi kép:

Khi các chuyên gia tư vấn tài chính phân tích tác động của lãi kép đối với đầu tư, họ thường xem xét ba yếu tố xác định giá trị tương lai của đầu tư (FV):

• PV – giá trị hiện tại của khoản đầu tư
• i – lãi suất thu được trong từng thời kỳ
• n – số chu kỳ

Sau khi đã biết các yếu tố này, thì bạn có thể sử dụng công thức sau để tìm ra giá trị tương lai của khoản đầu tư với mức lãi suất kép nhất định:

FV = PV * (1 + i)ⁿ

Để minh họa cho điểm tốt hơn, đây là một vài ví dụ nhanh chóng.

Ví dụ 1: Công thức lãi suất kép hàng tháng

Giả sử bạn đầu tư 2.000 USD với lãi suất 8% và ghép lãi hàng tháng và, bạn muốn biết giá trị đầu tư sau 5 năm.

Trước tiên, hãy viết xuống danh sách các yếu tố liên quan đến công thức lãi kép của bạn:

• PV = 2.000 đô-la
• i = 8%năm, ghép lãi hàng tháng (lãi suất mỗi tháng = 0.08 / 12 = 006666667)
• n = 5 năm x 12 tháng (5 * 12 = 60)

Nhập số liệu ở trên vào công thức, và bạn sẽ nhận được:

= $2,000 * (1 + 0.8/12)^5×12

hoặc là                  = $2,000 * 1.006666667⁶⁰

hoặc là                  = $2,000 * 1.489845708 = $2,979.69

Ví dụ 2: Công thức lãi suất hàng ngày

Tôi hy vọng bạn đã hiểu rõ ví dụ về lãi kép hàng tháng, và bây giờ bạn có thể sử dụng phương pháp tương tự cho trường hợp ghép lãi hàng ngày. Khoản đầu tư ban đầu, lãi suất, thời gian và công thức giống hệt như trong ví dụ trên, chỉ có giai đoạn kết hợp khác nhau:

• PV = 2.000 đô la
• i = 8% mỗi năm, ghép lãi mỗi ngày (0.08 / 365 = 0.000219178)
• n = 5 năm x 365 ngày (5 * 365 = 1825)

Cung cấp các con số cho công thức lãi kép, và bạn sẽ nhận được kết quả sau:

=$2,000 * (1 + 0.000219178)¹⁸²⁵ = $2,983.52

Như bạn thấy đấy, với trường hợp ghép lãi hàng ngày, giá trị tương lai của cùng một khoản đầu tư cao hơn một chút so với trường hợp ghép lãi hàng tháng. Điều này là do lãi suất 8% làm tăng lãi suất cho số tiền gốc mỗi ngày thay vì mỗi tháng. Như bạn có thể đoán, kết quả ghép lãi hàng tháng sẽ cao hơn ghép lãi hàng năm.

Tất cả đều tốt cho bạn cả, nhưng những gì bạn thực sự muốn là một công thức Excel cho lãi kép, phải không? Mong bạn vẫn cùng tôi tiếp tục đi theo ý tưởng này. Bây giờ chúng tôi đang chuẩn bị đề cập tới phần thú vị nhất – xây dựng máy tính tính lãi kép của riêng bạn mạnh và đa năng trong Excel.

Công thức lãi kép phổ biến trong Excel (ghép lãi hàng ngày, hàng tuần, hàng tháng, hàng năm)

Thông thường, có nhiều cách để làm điều gì đó trong Excel và một công thức tính lãi kép cũng không phải là ngoại lệ 🙂 Mặc dù Microsoft Excel không cung cấp hàm công thức đặc biệt nào để tính lãi kép, nhưng bạn có thể sử dụng các hàm khác để tạo ra máy tính tính lãi kép của riêng bạn.

Hãy bắt đầu tạo ra một máy tính tính lãi kép trong Excel của chúng tôi bằng cách nhập các yếu tố cơ bản, nhằm xác định giá trị tương lai của một khoản đầu tư vào một bảng tính Excel:

• Initial investment: Khoản đầu tư ban đầu (A3)
• Annual interest rate: Lãi suất năm (A4)
• Number of compounding periods per year: số kì trả lãi mỗi năm (A5)
• Number of years: Số năm (A6)

Khi kết thúc, bảng tính Excel của bạn có thể trông tương tự như sau:

Tất cả những gì bạn cần bây giờ là công thức lãi kép để tính số tiền kiếm được (Số dư) dựa trên các giá trị đầu vào.  Chúng ta sẽ đơn giản dùng công thức tính lãi kép, mà được ngân hàng và các tổ chức tài chính khác sử dụng và được sửa đổi một chút để Excel có thể hiểu được nó.

Công thức tính lãi kép cho Excel:

=Initial investment * (1 + Annual interest rate / Compounding periods per year) ^ (Years * Compounding periods per year)

Khi điều chỉnh máy tính tính lãi kép (được hiển thị trong hình ở trên), công thức có dạng sau:

= B3 * (1 + B4 / B5) ^ (B6 * B5)

Bạn có thấy các con số khá quen thuộc?  Nếu bạn muốn biết đầu tư của bạn sẽ có giá trị như thế nào với lãi suất 8% hàng năm trong trường hợp ghép lãi mỗi quý, chỉ cần nhập 4 vào ô B5:

Xem thêm: Tổng hợp những bài hướng dẫn về Excel cơ bản hay nhất mọi thời đại

Để tính giá trị tương lai của khoản đầu tư của bạn với ghép lãi mỗi nửa năm, hãy nhập 2 vào ô Compounding periods per year. Đối với lãi suất hàng tuần, nhập 52, đây là số tuần tương ứng mỗi năm. Nếu bạn quan tâm đến việc ghép lãi hàng ngày, hãy nhập 365, v.v.

Như bạn thấy, chúng tôi đã tạo ra một máy tính phổ biến tính lãi kép thực sự cho Excel. Hy vọng rằng bây giờ bạn không hề hối tiếc vì đã đầu tư vài phút để tìm ra công thức phức tạp mà các nhà lập kế hoạch tài chính sử dụng:)

Mở rộng công thức tính lãi kép cho Excel

Nếu vì lý do nào đó bạn không hài lòng với công thức lãi suất chung được thảo luận ở trên, bạn có thể tạo ra máy tính lãi suất hợp chất Excel của bạn với hàm Giá trị tương lai – có sẵn trong Microsoft Excel 2013, 2010, 2007, 2003 và 2000.

Hàm FV của Excel trả về giá trị tương lai của một khoản đầu tư dựa trên các yếu tố tương tự như những gì chúng ta vừa thảo luận, mặc dù cú pháp của nó hơi khác:

FV(rate, nper, pmt, [pv], [type])

Trong công thức trên, 3 đối số đầu tiên là bắt buộc và 2 đối số sau cùng là tùy chọn.

• rate – lãi suất cho mỗi kỳ, y như tên của đối số.
• nper – số kì thanh toán.
• pmt – một khoản thanh toán bổ sung được thực hiện mỗi kỳ, đại diện như một số âm. Nếu đối số pmt bị bỏ qua, tham số pv phải được đưa vào.
• pv (tùy chọn) – giá trị hiện tại của đầu tư (đầu tư cơ bản), cũng là một con số âm. Nếu đối số pv bị bỏ qua, nó được giả định bằng không (0), trong trường hợp này tham số pmt phải được chỉ định.
• type (tùy chọn) – quy định khi nào các khoản thanh toán phụ thêm phải trả: 0 hoặc bỏ qua – vào cuối kỳ, và 1 – vào đầu kỳ.

Hãy sử dụng công thức Excel FV với các giá trị giống như trong các ví dụ về tính lãi kép hàng tháng và xem liệu kết quả có tương tự hay không nhé:

Giả sử, chúng tôi gửi $ 2.000 TRONG 5 năm vào tài khoản tiết kiệm với mức lãi suất 8%/năm, kế hoạch ghép lãi hàng tháng, và không có khoản thanh toán bổ sung. Vì vậy, công thức tính lãi kép trong Excel của chúng tôi như sau:

= FV (0,08 /12, 5 * 12 , , -2000)

Nếu bạn cần giải thích của các tham số, hãy xem phần bên dưới:

• rate – 0.008 / 12 vì bạn có lãi suất 8%/năm, ghép lãi hàng tháng.
• nper – 5 * 12, tức là 5 năm * 12 tháng
• pmt được để trống vì nhiệm vụ của chúng tôi không liên quan đến khoản thanh toán bổ sung cho khoản đầu tư cơ bản 2.000 đô la.
• pv là -2000 vì cú pháp của hàm FV yêu cầu sử dụng một số âm trong đối số này.

Như hình bên dưới đây, thì công thức FV trả về kết quả tương tự như máy tính tính lãi kép mà chúng ta đã tạo ra trong ví dụ trước.

Tất nhiên, bạn có thể thay thế các giá trị trong hàm FV bằng các tham chiếu theo ô.

Xem thêm: Giáo trình Excel nâng cao mới nhất

Ví dụ,  hình dưới đây cho thấy giá trị tương lai của khoản đầu tư $ 4.000 sau 15 năm với mức lãi suất hàng năm là 7%, kế hoạch ghép lãi hàng tuần:

Để làm cho công thức tính lãi kép của bạn mạnh hơn, bạn có thể mở rộng nó bằng tùy chọn Additional contribution (thanh toán bổ sung) và sửa đổi công thức tính lãi kép cho phù hợp:

= FV (B4 / B5, B6 * B5, -B8, -B3, B9)

Trong đó:

• B3 – khoản đầu tư cơ bản
• B4 – lãi suất hàng năm
• B5 – số lần ghép lãi mỗi năm
• B6 – số năm gửi tiết kiệm
• B8 – khoản đóng góp bổ sung (tùy chọn)
• B9 – loại đóng góp bổ sung. Hãy nhớ rằng bạn nhập 1 nếu bạn gửi một khoản tiền bổ sung vào đầu kỳ tính lãi, và 0 hoặc bỏ qua nếu việc bổ sung được thực hiện vào cuối kỳ.

Rất nhiều kiến thức phải không nào? Toàn bộ những kiến thức này các bạn đều có thể học được trong khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: Hoc Excel.Online