Cách tạo lịch khấu hao khoản vay trong Excel

Tạo lịch trả dần khoản vay trong Excel có thể là một kỹ năng có giá trị, cho phép bạn hình dung và quản lý các khoản trả nợ của mình một cách hiệu quả. Lịch trình khấu hao là một bảng trình bày chi tiết từng khoản thanh toán định kỳ cho khoản vay khấu hao (thường là khoản vay thế chấp hoặc khoản vay mua ô tô). Nó chia mỗi khoản thanh toán thành các thành phần lãi và gốc, hiển thị số dư còn lại sau mỗi lần thanh toán. Hãy cùng đi sâu vào hướng dẫn từng bước để tạo một lịch trình như vậy trong Excel.

Lịch khấu hao là gì?

Lịch khấu hao là một bảng chi tiết được sử dụng trong tính toán khoản vay, hiển thị quá trình trả hết khoản vay theo thời gian. Lịch trình khấu hao thường được sử dụng cho các khoản vay có lãi suất cố định như thế chấp, vay mua ô tô và cho vay cá nhân, trong đó số tiền thanh toán không đổi trong suốt thời hạn cho vay, nhưng tỷ lệ thanh toán theo lãi so với tiền gốc thay đổi theo thời gian.

Thực tế, để tạo lịch khấu hao khoản vay trong Excel, các hàm tích hợp PMT, PPMT và IPMT là rất quan trọng. Hãy hiểu chức năng của từng chức năng:

Chức năng PMT: Hàm này được sử dụng để tính toán tổng số tiền thanh toán mỗi kỳ cho khoản vay dựa trên các khoản thanh toán cố định và lãi suất không đổi.
Chức năng IPMT: Hàm này tính toán phần lãi của khoản thanh toán trong một khoảng thời gian nhất định.
Chức năng PPMT: Hàm này được sử dụng để tính phần gốc của khoản thanh toán trong một khoảng thời gian cụ thể.

Bằng cách sử dụng các hàm này trong Excel, bạn có thể tạo lịch khấu hao chi tiết hiển thị các thành phần lãi và gốc của mỗi khoản thanh toán, cùng với số dư còn lại của khoản vay sau mỗi lần thanh toán.

Tạo lịch khấu hao trong Excel

Trong phần này, sẽ giới thiệu hai phương pháp riêng biệt để tạo lịch khấu hao trong Excel. Các phương pháp này đáp ứng các sở thích và trình độ kỹ năng khác nhau của người dùng, đảm bảo rằng bất kỳ ai, bất kể trình độ sử dụng Excel đến đâu, đều có thể xây dựng thành công lịch trình khấu hao chi tiết và chính xác cho khoản vay của họ.

Tạo lịch trình khấu hao bằng cách sử dụng công thức

Các công thức cung cấp sự hiểu biết sâu sắc hơn về các tính toán cơ bản và mang lại sự linh hoạt để tùy chỉnh lịch trình theo yêu cầu cụ thể. Cách tiếp cận này lý tưởng cho những ai muốn có trải nghiệm thực tế và hiểu biết rõ ràng về cách mỗi khoản thanh toán được chia thành các thành phần gốc và lãi. Bây giờ, hãy chia nhỏ quy trình tạo lịch khấu hao trong Excel từng bước:

Bước 1: Thiết lập thông tin khoản vay và bảng phân bổ

Nhập thông tin khoản vay tương đối, chẳng hạn như lãi suất hàng năm, thời hạn cho vay theo năm, số lần thanh toán mỗi năm và số tiền cho vay vào các ô như ảnh chụp màn hình sau:
Sau đó, tạo bảng phân bổ trong Excel với các nhãn được chỉ định, chẳng hạn như Kỳ hạn, Thanh toán, Tiền lãi, Tiền gốc, Số dư còn lại trong các ô A7:E7.
Trong cột Kỳ, nhập số kỳ. Với ví dụ này, tổng số lần thanh toán là 24 tháng (2 năm) nên bạn sẽ nhập các số từ 1 đến 24 vào cột Kỳ. Xem ảnh chụp màn hình:
Khi bạn đã thiết lập bảng có nhãn và số kỳ, bạn có thể tiếp tục nhập công thức và giá trị cho các cột Thanh toán, Lãi suất, Tiền gốc và Số dư dựa trên thông tin cụ thể về khoản vay của bạn.

