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 Removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
"Mike Deblis" <(E-Mail Removed)> wrote in message
news:VRHTb.2124$(E-Mail Removed)...
> 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
>
> --
> Mike
>
>
Similar Threads
