Cách tạo lịch khấu hao trong Excel: Các bước được giải thích

Một lịch khấu hao cung cấp lộ trình tài chính rõ ràng, cho thấy mỗi khoản thanh toán khoản vay được chia như thế nào giữa tiền lãi và tiền gốc cho đến khi bạn trả hết nợ.

Với các hàm tích hợp của Excel, bạn có thể tạo các lịch khấu hao chi tiết để hình dung vòng đời khoản vay, đưa ra quyết định tài chính sáng suốt, và thậm chí khám phá các phương án trả sớm.

Trong bài viết này, bạn sẽ học cách xây dựng từ đầu một lịch khấu hao toàn diện trong Excel. Chúng tôi sẽ đề cập đến các hàm thiết yếu như PMT(), IPMT() và PPMT(), hướng dẫn bạn qua quy trình từng bước và khám phá các kịch bản thực tế để nâng cao bộ công cụ lập kế hoạch tài chính của bạn.

Chính xác thì lịch khấu hao là gì?

Lịch khấu hao là một bảng đầy đủ các khoản thanh toán định kỳ của khoản vay, cho thấy tiền gốc và tiền lãi cấu thành mỗi khoản thanh toán cho đến khi khoản vay được tất toán vào cuối kỳ hạn.

Khi bạn vay tiền, một số yếu tố chính quyết định cách các khoản thanh toán hoạt động:

• Tiền gốc: Số tiền gốc ban đầu bạn vay và phải hoàn trả
• Lãi: Chi phí đi vay, được tính theo tỷ lệ phần trăm trên tiền gốc
• Kỳ thanh toán: Tần suất bạn thanh toán (hàng tháng, hai tuần một lần, v.v.)
• Kỳ hạn khoản vay: Tổng khoảng thời gian bạn sẽ hoàn trả khoản vay

Với hầu hết các khoản vay tiêu chuẩn—như thế chấp, vay mua xe và vay cá nhân—khoản thanh toán giữ nguyên trong suốt kỳ hạn. Tuy nhiên, điều thay đổi qua mỗi kỳ thanh toán là tỷ trọng giữa lãi và gốc. Giai đoạn đầu, tỷ lệ lãi trong mỗi khoản thanh toán cao hơn. Khi tiến dần về cuối kỳ hạn, phần gốc trong mỗi khoản thanh toán tăng lên, giúp giảm dư nợ nhanh hơn.

Mục đích chính của việc tạo lịch khấu hao là tăng tính minh bạch cho khoản vay của bạn. Nó cho phép bạn thấy chính xác tổng lãi phải trả theo thời gian, tốc độ bạn tích lũy giá trị vốn (equity), và tác động của các khoản trả thêm đến tiến độ khoản vay. Với doanh nghiệp, các lịch này cũng phục vụ cho kế toán và lập kế hoạch thuế.

Các hàm và công thức Excel bạn cần biết

Excel không có một hàm "amortization" tích hợp sẵn. Thay vào đó, bạn thường xây dựng lịch khấu hao bằng cách kết hợp các hàm tài chính cụ thể để tính các khía cạnh khác nhau của khoản thanh toán. Dưới đây là các hàm then chốt bạn sẽ cần:

Hàm PMT()

Hàm PMT() tính khoản thanh toán cho một khoản vay dựa trên khoản thanh toán không đổi và lãi suất không đổi:

=PMT(rate, nper, pv, [fv], [type])Được hỗ trợ bởi

• rate: Lãi suất mỗi kỳ (lãi suất năm chia cho tần suất thanh toán)

• nper: Tổng số kỳ thanh toán

• pv: Giá trị hiện tại (số tiền vay)

• [fv]: Giá trị tương lai tùy chọn (thường là 0 với khoản vay trả hết)

• [type]: Tùy chọn; 0 nếu thanh toán cuối kỳ, 1 nếu thanh toán đầu kỳ

Để tính khoản thanh toán hàng tháng cho khoản thế chấp 250.000 đô với lãi suất năm 4,5% trong 30 năm: 

=PMT(4.5%/12, 30*12, 250000)Được hỗ trợ bởi

Hàm trả về -1.266,71 đô, đại diện cho dòng tiền chi ra của người vay.