Bước 2: Tính tổng số tiền thanh toán bằng hàm PMT

Cú pháp của PMT là:

=-PMT(lãi suất mỗi kỳ, tổng số lần thanh toán, số tiền vay)

lãi suất mỗi kỳ: Nếu lãi suất khoản vay của bạn là hàng năm, hãy chia cho số lần thanh toán mỗi năm. Ví dụ: nếu lãi suất hàng năm là 5% và thanh toán hàng tháng thì lãi suất mỗi kỳ là 5%/12. Trong ví dụ này, tỷ lệ sẽ được hiển thị là B1/B3.
tổng số lần thanh toán: Nhân thời hạn vay tính bằng năm với số lần thanh toán trong năm. Trong ví dụ này, nó sẽ được hiển thị là B2*B3.
số tiền vay: Đây là số tiền gốc bạn đã vay. Trong ví dụ này là B4.
Dấu âm(-): Hàm PMT trả về số âm vì nó thể hiện khoản thanh toán đi. Bạn có thể thêm dấu âm trước hàm PMT để hiển thị khoản thanh toán dưới dạng số dương.

Nhập công thức sau vào ô B7, sau đó kéo chốt điền xuống để điền công thức này sang các ô khác và bạn sẽ thấy số tiền thanh toán không đổi cho tất cả các kỳ. Xem ảnh chụp màn hình:

= -PMT($B$1/$B$3, $B$2*$B$3, $B$4)

Bước 3: Tính lãi bằng hàm IPMT

Ở bước này, bạn sẽ tính lãi cho từng kỳ thanh toán bằng hàm IPMT của Excel.

=-IPMT(lãi suất mỗi kỳ, giai đoạn cụ thể, tổng số lần thanh toán, số tiền vay)

Lãi suất mỗi kỳ: Nếu lãi suất khoản vay của bạn là hàng năm, hãy chia cho số lần thanh toán mỗi năm. Ví dụ: nếu lãi suất hàng năm là 5% và thanh toán hàng tháng thì lãi suất mỗi kỳ là 5%/12. Trong ví dụ này, tỷ lệ sẽ được hiển thị là B1/B3.
Giai đoạn cụ thể: Khoảng thời gian cụ thể mà bạn muốn tính lãi. Điều này thường sẽ bắt đầu bằng 1 ở hàng đầu tiên trong lịch trình của bạn và tăng thêm 1 ở mỗi hàng tiếp theo. Trong ví dụ này, khoảng thời gian bắt đầu từ ô A7.
Tổng số lần thanh toán: Nhân thời hạn vay tính bằng năm với số lần thanh toán trong năm. Trong ví dụ này, nó sẽ được hiển thị là B2*B3.
Số tiền vay: Đây là số tiền gốc bạn đã vay. Trong ví dụ này là B4.
Dấu âm(-): Hàm PMT trả về số âm vì nó thể hiện khoản thanh toán đi. Bạn có thể thêm dấu âm trước hàm PMT để hiển thị khoản thanh toán dưới dạng số dương.

Nhập công thức sau vào ô C7, sau đó kéo chốt điền xuống cột để điền công thức này và nhận lãi cho từng kỳ.

=-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

Bước 4: Tính tiền gốc bằng hàm PPMT

Sau khi tính lãi cho từng kỳ, bước tiếp theo trong việc lập lịch phân bổ là tính phần gốc của mỗi khoản thanh toán. Việc này được thực hiện bằng cách sử dụng hàm PPMT, được thiết kế để xác định phần gốc của khoản thanh toán trong một khoảng thời gian cụ thể, dựa trên các khoản thanh toán cố định và lãi suất không đổi.

