Excel 2003 "IF" formula (I think!!)

  • Thread starter Thread starter calquestions
  • Start date Start date
C

calquestions

hello Fellow Excel users,
I wanted to use a formula to calculate one rate at certain percentageof
return and another rate to calculate if greater than that return. For
example: assuming that you have a return on an investment of 25% during
a given year. I am trying to automatically calculate a split between
two different parties at different level of return such as having one
party get the first 10% of the return then an 80/20 split for the
portion between 10%-13% and 50/50 split between the portion between
13%-20% and back to 80/20 split between the portion greater than 20%
return. Can this be done in Excel and if so would I use the "IF"
statement? Any suggestions would be greatly appreciated.
 
With the percent return in cell A1 (entered as a percent), then this formula may work (may because
you weren't explicit about which party gets the 80 and which gets the 20).

=IF(A1>=0.1,0.1,A1)+IF(A1>=0.13,0.024,MAX(0.8*(A1-0.1),0))+IF(A1>=0.2,0.035,MAX(0.5*(A1-0.13),0))+MAX(0.8*(A1-0.2),0)

Format the cell with the formula as a percent.

HTH,
Bernie
MS Excel MVP
 
Back
Top