1

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:

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.

Improve this question
3
  • If I understand correctly, this could involves multiple formulas along with the growth rates formula. Take a look at this link it might be what you seek. igetit.net/newsletters/y06_08/calculategrowth.aspx
    – iSR5
    Mar 29, 2016 at 4:42
  • Thanks. The challenge I'm facing is related more to separating the variable date inputs into a fixed calendar input. In it's simplest form, if there were two inputs: a 4% rate from 1 July 2014 to 30 June 2015 and a 6% rate from 1 July 2015 to 30 June 2016, how we would we calculate the average formula for the fixed calendar year period 1 January 2015 to 31 December 2015.
    – lrmoro
    Mar 29, 2016 at 4:56
  • if you have a fixed calendar input, you can use VLookup function to search for the date of debt that falls between two periods and get the rate of that period and apply it along with using some IF function as necessary.
    – iSR5
    Mar 29, 2016 at 6:09

1 Answer 1

Reset to default
1

I produced an answer which essentially does the trick.

Thought I'd post it here in case this comes up for someone else.

Example Solution Table Short

I've added a new column for each year, and above each column, entered the start date and end date of the year (in this case starting 1 August and ending 31 July the following year).

From there I've used a variant of the following formula in each cell:

D5 =MAX(MIN($B5,D$2)-MAX($A5,D$1),0)/(D$2-D$1+1)*$C5

Which could be broken down as:

  • The End Date (but no later than the end of the year)

Minus:

  • The Start Date (but no earlier than the start of the year)
  • Capped to be no less than zero (as when the formula is copied across to other periods it will produce a negative result)

Divided by:

  • The number of days in the period (to produce a percentage of the year)

Multiplied by:

  • The interest rate for the period

While this needed a larger table, it produced a pretty accurate average interest rate for each period, as shown in the totals. It did feel a little clunky, but as the actual table is much larger, the formula could be easily dragged across a large number of periods and entries.

It may have been more accurate using the YEARFRAC formula, but I had issues producing a "zero" result for the future periods, where no result is expected.

I'm sure there's a cleaner and more accurate answer out there, which I'd be very interested to learn, but this might be helpful to someone.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.