My numbers won't match other compound interest calculators.

[merry3irwin]

Hi guys,

I've been trying to make an excel spreadsheet to calculate and graph compounding interest over time. This is just a fun tool for myself (I know there are plenty of calculators online), and maybe to illustrate to my friends the power of money not spent.

I have made all the assumptions that banks use to calculate their interest (compounded daily, paid monthly) but cross checking with other similar calculators on the net my numbers are close but not exact. On a 10k principal compounded over 30 years at 4.7% I'm off by about 1300 dollars ($42235 vs $40955).   

Any idea what the issue is? I've attached the spreadsheet.

Thank you.

[markbrynn]

I took a quick look at your spreadsheet, but didn't figure out exactly what you were doing. On the other hand, I matched the answer that the calculator gives by the following:

total = 10,000 x [1 + 0.047/365] ^ (30*365)

In words: take the yearly interest rate and turn it into a daily rate by dividing it by 365. (That is compounding daily.) Then add it to 1 and put it to the power of 365 for full year x 30 for number of years. This overall compounded rate (daily for 30 years) is then multiplied by the starting principle. The idea that the interest is "paid" monthly doesn't seem to factor in (compounded daily is the important part).

One other thing to keep in mind. Switching from days to months to years doesn't work very well as months vary in lenght of day. Luckily with a spreadsheet you can be nerdy and make a "standard month" by dividing 365 by 12 (30.416). Using this as your standard month, you can use a formula of A2 = A1 * (1 + (1.047/365)) ^ 30.416   If you put 10,000 in A1 and fill the formula down 360 cells (month in 30 years), then you should arrive at (or very, very close to) 40,955.

Hope this helps.

[Ozstache]

I think the difference is coming from what I believe is your incorrect assumption that banks compound interest daily. In Oz at least, banks calculate interest on the daily balance but do not compound until the end of the month. This simplifies the required equation for balance after monthly deposit and interest each month to be:

New balance = ( old balance * ( 1 + APR / 12) ) + deposit     

where deposit is made at the end of each month and 0 <= APR <= 1 ie. 0.047 in your attached example

if deposit is made at beginning of month, equation changes to:

New balance = ( old balance + deposit) * ( 1 + APR / 12)     

[merry3irwin]

Thanks Mark. The numbers are coming very very close now :)

Ozstache: Don't the banks calculate the very small daily rate on the balance of the account and then tally it up and pay it into the account at the end of the month?

[marty998]

spot on Ozstache, beat me to it. calculated daily, compounded monthly is the problem.

As a completely irrelevant aside, if you continuously compound (that nebulous impossible concept beyond  daily, hourly, per minute, per second, per yottasecond), the growth factor you get is Euler's exponent.

E.g. $10000 continuously compounded at 5% interest rate grows to $10,000 x e^.05 = $10,512.71

If that $10,000 was compounded once per year obviously you only get $10,500. So the extra benefit to compounding over shorter time periods is limited to $12.71 in this instance.

[marty998]

Thanks Mark. The numbers are coming very very close now :)

Ozstache: Don't the banks calculate the very small daily rate on the balance of the account and then tally it up and pay it into the account at the end of the month?

Interest paid is sum (wheres a sigma when you need it)....

Interest is simply sum of (balance x rate / 365) for however many days in the month. Interestingly in Oz it is 365 days even if it is a leap year as in 2012.

[Ozstache]

Ozstache: Don't the banks calculate the very small daily rate on the balance of the account and then tally it up and pay it into the account at the end of the month?

Yes they do, but they do so based on the current, not the compounded, balance each day. Banks will only compound when they have to because, as you can see with the way you have calculated it, they'd otherwise have to pay you slightly more.

[merry3irwin]

So is my formula calculating daily AND compounding daily, when it's only meant to be compounding monthly?

[Ozstache]

So is my formula calculating daily AND compounding daily, when it's only meant to be compounding monthly?

With the /365 and ^365 in there, yes it does look as though you are compounding daily.  The monthly interest part of the calculation on a set balance compounded monthly is really just as simple as: balance * ( 1 + APR /12)

[merry3irwin]

Awesome! Thanks so much guys. Fixed up the spreadsheet, it's running beautifully now.

This was also a stepping stone to designing other expense charts to accurately track expenses and pretty much my whole financial life. I'm a geek and proud :D

[simonsez]

If this is supposed to match reality moving forward and the instrument has a variable interest rate, remember that for the 4.7% average to spit out the number you seem to have corrected that it will need to be the geometric mean and not the arithmetic mean.  i.e. If you had 15 years at 4% and 15 years at 5.4%, the end $ amount would NOT be $40,955 but if you had 15 years at 4% and 15 years at 5.5225% (square root of (4*5.5225)=4.7) the amount would be correct.  Note that the arithmetic mean would actually be higher than 4.7% (as it would in any case where all 30 interest rates are not the same).