Cách tính trung bình năm trong Excel

Khi bạn đang phân tích dữ liệu hoặc lập kế hoạch cho tương lai, sẽ rất hữu ích khi biết một số công thức trong Excel sẽ tính tốc độ tăng trưởng. Mặc dù một số được tích hợp vào chương trình, nhưng bạn sẽ cần các công thức phù hợp để đạt được tốc độ tăng trưởng trung bình mong muốn.  

Cách tính Tốc độ tăng trưởng trung bình gộp

Tốc độ tăng trưởng trung bình hàng năm (AAGR) và Tốc độ tăng trưởng trung bình gộp (CAGR) là những công cụ tuyệt vời để dự đoán tăng trưởng trong nhiều giai đoạn. Bạnbạn có thể tính tốc độ tăng trưởng trung bình hàng năm trong Excel bằng cách tính giá trị hiện tại và tương lai của một khoản đầu tư theo các khoảng thời gian mỗi năm.

CAGR có thể được coi là tốc độ tăng trưởng đi từ giá trị đầu tư ban đầu cho đến giá trị đầu tư cuối cùng khi bạn giả định rằng khoản đầu tư đã được gộp trong một khoảng thời gian. Trong hướng dẫn này, bạn sẽ học cách tính Tỷ lệ tăng trưởng trung bình hàng năm và Tỷ lệ tăng trưởng gộp hàng năm trong Excel

Cách tính Tốc độ tăng trưởng trung bình hàng năm

Tốc độ tăng trưởng trung bình hàng năm (AAGR) là mức tăng trung bình của một khoản đầu tư trong một khoảng thời gian. AAGR đo lường tỷ lệ lợi nhuận hoặc tăng trưởng trung bình trong các khoảng thời gian cách đều nhau không đổi.

Để xác định tỷ lệ phần trăm tăng trưởng cho mỗi năm, phương trình sử dụng là

Percentage Growth Rate = (Ending value / Beginning value) -1

Theo công thức này, tốc độ tăng trưởng trong các năm có thể được tính bằng cách chia giá trị hiện tại cho giá trị trước đó. Trong ví dụ này, tốc độ tăng trưởng mỗi năm sẽ là

Tăng trưởng cho Năm 1 = $250.000 / $200.000 – 1 = 25. 00%

Tăng trưởng cho Năm 2 = $265.000 / $250.000 – 1 = 6. 00%

Tăng trưởng cho Năm thứ 3 = $268.000 / $265.000 – 1 = 1. 13%

Tăng trưởng cho Năm 1 = $277.000 / $268.000 – 1 = 3. 36%

Tăng trưởng cho Năm 2 = $290.000 / $277.000 – 1 = 4. 69%

Tăng trưởng cho Năm thứ 3 = $325.000 / $290.000 – 1 = 12. 07%

AAGR được tính bằng cách chia tổng tốc độ tăng trưởng cho số năm

AAGR = (25% + 6. 00% + 1. 13%+ 3. 36% + 4. 69% + 12. 07%) / 6 = 8. 71%

Trong ví dụ này, các ô A1 đến B7 chứa dữ liệu của ví dụ nêu trên.

Để tính AAGR trong Excel

• Chọn ô C3 bằng cách nhấp chuột vào ô đó.
• Nhập công thức =(B3-B2)/B2 vào ô C3. Nhấn Enter để gán công thức cho ô C3.

• Kéo bộ điều khiển điền từ ô C3 đến ô C8 to copy the formula to the cells below.
• Cột C bây giờ sẽ có tốc độ tăng trưởng hàng năm. Chuyển đến ô F4.
• Gán công thức =AVERAGE(C3:C8). Nhấn Enter.

Điều này sẽ cho thấy tốc độ tăng trưởng trung bình hàng năm là 8. 71% trong ô F4

Cách tính Tốc độ tăng trưởng trung bình gộp

Tốc độ tăng trưởng trung bình gộp là tốc độ đi từ khoản đầu tư ban đầu đến khoản đầu tư kết thúc khi khoản đầu tư kết hợp theo thời gian. Phương trình của CAGR là

CAGR = ( EV / IV)1 / n – 1 ở đâu,

EV = Giá trị kết thúc

IV = Giá trị ban đầu

n = Khoảng thời gian

Ví dụ: nếu bạn đầu tư 225.000 đô la vào một doanh nghiệp trong 5 năm và giá trị cuối năm cho mỗi năm là

Bạn có thể tính CAGR như

CAGR = (500,000/225,000)1/5-1= .1731=17.32%

Các ô A1 đến B6 chứa dữ liệu nêu trên. Để tính CAGR,

• Đi tới ô E3. Chọn bằng chuột.
• Gán công thức =(B6/E2)^(1/(5-1))-1 cho ô E3. Nhấn Enter để gán công thức cho ô E3.

• Ô E3 sẽ có giá trị CAGR. Định dạng nó dưới dạng giá trị phần trăm bằng cách nhấp vào biểu tượng phần trăm (%) từ Trang chủ > Số .

Ô E3 bây giờ sẽ hiển thị tốc độ tăng trưởng kép hàng năm là 22. 08%

Cách tính Tốc độ tăng trưởng gộp hàng năm bằng Hàm XIRR

Bạn cũng có thể sử dụng hàm XIRR để tính CAGR trong Excel. Hàm XIRR trong Excel trả về tỷ lệ hoàn vốn nội bộ cho một loạt dòng tiền có thể không xảy ra đều đặn. Hàm XIRR sử dụng cú pháp =XIRR(value, date, [guess]). Nó sử dụng các giá trị và ngày tương ứng của chúng để tìm tỷ lệ hoàn vốn. Bạn có thể cung cấp giá trị bắt đầu và kết thúc cũng như ngày tương ứng làm đối số cho hàm XIRR để tìm CAGR. Để tìm CAGR từ ví dụ trước bằng hàm XIRR.

• Tạo bảng mới trong các ô A11 đến B13 với các giá trị đầu và cuối. Cột A phải chứa các ngày ở định dạng Ngày trong Excel để hàm XIRR hoạt động. Đảm bảo thêm dấu dấu trừ (-) trước giá trị kết thúc. Nếu không có điều này, bạn sẽ nhận được #NUM. lỗi.
• Chuyển đến ô E12.
• Gán công thức =XIRR(B12:B13,A12:A13) cho ô E12.

• Nhấn Enter để gán công thức cho E12.

Ô E12 bây giờ sẽ hiển thị giá trị CAGR 22. 09%

Bạn vẫn cần trợ giúp về định dạng Excel hoặc có câu hỏi khác về Excel? . Phiên đầu tiên của bạn luôn miễn phí.  

Bạn vẫn đang tìm trợ giúp về hàm VLOOKUP?