Mục lục
- Hiểu thế chấp của bạn
- Tính toán thanh toán hàng tháng
- Tính lãi suất hàng năm
- Xác định thời hạn cho vay
- Phân chia khoản vay
- Tính toán khoản vay trong Excel
- Khấu hao khoản vay
- Tạo lịch trình cho vay
Trả nợ vay là hành động trả lại tiền đã vay trước đây từ người cho vay, thường thông qua một loạt các khoản thanh toán định kỳ bao gồm tiền gốc cộng với tiền lãi. Bạn có biết bạn có thể sử dụng chương trình phần mềm Excel để tính toán các khoản trả nợ của mình không?
Bài viết này là một hướng dẫn từng bước để thiết lập các tính toán cho vay.
Chìa khóa chính
- Sử dụng Excel để xử lý khoản thế chấp của bạn thông qua việc xác định khoản thanh toán hàng tháng, lãi suất và lịch trình cho vay của bạn. Bạn có thể xem xét sâu hơn về phân tích khoản vay bằng excel và tạo lịch trả nợ phù hợp với bạn.Có các tính toán có sẵn cho từng bước mà bạn có thể điều chỉnh để đáp ứng nhu cầu cụ thể của mình. Tách xuống và kiểm tra từng bước khoản vay của bạn có thể làm cho quá trình trả nợ cảm thấy ít áp đảo hơn và dễ quản lý hơn.
Hiểu thế chấp của bạn
Sử dụng Excel, bạn có thể hiểu rõ hơn về khoản thế chấp của mình bằng ba bước đơn giản. Bước đầu tiên xác định thanh toán hàng tháng. Bước thứ hai tính lãi suất, và bước thứ ba xác định lịch trình cho vay.
Bạn có thể xây dựng một bảng trong Excel sẽ cho bạn biết lãi suất, cách tính khoản vay trong suốt thời gian vay, phân tách khoản vay, khấu hao và thanh toán hàng tháng.
Tính toán thanh toán hàng tháng
Đầu tiên, đây là cách tính khoản thanh toán hàng tháng cho khoản thế chấp. Sử dụng lãi suất hàng năm, tiền gốc và thời hạn, chúng tôi có thể xác định số tiền phải trả hàng tháng.
Công thức, như thể hiện trong ảnh chụp màn hình ở trên, được viết như sau:
= -PMT (tỷ lệ; chiều dài; hiện tại_value;;)
Dấu trừ trước PMT là cần thiết vì công thức trả về số âm. Ba đối số đầu tiên là tỷ lệ của khoản vay, độ dài của khoản vay (số kỳ) và tiền gốc được vay. Hai đối số cuối cùng là tùy chọn, giá trị còn lại mặc định là 0; phải trả trước (cho một) hoặc cuối (cho 0), cũng là tùy chọn.
Công thức Excel được sử dụng để tính toán khoản thanh toán hàng tháng của khoản vay là:
= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)
Giải thích: Đối với tỷ lệ, chúng tôi sử dụng tỷ lệ hàng tháng (thời gian của tỷ lệ), sau đó chúng tôi tính toán số kỳ (120 trong 10 năm nhân với 12 tháng) và cuối cùng, chúng tôi chỉ ra số tiền gốc đã vay. Khoản thanh toán hàng tháng của chúng tôi sẽ là $ 1.161, 88 trong vòng 10 năm.
Tính lãi suất hàng năm
Chúng tôi đã thấy làm thế nào để thiết lập tính toán một khoản thanh toán hàng tháng cho một thế chấp. Nhưng chúng tôi có thể muốn đặt khoản thanh toán hàng tháng tối đa mà chúng tôi có thể đủ khả năng cũng hiển thị số năm mà chúng tôi sẽ phải trả khoản vay. Vì lý do đó, chúng tôi muốn biết lãi suất hàng năm tương ứng.
Như được hiển thị trong ảnh chụp màn hình ở trên, trước tiên chúng tôi tính tỷ lệ thời gian (hàng tháng, trong trường hợp của chúng tôi), và sau đó là tỷ lệ hàng năm. Công thức được sử dụng sẽ là RATE, như thể hiện trong ảnh chụp màn hình ở trên. Nó được viết như sau:
= RATE (Nper; pmt; Present_value;;)
Ba đối số đầu tiên là độ dài của khoản vay (số kỳ), khoản thanh toán hàng tháng để trả nợ và tiền gốc đã vay. Ba đối số cuối cùng là tùy chọn và giá trị còn lại mặc định là 0; đối số thuật ngữ để quản lý sự trưởng thành trước (cho một) hoặc ở cuối (cho số không) cũng là tùy chọn. Cuối cùng, đối số ước tính là tùy chọn nhưng có thể đưa ra ước tính ban đầu về tỷ lệ.
Công thức Excel được sử dụng để tính lãi suất cho vay là:
= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)
Lưu ý: dữ liệu tương ứng trong thanh toán hàng tháng phải được cung cấp dấu âm. Đây là lý do tại sao có một dấu trừ trước công thức. Tỷ lệ thời gian là 0, 294%.
Chúng tôi sử dụng công thức = (1 + B5) là 12-1 ^ = (1 + 0, 294%) ^ 12-1 để có được tỷ lệ cho vay hàng năm, là 3, 58%. Nói cách khác, để vay 120.000 đô la trong 13 năm để trả 960 đô la hàng tháng, chúng ta nên thương lượng khoản vay với tỷ lệ tối đa 3, 58% hàng năm.
Sử dụng Excel là một cách tuyệt vời để theo dõi những gì bạn nợ và sắp xếp lịch trả nợ để giảm thiểu mọi khoản phí mà bạn có thể phải chịu.
Xác định thời hạn cho vay
Bây giờ chúng ta sẽ xem cách xác định thời hạn của khoản vay khi bạn biết tỷ lệ hàng năm, tiền gốc đã vay và khoản thanh toán hàng tháng sẽ được hoàn trả. Nói cách khác, chúng ta sẽ cần bao lâu để trả một khoản thế chấp $ 120.000 với tỷ lệ 3, 10% và khoản thanh toán hàng tháng là $ 1, 100?
Công thức chúng ta sẽ sử dụng là NPER, như trong ảnh chụp màn hình ở trên, và nó được viết như sau:
= NPER (tỷ lệ; pmt; Present_value;;)
Ba đối số đầu tiên là tỷ lệ hàng năm của khoản vay, khoản thanh toán hàng tháng cần thiết để trả nợ và tiền gốc đã vay. Hai đối số cuối cùng là tùy chọn, giá trị còn lại mặc định là không. Đối số thuật ngữ phải trả trước (cho một) hoặc ở cuối (cho số không) cũng là tùy chọn.
= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)
Lưu ý: dữ liệu tương ứng trong thanh toán hàng tháng phải được cung cấp dấu âm. Đây là lý do tại sao chúng ta có một dấu trừ trước công thức. Thời hạn hoàn trả là 127, 97 kỳ (tháng trong trường hợp của chúng tôi).
Chúng tôi sẽ sử dụng công thức = B5 / 12 = 127.97 / 12 cho số năm để hoàn thành khoản vay. Nói cách khác, để vay 120.000 đô la, với tỷ lệ hàng năm là 3, 10% và trả 1.100 đô la hàng tháng, chúng tôi nên hoàn trả các kỳ hạn trong 128 tháng hoặc 10 năm và 8 tháng.
Phân chia khoản vay
Một khoản thanh toán cho vay bao gồm gốc và lãi. Tiền lãi được tính cho mỗi kỳ hạn, ví dụ, các khoản hoàn trả hàng tháng trong vòng 10 năm sẽ cho chúng tôi 120 kỳ.
Bảng trên cho thấy sự cố của khoản vay (tổng thời gian bằng 120) bằng cách sử dụng các công thức PPMT và IPMT. Các đối số của hai công thức là như nhau và được chia nhỏ như sau:
= -PPMT (tỷ lệ; num_ period; chiều dài; hiệu trưởng;;)
Các đối số tương tự như đối với công thức PMT đã thấy, ngoại trừ "num_apse", được thêm vào để hiển thị khoảng thời gian chia nhỏ khoản vay được trả gốc và lãi. Đây là một ví dụ:
= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12; 120000)
Kết quả được hiển thị trong ảnh chụp màn hình ở trên "Phân tách khoản vay" trong khoảng thời gian được phân tích, đó là "một;" đó là giai đoạn đầu tiên hoặc tháng đầu tiên Chúng tôi trả $ 1, 161, 88 được chia thành $ 85, 20 và tiền lãi $ 305, 68.
Tính toán khoản vay trong Excel
Cũng có thể tính toán trả gốc và lãi trong nhiều giai đoạn như 12 tháng đầu hoặc 15 tháng đầu.
= -CUMPRINC (tỷ lệ; chiều dài; gốc; start_date; end_date; loại)
Chúng tôi tìm thấy các đối số, tỷ lệ, độ dài, hiệu trưởng và thuật ngữ (bắt buộc) mà chúng tôi đã thấy trong phần đầu tiên với công thức PMT. Nhưng ở đây, chúng ta cũng cần các đối số "start_date" và "end_date". "Start_date" chỉ ra thời điểm bắt đầu của giai đoạn được phân tích và "end_date" chỉ ra sự kết thúc của giai đoạn được phân tích.
Đây là một ví dụ:
= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)
Kết quả được hiển thị trong ảnh chụp màn hình "Cumul 1st year", do đó, các giai đoạn được phân tích nằm trong khoảng từ một đến 12 của giai đoạn đầu tiên (tháng đầu tiên) đến thứ mười hai (tháng thứ 12). Trong một năm, chúng tôi sẽ trả $ 10, 419, 55 tiền gốc và $ 3, 522, 99 tiền lãi.
Khấu hao khoản vay
Các công thức trước cho phép chúng tôi tạo thời gian biểu theo từng giai đoạn, để biết chúng tôi sẽ trả bao nhiêu tiền gốc và lãi hàng tháng, và để biết số tiền còn lại phải trả là bao nhiêu.
Tạo lịch trình cho vay
Để tạo lịch cho vay, chúng tôi sẽ sử dụng các công thức khác nhau được thảo luận ở trên và mở rộng chúng theo số kỳ.
Trong cột giai đoạn đầu tiên, nhập "1" làm khoảng thời gian đầu tiên và sau đó kéo ô xuống. Trong trường hợp của chúng tôi, chúng tôi cần 120 kỳ kể từ khi thanh toán khoản vay 10 năm nhân với 12 tháng bằng 120.
Cột thứ hai là số tiền hàng tháng chúng ta cần phải trả mỗi tháng, không đổi trong toàn bộ lịch trình cho vay. Để tính toán số tiền, hãy chèn công thức sau vào ô của giai đoạn đầu tiên của chúng tôi:
= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)
Cột thứ ba là tiền gốc sẽ được hoàn trả hàng tháng. Ví dụ: trong giai đoạn thứ 40, chúng tôi sẽ hoàn trả $ 945, 51 tiền gốc trên tổng số tiền hàng tháng là $ 1, 161, 88.
Để tính số tiền gốc được chuộc lại, chúng tôi sử dụng công thức sau:
= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)
Cột thứ tư là tiền lãi mà chúng tôi sử dụng công thức để tính số tiền gốc trả cho số tiền hàng tháng của chúng tôi để khám phá số tiền lãi phải trả:
= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)
Cột thứ năm chứa số tiền còn lại phải trả. Ví dụ: sau lần thanh toán thứ 40, chúng tôi sẽ phải trả 83, 994, 69 đô la trên 120.000 đô la.
Công thức như sau:
= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)
Công thức sử dụng kết hợp tiền gốc trong một khoảng thời gian trước ô chứa tiền gốc đã vay. Thời kỳ này bắt đầu thay đổi khi chúng ta sao chép và kéo ô xuống. Bảng dưới đây cho thấy vào cuối 120 kỳ, khoản vay của chúng tôi được hoàn trả.
