# Compound Interest Calculation

1. ## Compound Interest Calculation

I'm trying to calculate compound interest on a series of sums.

I attach a file with a simplified version.

Column H calculates the interest the way that I have always done so. For each day that there is a transaction (eg money placed into an account) - it counts the number of days between the date of a transaction and the end of the year and calculates compound interest on a daily basis. That works fine (I think..)

To double check this, there is a cell that uses the same formula based on 365.25 days to come up with 5%, which is the interest rate after compounding per year.

I am looking at using a different approach which is in Column J. This needs a row for every day and calculates the interest using the same formula for 1 day on the running total.

The results are meant to be the same for both methods but the interest total is about 2% higher in one than the other.

Does anyone have any thoughts on what I am doing wrong here?

2. ## Re: Compound Interest Calculation

Hi Ian,

I got very different results:

First I calculated the Continuous Interest:
Formula:
`Please Login or Register  to view this content.`
- 5127.11

Then I listed Interest due each day and added them up:
Formula:
`Please Login or Register  to view this content.`
- 5123.85

And, finally listed the interest paid each day and got day 365:
Formula:
`Please Login or Register  to view this content.`
- 5123.15

It's still sloppy but refer to the Interest Calc (2) page

3. ## Re: Compound Interest Calculation

Hi Ian,

See if doing the problem in a few different ways lets you get closer to a better answer. See:
https://exceljet.net/formula/calcula...r-given-period

In dealing with banks and other financial institutions, some use 360 while others used 365. I don't know who used that last .25 of a day per year. Back in the old HP Calculator days it was hard to decide if interest before the payment happened or after the payment happened. See if the above link can show what method you are using.

4. ## Re: Compound Interest Calculation

Thanks for your inputs guys, I'm going to try some other formulas from your suggestions to see if I can get some sort of consensus. The way it needs to work is so that if money is put in one day and withdrawn the next then 1 day's interest is charged.

The 365.25 day approach was to avoid having to have different daily rates in a leap year - but I guess you worked that out already...

5. ## Re: Compound Interest Calculation

You're welcome and thanks for the rep!

One day's interest is charged not accrued?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1