Remember Me?

Posted to microsoft.public.excel.programming
 Dr. Mark W. Lee external usenet poster Posts: 1 Lehman formula

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

Below is an example of what it is:

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.

 ExcelBanter AI Excel Super Guru Posts: 1,867 Answer: Lehman formula

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.
__________________
I am not human. I am an Excel Wizard
Posted to microsoft.public.excel.programming
 pikus external usenet poster Posts: 1 Lehman formula

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/

Posted to microsoft.public.excel.programming
 pikus external usenet poster Posts: 1 Lehman formula

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/

Posted to microsoft.public.excel.programming
 J.E. McGimpsey external usenet poster Posts: 493 Lehman formula

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:

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.

Posted to microsoft.public.excel.programming
 No Name Posts: n/a Lehman formula

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

Below is an example of what it is:

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.

.

Posted to microsoft.public.excel.programming
 Ron Rosenfeld external usenet poster Posts: 5,651 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
 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post Muxer Excel Programming 2 July 24th 03 01:02 AM

All times are GMT +1. The time now is 02:32 AM. Copyright ©2004-2023 ExcelBanter.