Cú pháp của IPMT là:

=-PPMT(lãi suất mỗi kỳ, giai đoạn cụ thể, tổng số lần thanh toán, số tiền vay)

Cú pháp và tham số cho công thức PPMT giống hệt với cú pháp được sử dụng trong công thức IPMT đã thảo luận trước đó.

Nhập công thức sau vào ô D7, sau đó kéo chốt điền xuống cột để điền giá trị gốc cho từng khoảng thời gian. Xem ảnh chụp màn hình:

=-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4)

Bước 5: Tính số dư còn lại

Sau khi tính cả lãi và gốc của mỗi lần thanh toán, bước tiếp theo trong lịch trả nợ của bạn là tính số dư còn lại của khoản vay sau mỗi lần thanh toán. Đây là một phần quan trọng của lịch trình vì nó cho thấy số dư khoản vay giảm dần theo thời gian như thế nào.

Trong ô đầu tiên của cột số dư – E7, nhập công thức sau, nghĩa là số dư còn lại sẽ là số tiền vay ban đầu trừ đi phần gốc của khoản thanh toán đầu tiên:
Đối với kỳ thứ hai và tất cả các kỳ tiếp theo, hãy tính số dư còn lại bằng cách trừ số tiền gốc của kỳ hiện tại khỏi số dư của kỳ trước. Hãy áp dụng công thức sau vào ô E8:
Và sau đó kéo núm điều khiển điền xuống cột. Như bạn có thể thấy, mỗi ô sẽ tự động điều chỉnh để tính số dư còn lại dựa trên các khoản thanh toán gốc được cập nhật.

Bước 6: Lập hồ sơ vay

Sau khi thiết lập lịch khấu hao chi tiết, việc tạo bản tóm tắt khoản vay có thể cung cấp cái nhìn tổng quan nhanh chóng về các khía cạnh chính của khoản vay của bạn. Bản tóm tắt này thường sẽ bao gồm tổng chi phí của khoản vay, tổng tiền lãi phải trả.

Để tính tổng số tiền thanh toán:

=SUM(B7:B30)

Để tính tổng tiền lãi:

=SUM(C7:C30)

Kết quả:

Giờ đây, lịch trình khấu hao khoản vay đơn giản nhưng toàn diện đã được tạo thành công. xem ảnh chụp màn hình:

Tạo lịch khấu hao bằng mẫu Excel

Tạo lịch khấu hao trong Excel bằng mẫu là một phương pháp đơn giản và tiết kiệm thời gian. Excel cung cấp các mẫu dựng sẵn tự động tính toán lãi, gốc và số dư còn lại của mỗi khoản thanh toán. Dưới đây là cách tạo lịch khấu hao bằng mẫu Excel:

Nhấp chuột Tập tin > Mới, trong hộp tìm kiếm, nhập lịch khấu hao, và hãy nhấn đăng ký hạng mục thi chìa khóa. Sau đó, chọn mẫu phù hợp nhất với nhu cầu của bạn bằng cách nhấp vào nó. Ví dụ ở đây tôi sẽ chọn Mẫu máy tính khoản vay đơn giản. Xem ảnh chụp màn hình:
Khi bạn đã chọn mẫu, hãy nhấp vào Tạo để mở nó dưới dạng một sổ làm việc mới.
Sau đó, nhập chi tiết khoản vay của riêng bạn, mẫu sẽ tự động tính toán và điền lịch trình dựa trên thông tin đầu vào của bạn.
Cuối cùng, hãy lưu sổ làm việc về lịch khấu hao mới của bạn.

Tạo một lịch trình khấu hao cho một số kỳ khác nhau

Trong ví dụ trước, chúng ta tạo lịch trả nợ cho một số lần thanh toán cố định. Cách tiếp cận này hoàn hảo để xử lý một khoản vay hoặc thế chấp cụ thể mà các điều khoản không thay đổi.

