Bạn đã bao giờ thắc mắc cách tạo lịch trả khoản vay trong Google Trang tính chưa? Sau đó, bạn đang gặp may mắn! Phần mềm bảng tính như Google Trang tính có thể giúp bạn sắp xếp và tự động hóa các công việc hàng ngày của mình, cho dù bạn muốn duy trì ngân sách, theo dõi chi phí, lập kế hoạch hoạt động hay thanh toán các khoản vay đúng hạn.
Một bảng phân bổ có thể là một cách thực sự hiệu quả để xem các khoản thanh toán hàng tháng của bạn có thể ảnh hưởng như thế nào đến tổng chi phí khoản vay của bạn, cho dù đó là khoản vay thế chấp, vay mua ô tô hay vay cá nhân.
Trong hướng dẫn này, chúng tôi sẽ trình bày từ đầu đến cuối cách bạn có thể tạo lịch trả khoản vay của riêng mình chỉ đơn giản bằng cách sử dụng Google Trang tính. Điều này có nghĩa là bạn không phải tìm kiếm các ứng dụng ưa thích và có toàn quyền kiểm soát quá trình hoàn trả khoản vay của mình chỉ bằng cách sử dụng một vài công thức của Google Trang tính.
Khoản vay trả dần là gì?
Khoản vay trả dần là một loại cho vay bao gồm các khoản thanh toán định kỳ được lên lịch trong một khoảng thời gian cụ thể. Các khoản thanh toán liên quan đến cả gốc và lãi.
Quy trình này bao gồm việc thanh toán khoản lãi thích hợp đầu tiên trong kỳ để phần còn lại của khoản thanh toán có thể được sử dụng để giảm số tiền gốc.
Bảng hoàn trả khoản vay là gì?
Bảng hoàn trả khoản vay bao gồm lịch thanh toán định kỳ cho khoản vay. Biểu đồ thể hiện số tiền gốc và lãi suất của mỗi lần trả từ đầu kỳ đến khi trả khoản vay cuối kỳ.
Quá trình hoàn trả bao gồm việc trả cùng một số tiền trong mỗi lần thanh toán định kỳ. Tuy nhiên, khi bắt đầu quy trình, phần lớn số tiền được trả là tiền lãi, và về cuối, phần lớn số tiền này bao gồm tiền gốc.
Do đó, điều này làm cho quá trình trả nợ thoải mái cho người đi vay và giảm chi phí tổng thể của khoản vay.
Dòng cuối cùng của bảng cho biết tổng số tiền trả lãi và gốc của người vay trong cả kỳ hạn.
Cách tạo lịch trả dần khoản vay trong Google Trang tính
Vì vậy, bây giờ chúng ta đã biết những điều cơ bản, hãy bắt đầu tạo một bảng tính.
Đầu tiên, chúng ta sẽ bắt đầu bằng cách tạo một wireframe trang tính cơ bản, trong đó chúng ta sẽ nhập lịch cơ bản, ngày bắt đầu, lãi suất khoản vay và tổng số tiền đến hạn.
Sau đó, chúng ta sẽ tạo một bảng và điền vào hai hàng đầu tiên các công thức thích hợp. Cuối cùng, tất cả những gì chúng ta phải làm chỉ là kéo các công thức xuống để điền vào phần còn lại của lịch trình. Ở dòng cuối cùng, cột dư nợ hiển thị 0.0.
Tạo một lược đồ bảng lịch trả nợ cơ bản
Hãy bắt đầu với bố cục cơ bản của bảng tính.
Chỉ cần nhấp vào Tệp -> Tạo bản sao để bạn có thể chỉnh sửa và lưu tài liệu của riêng mình.
Phần trên cùng của trang tính (hàng 1 và 2) bao gồm tất cả các giá trị sẽ không đổi trong suốt thời gian cho vay:
- Số tiền gốc của một khoản vay hoặc tổng số tiền của một khoản vay phải được hoàn trả.
- Lãi suất áp dụng cho khoản vay.
- Thời hạn (tính bằng năm) hoặc khoảng thời gian khoản vay được hoàn trả.
- Ngày bắt đầu mà lịch trình hoàn trả khoản vay bắt đầu.
Giả sử chúng ta có một khoản vay mua nhà trị giá 100.000 đô la đến hạn trong 10 năm với lãi suất 5% bắt đầu từ ngày 29/06/2021. Vì vậy, hãy điền các giá trị này vào bảng:
Sau khi hoàn tất, bạn có thể tiến hành điền vào phần cuối của trang tính.
Điền vào bảng khấu hao chính
Đã đến lúc hoàn thành phần chính của trang tính. Dưới đây là các cột bạn sẽ cần hoàn thành:
Giai đoạn = Stage
Cột Kỳ hạn sẽ bao gồm số thứ tự của kỳ thanh toán. Vì vậy, đối với đợt đầu tiên, khoảng thời gian sẽ là 1, đối với đợt thứ hai, khoảng thời gian sẽ là 2, và cứ tiếp tục như vậy.
Nhập 0 vào hàng đầu tiên của cột này và 1 vào hàng thứ hai.
Ngày tháng
Cột Ngày chứa các ngày mà mỗi khoản thanh toán phải được thực hiện. Trong hàng đầu tiên, ngày khớp với ngày bắt đầu (trong ô E2). Sau đó, mỗi ngày sẽ chênh lệch một tháng (do bạn trả góp hàng tháng).
Vì vậy, trong hàng đầu tiên của cột này, hãy nhập công thức: = E2
Trên dòng thứ hai, nhập công thức: = DATE (YEAR (B7), MONTH (B7) + 1, Day (B7))
Điều này sẽ trả về ngày đúng một tháng sau ngày trong ô B7.
Thanh toán hàng tháng
Tiếp theo, chúng ta cần tính toán khoản thanh toán hàng tháng cho mỗi hàng. Chúng tôi có thể sử dụng tính năng PMT của Google Trang tính để tính khoản thanh toán hàng tháng.
Chức năng PMT
Hàm PMT tính toán khoản thanh toán định kỳ cho khoản đầu tư theo niên kim trong đó số tiền và lãi suất không đổi cho mỗi lần thanh toán định kỳ.
Cú pháp của hàm PMT như sau:
PMT (tỷ lệ, số_nguồn_kỳ, giá_trị_có_triển, [future_value, end_or_beginning])
Đây,
- Tỷ lệ – lãi suất cho thời gian thanh toán.
- number_of_periods – số lần thanh toán.
- giá_trị_của_mày không là gì khác ngoài số tiền chính.
- future_value là tùy chọn. Đây là giá trị tương lai còn lại sau khi thanh toán cuối cùng được thực hiện.
- end_or_beginning cũng là tùy chọn. Nó chỉ định liệu các khoản thanh toán đến hạn vào cuối (0) hay đầu (0) của mỗi kỳ. Theo mặc định, giá trị này là 0.
Trong ví dụ của chúng tôi, đây là các giá trị chúng tôi sẽ cần chèn:
- lãi suất: Lãi suất trong ô B2 là cho cả năm, nhưng cài đặt này yêu cầu lãi suất hàng tháng. Vì vậy, giá trị này sẽ là B2 / 12.
- number_of_periods: Tham số này sẽ chứa số khoảng thời gian trong tháng. Vì ô E1 chứa khoảng thời gian tính bằng năm nên giá trị này sẽ là E1 * 12.
- giá_trị_của_trình: Số tiền chính nằm trong ô B1, vì vậy chúng tôi sẽ nhập số tiền đó vào tham số này.
Vì bạn không phải trả phí hàng tháng vào ngày bắt đầu nên giá trị này có thể là 0,00 cho hàng đầu tiên.
Trên dòng thứ hai, bạn có thể nhập công thức sau:
= PMT ($ B $ 2/12, $ E $ 1 * 12, $ B $ 1)
Chúng tôi đã chặn tất cả các tham chiếu ô có ký tự $ vì chúng tôi không muốn các tham chiếu ô đó cập nhật khi chúng tôi sao chép công thức vào phần còn lại của các hàng.
Hãy nhớ rằng khoản thanh toán hàng tháng sẽ giống nhau cho mọi thời kỳ. Vì vậy, tất cả các hàng của cột này sẽ có cùng một giá trị.
Ghi chú. Giá trị trả lại là số âm vì đây là khoản thanh toán đi. Từ quan điểm tài chính, các khoản thanh toán đi luôn được biểu thị bằng một số âm và các khoản thanh toán đến bằng một số dương.
Trả lãi
Cột này sẽ hiển thị tiền lãi phải trả cho mỗi lần trả góp. Để tính toán khoản trả lãi hàng tháng, chúng ta có thể sử dụng chức năng IPMT của Google Trang tính.
Chức năng IPMT
Chức năng IPMT tính toán khoản thanh toán lãi suất cho khoản đầu tư theo niên kim trong đó số tiền và lãi suất không đổi cho mỗi lần thanh toán định kỳ.
Cú pháp của hàm IPMT như sau:
IPMT (tỷ lệ, khoảng thời gian, số_tính_kỳ, giá_trị_có_triển, [future_value, end_or_beginning])
Đây,
- lãi suất – lãi suất cho kỳ thanh toán.
- kỳ – số thứ tự của thời kỳ khấu hao.
- number_of_periods – số lần thanh toán.
- giá_trị_ hiện_trị là số tiền chính.
- future_value là tùy chọn. Đây là giá trị tương lai còn lại sau khi thanh toán cuối cùng được thực hiện.
- end_or_beginning cũng là tùy chọn. Nó chỉ định liệu các khoản thanh toán đến hạn vào cuối (0) hay đầu (0) của mỗi kỳ. Theo mặc định, giá trị này là 0.
Trong ví dụ của chúng tôi, đây là các giá trị chúng tôi sẽ cần chèn:
- lãi suất: Lãi suất trong ô B2 là cho cả năm, nhưng tùy chọn này yêu cầu lãi suất hàng tháng. Vì vậy, giá trị này sẽ là B2 / 12.
- kỳ: nó sẽ chứa số thứ tự của kỳ thanh toán. Giá trị này được chứa trong cột A cho mỗi hàng phù hợp, vì vậy giá trị cho hàng thứ hai sẽ là A8.
- number_of_periods: Tham số này sẽ chứa số khoảng thời gian trong tháng. Vì ô E1 chứa khoảng thời gian tính bằng năm nên giá trị này sẽ là E1 * 12.
- giá_trị_của_trình: Số tiền chính nằm trong ô B1, vì vậy chúng tôi sẽ nhập số tiền đó vào tham số này.
Bởi vì bạn không phải trả lãi vào ngày bắt đầu, giá trị này có thể là 0,00 cho hàng đầu tiên.
Trên dòng thứ hai, bạn có thể nhập công thức sau:
= IPMT ($ B $ 2/12, A8, $ E $ 1 * 12, $ B $ 1)
Chúng tôi đã chặn tất cả các tham chiếu ô có ký tự $ vì chúng tôi không muốn các tham chiếu ô đó cập nhật khi chúng tôi sao chép công thức vào phần còn lại của các hàng.
Liên kết duy nhất mà chúng tôi chưa chặn là liên kết thứ hai (đại diện cho khoảng thời gian) vì giá trị này phải thay đổi theo từng hàng.
Thanh toán cơ bản
Cột này hiển thị số tiền gốc phải trả cho khoản thanh toán hiện tại. Giá trị này có thể nhận được bằng cách trừ đi khoản thanh toán lãi suất hiện tại cho khoản thanh toán hàng tháng hiện tại. Vì vậy, đối với hàng thứ hai, hãy nhập công thức:
= C8-D8
Dòng đầu tiên sẽ lại có giá trị 0,00.
Dư nợ cho vay
Cột này sẽ hiển thị số tiền còn nợ. Giá trị này có thể nhận được bằng cách trừ đi khoản thanh toán gốc hiện tại cho số dư khoản vay trước đó.
Trên hàng đầu tiên, vì tiền vẫn chưa được thanh toán hết, giá trị này sẽ chứa toàn bộ số tiền gốc (hoặc tham chiếu đến ô B1).
Trong dòng thứ hai, giá trị sẽ là:
= E8 + F7
Vì số tiền thanh toán gốc là số âm nên chúng tôi đã sử dụng dấu “+” thay cho dấu “-“.
Sau khi điền vào hai dòng đầu tiên, nó vẫn chỉ để sao chép các giá trị vào các dòng còn lại. Thực hiện việc này bằng cách chọn toàn bộ dòng và kéo chốt điền vào số dòng yêu cầu.
Điều này có nghĩa là nếu bạn phải trả khoản vay trong 10 năm, bạn sẽ có 10 * 12 = 120 dòng.
Dòng cuối cùng phải có số dư cho vay là 0.0.
Tóm tắt thông tin ở dòng cuối cùng
Hàng cuối cùng của bảng phân bổ phải hiển thị tổng các khoản thanh toán lãi và gốc của người đi vay trong toàn bộ thời hạn.
Tổng phần trăm có thể được tính bằng cách tính tổng các giá trị trong cột D. Vì vậy, trong ô C129, hãy nhập công thức:
= SUM (D7: D)
Các khoản thanh toán gốc trọn đời được tính bằng cách cộng các giá trị trong cột E. Vì vậy, trong ô C130, hãy nhập công thức: = SUM (E7: E)
Lịch trình khấu hao của bạn hiện đã sẵn sàng và bởi vì các công thức đã được liên kết với các tham chiếu ô thích hợp, bất kỳ thay đổi nào đối với tiền gốc, lãi suất, kỳ hạn hoặc ngày bắt đầu sẽ được tự động cập nhật ở mọi nơi.
Dưới đây là một mẫu bảng tính lịch trình hoàn trả khoản vay hoàn chỉnh với tất cả các công thức.
Một lần nữa, chỉ cần nhấp vào “Tệp” -> “Tạo bản sao”. Bây giờ bạn có thể thêm bất kỳ dữ liệu tùy chỉnh nào bạn thích và các công thức sẽ tự động cập nhật.
Bạn có thể dễ dàng tùy chỉnh lịch trả nợ trong Google Trang tính cho bất kỳ loại khoản vay nào. Tất cả những gì bạn phải làm là điều chỉnh các giá trị theo yêu cầu của bạn.
Chúng tôi hy vọng điều này hữu ích cho bạn.
