Lehman formula

  • Thread starter Thread starter Dr. Mark W. Lee
  • Start date Start date
D

Dr. Mark W. Lee

I am looking for a way to calculate a Lehman Formula in my spreadsheet.

Below is an example of what it is:

Thanks in advance,

Mark

Lehman Formula Fee


The finders' fee is the first check we write at the closing

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.
 
Try using a series of nested IF statements in hiden cells. Like this:


A1 = TOTAL

B1
=IF(A1<1000000,A1*0.05,50000)

C1
=IF(A1>1000000, IF(A1<2000000,(A1-1000000)*0.04,40000),“”)

D1
=IF(A1>2000000, IF(A1<3000000,(A1-2000000)*0.03,30000),“”)

E1
=IF(A1>3000000, IF(A1<4000000,(A1-3000000)*0.02,20000),“”)

F1
=IF(A1>4000000,(A1-4000000)*0.02,“”)

G1
=SUM(B1:F1)
 
Try using a series of nested IF statements in hiden cells. Like this:


A1 = TOTAL

B1
=IF(A1<1000000,A1*0.05,50000)

C1
=IF(A1>1000000, IF(A1<2000000,(A1-1000000)*0.04,40000),“”)

D1
=IF(A1>2000000, IF(A1<3000000,(A1-2000000)*0.03,30000),“”)

E1
=IF(A1>3000000, IF(A1<4000000,(A1-3000000)*0.02,20000),“”)

F1
=IF(A1>4000000,(A1-4000000)*0.02,“”)

G1
=SUM(B1:F1)
 
I believe the formula you are looking for is this:
"=IF(RC[-1]<=1000000,RC[-1]*0.05,IF(RC[-1]<=2000000,((RC[-
1]-1000000)*0.04)+50000,IF(RC[-1]<=3000000,((RC[-1]-
2000000)*0.03)+90000,IF(RC[-1]<=4000000,((RC[-1]-3000000)
*0.02)+120000,((RC[-1]-4000000)*0.01)+140000))))"
the formula assumes the cell containing the amount is in
the cell on the left of the formula. if you copy this
formula into a cell be sure to remove the " marks around
it.
Russ
 
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.
Click to expand...

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;3000000,120000;4000000,140000},2)+
(A1-VLOOKUP(A1,{0;1000000;2000000;3000000;4000000},1))*
VLOOKUP(A1,{0,0,0.05;1000000,50000,0.04;2000000,90000,0.03;3000000,120000,0.02;4000000,140000,0.01},3)


--ron
 
Back
Top