Cách tạo máy tính khấu hao cho vay của riêng bạn

Hầu hết mọi người nghĩ rằng có một số loại ma thuật voodoo tạo ra một khoản vay lịch khấu hao . Nhưng tôi vui mừng nói với bạn rằng không có - và thậm chí tốt hơn - ngay cả một người mới bắt đầu cũng có thể tạo ra một cách dễ dàng bằng cách sử dụng Microsoft Excel.

Bảng phân bổ nợ cho bạn nhiều thông tin tốt: tổng số tiền phải trả cho người cho vay, phần thanh toán lãi, phần thanh toán chính và số dư nợ cho vay.

Khi khoản vay được khấu hao, đơn giản chỉ có nghĩa là thanh toán lãi và khoản thanh toán chính được kết hợp thành một khoản thanh toán định kỳ và số tiền thanh toán bằng nhau cho mỗi kỳ thanh toán. Nhưng một trong những tính năng thú vị nhất của khoản vay trả dần là với mỗi lần thanh toán, số tiền lãi và tiền gốc được trả thay đổi

Excel là một công cụ cực kỳ hữu ích để tính toán bảng phân bổ riêng của bạn. Bạn thậm chí có thể thử nghiệm với các yếu tố đầu vào khác nhau để xem mức lãi suất, thời gian trả nợ và số tiền vay gốc khác nhau ảnh hưởng như thế nào đến khoản thanh toán khoản vay mà bạn sẽ thực hiện.

Hãy xem một ví dụ để minh họa quá trình.

Giả sử bạn vừa mua một chiếc xe với khoản vay 20.000 đô la phải được hoàn trả hoàn toàn sau 4 năm. Lãi suất hàng năm đối với khoản vay là 3%. Vì khoản vay được khấu hao, các khoản thanh toán hàng tháng sẽ là số tiền giống nhau.

Bước đầu tiên là tính số tiền thanh toán hàng tháng. Điều này có thể được thực hiện bằng cách sử dụng PMT (viết tắt của "thanh toán") chức năng trong Excel.

Bắt đầu bằng cách tạo một bảng tính mới và nhập vào các dữ liệu sau cần thiết để tính toán số tiền thanh toán.

Cột A sẽ chứa các nhãn sau cho dữ liệu của chúng tôi:

A1 = gốc vay gốc (số tiền vay của bạn)

A2 = thời hạn cho vay theo năm

A3 = lãi suất hàng năm

A4 = số lần thanh toán mỗi năm

A5 = số tiền thanh toán (số tiền chúng tôi tính)

Cột B sẽ chứa các giá trị tương ứng cho mỗi nhãn trong cột A:

B1 = $ 20.000

B2 = 4

B3 = 3%

B4 = 12

B5 = giá trị chúng tôi đang tìm kiếm

Chức năng PMT của Excel có thể được tìm thấy trong menu chức năng tài chính. Trước tiên, hãy chọn ô B5, sau đó nhấp vào menu chức năng tài chính trong tab công thức và chọn hàm PMT từ danh sách. Một hộp sẽ được hiển thị nơi bạn nên nhập dữ liệu từ bảng tính giống như trong hình ảnh này:

Có một vài điều quan trọng cần lưu ý ở đây. Đầu tiên, chúng ta cần phải thực hiện hai điều chỉnh quan trọng, bởi vì chúng tôi đang tính thanh toán hàng tháng. Chúng tôi cần phải chia lãi suất hàng năm của chúng tôi cho 12 để nhận lãi suất hàng tháng (Tỷ lệ) và chúng tôi cần nhân 4 với 12 để có tổng số tháng trong giai đoạn trả nợ của chúng tôi (Nper).

Thứ hai, giá trị hiện tại (PV) được nhập dưới dạng giá trị âm vì số tiền đó được nợ cho người cho vay.

Khi hộp của bạn được điền vào, nhấn "OK".

Bảng tính của bạn bây giờ sẽ trông như sau:

Bây giờ chúng tôi sẽ bắt đầu xây dựng bảng phân bổ.

Bỏ qua một hàng bên dưới bảng tính được thực hiện trong phần 1 và bắt đầu từ hàng 7, thực hiện như sau nhãn bắt đầu từ cột A và kết thúc trong cột E:

A7 = Tháng

B7 = Thanh toán

C7 = Lãi

D7 = Nguyên tắc

E7 = Số dư

Đây là năm tiêu đề cho bảng phân bổ.

Bây giờ là lúc để bắt đầu điền vào bảng. Bước đầu tiên là nhập số tháng trong thời hạn của khoản vay. Dưới tiêu đề "tháng", nhập "0" vào ô A8 (thời điểm khoản vay được phát hành). Điền vào các ô tiếp theo theo thứ tự tuần tự thông qua số 48 (sẽ đưa bạn vào ô A56). Một mẹo: một khi bạn đã nhập 0, hãy đảm bảo rằng ô được chọn (A8), sau đó kéo góc dưới bên phải của ô xuống dòng 56. Sau đó chọn "điền chuỗi" từ hộp nhỏ xuất hiện ở bên phải của ô cuối cùng. Điều này sẽ tự động điền vào chuỗi số trong 48 tháng.

