SOLVED

need help on conditional if formulas

Occasional Contributor

Trying to insert formulas for spreadsheet below

 

 RATE 1st $100,000RATE next $400,000RATE next $2,000,000RATE next $2,500,000RATE next $2,500,000RATE over $7,500,000
RATE2.500%1.500%1.000%0.075%0.070%0.069%
 $100,000$400,000$2,000,000$2,500,000$2,500,000 
CONTRACT AMOUNT      
$8,000,000.00$2,500.00     
11 Replies
What's your goal with this spreadsheet?

@rchelpforrc 

See the attached demo.

to calculate bond rates, for each column - need correct formula(s) to enter. Thanks
is there a way to enter the formula in each column, so i can add a "total" at the end?

@rchelpforrc 

Yes, if you wish:

I appreciate your help greatly. the key is items in row 1 as "next" for each column?
Shouldn't e5 be 18,750?
Shouldn't f5 be 17,500?
Shouldn't g5 be .069% of anything over 7.5 million?

@rchelpforrc 

0.075% of $2,500,000 is $1,875 and not $18,750.

0.070% of $2,500,000 is $1,750 and not $17,500.

The remaining amount over $7,500,000 is $500,000.

0.069% of $500,000 is $345.

Perhaps the percentages should have been 0.75%, 0.70% and 0.69%? The jump from 1.00% to 0.075% is very large...

 

Thanks much, i believe you are correct.
I'll try entering that way, and see if it works.
it all seems to work, except if A5 is 0, i get negative numbers in all columns, how can i fix that?
best response confirmed by rchelpforrc (Occasional Contributor)
Solution

@rchelpforrc 

See the attached version.

Hans, thanks much, you have solved my problem(s).