สูตรชำละหนี้เงินกู้

[วังวู ช่ง]

เรียนทุกท่านที่รู้ครับ ช่วยแนะนำสูตรการชำละเงินกู้ให้ผมด้วยครับ
เงื่อนไข
1 ถ้ากู้เดือนไหนแม่นต้องชำละในเดือนนั้น
2 อัตราดอกเบี้ยรายเดือนแม่น 2.99%
3 ถ้าวงเงินกู้แต่ 10,000,000 ลงมาแม่นแต่ละเดือนชำละคงที่ แต่ 10,500,000 ขื้นไปแม่นชำละไม่เหมือนกันครับ

ขอบคุณล่วงหน้าครับ

[bank9597]

ผมไม่เข้าใจวิธีการคิดดอกเบี้ยครับ ว่าคิดอย่างไร เช่น

เงินต้น 10,000,000.00
ดอกเบี้ย 2.99% = 299,000.00
รวมต้นและดอก = 10,299,000.00
ผ่อน 12 เดือน เดือนล่ะ 858,250

แต่ในไฟล์ผ่อนเดือนล่ะ 1,133,000.00

และก็การผ่อนยอดไม่คงที่ มีวิธีการคิดยังไงครับ

[วังวู ช่ง]

ผมไม่เข้าใจวิธีการคิดดอกเบี้ยครับ ว่าคิดอย่างไร เช่น

เงินต้น 10,000,000.00
ดอกเบี้ย 2.99% = 299,000.00
รวมต้นและดอก = 10,299,000.00
ผ่อน 12 เดือน เดือนล่ะ 858,250

แต่ในไฟล์ผ่อนเดือนล่ะ 1,133,000.00

และก็การผ่อนยอดไม่คงที่ มีวิธีการคิดยังไงครับ

ความหมายคือว่า
เงินต้น 10,000,000.00
ดอกเบี้ย 2.99% = 299,000.00/เดือน ถ้า 12 เดือนคงเป็น 299,000*12=3,588,000
ต้องจ่ายผ่อน = (10,000,000+3,588,000)/12=1,132,333.33333333333333..... แต่เขาปรับขื้น 1,133,000 ครับ
ส่วนผู้ที่กู้แต่ 10,500,000 ขื้นเขาทำแบบนี้ครับ
ตัวอย่าง 15,000,000 กู้ 24 เดือน
1. เดือนที่ 1 คำนวนแบบนี้ครับ
- จ่ายต้นทุนแม่น = 15,000,000/24=625,000
- ดอกเบี้ยของเดือนที่หนึ่ง=15,000,000*2.99%=448,500 เขา up เป็น 449,000
รวม=625,000+449,000 = 1,074,000
2. เดือนที่ 2
- จ่ายต้นทุนแม่น = 15,000,000/24=625,000
- ดอกเบี้ยของเดือนที่สอง=(15,000,000-625,000)*2.99%=14,375,000*2.99%=429,812.50 ปรับเป็น 430,000
รวมเงินของเดือนที่สอง=625,000+430,000=1,055,000
3. เดือนที่ 3
- ดอกเบี้ยของเดือนที่สาม=(15,000,000-625,000-625,000)*2.99%=13,750,000*2.99%=412,000
รวมเงินจ่ายเดือนที่สาม = 625,000+412,000=1,037,000
และเดือนต่อๆไปทำเหมือกันครับ

[bank9597]

ที่ D3 คีย์

Code: Select all

=SUMPRODUCT(--(TEXT(D$2,"YYYYMM")*1>=TEXT($B3,"YYYYMM")*1),--(TEXT(D$2,"YYYYMM")*1<=TEXT(DATE(YEAR($B3),MONTH($B3)+$C3,1),"YYYYMM")*1),IF($A3>10500000,ROUNDUP(($A3/$C3),-3)+ROUNDUP((($A3-(($A3/$C3)*MAX(0,(COUNT($D$2:D$2)-COUNTIF($C3:C3,0)-1))))*2.99%),-3),ROUNDUP(($A3+($A3*2.99%)*$C3)/$C3,-3)))

คัดลอกไปขวามือ แล้วลงล่างพร้อมกัน

[วังวู ช่ง]

ที่ D3 คีย์

Code: Select all

=SUMPRODUCT(--(TEXT(D$2,"YYYYMM")*1>=TEXT($B3,"YYYYMM")*1),--(TEXT(D$2,"YYYYMM")*1<=TEXT(DATE(YEAR($B3),MONTH($B3)+$C3,1),"YYYYMM")*1),IF($A3>10500000,ROUNDUP(($A3/$C3),-3)+ROUNDUP((($A3-(($A3/$C3)*MAX(0,(COUNT($D$2:D$2)-COUNTIF($C3:C3,0)-1))))*2.99%),-3),ROUNDUP(($A3+($A3*2.99%)*$C3)/$C3,-3)))

