สูตรคำนวณเงินงวดใน Excel

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

การจ่ายเงินงวดคืออะไร

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

นอกจากนี้ ดอกเบี้ยเงินกู้รวมอยู่ในจำนวนเงินที่จ่ายให้กับธนาคารแล้ว

เงินงวดการจำแนก

การชำระเงินงวดสามารถแบ่งออกเป็นประเภทต่อไปนี้:

1. แก้ไขแล้ว. การชำระเงินที่ไม่เปลี่ยนแปลงมีอัตราคงที่โดยไม่คำนึงถึงเงื่อนไขภายนอก
2. สกุลเงิน. ความสามารถในการเปลี่ยนจำนวนเงินที่ชำระในกรณีที่อัตราแลกเปลี่ยนลดลงหรือเพิ่มขึ้น
3. จัดทำดัชนี การชำระเงินขึ้นอยู่กับระดับ ตัวบ่งชี้อัตราเงินเฟ้อ ในช่วงระยะเวลาเงินกู้ ขนาดของพวกเขามักจะเปลี่ยนแปลง
4. ตัวแปร เงินงวดซึ่งอาจเปลี่ยนแปลงได้ขึ้นอยู่กับสถานะของระบบการเงิน ตราสาร

เอาใจใส่! การชำระเงินคงที่เป็นสิ่งที่ดีกว่าสำหรับผู้กู้ทุกคน เนื่องจากมีความเสี่ยงน้อย

ข้อดีและข้อเสียของการจ่ายเงินงวด

เพื่อให้เข้าใจหัวข้อนี้มากขึ้น จำเป็นต้องศึกษาลักษณะสำคัญของการชำระคืนเงินกู้ประเภทนี้ มันมีข้อดีดังต่อไปนี้:

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

ไม่ได้โดยไม่มีข้อเสีย:

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

การชำระเงินกู้คืออะไร?

การจ่ายเงินงวดมีองค์ประกอบดังต่อไปนี้:

• ดอกเบี้ยจ่ายโดยบุคคลเมื่อชำระเงินกู้
• ส่วนหนึ่งของเงินต้น

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

สูตรการชำระเงินงวดพื้นฐานใน Excel

ดังที่กล่าวไว้ข้างต้น ใน Microsoft Office Excel คุณสามารถทำงานกับการชำระเงินกู้และเงินทดรองประเภทต่างๆ ได้ เงินรายปีก็ไม่มีข้อยกเว้น โดยทั่วไป สูตรที่คุณสามารถคำนวณเงินสมทบได้อย่างรวดเร็วมีดังนี้:  

สำคัญ! เป็นไปไม่ได้ที่จะเปิดวงเล็บในตัวส่วนของนิพจน์นี้เพื่อทำให้ง่ายขึ้น

ค่าหลักของสูตรจะถูกถอดรหัสดังนี้:

• AP – เงินงวด (ชื่อย่อ)
• O – ขนาดของหนี้ต้นของผู้กู้
• PS – อัตราดอกเบี้ยที่เสนอเป็นรายเดือนโดยธนาคารแห่งหนึ่ง
• C คือจำนวนเดือนที่เงินกู้คงอยู่

ในการซึมซับข้อมูลก็เพียงพอที่จะยกตัวอย่างการใช้สูตรนี้ พวกเขาจะหารือเพิ่มเติม

ตัวอย่างการใช้ฟังก์ชัน PMT ใน Excel

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

1. สร้างสเปรดชีตทั่วไปใน Excel ตามข้อมูลต้นฉบับ

2. เปิดใช้งานฟังก์ชัน PMT และป้อนอาร์กิวเมนต์ในกล่องที่เหมาะสม
3. ในช่อง "เดิมพัน" ให้ป้อนสูตร "B3/B5" นี่จะเป็นอัตราดอกเบี้ยเงินกู้
4. ในบรรทัด "Nper" ให้เขียนค่าในรูปแบบ "B4*B5" นี่จะเป็นจำนวนการชำระเงินทั้งหมดสำหรับเงินกู้ทั้งหมด
5. กรอกข้อมูลในช่อง “PS” ที่นี่คุณต้องระบุจำนวนเงินเริ่มต้นที่นำมาจากธนาคารโดยเขียนมูลค่า "B2"

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

ข้อมูลเพิ่มเติม! ตัวเลขติดลบแสดงว่าผู้กู้กำลังใช้จ่ายเงิน

