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ì?

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

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

Tạo lịch khấu hao với các khoản thanh toán bổ sung

Tạo lịch khấu hao (có thanh toán thêm) bằng mẫu 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, chúng tôi 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ọ.

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ổ

1. 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:
   
2. 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.
3. 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:
   
4. 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à:

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.

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à:

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.

1. 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:

   =B4-D7

   
2. Đố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:

   =E7-D8

    Chú thích: Tham chiếu đến ô cân bằng phải mang tính tương đối để nó cập nhật khi bạn kéo công thức xuống.
   
3. 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 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ổ

1. 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:
   
2. 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.
3. 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à:

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à:

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à:

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:

Tạo lịch khấu hao với các khoản thanh toán bổ sung

Bằng cách thực hiện các khoản thanh toán bổ sung ngoài các khoản đã định, khoản vay có thể được thanh toán nhanh hơn. Lịch khấu hao kết hợp các khoản thanh toán bổ sung, khi được tạo trong Excel, cho thấy các khoản thanh toán bổ sung này có thể đẩy nhanh quá trình hoàn trả khoản vay và giảm tổng tiền lãi phải trả như thế nào. Đây là cách bạn có thể thiết lập nó:

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

1. 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, số tiền cho vay và khoản thanh toán thêm vào các ô như ảnh chụp màn hình sau:
   
2. Sau đó, tính toán khoản thanh toán theo lịch trình.
   Ngoài các ô đầu vào, cần có một ô được xác định trước khác cho các tính toán tiếp theo của chúng tôi - số tiền thanh toán theo lịch trình. Đây là số tiền thanh toán thường xuyên cho khoản vay nếu không có khoản thanh toán bổ sung nào được thực hiện. Hãy áp dụng công thức sau vào ô B6:

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

   
   
3. Sau đó, tạo bảng khấu hao trong Excel:
   • Đặt các nhãn được chỉ định, chẳng hạn như Kỳ hạn, Thanh toán theo lịch trình, Thanh toán bổ sung, Tổng thanh toán, Tiền lãi, Tiền gốc, Số dư còn lại trong các ô A8:G8;
   • Trong cột Kỳ hạn, 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ừ 0 đến 360. Số này có thể chi trả cho khoản vay tiêu chuẩn có thời hạn 30 năm nếu bạn thanh toán hàng tháng;
   • Đối với Giai đoạn 0 (hàng 9 trong trường hợp của chúng tôi), truy xuất giá trị Số dư bằng công thức này = B4, tương ứng với số tiền vay ban đầu. Tất cả các ô khác trong hàng này phải để trống.
   • 

⭐️ Bước 2: Xây dựng công thức tính thời gian khấu hao có trả thêm

Nhập lần lượt các công thức sau vào các ô tương ứng. Để nâng cao khả năng xử lý lỗi, chúng tôi đưa công thức này và tất cả các công thức trong tương lai vào trong hàm IFERROR. Cách tiếp cận này giúp tránh nhiều lỗi tiềm ẩn có thể phát sinh nếu bất kỳ ô đầu vào nào bị bỏ trống hoặc chứa giá trị không chính xác.

● Tính toán khoản thanh toán theo lịch trình:

Nhập công thức sau vào ô B10:

=IFERROR(IF($B$6<=G9, $B$6, G9+G9*$B$1/$B$3), "")

● Tính số tiền trả thêm:

Nhập công thức sau vào ô C10:

=IFERROR(IF($B$5<G9-E10,$B$5, G9-E10), "")

● Tính tổng số tiền thanh toán:

Nhập công thức sau vào ô D10:

=IFERROR(B10+C10, "")

● Tính tiền gốc:

Nhập công thức sau vào ô E10:

=IFERROR(IF(B10>0, MIN(B10-F10, G9), 0), "")

● Tính lãi:

Nhập công thức sau vào ô F10:

=IFERROR(IF(B10>0, $B$1/$B$3*G9, 0), "")

● Tính số dư còn lại

Nhập công thức sau vào ô G10:

=IFERROR(IF(G9 >0, G9-E10-C10, 0), "")

Khi bạn đã hoàn thành từng công thức, hãy chọn phạm vi ô B10:G10 và sử dụng núm điều khiển điền để kéo và mở rộng các công thức này xuống toàn bộ tập hợp các khoảng thời gian thanh toán. Đối với bất kỳ khoảng thời gian nào không được sử dụng, các ô sẽ hiển thị số 0. Xem ảnh chụp màn hình:

⭐️ Bước 3: Lập hồ sơ vay

● Nhận số lần thanh toán theo lịch trình:

=B2:B3

● Nhận số tiền thanh toán thực tế:

=COUNTIF(D10:D369,">"&0)

● Nhận tổng số tiền thanh toán thêm:

=SUM(C10:C369)

● Nhận tổng lãi:

=SUM(F10:F369)

⭐️ Kết quả:

Bằng cách làm theo các bước này, bạn tạo lịch khấu hao động trong Excel để tính các khoản thanh toán bổ sung.

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:

1. 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:
   
2. 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.
3. 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.
4. 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.