การใช้ excel ช่วยคำนวณดอกเบี้ยเงินฝากประจำ

การใช้ excel ในบทความนี้ขอนำเสนอ การใช้ excel คำนวณดอกเบี้ยเงินฝากประจำ โดยเงินฝากประจำที่จะนำเสนอในบทความนี้จะหมายถึง การฝากเงินเป็นประจำทุกๆเดือนติดต่อกันในระยะเวลาที่กำหนดและดอกเบี้ยที่กำหนด ปัญหาของผู้ที่ต้องการฝากเงินในลักษณะนี้คือ สุดท้ายแล้วจะได้ดอกเบี้ยเท่าไหร่ เงินฝากประจำแบบนี้เป็นการลงทุนการเงินที่มีความเสี่ยงต่ำ โดยสถาบันการเงินในปัจจุบันให้ดอกเบี้ยค่อนข้างสูง(หากเทียบกับเงินฝากลักษณะอื่นๆ) และที่สำคัญดอกเบี้ยของการฝากเงินลักษณะนี้ไม่ถูกหักภาษีเงินได้ร้อยละ 15 ครับ ในบทความนี้จะนำเสนอการสร้างตารางใน excel เพื่อคิดดอกเบี้ยเงินฝาก เริ่มต้นจากแนวคิดการคิดดอกเบี้ยเงินฝากของเงินฝากประจำดังกล่าว โดยมีแนวคิดดังนี้ 

ดอกเบี้ยที่ได้รับในแต่ละเดือน = เงินต้น x อัตราดอกเบี้ย(ร้อยละต่อปี)xจำนวนวันที่คิดดอกเบี้ย/365

โดยส่วนใหญ่เมื่อครบกำหนดฝากธนาคารจะคิดโบนัสดอกเบี้ยอีกร้อยละ 2.5 ของดอกเบี้ยที่ได้รับ 

เงินต้นที่ใช้คำนวณในแต่ละเดือนจะเพิ่มขึ้นตามเงินฝากที่สะสมในแต่ละเดือน ตาราง excel สำหรับคำนวณดอกเบี้ยแสดงได้ดังภาพที่ 1 โดยสมมุติให้เป็นเงินฝากแบบประจำ 12 เดือน อัตราดอกเบี้ยร้อยละ 3.5 โดยเริ่มเปิดบัญชีเงินฝากวันที่ 1 มค 2555

ภาพที่ 1 ตาราง excel คำนวณดอกเบี้ยเงินฝากประจำ

จากภาพที่ 1 จะขออธิบายสูตร excel และการบันทึกข้อมูลในแต่ละเซลล์ดังนี้

• ข้อมูลจำนวนงวดเงินฝากในหลัก A และ วันเดือนปีที่ฝากเงินในแต่ละเดือน ผู้ใช้ excel ต้องบันทึกทุกเดือน
• การคำนวณเงินต้นสะสมในงวดที่ 2 ถึง 12 จะเขียนสูตร excel โดยอ้างอิงเงินต้นสะสมในเซลล์ C2
• สูตร excel เงินต้นสะสม C3 = $C$2*A3 (copy สูตร excel ลงไปในแถวด้านล่างต่อไป)
• ข้อมูลอัตราดอกเบี้ยเงินฝาก ผู้ใช้ excel ต้องบันทึกทุกเดือน (ในกรณีที่ดอกเบี้ยเงินฝากในแต่ละเดือนไม่คงที่)
• จำนวนสำหรับคำนวณดอกเบี้ยในแต่ละเดือน จะคำนวณระยะห่างจากวันฝากเงินเดือนนี้และวันฝากเงินเดือนถัดไป เราสามารถเขียนสูตร excel ได้ดังนี้ E2 = B3-B4
• ดอกเบี้ยแต่ละเดือน เขียนสูตร excel ได้ดังนี้ F2 = C2*(E2/365)*E2
• ในเซลล์ B14 จะเป็นวันเดือนปีที่ครบกำหนดการฝากประจำ
• ในเซลล์ F14 เป็นดอกเบี้ยรวมทั้ง 12 เดือน เขียนสูตร excel ได้เป็น F14=SUM(F2:F13)
• ในเซลล์ F15 คำนวณโบนัสดอกเบี้ยร้อยละ 2.5 เขียนสูตร excel ได้เป็น F15 = F14*0.025
• ในเซลล์ F16 คำนวณดอกเบี้ย  เขียนสูตร excel ได้เป็น F16 = F15+F14
• ในเซลล์ F17 คำนวณเงินต้นและดอกเบี้ยที่ได้จาการฝากประจำ เขียนสูตร excel ได้เป็น F17 = F13+F16

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