ตัวอย่างการคำนวณจำนวนเงินที่ชำระมากเกินไปสำหรับเงินกู้ใน Excel

ในปัญหานี้คุณต้องคำนวณจำนวนเงินที่ผู้กู้เงิน 50000 รูเบิลในอัตราดอกเบี้ย 27% เป็นเวลา 5 ปีจะจ่ายเงินมากเกินไป โดยรวมแล้วผู้กู้ชำระเงิน 12 งวดต่อปี วิธีการแก้:

1. รวบรวมตารางข้อมูลเดิม

2. จากยอดชำระทั้งหมด ให้ลบจำนวนเงินเริ่มต้นตามสูตร «=ABS(ПЛТ(B3/B5;B4*B5;B2)*B4*B5)-B2». ต้องแทรกลงในแถบสูตรที่ด้านบนของเมนูหลักของโปรแกรม
3. เป็นผลให้จำนวนเงินที่จ่ายเกินจะปรากฏในบรรทัดสุดท้ายของจานที่สร้างขึ้น ผู้ยืมจะจ่ายเงินมากเกินไป 41606 รูเบิลด้านบน

สูตรคำนวณการผ่อนชำระรายเดือนที่เหมาะสมใน Excel

งานที่มีเงื่อนไขดังต่อไปนี้: ลูกค้าได้ลงทะเบียนบัญชีธนาคารสำหรับ 200000 rubles โดยสามารถเติมเงินได้รายเดือน มีความจำเป็นต้องคำนวณจำนวนเงินที่บุคคลต้องจ่ายทุกเดือนเพื่อที่หลังจาก 4 ปีเขามี 2000000 รูเบิลในบัญชีของเขา อัตราคือ 11% วิธีการแก้:

1. สร้างสเปรดชีตตามข้อมูลต้นฉบับ

2. ป้อนสูตรในบรรทัดอินพุตของ Excel «=ПЛТ(B3/B5;B6*B5;-B2;B4)» และกด "Enter" จากแป้นพิมพ์ ตัวอักษรจะแตกต่างกันไปขึ้นอยู่กับเซลล์ที่วางตาราง
3. ตรวจสอบว่าจำนวนเงินสมทบคำนวณโดยอัตโนมัติในบรรทัดสุดท้ายของตาราง

เอาใจใส่! ดังนั้นเพื่อให้ลูกค้าสะสม 4 รูเบิลในอัตรา 2000000% ใน 11 ปี เขาต้องฝาก 28188 รูเบิลทุกเดือน ค่าลบในจำนวนเงินแสดงว่าลูกค้าขาดทุนจากการให้เงินกับธนาคาร

คุณสมบัติของการใช้ฟังก์ชัน PMT ใน Excel

โดยทั่วไปสูตรนี้เขียนดังนี้: =PMT(อัตรา; nper; ps; [bs]; [ประเภท]) ฟังก์ชั่นมีคุณสมบัติดังต่อไปนี้:

1. เมื่อคำนวณเงินสมทบรายเดือน จะพิจารณาเฉพาะอัตรารายปีเท่านั้น
2. เมื่อระบุอัตราดอกเบี้ย การคำนวณใหม่ตามจำนวนงวดต่อปีเป็นสิ่งสำคัญ
3. แทนที่จะเป็นอาร์กิวเมนต์ "Nper" ในสูตร จะมีการระบุตัวเลขเฉพาะ นี่คือระยะเวลาการชำระเงิน

การคำนวณการชำระเงิน

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

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

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

1. รวบรวมตารางต้นฉบับและเลือกเซลล์ที่คุณต้องการแสดงผลและคลิกที่ปุ่ม "แทรกฟังก์ชัน" ที่ด้านบน

2. ในรายการฟังก์ชัน เลือก "PLT" แล้วคลิก "ตกลง"

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

4. เมื่อเติมอาร์กิวเมนต์ทั้งหมดแล้ว สูตรที่เหมาะสมจะถูกเขียนในบรรทัดสำหรับการป้อนค่า และผลการคำนวณที่มีเครื่องหมายลบจะปรากฏในช่องของตาราง "การชำระเงินรายเดือน"

สำคัญ! หลังจากคำนวณค่างวดแล้ว จะสามารถคำนวณจำนวนเงินที่ผู้กู้จะชำระเกินตลอดระยะเวลาเงินกู้ได้

