Results 1 to 4 of 4

20050831, 22:13 #1
 Join Date
 Nov 2004
 Location
 Wilmington, North Carolina, USA
 Posts
 1,196
 Thanks
 0
 Thanked 0 Times in 0 Posts
Compounding Interest (daily) (2000)
So I've been doing some research, but I don't know exactly how all of these financial options work. Right now I'm trying to create a formula in Excel to calculate the amount of return on a $2500 initial deposit into an account with 2% daily compounding interest (paid monthly)....anyone know any sites to shed some light/any formulas in excel that I could use? Thanks <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
____________________________
Jeremy
"If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." Richard Clarke

20050831, 22:30 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 31 Times in 31 Posts
Re: Compounding Interest (daily) (2000)
See the links provided in the thread starting at <post#=511,840>post 511,840</post: >.

20050831, 23:03 #3
 Join Date
 Dec 2000
 Location
 Vancouver, Br. Columbia, Canada
 Posts
 268
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Compounding Interest (daily) (2000)
See the attached file.
<font color=red>note that you must have the "Analysis Toolpak" installed, since this worksheet uses the "EOMonth" formula available in the toolpak</font color=red>
As you know, the more often interest is compounded, the faster the balance grows  the interest in a year will be
<pre>i = P * ((1 + r/n)^n  1)</pre>
where P is the initial principal, r is the stated (or nominal) annual rate, and n is the number of compounding periods. There is a limit though  as n gets larger and larger, the interest in the year get closer and closer to the "continuously compounded" interest or,
<pre> i = P * (exp(r * frac)  1) </pre>
where "frac" is the fraction of the year we are talking about. If we are looking at a whole year then i = P x (exp®  1) when interest is compounded continuously. If we are looking at a part of a year, then frac = the number of days the balance is outstanding divided by 365 (the difference between daily and continuous compounding is not even close to material).
The attached sheet determines the number of days in each month that the principal has been on deposit, and then uses the continuous compounding function to calculate the accrued interet to the next month end. The interest is added to the principal  but you don't need to do that; you could, for example, just calculate the interest over the three year life as
<pre> i = P * (exp(r * 3)  1) </pre>
or in your example
<pre> i = 2500 * (exp(0.02 * 3)  1) = 154.59 </pre>

20050901, 18:01 #4
 Join Date
 Nov 2004
 Location
 Wilmington, North Carolina, USA
 Posts
 1,196
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Compounding Interest (daily) (2000)
Dean (and Hans) thank you both very much, for all the information. Your spreadsheet does exactly what I wondered about.
____________________________
Jeremy
"If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." Richard Clarke