ตารางหนี้ด้วยสูตร PMT, IPMT & IF - คำแนะนำและตัวอย่าง

เราสามารถใช้สูตร PMT, IPMT และ IF ของ Excel เพื่อสร้างตารางหนี้ ขั้นแรกเราต้องตั้งค่าแบบจำลองโดยการป้อนสมมติฐานหนี้บางส่วน ในตัวอย่างนี้เราถือว่าหนี้เป็น 5,000,000 ดอลลาร์ระยะเวลาการชำระเงินคือ 5 ปีและอัตราดอกเบี้ยอัตราดอกเบี้ยอัตราดอกเบี้ยหมายถึงจำนวนเงินที่ผู้ให้กู้เรียกเก็บกับผู้กู้สำหรับหนี้รูปแบบใด ๆ โดยทั่วไปจะแสดงเป็น เปอร์เซ็นต์ของเงินต้น เป็น 4.5%

1. ยอดดุลเปิดในตารางหนี้ของเราเท่ากับจำนวนเงินกู้ 5 ล้านดอลลาร์ดังนั้นในเซลล์ E29 เราจึงป้อน= B25เพื่อเชื่อมโยงกับข้อมูลสมมติฐาน จากนั้นเราสามารถใช้สูตร PMT ในการคำนวณการชำระเงินรวมสำหรับงวดแรก= PMT ($ B $ 27, $ B $ 26, $ B $ 25) สูตรคำนวณจำนวนเงินที่ชำระโดยใช้จำนวนเงินกู้ระยะเวลาและอัตราดอกเบี้ยที่ระบุไว้ในส่วนสมมติฐาน

ตารางหนี้

2. ในเซลล์ E28 ป้อนช่วงเวลาที่เราอยู่ซึ่งก็คือ 1 ในเซลล์ E29 ให้ป้อน= E28 + 1และกรอกสูตรทางด้านขวา ถัดไปใช้สูตร IPMT ที่จะหาการจ่ายดอกเบี้ยสำหรับงวดแรก= IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25)

3. ชำระเงินต้นคือความแตกต่างระหว่างการชำระเงินทั้งหมดและดอกเบี้ยซึ่งเป็น= E30-E31 ความสมดุลคือความสมดุลปิดเปิดบวกชำระเงินต้นถูกทำซึ่งเป็น= E29 + E32 ความสมดุลเปิดสำหรับระยะเวลาที่ 2 คือความสมดุลปิดเป็นระยะเวลา 1 ซึ่งเป็น= E33

4. คัดลอกสูตรทั้งหมดจากเซลล์ E29 ถึง E33 ไปยังคอลัมน์ถัดไปจากนั้นคัดลอกทุกอย่างไปทางขวา ตรวจสอบว่ายอดปิดงวด 5 = 0 เพื่อให้แน่ใจว่ามีการใช้สูตรและตัวเลขที่ถูกต้อง

5. สังเกตว่ามีข้อความแสดงข้อผิดพลาดเริ่มจากช่วงที่ 6 เนื่องจากยอดคงเหลือเปิดเป็น 0 ที่นี่เราสามารถใช้ฟังก์ชัน IF เพื่อล้างข้อผิดพลาด ในเซลล์ E30 ชนิด= IF (E29> 0 PMT ($ B $ 27, $ B $ 26, $ B $ 25), 0) สูตรระบุว่าหากยอดดุลเปิดน้อยกว่า 0 มูลค่าการชำระเงินทั้งหมดจะแสดงเป็น 0

6. ในเซลล์ 31 ชนิด= IF (E29> 0 IPMT ($ B $ 27, E28, $ B $ 26, $ B $ 25), 0) สูตรนี้คล้ายกับสูตรก่อนหน้านี้ซึ่งระบุว่าหากยอดดุลเปิดน้อยกว่า 0 การจ่ายดอกเบี้ยจะแสดงเป็น 0

7. คัดลอกเซลล์ E30 และ E31 กด SHIFT + ลูกศรขวาจากนั้น CTRL + R เพื่อเติมทางขวา คุณจะเห็นว่าตอนนี้ข้อความแสดงข้อผิดพลาดทั้งหมดแสดงเป็น 0

XNPV เป็น XIRR พร้อมฟังก์ชัน DATE และ IF

เราสามารถคำนวณ NPV และ IRR ตามวันที่ที่ระบุโดยใช้ฟังก์ชัน Excel XNPV และ XIRR ด้วยฟังก์ชัน DATE และ IF

8. ไปที่เซลล์ E6 แล้วป้อน= DATE (E5,12,31)เพื่อแสดงวันที่ คัดลอกไปทางขวา คุณจะเห็น #VALUE! หลังจาก 2021 เราสามารถแก้ไขปัญหานี้โดยใช้ฟังก์ชัน IFERROR = IFERROR (วันที่ (E5,12,31),””)

9. ตอนนี้เราสามารถเริ่มคำนวณ NPV และ IRR ได้แล้ว ขั้นแรกเราต้องป้อนจำนวนกระแสเงินสดอิสระ เราถือว่าจำนวน FCF จากช่วงที่ 1 ถึง 5 คือ -1,000, 500, 600, 700, 900 ในเซลล์ C37 เราจะป้อนอัตราคิดลด 15% ใน B37 เซลล์คำนวณ NPV ใช้สูตร XNPV = XNPV (C37, E35: I35, E6: I6)

10. ใน B38 เซลล์คำนวณ IRR โดยใช้สูตร XIRR = XIRR (E35: I35, E6: I6)