คัดลอกไปขวามือ แล้วลงล่างพร้อมกัน

ขอบคุณมากๆ ครับ ผมมาลองแล้วคำตอบยังไม่ตรงครับ ผมกำลังดู และ ปรับสูตรอยู่ครับ แต่ยังไม่รู้ว่าได้คำตอบ หรือ เปล่าครับ

[primevalm]

Code: Select all

=IF(AND(D$2-TEXT($B3,"mm/yyyy")>=0,12*(YEAR(D$2)-YEAR($B3))+MONTH(D$2)-MONTH($B3)<$C3),CHOOSE(2-($A3<10500000),ROUNDUP((1/$C3+2.99%)*$A3,-3),ROUNDUP($A3/$C3,-3)+ROUNDUP($A3*(1-(12*(YEAR(D$2)-YEAR($B3))+MONTH(D$2)-MONTH($B3))/$C3)*2.99%,-3)),"")

เอาเท่านี้ก่อน

[bank9597]

ที่ D3 คีย์

Code: Select all

=SUMPRODUCT(--(TEXT(D$2,"YYYYMM")*1>=TEXT($B3,"YYYYMM")*1),--(TEXT(D$2,"YYYYMM")*1<=TEXT(DATE(YEAR($B3),MONTH($B3)+$C3,1),"YYYYMM")*1),IF($A3>10500000,ROUNDUP(($A3/$C3),-3)+ROUNDUP((($A3-(($A3/$C3)*MAX(0,(COUNT($D$2:D$2)-COUNTIF($C3:C3,0)-1))))*2.99%),-3),ROUNDUP(($A3+($A3*2.99%)*$C3)/$C3,-3)))

คัดลอกไปขวามือ แล้วลงล่างพร้อมกัน

ขอบคุณมากๆ ครับ ผมมาลองแล้วคำตอบยังไม่ตรงครับ ผมกำลังดู และ ปรับสูตรอยู่ครับ แต่ยังไม่รู้ว่าได้คำตอบ หรือ เปล่าครับ

ลองเอาสูตรไปปรับดูครับ เบื้องต้นผมได้ใช้สูตรกับไฟล์เดิมของคุณแล้ว แสดงผลถูกต้องครับ

[วังวู ช่ง]

Code: Select all

=IF(AND(D$2-TEXT($B3,"mm/yyyy")>=0,12*(YEAR(D$2)-YEAR($B3))+MONTH(D$2)-MONTH($B3)<$C3),CHOOSE(2-($A3<10500000),ROUNDUP((1/$C3+2.99%)*$A3,-3),ROUNDUP($A3/$C3,-3)+ROUNDUP($A3*(1-(12*(YEAR(D$2)-YEAR($B3))+MONTH(D$2)-MONTH($B3))/$C3)*2.99%,-3)),"")

เอาเท่านี้ก่อน

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

[วังวู ช่ง]

ที่ D3 คีย์

Code: Select all

=SUMPRODUCT(--(TEXT(D$2,"YYYYMM")*1>=TEXT($B3,"YYYYMM")*1),--(TEXT(D$2,"YYYYMM")*1<=TEXT(DATE(YEAR($B3),MONTH($B3)+$C3,1),"YYYYMM")*1),IF($A3>10500000,ROUNDUP(($A3/$C3),-3)+ROUNDUP((($A3-(($A3/$C3)*MAX(0,(COUNT($D$2:D$2)-COUNTIF($C3:C3,0)-1))))*2.99%),-3),ROUNDUP(($A3+($A3*2.99%)*$C3)/$C3,-3)))

คัดลอกไปขวามือ แล้วลงล่างพร้อมกัน

ขอบคุณมากๆ ครับ ผมมาลองแล้วคำตอบยังไม่ตรงครับ ผมกำลังดู และ ปรับสูตรอยู่ครับ แต่ยังไม่รู้ว่าได้คำตอบ หรือ เปล่าครับ

ลองเอาสูตรไปปรับดูครับ เบื้องต้นผมได้ใช้สูตรกับไฟล์เดิมของคุณแล้ว แสดงผลถูกต้องครับ

ครับผม ผมจะลองดูครับ ไงจะแจ้งให้ครับ

[วังวู ช่ง]

ที่ D3 คีย์

Code: Select all

