Thread: Lehman formula
View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.programming
Ron Rosenfeld Ron Rosenfeld is offline
external usenet poster
 
Posts: 5,651
Default Lehman formula

On Tue, 9 Dec 2003 11:25:13 -0500, "Dr. Mark W. Lee" wrote:

The Lehman formula fee is equal to:

5% of the first one million of value,
4% of the second million,
3% of the third million,
2% of the fourth million, and
1% of each million thereafter.


I would set up a table like a tax table.

With the value in A1, the formula would be:

=VLOOKUP(A1,{0,0;1000000,50000;2000000,90000;30000 00,120000;4000000,140000},2)+
(A1-VLOOKUP(A1,{0;1000000;2000000;3000000;4000000},1)) *
VLOOKUP(A1,{0,0,0.05;1000000,50000,0.04;2000000,90 000,0.03;3000000,120000,0.02;4000000,140000,0.01}, 3)


--ron