The solution I would like to achieve is as follows:

1. I enter a start date in a cell (say A1)
2. I enter an end date in a cell (say A2)
3. I enter a sum of money in a cell (say A3)
4. Excel calculates the interest on the sum of money (A3) for the period between the dates in cells A1 and A2 compounding the interest at dates when the interest rate changes.

The interest rate information is based on the Bank of Engalnd Base Rate information as follows:

Date Rate
03/08/06 4.75%
09/11/06 5.00%
11/01/07 5.25%
10/05/07 5.50%
05/07/07 5.75%
06/12/07 5.50%
07/02/08 5.25%
10/04/08 5.00%
08/10/08 4.50%
06/11/08 3.00%
04/12/08 2.00%
08/01/09 1.50%
05/02/09 1.00%
05/03/09 0.50%

So, by way of an example:

If I have a start date of 01/01/09 and an end date of 10/02/09 and a sum of £1,000; Excel would:

1. recognise that 01/01/09 (start) falls between 04/12/08 and 08/01/09 and calculate interest on the £1,000 for the period 01/01/09 (start) to 07/01/09 at 2%, calculate interest on the sum from 08/01/09 to 05/02/09 at 1.5% and calculate interest from 06/02/09 to 10/02/09 (end) at 1%.


Whether there is a single formula or the answer is linked to another sheet where the calculation takes place I don't mind. For instance, the calculation could take place line by line against the dates / rates above but how would I get the start and finish date to 'slot' into the above list.

Any help appreciated.