วิธีกำหนดเวลาการชำระคืนเงินกู้ของคุณด้วยสูตร Excel

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

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

ทำความเข้าใจการจำนองของคุณ

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

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

ขั้นตอนที่ 1: คำนวณการชำระเงินรายเดือน

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

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

= -pmt (อัตรา; ความยาว; present_value; [future_value]; [type])

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

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

= pmt ((1+b2)^(1/12) -1; b4*12; b3) = pmt ((1+3,10%)^(1/12) -1; 10*12; 120000)

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

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

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

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

= อัตรา (nper; pmt; present_value; [future_value]; [type])

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

สูตร Excel ที่ใช้ในการคำนวณอัตราการให้กู้ยืมคือ:

= อัตรา (12*b4; -b2; b3) = อัตรา (12*13; -960; 120000)

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

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

ขั้นตอนที่ 3: กำหนดความยาวของเงินกู้

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

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

= nper (อัตรา; pmt; present_value; [future_value]; [type])

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

= 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 ปีและแปดเดือน

ขั้นตอนที่ 4: การย่อยสลายเงินกู้

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

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

= -ppmt (อัตรา; num_period; ความยาว; principal; [ตกค้าง]; [term])

อาร์กิวเมนต์นั้นเหมือนกับสูตร 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

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

= -cumprinc (อัตรา; ความยาว; principal; start_date; end_date; ประเภท)

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

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

= -cumbrinc ((1+b2)^(1/12) -1; b4*12; b3; 1; 0)

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

ขั้นตอนที่ 6: การตัดจำหน่ายเงินกู้

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

ขั้นตอนที่ 7: การสร้างตารางเงินกู้

ในการสร้างตารางเงินกู้เราจะใช้สูตรที่แตกต่างกันที่กล่าวถึงข้างต้นและขยายพวกเขาผ่านจำนวนรอบระยะเวลา

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

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

= -pmt (tp; 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

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

18; a18; 0)

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

บรรทัดล่าง

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