การใช้ excel : ประยุกต์การสร้างกราฟเส้นตรงกับกราฟแนวโน้มแบบยกกำลัง (1)

การใช้ excel ในบทความนี้ขอนำคำถามจากคุณ kv ซึ่งฝากไว้ที่บทความเรื่อง การใช้ excel สร้างกราฟเส้นตรง (Linear graph) เกี่ยวกับการวิเคราะห์ข้อมูลที่มีแนวโน้มแบบยกกำลังหรือแบบ exponential โดยผมจะนำเสนอการประยุกต์การวิเคราะห์ข้อมูลแบบกราฟเส้นตรงที่ได้นำเสนอมาแล้วในบทความดังกล่าว โดยเริ่มต้นจากการแนะนำรูปแบบฟังก์ชันยกกำลังที่นิยมนำมาใช้วิเคราะห์แนวโน้มข้อมูล ซึ่งในบทความนี้ขอนำเสนอ 3 ฟังก์ชันดังนี้ครับ

แนวคิด

เราจะเปลี่ยนฟังก์ชันยกกำลังเหล่านี้ให้อยู่ในรูปของฟังก์ชันเชิงเส้น y = mx + c และประยุกต์ใช้สูตร excel ที่เกี่ยวกับกราฟเส้นตรงซึ่งได้อธิบายไปแล้วในบทความ การใช้ excel สร้างกราฟเส้นตรง คำนวณหาความชันและจุดตัดแกน y และย้อนกลับไปคำนวณหาค่าคงที่ a , b ในฟังก์ชันยกกำลังดังกล่าว และสุดท้ายจะประยุกต์ใช้สูตร excel : TREND ซึ่งใช้สำหรับสมการเส้นตรง ช่วยคำนวณค่า y ในฟังก์ชันยกกำลังดังกล่าว

การเปลี่ยนฟังก์ชันยกกำลังให้เป็นพังก์ชันเชิงเส้น

ทำการ Take ค่า ln กับฟังก์ชันยกกำลังทั้งสองข้างและจัดรูปแบบสมการใหม่ได้ดังนี้ครับ

เมื่อพิจารณาฟังก์ชันที่ถูกแปลงใหม่กับรูปแบบของสมการเส้นตรง y = mx + c เราจะพบว่า

สมการแรก หากเราพล็อตกราฟ แกนตั้งเป็นค่า ln y แกนนอนเป็นค่า x  ความชันของชุดข้อมูล m จะเท่ากับ ln b จุดตัดแกนตั้ง c = ln a ดังนั้น a = exponential(c)  ขณะที่ b = exponential(m)

สมการที่สอง หากเราพล็อตกราฟ แกนตั้งเป็นค่า ln y แกนนอนเป็นค่า x  ความชันของเส้นตรง m จะเท่ากับ b จุดตัดแกนตั้ง c = ln a ดังนั้น a = exponential(c)  ขณะที่ b = m

สมการที่สาม หากเราพล็อตกราฟ แกนตั้งเป็นค่า ln y แกนนอนเป็นค่า ln x  ความชันของเส้นตรง m จะเท่ากับ b จุดตัดแกนตั้ง c = ln a ดังนั้น a = exponential(c)  ขณะที่ b = m 

การเตรียมข้อมูลใน excel เพื่อคำนวณหาค่าคงที่ในฟังก์ชันยกกำลัง

ผมจะยกตัวอย่างการหาวิเคราะห์เส้นแนวโน้มของสมการที่ 1 สมมุติว่าเรามีข้อมูล x และ y จากข้อมูลจริงและได้บันทึกข้อมูลลง excel ในหลัก A และ B ดังแสดงในภาพที่ 1

ภาพที่ 1 ลักษณะข้อมูล

จากลักษณะของฟังก์ชันยกกำลังที่เปลี่ยนเป็นฟังก์ชันเส้นตรงของสมการที่ 1 จะต้องแปลงค่า y ให้เป็นค่า ln y โดยเก็บไว้ในหลัก C เขียนสูตร excel ได้เป็น C4= ln(B4) 

ใช้สูตร excel : SLOPE เพื่อหาความชันและจุดตัดของข้อมูล (x , lny) ใน D2 และ E2 ได้ดังนี้

