Cách tính tỷ lệ tăng trưởng luỹ kế hàng năm, CAGR (compound annual growth rate) bằng Excel và công thức ví dụ

Trong bài viết này, blog.hocexcel.online sẽ giúp bạn hiểu được tỉ lệ tăng trưởng luỹ kế hàng năm (CAGR) là gì và cách xây dựng công thức CAGR rõ ràng, dễ hiểu trong Excel

Nói một cách đơn giản, CAGR được dùng để đo tỉ lệ hoàn vốn khi bạn thực hiện một đầu tư nào đó trong một khoảng thời gian nhất định. Cụ thể hơn, đây không phải một thuật ngữ kế toán nhưng lại được thường xuyên sử dụng bởi các nhà phân tích tài chính, các nhà quản lý đầu tư hay chủ doanh nghiệp giúp họ biết được công việc kinh doanh có phát triển hay không hoặc để so sánh với sức tăng trưởng doanh thu của công ty đối thủ.  

Với bài hướng dẫn này, HEO sẽ không đào sâu vào vấn đề liên quan đến số học mà tập trung vào cách xây dựng một công thức CAGR trong Excel một cách hiệu quả dựa trên 3 giá trị: giá trị đầu tư lúc đầu, giá trị kết thúc và thời gian.

TỈ LỆ TĂNG TRƯỞNG LUỸ KẾ HÀNG NĂM LÀ GÌ?

Tỉ lệ tăng trưởng luỹ kế hàng năm (CAGR) là một thuật ngữ tài chính dùng để đo lường tỉ lệ tăng trưởng trung bình hàng năm của một đầu tư trong một khoảng thời gian.

Để hiểu rõ hơn logic của CAGR, chúng ta hãy cùng xem ví dụ sau đây. Giả sử những số liệu dưới đây nằm trong báo cáo tài chính của công ty bạn

Bảng dưới đây thể hiện cách tính tỉ lệ tăng trưởng sau mỗi năm sử dụng công thức tính tỉ lệ tăng phần trăm.

Nhưng làm cách nào để tính tỉ lệ tăng trưởng trong vòng 5 năm? Có hai cách đó là tính tỉ lệ tăng trưởng luỹ kế hoặc trung bình. Sử dụng tỉ lệ tăng trưởng luỹ kế có lợi hơn bởi hai lý do sau:

• Tỉ lệ tăng trưởng trung bình hàng năm (AAGR) là giá trị trung bình số học của một chuỗi các tỉ lệ tăng trưởng và dễ dàng tính được bằng công thức trung bình AVERAGE trong Excel. Tuy nhiên, nó không hề tính đến hiệu ứng luỹ kép dẫn đến sự tăng trưởng của một đầu tư có thể bị đánh giá cao hơn so với thực tế.
• Tỉ lệ tăng trưởng luỹ kế hàng năm (CAGR) là trá trị trung bình hình học thể hiện tỉ lệ hoàn vốn của một đầu tư được luỹ kế với tỉ lệ ổn định mỗi năm.

Xem thêm: Hiểu và phân tích dữ liệu, ra quyết định với biểu đồ Excel dành cho CFO và CEO

CÔNG THỨC CAGR:

Công thức tính CAGR được sử dụng trong kinh doanh, phân tích tài chính và đầu tư như sau:

Với

• BV: giá trị ban đầu của đầu tư
• EV: giá trị kết thúc của đầu tư
• N: số chu kì (ví dụ số năm, số quý hoặc tháng, ngày…)

Trong hình dưới đây, công thức Average và CAGR trả lại hai kết quả khác nhau

Để giúp dễ hiểu hơn, hình ảnh dưới đậy thể hiện cách CAGR được tính trong từng thời kì khác nhau

CÁCH TÍNH CAGR TRONG EXCEL:

Sau khi hiểu được ý tưởng cơ bản của khái niệm tỉ lệ tăng trưởng luỹ kế hàng năm, chúng ta hãy cùng xem cách tính tỉ lệ này trong trang tính Excel như thế nào. Có tất cả 4 cách để xây dựng công thức tính CAGR

• Sử dụng công thức CAGR
• Sử dụng hàm POWER
• Sử dụng hàm RATE
• Sử dụng hàm IRR

CÔNG THỨC 1: Cách tạo trực tiếp công thức tính CAGR

Từ công thức CAGR ở trên, chúng ta chỉ cần vài phút là tạo được công thức tính CAGR trong Excel . Chỉ cần chỉ rõ các giá trị sau trong trang tính:

• BV: giá trị ban đầu của đầu tư
• EV: giá trị kết thúc của đầu tư
• n: số chu kì (ví dụ số năm, số quý hoặc tháng, ngày…)

Sau đó viết công thức CAGR vào ô trống như sau:

=(EV/BV)^(1/n)-1

Trong ví dụ này, BV ở ô B1, EV ở ô B2, và n ô B3. Do đó, chúng ta nhập công thức sau vào ô B5

=(B2/B1)^(1/B3)-1