Tuy nhiên, nếu bạn đang tìm cách tạo lịch khấu hao linh hoạt có thể được sử dụng nhiều lần cho các khoản vay với các khoảng thời gian khác nhau, cho phép bạn sửa đổi số lần thanh toán theo yêu cầu cho các trường hợp vay khác nhau, thì bạn cần phải thực hiện theo một phương pháp chi tiết hơn.

Bước 1: Thiết lập thông tin khoản vay và bảng phân bổ

Nhập thông tin khoản vay tương đối, chẳng hạn như lãi suất hàng năm, thời hạn cho vay theo năm, số lần thanh toán mỗi năm và số tiền cho vay vào các ô như ảnh chụp màn hình sau:
Sau đó, tạo bảng phân bổ trong Excel với các nhãn được chỉ định, chẳng hạn như Kỳ hạn, Thanh toán, Tiền lãi, Tiền gốc, Số dư còn lại trong các ô A7:E7.
Trong cột Kỳ, nhập số tiền thanh toán cao nhất mà bạn có thể cân nhắc cho bất kỳ khoản vay nào, ví dụ: điền các số từ 1 đến 360. Điều này có thể bao gồm khoản vay 30 năm tiêu chuẩn nếu bạn thanh toán hàng tháng.

Bước 2: Sửa công thức thanh toán, lãi, gốc bằng hàm IF

Nhập các công thức sau vào các ô tương ứng, sau đó kéo núm điều khiển điền để mở rộng các công thức này xuống số kỳ thanh toán tối đa mà bạn đã đặt.

Công thức thanh toán:

Thông thường, bạn sử dụng hàm PMT để tính toán khoản thanh toán. Để kết hợp câu lệnh IF, công thức cú pháp là:

= NẾU (giai đoạn hiện tại <= tổng thời gian, -PMT(lãi suất mỗi kỳ, tổng thời gian, số tiền vay), “” )

Vậy công thức là thế này:

=IF(A7<=$B$2*$B$3, -PMT($B$1/$B$3, $B$2*$B$3, $B$4), “”)

Công thức tính lãi:

Công thức cú pháp là:

= NẾU (giai đoạn hiện tại <= tổng thời gian, -IPMT(lãi suất mỗi kỳ, giai đoạn hiện tại, tổng thời gian, số tiền vay), “” )

Vậy công thức là thế này:

=IF(A7<=$B$2*$B$3,-IPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), “”)

Công thức nguyên tắc:

Công thức cú pháp là:

= NẾU (giai đoạn hiện tại <= tổng thời gian, -PPMT(lãi suất mỗi kỳ, giai đoạn hiện tại, tổng thời gian, số tiền vay), “” )

Vậy công thức là thế này:

=IF(A7<=$B$2*$B$3,-PPMT($B$1/$B$3, A7, $B$2*$B$3, $B$4), “”)

Bước 3: Điều chỉnh công thức số dư còn lại

Đối với số dư còn lại, bạn thường có thể trừ tiền gốc khỏi số dư trước đó. Với câu lệnh IF, hãy sửa đổi nó thành:

Ô cân bằng thứ nhất:(E7)

=B4-D7

Ô cân bằng thứ hai:(E8)

=IF(A8<=$B$2*$B$3, E7-D8, “”)

Bước 4: Lập hồ sơ vay

Sau khi bạn đã thiết lập lịch khấu hao với các công thức đã sửa đổi, bước tiếp theo là tạo bản tóm tắt khoản vay.

Để tính tổng số tiền thanh toán:

=SUM(B7:B366)

Để tính tổng tiền lãi:

=SUM(C7:C366)

Kết quả:

Giờ đây, bạn có lịch khấu hao toàn diện và linh hoạt trong Excel, kèm theo bản tóm tắt khoản vay chi tiết. Bất cứ khi nào bạn điều chỉnh thời hạn thanh toán, toàn bộ lịch trình khấu hao sẽ tự động cập nhật để phản ánh những thay đổi này. Xem bản demo bên dưới: