Author Topic: Shamelessly requesting assistance with math + Excel  (Read 2521 times)

nikki

  • Pencil Stache
  • ****
  • Posts: 645
  • Age: 33
  • Location: South Korea
Shamelessly requesting assistance with math + Excel
« on: June 25, 2014, 05:43:49 AM »
I'm not great at Excel. Really not.

But I wonder if it's possible to make a spreadsheet that calculates my pension? The formula is... ugly:

(standard monthly income × the number of service period
months/12 × 975/1,000) + {standard monthly income × the
number of service period months/12 ×(the period of one’s
service - 60)/12 × 65/10,000}

EDIT: I should also add that the standard monthly income changes for most teachers each year (up to maybe the 10 year mark?), as most universities do give a raise with each new contract. Also if people switch universities, their standard monthly income might change, but their time in the pension is still going. This is the part where my capabilities completely break down. No clue if this is even spreadsheet-able.

From http://www.tp.or.kr:8088/hp/upload/fixform/english.pdf

If someone could explain how to set up a spreadsheet that could calculate the current amount in a pension, it would benefit me and thousands of teachers in Korea. This is actually the first time I've even seen the formula! There isn't much communication about the pension to English speaking teachers :-/

Help!

Sincerely,
Incompetent
« Last Edit: June 25, 2014, 05:56:29 AM by nikki »

matchewed

  • Magnum Stache
  • ******
  • Posts: 4334
  • Location: CT
Re: Shamelessly requesting assistance with math + Excel
« Reply #1 on: June 25, 2014, 05:51:32 AM »
Try running this - =((F2*G2/12)*975/1000)+((F2*G2/12)*(H2-60)/12*65/10000)

F2 = Standard Monthly Income
G2 = The number of service period months
H2 = The period of one's service

I'm not awesome at excel either but give it a shot and see if it matches up.

nikki

  • Pencil Stache
  • ****
  • Posts: 645
  • Age: 33
  • Location: South Korea
Re: Shamelessly requesting assistance with math + Excel
« Reply #2 on: June 25, 2014, 06:00:17 AM »
Try running this - =((F2*G2/12)*975/1000)+((F2*G2/12)*(H2-60)/12*65/10000)

F2 = Standard Monthly Income
G2 = The number of service period months
H2 = The period of one's service

I'm not awesome at excel either but give it a shot and see if it matches up.

Wow quick! Saving this to verify tomorrow. Apparently I can register on their website and find out my current amount, but I'll need a Korean speaker's assistance to set it up. I hope my office's assistant won't mind helping bahaha!

Ozstache

  • Pencil Stache
  • ****
  • Posts: 839
  • Age: 51
  • Location: Oztralia
Re: Shamelessly requesting assistance with math + Excel
« Reply #3 on: June 25, 2014, 06:15:54 AM »
But I wonder if it's possible to make a spreadsheet that calculates my pension? The formula is... ugly:

(standard monthly income × the number of service period
months/12 × 975/1,000) + {standard monthly income × the
number of service period months/12 ×(the period of one’s
service - 60)/12 × 65/10,000}


Do you realise that, per the pdf document you linked, this is the formula for a lump-sum retirement grant for service between five and 20 years and not for the pension, which is paid after 20 years service?

nikki

  • Pencil Stache
  • ****
  • Posts: 645
  • Age: 33
  • Location: South Korea
Re: Shamelessly requesting assistance with math + Excel
« Reply #4 on: June 25, 2014, 07:22:23 AM »
But I wonder if it's possible to make a spreadsheet that calculates my pension? The formula is... ugly:

(standard monthly income × the number of service period
months/12 × 975/1,000) + {standard monthly income × the
number of service period months/12 ×(the period of one’s
service - 60)/12 × 65/10,000}


Do you realise that, per the pdf document you linked, this is the formula for a lump-sum retirement grant for service between five and 20 years and not for the pension, which is paid after 20 years service?

Right--sorry for the term confusion! Most expats just call it "pension" but intend to take the lump-sum.   

YoungInvestor

  • Bristles
  • ***
  • Posts: 397
Re: Shamelessly requesting assistance with math + Excel
« Reply #5 on: June 25, 2014, 07:36:17 AM »
Do you just want the lump sum?

B1 = Standard Monthly salary
B2 = Number of service period months
B3 = Period of one's service

I don't quite get what the difference between B2 and B3 is, and I used B3 for the 5 years criterion mentionned in the PDF, but anyway, you could change the specifics). This is intended as a formula template more than a definitive formula and you should make sure that you understand it well and are sure that it gives you the right results before implementing it.

=IF(B3<60,B1*B2/12*78/100,(B1*B2/12*975/1000)+(B1*B2/12*(B3-60)/12*65/10000))