Để có giải thích chi tiết về hàm PMT(), bao gồm lỗi thường gặp và ví dụ nâng cao, bạn có thể tham khảo hướng dẫn PMT() cho Excel của chúng tôi.

Hàm IPMT()

Hàm IPMT() tính phần tiền lãi của một khoản thanh toán cụ thể:

=IPMT(rate, per, nper, pv, [fv], [type])Được hỗ trợ bởi

• per: Kỳ thanh toán cụ thể bạn muốn tính

Tham số per rất quan trọng vì nó xác định khoản lãi của kỳ nào bạn đang tính — khác với hàm PMT() vốn cho cùng một khoản thanh toán ở mọi kỳ, kết quả của IPMT() thay đổi đáng kể tùy kỳ do phần lãi giảm dần theo thời gian vay.

Điều này cho thấy chính xác bao nhiêu trong một khoản thanh toán được dùng để trả lãi.

Hàm PPMT()

Hàm PPMT() tính phần tiền gốc của một khoản thanh toán cụ thể:

=PPMT(rate, per, nper, pv, [fv], [type])Được hỗ trợ bởi

Tương tự IPMT(), số tiền gốc thay đổi theo kỳ; các khoản thanh toán ban đầu có phần gốc nhỏ hơn và các khoản sau có phần gốc lớn hơn. Điều này cho thấy bao nhiêu trong một khoản thanh toán giúp giảm dư nợ của bạn.

Công thức tùy chỉnh

Với mỗi kỳ thanh toán, bạn sẽ muốn theo dõi:

Số dư còn lại: Số dư khoản vay còn lại sau mỗi lần thanh toán

Remaining balance = Previous balance - Current principal paymentĐược hỗ trợ bởi

Lũy kế tiền gốc: Tổng tiền gốc đã trả đến hiện tại

Cumulative principal = Previous cumulative principal + current principal paymentĐược hỗ trợ bởi

Lũy kế tiền lãi: Tổng tiền lãi đã trả đến hiện tại

Formula: Previous cumulative interest + current interest paymentĐược hỗ trợ bởi

Hàm Round()

Ngay cả sai số làm tròn nhỏ cũng có thể tích lũy theo thời gian trong một lịch khấu hao:

=ROUND(value, 2)Được hỗ trợ bởi

Điều này đảm bảo mọi giá trị tiền tệ được làm tròn đến cent gần nhất.

Tham chiếu tuyệt đối và tương đối

Khi xây dựng bảng khấu hao, bạn sẽ cần:

• Dùng tham chiếu tuyệt đối ($) cho các giá trị cố định như lãi suất và số tiền vay

• Dùng tham chiếu tương đối cho các giá trị thay đổi theo từng kỳ

Ví dụ dưới đây dùng tham chiếu tuyệt đối cho lãi suất, kỳ hạn và số tiền, nhưng tham chiếu tương đối cho kỳ thanh toán:

=PPMT($B$2/12, A10, $B$3*12, $B$1)Được hỗ trợ bởi

Hướng dẫn từng bước để tạo lịch khấu hao

Giờ bạn đã hiểu các hàm chính, hãy xây dựng một lịch khấu hao từ đầu. Phần hướng dẫn này sẽ tạo một bảng thanh toán khoản vay hoàn chỉnh để theo dõi từng khoản thanh toán trong suốt vòng đời khoản vay.

1. Nhập dữ liệu khoản vay

Nhập chi tiết khoản vay vào các ô đầu vào chuyên dụng như minh họa bên dưới:

• Số tiền vay: 250.000 đô (ô B1)
• Lãi suất năm: 4,5% (ô B2)
• Kỳ hạn khoản vay: 30 năm (ô B3)
• Tần suất thanh toán: Hàng tháng (ô B4)

Thiết lập các tham số đầu vào khoản vay. Ảnh: Tác giả. 

2. Tính khoản thanh toán hàng tháng

Tại ô B5, tính khoản thanh toán hàng tháng bằng hàm PMT: =PMT(B2/12, B3*12, B1)

Công thức trả về -1.266,71 đô, đại diện cho khoản thanh toán hàng tháng của bạn. Dấu âm cho biết đây là khoản chi (dòng tiền ra).

Thiết lập tiêu đề cột và công thức. Ảnh: Tác giả.

