Tác Giả:
Marcus Baldwin
Ngày Sáng TạO:
19 Tháng Sáu 2021
CậP NhậT Ngày Tháng:
11 Có Thể 2024
NộI Dung
Các phần khácWikiHow này hướng dẫn bạn cách tính toán các chi phí liên quan đến thế chấp như lãi suất, các khoản thanh toán hàng tháng và tổng số tiền vay bằng bảng tính Microsoft Excel. Sau khi thực hiện xong việc này, bạn cũng có thể tạo lịch thanh toán sử dụng dữ liệu của mình để tạo kế hoạch thanh toán hàng tháng nhằm đảm bảo bạn trả hết nợ thế chấp đúng hạn.
Các bước
Phương pháp 1/2: Tạo Máy tính Thế chấp
Mở Microsoft Excel. Nếu bạn chưa cài đặt Excel trên máy tính của mình, bạn có thể sử dụng tiện ích mở rộng Excel trực tuyến của Outlook ở vị trí của nó. Trước tiên, bạn có thể cần tạo một tài khoản Outlook.
Lựa chọn Sổ làm việc trống. Thao tác này sẽ mở một bảng tính Excel mới.
Tạo cột "Danh mục" của bạn. Điều này sẽ nằm trong cột "A". Để làm như vậy, trước tiên bạn nên nhấp và kéo dải phân cách giữa cột "A" và "B" sang bên phải ít nhất ba khoảng trắng để bạn không bị hết chỗ viết. Bạn sẽ cần tổng cộng tám ô cho các danh mục sau:- Số tiền cho vay $
- Lãi suất hàng năm
- Khoản vay trọn đời (tính theo năm)
- Số lần thanh toán mỗi năm
- Tổng số lần thanh toán
- Thanh toán mỗi kỳ
- Tổng thanh toán
- Chi phí lãi vay
Nhập các giá trị của bạn. Những thứ này sẽ nằm trong cột "B" của bạn, ngay bên phải cột "Danh mục". Bạn sẽ cần nhập các giá trị thích hợp cho khoản thế chấp của mình.- Của bạn Số tiền vay giá trị là tổng số tiền bạn nợ.
- Của bạn Lãi suất hàng năm giá trị là phần trăm lãi suất tích lũy mỗi năm.
- Của bạn Khoản vay trọn đời giá trị là khoảng thời gian bạn có trong nhiều năm để trả hết khoản vay.
- Của bạn Số lần thanh toán mỗi năm giá trị là số lần bạn thanh toán trong một năm.
- Của bạn Tổng số lần thanh toán giá trị là giá trị Khoản vay trọn đời nhân với giá trị Khoản thanh toán mỗi năm.
- Của bạn Thanh toán mỗi kỳ giá trị là số tiền bạn phải trả cho mỗi lần thanh toán.
- Của bạn Tổng thanh toán giá trị bao gồm tổng chi phí của khoản vay.
- Của bạn Chi phí lãi vay giá trị xác định tổng chi phí lãi trong suốt thời gian của giá trị Khoản vay trọn đời.
Tìm ra tổng số lần thanh toán. Vì đây là giá trị Khoản vay trọn đời nhân với giá trị Khoản thanh toán mỗi năm nên bạn không cần công thức để tính giá trị này.
- Ví dụ: nếu bạn thanh toán một tháng cho khoản vay có thời hạn 30 năm, bạn sẽ nhập "360" vào đây.
Tính số tiền phải trả hàng tháng. Để tìm ra số tiền bạn phải trả cho khoản thế chấp mỗi tháng, hãy sử dụng công thức sau: "= -PMT (Lãi suất / Khoản thanh toán mỗi năm, Tổng số lần thanh toán, Số tiền vay, 0)".
- Đối với ảnh chụp màn hình được cung cấp, công thức là "-PMT (B6 / B8, B9, B5,0)". Nếu các giá trị của bạn hơi khác, hãy nhập chúng với các số ô thích hợp.
- Lý do bạn có thể đặt dấu trừ trước PMT là vì PMT trả lại số tiền được trừ vào số tiền còn nợ.
Tính tổng chi phí của khoản vay. Để thực hiện việc này, chỉ cần nhân giá trị "thanh toán mỗi kỳ" với giá trị "tổng số lần thanh toán".
- Ví dụ: nếu bạn thực hiện 360 lần thanh toán $ 600,00, tổng chi phí khoản vay của bạn sẽ là $ 216,000.
Tính tổng chi phí lãi vay. Tất cả những gì bạn cần làm ở đây là trừ tổng số tiền vay ban đầu của bạn khỏi tổng chi phí khoản vay mà bạn đã tính ở trên. Khi bạn đã hoàn tất, máy tính thế chấp của bạn đã hoàn tất.
Phương pháp 2 trên 2: Lập lịch thanh toán (Phân bổ)
Tạo mẫu Lịch biểu Thanh toán của bạn ở bên phải mẫu Máy tính Thế chấp của bạn. Vì Lịch biểu thanh toán sử dụng Máy tính thế chấp để cung cấp cho bạn đánh giá chính xác về số tiền bạn sẽ nợ / trả hết mỗi tháng, nên những điều này sẽ đi trong cùng một tài liệu. Bạn sẽ cần một cột riêng cho từng danh mục sau:
- Ngày - Ngày thanh toán được đề cập được thực hiện.
- Số tiền phải trả) - Số thanh toán trong tổng số thanh toán của bạn (ví dụ: "1", "6", v.v.).
- Thanh toán ($) - Tổng số tiền đã thanh toán.
- Quan tâm - Số tiền trong tổng số tiền lãi phải trả.
- Hiệu trưởng - Số tiền của tổng số tiền được trả không phải là lãi suất (ví dụ: thanh toán khoản vay).
- Thanh toán thêm - Số tiền của bất kỳ khoản thanh toán bổ sung nào bạn thực hiện.
- Tiền vay - Số tiền vay của bạn còn lại sau khi thanh toán.
Thêm số tiền vay ban đầu vào lịch thanh toán. Điều này sẽ đi vào ô trống đầu tiên ở đầu cột "Khoản vay".
Thiết lập ba ô đầu tiên trong "Ngày"và" Thanh toán (Số) ". Trong cột ngày, bạn sẽ nhập ngày mà bạn vay, cũng như hai ngày đầu tiên bạn định thanh toán hàng tháng (ví dụ: 2/1/2005, 3/1/2005 và 4 / 1/2005). Đối với cột Thanh toán, hãy nhập ba số thanh toán đầu tiên (ví dụ: 0, 1, 2).
Sử dụng chức năng "Điền" để tự động nhập phần còn lại của giá trị Ngày và Thanh toán của bạn. Để làm như vậy, bạn cần thực hiện các bước sau:
- Chọn mục nhập đầu tiên trong cột (Số) Thanh toán của bạn.
- Kéo con trỏ xuống cho đến khi bạn đánh dấu con số áp dụng cho số lần thanh toán bạn sẽ thực hiện (ví dụ: 360). Vì bạn đang bắt đầu từ "0", bạn sẽ kéo xuống hàng "362".
- Nhấp vào Điền ở góc trên cùng bên phải của trang Excel.
- Chọn Sê-ri.
- Đảm bảo rằng "Tuyến tính" được chọn trong phần "Loại" (khi bạn thực hiện cột Ngày của mình, "Ngày" sẽ được chọn).
- Bấm OK.
Chọn ô trống đầu tiên trong cột "Thanh toán ($)".
Nhập công thức Thanh toán cho mỗi kỳ. Công thức tính toán giá trị Thanh toán cho mỗi kỳ dựa trên thông tin sau ở định dạng sau: "Thanh toán mỗi kỳ
- Bạn phải mở đầu công thức này bằng thẻ "= IF" để hoàn thành các phép tính.
- Giá trị "Lãi suất hàng năm", "Số lần thanh toán mỗi năm" và "Thanh toán mỗi kỳ" của bạn sẽ cần được viết như sau: $ letter $ number. Ví dụ: $ B $ 6
- Với ảnh chụp màn hình ở đây, công thức sẽ giống như sau: "= IF ($ B $ 10
nhấn ↵ Nhập. Điều này sẽ áp dụng công thức Thanh toán mỗi kỳ cho ô đã chọn của bạn.
- Để áp dụng công thức này cho tất cả các ô tiếp theo trong cột này, bạn cần sử dụng tính năng "Điền" mà bạn đã sử dụng trước đó.
Chọn ô trống đầu tiên trong cột "Sở thích".
Nhập công thức tính giá trị Lãi suất của bạn. Công thức tính giá trị Lãi suất của bạn dựa trên thông tin sau ở định dạng sau: "Tổng khoản vay * Lãi suất hàng năm / Số lần thanh toán mỗi năm".
- Công thức này phải được đặt trước bằng dấu "=" để hoạt động.
- Trong ảnh chụp màn hình được cung cấp, công thức sẽ giống như sau: "= K8 * $ B $ 6 / $ B $ 8" (không có dấu ngoặc kép).
nhấn ↵ Nhập. Điều này sẽ áp dụng công thức Sở thích cho ô đã chọn của bạn.
- Để áp dụng công thức này cho tất cả các ô tiếp theo trong cột này, bạn cần sử dụng tính năng "Điền" mà bạn đã sử dụng trước đó.
Chọn ô trống đầu tiên trong cột "Chính".
Nhập công thức chính. Đối với công thức này, tất cả những gì bạn cần làm là trừ giá trị "Tiền lãi" cho giá trị "Thanh toán ($)".
- Ví dụ: nếu ô "Lãi suất" của bạn là H8 và ô "Thanh toán ($)" là G8, bạn sẽ nhập "= G8 - H8" mà không có dấu ngoặc kép.
nhấn ↵ Nhập. Điều này sẽ áp dụng công thức Chính cho ô đã chọn của bạn.
- Để áp dụng công thức này cho tất cả các ô tiếp theo trong cột này, bạn cần sử dụng tính năng "Điền" mà bạn đã sử dụng trước đó.
Chọn ô trống đầu tiên trong cột "Khoản vay". Số này phải nằm ngay dưới số tiền vay ban đầu bạn đã vay (ví dụ: ô thứ hai trong cột này).
Nhập công thức Khoản vay. Việc tính giá trị Khoản vay bao gồm: "Khoản vay" - "Tiền gốc" - "Phụ phí".
- Đối với ảnh chụp màn hình được cung cấp, bạn nhập "= K8-I8-J8" mà không có trích dẫn.
nhấn ↵ Nhập. Điều này sẽ áp dụng công thức Khoản vay cho ô đã chọn của bạn.
- Để áp dụng công thức này cho tất cả các ô tiếp theo trong cột này, bạn cần sử dụng tính năng "Điền" mà bạn đã sử dụng trước đó.
Sử dụng chức năng Điền để hoàn thành các cột công thức của bạn. Thanh toán của bạn phải giống nhau hoàn toàn. Lãi suất và số tiền cho vay sẽ giảm, trong khi giá trị của tiền gốc tăng lên.
Tổng hợp tiến độ thanh toán. Ở cuối bảng, tổng các khoản thanh toán, tiền lãi và tiền gốc. Tham chiếu chéo các giá trị này với máy tính thế chấp của bạn. Nếu chúng khớp, bạn đã thực hiện đúng các công thức.
- Số tiền gốc của bạn phải khớp chính xác với số tiền vay ban đầu.
- Các khoản thanh toán của bạn phải khớp với tổng chi phí của khoản vay từ máy tính thế chấp.
- Lãi suất của bạn phải khớp với chi phí lãi suất từ máy tính thế chấp.
Máy tính thanh toán thế chấp mẫu
Câu hỏi và câu trả lời của cộng đồng
Nếu tôi thanh toán thêm khoản vay, có công thức nào sẽ tính lại khoản thanh toán hàng tháng không?
Bao gồm 2 cột, một cho thành phần lãi suất và một cho thành phần chính. Thêm một cột mới bên cạnh chức danh hiệu trưởng "hiệu trưởng bổ sung". Bây giờ trừ nó ra khỏi tiền gốc ban đầu.
Trong Excel, công thức tính số tiền vay là gì nếu tôi biết khoản thanh toán, lãi suất và thời hạn của khoản vay?
Sử dụng các công thức tương tự được đề cập trong phương pháp 1 ở trên; tuy nhiên, hãy sử dụng Goal Seek để tự động tính số tiền vay.
Khi tôi thêm các khoản thanh toán bổ sung vào khoản vay, điều này sẽ điều chỉnh lịch thanh toán cho phù hợp, nhưng có công thức nào để cho tôi biết số tiền lãi hoặc tháng tôi tiết kiệm được bằng cách thanh toán không?
Thiết lập hai bảng tính, một bảng chứa "khoản vay cơ bản" của bạn mà không cần thanh toán thêm và bảng thứ hai chứa các khoản thanh toán bổ sung. "Tổng các khoản thanh toán" sẽ giảm khi bạn thêm các khoản thanh toán bổ sung trong trang tính thứ hai. Trừ giá trị này khỏi tổng các khoản thanh toán "khoản vay cơ bản" để xem bạn tiết kiệm được bao nhiêu bằng cách bỏ thêm tiền vào khoản vay của mình.
Bước 4 của Phương pháp 2 tham chiếu các công thức ở đầu ảnh chụp màn hình và biểu đồ tham chiếu. Đây là những đâu?
Tùy chọn Điền thường nằm trên tab Trang đầu trong Excel, trong phần "Chỉnh sửa". Tôi không chắc bạn đang đề cập đến điều gì bằng "công thức ở đầu ảnh chụp màn hình" hoặc "biểu đồ tham chiếu" vì cả hai đều không được đề cập trong bài viết này.
Công thức thanh toán theo kỳ sử dụng lãi suất đơn giản là gì?
Sử dụng công cụ tính lãi đơn giản này để tìm A, Giá trị Đầu tư Cuối cùng, sử dụng công thức lãi đơn giản: A = P (1 + rt) trong đó P là Số tiền gốc được đầu tư với Lãi suất R% mỗi kỳ cho t Số của Khoảng thời gian.
Nếu lãi suất cố định trong 5 năm đầu, nhưng sau đó chuyển sang lãi suất khác, làm cách nào để tính lại bằng cách sử dụng lãi suất mới trong các năm còn lại?
Đối với Thế chấp có thể điều chỉnh, bạn không thể dự đoán chính xác tỷ lệ phần trăm trong tương lai. Thêm cột điều chỉnh lãi suất cho mỗi lần thanh toán. Trong năm năm đầu tiên, mức điều chỉnh là 0. Đối với năm thứ 6, điều chỉnh = mức tăng tối đa hàng năm. Đối với năm thứ 7, hãy điều chỉnh = đến mức tăng tối đa hàng năm Khi bạn đạt đến mức lãi tối đa được phép cho khoản vay của mình, hãy tiếp tục sử dụng khoản đó. Với phương pháp này, bạn dự đoán trường hợp xấu nhất cho ARM.
Làm cách nào để tính khoản thanh toán khoản vay trong MS Excel? Câu trả lời
Lời khuyên
- Dấu "-" phía trước hàm PMT là cần thiết, nếu không giá trị sẽ là số âm. Ngoài ra, lý do lãi suất được chia cho số lần thanh toán là vì lãi suất là theo năm, không phải theo tháng.
- Để Tự động điền ngày bằng bảng tính Google Dogs, hãy nhập ngày vào ô đầu tiên rồi chuyển tiếp một tháng vào ô thứ hai, sau đó đánh dấu cả hai ô và thực hiện Tự động điền như được mô tả ở trên. Nếu Tự động điền nhận dạng một mẫu, nó sẽ Tự động điền cho bạn.
- Trước tiên, hãy thử tạo bảng giống như ví dụ, nhập các giá trị ví dụ. Khi mọi thứ đã kiểm tra và bạn chắc chắn rằng công thức đúng, hãy nhập các giá trị của riêng bạn.
