Cách tạo Bảng phân bổ tỷ lệ biến đổi trong Microsoft Excel

Khi bạn nợ ngân hàng hoặc tổ chức cho vay, họ thường cung cấp cho bạn một bản sao kê để xem bạn đã trả bao nhiêu. Tuy nhiên, đó là về nó. Họ có thể tính toán số tiền bạn còn lại phải trả và trong bao lâu, nhưng bạn sẽ phải nhờ dịch vụ khách hàng yêu cầu.

Một số tổ chức cũng cung cấp máy tính trực tuyến — tuy nhiên, những máy tính này thường chỉ hoạt động với lãi suất cố định. Nếu khoản vay của bạn sử dụng lãi suất thay đổi, bạn sẽ phải tự tìm hiểu. May mắn thay, bạn có thể tạo bảng phân bổ trong Excel với lãi suất thay đổi. Đây là cách bạn có thể xây dựng nó.

Xây dựng Bảng phân bổ lãi suất thay đổi cho vay

Trước khi xây dựng bảng, bạn phải có trong tay các thông tin sau:

• Tổng số tiền cho vay
• Thời hạn cho vay (số năm phải trả)
• Số lần thanh toán mỗi kỳ hạn (số kỳ thanh toán trong năm)

Bạn cũng cần phải làm quen với công thức sau:

-PMT(RATE,NPER,PV)

TỶ LỆ đề cập đến lãi suất hàng năm áp dụng cho khoản thanh toán đó, NPER đề cập đến số lượng thanh toán còn lại và PV đề cập đến số dư khoản vay còn lại trước khi thanh toán cho khoảng thời gian nói trên.

Khi bạn có thông tin đó, hãy nhập chúng vào tệp Excel của bạn. Đối với ví dụ này, chúng tôi sẽ sử dụng khoản thế chấp 20 năm, trị giá 5.000.000 đô la được trả hàng tháng.

Sau khi thêm dữ liệu ban đầu, bạn có thể bắt đầu tạo bảng khấu hao.

Tạo các cột cho bảng khấu hao của bạn

Bạn cần tạo các cột sau cho bảng của mình:

• Thời hạn thanh toán: hiển thị kỳ thanh toán.
• Số tiền thanh toán: tổng số tiền phải thanh toán trong kỳ.
• Trả lãi: số tiền thanh toán chuyển sang lãi suất.
• Tiền gốc đã trả: số tiền thanh toán giảm số dư gốc.
• Dư nợ cho vay: tổng số tiền vay còn lại để thanh toán.
• Lãi suất hàng năm: lãi suất hàng năm của kỳ thanh toán đó.
• Thanh toán một lần: ghi lại các khoản thanh toán nâng cao, nếu có.

Điền vào dữ liệu cố định

Khi bạn đã tạo các cột của mình, đã đến lúc điền vào thông tin được xác định trước. Bên dưới Thời hạn thanh toán cột, viết 0 cho dữ liệu ban đầu của bạn, 1 trong hàng tiếp theo để hiển thị kỳ thanh toán đầu tiên và 2 trong hàng sau đó. Sau khi làm như vậy, hãy chọn cả ba ô.

Sau khi bạn đã chọn chúng, hãy đặt con trỏ của bạn vào hộp màu xanh lục ở góc dưới bên phải của hộp chọn cho đến khi nó biến thành một hình chữ thập mỏng, màu đen. Từ đó, nhấp vào hộp và sau đó kéo nó xuống dưới.

Bạn sẽ thấy một hộp nhỏ xuất hiện ở bên phải con trỏ của bạn. Con số này cho biết số cuối cùng mà Excel sẽ điền vào — thả nút chuột khi bạn nhấn tổng số kỳ thanh toán cho khoản vay của mình (trong ví dụ của chúng tôi là 240).

Dưới Tiền vay THĂNG BẰNGliên kết tổng số tiền cho vay trong Kỳ 0 với bản gốc Tổng số tiền cho vay. Đối với ví dụ của chúng tôi, hãy sử dụng công thức = C1. Để trống phần còn lại của các hàng.

Dưới Lãi suất hàng năm, nhập lãi suất cho kỳ thanh toán nói trên. Nếu bạn có 5/1 ARM (thế chấp có lãi suất có thể điều chỉnh), thì năm năm đầu tiên khoản vay của bạn có lãi suất cố định. Sau đó, nó sẽ điều chỉnh hàng năm, dựa trên thị trường, sau khoảng thời gian đó.

Đối với ví dụ này, chúng tôi sẽ sử dụng lãi suất 3,57% trong năm năm đầu tiên và sau đó sử dụng các tỷ lệ khác nhau dựa trên tỷ lệ lịch sử.

Thêm công thức

Khi bạn đã đặt lãi suất giữ chỗ, đã đến lúc xác định số tiền thanh toán đầu tiên của bạn. Nhập công thức thanh toán được đề cập bên dưới trong Kỳ thanh toán 1 hàng dưới Số tiền thanh toán cột.

=-PMT(RATE,NPER,PV)

Dưới TỶ LỆ, hãy chọn ô liệt kê lãi suất hàng năm hiện tại (ô F6 trong ví dụ của chúng tôi) và sau đó chia nó cho số lần thanh toán mỗi kỳ. Thêm ký hiệu $ trước chỉ báo cột và hàng (ô $ C $ 3 trong ví dụ của chúng tôi) để đảm bảo nó không thay đổi khi bạn áp dụng công thức cho các ô khác sau này.

