ในการคำนวณดอกเบี้ยทบต้นใน Excel ให้ใช้ =P*(1+r/n)^(n*t) หรือฟังก์ชันในตัว FV(): =FV(r/n, n*t, 0, -P) สำหรับ $1,000 ที่อัตรา 5% ทบต้นรายเดือน เป็นเวลา 5 ปี ทั้งสองแบบจะได้ผลลัพธ์ $1,283.36
ในคู่มือนี้จะแสดงการคำนวณทีละขั้น: สูตรคำนวณเอง, FV() และ PMT(), การคาดการณ์เงินออมเพื่อเกษียณ, ตารางตัดจำหน่ายเงินกู้, อัตราดอกเบี้ยผันแปร และผลกระทบจากความถี่ในการทบต้น สำหรับเทคนิคการสร้างแบบจำลองที่ครอบคลุมซึ่งต่อยอดจากเนื้อหานี้ ดูคอร์ส Financial Modeling in Excel ของเรา
สรุปสั้นๆ
- สูตรดอกเบี้ยทบต้นใน Excel คือ
=P*(1+r/n)^(n*t)
FV() ให้ผลลัพธ์เดียวกันและยังรองรับเงินสมทบสม่ำเสมอ
PMT() คำนวณย้อนกลับจากเป้าหมายเพื่อหาเงินผ่อนชำระรายเดือนที่ต้องการ
- ใช้
EFFECT() เพื่อเปรียบเทียบอัตราที่มีความถี่การทบต้นต่างกัน
FVSCHEDULE() ใช้อัตราดอกเบี้ยที่ต่างกันแต่ละปีในสูตรเดียว
ดอกเบี้ยทบต้นคืออะไร?
ดอกเบี้ยทบต้นคือการเติบโตแบบเอ็กซ์โปเนนเชียลของเงินตามกาลเวลา แตกต่างจากดอกเบี้ยอย่างง่ายที่เติบโตเชิงเส้น ดอกเบี้ยทบต้นจะเร่งตัวขึ้นเมื่อดอกเบี้ยที่ได้รับในแต่ละงวดกลายเป็นส่วนหนึ่งของเงินต้นสำหรับงวดถัดไป
ความแตกต่างของอัตราเพียงเล็กน้อยจะยิ่งใหญ่เมื่อเวลาผ่านไป $10,000 ฝากไว้ 30 ปี ที่ 6% ทบต้นรายปี เติบโตเป็น $57,434.91 หาก 8% จะเติบโตเป็น $100,626.57 เกือบสองเท่า
ตัวอย่างความแตกต่างของ $10,000 ที่ 6% ต่อปี เป็นเวลา 5 ปี:
| | ดอกเบี้ยอย่างง่าย | ดอกเบี้ยทบต้น (รายปี) |
|---|
| สูตรใน Excel | =10000*(1+0.06*5) | =10000*(1+0.06)^5 |
| ยอดเงินสุดท้าย | $13,000.00 | $13,382.26 |
| ดอกเบี้ยที่ได้รับ | $3,000.00 | $3,382.26 |
ส่วนเพิ่ม $382.26 คือดอกเบี้ยที่เกิดจากดอกเบี้ยก่อนหน้า และช่องว่างจะกว้างขึ้นทุกปีที่เงินยังคงถูกลงทุน
สูตรดอกเบี้ยทบต้น
ดอกเบี้ยทบต้นคำนวณด้วยสูตร:

สูตรดอกเบี้ยทบต้น ภาพโดยผู้เขียน
โดยที่:
- A = ยอดเงินสุดท้ายหลังทบต้น
- P = เงินต้น (เงินลงทุนตั้งต้น)
- r = อัตราดอกเบี้ยรายปี (ในรูปทศนิยม)
- n = จำนวนครั้งที่ทบต้นต่อปี
- t = จำนวนปี
เมื่อเข้าใจแนวคิดและสูตรคณิตศาสตร์แล้ว มาดูวิธีทำใน Excel กัน
วิธีคำนวณดอกเบี้ยทบต้นใน Excel
เพื่อใช้สูตรนี้ใน Excel ให้ตั้งค่าตารางง่ายๆ โดยใส่ป้ายกำกับชัดเจนสำหรับแต่ละตัวแปร
เริ่มจากจัดระเบียบสเปรดชีตด้วยป้ายกำกับต่อไปนี้:
- เซลล์ A1: “Principal”
- เซลล์ A2: “Annual Interest Rate”
- เซลล์ A3: “Compounding Periods per Year”
- เซลล์ A4: “Time (years)”
- เซลล์ A5: “Final Amount”
จากนั้นป้อนค่าที่คอลัมน์ B:
-
เซลล์ B1: ใส่จำนวนเงินต้น (เช่น 1000)
-
เซลล์ B2: ใส่อัตราดอกเบี้ยในรูปทศนิยม (เช่น 0.05 สำหรับ 5%)
-
เซลล์ B3: ใส่จำนวนงวดทบต้นต่อปี (เช่น 12 สำหรับรายเดือน)
-
เซลล์ B4: ใส่จำนวนปี (เช่น 5)
หลังตั้งค่าข้อมูลแล้ว แผ่นงาน Excel ควรมีลักษณะดังนี้:

การตั้งค่าตารางใน Excel ภาพโดยผู้เขียน
สุดท้าย ในเซลล์ B5 ใส่สูตรดอกเบี้ยทบต้น:
=B1*(1+B2/B3)^(B3*B4)ขับเคลื่อนโดย
สูตรดอกเบี้ยทบต้นใน Excel ภาพโดยผู้เขียน
สูตรนี้นำสมการทางคณิตศาสตร์ด้านบนมาใช้โดยตรง เพื่อคำนวณยอดเงินสุดท้ายหลังทบต้น

การคำนวณดอกเบี้ยทบต้น ภาพโดยผู้เขียน
การใช้ฟังก์ชัน FV()
แม้สูตรคำนวณเองจะใช้ได้ดี แต่ Excel ก็มีฟังก์ชันการเงินในตัวที่ช่วยให้คำนวณดอกเบี้ยทบต้นง่ายขึ้น ฟังก์ชัน FV() (Future Value) มีประโยชน์มากสำหรับคำนวณดอกเบี้ยทบต้นเมื่อมีการชำระเงินหรือลงทุนสม่ำเสมอ
ไวยากรณ์ของฟังก์ชัน FV() คือ:
=FV(rate, nper, pmt, [pv], [type])ขับเคลื่อนโดย
โดยที่:
-
rate = อัตราดอกเบี้ยต่องวด
-
nper = จำนวนงวดทั้งหมด
-
pmt = เงินที่จ่ายแต่ละงวด (ใส่ 0 หากไม่มี)
-
pv = มูลค่าปัจจุบัน (เงินต้นตั้งต้น) Excel มองเงินที่จ่ายออกเป็นค่าติดลบ และเงินที่ได้รับเป็นค่าบวก ดังนั้นการใส่เงินต้นเป็นจำนวนลบจะทำให้ได้ผลลัพธ์เป็นบวก
-
type = เวลาที่ชำระเงิน (0 สำหรับสิ้นงวด, 1 สำหรับต้นงวด)
มาปรับสเปรดชีตให้ใช้ฟังก์ชัน FV() กัน:
- เซลล์ A6: “Using FV() Function”
พิมพ์ดังนี้ในเซลล์ B6:
=FV(B2/B3, B3*B4, 0, -B1)ขับเคลื่อนโดย

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

ดอกเบี้ยทบต้นด้วย FV() ภาพโดยผู้เขียน
หากต้องการเฉพาะดอกเบี้ยที่ได้รับ ไม่ใช่ยอดสุดท้าย ให้หักเงินต้นออก: =FV(B2/B3, B3*B4, 0, -B1)-B1 ได้ $283.36
การใช้ฟังก์ชัน PMT()
ฟังก์ชัน PMT() คำนวณค่างวดเงินกู้ บนพื้นฐานการผ่อนชำระและอัตราดอกเบี้ยคงที่ แม้ออกแบบหลักๆ เพื่อเงินกู้ แต่สามารถปรับใช้กับกรณีดอกเบี้ยทบต้นเพื่อหาจำนวนเงินสมทบสม่ำเสมอที่ต้องจ่ายเพื่อให้ถึงยอดเป้าหมายได้
ไวยากรณ์ของฟังก์ชัน PMT() คือ:
=PMT(rate, nper, pv, [fv], [type])ขับเคลื่อนโดย
โดยที่:
-
rate = อัตราดอกเบี้ยต่องวด
-
nper = จำนวนงวดทั้งหมด
-
pv = มูลค่าปัจจุบัน (เงินต้นตั้งต้น)
-
fv = มูลค่าในอนาคต (ยอดเป้าหมายที่ต้องการถึง)
-
type = เวลาที่ชำระเงิน (0 สำหรับสิ้นงวด, 1 สำหรับต้นงวด)
มาลงในสเปรดชีตกัน:
-
เซลล์ A7: “Target Amount”
-
เซลล์ B7: ใส่ยอดเป้าหมาย (เช่น 5000)
-
เซลล์ A8: “Required Monthly Payment”
พิมพ์ดังนี้ในเซลล์ B8:
=PMT(B2/B3, B4*B3, -B1, B7)ขับเคลื่อนโดย
คำนวณดอกเบี้ยทบต้นด้วย PMT() ภาพโดยผู้เขียน
สูตรนี้คำนวณจำนวนเงินที่ต้องสมทบรายเดือนเพื่อให้ถึงยอดเป้าหมาย โดยอิงจากเงินตั้งต้นและอัตราดอกเบี้ยของคุณ
จำนวนเงินผ่อนรายเดือนที่คำนวณได้จะแสดงดังนี้:

ดอกเบี้ยทบต้นด้วย PMT() ภาพโดยผู้เขียน
ฟังก์ชัน PMT() ใน Excel จะคืนค่าเป็นลบโดยปริยาย เพราะมองจากมุมมองกระแสเงินสด:
- ค่าลบแทนเงินที่จ่ายออก (กระแสเงินออก)
- ค่าบวกแทนเงินที่ได้รับ (กระแสเงินเข้า)
ดอกเบี้ยทบต้นใน Excel ในการใช้งานจริง
สูตรดอกเบี้ยทบต้นใน Excel ใช้ได้จริงหลากหลาย ทั้งการเงินส่วนบุคคลและธุรกิจ มาดูสถานการณ์ใช้งานที่เป็นรูปธรรมกัน
เงินออมและการลงทุน
การคาดการณ์การเติบโตของเงินออมหรือบัญชีลงทุนเป็นการใช้งานที่พบบ่อยของการคำนวณดอกเบี้ยทบต้น การเข้าใจว่าเงินเติบโตอย่างไรเมื่อเวลาผ่านไปช่วยให้ตัดสินใจได้ดีขึ้น
มาสร้างแบบจำลองการออมเพื่อเกษียณในส่วนใหม่ของสเปรดชีต หลังพิมพ์ “Retirement Savings Projection” ในเซลล์ D1 ให้ตั้งค่าพารามิเตอร์ในเซลล์ D2 ถึง D6:
-
เซลล์ D2: “Initial Investment” และ E2: 10000
-
เซลล์ D3: “Monthly Contribution” และ E3: 500
-
เซลล์ D4: “Annual Interest Rate” และ E4: 0.07
-
เซลล์ D5: “Time Horizon (years)” และ E5: 30
-
เซลล์ D6: “Compounding Frequency” และ E6: “Monthly”
หลังพิมพ์ “Projected Future Value” ในเซลล์ D7 ให้พิมพ์สมการนี้ใน E7:
=FV(E4/12, E5*12, -E3, -E2)ขับเคลื่อนโดย
คำนวณมูลค่าในอนาคตที่คาดการณ์ไว้ ภาพโดยผู้เขียน
สูตรนี้คาดการณ์เงินออมเพื่อเกษียณ โดยคำนึงถึงทั้งเงินตั้งต้นและเงินสมทบสม่ำเสมอ ดูมูลค่าในอนาคตได้ดังนี้:

