Chúng ta có thể sử dụng công thức PMT, IPMT và IF của Excel để tạo lịch trình nợ. Đầu tiên, chúng ta cần thiết lập mô hình bằng cách nhập một số giả định về nợ. Trong ví dụ này, chúng tôi giả định khoản nợ là 5.000.000 đô la, thời hạn thanh toán là 5 năm và lãi suất Lãi suất Lãi suất đề cập đến số tiền người cho vay tính cho người đi vay đối với bất kỳ hình thức nợ nào, thường được biểu thị bằng một tỷ lệ phần trăm của tiền gốc. là 4,5%.
1. Số dư đầu kỳ trong lịch trình nợ của chúng tôi bằng số tiền vay là 5 triệu đô la, vì vậy trong ô E29, chúng tôi nhập = B25 để liên kết nó với đầu vào giả định. Sau đó, chúng ta có thể sử dụng công thức PMT để tính tổng số tiền thanh toán cho kỳ đầu tiên = PMT ($ B $ 27, $ B $ 26, $ B $ 25) . Công thức tính số tiền thanh toán bằng cách sử dụng số tiền, thời hạn và lãi suất cho vay được nêu trong phần giả định.
2. Trong ô E28, nhập dấu chấm mà chúng ta đang ở, là 1. Trong ô E29, nhập = E28 + 1 và điền công thức vào bên phải. Tiếp theo, sử dụng công thức IPMT để tìm ra khoản thanh toán lãi suất cho kỳ đầu tiên = IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25) .
3. Khoản thanh toán gốc là phần chênh lệch giữa tổng số tiền thanh toán và tiền trả lãi, là = E30-E31 . Số dư cuối kỳ là số dư đầu kỳ cộng với khoản thanh toán gốc đang được thực hiện, là = E29 + E32 . Số dư đầu kỳ của kỳ 2 là số dư cuối kỳ của kỳ 1, là = E33 .
4. Sao chép tất cả các công thức từ ô E29 đến E33 sang cột tiếp theo, sau đó sao chép mọi thứ sang bên phải. Kiểm tra xem số dư cuối kỳ của kỳ 5 = 0 để đảm bảo đang sử dụng các công thức và số chính xác.
5. Nhận thấy rằng có một số thông báo lỗi bắt đầu từ kỳ 6 vì số dư đầu kỳ là 0. Ở đây chúng ta có thể sử dụng hàm IF để làm sạch lỗi. Trong ô E30, nhập = IF (E29> 0, PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) . Công thức nói rằng nếu số dư đầu kỳ nhỏ hơn 0, thì tổng giá trị thanh toán sẽ được hiển thị là 0.
6. Trong ô 31, nhập = IF (E29> 0, IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) . Công thức này tương tự như công thức trước, trong đó nói rằng nếu số dư đầu kỳ nhỏ hơn 0, thì khoản thanh toán lãi suất sẽ được hiển thị là 0.
7. Sao chép ô E30 và E31, nhấn SHIFT + mũi tên phải rồi nhấn CTRL + R để điền sang phải. Bạn sẽ thấy rằng tất cả các thông báo lỗi hiện được hiển thị dưới dạng 0.
XNPV một XIRR với các hàm DATE và IF
Chúng ta có thể tính NPV và IRR dựa trên các ngày cụ thể bằng cách sử dụng các hàm Excel XNPV và XIRR với các hàm DATE và IF.
8. Chuyển đến ô E6 và nhập = DATE (E5,12,31) để hiển thị ngày. Sao chép sang bên phải. Bạn sẽ thấy #VALUE! thông báo sau năm 2021. Chúng tôi có thể sửa lỗi này bằng cách sử dụng hàm IFERROR = IFERROR (DATE (E5,12,31), ”) .
9. Bây giờ chúng ta có thể bắt đầu tính NPV và IRR. Đầu tiên, chúng ta cần nhập số lượng dòng tiền tự do. Chúng tôi giả định rằng số tiền FCF từ giai đoạn 1 đến 5 là -1,000, 500, 600, 700, 900. Trong ô C37, chúng tôi sẽ nhập tỷ lệ chiết khấu là 15%. Trong ô B37, tính NPV bằng công thức XNPV = XNPV (C37, E35: I35, E6: I6) .
10. Trong ô B38, tính IRR bằng công thức XIRR = XIRR (E35: I35, E6: I6) .
Thêm OFFSET vào XNPV và XIRR
Chúng ta có thể thay đổi các công thức XNPV và XIRR để tạo các công thức động hơn bằng cách sử dụng hàm OFFSET.
11. Trong ô B42, thay đổi công thức thành = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) . Công thức năng động hơn bởi vì nếu số kỳ tăng lên, thì các kỳ của dòng tiền tự do cũng sẽ tăng lên. Chúng ta không cần thay đổi công thức NPV nếu khoảng thời gian dự báo dài hơn. Đối với hàm IRR, hãy thay đổi nó thành = XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) .
12. Sau khi điều chỉnh công thức cho số kỳ, chúng ta nên bù trừ các ngày. Trong ô B42, thay đổi công thức thành = XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) . Điều này cho phép công thức NPV và IRR chọn ra đúng số lượng dòng tiền tự do với sự thay đổi số kỳ.
Tóm tắt các Công thức Lịch trình Nợ Chính
- Công thức PMT để tính số tiền thanh toán nợ: = PMT (lãi suất, số kỳ hạn, giá trị hiện tại)
- Công thức IPMT để tính trả lãi: = IPMT (lãi suất, kỳ hạn, số kỳ hạn, giá trị hiện tại)
- Công thức XNPV để tìm giá trị hiện tại ròng: = XNPV (tỷ lệ chiết khấu, dòng tiền tự do, ngày tháng)
- Công thức XIRR để tìm tỷ suất hoàn vốn nội bộ: = XIRR (dòng tiền tự do, ngày tháng)
- Công thức OFFSET để tính NPV động: = XNPV (tỷ lệ chiết khấu, FCF đầu tiên: OFFSET (FCF đầu tiên, 0, # kỳ - 1), ngày đầu tiên: OFFSET (Ngày đầu tiên, 0, # kỳ - 1))
- Công thức OFFSET để tính IRR động: = XIRR (FCF thứ nhất: OFFSET (FCF thứ nhất, 0, # giai đoạn - 1), ngày đầu tiên: OFFSET (Ngày đầu tiên, 0, # giai đoạn - 1))
Các nguồn lực khác
Cảm ơn bạn đã đọc hướng dẫn của Finance về Lịch trình Nợ với các công thức PMT, IPMT và IF. Để tiếp tục học hỏi và thăng tiến sự nghiệp của bạn, các nguồn Tài chính sau đây sẽ hữu ích:
- Các công thức Excel cơ bản Các công thức Excel cơ bản Nắm vững các công thức Excel cơ bản là rất quan trọng đối với người mới bắt đầu để trở nên thành thạo trong phân tích tài chính. Microsoft Excel được coi là phần mềm tiêu chuẩn của ngành trong phân tích dữ liệu. Chương trình bảng tính của Microsoft cũng trở thành một trong những phần mềm được các chủ ngân hàng đầu tư ưa thích nhất
- Các phương pháp hay nhất về lập mô hình tài chính Các phương pháp hay nhất về lập mô hình tài chính Bài viết này cung cấp cho người đọc thông tin về các phương pháp hay nhất về lập mô hình tài chính và hướng dẫn từng bước, dễ làm để xây dựng một mô hình tài chính.
- Danh sách các hàm Excel Chức năng Danh sách các hàm Excel quan trọng nhất dành cho các nhà phân tích tài chính. Bảng lừa đảo này bao gồm 100 hàm quan trọng cần biết với tư cách là một nhà phân tích Excel
- Tổng quan về phím tắt Excel Phím tắt Excel Tổng quan Phím tắt Excel là một phương pháp bị bỏ qua để tăng năng suất và tốc độ trong Excel. Các phím tắt trong Excel cung cấp cho nhà phân tích tài chính một công cụ mạnh mẽ. Các phím tắt này có thể thực hiện nhiều chức năng. đơn giản như điều hướng trong bảng tính để điền công thức hoặc nhóm dữ liệu.