=SUMPRODUCT(--(TEXT(D$2,"YYYYMM")*1>=TEXT($B3,"YYYYMM")*1),--(TEXT(D$2,"YYYYMM")*1<=TEXT(DATE(YEAR($B3),MONTH($B3)+$C3,1),"YYYYMM")*1),IF($A3>10500000,ROUNDUP(($A3/$C3),-3)+ROUNDUP((($A3-(($A3/$C3)*MAX(0,(COUNT($D$2:D$2)-COUNTIF($C3:C3,0)-1))))*2.99%),-3),ROUNDUP(($A3+($A3*2.99%)*$C3)/$C3,-3)))

คัดลอกไปขวามือ แล้วลงล่างพร้อมกัน

ขอบคุณมากๆ ครับ ผมมาลองแล้วคำตอบยังไม่ตรงครับ ผมกำลังดู และ ปรับสูตรอยู่ครับ แต่ยังไม่รู้ว่าได้คำตอบ หรือ เปล่าครับ

ลองเอาสูตรไปปรับดูครับ เบื้องต้นผมได้ใช้สูตรกับไฟล์เดิมของคุณแล้ว แสดงผลถูกต้องครับ

ขอบคุณท่านมากๆครับ สูตรของท่านที่แนะนำผม ชำละเงินเกีนหนึ่งเดือนครับ ตัวอย่างเงินกู้
จำนวนเงินกู้ วันอนุมัติ จำนวนเดือนกู้
20,000,000.00 29/10/2015 24.00
เลี่มจ่าย 10/2015 แล้วจ่ายครั้งสุดท้ายคือ 09/2017 แต่ที่ท่านแนะนำผมแม่นถึง 10/2017 แล้วจะแก้ตรงไหน อย่างไรครับ

[bank9597]

เพิ่ม -1 ลงไปในสูตรครับ ตามด้านล่าง ตัวสีแดงครับ

MONTH($B3)+$C3-1,1

[วังวู ช่ง]

ที่ D3 คีย์

Code: Select all

=SUMPRODUCT(--(TEXT(D$2,"YYYYMM")*1>=TEXT($B3,"YYYYMM")*1),--(TEXT(D$2,"YYYYMM")*1<=TEXT(DATE(YEAR($B3),MONTH($B3)+$C3,1),"YYYYMM")*1),IF($A3>10500000,ROUNDUP(($A3/$C3),-3)+ROUNDUP((($A3-(($A3/$C3)*MAX(0,(COUNT($D$2:D$2)-COUNTIF($C3:C3,0)-1))))*2.99%),-3),ROUNDUP(($A3+($A3*2.99%)*$C3)/$C3,-3)))

คัดลอกไปขวามือ แล้วลงล่างพร้อมกัน

ขอบคุณมากๆ ครับ ผมมาลองแล้วคำตอบยังไม่ตรงครับ ผมกำลังดู และ ปรับสูตรอยู่ครับ แต่ยังไม่รู้ว่าได้คำตอบ หรือ เปล่าครับ

ลองเอาสูตรไปปรับดูครับ เบื้องต้นผมได้ใช้สูตรกับไฟล์เดิมของคุณแล้ว แสดงผลถูกต้องครับ

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

[bank9597]

ลองปรับสูตรตามนี้ครับ

ที่ G4 คีย์ =SUMPRODUCT(--(TEXT(G$3,"YYYYMM")*1>=TEXT($D4,"YYYYMM")*1),--(TEXT(G$3,"YYYYMM")*1<=TEXT(DATE(YEAR($D4),MONTH($D4)+$F4-1,1),"YYYYMM")*1),IF($E4>10500000,ROUNDUP(($E4/$F4),-3)+ROUNDUP((($E4-(($E4/$F4)*MAX(0,(COUNT($G$3:G$3)-COUNTIF($F4:F4,0)-1))))*2.99%),-3),ROUNDUP(($E4+($E4*2.99%)*$F4)/$F4,-3)))
คัดลอกไปทางขวามือ แล้วลงล่างพร้อมกัน

ตรวจสอบผลลัพธ์แล้วแจ้งข้อผิดพลาดมาอีกครั้ง (หากมี ให้ทำสีช่องที่ผิดพลาด)

ข้อสังเกตุ คือ เริมกู้เดือน 9 แต่ในหัวตารางเริ่มที่เดือน 10 แบบนี้ทำให้ยอดหายไป 1 เดือนครับ ตามภาพด้านล่าง

[วังวู ช่ง]

แบบนี้ก็อใช้ได้แล้วครับ ขอบคุณท่านมากๆครับ ใช้ไปก่อนเป็นไงจะแจ้งให้อีกทีครับ