กำหนดการชำระคืนเงินกู้ด้วยสูตร excel

สารบัญ

• ทำความเข้าใจเกี่ยวกับสินเชื่อที่อยู่อาศัยของคุณ
• คำนวณการจ่ายรายเดือน
• คำนวณอัตราดอกเบี้ยประจำปี
• การกำหนดความยาวของเงินกู้
• การสลายตัวของสินเชื่อ
• การคำนวณสินเชื่อใน Excel
• ค่าตัดจำหน่ายสินเชื่อ
• การสร้างตารางสินเชื่อ

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

บทความนี้เป็นคำแนะนำทีละขั้นตอนในการตั้งค่าการคำนวณสินเชื่อ

ประเด็นที่สำคัญ

• ใช้ Excel เพื่อจัดการกับการจำนองของคุณผ่านการพิจารณาการชำระเงินรายเดือนอัตราดอกเบี้ยและกำหนดการกู้ยืมของคุณคุณสามารถดูรายละเอียดเชิงลึกเกี่ยวกับการแยกย่อยของเงินกู้ด้วย excel และสร้างตารางการชำระคืนที่เหมาะกับคุณ. มีการคำนวณสำหรับแต่ละขั้นตอนที่คุณสามารถปรับแต่งเพื่อตอบสนองความต้องการเฉพาะของคุณการจดบันทึกและตรวจสอบสินเชื่อของคุณอย่างเป็นขั้นเป็นตอนสามารถทำให้กระบวนการชำระคืนรู้สึกยุ่งยากน้อยลงและจัดการได้ง่ายขึ้น

ทำความเข้าใจเกี่ยวกับสินเชื่อที่อยู่อาศัยของคุณ

เมื่อใช้ Excel คุณสามารถทำความเข้าใจการจำนองของคุณได้ดีขึ้นในสามขั้นตอนง่ายๆ ขั้นตอนแรกกำหนดการชำระเงินรายเดือน ขั้นตอนที่สองคำนวณอัตราดอกเบี้ยและขั้นตอนที่สามกำหนดตารางการกู้ยืม

คุณสามารถสร้างตารางใน Excel ที่จะบอกอัตราดอกเบี้ยการคำนวณสินเชื่อสำหรับระยะเวลาของเงินกู้การสลายตัวของเงินกู้การตัดจำหน่ายและการชำระเงินรายเดือน

คำนวณการจ่ายรายเดือน

อันดับแรกนี่คือวิธีคำนวณการชำระเงินรายเดือนสำหรับการจำนอง โดยใช้อัตราดอกเบี้ยรายปีเงินต้นและระยะเวลาเราสามารถกำหนดจำนวนเงินที่จะชำระคืนเป็นรายเดือน

สูตรดังที่แสดงในภาพหน้าจอด้านบนเขียนดังนี้:

= -PMT (อัตราความยาว; มูลค่าปัจจุบัน;;)

การลบเครื่องหมายหน้า PMT เป็นสิ่งจำเป็นเนื่องจากสูตรส่งคืนจำนวนลบ ข้อโต้แย้งสามข้อแรกคืออัตราของเงินกู้ความยาวของเงินกู้ (จำนวนงวด) และเงินต้นที่ยืม อาร์กิวเมนต์สองตัวสุดท้ายเป็นตัวเลือกค่าที่เหลือจะมีค่าเริ่มต้นเป็นศูนย์ ชำระล่วงหน้า (หนึ่ง) หรือท้ายที่สุด (สำหรับศูนย์) ก็เป็นตัวเลือก

สูตร Excel ที่ใช้ในการคำนวณการชำระเงินกู้รายเดือนคือ:

= -PMT ((1 + B2) ^ (1/12) -1; B4 * 12; B3) = PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

คำอธิบาย: สำหรับอัตรานี้เราใช้อัตรารายเดือน (ระยะเวลาอัตรา) จากนั้นเราคำนวณจำนวนงวด (120 เป็นเวลา 10 ปีคูณด้วย 12 เดือน) และสุดท้ายเราระบุเงินต้นที่ยืม การชำระเงินรายเดือนของเราคือ $ 1, 161.88 ในระยะเวลา 10 ปี

