Các công thức Excel cơ bản mà mọi kế toán viên nên biết

Excel là một công cụ đắc lực hỗ trợ bạn tiết kiệm thời gian và tăng tốc độ công việc kế toán. Excel có thể tính toán chính xác các khoản đầu tư, doanh thu, lãi suất của doanh nghiệp chỉ trong 1 cú pháp đơn giản. Hãy cùng Gitiho khám phá các cú pháp Excel cơ bản dành cho kế toán trong bài viết này nhé!

Các công thức Excel cơ bản cho kế toán

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

Các kế toán viên có thể sử dụng công thức sau đây để tính toán giá trih tương lai của khoản đầu tư bằng công thức dưới đây trong Excel.

Công thức:

P * (1 + r) ^ n

Trong đó: 

P: Số tiền đầu tư chính

r: Tiền lãi có thể là hàng năm, hai năm một lần, hàng quý hoặc hàng tháng

n: Thời gian đầu tư là số thời kỳ mà khoản đầu tư được thực hiện

Ví dụ: Bạn đầu tư 200 triệu đồng trong 2 năm với lãi suất hàng năm là 5%, giá trị tương lai của khoản đầu tư này có thể được tính bằng cách nhập công thức sau vào bất kỳ ô tính trên Excel  = 200 * (1 + 5%) ^ 2 =  220,5 (triệu đồng).

Công thức Excel để tính khấu hao tài sản

Trong Excel, bạn có thể tính khấu hao của một tài sản trong một khoảng thời gian nhất định. Có 5 phương pháp khác nhau trong Excel để tính khấu hao của tài sản:

Khấu hao Đường thẳng (SLN)

Đây là phương pháp cơ bản nhất để tính khấu hao. Phương pháp này khấu hao giá trị tài sản theo một lượng cố định trong một khoảng thời gian.

Hàm SLN là hàm tính khấu hao tài sản theo phương pháp đường thẳng trong một khoảng thời gian nhất định.

Cú pháp: 

SLN(cost, salvage, life)

Trong đó:

Cost: là giá trị ban đầu của tài sản.

Salvage: là giá trị còn lại của tài sản tại thời điểm cuối đời hữu dụng.

Life: là tổng số chu kỳ sử dụng của tài sản (còn gọi là đời hữu dụng của tài sản).

Ví dụ: Giá tài sản ban đầu của bạn là 500 triệu đồng và có giá trị còn lại là 100 triệu đồng sau 10 năm, chúng ta có thể tính khấu hao theo đường thẳng hàng năm của tài sản là = SLN (500, 100, 10) = 40 triệu đồng

Xem thêm: Hướng dẫn sử dụng các hàm trong Excel để tính khấu hao

Khấu hao tài sản giảm dần (DB) 

Đây là một phương pháp khấu hao kép. Trong phương pháp này, giá trị của tài sản được giảm theo một tỷ lệ phần trăm cố định trong một khoảng thời gian.

Hàm DB trong Excel là hàm tính khấu hao của tài sản với kỳ hạn cụ thể bằng cách dùng phương pháp số dư giảm dần theo mức cố định.

Cú Pháp: 

DB(cost, salvage, life, period, [month])

Trong Đó:

• Cost: Chi phí ban đầu của sản phẩm, tham số bắt buộc
• Salvage: Giá trị sau khi khấu hao (giá trị thu hồi của tài sản), tham số bắt buộc
• Life: Số kỳ khấu hao, tham số bắt buộc
• Period: Kỳ muốn khấu hao, tham số bắt buộc
• Month: Số tháng của năm đầu tiên. Nếu bỏ qua thì mặc định là 12

Ví dụ: Giá tài sản ban đầu của bạn là 500 triệu đồng và có giá trị còn lại là 100 triệu sau 10 năm, chúng ta có thể tính khấu hao tài sản giảm dần trong năm 1 như sau = DB (500, 100, 10, 1) = 74.5 (triệu đồng)

Khấu hao số dư giảm kép (DDB)

Trong phương pháp này, giá ban đầu của một tài sản được tính theo tỷ lệ khấu hao đường thẳng kép. Do đó, trong phương pháp này có khấu hao cao nhất trong kỳ đầu tiên và ít hơn trong các kỳ kế tiếp.

Hàm DDB trong Excel có thể được sử dụng để tính khấu hao số dư giảm dần.

Cú Pháp: 

DDB(cost, salvage, life, period, [factor])

Trong Đó:

Cost: Chi phí ban đầu của sản phẩm, tham số bắt buộc

Salvage: Giá trị sau khi khấu hao (giá trị thu hồi của tài sản), tham số bắt buộc

Life: Số kỳ khấu hao, tham số bắt buộc

Period: Kỳ muốn khấu hao, tham số bắt buộc

Factor: Tỷ lệ giảm dần số dư. Nếu bỏ qua sẽ mặc định bằng 2 (phương pháp giảm kép)

Ví dụ: Giá tài sản ban đầu của bạn là 500 triệu đồng và có giá trị còn lại là 100 triệu sau 10 năm, chúng ta có thể tính khấu hao theo số dư giảm kép của tài sản trong năm 1 như sau = DDB (500, 100, 10, 1) = 100 (triệu đồng)

Khấu hao theo số dư giảm dần (VDB)

Hàm VDB trả về khấu hao của tài sản trong bất kỳ khoảng thời gian nào do người dùng chỉ định bằng cách sử dụng phương pháp số dư giảm dần.

Cú pháp:

VDB(Cost, Salvage, Life, Start_period, End_period, [Factor], [No_switch])

Trong đó:

Cost: Chi phí ban đầu của tài sản, là tham số cố định.

Salvage: Giá trị thu hồi của tài sản là giá trị sau khi khấu hao của tài sản, là tham số bắt buộc.

Life: Số kỳ khấu hao của tài sản, là tham số bắt buộc.

Start_period: Kỳ đầu tiên muốn tính khấu hao của tài sản, là tham số bắt buộc.

End_period: Kỳ cuối cùng muốn tính khấu hao, là tham số bắt buộc.

Factor: Tỷ lệ giảm dần của số dư, là tham số tùy chọn, nếu bỏ qua được mặc định là 2.

No_switch: Giá trị logic dùng để xác định, khi số khấu hao lớn hơn mức giảm dần của số dư có chuyển sang phương pháp khấu hao theo đường thẳng hay không.

+ Trường hợp No_switch = TRUE: Không chuyển sang phương pháp khấu hao theo đường thẳng ngay cả khi giá trị khấu hao lớn hơn số dư giảm dần.

+ Trường hợp No_switch = FALSE hoặc bỏ qua: Thực hiện chuyển sang phương pháp khấu hao theo đường thăng khi giá trị khấu hao lớn hơn số dư giảm dần.

Ví dụ: Giá tài sản ban đầu của bạn là 500 triệu đồng và có giá trị còn lại là 100 triệu sau 10 năm, chúng ta có thể tính khấu hao theo số dư giảm dần của tài sản trong năm 1 như sau = VDB (500, 100, 10, 1, 3) = 144 (triệu đồng)

Khấu hao tài sản theo thời gian còn lại (SYD)

Hàm SYD trong Excel thực hiện tính khấu hao của tài sản theo thời gian còn lại trong khoảng thời gian xác định.

Cú pháp: 

= SYD(cost, salvage, life, per)

Trong đó:

Cost: Giá trị ban đầu của tài sản, tham số bắt buộc.

Salvage: Giá trị khấu hao của tài sản hay còn gọi là giá trị thu hồi của tài sản.

Life: Kỳ hạn của tài sản, tham số bắt buộc.

Per: Số kỳ tính khấu hao, bắt buộc có cùng đơn vị với kỳ hạn của tài sản, là tham số bắt buộc.

Ví dụ : Giá tài sản ban đầu của bạn là 500 triệu đồng và có giá trị còn lại là 100 triệu sau 10 năm, chúng ta có thể tính khấu hao theo thời gian còn lại của tài sản như sau = SYD (500, 100, 10, 1) = 72.73 (triệu đồng)

Xem thêm: Tính khấu hao tài sản cố định theo phương pháp khấu hao đường thẳng

Tính giá ròng của khoản đầu tư không định kỳ trên Excel (XNPV)

Hàm XNPV dùng để tính giá ròng của một khoản đầu tư bằng cách sử dụng tỷ lệ chiết khấu với các khoản chi trả (hoặc thu nhập) không định kỳ. Nếu muốn tính giá ròng của một khoản đầu tư bằng cách sử dụng tỷ lệ chiết khấu với các khoản chi trả (hoặc thu nhập) theo những kỳ hạn đều đặn, bạn dùng hàm NPV.
Cú pháp: 

= XNPV(rate, values, dates)

