Calculating compount interest from a table of dates/interest rates

[Mike Deblis]

Hi,

There must be a standard way of doing this...

I have a table of bank base rates and the day on which they came into
effect - about 50 entries in two columns (date/rate%).

I want to calculate the compound interest on one of my customer's
outstanding invoices from the due date of the invoice.

My knowledge of Excel is basic, but even I understand there must be a
standard way of doing this ;-)

The interest rate table goes back several years, as does the outstanding
amount. Any help would be gratefully received.

Thanks

 

[Norman Harker]

Hi Mike!

Here's a fairly simple approach:

04-Feb-2000 5.000% 0.0133681% 1000
30-Jun-2000 6.000% 0.0159654% 1019.844
30-Nov-2000 5.500% 0.0146698% 1045.061
26-Dec-2000 4.500% 0.0120601% 1049.054
24-May-2001 3.500% 0.0094255% 1068.074
14-Jul-2001 5.000% 0.0133681% 1073.221
16-Aug-2001 6.000% 0.0159654% 1077.965
21-Nov-2001 5.500% 0.0146698% 1094.788
24-Apr-2002 4.500% 0.0120601% 1119.8
22-Oct-2002 3.500% 0.0094255% 1144.511
25-Nov-2002 5.000% 0.0133681% 1148.185
11-Jan-2003 6.000% 0.0159654% 1155.421
07-Mar-2003 5.500% 0.0146698% 1165.61
10-Jun-2003 4.500% 0.0120601% 1181.967


Column A is the date
Column B is the annual effective rate that applies for that date
Column C is the daily effective rate calculated using
=(1+B1)^(1/365)-1
If you have an APR(12) quoted then you'll need to calculate daily
effective using =(1+B1/12)^(12/365)-1

I put the initial debt at the top of Column D
D2 formula is =D1*(1+C1)^(A2-A1)

Final amount in Column D is the accumulated debt.
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.