ขั้นตอนที่ 2: รายละเอียดการชำระเงิน

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

1. สร้างสเปรดชีตเป็นเวลา 24 เดือน

2. วางเคอร์เซอร์ในเซลล์แรกของตารางและแทรกฟังก์ชัน "OSPLT"

3. กรอกอาร์กิวเมนต์ของฟังก์ชันในลักษณะเดียวกัน

4. เมื่อกรอกข้อมูลในฟิลด์ "ระยะเวลา" คุณต้องอ้างอิงเดือนแรกในตารางซึ่งระบุเซลล์ 1

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

7. เลือกฟังก์ชัน "PRPLT" เพื่อเติมลงในคอลัมน์ที่สองของตาราง
8. กรอกอาร์กิวเมนต์ทั้งหมดในหน้าต่างที่เปิดขึ้นตามภาพหน้าจอด้านล่าง

9. คำนวณการชำระเงินรายเดือนทั้งหมดโดยเพิ่มค่าในสองคอลัมน์ก่อนหน้า

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

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

การคำนวณการชำระเงินงวดสำหรับเงินกู้ใน Excel

ฟังก์ชัน PMT มีหน้าที่ในการคำนวณเงินงวดใน Excel หลักการคำนวณโดยทั่วไปคือทำตามขั้นตอนต่อไปนี้:

1. รวบรวมตารางข้อมูลเดิม
2. สร้างตารางการชำระหนี้ในแต่ละเดือน
3. เลือกเซลล์แรกในคอลัมน์ "การชำระเงินกู้" และป้อนสูตรการคำนวณ “PLT ($B3/12;$B$4;$B$2)”
4. ค่าผลลัพธ์ถูกยืดออกสำหรับคอลัมน์ทั้งหมดของเพลต

การคำนวณใน MS Excel ชำระคืนเงินต้นของหนี้

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

การคำนวณยอดเงินต้น (ด้วย BS=0, type=0)

สมมติว่าเงินกู้ 100000 รูเบิลใช้เวลา 10 ปีที่ 9% มีความจำเป็นต้องคำนวณจำนวนเงินต้นของหนี้ในเดือนที่ 1 ของปีที่ 3 วิธีการแก้:

1. รวบรวมแผ่นข้อมูลและคำนวณการชำระเงินรายเดือนโดยใช้สูตร PV ด้านบน
2. คำนวณส่วนแบ่งการชำระเงินที่ต้องชำระหนี้บางส่วนโดยใช้สูตร «=-PMT-(PS-PS1)*รายการ=-PMT-(รายการ PS +PMT+PS*)».
3. คำนวณจำนวนเงินต้นของหนี้ 120 งวดโดยใช้สูตรที่รู้จักกันดี
4. การใช้ตัวดำเนินการ HPMT ค้นหาจำนวนดอกเบี้ยที่จ่ายสำหรับเดือนที่ 25
5. ตรวจสอบผล

การคำนวณจำนวนเงินต้นที่จ่ายระหว่างสองงวด

การคำนวณนี้ทำได้ดีที่สุดด้วยวิธีง่ายๆ คุณต้องใช้สูตรต่อไปนี้ในการคำนวณจำนวนเงินในช่วงเวลาสองช่วงเวลา:

• =«-BS(รายการ; con_period; plt; [ps]; [ประเภท]) /(1+ประเภท *รายการ)».
• = “+ BS(rate; start_period-1; plt; [ps]; [type]) /IF(start_period =1; 1; 1+type *rate)”

เอาใจใส่! ตัวอักษรในวงเล็บจะถูกแทนที่ด้วยค่าเฉพาะ

ชำระคืนก่อนกำหนดด้วยเงื่อนไขที่ลดลงหรือการชำระเงิน

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

เพื่อลดการชำระเงิน คุณต้องคำนวณเงินสมทบใหม่ในแต่ละเดือนก่อน

เครื่องคำนวณสินเชื่อที่มีการชำระเงินผิดปกติ

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

1. ป้อนวันของเดือนที่ชำระเงิน และระบุหมายเลข
2. ตรวจสอบจำนวนเงินที่เป็นลบและบวก สิ่งที่เป็นลบเป็นที่ต้องการ
3. นับวันระหว่างวันที่สองวันที่ฝากเงิน

การคำนวณการชำระเงินเป็นงวดใน MS Excel เงินฝากประจำ

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

สรุป

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