Bạn có biết bạn có thể sử dụng Excel để tính toán khoản hoàn trả vốn vay của bạn? Bài viết này sẽ hướng dẫn bạn qua tất cả các bước cần thiết để làm như vậy. Sử dụng Excel, bạn có thể hiểu rõ về thế chấp của mình bằng ba bước đơn giản. Bước đầu tiên là xác định thanh toán hàng tháng. Thứ hai là để phát hiện ra mức lãi suất, và thứ ba là tìm ra lịch trình cho vay. Để làm như vậy, bạn có thể xây dựng một bảng trong Excel sẽ cho bạn biết: Các mức giá thấp nhất; tính khoản vay trong thời hạn; phân hủy một khoản vay, cũng như khấu hao và tính cho tiền thuê hàng tháng.

Tính toán khoản vay cho thuê hàng tháng

Trước tiên, hãy xem làm thế nào để thực hiện tính toán khoản thanh toán hàng tháng cho một thế chấp. Nói cách khác, sử dụng tỷ lệ Iinterest hàng năm, hiệu trưởng và thời gian, 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:

- Dấu hiệu trừ ở phía trước của PMT là cần thiết, vì công thức trả về một số âm (negative number). Ba đối số đầu tiên là tỷ lệ khoản vay, thời hạn của khoản vay (số kỳ) và Bên vay mượn. Hai đối số cuối cùng là tùy chọn, giá trị còn lại mặc định là 0, trả trước (cho 1) hoặc ở cuối (cho 0), cũng là tùy chọn.

Công thức Excel dùng để tính 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ệ sử dụng thời gian là tỷ lệ hàng tháng, chúng tôi tính số kỳ (tháng ở đây 120 trong 10 năm nhân với 12 tháng) và cuối cùng chúng tôi cho biết khoản vay gốc. Thanh toán hàng tháng của chúng tôi sẽ là $ 1, 161. 88 trong 10 năm.

Tính toán thế chấp cho lãi suất

Chúng ta đã thấy làm thế nào để thiết lập tính toán 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 mộ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 ta phải hoàn trả. Vì lý do đó, chúng tôi muốn biết mức lãi suất tương ứng hàng năm.

Tính lãi suất cho khoản vay

Như được minh họa trong ảnh chụp màn hình ở trên, trước tiên chúng ta tính tỷ lệ thời kỳ (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; [future_value]; [type])

Ba đối số đầu tiên là chiều dài khoản vay (số kỳ), khoản thanh toán hàng tháng để trả nợ gốc vay. Ba đối số cuối cùng là không bắt buộc và giá trị còn lại mặc định là 0, thì đối số dài hạn để quản lý sự trưởng thành trước (cho 1) hoặc ở cuối (cho 0) cũng là tùy chọn, và cuối cùng đối số ước lượng là tùy chọn, nhưng có thể đưa ra ước tính ban đầu về tỷ lệ.

Công thức Excel dùng để tính lãi suất cho vay là:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

Lưu ý: các dữ liệu tương ứng trong thanh toán hàng tháng phải có một dấu hiệu tiêu cực. Đây là lý do tại sao một dấu trừ trước công thức. Thời gian của chúng tôi là 0.294%.

Chúng ta sử dụng công thức

= (1 + B5) là 12-1 ^ = (1 + 0 294%) ^ 12-1

để có tỷ lệ nợ vay của chúng ta là 3. 58 %. Nói cách khác, để mượn 120.000 $ trên 13 năm để thanh toán hàng tháng 960 $, chúng ta nên thương lượng một khoản vay hàng năm 3. Tỷ lệ tối đa 58%.

Tính toán thế chấp cho độ dài của khoản vay

Bây giờ chúng ta sẽ thấy làm thế nào để có được thời hạn của một khoản vay khi bạn biết mức lãi suất hàng năm, khoản vay gốc và khoản thanh toán hàng tháng phải trả. Nói cách khác, chúng tôi cần phải trả một khoản thế chấp 120.000 đô la với khoản tiền là 3,10% và thanh toán hàng tháng là $ 1, 100 như thế nào?

Số lần trả nợ vay Công thức chúng tôi sẽ sử dụng là NPER, như thể hiện trong ảnh chụp màn hình ở trên, và nó được viết như sau: = NPER (rate, pmt; present_value; [future_value]; [loại])

Ba lập luận đầu tiên là tỷ lệ vay hàng năm, khoản thanh toán hàng tháng cần thiết để hoàn trả khoản vay, và khoản vay chính Hai đối số cuối cùng là tùy chọn, giá trị còn lại mặc định là 0, thuật ngữ đối số trả trước (cho 1) hoặc ở cuối (cho 0) 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 ý: các dữ liệu tương ứng trong thanh toán hàng tháng phải có một dấu hiệu tiêu cực. Đây là lý do tại sao chúng ta có một dấu trừ trước công thức. Thời gian hoàn trả là 127. 97 kỳ (tháng trong trường hợp của chúng tôi).

Chúng ta sẽ sử dụng công thức = B5 / 12 = 127. 97/12 cho số năm để hoàn thành việc hoàn trả khoản vay. Nói cách khác, để vay $ 120,000, với mức phí hàng năm 3,10% và phải trả $ 1, 100 hàng tháng, chúng ta nên hoàn trả kỳ hạn thanh toán cho 128 tháng hoặc 10 năm và 8 tháng.