3. Tạo hàng đầu tiên của lịch

Tiếp theo, chúng ta tạo các tiêu đề sau và điền hàng dữ liệu đầu tiên (hàng 7 và 8):  

• Kỳ: Nhập 1 vào ô A8

• Số tiền thanh toán: =$B$5 ở ô B8 (tham chiếu khoản thanh toán hàng tháng đã tính)

• Tiền lãi: =IPMT($B$2/12, A8, $B$3*12, $B$1) ở ô C8

• Tiền gốc: =PPMT($B$2/12, A8, $B$3*12, $B$1) ở ô D8

• Số dư còn lại: =$B$1+D8 ở ô E8 (tiền vay ban đầu cộng khoản gốc thanh toán; hoạt động vì D8 trả về giá trị âm, tương đương trừ đi tiền gốc khỏi khoản vay)

• Lũy kế tiền gốc: =D8 ở ô F8 (trùng với tiền gốc kỳ đầu)

• Lũy kế tiền lãi: =C8 ở ô G8 (trùng với tiền lãi kỳ đầu)

Phân rã khoản thanh toán đầu tiên. Ảnh: Tác giả.

4. Hoàn thiện lịch

Với hàng thứ hai (hàng 9):

• Kỳ: =A8+1 ở ô A9 (tăng thêm 1)

• Số tiền thanh toán: Sao chép công thức từ B8 xuống B9

• Tiền lãi: =IPMT($B$2/12, A9, $B$3*12, $B$1) ở ô C9

• Tiền gốc: =PPMT($B$2/12, A9, $B$3*12, $B$1) ở ô D9

• Số dư còn lại: =E8+D9 ở ô E9 (số dư trước cộng tiền gốc hiện tại)

• Lũy kế tiền gốc: =F8+D9 ở ô F9 (tổng trước đó cộng tiền gốc hiện tại)

• Lũy kế tiền lãi: =G8+C9 ở ô G9 (tổng trước đó cộng tiền lãi hiện tại)

Lưu ý ở cả hai hàm IPMT() và PPMT(), A9 được dùng làm tham chiếu tương đối (không có dấu đô la), nghĩa là khi bạn sao chép công thức xuống, nó sẽ tự động cập nhật thành A10, A11, v.v., và tính đúng kỳ mỗi lần.

Thiết lập các công thức có thể sao chép xuống. Ảnh: Tác giả.

Một vài khoản thanh toán đầu tiên trong lịch khấu hao. Ảnh: Tác giả.

Chọn tất cả ô ở hàng 9 và kéo xuống đến hàng 367 (đối với khoản vay 30 năm, trả hàng tháng với 360 kỳ).

Khi tiến dần trong kỳ hạn vay, phần gốc trong mỗi khoản thanh toán sẽ tăng lên so với phần lãi. Ảnh: Tác giả.

5. Xác minh khoản thanh toán cuối cùng

Trong một lịch khấu hao được xây dựng đúng, khoản thanh toán cuối cùng sẽ đưa số dư còn lại về đúng bằng không, và tổng tiền gốc lũy kế bằng với số tiền vay ban đầu.

Khoản thanh toán cuối cùng đưa số dư còn lại về đúng bằng không. Ảnh: Tác giả.

Như bạn thấy ở hàng cuối, số dư còn lại là 0,00 đô (được đánh dấu), và tổng tiền gốc lũy kế bằng 250.000 đô, khớp với số tiền vay ban đầu. Điều này xác nhận lịch khấu hao của chúng ta là chính xác.

Khi xác minh số dư cuối, việc áp dụng hàm ROUND() cho các tính toán quan trọng có thể hữu ích. Những sai khác thập phân nhỏ do độ chính xác tính toán của Excel có thể khiến số dư cuối hiển thị một khoản nhỏ thay vì đúng bằng không. Dùng =ROUND(value, 2) đảm bảo mọi giá trị tiền tệ nhất quán theo tiêu chuẩn báo cáo tài chính thông thường.

Những điều bổ sung bạn có thể muốn cân nhắc

Một lịch tiêu chuẩn là điểm khởi đầu. Hãy cân nhắc những điều này để làm cho mô hình của bạn linh hoạt hơn:

Quản lý biến động thanh toán