คำนวณอัตราดอกเบี้ยประจำปี

เราได้เห็นวิธีการตั้งค่าการคำนวณการชำระเงินรายเดือนสำหรับการจำนอง แต่เราอาจต้องการตั้งค่าการจ่ายรายเดือนสูงสุดที่เราสามารถจ่ายได้ซึ่งจะแสดงจำนวนปีที่เราจะต้องชำระคืนเงินกู้ ด้วยเหตุนี้เราจึงต้องการทราบอัตราดอกเบี้ยรายปีที่สอดคล้องกัน

ดังที่แสดงในภาพหน้าจอด้านบนก่อนอื่นเราจะคำนวณอัตราระยะเวลา (รายเดือนในกรณีของเรา) แล้วตามด้วยอัตรารายปี สูตรที่ใช้จะเป็น RATE ดังแสดงในภาพหน้าจอด้านบน มันเขียนไว้ดังนี้:

= อัตรา (Nper; PMT; มูลค่าปัจจุบัน;;)

อาร์กิวเมนต์สามข้อแรกคือความยาวของเงินกู้ (จำนวนงวด) การชำระรายเดือนเพื่อชำระเงินกู้และเงินต้นที่ยืม อาร์กิวเมนต์สามตัวสุดท้ายเป็นทางเลือกและค่าที่เหลือจะมีค่าเริ่มต้นเป็นศูนย์ อาร์กิวเมนต์คำสำหรับการจัดการการกำหนดล่วงหน้า (สำหรับหนึ่ง) หรือในตอนท้าย (สำหรับศูนย์) ก็เป็นตัวเลือก ในที่สุดอาร์กิวเมนต์การประมาณค่าเป็นทางเลือก แต่สามารถให้การประมาณค่าเริ่มต้นของอัตรา

สูตร Excel ที่ใช้ในการคำนวณอัตราดอกเบี้ยเงินกู้คือ:

= RATE (12 * B4; -B2; B3) = RATE (12 * 13; -960; 120000)

หมายเหตุ: ข้อมูลที่เกี่ยวข้องในการชำระเงินรายเดือนจะต้องได้รับสัญญาณเชิงลบ นี่คือสาเหตุที่มีเครื่องหมายลบอยู่หน้าสูตร ระยะเวลาของอัตราคือ 0.294%

เราใช้สูตร = (1 + B5) คือ 12-1 ^ = (1 + 0.294%) ^ 12-1 เพื่อรับอัตราประจำปีของสินเชื่อของเราซึ่งคือ 3.58% กล่าวอีกนัยหนึ่งคือการยืมเงิน 120, 000 ดอลลาร์ในระยะเวลา 13 ปีเพื่อจ่าย $ 960 ต่อเดือนเราควรเจรจาเงินกู้ในอัตราสูงสุดปีละ 3.58%

การใช้ Excel เป็นวิธีที่ยอดเยี่ยมในการติดตามสิ่งที่คุณเป็นหนี้และกำหนดตารางเวลาสำหรับการชำระคืนซึ่งจะลดค่าธรรมเนียมใด ๆ ที่คุณอาจมีในที่สุด

การกำหนดความยาวของเงินกู้

ตอนนี้เราจะดูวิธีการกำหนดระยะเวลาของเงินกู้เมื่อคุณทราบอัตราประจำปีการยืมเงินต้นและการชำระเงินรายเดือนที่จะต้องชำระคืน กล่าวอีกนัยหนึ่งเราต้องใช้เวลานานเท่าใดในการชำระค่าจำนอง 120, 000 ดอลลาร์ด้วยอัตรา 3.10% และการชำระรายเดือน 1, 100 ดอลลาร์

สูตรที่เราจะใช้คือ NPER ดังที่แสดงในภาพหน้าจอด้านบนและเขียนเป็นดังนี้:

= NPER (อัตรา; PMT; มูลค่าปัจจุบัน;;)

