ถามคนเก่งๆ สูตรexcel หน่อยครับ เกี่ยวกับเรื่องดอกเบี้ยทบต้น

[romee]

ถ้าอยากรู้ว่า หุ้น xxx 5ปีผ่านไปมันเพิ่มขึ้นไป 5เท่า ถ้าคิดว่าได้ผลตอบแทนทบต้น กี่% ต้องใช้สูตรใหน ยังไงบ้างอ่ะครับ

ขอบคุณครับ

[birthboro]

ผมใช้เครื่องคิดเลขทางการเงินกดครับ ลงฝึกใช้ดูช่วยได้มากนะครับ

[ครรชิต ไพศาล]

เงินปลาย = เงินต้น*(1+อัตราดอกเบี้ยต่อปี)^ปี

5 =1*(1+x)^5
5=(1+x)^5
x =รากที่5ของ5 - 1

ถ้าอัตราดอกเบี้ยปีละ 37.97298 %
x = 0.3797298
เอามาแทนค่าในสมการ (1+x)^5 = 5.00000251

ค่าที่ละเอียดถูกต้อง จาก Microsoft Math

[Ii'8N]

ผมก็ใช้วิธีเดียวกับพี่ครรชิต

แต่ปัญหา คือต้องทำกลับกัน เวลาเราต้องการรู้อัตรา
เราได้ตัวเลขตั้งต้นมาของหุ้นที่จะซื้อ หรือการใส่เงินเมื่อเริ่มลงทุน แล้ว forecast ปลายทางมา แต่อยากรู้อัตราทบต้น (มากกว่าที่จะคิดการเพิ่มเป็น Net Asset Value ที่จับหารตรงๆ)

เคยพยายามไปหาสูตรรากที่ n ใน excel แต่ยังหาไม่เจอ (อาจมีก็ได้ แต่ยังไม่รู้หาไม่ได้จริงๆ)

หาวิธีอยู่นาน ...เลยไปเช็คกับความรู้ในหนังสือเก่าๆ ที่คืนอาจารย์คณิตศาสตร์ไปนานแล้ว ได้ความว่า รากที่ n ก็คือ ยกกำลัง 1/n นั่นเอง


ใน excel สูตรยกกำลังทำได้ 2 อย่าง คือใช้เครื่องหมาย ^ เข้าไปตรงๆ
= ฐาน^ยกกำลัง
หรือจะใช้คำว่า power
= power (ฐาน, ยกกำลัง)


เช่น
2 ยกกำลัง 4 ----> =2^4 หรือ = power (2,4)
รากที 2 ของ 4 ----> =4^(1/2) หรือ = power (4,(1/2))

สูตรที่พี่ครรชิตบอก
เงินปลาย = เงินต้น*(1+อัตราดอกเบี้ยต่อปี)^ปี
คิดกลับทาง
อัตราดอกเบี้ยต่อปี = {รากจำนวนปี ของ (เงินปลาย/เงินต้น)} - 1

r = {root Y (FV/PV)} -1
หรือ r = {(FV/PV)ูู^(1/Y) } -1
r = compound rate
Y = Year period
FV = Future value
PV = Present value


กรณีตัวอย่าง ใน buffetology บอกว่าปู่บัฟเฟเตต์
1. ซื้อหุ้น Washington post
ที่ EPS $6.14 ----27ปีผ่านไป-----EPS $500

r
= ( (500/6.17) ^ (1/27) ) -1
หรือ
= ( power ((500/6.17),(1/27)) ) -1

เช็คกับคำตอบในหนังสือ บอกว่า 17.8% ซึ่งใกล้เคียงกัน เพราะตัวเลข $500 บอกเป็นค่าประมาณ ซึ่งความเป็นจริงก็สวิงไปมาตามอารมณ์นายตลาด

2. ซื้อหุ้น Washington post
ที่ EPS $5.22 ----12ปีผ่านไป-----EPS $50


เพื่อการเรียนรู้ คุณควรลองทำเหมือนสูตรข้างต้นเองใน excel
ค่าที่ได้ออกมา ในหนังสือบอกราว 21%
ผมคำนวณได้ 20.72%

[Ii'8N]

แก้ครับ กลับมาดู เพิ่งเห็น

2. ซื้อหุ้น Coca-cola

[murder_doll]

พิมใน EXCELว่า
=RATE(จำนวนปี,,เงินต้นงวด,-เงินปลายงวด)
=RATE(5,,1,-5) จะได้38%ทบต้นต่อปีครับ

[Ii'8N]

ขอบคุณครับ

Excel ช่วยร่นให้สั้นลงได้เยอะเลย ...อย่่าง IRR เหมือนกัน เมื่อก่อนต้องมาตีตาราง แล้ว guesstimate trail&error แต่พิมใน Excel ตัวเลขออกมาปลายทางเลย...ยังมีสูตรสำเร็จ Financial ดีๆ อีกเยอะ ที่ผมยังใช้ไม่เป็น กำลังเรียนรู้อยู่

[romee]

ขอบคุณครับ

Excel ช่วยร่นให้สั้นลงได้เยอะเลย ...อย่่าง IRR เหมือนกัน เมื่อก่อนต้องมาตีตาราง แล้ว guesstimate trail&error แต่พิมใน Excel ตัวเลขออกมาปลายทางเลย...ยังมีสูตรสำเร็จ Financial ดีๆ อีกเยอะ ที่ผมยังใช้ไม่เป็น กำลังเรียนรู้อยู่

รบกวนสอนวิธีใช้ IRR กับ xirr ใน excel ให้ทีสิครับ ผมก็งงๆเหมือนกัน

พิมใน EXCELว่า
=RATE(จำนวนปี,,เงินต้นงวด,-เงินปลายงวด)
=RATE(5,,1,-5) จะได้38%ทบต้นต่อปีครับ

ขอบคุณครับ ช่วยได้มากเลย

[blueplanet]

ใช้ XIRR
ซึ่งต้องระบุข้อมูล 2 ชุด
คือ วันที่ เริ่มต้น 1/1/2000 เงินที่ใส่ (มีค่าเป็นลบ) คือ -100
และวันที่ได้รับเงิน 1/1/2005 เงินที่ได้รับ(มีค่าเป็นบวก) คือ 500

[Ii'8N]

ผม assume ว่าคุณรู้จักดีแล้วว่า IRR คืออะไร

สูตรก็ง่ายๆ
= IRR(Arrays ปีที่ 1 ถึงปีที่ N ที่ต้องการคำนวณ)
เช่น
=IRR(A9:A15) ข้อมูลแนวตั้ง
=IRR(F10:L10) ข้อมูลแนวนอน

IRR มักใช้ในการ forecast การลงทุนทำธุรกิจอย่างกว้างๆ หรือคำนวณผลตอบแทนของกรมธรรม์ฯ ที่เงินเข้าออกสมำ่เสมอ
เงื่อนไขการใช้ของ function นี้ คือ
- ระยะห่างของการลงทุนและการรับผลตอบแทน นับเป็นปีละครั้ง
- สิ่งที่เป็นการลงทุน initial outlay ต้องจ่้ายลงทุน หรือว่ามีขาดทุนที่ต้องหักเงินออกออกจากกระเป๋า ใส่ตัวเลขติดลบ
- กำไรที่จ่ายให้มากระเป๋าเราใส่ตัวเลขธรรมดา (บวก)

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

- ถ้าปีเดียวกัน มีทั้ง "ได้และจ่าย" เราจ่ายลงทุน/ส่งเบี้ย และเรารับเงินปันผล/ได้กำไรจากธุรกิจ/เงินคืน เอาตัวเลขมาหักลบกันก่อน



...........ซึ่งก็เหมือนการคำนวณ IRR ตอนที่เรามาจับคำนวณ ก่อนที่จะจับไปคูณ discount rate แต่ละปี cash outflow ติดลบ, cash inflow เป็นบวก

- ถ้าปีมากๆ บางทีคำนวณไม่ออก ให้ใส่ตัวเลข guess อะไรก็ได้ (xx%) แม้จะผิด excel จะทำให้ออกมาถูกเอง
เช่น
=IRR(A9:A15,15%) ข้อมูลแนวตั้ง
=IRR(F10:L10,0.51) ข้อมูลแนวนอน





ส่วน XIRR เป็นการคำนวณที่การนำเงินเข้าไปไม่สม่ำเสมอ ไม่ว่าจะใส่้เงินลงทุน หรือได้กำไร
แต่การทีไม่สมำ่เสมอ จึงคำนวณโดยเอา cashflow มาสัมพันธ์กับจำนวนวัน ถ้าไม่เต็มปี ก็จะเฉลี่ยให้เปรียบเสมือน 365 วัน

สูตรมันจึงประกอบด้วยตัวเลข 2 ชุด อย่างคุณ blueplanet ว่า

= XIRR (Array เงินเข้าหรือออก, Array วันที่, guess value)
เช่น
=IRR(A9:A15,B9:B1515%) ข้อมูลแนวตั้ง
=IRR(F10:L10,F11:L11,0.51) ข้อมูลแนวนอน


หลักการ เงินออก "ค่าลบ" เงินเข้า "ค่าบวก" เหมือนเดิม

XIRR ผมก็เพิ่งมาเรียนรู้ใน ThaiVI นี่แหละครับ
ก่อนหน้า่นี้ ผมทำเองด้วยการคำนวณวันที่ ว่าผ่านไปแล้วกี่วันของปี แล้วจับนำมันมาเฉลี่ย
พอมี XIRR สั้นนิดเดียวออกมาเลย

ผมใช้วิธี manual ในการคำนวณ IRR port ของผม เพราะผมเป็นมนุษย์เงินเดือน port ผมเติมเงินลงไปทุกเดือน แล้วได้โบนัสกับปันผลมา ผมก็เอามาลงหมด ดังนั้นใน 1 ปี ยังไม่นับครั้งที่มีการปรับพอร์ต ผมจึงซื้อไม่ต่ำกว่า 12 ครั้งแน่นอน
รวมถึงหุ้นแต่ละตัว ถ้าผมซื้อหลายครั้ง ผมจะแยกกันคำนวณ เพราะผมถือว่าการลงทุนแต่ละครั้ง ไม่ใช่การเฉลี่ยต้นทุน
ในความเป็นจริงถ้าเราขายแบบแบ่งไม่ได้ขายทิ้งครั้งเดียว broker ก็จะเอาคำนวณต้นทุนส่วนที่ซื้อก่อนออกมาขายก่อน (FIFO) ไม่ใช่ต้นทุน average



ลองเข้าไปอ่านที่เขาถกกันไว้ดูเพิ่มนะครับ
จะเข้าใจยิ่งขึ้น ลอง copy จาก help หรือ เอาไฟล์ที่หลายๆ คนทำมา แล้วลองเล่นดูแก้ตัวเลขไปมาเอง ไม่ต้องจำอะไรเลย


http://board.thaivi.org/viewtopic.php?f=1&t=45776

ตารางคำนวณผลตอบแทน ครับ
http://board.thaivi.org/download/file.php?id=4911

http://board.thaivi.org/viewtopic.php?t=44521



ที่ผมทำไว้ ตัวอย่างสมมติ

IRR Calculation.zip

(27.26 KiB) ดาวน์โหลด 148 ครั้ง

ถ้าออกไปไหนมาไหน ไม่มีตำรา แล้วยังไม่คล่อง
มีวิธีลัดในการเขียนสูตรหรือเรียนรู้ด้วยตัวเองครับ
เข้าไปใน help ของ function ที่เราต้องการเรียนรู้ ผ่าน F1 หรือระหว่าง Insert Function จะมี Link คำว่า Help on this function
เข้าไปดู แล้ว copy ตัวอย่างมาลง cell A1 ของ worksheet ว่างๆ
เราได้ตัวอย่างของจริงมาใช้หรือทดลองได้ทันทีเลย

[Ii'8N]

...

[romee]

ขอบคุณ คุณ Ii'8N มากๆครับ