Calculate interest between two dates with varying interest rates in the period

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.

Thanks Bernie. You are a genius.

Originally Posted by Bernie Deitrick

Try the attached - I assumed (probably not correctly, but it will not lead to huge errors) that the interest is compounded daily. And from your dates, I was not able to tell if you were giving MDY or DMY values - none were above 12, which is the usual 'tell'

Attachment 305782

Could someone help me download this workbook?

I know this thread is several years old - but it is the only thread I've been able to find (anywhere online, not just on this site) that describes my exact problem.

My only question is this - is it possible to take this and sort of industrialize it? I'm thinking about trying to nest all of the functions (columns C, D, and E on the rates-calcs tab) because I have a sheet with about 200-500 items that need to be calculated.

For example, my sheet is looking to calculate interest on advances, matched against a calendar of interest rates (based on LIBOR). Each has an advance date and a repayment date, and they range from several days to several years. Since there is a variable number of them (generally 200-400) would I be better off trying to nest all of your information into a jumbo formula in one cell (haven't started trying yet, might not be possible) or writing a macro that takes each line item and calculates it separately in the sheet similar to yours?

Agreed. I cobbled this together (in case it helps anyone else, I'm posting it here). I'm not sure if it is the most efficient method, but it works.

I have a third tab that is a "staging area" which is basically the first tab of your adjustable rate sheet, with a minor adjustment, i.e. negative dates are zeroed, etc., which are relevant to my specific needs.

Thank you for your input!

Sub IOA_calc()
Dim pointer As Integer
pointer = 5

Do While Worksheets("Working Area").Range("M" & pointer).Value <> ""
' Copy advance date, repay date, and amount, enter into staging sheet
    Range("C" & pointer).Copy
    Sheets("Macro Staging").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Working Area").Select
    Range("K" & pointer).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Macro Staging").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Working Area").Select
    Range("I" & pointer).Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Macro Staging").Select
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    
' Copy resulting IOA calculation, and input into column N in working area sheet
    Range("B6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Working Area").Select
    Range("N" & pointer).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("N" & pointer).Select
    
pointer = pointer + 1
Loop

End Sub