Simple formula request

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

Ron
Fri May 09 20:41:10 CDT 2008

On Fri, 09 May 2008 21:16:42 -0400, joetaxpayer <joetaxpayer@nospam.com> wrote:

> 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

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

GB
Sat May 10 06:54:10 CDT 2008


"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:18v924943edm3ok8aiugaad4msi2ks50eg@4ax.com...
> On Fri, 09 May 2008 21:16:42 -0400, joetaxpayer <joetaxpayer@nospam.com>
> wrote:
>
>> 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?

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.

Ron
Sat May 10 12:12:28 CDT 2008

On Sat, 10 May 2008 12:54:10 +0100, "GB" <NOTsomeone@microsoft.com> wrote:

>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.
>
>
>

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

joetaxpayer
Sat May 10 22:20:06 CDT 2008



GB wrote:

> "Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
> news:18v924943edm3ok8aiugaad4msi2ks50eg@4ax.com...
>
>>On Fri, 09 May 2008 21:16:42 -0400, joetaxpayer <joetaxpayer@nospam.com>
>>wrote:
>>
>>
>>>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?
>
>
> 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.

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

Ron
Sun May 11 05:07:39 CDT 2008

On Sat, 10 May 2008 23:20:15 -0400, joetaxpayer <joetaxpayer@nospam.com> wrote:

>(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