มูลค่าในอนาคตที่คาดการณ์ด้วย FV() ภาพโดยผู้เขียน
สามารถต่อยอดแบบจำลองด้วยการสร้างตารางคาดการณ์แบบปีต่อปี:
-
ที่เซลล์ D9 ใส่ “Year-by-Year Projection”
-
ที่เซลล์ D10 ใส่ “Year” และที่เซลล์ E10 ใส่ “Balance”
-
ที่เซลล์ D11 ใส่ 0 (ปีเริ่มต้น)
-
ที่เซลล์ E11 ใส่เงินตั้งต้น: =E2
-
ที่เซลล์ D12 ใส่ 1
-
ที่เซลล์ E12 คำนวณยอดคงเหลือหลังปีที่ 1:
=FV($E$4/12, 1*12, -$E$3, -E11)ขับเคลื่อนโดย
การสร้างตารางคาดการณ์ปีต่อปี ภาพโดยผู้เขียน
- เลือกเซลล์ D11 และ D12 จากนั้นคลิกและลาก fill handle (สี่เหลี่ยมมุมขวาล่าง) ลงไปถึงแถว 41 เพื่อสร้างปี 0 ถึง 30
- เลือกเซลล์ E12 แล้วคลิกและลาก fill handle ลงไปถึงแถว 41
Excel จะปรับการอ้างอิงเซลล์อัตโนมัติในแต่ละแถว สร้างลำดับการคำนวณที่แต่ละปีคำนวณต่อจากยอดปีที่แล้ว

ตารางคาดการณ์ปีต่อปี ภาพโดยผู้เขียน
ตารางแสดงให้เห็นว่าอัตราการเติบโตเร่งขึ้น: ยอดใช้เวลา 9 ปีจึงเกิน $90,000 แล้วเพิ่มขึ้นมากกว่าสองเท่าใน 10 ปีถัดมา
ใช้เวลานานเท่าใดจึงจะถึงเป้าหมาย
หากกลับคำถามว่าใช้เวลากี่ปีจึงจะถึงยอดเป้าหมาย ให้ใช้ NPER() ด้วยอินพุตเดิมและเป้าหมาย $100,000:
=NPER(E4/12, -E3, -E2, 100000)ขับเคลื่อนโดย
ได้ผลประมาณ 114 เดือน หรือ 9.5 ปี แบ่งด้วย 12 เพื่อแปลงเป็นปี สำหรับเป้าหมายที่ไม่มีฟังก์ชันสำเร็จรูป Goal Seek ใช้หาอินพุตที่ทำให้ได้ผลลัพธ์ตามเป้า
การคำนวณเงินกู้
ดอกเบี้ยทบต้นเกี่ยวข้องกับเงินกู้เช่นกัน โดยดอกเบี้ยจะทบจากยอดคงเหลือ Excel มีฟังก์ชันช่วยทำความเข้าใจหลายด้านของเงินกู้ ตั้งแต่ค่างวดรายเดือนถึงดอกเบี้ยรวมที่จ่าย
เพื่อสร้างตารางตัดจำหน่ายเงินกู้ ให้ตั้งค่าพารามิเตอร์เงินกู้ดังตัวอย่าง:
การคำนวณค่างวดรายเดือนของเงินกู้ ภาพโดยผู้เขียน
ที่เซลล์ H5 พิมพ์ดังนี้เพื่อคำนวณค่างวดรายเดือน:
=PMT(H3/12, H4*12, H2)ขับเคลื่อนโดย
คำนวณค่างวดรายเดือนด้วย PMT() ภาพโดยผู้เขียน
จะเห็นค่างวดรายเดือนดังนี้:

ค่างวดรายเดือนของเงินกู้ ภาพโดยผู้เขียน
จากนั้นตั้งค่าหัวตาราง ตารางตัดจำหน่าย ดังนี้:
การสร้างตารางตัดจำหน่าย ภาพโดยผู้เขียน
ใส่ค่าเริ่มต้นสำหรับงวดแรก (แถว 8):
-
เซลล์ G8: 1 (งวดที่หนึ่ง)
-
เซลล์ H8: =H2 (ยอดเงินกู้เริ่มต้น)
-
เซลล์ I8: =ABS(H5) (จำนวนค่างวด ใช้ ABS() แปลงผลลัพธ์ติดลบของ PMT() ให้เป็นบวก)
-
เซลล์ J8: =H8*($H$3/12) (ส่วนดอกเบี้ย: ยอดยกมาต้นงวด × อัตรารายเดือน)
-
เซลล์ K8: =I8-J8 (ส่วนเงินต้น: ค่างวด − ดอกเบี้ย)
-
เซลล์ L8: =H8-K8 (ยอดปลายงวด: ยอดต้นงวด − เงินต้นที่ชำระ)
การคำนวณในตารางตัดจำหน่าย ภาพโดยผู้เขียน
สำหรับงวดที่สอง (แถว 9) ให้สร้างสูตรดังนี้:
-
เซลล์ G9: =G8+1 (ลำดับงวด)
-
เซลล์ H9: =L8 (ยอดต้นงวดเท่ากับยอดปลายงวดก่อนหน้า)
-
เซลล์ I9: =I8 (ค่างวดเท่าเดิม)
-
เซลล์ J9: =H9*($H$3/12) (ส่วนดอกเบี้ยคำนวณจากยอดต้นงวดใหม่)
-
เซลล์ K9: =I9-J9 (ส่วนเงินต้น)
-
เซลล์ L9: =H9-K9 (ยอดปลายงวด)
การคำนวณในตารางตัดจำหน่าย ภาพโดยผู้เขียน
จากนั้นเลือกเซลล์ G9 ถึง L9 แล้วลากลงไปถึงแถว 367 (ครบ 360 งวด)
ที่เซลล์ H6 สามารถคำนวณดอกเบี้ยรวมที่จ่ายได้:
=SUM(J8:J367)ขับเคลื่อนโดย
ตารางสุดท้ายจะมีหน้าตาดังนี้:
ตารางตัดจำหน่าย ภาพโดยผู้เขียน
ตารางแสดงให้เห็นว่ายอดหนี้ลดช้าในช่วงแรก ค่างวดแรก $1,266.71 มีดอกเบี้ย $937.50 และเงินต้นเพียง $329.21 เงินต้นจะมากกว่าดอกเบี้ยครั้งแรกที่งวดที่ 176 ในปีที่ 15 ที่งวดที่ 180 สัดส่วนคือดอกเบี้ย $623.36 ต่อเงินต้น $643.36 และงวดสุดท้ายคือดอกเบี้ย $4.73 ต่อเงินต้น $1,261.98

เทคนิคดอกเบี้ยทบต้นขั้นสูง
มาดูสถานการณ์ที่ใช้เทคนิคขั้นสูง เช่น การจัดการงวดทบต้นไม่สม่ำเสมอ และอัตราดอกเบี้ยที่เปลี่ยนแปลง
อัตราดอกเบี้ยแบบผันแปร
ในความเป็นจริงมักมีอัตราดอกเบี้ยผันแปร เช่น สินเชื่อที่อยู่อาศัยอัตราลอยตัวหรือการลงทุนผูกกับตลาด Excel จัดการสถานการณ์ซับซ้อนได้ด้วยการตั้งค่าเพิ่ม
เราสามารถสร้างแบบจำลองที่ใช้อัตราดอกเบี้ยต่างกันในช่วงเวลาต่างกัน ตั้งค่าตารางอัตราดอกเบี้ยแบบผันแปร โดยสมมติว่าอัตราจะเพิ่มขึ้น 0.5% ทุก 5 ปี:
การตั้งค่าตารางสำหรับอัตราดอกเบี้ยที่เปลี่ยนแปลง ภาพโดยผู้เขียน
เพื่อคำนวณยอดปลายปีที่ 1 และยอดต้นปีที่ 2 ให้พิมพ์ดังนี้:
-
เซลล์ Q4: =P4*(1+O4)
-
เซลล์ P5: =Q4
การคำนวณอัตราดอกเบี้ยที่เปลี่ยนแปลง ภาพโดยผู้เขียน
ตอนนี้สามารถเลือกแถวของปีที่ 1–2 แล้วลากลงเพื่อขยายตารางไปยังจำนวนปีที่ต้องการ โดยปรับอัตราดอกเบี้ยตามต้องการ ดังภาพด้านล่าง:
เครื่องคำนวณอัตราดอกเบี้ยผันแปร ภาพโดยผู้เขียน
แบบจำลองนี้ช่วยให้เห็นภาพว่าอัตราดอกเบี้ยที่เปลี่ยนไปส่งผลต่อการลงทุนหรือเงินกู้ของคุณเมื่อเวลาผ่านไปอย่างไร
ถ้าต้องการเพียงยอดสุดท้าย Excel มีฟังก์ชันสำหรับเรื่องนี้: FVSCHEDULE(principal, schedule) จะใช้อัตราแต่ละตัวในช่วงที่กำหนดต่อเนื่องกัน จากตารางด้านบน:
=FVSCHEDULE(P4, O4:O9)ขับเคลื่อนโดย
ได้ $123,519.38 เท่ากับยอดปลายสุดของตาราง โดยไม่ต้องมีแถวปีต่อปี
ความถี่ในการทบต้น
ผลิตภัณฑ์การเงินจริงมักใช้ความถี่การทบต้นที่ไม่มาตรฐาน บางการลงทุนทบต้นรายไตรมาส บางรายเดือน บางรายวัน ความแตกต่างของความถี่นี้ส่งผลต่อผลตอบแทนอย่างมีนัยสำคัญเมื่อเวลาผ่านไป
ในการเปลี่ยนความถี่ทบต้น ให้เปลี่ยนค่า n ในสูตร: 1 สำหรับรายปี, 4 สำหรับรายไตรมาส, 12 สำหรับรายเดือน, 365 สำหรับรายวัน สำหรับ $10,000 ที่ 6% ทบต้นรายวันเป็นเวลา 5 ปี:
=10000*(1+0.06/365)^(365*5)ขับเคลื่อนโดย
ตัวอย่างเครื่องคำนวณการทบต้นแบบไม่สม่ำเสมอจะมีลักษณะดังนี้:
เครื่องคำนวณการทบต้นไม่สม่ำเสมอ ภาพโดยผู้เขียน
แต่ละแถวใช้สูตรเดียวกันโดยเปลี่ยนค่า n: =Principal*(1+Rate/n)^(n*Years) สำหรับแถวแบบกำหนดเอง n คือจำนวนงวดต่อปี ดังนั้นทุก 30 วันคือ 365/30
กำไรจะลดลงเมื่อทบต้นถี่ขึ้น ทบต้นรายปีได้ $13,382.26 รายไตรมาส $13,468.55 รายเดือน $13,488.50 และรายวัน $13,498.26 แม้แต่การทบต้นต่อเนื่องซึ่งเป็นขีดจำกัดทางคณิตศาสตร์ ก็ได้เพียง $13,498.59 (=10000*EXP(0.06*5))

