Trong bài học về hàm PMT tính số tiền phải trả hàng kỳ cho khoản vay có lãi suất xác định, chúng ta đã xác định được cách sử dụng hàm PMT. Nhưng nếu muốn phân biệt rõ số trả gốc và trả lãi hàng kỳ thì làm thế nào? Trong bài này chúng ta cùng tìm hiểu cách phân tích trả nợ gốc và lãi cùng chuỗi hàm PMT trong excel nhé.

Vẫn sử dụng ví dụ của bài học trước để áp dụng vào bài học này:

Bạn cần vay một khoản tiền là 100 triệu tại thời điểm hiện tại, trả đều đặn hàng tháng trong vòng 3 năm với lãi suất 6% mỗi tháng. Vậy mỗi tháng phải trả bao nhiêu tiền? Tổng cộng sau 3 năm phải trả cả gốc và lãi là bao nhiêu tiền? Phân tích số tiền gốc, tiền lãi, còn nợ của từng kỳ?

Cách làm:

Để phân tích trả nợ, chúng ta lập 1 bảng xác định rõ từng kỳ thanh toán như sau:

• Xác định rõ từng kỳ thanh toán
• Tại mỗi kỳ sẽ xác định các chỉ tiêu: Số phải trả, Số trả gốc, Số trả lãi, số còn nợ

Mỗi chỉ tiêu sẽ được tính cụ thể như sau:

Xem nhanh

Toggle

1. Số phải trả = hàm PMT

F2 =PMT($C$4,$C$3,$C$2)

Các vị trí của tham chiếu trong hàm PMT sẽ cần được cố định để chúng ta có thể tính tiếp cho các kỳ sau.

Xem thêm: Tài liệu Excel cơ bản hay nhất

2. Trả gốc = hàm PPMT (Principal Part of the Payment)

Cấu trúc hàm PPMT(Rate, Per, Nper, PV, [FV], [Type])

Ở đây xuất hiện thêm 1 tham chiếu là Per = Tại kỳ nào => Xác định cụ thể trả gốc vào kỳ nào. Khoản tiền này sẽ được quy đổi về giá trị tương lai của khoản tiền tại thời điểm kết thúc kỳ hạn trả nợ.

G2 =PPMT($C$4,$E2,$C$3,$C$2)

3. Trả lãi = hàm IPMT (Interest Part of the Payment)

Cấu trúc hàm IPMT(Rate, Per, Nper, PV, [FV], [Type])

Cấu trúc này tương tự với cấu trúc hàm PPMT, chỉ khác là kết quả của hàm cho chúng ta số tiền là số lãi phải trả, quy đổi về giá trị tương lai của khoản tiền tại thời điểm kết thúc kỳ hạn trả nợ.

H2 =IPMT($C$4,E2,$C$3,$C$2)

4. Số còn nợ = Khoản vay + Trả gốc (vì trả gốc là số âm)

Số còn nợ = Khoản vay còn lại sau mỗi kỳ – Trả gốc mỗi kỳ

I2 = C2+G2

I3 = I2+G3

I4=I3+G4

…

=> Sau đó Filldown toàn bộ công thức cho các kỳ còn lại để xem kết quả:

Như vậy chúng ta có thể phân biệt được rõ số gốc và số lãi trong mỗi kỳ thanh toán.

Càng trả nhiều kỳ thì số lãi phải trả càng tăng

Chúng ta thử xét ví dụ trên với số kỳ trả nợ là 12 tháng xem sao:

Như vậy chỉ cần giảm số kỳ trả lãi từ 36 xuống 12 thì đã giảm được hơn 100 triệu tiền lãi rồi.

Qua bài học này, chúng ta học thêm được kỹ năng phân tích khoản vay và việc thanh toán khoản vay nhờ vào các hàm PMT trong Excel. Đây là kỹ năng cơ bản nhưng rất quan trọng mà khi làm việc ở mảng tài chính chúng ta đều cần phải nắm được.

Chúc các bạn học tốt cùng Học Excel Online!

Tải về: File kèm theo bài học

Áp dụng Excel vào tài chính và kế toán giúp bạn giải quyết được rất nhiều công việc trong thời gian ngắn và với số liệu chính xác hơn. Để áp dụng thành thạo Excel vào lĩnh vực tài chính, kế toán, các bạn cần hiểu và nắm rất rõ tính năng của các hàm, công cụ trong Excel.

Bạn tham khảo khóa học EX101 – Excel từ cơ bản tới chuyên gia của Học Excel Online. Đây là khóa học giúp bạn hệ thống kiến thức một cách đầy đủ, chi tiết. Hơn nữa không hề có giới hạn về thời gian học tập nên bạn có thể thoải mái học bất cứ lúc nào, dễ dàng tra cứu lại kiến thức khi cần. Hiện nay hệ thống đang có ưu đãi rất lớn cho bạn khi đăng ký tham gia khóa học. Chi tiết xem tại: HocExcel.Online