Tiếp theo, tất cả các khoản thanh toán có thể được điền bởi vì giá trị của mỗi khoản thanh toán hàng tháng đã được xác định. Bắt đầu từ tháng 1 (không có khoản thanh toán nào vào tháng 0 vì thanh toán diễn ra vào cuối mỗi tháng), điền vào giá trị $ 442,69 cho mỗi tháng 1-48. Sau đó, số tiền lãi và số tiền gốc có thể được tính bằng cách sử dụng các hàm Excel tương ứng của chúng.

Tính lãi và các thành phần chính của các khoản thanh toán sử dụng hàm Excel rất đơn giản và có thể được sử dụng để điền vào toàn bộ bảng phân bổ. Để tính toán tiền lãi, hãy chọn ô C9 và sau đó nhấp vào menu chức năng tài chính trong tab công thức. Chọn chức năng IPMT (cho "thanh toán lãi suất") từ danh sách. Một hộp sẽ được hiển thị nơi bạn nên nhập dữ liệu từ bảng tính giống như trong hình ảnh này:

Lưu ý: Dấu "$" giữa các chữ cái và số là cần thiết khi chúng tôi sao chép các công thức xuống đến các ô còn lại sau đó. “$” Khóa ô được gọi đến, vì vậy các công thức vẫn giữ nguyên khi chúng tôi sao chép chúng xuống các ô còn lại. Bạn có thể khóa các ô (và xem "$") bằng cách nhấn F4.

Tiếp theo, chúng tôi có thể tính số tiền gốc trong khoản thanh toán. Tương tự như tính toán IPMT, chọn ô D9 và sau đó nhấp vào menu chức năng tài chính trong tab công thức. Chọn chức năng PPMT (cho thanh toán chính) từ danh sách Hộp thoại sẽ được hiển thị nơi bạn nhập dữ liệu từ bảng tính giống như trong hình ảnh này:

Lưu ý: Hiệu trưởng có thể được tính bằng cách sử dụng hàm này hoặc đơn giản bằng cách trừ số tiền lãi từ tổng số tiền thanh toán, tương tự, lãi suất có thể được tính nếu chúng tôi tính toán số vốn đầu tiên bằng hàm PPMT và sau đó trừ đi giá trị đó từ tổng số tiền thanh toán. để biết cách sử dụng cả hai công thức, điều này cũng cho phép phần còn lại của bảng được điền hiệu quả hơn như bạn sẽ thấy bên dưới.

Cột cuối cùng (Cột E) là cột số dư còn lại. Trong khoảng thời gian này, chúng tôi đã xác định số tiền lãi và tiền gốc.Trong tháng 0, chưa có khoản thanh toán nào, do đó số dư là 20.000 đô la. Tuy nhiên, chúng tôi muốn lệnh Excel tự động tính toán số dư trong mỗi khoảng thời gian, thay vì manu đồng minh nhập “$ 20,000”, nhập “= B1”, tham khảo bảng trên để tính toán thanh toán. Đối với tháng 1, vì số dư được tính bằng cách trừ số dư của tháng trước đó bằng thanh toán chính của tháng hiện tại, hãy nhập “= E8-D9” vào ô E9. Số dư sẽ tự động được tính. Bảng phân bổ của bạn hiện đã hoàn thành cho tháng 1 và phải giống hệt như sau:

Tất cả dữ liệu cho các tháng còn lại có thể được điền bằng cách chọn các ô B9-E9, sao chép vùng chọn, sau đó dán ô chọn vào ô B10-E10. Vì chúng ta đã nhập các công thức cho mỗi ô và khóa các ô của chúng ta sẽ được sử dụng trong mọi phép tính, các giá trị cho lãi suất, tiền gốc và số dư được tính tự động. Thấy rằng công việc cho tháng 2, toàn bộ bảng có thể được hoàn thành bằng cách chọn cột B-E đến tháng 48 và dán thông tin đã sao chép. Excel hiện đã tính toán mọi giá trị cho bảng phân bổ bằng cách sử dụng các công thức chúng tôi đã nhập. Bảng hoàn thành của bạn sẽ trông giống như sau:

Năm đầu tiên của khoản vay:

Năm cuối cùng của khoản vay:

Ghi chú: Những bảng này chỉ chứa những năm đầu tiên và cuối cùng của bảng phân bổ. Năm 2 và 3 sẽ nằm giữa hai bảng này tạo thành một bảng phân bổ liên tục trong 48 tháng.

Nếu bạn quan tâm đến việc tính toán các khoản thanh toán thế chấp cho một khoản thế chấp có lãi cố định, bạn có thể tự mình thực hiện bảng phân bổ bằng các bước này, hoặc bạn có thể sử dụng máy tính tài chính của chúng tôi để làm điều đó cho bạn. Máy tính thế chấp của chúng tôi sẽ cho bạn thấy khoản thanh toán hàng tháng của bạn cũng như lịch trình khấu hao đầy đủ.