I need to calculate the average interest rate for a calendar year, based on a table of exchange rates which change at various dates.

The table simply shows the start date and end date of the set interest rate, for a period of ten years. The interest rate may change as often as four times in a calendar year, and in some years not at all. In a ten year period there are 22 entries. I need to calculate the average interest rate of each of the ten calendar years in the period.

Example table of exchange rates:

So in the table above (`DD/MM/YYYY`

format), to reach the average interest rate for 2008, I calculate the following:

- 01 Jan 2008 to 07 Jul 2008 - Base Rate: 6.00% - Rate for Period 3.10%
- 08 Jul 2008 to 03 Nov 2008 - Base Rate: 6.25% - Rate for Period 2.04%
- 04 Nov 2008 to 31 Dec 2008 - Base Rate: 7.00% - Rate for Period 1.13%

Average Rate for the year 6.27%.

Is there a formula I could use to calculate this in Excel?

My biggest challenge so far has been to apply any formula to effectively "drag down" to produce the correct results for each calendar year.