Xử lý khoản vay

Khoản vay bao gồm hai khoản tiền, gốc và lãi. Lãi suất được tính cho từng giai đoạn, ví dụ như việc trả nợ hàng tháng trong 10 năm, sẽ cho chúng tôi 120 kỳ.

Ảnh chụp màn hình ở trên cho biết sự cố về khoản vay (tổng thời gian bằng 120), sử dụng công thức PPMT và IPMT. Các đối số của hai công thức đều giống nhau và được chia nhỏ như sau:

= - PPMT (tỷ lệ, số lẻ, chiều dài, gốc, [còn lại]; [terme])

= - INTPER (rate, num_period; Các đối số cũng giống như đối với công thức PMT được thấy trong phần thứ nhất, ngoại trừ num_period được thêm vào để hiển thị khoảng thời gian để phá vỡ khoản vay, cho nợ gốc và lãi cho nó. Ví dụ:

= - PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12 ) -1, 1, 10 * 12, 120000)

= - INTPER ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = INTPER ((1 + 3, 10 %) ^ (1/12) -1, 1, 10 * 12; 120000)

Kết quả là kết quả được hiển thị trong ảnh chụp màn hình "Loan Decomposition" trong giai đoạn được phân tích là "1", do đó giai đoạn đầu tiên , hoặc tháng đầu tiên.Đối với loại này, chúng tôi phải trả $ 1161. 88, chia thành $ 856, 20 principal và $ 305. 68 quan tâm.

Tính toán khoản vay của Excel

Bây giờ bạn cũng có thể tính toán trả nợ gốc và lãi trong một số kỳ, ví dụ như 12 tháng đầu hoặc 15 tháng đầu tiên.

= - CUMPRINC (rate, length, principal, start_date, end_date; type)

= - CUMIPMT (tỷ lệ, chiều dài, hiệu trưởng, ngày bắt đầu, end_date, type)

Chúng ta tìm thấy các đối số, tỷ lệ, chiều dài, chính và thuật ngữ (bắt buộc) mà chúng ta đã thấy ở 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. Thứ nhất cho thấy sự bắt đầu của giai đoạn được phân tích và lần thứ hai kết thúc. Chúng ta hãy lấy một ví dụ:

= - CUMPRINC ((1 + B2) ^ (1/12) -1, B4 * 12, B3; 1; 12; 0)

= - CUMPRINC ((1 + 3, (1 + B2) ^ (1/12) -1; B4 * 12; B3 (1 + 3, 10%) ^ (1/12) -1, 10 * 12; 120000; 1; 12; 0)

Kết quả là một trong những thể hiện trong ảnh chụp màn hình "Cumul 1 năm", do đó, các giai đoạn phân tích từ 1 đến 12, của giai đoạn đầu tiên (tháng đầu tiên) đến mười hai (tháng thứ 12). Hơn một năm, chúng tôi sẽ trả $ 10 419, 55 Principal và $ 3 522. 99 Lãi.

Khấu trừ khoản vay

Các công thức trước tiên cho phép chúng ta tạo ra thời biểu theo thời gian, chúng ta sẽ phải trả hàng tháng bao nhiêu tiền gốc và lãi và số tiền còn lại phải trả là bao nhiêu.

Tạo một kế hoạch cho vay trong Excel

Để tạo một kế hoạ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 qua số kỳ.

Trong cột thời kỳ đầu tiên, chỉ cần nhập "1" làm khoảng thời gian đầu tiên, 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 = 120.

Cột thứ hai là số tiền hàng tháng mà chúng tôi cần thanh toán mỗi tháng, không đổi trong toàn bộ thời hạn khoản vay. Để tính toán nó, chèn công thức sau vào ô của giai đoạn đầu tiên:

= - PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12 ) -1; 10 * 12; 120000)

Cột thứ ba là hiệu trưởng sẽ được thanh toán hàng tháng. Ví dụ: trong giai đoạn 40, chúng tôi sẽ trả lại 945 đô la. 51 trên tổng số tiền hàng tháng của chúng tôi là $ 1, 161. 88. Để tính toán 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à lãi suất, chúng tôi tính khoản nợ gốc trên số tiền hàng tháng của chúng tôi để khám phá nhiều khoản lãi sẽ được thanh toán, sử dụng công thức:

= - 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 để trả. Ví dụ: sau khoản thanh toán thứ 40, chúng tôi sẽ phải trả $ 83, 994. 69 trên 120.000 đô la Mỹ. Công thức này như sau:

= $ B $ 3 + CUMPRINC (TP, $ B $ 4 * 12; $ B $ 3; 1; A18; 0)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; 1; 0)

Công thức sử dụng kết hợp gốc trong một khoảng thời gian trước với ô chứa nợ chính. Giai đoạn này bắt đầu thay đổi khi chúng ta sao chép và kéo tế bào xuống.Ảnh chụp màn hình dưới đây cho thấy rằng vào cuối 120 thời hạn khoản vay của chúng tôi được hoàn trả.