Simple formula request

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

joetaxpayer - 10 May 2008 02:16 GMT

From http://www.socialsecurity.gov/OACT/COLA/piaformula.html
There is this paragraph;

For an individual who first becomes eligible for old-age insurance
benefits or disability insurance benefits in 2008, or who dies in 2008
before becoming eligible for benefits, his/her PIA will be the sum of:
(a) 90 percent of the first $711 of his/her average indexed monthly
earnings, plus
(b) 32 percent of his/her average indexed monthly earnings over
$711 and through $4,288, plus
(c) 15 percent of his/her average indexed monthly earnings over $4,288.

I would like to write a single line to take the income (shown in an
adjacent cell) and produce the benefit based on this rule. I'm sure it's
simple, but I am having a brain freeze. Would someone be so kind as to
help me?

(Please forgive the duplicate post to the Mac group, this is not Mac
related, pretty generic question)

Joe
www.blog.joetaxpayer.com

Reply to this Message

Ron Rosenfeld - 10 May 2008 02:41 GMT

> From http://www.socialsecurity.gov/OACT/COLA/piaformula.html
>There is this paragraph;
[quoted text clipped - 18 lines]
>Joe
>www.blog.joetaxpayer.com

Try this:

=VLOOKUP(A1,Tbl,2)+(A1-VLOOKUP(A1,Tbl,1))*VLOOKUP($A$1,Tbl,3)

where Tbl refers to a range containing:

0    0    90%
711    639.9    32%
4288    1784.54    15%

--ron

Reply to this Message

GB - 10 May 2008 12:54 GMT

>> From http://www.socialsecurity.gov/OACT/COLA/piaformula.html
>>There is this paragraph;
[quoted text clipped - 13 lines]
>>simple, but I am having a brain freeze. Would someone be so kind as to
>>help me?

For a one cell solution, how about:

A1: Average indexed monthly earnings

Use: 90%*A1 - MAX(58%*(A1-711),0) - MAX(17%*(A1-4288),0)

This takes 90% of the whole figure, then reduces it by 58% of the amount
over $711 (so that bit effectively gets 32%), and so on.

Reply to this Message

Ron Rosenfeld - 10 May 2008 18:12 GMT

>For a one cell solution, how about:
>
[quoted text clipped - 4 lines]
>This takes 90% of the whole figure, then reduces it by 58% of the amount
>over $711 (so that bit effectively gets 32%), and so on.

That works, but the lookup table may be easier to extend and/or modify.
--ron

Reply to this Message

joetaxpayer - 11 May 2008 04:20 GMT

>>>From http://www.socialsecurity.gov/OACT/COLA/piaformula.html
>>>There is this paragraph;
[quoted text clipped - 22 lines]
> This takes 90% of the whole figure, then reduces it by 58% of the amount
> over $711 (so that bit effectively gets 32%), and so on.

Got it. That worked perfectly. I understand the 17 and 58, but would not
have come up with that on my own. Very clever, and much appreciated.

(thanks, Ron as well, but I am a bit excel-dyslexic at times, unable to
get the table working)

Joe

Reply to this Message

Ron Rosenfeld - 11 May 2008 11:07 GMT

>(thanks, Ron as well, but I am a bit excel-dyslexic at times, unable to
>get the table working)

Not sure what you mean by "unable to get the table working"

The table solution is more easily adaptable. Let us say, for example, that you
wanted to add more lines; or change the percentages.

You enter the table in, for example, I1:K3

    I    J    K
1    0    0    90%
2    711    639.9    32%
3    4288    1784.54    15%

Of note, columns I and K are your given data.
Column J is the amount produced by the value in column I.

So the formula in J2: =J1+(I2-I1)*K1

and fill down as needed.

Then you use the equivalent formula:

=VLOOKUP(A1,$I$1:$K$3,2)+(A1-VLOOKUP(A1,$I$1:$K$3,1))*VLOOKUP($A$1,$I$1:$K$3,3)

(You can also select your Table, then Insert/Name/Define and NAME it, to use it
in the formula).
--ron

Reply to this Message

Rate this thread: