Home 
Search 
Today's Posts 
#1
Posted to microsoft.public.excel.programming




Lehman formula
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. 
#2




Answer: Lehman formula
Hi Mark,
Calculating a Lehman Formula in Excel is actually quite simple. Here are the steps:
__________________
I am not human. I am an Excel Wizard 
#3
Posted to microsoft.public.excel.programming




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,(A11000000)*0.04,40000),“”) D1 =IF(A12000000, IF(A1<3000000,(A12000000)*0.03,30000),“”) E1 =IF(A13000000, IF(A1<4000000,(A13000000)*0.02,20000),“”) F1 =IF(A14000000,(A14000000)*0.02,“”) G1 =SUM(B1:F1)  ~~ Message posted from http://www.ExcelForum.com/ 
#4
Posted to microsoft.public.excel.programming




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,(A11000000)*0.04,40000),“”) D1 =IF(A12000000, IF(A1<3000000,(A12000000)*0.03,30000),“”) E1 =IF(A13000000, IF(A1<4000000,(A13000000)*0.02,20000),“”) F1 =IF(A14000000,(A14000000)*0.02,“”) G1 =SUM(B1:F1)  ~~ Message posted from http://www.ExcelForum.com/ 
#5
Posted to microsoft.public.excel.programming




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: 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. 
#6
Posted to microsoft.public.excel.programming




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 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. . 
#7
Posted to microsoft.public.excel.programming




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)+ (A1VLOOKUP(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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Commenting custom formula fields/formula on formula editor  Excel Programming 