การเพิ่ม OFFSET ให้กับ XNPV และ XIRR

เราสามารถเปลี่ยนเป็นสูตร XNPV และ XIRR เพื่อสร้างสูตรแบบไดนามิกมากขึ้นโดยใช้ฟังก์ชัน OFFSET

11. ใน B42 เซลล์เปลี่ยนสูตรการ= XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: I6) สูตรเป็นแบบไดนามิกมากขึ้นเนื่องจากหากจำนวนงวดเพิ่มขึ้นช่วงเวลาของกระแสเงินสดอิสระก็จะเพิ่มขึ้นด้วย เราไม่จำเป็นต้องเปลี่ยนสูตร NPV หากระยะเวลาคาดการณ์นานขึ้น สำหรับฟังก์ชั่น IRR ที่เปลี่ยนไป= XIRR (E40: OFFSET (E40,0, $ F $ 3-1), E6: I6)

12. หลังจากปรับสูตรสำหรับจำนวนงวดแล้วเราควรหักล้างวันที่ ใน B42 เซลล์เปลี่ยนสูตรการ= XNPV (C42, E40: OFFSET (E40,0, $ F $ 3-1), E6: OFFSET (E6,0, $ F $ 3-1)) สิ่งนี้ช่วยให้สูตร NPV และ IRR สามารถรับจำนวนกระแสเงินสดอิสระที่เหมาะสมพร้อมกับการเปลี่ยนแปลงจำนวนงวด

สรุปสูตรกำหนดการชำระหนี้ที่สำคัญ

  • PMT สูตรคำนวณยอดชำระหนี้= PMT (อัตราดอกเบี้ยจำนวนเงื่อนไขมูลค่าปัจจุบัน)
  • สูตร IPMT สำหรับการคำนวณการจ่ายดอกเบี้ย: = IPMT (อัตราดอกเบี้ยระยะเวลาจำนวนเงื่อนไขมูลค่าปัจจุบัน)
  • สูตร XNPV สำหรับการหามูลค่าปัจจุบันสุทธิ: = XNPV (อัตราคิดลดกระแสเงินสดอิสระวันที่)
  • สูตร XIRR สำหรับการหาอัตราผลตอบแทนภายใน: = XIRR (กระแสเงินสดอิสระวันที่)
  • สูตร OFFSET สำหรับการคำนวณ NPV แบบไดนามิก: = XNPV (อัตราคิดลด, FCF ครั้งที่ 1: OFFSET (FCF ที่ 1, 0, # งวด - 1) วันที่ 1: OFFSET (วันที่ 1, 0, # งวด - 1))
  • สูตร OFFSET สำหรับการคำนวณ IRR แบบไดนามิก: = XIRR (FCF ครั้งที่ 1: OFFSET (FCF ที่ 1, 0, # งวด - 1) วันที่ 1: OFFSET (วันที่ 1, 0, # งวด - 1))

แหล่งข้อมูลอื่น ๆ

ขอขอบคุณที่อ่านคู่มือการเงินเกี่ยวกับตารางหนี้ด้วยสูตร PMT, IPMT และ IF เพื่อให้เรียนรู้และก้าวหน้าในอาชีพของคุณแหล่งข้อมูลด้านการเงินต่อไปนี้จะเป็นประโยชน์:

  • สูตร Excel ขั้นพื้นฐานสูตร Excel ขั้นพื้นฐานการเรียนรู้สูตร Excel ขั้นพื้นฐานเป็นสิ่งสำคัญสำหรับผู้เริ่มต้นเพื่อให้มีความเชี่ยวชาญในการวิเคราะห์ทางการเงิน Microsoft Excel ถือเป็นซอฟต์แวร์มาตรฐานอุตสาหกรรมในการวิเคราะห์ข้อมูล โปรแกรมสเปรดชีตของ Microsoft ยังเป็นหนึ่งในซอฟต์แวร์ที่วาณิชธนกิจต้องการมากที่สุด
  • แนวทางปฏิบัติที่ดีที่สุดของการสร้างแบบจำลองทางการเงินแนวทางปฏิบัติที่ดีที่สุดในการสร้างแบบจำลองทางการเงินบทความนี้มีขึ้นเพื่อให้ข้อมูลแก่ผู้อ่านเกี่ยวกับแนวทางปฏิบัติที่ดีที่สุดในการสร้างแบบจำลองทางการเงินและคำแนะนำทีละขั้นตอนที่ง่ายต่อการปฏิบัติตามในการสร้างแบบจำลองทางการเงิน
  • รายการฟังก์ชันฟังก์ชันของ Excel รายการฟังก์ชัน Excel ที่สำคัญที่สุดสำหรับนักวิเคราะห์ทางการเงิน เอกสารข้อมูลสรุปนี้ครอบคลุมฟังก์ชันกว่า 100 รายการที่สำคัญที่ต้องทราบในฐานะนักวิเคราะห์ Excel
  • ภาพรวมของทางลัดของ Excel ภาพรวมทางลัดของ Excel ทางลัดของ Excel เป็นวิธีการที่มองข้ามไปในการเพิ่มผลผลิตและความเร็วภายใน Excel ทางลัดของ Excel ช่วยให้นักวิเคราะห์การเงินเป็นเครื่องมือที่มีประสิทธิภาพ ทางลัดเหล่านี้สามารถทำหน้าที่ได้หลายอย่าง ง่ายอย่างการนำทางภายในสเปรดชีตไปจนถึงการกรอกสูตรหรือจัดกลุ่มข้อมูล