Hướng dẫn cách phân tích trả nợ gốc và lãi cùng chuỗi hàm PMT trong Excel

Chia sẻ bài viết này:
  • 1
  •  
  •  
  •  
  •  
  •  
  •   
  •   

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:

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ợ.

Bằng việc thấu hiểu rằng hầu hết nhân viên văn phòng thường mất nhiều thời gian tra cứu cách sử dụng các hàm, các tính năng trong Excel, gặp khó khăn trong lồng ghép hàm, xử lý dữ liệu, công việc bận rộn không có thời gian học. Nên Học Excel Online đã thiết kế ra chương trình Excel từ cơ bản tới nâng cao dành cho người đi làm. Đây là chương trình học online, học viên có thể chủ động học mọi lúc mọi nơi, trao đổi trực tiếp với giảng viên tại bài giảng trong suốt quá trình học.

Với khoá học này học viên sẽ nắm được: :

  • Trọn bộ công cụ định dạng báo cáo chuyên nghiệp: Format, Cell, Number, Style,...không mất thời gian cả ngày ngồi sửa báo cáo
  • Toàn bộ kỹ năng lọc dữ liệu, lọc dữ liệu nâng cao trong Excel phục vụ cho mục đích trích xuất dữ liệu
  • Data Validation kiểm soát dữ liệu khi nhập vào một vùng trong bảng tính
  • TẤT TẦN TẬT hơn 100 hàm thông dụng, công thức mảng trong Excel, học tới đâu nhớ tới đó
  • Bộ 36 PHÍM TẮT giúp thao tác nhanh gấp đôi
  • BÁO CÁO SIÊU TỐC trong 1 phút với Pivot Table
  • Hỏi đáp trực tiếp tại bài giảng với giảng viên trong suốt quá trình học
  • CHUYÊN NGHIỆP trong báo cáo bằng Biểu đồ, đồ thị,....
  • 142+ bài giảng, 13+ giờ học, 200+ ví dụ thực tế cùng chuyên gia
  • 100+ Kỹ năng, thủ thuật Excel thông dụng với các hàm: SUMIF, HLOOKUP, VLOOKUP, DATEDIF…

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


Chia sẻ bài viết này:
  • 1
  •  
  •  
  •  
  •  
  •  
  •   
  •