Interest Calculation

[JoeC]

I would like to calculate the interest a sum of money has
earned from say 5April04 to todays date taking into
account different rates which are effective from
different dates, eg 4.10% from 1Mar04, 4.30% from 1Jun04
and 4.50% from 1July04.
How best can I achieve this? Your help would be
appreciated.
Regards
JoeC

 

[Peter Atherton]

-----Original Message-----
I would like to calculate the interest a sum of money has
earned from say 5April04 to todays date taking into
account different rates which are effective from
different dates, eg 4.10% from 1Mar04, 4.30% from 1Jun04
and 4.50% from 1July04.
How best can I achieve this? Your help would be
appreciated.
Regards
JoeC

Joe

Set up your table like this from a1
Date I Days C Int
01/03/04 4.10% 92 0.010477778
01/06/04 4.30% 30 0.014061111
01/07/04 4.50% 36 0.018561111

in C1 type the formula =IF(A3="",TODAY()-A2,A3-A2)
in D1 type the formula =$B2/12*$C2/30

Copy both formulas down. You can multiply column D by the
amount invested.

Regards
Peter

 

[Norman Harker]

Hi JoeC!

I don't think there's a "best" way but the following approach covers
the principles involved.

A1: Deposit
A3: 5-Apr-2004
A4: 1-Jun-2004
A5: 1-Jul-2005
A8: Amount today
B1: 1000
B3: 4.1%
B4: 4.3%
B5: 4.5%
C3:
=(1+B3/12)^(12/365)-1
Copied down to C5
B8:
=B1*(1+C3)^(A4-A3)*(1+C4)^(A5-A4)*(1+C5)^(TODAY()-A5)

I've assumed the rates you quote are annual nominal compounded
monthly. If that assumption is incorrect, you'll need to modify the
calculation to get the daily rate equivalent of whatever interest rate
basis you have.

The column C calculations are the calculations of the daily effective
equivalents of the quoted rates.

The B8 formula is simply PV*(1+i1)^n1*(1+i2)^n2*(1+i3)^n3

You could put all of the above in a single formula that substitutes
the interim calculations and even hard codes the date and interest
rate data, but this would be far more difficult to understand and much
less easy to amend and adapt for different data.

Another approach is to set up an accumulation schedule that tracks the
accumulating balance and interest calculations.

 

[Tom Skerman]

A variation on this subject. I need to calculate the monthly interest that
accrues on an amount outstanding. The amount outstanding (account balance)
will fluctuate from week to week. Sort of like and overdraft. Can Excell do
this?

Regards,

Tom Skerman