ข้อโต้แย้งสามข้อแรกคืออัตราประจำปีของเงินกู้การชำระรายเดือนที่จำเป็นในการชำระคืนเงินกู้และเงินต้นที่ยืม อาร์กิวเมนต์สองตัวสุดท้ายเป็นตัวเลือกค่าที่เหลือจะมีค่าเริ่มต้นเป็นศูนย์ อาร์กิวเมนต์คำสั่งจ่ายล่วงหน้า (หนึ่งรายการ) หรือท้ายที่สุด (สำหรับศูนย์) ก็เป็นทางเลือกเช่นกัน

= NPER ((1 + B2) ^ (1/12) -1; -B4; B3) = NPER ((1 + 3, 10%) ^ (1/12) -1; -1100; 120000)

หมายเหตุ: ข้อมูลที่เกี่ยวข้องในการชำระเงินรายเดือนจะต้องได้รับสัญญาณเชิงลบ นี่คือเหตุผลที่เรามีเครื่องหมายลบหน้าสูตร ระยะเวลาการชำระเงินคืนเป็นระยะเวลา 127.97 (เดือนในกรณีของเรา)

เราจะใช้สูตร = B5 / 12 = 127.97 / 12 สำหรับจำนวนปีเพื่อชำระคืนเงินกู้ให้เสร็จสมบูรณ์ กล่าวอีกนัยหนึ่งคือการยืม $ 120, 000 ด้วยอัตรารายปี 3.10% และจ่าย $ 1, 100 ต่อเดือนเราควรชำระคืนครบกำหนดเวลา 128 เดือนหรือ 10 ปี 8 เดือน

การสลายตัวของสินเชื่อ

การชำระเงินกู้ประกอบด้วยเงินต้นและดอกเบี้ย ดอกเบี้ยถูกคำนวณสำหรับแต่ละงวดตัวอย่างเช่นการชำระคืนรายเดือนมากกว่า 10 ปีจะทำให้เรามี 120 งวด

ตารางด้านบนแสดงรายละเอียดของเงินกู้ (ระยะเวลารวมเท่ากับ 120) โดยใช้สูตร PPMT และ IPMT อาร์กิวเมนต์ของสูตรทั้งสองนั้นเหมือนกันและแยกย่อยดังนี้:

= -PPMT (อัตรา; num_period ความยาว; เงินต้น;;)

อาร์กิวเมนต์เหมือนกับสูตร PMT ที่เห็นอยู่แล้วยกเว้น "num_period" ซึ่งถูกเพิ่มเข้ามาเพื่อแสดงช่วงเวลาที่จะพังเงินกู้ยืมที่ได้รับจากเงินต้นและดอกเบี้ย นี่คือตัวอย่าง:

= -PPMT ((1 + B2) ^ (1/12) -1; 1; B4 * 12; B3) = PPMT ((1 + 3, 10%) ^ (1/12) -1; 1; 10 * 12 120000)

ผลลัพธ์จะแสดงในภาพหน้าจอด้านบน "การสลายตัวสินเชื่อ" ตลอดระยะเวลาการวิเคราะห์ซึ่งก็คือ "หนึ่ง;" นั่นคือช่วงเวลาแรกหรือเดือนแรก เราจ่าย $ 1, 161.88 แยกเป็นเงิน $ 856.20 และดอกเบี้ย $ 305.68

การคำนวณสินเชื่อใน Excel

นอกจากนี้ยังเป็นไปได้ที่จะคำนวณการชำระคืนเงินต้นและดอกเบี้ยเป็นระยะเวลาหลายช่วงเช่น 12 เดือนแรกหรือ 15 เดือนแรก

= -CUMPRINC (อัตราความยาว; เงินต้น; start_date; end_date; พิมพ์)

เราพบข้อโต้แย้งอัตราความยาวเงินต้นและคำ (ซึ่งจำเป็น) ที่เราได้เห็นแล้วในส่วนแรกด้วยสูตร PMT แต่ที่นี่เราต้องการอาร์กิวเมนต์ "start_date" และ "end_date" ด้วย "start_date" หมายถึงจุดเริ่มต้นของช่วงเวลาที่จะวิเคราะห์และ "end_date" หมายถึงจุดสิ้นสุดของช่วงเวลาที่จะวิเคราะห์