Lịch khấu hao càng hữu ích hơn khi được tùy chỉnh để khám phá các kịch bản thanh toán khác nhau. Để mô phỏng trả thêm, hãy tạo một ô đầu vào bổ sung cho các khoản thanh toán bổ sung và sửa công thức tiền gốc để bao gồm khoản này. Cách tiếp cận này giúp hình dung việc trả thêm làm giảm cả kỳ hạn khoản vay và tổng lãi phải trả. Với các khoản vay có trả cuối kỳ lớn (balloon) hoặc lãi suất biến đổi, hãy cân nhắc tạo các phần riêng trong lịch phản ánh thay đổi lãi suất tại các thời điểm cụ thể. Bạn có thể dùng Trình quản lý Kịch bản (Scenario Manager) của Excel để so sánh các môi trường lãi suất khác nhau và tác động của chúng đến tổng chi phí.

Tăng cường khả năng đọc và phân tích

Định dạng phù hợp sẽ biến lịch khấu hao của bạn từ một bảng cơ bản thành công cụ tài chính hữu ích. Áp dụng định dạng tiền tệ với số chữ số thập phân nhất quán cho mọi giá trị tiền tệ, và dùng định dạng có điều kiện để làm nổi bật các cột mốc quan trọng—như khi bạn đã trả được một nửa tiền gốc hoặc đạt các ngưỡng vốn nhất định. Cũng quan trọng là hiểu các phương pháp tính lãi khác nhau ảnh hưởng đến khoản thanh toán ra sao. Tính lãi theo ngày (phổ biến ở vay sinh viên) dựa trên năm 365 ngày, trong khi nhiều khoản thế chấp dùng phương pháp tính lãi theo tháng. Ghi rõ phương pháp mà lịch của bạn sử dụng sẽ đảm bảo tính chính xác khi so sánh với sao kê khoản vay thực tế.

Tùy chỉnh cho các loại khoản vay cụ thể

Các khoản vay khác nhau có đặc điểm riêng có thể yêu cầu điều chỉnh mẫu lịch khấu hao cơ bản của bạn. Với thế chấp có tài khoản ký quỹ (escrow), hãy thêm cột để theo dõi bảo hiểm và thuế cùng với gốc và lãi. Với các khoản vay có khấu hao âm (khoản thanh toán không đủ bù lãi phát sinh), hãy sửa công thức số dư còn lại để cộng lãi chưa trả vào tiền gốc. Tùy chọn thời điểm thanh toán trong các hàm tài chính của Excel (tham số type) cho phép bạn mô phỏng thanh toán đầu kỳ hoặc cuối kỳ, đặc biệt hữu ích cho hợp đồng thuê và một số khoản vay thương mại nơi quy ước thời điểm khác với các khoản vay tiêu dùng tiêu chuẩn.

Kết luận

Tạo lịch khấu hao trong Excel cho bạn cái nhìn sâu sắc về khoản vay vượt xa những gì hầu hết bên cho vay cung cấp. Bằng cách tự xây dựng lịch, bạn có thể thấy chính xác mỗi khoản thanh toán ảnh hưởng đến số dư ra sao, hiểu đúng chi phí đi vay theo thời gian và đưa ra quyết định sáng suốt về phương án trả trước. Các kỹ năng phát triển qua quy trình này—sử dụng các hàm tài chính của Excel, tạo công thức động và thiết kế bảng thông tin—vượt xa phân tích khoản vay và có thể áp dụng cho nhiều khía cạnh lập kế hoạch tài chính cá nhân và doanh nghiệp.

Nếu bạn muốn đi xa hơn khấu hao và xây dựng các mô hình tài chính hoàn chỉnh—bao gồm dự báo thu nhập, đánh giá đầu tư và chạy các kịch bản giả định—hãy xem khóa học Financial Modeling in Excel của chúng tôi. Đây là bước tiếp theo tuyệt vời để củng cố kỹ năng bảng tính cho phân tích tài chính. Và hãy nhớ, bạn càng quen thuộc với các phép tính tài chính này, bạn càng có vị thế tốt hơn để làm chủ tương lai tài chính của mình, dù bạn đang quản lý nợ cá nhân, lập kế hoạch đầu tư cho doanh nghiệp hay tư vấn cho khách hàng về các lựa chọn tài chính.