Công thức tính khoản thanh toán niên kim trong Excel

Thanh toán khoản vay dễ dàng hơn và nhanh hơn để tính toán với Microsoft Office Excel. Nhiều thời gian hơn được dành cho việc tính toán thủ công. Bài viết này sẽ tập trung vào các khoản thanh toán theo niên kim, các tính năng của cách tính, ưu điểm và nhược điểm của chúng.

Thanh toán niên kim là gì

Phương thức trả nợ hàng tháng, trong đó số tiền ký quỹ không thay đổi trong toàn bộ thời gian của khoản vay. Những thứ kia. vào những ngày nhất định của mỗi tháng, một người gửi một số tiền cụ thể cho đến khi khoản vay được hoàn trả đầy đủ.

Hơn nữa, tiền lãi của khoản vay đã được tính vào tổng số tiền phải trả cho ngân hàng.

Niên kim phân loại

Các khoản thanh toán hàng năm có thể được chia thành các loại sau:

1. Đã sửa. Các khoản thanh toán không thay đổi có tỷ lệ cố định bất kể điều kiện bên ngoài.
2. Tiền tệ. Khả năng thay đổi số tiền thanh toán trong trường hợp tỷ giá hối đoái giảm hoặc tăng.
3. được lập chỉ mục. Thanh toán tùy theo mức độ, chỉ số lạm phát. Trong thời gian cho vay, quy mô của chúng thường thay đổi.
4. Biến. Niên kim, có thể thay đổi tùy thuộc vào tình trạng của hệ thống tài chính, các công cụ.

Chú ý! Các khoản thanh toán cố định thích hợp hơn cho tất cả những người đi vay, vì ít rủi ro.

Ưu điểm và nhược điểm của thanh toán niên kim

Để hiểu rõ hơn về đề tài, cần nghiên cứu những nét chính của hình thức thanh toán tiền vay này. Nó có những ưu điểm sau:

• Thiết lập số tiền thanh toán cụ thể và ngày thanh toán.
• Tính khả dụng cao cho người vay. Hầu như ai cũng có thể nộp đơn xin hưởng niên kim, bất kể tình hình tài chính của họ như thế nào.
• Khả năng giảm số tiền trả góp hàng tháng với sự gia tăng lạm phát.

Không phải không có nhược điểm:

• Tỷ lệ cao. Người đi vay sẽ trả thừa một số tiền lớn hơn so với khoản thanh toán chênh lệch.
• Các vấn đề phát sinh do mong muốn trả hết nợ trước thời hạn.
• Không tính toán lại cho các khoản thanh toán sớm.

Thanh toán khoản vay là gì?

Thanh toán hàng năm có các thành phần sau:

• Tiền lãi do một người trả khi thanh toán khoản vay.
• Một phần của số tiền gốc.

Kết quả là tổng số tiền lãi gần như luôn vượt quá số tiền người vay phải đóng góp để giảm bớt nợ.

Công thức thanh toán hàng năm cơ bản trong Excel

Như đã đề cập ở trên, trong Microsoft Office Excel, bạn có thể làm việc với nhiều loại thanh toán khác nhau cho các khoản vay và ứng trước. Niên kim cũng không ngoại lệ. Nói chung, công thức mà bạn có thể nhanh chóng tính toán các khoản đóng góp theo niên kim như sau:  

Quan trọng! Không thể mở ngoặc ở mẫu số của biểu thức này để đơn giản hóa nó.

Các giá trị chính của công thức được giải mã như sau:

• AP - thanh toán niên kim (tên viết tắt).
• O - quy mô nợ gốc của người đi vay.
• PS - lãi suất do một ngân hàng cụ thể đưa ra hàng tháng.
• C là số tháng khoản vay kéo dài.

Để đồng hóa thông tin, chỉ cần đưa ra một vài ví dụ về việc sử dụng công thức này là đủ. Chúng sẽ được thảo luận thêm.

Ví dụ về cách sử dụng hàm PMT trong Excel

Chúng tôi đưa ra một điều kiện đơn giản của vấn đề. Cần phải tính toán khoản thanh toán khoản vay hàng tháng nếu ngân hàng đưa ra mức lãi suất là 23%, và tổng số tiền là 25000 rúp. Thời gian cho vay trong 3 năm. Vấn đề được giải quyết theo thuật toán:

1. Tạo một bảng tính chung trong Excel dựa trên dữ liệu nguồn.

2. Kích hoạt hàm PMT và nhập các đối số cho nó vào ô thích hợp.
3. Trong trường "Đặt cược", nhập công thức "B3 / B5". Đây sẽ là lãi suất của khoản vay.
4. Trong dòng “Nper” ghi giá trị ở dạng “B4 * B5”. Đây sẽ là tổng số lần thanh toán cho toàn bộ thời hạn của khoản vay.
5. Điền vào trường "PS". Ở đây bạn cần chỉ ra số tiền ban đầu lấy từ ngân hàng, ghi giá trị “B2”.

6. Đảm bảo rằng sau khi nhấp vào “OK” trong bảng nguồn, giá trị “Thanh toán hàng tháng” đã được tính toán.

Thông tin thêm! Một số âm cho biết rằng người đi vay đang tiêu tiền.

Ví dụ về tính toán số tiền thanh toán quá mức cho một khoản vay trong Excel

Trong bài toán này, bạn cần tính số tiền mà một người đã vay 50000 rúp với lãi suất 27% trong 5 năm sẽ trả quá mức. Tổng cộng, người vay thực hiện 12 lần thanh toán mỗi năm. Dung dịch:

1. Biên dịch bảng dữ liệu gốc.

2. Từ tổng số tiền thanh toán, trừ số tiền ban đầu theo công thức «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». Nó phải được chèn vào thanh công thức ở đầu menu chính của chương trình.
3. Do đó, số tiền trả quá sẽ xuất hiện ở dòng cuối cùng của đĩa đã tạo. Người đi vay sẽ trả quá 41606 rúp.

Công thức tính khoản thanh toán khoản vay hàng tháng tối ưu trong Excel

Một nhiệm vụ với điều kiện sau: khách hàng đã đăng ký tài khoản ngân hàng 200000 rúp với khả năng được bổ sung hàng tháng. Cần phải tính số tiền thanh toán mà một người phải thực hiện hàng tháng, để sau 4 năm anh ta có 2000000 rúp trong tài khoản của mình. Tỷ lệ là 11%. Dung dịch:

1. Tạo bảng tính dựa trên dữ liệu gốc.

2. Nhập công thức vào dòng nhập Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» và nhấn "Enter" từ bàn phím. Các chữ cái sẽ khác nhau tùy thuộc vào các ô mà bảng được đặt.
3. Kiểm tra để đảm bảo rằng số tiền đóng góp được tự động tính ở dòng cuối cùng của bảng.

Chú ý! Như vậy, để khách hàng tích lũy 4 rúp với tỷ lệ 2000000% trong 11 năm, anh ta cần phải gửi 28188 rúp mỗi tháng. Số tiền bị trừ cho thấy rằng khách hàng phải gánh chịu tổn thất khi đưa tiền cho ngân hàng.

Các tính năng của việc sử dụng hàm PMT trong Excel

Nói chung, công thức này được viết như sau: = PMT (tỷ lệ; nper; ps; [bs]; [loại]). Hàm có các tính năng sau:

1. Khi các khoản đóng góp hàng tháng được tính, chỉ tỷ lệ hàng năm được tính đến.
2. Khi xác định lãi suất, điều quan trọng là phải tính toán lại dựa trên số lần trả góp mỗi năm.
3. Thay vì đối số “Nper” trong công thức, một số cụ thể được chỉ ra. Đây là kỳ thanh toán.

Tính toán thanh toán

Nói chung, thanh toán niên kim được tính theo hai giai đoạn. Để hiểu chủ đề, mỗi giai đoạn phải được xem xét riêng biệt. Điều này sẽ được thảo luận thêm.

Giai đoạn 1: tính trả góp hàng tháng

Để tính toán trong Excel số tiền bạn cần trả hàng tháng cho một khoản vay với lãi suất cố định, bạn phải:

1. Biên dịch bảng nguồn và chọn ô mà bạn muốn hiển thị kết quả và nhấp vào nút “Chèn chức năng” ở trên cùng.

2. Trong danh sách các chức năng, chọn “PLT” và nhấp vào “OK”.

3. Trong cửa sổ tiếp theo, đặt các đối số cho hàm, chỉ ra các dòng tương ứng trong bảng đã biên dịch. Ở cuối mỗi dòng, bạn cần nhấp vào biểu tượng, sau đó chọn ô mong muốn trong mảng.

4. Khi tất cả các đối số được điền vào, công thức thích hợp sẽ được viết trong dòng để nhập giá trị và kết quả tính toán có dấu trừ sẽ xuất hiện trong trường của bảng "Thanh toán hàng tháng".

Quan trọng! Sau khi tính toán trả góp, sẽ tính được số tiền mà người vay sẽ trả thừa cho toàn bộ thời gian vay.

Giai đoạn 2: chi tiết thanh toán

Số tiền trả quá có thể được tính toán hàng tháng. Kết quả là, một người sẽ hiểu số tiền anh ta sẽ chi cho một khoản vay hàng tháng. Tính toán chi tiết được thực hiện như sau:

1. Tạo bảng tính trong 24 tháng.

2. Đặt con trỏ vào ô đầu tiên của bảng và chèn hàm “OSPLT”.

3. Điền vào các đối số của hàm theo cách tương tự.

4. Khi điền vào trường "Kỳ", bạn cần tham chiếu đến tháng đầu tiên trong bảng, chỉ ra ô 1.

5. Kiểm tra xem ô đầu tiên trong cột “Thanh toán theo nội dung khoản vay” đã được điền chưa.
6. Để điền vào tất cả các hàng của cột đầu tiên, bạn cần kéo dài ô đến cuối bảng

7. Chọn chức năng “PRPLT” để điền vào cột thứ hai của bảng.
8. Điền vào tất cả các đối số trong cửa sổ đã mở phù hợp với ảnh chụp màn hình bên dưới.

9. Tính tổng số tiền thanh toán hàng tháng bằng cách cộng các giá trị trong hai cột trước đó.

10. Để tính toán “Số dư phải trả”, bạn cần thêm lãi suất vào khoản thanh toán trên phần thân của khoản vay và kéo dài nó đến cuối bảng để lấp đầy tất cả các tháng của khoản vay.

Thông tin thêm! Khi tính phần dư, các ký hiệu đô la phải được treo trên công thức để nó không bị dịch chuyển ra ngoài khi kéo căng.

Tính toán các khoản thanh toán niên kim cho một khoản vay trong Excel

Hàm PMT chịu trách nhiệm tính toán niên kim trong Excel. Nguyên tắc tính toán nói chung là thực hiện các bước sau:

1. Biên dịch bảng dữ liệu gốc.
2. Xây dựng lịch trả nợ cho từng tháng.
3. Chọn ô đầu tiên trong cột “Thanh toán khoản vay” và nhập công thức tính “PLT ($ B3 / 12; $ B $ 4; $ B $ 2)”.
4. Giá trị kết quả được kéo dài cho tất cả các cột của tấm.

Tính toán trong MS Excel hoàn trả số tiền gốc của khoản nợ

Các khoản thanh toán hàng năm phải được thực hiện hàng tháng với số tiền cố định. Và lãi suất không thay đổi.

Tính toán số dư của số tiền gốc (với BS = 0, loại = 0)

Giả sử rằng một khoản vay 100000 rúp được thực hiện trong 10 năm với lãi suất 9%. Cần tính số nợ gốc của tháng thứ 1 của năm thứ 3. Dung dịch:

1. Biên dịch biểu dữ liệu và tính toán khoản thanh toán hàng tháng bằng công thức PV ở trên.
2. Tính phần thanh toán cần thiết để trả một phần nợ bằng công thức «= -PMT- (PS-PS1) * item = -PMT- (PS + PMT + PS * item)».
3. Tính số nợ gốc trong 120 kỳ theo công thức đã biết.
4. Sử dụng toán tử HPMT để tìm số tiền lãi phải trả cho tháng thứ 25.
5. Kiểm tra kết quả.

Tính số tiền gốc phải trả giữa hai kỳ

Tính toán này tốt nhất được thực hiện theo cách đơn giản. Bạn cần sử dụng các công thức sau để tính số tiền trong khoảng thời gian cho hai giai đoạn:

• = «- BS (item; con_period; plt; [ps]; [type]) / (1 + type * item)».
• = “+ BS (rate; start_period-1; plt; [ps]; [type]) / IF (start_period = 1; 1; 1 + type * rate)”.

Chú ý! Các chữ cái trong ngoặc đơn được thay thế bằng các giá trị cụ thể.

Trả nợ trước hạn với thời hạn giảm dần hoặc thanh toán

Nếu bạn cần giảm thời hạn cho vay, bạn sẽ phải thực hiện các phép tính bổ sung bằng cách sử dụng toán tử IF. Vì vậy sẽ có thể kiểm soát được số dư bằng không, không nên đạt trước khi kết thúc kỳ thanh toán.

Để giảm các khoản thanh toán, bạn cần tính toán lại khoản đóng góp cho mỗi tháng trước đó.

Máy tính khoản vay với các khoản thanh toán không thường xuyên

Có một số tùy chọn niên kim mà người vay có thể gửi số tiền thay đổi vào bất kỳ ngày nào trong tháng. Trong tình huống đó, số dư nợ và lãi được tính cho từng ngày. Đồng thời trong Excel, bạn cần:

1. Nhập các ngày trong tháng mà các khoản thanh toán được thực hiện và cho biết số của chúng.
2. Kiểm tra số tiền âm và dương. Những cái tiêu cực được ưu tiên hơn.
3. Đếm số ngày giữa hai ngày mà tiền được gửi vào.

Tính toán khoản thanh toán định kỳ trong MS Excel. Tiền gửi có kỳ hạn

Trong Excel, bạn có thể nhanh chóng tính toán số tiền thanh toán thường xuyên, với điều kiện là một số tiền cố định đã được tích lũy. Hành động này được thực hiện bằng cách sử dụng hàm PMT sau khi bảng ban đầu đã được biên dịch.

Kết luận

Do đó, các khoản thanh toán theo niên kim dễ dàng hơn, nhanh hơn và hiệu quả hơn để tính toán trong Excel. Nhà điều hành PMT chịu trách nhiệm về tính toán của họ. Các ví dụ chi tiết hơn có thể được tìm thấy ở trên.