Hi

Excel version 2010

I would like to be able to do cumulative addition on some transaction fee's and make the formula as automated as possible.

Example;

My total transaction size is 36,000,000,000

I will be charged on a cumulative basis at the following rate;

Monthly Vol ($Bill) Base Fee ($) Per Million Notional
0 - 1 $6.00
1+ - 3 $5.40
3+ - 10 $4.80
10+ - 20 $4.20
20+ - 35 $3.60
35+ $3.00

Please note that the monthly volume is measured in billions and the base fee is applied per million. So for the first 1 billion the charge would be $6000.

My total fee should be $149,400

Sorry, I can't upload spreadsheets from work.

Any help with a formula for this would be great, not sure if there is anything I could do with a lookup table which can add cumulatively?