Driving me nuts trying to figure this formula

[Scott - Key West]

FEMA gives my city a percent of disaster grants for administrative costs,
based upon a tiered system related to the amount of the awards, and I've been
trying to figure a formula to determine these amounts.

Problem: The administrative costs awards are as follows:

3% on the first $100,000
2% of the next $900,000
1% of the next $4,000,000
..5% of everything in excess of $5,000,000

A figure of $6,000,000 ought to invoke all these percentages.

Any ideas

 

[FSt1]

hi
based on what you said and your chart, this is how i interpeted......
=SUM(0.02*100000)+(0.02*900000)+(0.01*400000)+(0.005*5000000)

not sure but maybe it will give you ideas
Regards
FSt1

 

[Scott - Key West]

I don't think that will do it. The formula needs to work for awards from
anywhere to $3,000 to $6m.

Lets say we have a $150,000 award. The formula would need calculate 3% on
the first $100,00, and an additional 2% on the remaining $50,000. The two
calculations combined would tell me what FEMA would be sending us for
administrative costs.

Lest say we have a $2.5m award. The formula would need calculate
3% on the first $100,00,
an additional 2% on the next $900k (between these first two steps, that
covers the first million), then,
an additional 1% on the next $4m (all three of these steps would cover the
first $5m), then,
an additional 0.5% on anything in excess of $5m

Hope this is clearer than mud.

 

[Tyro]

Try this formula to get the general logic. Then you can simplify it

=IF(A1>5000000,(A1-5000000)*0.5%+4000000*1%+900000*2%+100000*3%,IF(A1>1000000,(A1-1000000)*1%+900000*2%+100000*3%,IF(A1>100000,(A1-100000)*2%+100000*3%,A1*3%)))

Tyro

 

[T. Valko]

See this:

http://www.mcgimpsey.com/excel/variablerate.html

 

[krcowen]

Scott

=IF(C10<100000,C10*0.03,IF(C10<1000000,3000+0.02*(C10-100000),IF(C10<5000000,3000+18000+
(C10-1000000)*0.01,3000+18000+40000+0.05*(C10-5000000))))

Good luck.

Ken
Norfolk, Va.

 

[David Biddulph]

Lose the SUM function. It's doing nothing in that equation.

 

[Ron Rosenfeld]

On Tue, 15 Jan 2008 12:32:02 -0800, Scott - Key West <Scott - Key

FEMA gives my city a percent of disaster grants for administrative costs,
based upon a tiered system related to the amount of the awards, and I've been
trying to figure a formula to determine these amounts.

Problem: The administrative costs awards are as follows:

3% on the first $100,000
2% of the next $900,000
1% of the next $4,000,000
.5% of everything in excess of $5,000,000

A figure of $6,000,000 ought to invoke all these percentages.

Any ideas

I think you have stated the problem in a manner which makes applying it
somewhat difficult. But the process can be set up like a tax table:


For amts up to Pay this plus percentage over
$- $0 3%
$100,000 $3,000 2%
$1,000,000 $21,000 1%
$5,000,000 $61,000 0.50%

So you can set up the table someplace on your worksheet, NAME it Tbl, and use
the formula:

=VLOOKUP(GrantAmt,Tbl,2)+((GrantAmt-VLOOKUP(GrantAmt,Tbl,1))*VLOOKUP(GrantAmt,Tbl,3))

If the percentages or brackets change, you can easily edit the table.

--ron

 

[Scott - Key West]

Excellent! Tyro & Ken essentially came up with variants of the same formula
that worked marvelously.

This question really exceeded my gray matter capabilities. It would have
taken me at least a few long hot soaks in the tub to come up with what you
guys were able to jot down in a couple of minutes. I thought I had a rather
logical mind, but you guys must be pros.

Thanks again,

Scott
Key West

 

[ilia]

Looks kinda ugly, but

=(0.03*(MIN(A2,100000)))+(0.02*(MIN(900000,A2-(MIN(A2,100000)))))
+(0.01*(MIN(4000000,A2-(MIN(A2,100000))-(MIN(900000,A2-
(MIN(A2,100000)))))))+(0.005*(A2-(MIN(A2,100000))-(MIN(900000,A2-
(MIN(A2,100000))))-(MIN(4000000,A2-(MIN(A2,100000))-(MIN(900000,A2-
(MIN(A2,100000))))))))