Rate : Tỷ suất chiết khấu trong suốt thời gian sống của khoản đầu tư (suốt thời gian thực hiện dự án chẳng hạn). Tỷ suất này có thể thể hiện tỷ lệ lạm phát hoặc lãi suất đầu tư lạm phát.

Values : Các khoản chi trả hoặc thu nhập trong các kỳ hạn của khoản đầu tư, tương ứng với lịch chi trả trong dates. 

Dates : Ngày chi trả tương ứng.

Tính tỉ suất hoàn vốn nội bộ trong Excel (XIRR)

Hàm XIRR trả về tỷ lệ hoàn vốn nội bộ cho một loạt các luồng tiền có thể hoặc không thể định kỳ.

Cú pháp: 

XIRR(values, dates, [guess])

Trong đó:

• Value (bắt buộc): một mảng hoặc một phạm vi ô đại diện cho một loạt các luồng vào và ra.
• Dates(bắt buộc): Ngày tương ứng với dòng tiền. Ngày có thể xảy ra theo bất kỳ thứ tự nào, nhưng ngày đầu tư ban đầu phải là đầu tiên trong mảng.
• Guess (tùy chọn): Cung cấp dưới dạng phần trăm hoặc số thập phân. Nếu bỏ qua, Excel sẽ sử dụng tỷ lệ mặc định là 0,1 (10%).

Tính tỉ suất lưu hành nội bộ trên Excel(IRR)

Hàm IRR được dùng để tính tỷ suất lưu hành nội bộ của một dòng tiền nhằm đánh giá hiệu quả của phương án đầu tư hoặc dự án.

Cú pháp: 

=IRR(values; [guess])

Trong đó:

Values: Đối số bắt buộc. Là một mảng hoặc các tham chiếu đến các ô có chứa số liệu của dòng tiền. Giá trị đầu tư ban đầu: là 1 số âm. Những giá trị tiếp theo: là lợi nhuận hàng năm của dự án. Lưu ý các giá trị này phải theo trình tự thời gian.

Guess: Số % ước lượng gần với kết quả của IRR, thường mặc định là 10%.

Lưu ý:

• Values phải chứa ít nhất 1 giá trị âm và 1 giá trị dương.
• Hàm IRR sử dụng thứ tự các giá trị của values như là thứ tự lưu động tiền mặt. Do đó cần cẩn thận để các thứ tự chi trả hoặc thu nhập luôn được nhập đúng.
• Hàm IRR chỉ tính toán các giá trị số bên trong các mảng hoặc tham chiếu của values; còn các ô rỗng, các giá trị logic, text hoặc các giá trị lỗi đều sẽ bị bỏ qua.
• Hàm IRR có quan hệ mật thiết với hàm NPV, vì tỷ suất mà hàm IRR trả về chính là lãi suất làm sao để NPV=0. 

Tính tỉ suất hoàn vốn nội bộ có điều chỉnh trong Excel (MIRR)

Hàm MIRR là hàm tính toán trong Excel giúp bạn tính chính xác tỷ xuất hoàn vốn nội bộ có điều chỉnh liên quan đến các khoản vay, khoản đầu tư, lãi xuất của một chuỗi dòng tiền định kỳ

Cú Pháp: 

MIRR(values, finance_rate, reinvest_rate)

Trong Đó:

Values: Tham chiếu đến các ô chứa giá trị số (các khoản thanh toán - giá trị âm và thu nhập - giá trị dương), tham số bắt buộc

+ Các giá trị phải bao gồm cả giá trị âm và giá trị dương thì mới đủ điều kiện để tính tỷ suất hoàn vốn nội bộ điều chỉnh. Nếu không hàm MIRR báo lỗi #DIV /0!
+ Nếu đối số tham chiếu hoặc mảng chứa giá trị logic, văn bản, giá trị 0 hay ô trống sẽ bị bỏ qua

• Finance_rate: Lãi suất phải trả cho số tiền đã dùng trong các dòng tiền
• Reinvest_rate: Lãi suất nhận được từ các khoản tái đầu tư. 

Tính số tiền thanh toán hàng kỳ cho khoản vay trong Excel (PMT)

Hàm PMT dùng để tính số tiền cố định và phải trả định kỳ đối với một khoản vay có lãi suất không đổi. Cũng có thể dùng hàm PMT để tính số tiền cần đầu tư định kỳ (gửi tiết kiệm, chơi bảo hiểm..) để thu được một khoản tiền nào đó.
 

Cú pháp:

=PMT(rate, nper, pv, [fv], [type])

• Rate (Bắt buộc): Lãi suất hàng kỳ của khoản vay
• NPer (Bắt buộc): Tổng số kỳ thanh toán
• PV (Bắt buộc): Giá trị hiện tại của khoản vay (nợ gốc)
• FV (Tùy chọn): Giá trị tương lai (hoặc số dư) của khoản tiền sau khi thực hiện việc thanh toán đợt cuối cùng. Nếu không chọn thì hiểu mặc định là sẽ trả hết khoản vay
• Type (Tùy chọn): Số 0 hoặc số 1 - Thời điểm thanh toán là đầu kỳ hay cuối chu kỳ

Xem thêm: Hướng dẫn cách xác định các khoản vay phải thanh toán trong Excel bằng hàm PMT và IPMT.

Tính toán trả lãi cho khoản vay (IPMT)

Hàm IPMT trong Excel là hàm trả về thanh toán lãi cho một khoản đầu tư trong một kỳ hạn đã cho. 

Cú Pháp: 

IPMT(rate, per, nper, pv, [fv], [type])

Trong Đó:

• Rate: Lãi suất theo kỳ hạn, tham số bắt buộc
• Per: Kỳ hạn muốn tính lãi, tham số bắt buộc, thuộc khoảng từ 1 đến nper
• Nper: Tổng số kỳ hạn thanh toán, tham số bắt buộc
• Pv: Giá trị hiện tại, tham số bắt buộc
• fv: Giá trị tương lai thu được. Nếu bỏ qua sẽ mặc định là 0
• type: Thời điểm thanh toán. Nếu bỏ qua sẽ mặc định là 0

+ type = 0: Thanh toán cuối kỳ
+ type = 1: Thanh toán đầu kỳ

Tính lãi suất thực tế hàng năm bằng Excel (EFFECT)

Hàm EFFECT trong Excel giúp bạn tính lãi suất thực tế hàng năm, hàm này được ứng dụng rất nhiều trong thực tế.

Cú Pháp: 

EFFECT (nominal_rate, npery)

Trong đó:

• Nominal_rate: bắt buộc. Lãi suất danh nghĩa.
• Npery: bắt buộc. Số kỳ hạn tính lãi kép mỗi năm.

Tính lãi suất trong Excel (RATE)

Hàm RATE tính toán lãi suất theo chu kỳ của một khoản cho vay hoặc đầu tư.

Cú pháp:

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

Trong đó:

• Nper: là tổng số kỳ chi trả.
• Pmt: là số tiền chi trả (hay thu về) trong mỗi kỳ, cố định trong suốt giai đoạn.
• Pv: là giá trị hiện tại .
• Fv: là giá trị tương lai hoặc cần bằng tiền mặt mà ta muốn tìm sau lần chi trả cuối cùng. Nếu Fv không có thì phải cho có Pmt.
• Type: bằng 1 nếu số tiền trả đầu kỳ, bằng 0 nếu cuối kỳ.

Tính giá trị tương lai của một khoản đầu tư (FV)

“FV” là viết tắt của “Future Value” tức là" Giá trị tương lai". Như tên gọi cho thấy, công thức này tính toán giá trị tương lai của khoản đầu tư dựa trên lãi suất không đổi.

Cú pháp: 

=FV(Rate,Nper,Pmt,Pv,Type)

Trong đó:

• Rate: tỷ lệ lãi suất mỗi kỳ (tính theo tháng, quý, năm).
• Nper: tổng số kỳ chi trả.
• Pmt: số tiền chi trả trong mỗi kỳ, cố định trong suốt giai đoạn đầu tư.
• Pv: giá trị đầu tư ban đầu. Nếu để trống thì Pv=0
• Type: =1 nếu số tiền trả đầu kỳ,=0 nếu số tiền trả cuối kỳ.

Xem thêm: Tính giá trị tương lai của khoản đầu tư bằng hàm FV trong Excel đơn giản

Kết luận

Trong bài viết trên, Gitiho đã giới thiệu đến bạn các hàm tài chính trong Excel dành riêng cho kế toán viên để tính lãi suất, khấu hao tài sản, giá trị tương lai, hay số tiền cần phải trả cho các khoản vay. Hy vọng bạn áp dụng thành công cho công việc của mình và đừng quên theo dõi chúng mình để xem thêm các bài viết hữu ích về nghiệp vụ chuyên ngành kế toán cũng như tin học văn phòng nhé!