+ Reply to Thread
Results 1 to 5 of 5

Compound Interest Calculation

  1. #1
    Registered User
    Join Date
    11-29-2013
    Location
    UK
    MS-Off Ver
    Office 365 subscription
    Posts
    54

    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?
    Attached Files Attached Files

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Compound Interest Calculation

    Hi Ian,

    I got very different results:

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

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

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

    It's still sloppy but refer to the Interest Calc (2) page
    Attached Files Attached Files
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,816

    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.
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  4. #4
    Registered User
    Join Date
    11-29-2013
    Location
    UK
    MS-Off Ver
    Office 365 subscription
    Posts
    54

    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. #5
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,379

    Re: Compound Interest Calculation

    You're welcome and thanks for the rep!


    One day's interest is charged not accrued?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Compound interest calculation
    By flyby99 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-18-2019, 10:55 AM
  2. Compound interest calculation
    By forsterbuilder in forum Excel General
    Replies: 3
    Last Post: 05-06-2012, 07:26 AM
  3. [SOLVED] Mtge calculation (Dly compound interest and multiple interest rate
    By Spudson in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-03-2005, 03:55 PM
  4. [SOLVED] Mtge calculation (Dly compound interest and multiple interest rate
    By Spudson in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2005, 06:17 PM
  5. Compound interest calculation
    By Ira Hayes in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 01-13-2005, 09:06 AM
  6. Compound interest calculation
    By Ira Hayes in forum Excel General
    Replies: 7
    Last Post: 01-13-2005, 09:06 AM
  7. Compound interest calculation
    By Ira Hayes in forum Excel General
    Replies: 7
    Last Post: 01-13-2005, 09:06 AM

Bookmarks

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