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.

Hi Mark,

Calculating a Lehman Formula in Excel is actually quite simple. Here are the steps:

1. Determine the value of the transaction or asset you are calculating the fee for.
2. Use the IF function to calculate the fee based on the value of the transaction. Here's an example formula:
   
   
   Formula:

   =IF(A1<=1000000,A1*0.05,IF(A1<=2000000,50000+(A1-1000000)*0.04,IF(A1<=3000000,90000+(A1-2000000)*0.03,IF(A1<=4000000,120000+(A1-3000000)*0.02,160000+(A1-4000000)*0.01)))) 

   In this formula, A1 is the cell containing the value of the transaction. The formula checks the value of the transaction and applies the appropriate percentage rate based on the Lehman Formula.
   
3. The result of the formula will be the Lehman Formula fee.

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(A11000000, IF(A1<2000000,(A1-1000000)*0.04,40000),“”)

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

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

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

G1
=SUM(B1:F1)


---
~~ Message posted from http://www.ExcelForum.com/

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(A11000000, IF(A1<2000000,(A1-1000000)*0.04,40000),“”)

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

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

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

G1
=SUM(B1:F1)


---
~~ Message posted from http://www.ExcelForum.com/

Take a look he

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

You'll want something like:

=SUMPRODUCT(--(A1{0;1000000;2000000;3000000;4000000}),
(A1-{0;1000000;2000000;3000000;4000000}),
{0.05;-0.01;-0.01;-0.01;-0.01})

This assumes a continuous distribution.


In article ,
"Dr. Mark W. Lee" wrote:

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.

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




.

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