How to calculate compound interest given table of dates/interest rate

[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

 

[Fred Smith]

As you can tell by the dearth of replies, this is not a standard problem.
Most financial applications (bonds, mortgages, retirement planning) would
use a fixed interest rate, so it's relatively straightforward to use the
financial functions.

However, your problem is not insurmountable.

I would start a table on the date of the invoice. The table should be Date,
Rate and Amount. The Amount in C1 is the amount of the invoice. C2 is:

=FV(B1/365,A2-A1,0,-C1)

Copy down until today's date.

This assumes daily compounding. If your compounding period is anything else,
you'll have to convert your interest rate to a daily rate. Post back if you
need this formula.