นี่คือตัวอย่าง:

= -CUMPRINC ((1 + B2) ^ (1/12) -1; B4 * 12; B3; 1; 12; 0)

ผลลัพธ์จะแสดงในสกรีนช็อต "Cumul 1st year" ดังนั้นช่วงเวลาที่วิเคราะห์จะอยู่ในช่วงตั้งแต่หนึ่งถึง 12 ของช่วงแรก (เดือนแรก) ถึงวันที่สิบสอง (เดือนที่ 12) กว่าหนึ่งปีเราจะจ่ายเงินต้น $ 10, 419.55 และดอกเบี้ย $ 3, 522.99

ค่าตัดจำหน่ายสินเชื่อ

สูตรก่อนหน้านี้ช่วยให้เราสามารถสร้างช่วงเวลาที่กำหนดตามระยะเวลาเพื่อทราบว่าเราจะจ่ายเงินต้นและดอกเบี้ยเป็นรายเดือนและรู้จำนวนที่เหลือที่จะจ่าย

การสร้างตารางสินเชื่อ

ในการสร้างตารางการกู้ยืมเราจะใช้สูตรต่าง ๆ ที่กล่าวถึงข้างต้นและขยายออกไปตามระยะเวลา

ในคอลัมน์ช่วงแรกให้ป้อน "1" เป็นช่วงเวลาแรกแล้วลากเซลล์ลง ในกรณีของเราเราต้องใช้ 120 งวดเนื่องจากการชำระคืนเงินกู้ 10 ปีคูณด้วย 12 เดือนเท่ากับ 120

คอลัมน์ที่สองคือจำนวนเงินรายเดือนที่เราต้องจ่ายในแต่ละเดือนซึ่งเป็นค่าคงที่ตลอดระยะเวลาเงินกู้ ในการคำนวณจำนวนเงินให้แทรกสูตรต่อไปนี้ในเซลล์ของช่วงเวลาแรกของเรา:

= -PMT (TP-1; B4 * 12; B3) = -PMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000)

คอลัมน์ที่สามคือเงินต้นที่จะชำระคืนเป็นรายเดือน ตัวอย่างเช่นสำหรับช่วงเวลาที่ 40 เราจะชำระคืน $ 945.51 เป็นเงินต้นด้วยยอดรวมรายเดือนของเรา $ 1, 161.88

ในการคำนวณจำนวนเงินต้นที่ไถ่ถอนเราใช้สูตรต่อไปนี้:

= -PPMT (TP; A18; $ B $ 4 * 12; $ B $ 3) = -PPMT ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

คอลัมน์ที่สี่คือดอกเบี้ยซึ่งเราใช้สูตรในการคำนวณเงินต้นคืนในจำนวนเงินรายเดือนของเราเพื่อค้นหาว่าต้องจ่ายดอกเบี้ยเท่าไร:

= -INTPER (TP; A18; $ B $ 4 * 12; $ B $ 3) = -INTPER ((1 + 3, 10%) ^ (1/12); 1; 10 * 12; 120000)

คอลัมน์ที่ห้ามีจำนวนเงินที่เหลือจ่าย ตัวอย่างเช่นหลังจากการชำระเงินครั้งที่ 40 เราจะต้องจ่าย $ 83, 994.69 ใน $ 120, 000

สูตรมีดังนี้:

= $ B $ 3 + CUMPRINC (TP; $ B $ 4 * 12; $ B $ 3; 1; A18; 0)

สูตรใช้การรวมกันของเงินต้นภายใต้ช่วงเวลาก่อนหน้าของเซลล์ที่มีเงินต้นยืม ช่วงเวลานี้เริ่มเปลี่ยนแปลงเมื่อเราคัดลอกและลากเซลล์ลง ตารางด้านล่างแสดงให้เห็นว่าเมื่อสิ้นสุดระยะเวลา 120 งวดเราจะชำระคืนเงินกู้