compounding interest

[samc9624]

Hi
I would like to be able to put a date in which is the date when i invested some money at a particular interest rate, then put another date in and be able to see what i should have earned for that period in interest(compounded)

I already have a excel that does it if i put the number of days in but i would like it in a calendar format to make life easier,
also if i add money along the way i would like to be able to pick that up also

appreciate any help as i am only new to excel but willing to learn

cheers.

 

[MickG]

Based on the Layout (example) Below, with Dates in "A", Capital investments in "B" the Balance from (Code) in "C" and the interest Rate +1 (i.e 10 % = 1.1) in "D" then this code should return Capital + Interest in "C".
This code is based on a 365 dys in year so will not be totally accurate.

[COLOR="RoyalBlue"][B]Row No [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(A)     [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(B)    [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(C)           [/B][/COLOR] [COLOR="RoyalBlue"][B]Col(D)   [/B][/COLOR]
1.      Dates       Principal  Balance           Int Rate 
2.      01/01/2010  1000                         1.1      
3.      01/01/2011             1100.00000000001           
4.      01/01/2012  1000       1210.00000000003           
5.      01/01/2013             2431.63487479678           
6.      01/06/2013             2529.42880869888  1.05     
7.      01/01/2015             2732.97132346298

[COLOR="Navy"]Sub[/COLOR] MG05Mar24
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Rate [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Dim[/COLOR] Cap [COLOR="Navy"]As[/COLOR] Double
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A3"), Range("A" & rows.Count).End(xlUp))
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
     Rate = IIf(Dn.Offset(-1, 3) <> "", Dn.Offset(-1, 3), Rate)
    Cap = Dn.Offset(-1, 2) + Dn.Offset(-1, 1)
    Dn.Offset(, 2) = Cap * (Rate ^ (1 / 365)) ^ (Dn - Dn.Offset(-1))
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[brettdj]

The first part can be done with formulas
Once you start adding/removing principal then an ongoing table sequencesuch as Mick provided is necessary

Some formulae for the first part which may be of use

Future Value = Principal * (1+rate)^(no of periods)
so Interest Earned = Future Value - Principal

I haven't got my head around the html maker yet so pls excuse the Excel formula layout
If you had say
A1 principal
A2 rate
A3 end investment date
A4 initial investment date

The the interest on an annual compounding investment is
=A1*(1+A2)^((A3-A4)/365)-A1

The interest on a continual compounding investment (assuming your periods are days) is
=A1*(1+A2/365)^(A3-A4)-A1
or more strictly correct formula
=A1*EXP(6%)^((A3-A4)/365)-A1

so for principal of 1000, (A1), an interest rate of 6% (A2), end date of 1-Jan-2012, initial date of 1-Jan-2011

The first formula gives $60
The continously compounded formula gives 61.83

Cheers

Dave