Nếu bạn có tất cả các khoản đầu tư được liệt kê trong một cột, bạn có thể linh hoạt công thức CAGR để tính số chu kì một cách tự động

=(EV/BV)^(1/(ROW(EV)-ROW(BV)))-1

Tip: Nếu kết quả là số thập phân, sử dụng định dạng Percentage (phần trăm)  cho ô công thức.

HIỂU VÀ PHÂN TÍCH DỮ LIỆU, RA QUYẾT ĐỊNH VỚI BIỂU ĐỒ EXCEL

CÔNG THỨC 2: Hàm POWER

Một cách khác giúp các bạn tính CAGR nhanh chóng là sử dụng hàm POWER cho kết quả là một số được nâng lên theo luỹ thừa.

Cú pháp của hàm POWER như sau:

POWER (number, power)

Với number là cơ số và power là số mũ.

Để tạo công thức tính CAGR dựa trên hàm POWER, xác định tham số như sau:

• Number: giá trị kết thúc (EV)/giá trị bắt đầu (BV)
• Power : 1/số chu kì (n)

=POWER (EV/BV, 1/n)-1

Dưới đây là ví dụ cách tính CAGR dựa trên hàm POWER

=POWER (B7/B2, 1/5)-1

Như trong ví dụ trước, bạn có thể sử dụng hàm ROW để tính số chu kì

=POWER (B7/B2, 1/(ROW (B7)-ROW (B2)))-1

CÔNG THỨC 3: Hàm RATE

Một cách khác để tính CAGR là sử dụng hàm RATE cho kết quả là tỉ lệ lãi suất mỗi chu kì của một niên kim

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

Từ cái nhìn đầu tiên, cú pháp này trông có vẻ phức tạp nhưng một khi bạn đã hiểu các tham số của nó, bạn có thể sẽ thích cách này để tính CAGR hơn

• Nper (bắt buộc) : tổng số lần trả cho một niên kim. Ví dụ số chu kì của một khoản vay hoặc đầu tư cần phải trả

• Pmt: khoản tiền phải trả mỗi kì. Nếu bỏ trống, tham số fv phải được cung cấp

• Pv (bắt buộc): giá trị hiện tại của khoản đầu tư

• Fv: giá trị tương lai của khoản đầu tư sau khi thực hiện lần thanh toán cuối cùng. Nếu bị bỏ trống, công thức sẽ tự động đặt giá trị mặc định 0

• Type: giá trị tuỳ chọn xác định thời điểm các khoản thanh toán đến hạn

• 0 (mặc định): các khoản thanh toán đến kì hạn cuối mỗi chu kì

• 1: các khoản thanh toán đến kì hạn đầu mỗi chu kì  

• Guess: dự đoán của bạn về tỉ lệ. Nếu bỏ qua, công thức giả định là 10%

Để biến hàm RATE thành công thức tính CAGR, bạn cần xác định tham số đầu (nper), thứ 3 (pv) và thứ 4 (fv) như sau:

=RATE (n,, -BV, EV)

Với

• BV là giá trị đầu của đầu tư
• EV là giá trị cuối của đầu tư
• n là số chu kì

Lưu ý: Bạn cần xác định rõ giá trị ban đầu (BV) là số âm, nếu không hàm CAGR sẽ trả về lỗi #NUM!

Để tính tỉ lệ tăng trưởng luỹ kế trong ví dụ này, công thức sẽ như sau:

=RATE (5,,-B2, B7)

Để tránh khỏi rắc rối khi tính số chu kì bằng tay, bạn có thể sử dụng hàm ROW:

=RATE (ROW (B7)-ROW (B2),,-B2, B7)

Xem thêm: Học Excel kế toán

CÔNG THỨC 4: Hàm IRR

Hàm IRR trong Excel cho kết quả là tỉ suất hoàn vốn nội bộ cho một chuỗi các dòng tiền xảy ra tại các khoảng thời gian đều đặn (ví dụ ngày, tháng, quý, năm, v.v). Cú pháp như sau

IRR(values, [quess])

Với

• Values: một dãy các số thể hiện cho dòng tiền. Dãy này phải chứa ít nhất một giá trị âm và một giá trị dương.

• [Guess]: tham số tuỳ chọn thể hiện dự đoán của bạn về tỉ suất hoàn vốn. Nếu bỏ trống, gái trị mặc định sẽ là 10%

Do hàm IRR không được xây dựng để tính tỉ lệ tăng trưởng luỹ kế, bạn sẽ phải biến đổi dữ liệu như sau:

• Giá trị ban đầu của đầu tư là số âm
• Giá trị cuối của đầu tư là một số dương
• Tất cả giá trị nằm giữa là số 0

Sau khi dữ liệu được sắp xếp lại, bạn có thể tính CAGR với công thức đơn giản sau:

=IRR (B2:B7)

Với B2 là giá trị ban đầu và B7 là giá trị cuối của khoản đầu tư

Như vậy, bạn đã biết cách tính CAGR trong Excel. Nếu bạn theo dõi các ví dụ, bạn có thể nhận thấy rằng cả 4 công thức đều trả về cùng một kết quả là 17.61%.