Buzz
Thanh toán khoản vay là hành động trả lại tiền đã vay từ một người cho vay trước đó, thông thường thông qua các khoản thanh toán định kỳ bao gồm cả gốc lẫn lãi. Bạn có biết bạn có thể sử dụng phần mềm Excel để tính toán khoản vay của mình không?
Bài viết này là hướng dẫn từng bước để thiết lập các tính toán khoản vay.
Những điều quan trọng cần nhớ:
- Sử dụng Excel để kiểm soát khoản vay hay thế chấp bằng cách xác định khoản thanh toán hàng tháng, lãi suất và lịch trả nợ.
- Bạn có thể xem xét chi tiết hơn về phân rã khoản vay bằng Excel và tạo lịch trả nợ phù hợp với bạn.
- Có các phương pháp tính toán sẵn để bạn điều chỉnh đáp ứng nhu cầu cụ thể.
- Phân tích và xem xét từng bước của khoản vay có thể giúp quá trình thanh toán trở nên ít gánh nặng và dễ dàng hơn.
Hiểu rõ hơn về Khoản vay của bạn
Sử dụng Excel, bạn có thể hiểu rõ hơn về khoản vay của bạn trong ba bước đơn giản. Bước đầu tiên xác định khoản thanh toán hàng tháng. Bước thứ hai tính toán lãi suất và bước thứ ba xác định lịch trả nợ.
Bạn có thể xây dựng một bảng trong Excel để biết lãi suất, tính toán khoản vay trong suốt thời gian vay, phân rã khoản vay, trả nợ theo định kỳ và khoản thanh toán hàng tháng.
Bước 1: Tính toán Khoả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 vay. Sử dụng lãi suất hàng năm, số tiền gốc và thời gian vay, chúng ta có thể xác định số tiền phải trả hàng tháng.
Công thức, như được hiển thị trong ảnh chụp màn hình ở trên, được viết như sau:
=-PMT(lãi_suất;số_kỳ;giá_trị_hiện_tại;[giá_trị_tương_lai];[loại])
Dấu trừ phía trước PMT là cần thiết vì công thức trả về một số âm. Ba đối số đầu tiên là lãi suất của khoản vay, số kỳ vay (số kỳ), và số tiền gốc đã vay. Hai đối số cuối là tùy chọn; giá trị dư thừa mặc định là không, và có thể thanh toán trước (cho một) hoặc vào cuối (cho không) 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;B412;B3)=PMT((1+3,10%)^(1/12)-1;1012;120000)
Đối với lãi suất, chúng ta sử dụng tỷ lệ lãi suất hàng tháng (kỳ lãi suất), sau đó tính toán số kỳ (120 cho 10 năm nhân 12 tháng) và cuối cùng, chúng ta chỉ định số tiền gốc vay, là $120,000. Khoản thanh toán hàng tháng của chúng tôi sẽ là $1,161.88 trong 10 năm.
Bước 2: Tính toán Lãi suất hàng năm
Chúng ta đã biết cách thiết lập tính toán khoản thanh toán hàng tháng cho một khoản vay. Nhưng chúng ta có thể muốn đặt một khoản thanh toán hàng tháng tối đa mà chúng ta có thể đảm nhận và hiển thị số năm mà chúng ta phải trả nợ khoản vay đó. Vì lí do đó, chúng ta muốn biết tỷ lệ 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, đầu tiên chúng ta tính toán tỷ lệ lãi suất theo kỳ (hàng tháng, trong trường hợp của chúng ta), và sau đó tỷ lệ lãi suất hàng năm. Công thức được sử dụng sẽ là RATE, như được hiển thị trong ảnh chụp màn hình ở trên. Nó được viết như sau:
=RATE(Số_kỳ_trả_nợ;Khoản_trả_hàng_tháng;Số_tiền_gốc_vay;[giá_trị_tương_lai];[loại])
Ba đối số đầu tiên là số kỳ vay (số kỳ), khoản thanh toán hàng tháng để trả nợ khoản vay và số tiền gốc đã vay. Ba đối số cuối là tùy chọn, với giá trị dư thừa mặc định là không; đối số về thời hạn để quản lý sự chấm dứt trước hạn (cho một) hoặc vào cuối (cho 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 toán tỷ lệ cho vay là:
=RATE(12B4;-B2;B3) = RATE(1213;-960;120000)
Lưu ý rằng dữ liệu tương ứng trong khoản thanh toán hàng tháng phải được đặt dấu trừ. Điều này là lý do tại sao trước công thức có dấu trừ. Kỳ lãi suất là 0.294%.
Chúng ta sử dụng công thức = (1 + B5) is 12-1 ^ = (1 + 0.294 %) ^ 12-1 để tính toán tỷ lệ lãi suất hàng năm của khoản vay của chúng ta, là 3.58%. Nói cách khác, để vay $120,000 trong 13 năm và trả hàng tháng $960, chúng ta nên thương lượng vay với mức lãi suất 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 số tiền bạn nợ và lập lịch trả nợ nhằm giảm thiểu bất kỳ khoản phí nào mà bạn có thể phải trả sau này.
Bước 3: Xác định Thời gian của Khoản vay
Bây giờ chúng ta sẽ xem làm thế nào để xác định thời gian của một khoản vay khi bạn biết tỷ lệ lãi suất hàng năm, số tiền gốc vay và khoản thanh toán hàng tháng cần phải trả. Nói cách khác, chúng ta sẽ cần bao lâu để trả một khoản vay thế chấp $120,000 với lãi suất 3.10% hàng năm 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ư được hiển thị trong ảnh chụp màn hình ở trên, và nó được viết như sau:
=NPER(lãi_suất;Khoản_trả_hàng_tháng;Số_tiền_gốc_vay;[giá_trị_tương_lai];[loại])
Ba đối số đầu tiên là tỷ lệ lãi suất hàng năm của khoản vay, khoản thanh toán hàng tháng cần thiết để trả nợ khoản vay và số tiền gốc đã vay. Hai đối số cuối là tùy chọn, với giá trị dư thừa mặc định là không. Đối số về thời hạn để thanh toán trước hạn (cho một) hoặc vào cuối (cho 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)
Dữ liệu tương ứng trong khoản thanh toán hàng tháng phải được đặt dấu trừ. Đây là lý do tại sao trước công thức có dấu trừ. Độ dài trả nợ là 127.97 kỳ (tháng trong trường hợp của chúng ta).
Chúng ta sẽ sử dụng công thức = B5 / 12 = 127.97 / 12 để tính số năm hoàn tất trả nợ khoản vay. Nói cách khác, để vay $120,000 với tỷ lệ lãi suất hàng năm là 3.10%, và trả hàng tháng $1,100, chúng ta nên trả nợ trong 128 tháng, tức là 10 năm và tám tháng.
Bước 4: Phân tích Khoản vay
Một khoản thanh toán vay bao gồm gốc và lãi suất. Lãi suất được tính toán cho mỗi kỳ vay – ví dụ, các khoản thanh toán hàng tháng trong 10 năm sẽ cho chúng ta 120 kỳ vay.
Bảng trên chỉ ra sự phân rã của một khoản vay (tổng số kỳ vay 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 này là giống nhau và được phân rã như sau:
=-PPMT(lãi_suất;kỳ_vay;số_kỳ_vay;gốc_vay;[giá_trị_dư_thừa];[thời_hạn])
Các đối số giống như công thức PMT đã thấy trước đó, ngoại trừ 'kỳ_vay,' được thêm vào để chỉ ra kỳ vay để phân rã khoản vay cho trước gốc và lãi suất. Dưới đây là một ví dụ:
=-PPMT((1+B2)^(1/12)-1;1;B412;B3) = PPMT((1+3,10%)^(1/12)-1;1;1012;120000)
Kết quả được hiển thị trong ảnh chụp màn hình 'Phân rã Khoản vay' qua các kỳ vay được phân tích, đó là 'một;' tức là kỳ vay đầu tiên hoặc tháng đầu tiên. Chúng ta trả $1,161.88 gồm $856.20 gốc và $305.68 lãi suất.
Bước 5: Tính toán Khoản vay trong Excel
Bạn cũng có thể tính toán khoản trả gốc và lãi suất cho nhiều kỳ vay, chẳng hạn như 12 tháng đầu tiên hoặc 15 tháng đầu tiên.
=-CUMPRINC(tỷ_lệ;lượng_vay;gốc_vay;ngày_bắt_đầu;ngày_kết_thúc;loại)
Chúng tôi tìm các đối số, tỷ lệ, độ dài, nguyên tắc và thời hạn (mà bắt buộc) mà chúng ta đã thấy trong phần đầu tiên với công thức PMT. Nhưng ở đây, chúng ta cần thêm các đối số 'start_date' và 'end_date'. 'Start_date' chỉ ra đầu của giai đoạn cần phân tích, và 'end_date' chỉ ra cuối của giai đoạn cần phân tích.
Dưới đâ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,' với các giai đoạn được phân tích từ tháng đầu tiên đến tháng thứ 12 của năm đầu tiên. Trong một năm, chúng ta sẽ trả $10,419.55 cho nguyên tắc và $ 3,522.99 cho lãi suất.
Bước 6: Trả nợ theo lộ trình
Các công thức trước cho phép chúng ta tạo lập lịch trình của chúng ta từng giai đoạn, để biết chúng ta sẽ trả hàng tháng bao nhiêu tiền cho nguyên tắc và lãi suất, và biết được còn lại bao nhiêu tiền để trả.
Bước 7: Tạo lịch trình khoản vay
Để tạo lịch trình khoản vay, chúng ta sẽ sử dụng các công thức khác nhau đã thảo luận ở trên và mở rộng chúng qua số lượng giai đoạn.
Trong cột giai đoạn đầu tiên, nhập '1' làm giai đoạn đầu tiên và sau đó kéo ô xuống. Trong trường hợp của chúng ta, chúng ta cần 120 giai đoạn vì một 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 trả mỗi tháng—điều này là không đổi trong suốt lịch trình khoản vay. Để tính toán số tiền, chèn công thức sau vào ô của giai đoạn đầu tiên của chúng ta:
=-PMT(TP;B4*12;B3) =-PMT((1+3,10%)^(1/12)-1;10*12;120000)
Cột thứ ba là số tiền gốc sẽ được trả hàng tháng. Ví dụ, cho giai đoạn thứ 40, chúng ta sẽ trả lại $945.51 cho nguyên tắc trong tổng số hàng tháng của chúng ta là $1,161.88.
Để tính toán số tiền nguyên tắc được trả, chúng ta 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, cho phép chúng ta sử dụng công thức để tính toán số tiền nguyên tắc được trả hàng tháng của chúng ta để khám phá xem sẽ phải trả bao nhiêu lãi suất:
=-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 khi thanh toán lần thứ 40, chúng ta sẽ phải trả $83,994.69 trên tổng số $120,000.
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 sự kết hợp của nguyên tắc dưới một giai đoạn trước ô chứa nguyên tắc mượn. Giai đoạn 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 rằng sau 120 giai đoạn, khoản vay của chúng ta đã được trả.
Làm thế nào để tạo lịch trả nợ trong Excel?
Để tạo bảng tra tầng hoặc lịch trả nợ trong Excel, bạn sẽ thiết lập một bảng với tổng số kỳ hạn vay trong cột đầu tiên, khoản thanh toán hàng tháng trong cột thứ hai, gốc hàng tháng trong cột thứ ba, lãi hàng tháng trong cột thứ tư và số dư còn lại trong cột thứ năm. Mỗi cột sẽ sử dụng một công thức khác nhau để tính toán các khoản phù hợp chia đều qua số lượng kỳ hạn trả nợ.
Công thức tính khoản thanh toán hàng tháng trong Excel là gì?
Sử dụng hàm PMT trong Excel để tạo công thức: PMT(lãi suất, nper, pv, [fv], [type]). Công thức này cho phép bạn tính toán các khoản thanh toán hàng tháng khi chia lãi suất hàng năm cho 12, cho số tháng trong năm.
Làm thế nào để tạo công thức PMT trong Excel?
Khi bạn tạo một công thức PMT, chẳng hạn như PMT(lãi suất, nper, pv, [fv], [type]), bạn cần một số điểm dữ liệu. Trong công thức này, lãi suất là lãi suất của khoản vay, nper là tổng số lượng thanh toán, pv là giá trị hiện tại, tức là số tiền gốc của khoản vay, fv là giá trị tương lai sau khi khoản vay được thanh toán, và type là khi nào thanh toán được thực hiện. Như đã chỉ ra trong dấu ngoặc vuông, fv và type là các đối số tùy chọn.
Điểm Quan Trọng
Việc trả lại khoản vay có thể gặp nhiều khó khăn, đặc biệt là trong việc tổ chức và trách nhiệm. Để giúp đỡ trong việc đó, Excel có thể tính toán và lập lịch trả nợ của bạn. Bạn có thể xây dựng một bảng trong Excel để theo dõi lãi suất, tính toán khoản vay trong thời gian vay, phân tích phân hủy của khoản vay, phương pháp hủy trả nợ của khoản vay và khoản thanh toán hàng tháng. Điều này có thể giúp quá trình trả lại khoản vay trở nên dễ dàng hơn.
