+ Reply to Thread
Results 1 to 5 of 5

Calculating simple daily interest between two dates

  1. #1
    Registered User
    Join Date
    02-10-2019
    Location
    Calgary, Alberta
    MS-Off Ver
    Mac 2011
    Posts
    2

    Calculating simple daily interest between two dates

    Hello, I am building a spreadsheet for tracking my travel expenses and disbursements and need a formula in a cell for calculating interest. I can charge 8%/annum on disbursements. So if I paid $382.50 (E16) for a medical report on Nov 08, 2016 (G3) and the date of my disbursement invoice is today, February 10, 2019 (A16) then, in words, I would need to multiply E16 by the product of the number of days the expense has been outstanding (=G3-A16) and the daily rate of interest 8%/365 or 0.0219. What I have then is =(G3-A16)*0.0219*E16 but I get 7639.60, a number that is wrong. I am not sure if this is relevant but the result I get is not justified to the right like the other numbers in the same column. Can someone guide me?
    I am obviously a beginner Excel user but am trying to improve. It took me quite a while just to understand and format dates into numbers, but at least I understand that now.

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,742

    Re: Calculating simple daily interest between two dates

    8% / 365 <> 0.02191.
    8% / 365 = 0.0002191 because 0.08 / 365 = 0.0002191.
    Try like this.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,483

    Re: Calculating simple daily interest between two dates

    That is because your annual intrest of 8% is 0,08 in decimals and daily interest is not 0,0219 (which is 2,19% a day) but 0,000219 a day (or 0,0219%)
    you just calculated for an annual rate of 800%

  4. #4
    Forum Expert tim201110's Avatar
    Join Date
    10-23-2011
    Location
    Russia
    MS-Off Ver
    2016, 2019
    Posts
    2,357

    Re: Calculating simple daily interest between two dates

    to take into account leap years
    =SUMPRODUCT(1/(337+DAY(DATE(YEAR(ROW(INDEX($A:$A,B2+1):INDEX($A:$A,C2))),3,))))

  5. #5
    Registered User
    Join Date
    02-10-2019
    Location
    Calgary, Alberta
    MS-Off Ver
    Mac 2011
    Posts
    2

    Re: Calculating simple daily interest between two dates

    Thank you, to each of you, for your response. I had the decimal in the wrong place for my daily interest rate! Here I thought my problem had to do with Excel language regarding combining formulas, but it was my simple arithmetic that was wrong. Thank you for your kind guidance. tim201110, taking into account leap years is a great idea, very precise, thank you.

+ 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. Calculating daily interest accrued above a threshold
    By Nick_Moore in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-24-2017, 05:22 AM
  2. Replies: 0
    Last Post: 07-25-2012, 07:08 AM
  3. Daily Simple Interest.
    By WYETTE in forum Excel General
    Replies: 6
    Last Post: 03-28-2012, 08:11 AM
  4. Calculating Daily Compound Interest with a Difference
    By jazman84 in forum Excel General
    Replies: 2
    Last Post: 09-14-2011, 02:35 AM
  5. Calculating finance charges on simple interest
    By Pyrex238 in forum Excel General
    Replies: 0
    Last Post: 03-30-2011, 02:04 PM
  6. simple daily interest
    By anon125 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-30-2008, 11:06 PM
  7. [SOLVED] Calculating daily interest expense
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-02-2005, 11: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