เพื่อเปรียบเทียบผลิตภัณฑ์ที่ทบต้นต่างความถี่ ให้แปลงแต่ละอัตราเป็นอัตราผลตอบแทนรายปีที่แท้จริงด้วย EFFECT(nominal_rate, periods_per_year) =EFFECT(0.06, 12) ได้ 6.17% และ =EFFECT(0.06, 365) ได้ 6.18% ดังนั้น 6% ทบต้นรายวันดีกว่า 6% ทบต้นรายเดือนเพียงเล็กน้อย
ข้อคิดส่งท้าย
สำหรับจำนวนเงินก้อนเดียว ใช้ =P*(1+r/n)^(n*t) หรือ FV() เพื่อหามูลค่าในอนาคต เพิ่ม PMT() และ NPER() เพื่อวางแผนเงินออมและเงินกู้ ใช้ FVSCHEDULE() เมื่ออัตราเปลี่ยน และ EFFECT() เพื่อเปรียบเทียบความถี่การทบต้นอย่างเท่าเทียม
หากต้องการพัฒนาทักษะ Excel ให้ไกลกว่าบทความนี้ ลองลงทะเบียนเรียน Financial Modeling in Excel คอร์สที่จะต่อยอดแนวคิดพื้นฐานจากบทเรียนนี้ด้วยเทคนิคการสร้างแบบจำลองทางการเงินขั้นสูง
ในการคำนวณดอกเบี้ยทบต้นใน Excel ให้ใช้
=P*(1+r/n)^(n*t)หรือฟังก์ชันในตัวFV():=FV(r/n, n*t, 0, -P)สำหรับ $1,000 ที่อัตรา 5% ทบต้นรายเดือน เป็นเวลา 5 ปี ทั้งสองแบบจะได้ผลลัพธ์ $1,283.36ในคู่มือนี้จะแสดงการคำนวณทีละขั้น: สูตรคำนวณเอง,
FV()และPMT(), การคาดการณ์เงินออมเพื่อเกษียณ, ตารางตัดจำหน่ายเงินกู้, อัตราดอกเบี้ยผันแปร และผลกระทบจากความถี่ในการทบต้น สำหรับเทคนิคการสร้างแบบจำลองที่ครอบคลุมซึ่งต่อยอดจากเนื้อหานี้ ดูคอร์ส Financial Modeling in Excel ของเราสรุปสั้นๆ
=P*(1+r/n)^(n*t)FV()ให้ผลลัพธ์เดียวกันและยังรองรับเงินสมทบสม่ำเสมอPMT()คำนวณย้อนกลับจากเป้าหมายเพื่อหาเงินผ่อนชำระรายเดือนที่ต้องการEFFECT()เพื่อเปรียบเทียบอัตราที่มีความถี่การทบต้นต่างกันFVSCHEDULE()ใช้อัตราดอกเบี้ยที่ต่างกันแต่ละปีในสูตรเดียวดอกเบี้ยทบต้นคืออะไร?
ดอกเบี้ยทบต้นคือการเติบโตแบบเอ็กซ์โปเนนเชียลของเงินตามกาลเวลา แตกต่างจากดอกเบี้ยอย่างง่ายที่เติบโตเชิงเส้น ดอกเบี้ยทบต้นจะเร่งตัวขึ้นเมื่อดอกเบี้ยที่ได้รับในแต่ละงวดกลายเป็นส่วนหนึ่งของเงินต้นสำหรับงวดถัดไป
ความแตกต่างของอัตราเพียงเล็กน้อยจะยิ่งใหญ่เมื่อเวลาผ่านไป $10,000 ฝากไว้ 30 ปี ที่ 6% ทบต้นรายปี เติบโตเป็น $57,434.91 หาก 8% จะเติบโตเป็น $100,626.57 เกือบสองเท่า
ตัวอย่างความแตกต่างของ $10,000 ที่ 6% ต่อปี เป็นเวลา 5 ปี:
=10000*(1+0.06*5)=10000*(1+0.06)^5ส่วนเพิ่ม $382.26 คือดอกเบี้ยที่เกิดจากดอกเบี้ยก่อนหน้า และช่องว่างจะกว้างขึ้นทุกปีที่เงินยังคงถูกลงทุน
สูตรดอกเบี้ยทบต้น
ดอกเบี้ยทบต้นคำนวณด้วยสูตร:
สูตรดอกเบี้ยทบต้น ภาพโดยผู้เขียน
โดยที่:
เมื่อเข้าใจแนวคิดและสูตรคณิตศาสตร์แล้ว มาดูวิธีทำใน Excel กัน
วิธีคำนวณดอกเบี้ยทบต้นใน Excel
เพื่อใช้สูตรนี้ใน Excel ให้ตั้งค่าตารางง่ายๆ โดยใส่ป้ายกำกับชัดเจนสำหรับแต่ละตัวแปร
เริ่มจากจัดระเบียบสเปรดชีตด้วยป้ายกำกับต่อไปนี้:
จากนั้นป้อนค่าที่คอลัมน์ B:
เซลล์ B1: ใส่จำนวนเงินต้น (เช่น
1000)เซลล์ B2: ใส่อัตราดอกเบี้ยในรูปทศนิยม (เช่น
0.05สำหรับ5%)เซลล์ B3: ใส่จำนวนงวดทบต้นต่อปี (เช่น
12สำหรับรายเดือน)เซลล์ B4: ใส่จำนวนปี (เช่น
5)หลังตั้งค่าข้อมูลแล้ว แผ่นงาน Excel ควรมีลักษณะดังนี้:
การตั้งค่าตารางใน Excel ภาพโดยผู้เขียน
สุดท้าย ในเซลล์ B5 ใส่สูตรดอกเบี้ยทบต้น:
สูตรนี้นำสมการทางคณิตศาสตร์ด้านบนมาใช้โดยตรง เพื่อคำนวณยอดเงินสุดท้ายหลังทบต้น
การคำนวณดอกเบี้ยทบต้น ภาพโดยผู้เขียน
การใช้ฟังก์ชัน FV()
แม้สูตรคำนวณเองจะใช้ได้ดี แต่ Excel ก็มีฟังก์ชันการเงินในตัวที่ช่วยให้คำนวณดอกเบี้ยทบต้นง่ายขึ้น ฟังก์ชัน
FV()(Future Value) มีประโยชน์มากสำหรับคำนวณดอกเบี้ยทบต้นเมื่อมีการชำระเงินหรือลงทุนสม่ำเสมอไวยากรณ์ของฟังก์ชัน
FV()คือ:โดยที่:
rate= อัตราดอกเบี้ยต่องวดnper= จำนวนงวดทั้งหมดpmt= เงินที่จ่ายแต่ละงวด (ใส่ 0 หากไม่มี)pv= มูลค่าปัจจุบัน (เงินต้นตั้งต้น) Excel มองเงินที่จ่ายออกเป็นค่าติดลบ และเงินที่ได้รับเป็นค่าบวก ดังนั้นการใส่เงินต้นเป็นจำนวนลบจะทำให้ได้ผลลัพธ์เป็นบวกtype= เวลาที่ชำระเงิน (0 สำหรับสิ้นงวด, 1 สำหรับต้นงวด)มาปรับสเปรดชีตให้ใช้ฟังก์ชัน
FV()กัน:พิมพ์ดังนี้ในเซลล์ B6:
คำนวณดอกเบี้ยทบต้นด้วย FV() ภาพโดยผู้เขียน
สังเกตว่าเราแบ่งอัตราดอกเบี้ยรายปีด้วยจำนวนงวดทบต้นเพื่อให้ได้อัตราต่องวด และใส่เงินต้นเป็นค่าลบตามข้อกำหนดของฟังก์ชัน
จะเห็นผลการคำนวณดอกเบี้ยทบต้นดังนี้:
ดอกเบี้ยทบต้นด้วย FV() ภาพโดยผู้เขียน
หากต้องการเฉพาะดอกเบี้ยที่ได้รับ ไม่ใช่ยอดสุดท้าย ให้หักเงินต้นออก:
=FV(B2/B3, B3*B4, 0, -B1)-B1ได้ $283.36การใช้ฟังก์ชัน PMT()
ฟังก์ชัน
PMT()คำนวณค่างวดเงินกู้ บนพื้นฐานการผ่อนชำระและอัตราดอกเบี้ยคงที่ แม้ออกแบบหลักๆ เพื่อเงินกู้ แต่สามารถปรับใช้กับกรณีดอกเบี้ยทบต้นเพื่อหาจำนวนเงินสมทบสม่ำเสมอที่ต้องจ่ายเพื่อให้ถึงยอดเป้าหมายได้ไวยากรณ์ของฟังก์ชัน
PMT()คือ:โดยที่:
rate= อัตราดอกเบี้ยต่องวดnper= จำนวนงวดทั้งหมดpv= มูลค่าปัจจุบัน (เงินต้นตั้งต้น)fv= มูลค่าในอนาคต (ยอดเป้าหมายที่ต้องการถึง)type= เวลาที่ชำระเงิน (0 สำหรับสิ้นงวด, 1 สำหรับต้นงวด)มาลงในสเปรดชีตกัน:
เซลล์ A7: “Target Amount”
เซลล์ B7: ใส่ยอดเป้าหมาย (เช่น
5000)เซลล์ A8: “Required Monthly Payment”
พิมพ์ดังนี้ในเซลล์ B8:
สูตรนี้คำนวณจำนวนเงินที่ต้องสมทบรายเดือนเพื่อให้ถึงยอดเป้าหมาย โดยอิงจากเงินตั้งต้นและอัตราดอกเบี้ยของคุณ
จำนวนเงินผ่อนรายเดือนที่คำนวณได้จะแสดงดังนี้:
ดอกเบี้ยทบต้นด้วย PMT() ภาพโดยผู้เขียน
ฟังก์ชัน
PMT()ใน Excel จะคืนค่าเป็นลบโดยปริยาย เพราะมองจากมุมมองกระแสเงินสด:ดอกเบี้ยทบต้นใน Excel ในการใช้งานจริง
สูตรดอกเบี้ยทบต้นใน Excel ใช้ได้จริงหลากหลาย ทั้งการเงินส่วนบุคคลและธุรกิจ มาดูสถานการณ์ใช้งานที่เป็นรูปธรรมกัน
เงินออมและการลงทุน
การคาดการณ์การเติบโตของเงินออมหรือบัญชีลงทุนเป็นการใช้งานที่พบบ่อยของการคำนวณดอกเบี้ยทบต้น การเข้าใจว่าเงินเติบโตอย่างไรเมื่อเวลาผ่านไปช่วยให้ตัดสินใจได้ดีขึ้น
มาสร้างแบบจำลองการออมเพื่อเกษียณในส่วนใหม่ของสเปรดชีต หลังพิมพ์ “Retirement Savings Projection” ในเซลล์ D1 ให้ตั้งค่าพารามิเตอร์ในเซลล์ D2 ถึง D6:
เซลล์ D2: “Initial Investment” และ E2:
10000เซลล์ D3: “Monthly Contribution” และ E3:
500เซลล์ D4: “Annual Interest Rate” และ E4:
0.07เซลล์ D5: “Time Horizon (years)” และ E5:
30เซลล์ D6: “Compounding Frequency” และ E6: “Monthly”
หลังพิมพ์ “Projected Future Value” ในเซลล์ D7 ให้พิมพ์สมการนี้ใน E7:
สูตรนี้คาดการณ์เงินออมเพื่อเกษียณ โดยคำนึงถึงทั้งเงินตั้งต้นและเงินสมทบสม่ำเสมอ ดูมูลค่าในอนาคตได้ดังนี้:
มูลค่าในอนาคตที่คาดการณ์ด้วย FV() ภาพโดยผู้เขียน
สามารถต่อยอดแบบจำลองด้วยการสร้างตารางคาดการณ์แบบปีต่อปี:
ที่เซลล์ D9 ใส่ “Year-by-Year Projection”
ที่เซลล์ D10 ใส่ “Year” และที่เซลล์ E10 ใส่ “Balance”
ที่เซลล์ D11 ใส่
0(ปีเริ่มต้น)ที่เซลล์ E11 ใส่เงินตั้งต้น:
=E2ที่เซลล์ D12 ใส่
1ที่เซลล์ E12 คำนวณยอดคงเหลือหลังปีที่
1:Excel จะปรับการอ้างอิงเซลล์อัตโนมัติในแต่ละแถว สร้างลำดับการคำนวณที่แต่ละปีคำนวณต่อจากยอดปีที่แล้ว
ตารางคาดการณ์ปีต่อปี ภาพโดยผู้เขียน
ตารางแสดงให้เห็นว่าอัตราการเติบโตเร่งขึ้น: ยอดใช้เวลา 9 ปีจึงเกิน $90,000 แล้วเพิ่มขึ้นมากกว่าสองเท่าใน 10 ปีถัดมา
ใช้เวลานานเท่าใดจึงจะถึงเป้าหมาย
หากกลับคำถามว่าใช้เวลากี่ปีจึงจะถึงยอดเป้าหมาย ให้ใช้
NPER()ด้วยอินพุตเดิมและเป้าหมาย $100,000:ได้ผลประมาณ 114 เดือน หรือ 9.5 ปี แบ่งด้วย 12 เพื่อแปลงเป็นปี สำหรับเป้าหมายที่ไม่มีฟังก์ชันสำเร็จรูป Goal Seek ใช้หาอินพุตที่ทำให้ได้ผลลัพธ์ตามเป้า
การคำนวณเงินกู้
ดอกเบี้ยทบต้นเกี่ยวข้องกับเงินกู้เช่นกัน โดยดอกเบี้ยจะทบจากยอดคงเหลือ Excel มีฟังก์ชันช่วยทำความเข้าใจหลายด้านของเงินกู้ ตั้งแต่ค่างวดรายเดือนถึงดอกเบี้ยรวมที่จ่าย
เพื่อสร้างตารางตัดจำหน่ายเงินกู้ ให้ตั้งค่าพารามิเตอร์เงินกู้ดังตัวอย่าง:
ที่เซลล์ H5 พิมพ์ดังนี้เพื่อคำนวณค่างวดรายเดือน:
จะเห็นค่างวดรายเดือนดังนี้:
ค่างวดรายเดือนของเงินกู้ ภาพโดยผู้เขียน
จากนั้นตั้งค่าหัวตาราง ตารางตัดจำหน่าย ดังนี้:
ใส่ค่าเริ่มต้นสำหรับงวดแรก (แถว 8):
เซลล์ G8:
1(งวดที่หนึ่ง)เซลล์ H8:
=H2(ยอดเงินกู้เริ่มต้น)เซลล์ I8:
=ABS(H5)(จำนวนค่างวด ใช้ABS()แปลงผลลัพธ์ติดลบของPMT()ให้เป็นบวก)เซลล์ J8:
=H8*($H$3/12)(ส่วนดอกเบี้ย: ยอดยกมาต้นงวด × อัตรารายเดือน)เซลล์ K8:
=I8-J8(ส่วนเงินต้น: ค่างวด − ดอกเบี้ย)เซลล์ L8:
=H8-K8(ยอดปลายงวด: ยอดต้นงวด − เงินต้นที่ชำระ)สำหรับงวดที่สอง (แถว 9) ให้สร้างสูตรดังนี้:
เซลล์ G9:
=G8+1(ลำดับงวด)เซลล์ H9:
=L8(ยอดต้นงวดเท่ากับยอดปลายงวดก่อนหน้า)เซลล์ I9:
=I8(ค่างวดเท่าเดิม)เซลล์ J9:
=H9*($H$3/12)(ส่วนดอกเบี้ยคำนวณจากยอดต้นงวดใหม่)เซลล์ K9:
=I9-J9(ส่วนเงินต้น)เซลล์ L9:
=H9-K9(ยอดปลายงวด)จากนั้นเลือกเซลล์ G9 ถึง L9 แล้วลากลงไปถึงแถว 367 (ครบ 360 งวด)
ที่เซลล์ H6 สามารถคำนวณดอกเบี้ยรวมที่จ่ายได้:
ตารางสุดท้ายจะมีหน้าตาดังนี้:
ตารางแสดงให้เห็นว่ายอดหนี้ลดช้าในช่วงแรก ค่างวดแรก $1,266.71 มีดอกเบี้ย $937.50 และเงินต้นเพียง $329.21 เงินต้นจะมากกว่าดอกเบี้ยครั้งแรกที่งวดที่ 176 ในปีที่ 15 ที่งวดที่ 180 สัดส่วนคือดอกเบี้ย $623.36 ต่อเงินต้น $643.36 และงวดสุดท้ายคือดอกเบี้ย $4.73 ต่อเงินต้น $1,261.98
เทคนิคดอกเบี้ยทบต้นขั้นสูง
มาดูสถานการณ์ที่ใช้เทคนิคขั้นสูง เช่น การจัดการงวดทบต้นไม่สม่ำเสมอ และอัตราดอกเบี้ยที่เปลี่ยนแปลง
อัตราดอกเบี้ยแบบผันแปร
ในความเป็นจริงมักมีอัตราดอกเบี้ยผันแปร เช่น สินเชื่อที่อยู่อาศัยอัตราลอยตัวหรือการลงทุนผูกกับตลาด Excel จัดการสถานการณ์ซับซ้อนได้ด้วยการตั้งค่าเพิ่ม
เราสามารถสร้างแบบจำลองที่ใช้อัตราดอกเบี้ยต่างกันในช่วงเวลาต่างกัน ตั้งค่าตารางอัตราดอกเบี้ยแบบผันแปร โดยสมมติว่าอัตราจะเพิ่มขึ้น 0.5% ทุก 5 ปี:
เพื่อคำนวณยอดปลายปีที่ 1 และยอดต้นปีที่ 2 ให้พิมพ์ดังนี้:
เซลล์ Q4:
=P4*(1+O4)เซลล์ P5:
=Q4ตอนนี้สามารถเลือกแถวของปีที่ 1–2 แล้วลากลงเพื่อขยายตารางไปยังจำนวนปีที่ต้องการ โดยปรับอัตราดอกเบี้ยตามต้องการ ดังภาพด้านล่าง:
แบบจำลองนี้ช่วยให้เห็นภาพว่าอัตราดอกเบี้ยที่เปลี่ยนไปส่งผลต่อการลงทุนหรือเงินกู้ของคุณเมื่อเวลาผ่านไปอย่างไร
ถ้าต้องการเพียงยอดสุดท้าย Excel มีฟังก์ชันสำหรับเรื่องนี้:
FVSCHEDULE(principal, schedule)จะใช้อัตราแต่ละตัวในช่วงที่กำหนดต่อเนื่องกัน จากตารางด้านบน:ได้ $123,519.38 เท่ากับยอดปลายสุดของตาราง โดยไม่ต้องมีแถวปีต่อปี
ความถี่ในการทบต้น
ผลิตภัณฑ์การเงินจริงมักใช้ความถี่การทบต้นที่ไม่มาตรฐาน บางการลงทุนทบต้นรายไตรมาส บางรายเดือน บางรายวัน ความแตกต่างของความถี่นี้ส่งผลต่อผลตอบแทนอย่างมีนัยสำคัญเมื่อเวลาผ่านไป
ในการเปลี่ยนความถี่ทบต้น ให้เปลี่ยนค่า n ในสูตร: 1 สำหรับรายปี, 4 สำหรับรายไตรมาส, 12 สำหรับรายเดือน, 365 สำหรับรายวัน สำหรับ $10,000 ที่ 6% ทบต้นรายวันเป็นเวลา 5 ปี:
ตัวอย่างเครื่องคำนวณการทบต้นแบบไม่สม่ำเสมอจะมีลักษณะดังนี้:
แต่ละแถวใช้สูตรเดียวกันโดยเปลี่ยนค่า n:
=Principal*(1+Rate/n)^(n*Years)สำหรับแถวแบบกำหนดเอง n คือจำนวนงวดต่อปี ดังนั้นทุก 30 วันคือ365/30กำไรจะลดลงเมื่อทบต้นถี่ขึ้น ทบต้นรายปีได้ $13,382.26 รายไตรมาส $13,468.55 รายเดือน $13,488.50 และรายวัน $13,498.26 แม้แต่การทบต้นต่อเนื่องซึ่งเป็นขีดจำกัดทางคณิตศาสตร์ ก็ได้เพียง $13,498.59 (
=10000*EXP(0.06*5))เพื่อเปรียบเทียบผลิตภัณฑ์ที่ทบต้นต่างความถี่ ให้แปลงแต่ละอัตราเป็นอัตราผลตอบแทนรายปีที่แท้จริงด้วย
EFFECT(nominal_rate, periods_per_year)=EFFECT(0.06, 12)ได้ 6.17% และ=EFFECT(0.06, 365)ได้ 6.18% ดังนั้น 6% ทบต้นรายวันดีกว่า 6% ทบต้นรายเดือนเพียงเล็กน้อยข้อคิดส่งท้าย
สำหรับจำนวนเงินก้อนเดียว ใช้
=P*(1+r/n)^(n*t)หรือFV()เพื่อหามูลค่าในอนาคต เพิ่มPMT()และNPER()เพื่อวางแผนเงินออมและเงินกู้ ใช้FVSCHEDULE()เมื่ออัตราเปลี่ยน และEFFECT()เพื่อเปรียบเทียบความถี่การทบต้นอย่างเท่าเทียมหากต้องการพัฒนาทักษะ Excel ให้ไกลกว่าบทความนี้ ลองลงทะเบียนเรียน Financial Modeling in Excel คอร์สที่จะต่อยอดแนวคิดพื้นฐานจากบทเรียนนี้ด้วยเทคนิคการสร้างแบบจำลองทางการเงินขั้นสูง