Vì NPERloại hình ĐẾM(. Sau đó, chọn ô đầu tiênnhấn sự thay đổi trên bàn phím của bạn, sau đó chọn ô cuối kỳ (ô hiện tại). Một lần nữa, đừng quên thêm ký hiệu $ trước số cột và hàng trong công thức chỉ ra ô cuối cùng ($ A $ 245 trong ví dụ của chúng tôi) để đảm bảo nó không thay đổi sau này.

Để đầu vào PVhãy chọn ô chứa tổng số tiền cho vay trong Kỳ 0 (ô E6 trong ví dụ của chúng tôi). Ngoài ra, lưu ý rằng PMT công thức có một dấu âm trước nó, vì vậy bạn nhận được một giá trị dương trong bảng của mình. Nếu bạn không thêm điều này, bạn sẽ nhận được một giá trị âm.

Bên dưới Trả lãi , tính số tiền chuyển thành tiền lãi trong khoản thanh toán hàng tháng của bạn. Sử dụng công thức sau:

=Balance Remaining*(Annual Interest Rate/Payments Per Period)

Đối với ví dụ của chúng tôi, đây là cách công thức sẽ trông như thế nào:

=E6*(F7/$C$3)

Trong đó E6 là số dư còn lại bạn phải trả, F7 là Lãi suất hàng năm áp dụng hiện tại và $ C $ 3 là số lần thanh toán bạn thực hiện cho mỗi kỳ hạn.

Khi bạn đã xác định số lượng Trả lãitrừ đi Số tiền thanh toán để có được Tiền gốc đã trả.

Trong ví dụ của chúng tôi, công thức là:

=B7-C7

Bạn có thể lấy Dư nợ cho vay từ đó bằng cách trừ đi dòng điện Số tiền thanh toán và Thanh toán một lần từ Số dư cho vay kỳ trước (Kỳ 0).

Đây là công thức trông giống như trong ví dụ của chúng tôi:

=E6-B7-G7

Khi bạn đã thêm tất cả các công thức, bạn sẽ chuẩn bị sẵn sàng tất cả dữ liệu của tháng đầu tiên.

Điền phần còn lại của bảng khấu hao của bạn

Với tất cả các công thức ban đầu đã sẵn sàng, bây giờ bạn có thể điền phần còn lại của bảng bằng cách kéo và thả. Đầu tiên, hãy chọn các ô có công thức (Số tiền thanh toán, Lãi suất đã trả, Số tiền gốc đã trả và Số dư Khoản vay trong Kỳ 1).

Sau đó, đặt con trỏ của bạn vào hộp màu xanh lá cây ở góc dưới bên phải của hộp lựa chọn cho đến khi nó biến thành một hình chữ thập mỏng, màu đen. Từ đó, hãy nhấp vào hộp và sau đó kéo hộp xuống đến Kỳ thanh toán cuối cùng. Khi bạn đến hàng cuối cùng, hãy thả chuột và Excel sẽ điền phần còn lại của các ô cho bạn.

Ngoài ra, bạn có thể Sao chép các ô có công thức, sau đó chọn tất cả các hàng Thời hạn thanh toán bên dưới chúng. Từ đó, chọn Dán hoặc nhấn Ctrl + V và Excel sẽ tự động điền vào bảng của bạn các giá trị chính xác.

Để tránh vô tình thay đổi công thức, hãy chọn tất cả các ô có công thức (ô A6 đến ô E246), sau đó điền vào nó bằng bất kỳ màu nào. Bạn cũng có thể sử dụng định dạng có điều kiện để thay đổi màu của các ô sau khi số dư khoản vay của bạn được thanh toán hết.

Thử nghiệm với dữ liệu của bạn

Bây giờ bạn đã hoàn thành bảng ban đầu của mình, bạn có thể tự do thay đổi Tổng số tiền cho vay để xem ảnh hưởng của nó đối với tổng khoản vay của bạn. Bạn cũng có thể thay đổi Lãi suất hàng năm mỗi Kỳ thanh toán, cho phép bạn nhập dữ liệu thực tế sau khi ngân hàng của bạn gửi lãi suất hiệu quả cho khoản vay của bạn. Và nếu bạn dư dả một chút, bạn có thể thanh toán trước theo Thanh toán một lần.

Bất kỳ thay đổi nào bạn thực hiện đều được tính theo công thức của bạn, cho phép bạn nhận được kết quả chính xác bất kể bạn nhập dữ liệu nào, miễn là dữ liệu đó hợp lệ. Nếu bạn có một khoản vay theo lãi suất cố định nhưng muốn thử nghiệm với thời hạn, thời hạn hoặc khoản thanh toán hàng tháng của khoản vay, hãy xem cách tạo lịch trình khấu hao theo lãi suất cố định trong Excel.

Biết có thể giúp bạn lập kế hoạch tài chính

Với lịch trình khấu hao theo tỷ lệ thay đổi, bạn có thể lập kế hoạch cho khoản vay và các khoản thanh toán của mình. Bằng cách đó, bạn có thể chuẩn bị cho mình, ngay cả khi lãi suất thay đổi. Và với cột tổng hợp, bạn thậm chí có thể thấy các khoản thanh toán nâng cao nhỏ có thể đi một chặng đường dài như thế nào trong việc giúp bạn trả hết khoản vay của mình.