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

การคำนวณสินเชื่อสำหรับค่าเช่ารายเดือน

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

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

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

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

= - 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 ดังที่แสดงในภาพหน้าจอด้านบนจะเขียนดังนี้:

= RATE (Nper; pmt; present_value; [future_value]; [type])

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

สูตร 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 เหรียญสหรัฐฯต่อปีเพื่อจ่ายรายเดือน 960 เหรียญต่อเดือนเราควรเจรจาเงินกู้เป็นรายปี 3. อัตราสูงสุด 58%

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

ตอนนี้เราจะดูวิธีการได้รับเงินกู้ระยะยาวเมื่อคุณทราบอัตรารายปีเงินต้นที่ยืมและการชำระเงินรายเดือนที่จะต้องชำระคืน กล่าวอีกนานเท่าใดเราจะต้องชำระคืนเงินกู้ 120,000 เหรียญที่มีอัตรา 3. 10% และการชำระเงินรายเดือนของ $ 1, 100 หรือไม่? จำนวนการชำระคืนเงินกู้ สูตรที่เราจะใช้คือ NPER ดังที่แสดงไว้ในภาพหน้าจอด้านบนและจะเขียนดังนี้:

= NPER (อัตรา; pmt; present_value; [future_value];

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

= 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 (rate; num_period; length; principal; [เหลือ]; [terme])

= - INTPER (rate; 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)

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

ผลลัพธ์ที่ได้คือภาพรวมของ "การสลายตัวของเงินกู้" ในช่วงที่ทำการวิเคราะห์คือ "1" ดังนั้นช่วงแรก หรือเดือนแรกสำหรับเรื่องนี้เราจ่ายเงิน 1161 เหรียญ 88 ซึ่งแบ่งออกเป็น 856 ดอลลาร์, 20 หลักและ 305 เหรียญสหรัฐฯ 68 ดอกเบี้ย

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

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

= - CUMPRINC (อัตรา, ความยาว, หลัก, วันที่เริ่มต้น, วันที่สิ้นสุด, ประเภท)

= - CUMIPMT (อัตรา, ความยาว, หลัก, วันที่เริ่มต้น, วันที่สิ้นสุด, ประเภท)

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

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

= - CUMPRINC ((1 + 3, 10% 12; 120000; 1; 12; 0)

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

= - CUMIPMT ((1 + 3, 10%) ^ (1/12) -1; 10 * 12; 120000; 1; 12; 0)

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

การตัดจำหน่ายเงินกู้

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

สร้างตารางเงินกู้ใน Excel

ในการสร้างกำหนดเวลาเงินกู้เราจะใช้สูตรต่างๆที่กล่าวถึงข้างต้นและขยายช่วงเวลาต่างๆ

ในคอลัมน์ช่วงแรกเพียงป้อน "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)

= 120000 + CUMPRINC ((1 + 3, 10%) ^ (1/12); 10 * 12; 120000; 1; 1; 0)

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