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

  • Thread starter Thread starter Mike Deblis
  • Start date Start date
M

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
 
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.
 
Back
Top