สูตร excel 

=SLOPE($C$4:$C$13,$A$4:$A$13)

=INTERCEPT($C$4:$C$13,$A$4:$A$13)

เราสามารถคำนวณค่าคงที่ a และ b ใน F2 และ G2 โดยเขียนสูตร excel ได้ดังนี้

=exp(E2)

=exp(D2)

เราสามารถคำนวณค่า y ของฟังก์ชันยกกำลังได้โดยใช้ค่าคงที่ a และ b ที่คำนวณได้ โดยคำนวณและเก็บไว้ในหลัก D ดังนี้

สูตร excel : D4 = $F$2*$G$2^$A4 (copy สูตร excel ไปยังแถวถัดไปได้)

ภาพที่ 2 ผลการคำนวณหาเส้นแนวโน้ม

สูตร excel สำหรับคำนวณค่าตัวแปรตาม (y) ในฟังก์ชันยกกำลัง

ในบทความ การใช้ excel สร้างกราฟเส้นตรง เราใช้สูตร excel : TREND สำหรับคำนวณค่าตัวแปรตามในสมการเส้นตรง เมื่อกำหนดค่าตัวแปรอิสระ x เราสามารถประยุกต์ สูตร excel : TREND กับฟังก์ชันยกกำลังได้ โดยมีหลักการดังนี้ 

เมื่อพิจารณาฟังก์ชันยกกำลังซึ่งถูกเปลี่ยนอยู่ในรูปสมการเชิงเส้นไปแล้วนั้นจะพบว่า หากใช้สูตร excel : TREND กับข้อมูลดังกล่าว สูตร excel : TREND จะคืนค่า ln y กลับมา ดังนั้นหากต้องการทราบค่า y ในฟังก์ชันยกกำลังจะต้องเขียนสูตร excel ดังนี้ สมมุติให้ เซลล์ C1 เป็นจุด x ที่ต้องการทราบค่า y และค่า y จะถูกคำนวณและแสดงในเซลล์ C2 เราจะเขียนสูตร excel ได้ดังนี้

C2 = exp(TREND($C$4:$C$13,$A$4:$A$13,$C$1))

ในกรณีสมการที่ 3 สูตร excel จะเปลี่ยนเป็น

C2 = exp(TREND($C$4:$C$13,$A$4:$A$13,ln($C$1)))

 

ผลคำนวณแสดงได้ดังภาพที่ 3

ภาพที่ 3 ผลการประยุกต์ใช้สูตร excel :TREND

จากที่ผมยกตัวอย่างมาเป็นการวิเคราะห์แนวโน้มตามลักษณะของสมการที่ 1 โดยท่านผู้อ่านสามารถนำไปรูปแบบการคำนวณใน excel ไปใช้กับสมการที่ 2 ได้ทันที 

โดยปรับสูตร excel ใน G2 จาก G2 = exp(D2) เป็น G2 = D2 และ

ปรับสูตร excel ใน D4-D13 เป็น D4 = $F$2*exp($G$2^$A4) (copy สูตร excel ไปยังแถวถัดไปได้)

ในขณะที่รูปแบบการคำนวณใน excel สำหรับสมการที่ 3 นั้นจะปรับเปลี่ยนการคำนวณดังนี้

คำนวณ ln x ในหลัก C

คำนวณ ln y ในหลัก D

คำนวณค่า slope ใน D2 ด้วยสูตร excel : D2=SLOPE($D$4:$D$13,$C$4:$C$13)

คำนวณค่า Intercept ใน E2 ด้วยสูตร excel : E2=SLOPE($D$4:$D$13,$C$4:$C$13)

คำนวณค่า a ใน F2 ด้วยสูตร excel : F2=exp(E2)

คำนวณค่า a ใน G2 ด้วยสูตร excel : G2=D2

ข้อควรระวังสำหรับการประยุกต์สมการเส้นตรงกับ สมการที่ 1-3 คือค่า y ที่ป้อนเข้าจะต้องไม่มีค่าเท่ากับศูนย์หรือใกล้ศูนย์มากๆ เนื่องจากไม่สามารถหาค่า ln 0 ได้ ในขณะที่สมการที่ 3 ค่า x ที่ป้อนเข้าจะต้องไม่มีค่าเท่ากับศูนย์หรือใกล้ศูนย์มากๆ เนื่องจากไม่สามารถหาค่า ln 0 ได้ 

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