Cách tạo lịch khấu hao với Excel
Nợ nần giúp mọi thứ thành hiện thực — một chiếc ô tô mới, một nền giáo dục đại học, hoặc thậm chí một ngôi nhà mới. Tuy nhiên, bạn phải hoàn trả nó. Bạn có thể yêu cầu sao kê khoản vay, nhưng nó không phải lúc nào cũng dễ hiểu. Tuy nhiên, nếu bạn có thể thấy tiền của mình đi đâu, bạn sẽ dễ dàng nhìn thấy ánh sáng cuối khoản vay.
Đây là lúc lịch trình phân bổ xuất hiện. Bạn sẽ hình dung rõ hơn về khoản vay bằng cách hiểu cách hoạt động của khoản nợ và các khoản thanh toán của bạn ảnh hưởng đến nó như thế nào. Bạn thậm chí có thể thấy việc trả thêm một chút có thể ảnh hưởng đáng kể đến nó như thế nào. Đây là cách bạn có thể tạo một tệp trong Microsoft Excel.
Mục Lục
Lịch trình khấu hao là gì?
Theo Investopedia, “Lịch trình phân bổ khoản vay là một bảng hoàn chỉnh về các khoản thanh toán khoản vay định kỳ, thể hiện số tiền gốc và số tiền lãi bao gồm mỗi khoản thanh toán cho đến khi khoản vay được trả hết vào cuối kỳ hạn của nó.”
Vì vậy, khi bạn tạo một lịch biểu, bạn sẽ thấy số tiền bạn phải trả hàng tháng và cách phân chia giữa việc trả lãi và số tiền gốc. Dựa trên các khoản thanh toán hàng tháng của bạn, bạn cũng sẽ thấy mất bao lâu để hoàn trả khoản vay. Và, nếu bạn trả thêm tiền, bạn cũng sẽ thấy nó ảnh hưởng như thế nào đến lịch thanh toán tổng thể.
Lịch trình khấu hao giúp bạn biết mất bao lâu để trả hết nợ và mỗi đô la bạn bỏ ra để trả nợ ảnh hưởng đến nó như thế nào. Bạn cũng có thể sử dụng nó để lập kế hoạch cho các giao dịch mua lớn, vì vậy bạn sẽ thấy số tiền mình phải trả hàng tháng và số tiền bạn sẽ tiết kiệm được nếu thanh toán sớm.
Cách tính toán chi tiết khấu hao của bạn trong Excel
Đối với ví dụ này, giả sử bạn định mua Mustang Mach 1 Premium 2022. Giá thực ước tính của nó là 68.529 đô la và khoản thanh toán hàng tháng của bạn là 1.164 đô la. Tính toán này dựa trên khoản trả trước $ 6,853 cho kỳ hạn 60 tháng và tỷ lệ phần trăm hàng năm là 5%.
Nhập dữ liệu vào Excel để xác nhận xem chúng có chính xác không. Bạn có thể xác định các khoản thanh toán hàng tháng, lãi suất hàng năm, số tháng phải trả khoản vay hoặc tổng số tiền vay bằng cách sử dụng các công thức sau, tương ứng: PMT, RATE, NPER và NPV. Bạn cũng có thể sử dụng các công thức này nếu ngân hàng hoặc đại lý không bao gồm một điểm dữ liệu (như lãi suất).
Các công thức này cũng bao gồm FV, TYPE và GUESS, nhưng bạn có thể bỏ qua chúng một cách an toàn vì chúng không liên quan đến ứng dụng của bạn.
Vì vậy, nếu bạn nhìn vào dữ liệu đã cho ở trên, PMT = 1,164, RATE = 5%, NPER = 60 và PV = 61,676 (giá ròng ước tính trừ đi khoản thanh toán). Nhưng nếu bạn có dữ liệu không đầy đủ, đây là cách bạn có thể tính toán cho chúng.
Thanh toán hàng tháng
Để tính toán các khoản thanh toán hàng tháng, hãy sử dụng hàm PMT:
=-PMT(RATE,NPER,PV,[FV],[TYPE])Chỉ cần nhớ chia RATE cho 12, vì chúng tôi đang tính toán các khoản thanh toán hàng tháng.
Lãi suất
Nếu bạn cần xác định lãi suất, hãy sử dụng hàm RATE:
=RATE(NPER,-PMT,PV,[FV],[TYPE],[GUESS])Công thức này sẽ cung cấp cho bạn tỷ lệ hàng tháng, vì vậy đừng quên nhân kết quả cuối cùng với 12.
Thời hạn thanh toán
Khi bạn không chắc mình sẽ trả một khoản vay trong bao nhiêu tháng, nhưng bạn biết mình đang vay bao nhiêu và số tiền bạn có thể trả hàng tháng, bạn có thể sử dụng hàm NPER:
=NPER(RATE,-PMT,PV,[FV],[TYPE])Một lần nữa, hãy chia TỶ LỆ cho 12 vì bạn đang tính số tháng bạn sẽ trả một khoản vay.
Tổng số tiền cho vay
Cuối cùng, nếu bạn muốn biết bạn có thể vay bao nhiêu với lãi suất cụ thể, số tiền bạn có thể trả hàng tháng và số tháng bạn sẵn sàng trả khoản vay, hãy sử dụng hàm PV:
=PV(RATE,NPER,-PMT,[FV],[TYPE]RATE phải luôn chia cho 12 khi NPER của bạn dựa trên tháng; nếu không, bạn sẽ nhận được một giá trị giảm phát nghiêm trọng.
Bây giờ bạn đã biết cách tìm bất kỳ giá trị nào còn thiếu, đã đến lúc tạo bảng phân bổ.
Xây dựng Bảng Phân bổ Khoản vay Lãi suất Cố định
Đầu tiên, bạn cần thêm các thông tin chi tiết về khoản vay của mình. Điều này bao gồm Tổng số tiền, Khoản trả trước, Số tiền vay, Lãi suất, Thời gian vay và Khoản thanh toán hàng tháng. Để đảm bảo bạn có dữ liệu chính xác, bạn có thể sử dụng công thức PMT ở trên, đặc biệt nếu bạn muốn thử nghiệm với thời gian vay hoặc số tiền vay.
Ngoài ra, bạn có thể sử dụng các công thức khác được đề cập nếu bạn muốn chơi với số liệu khấu hao hàng tháng, lãi suất hoặc tổng số tiền cho vay.
Sau khi thêm thông tin chi tiết của bạn, hãy tạo bảng phân bổ khoản vay. Đầu tiên, hãy thêm các cột sau vào dữ liệu ban đầu của bạn: Giai đoạn = Stage, Số dư đầu kỳ, Thanh toán hàng tháng, Thanh toán gốc, Trả lãi, Tiền gốc tích lũy, Lãi tích lũy, Số dư cuối kỳ, Lãi suất, và Thanh toán một lần.
Hàng đầu tiên của bảng lịch trình khấu hao của bạn
Khi bạn đã tạo các cột, hãy điền vào hàng đầu tiên (hàng 9) bên dưới Giai đoạn = Stage với Tháng 1. Sau khi làm như vậy, hãy sử dụng chức năng tự động điền của Excel để nhanh chóng điền vào cột Dấu chấm. Chọn ô và đặt con trỏ của bạn vào hộp nhỏ màu xanh lá cây ở góc dưới bên phải của ô đã chọn, nơi con trỏ sẽ biến thành một dấu thập nhỏ màu đen.
Khi con trỏ của bạn biến đổi, nhấp vào hộp nhỏ màu xanh lá cây, sau đó kéo chuột của bạn xuống dưới. Một chỉ báo nhỏ sẽ xuất hiện bên cạnh chuột, cho biết số tháng Excel sẽ áp dụng cho cột. Dừng và thả nút chuột khi nó chạm tới Tháng 60.
Sau khi điền vào cột Dấu chấm, đã đến lúc điền phần còn lại của hàng đầu tiên. Dưới Số dư đầu kỳloại hình = $ B $ 3 để tham chiếu đến ô mà bạn đã đặt Số tiền vay. Đừng quên thêm dấu $ vào trước ký tự cột và số hàng để đảm bảo rằng nó không thay đổi khi tự động điền phần còn lại của bảng.
- Dưới Thanh toán hàng thángloại hình:
=$B$6
Điều này sao chép lãi suất bạn đã nhập với dữ liệu ban đầu của mình. Đừng quên thêm dấu $ để nó không thay đổi khi bạn tự động điền vào bảng của mình.
- Dưới Hiệu trưởngsử dụng công thức sau:
=C9-E9Số tiền này trừ đi số tiền lãi phải trả từ khoản thanh toán hàng tháng.
- Dưới Quan tâmtính số tiền theo công thức sau:
=B9*(I9/12)Điều này nhân khoản thanh toán hàng tháng với lãi suất hàng tháng.
- Vì Tiền gốc tích lũyloại hình:
=D9- Vì Lãi tích lũy, loại hình:
=E9- Dưới Lãi suấtloại hình:
=B4Điều này sao chép lãi suất bạn đã tính trước đó. Tuy nhiên, nếu ngân hàng của bạn sử dụng lãi suất thay đổi, bạn có thể thay đổi điều này.
- Dưới Số dư cuối kỳsử dụng công thức:
=B9-D9Điều này sẽ xóa số tiền gốc bạn đã trả khỏi số dư đầu kỳ của mình.
- Giữ cho Thanh toán một lần để trống trừ khi bạn định trả thêm một số tiền trong giai đoạn này.
Hoàn thành Bảng phân bổ khoản vay
Bây giờ bạn đã điền hàng đầu tiên, đã đến lúc thêm phần còn lại của bảng bắt đầu từ hàng thứ hai (hàng 10).
- Dưới Số dư đầu kỳnhập công thức sau:
=H9-I9Điều này sao chép số dư cuối kỳ mà bạn đã thanh toán trước đó, cộng với bất kỳ khoản thanh toán tạm ứng nào bạn đã thực hiện.
- Cho Thanh toán hàng tháng, Hiệu trưởng, Quan tâmvà Số dư cuối kỳ sao chép công thức từ hàng trên.
- Dưới Tiền gốc tích lũynhập như sau:
=F9+D10Điều này cộng tổng số tiền gốc bạn đã trả trong kỳ trước vào khoản thanh toán gốc mà bạn đã thực hiện trong kỳ này.
- Dưới Lãi tích lũynhập như sau:
=G9+E10Điều này cộng tổng số tiền lãi bạn đã trả trong kỳ trước vào khoản thanh toán lãi suất bạn đã thực hiện trong kỳ này.
- Giữ cho Thanh toán một lần để trống trừ khi bạn định trả thêm một số tiền trong giai đoạn này.
Khi bạn đã nhập các công thức ở trên, hãy chọn dữ liệu từ hàng 10, từ Số dư đầu kỳ đến cột Thanh toán Tổng hợp. Sau khi chọn các ô, nhấn Điều khiển + C để sao chép chúng.
Chọn ô trong Số dư đầu kỳ cho Tháng 3 (hàng 11). Sau khi làm như vậy, cuộn xuống hàng Tháng 60 (hàng 68), nhấn Sự thay đổi trên bàn phím của bạn, sau đó chọn ô dưới Cột Thanh toán Trọn gói (ô I68).
Khi bạn hoàn thành lựa chọn, hãy nhấn Điều khiển + V để chèn các công thức bạn đã tạo; khi đó bạn sẽ thấy toàn bộ bảng phân bổ của mình được điền đầy đủ thông tin cần thiết.
Sau đó, bạn có thể gửi thông báo này cho đối tác của mình nếu bạn đang thực hiện một giao dịch mua lớn để giúp đi đến quyết định. Ngay cả khi họ đang sử dụng Google Trang tính, họ có thể nhập tệp Excel vào Trang tính để xem các tính toán của bạn.
Thử nghiệm với dữ liệu của bạn
Bạn có thể thay đổi dữ liệu của mình để xem nó sẽ ảnh hưởng như thế nào đến lịch thanh toán của bạn. Ví dụ: nếu bạn nghĩ rằng bạn có thể thực hiện các khoản thanh toán bổ sung lẻ tẻ (như khoản thanh toán tiền thưởng hàng năm của công ty), hãy thêm số tiền ước tính bổ sung của bạn trong khoảng thời gian chính xác cho Thanh toán một lần.
Bạn cũng có thể thay đổi số tháng bạn muốn thanh toán. Ví dụ: nếu bạn muốn trả hết khoản vay của mình trong 48 tháng thay vì 60 tháng, chỉ cần thay đổi số tháng trong dữ liệu ban đầu của bạn và bảng của bạn sẽ điều chỉnh để hiển thị số tiền bạn phải trả hàng tháng.
Khi Số dư đầu kỳ của bạn giảm xuống dưới 0, điều đó có nghĩa là khoản vay của bạn đã được thanh toán đầy đủ! Chỉ cần lưu ý rằng bảng này chỉ hoạt động đối với các khoản vay lãi suất cố định. Nếu khoản vay của bạn có tỷ lệ thay đổi, bạn sẽ phải sử dụng một cách tiếp cận khác.