+ Reply to Thread
Results 1 to 3 of 3

Compound interest - cell which calculates months in default is not accurate.

  1. #1
    Registered User
    Join Date
    02-12-2019
    Location
    London
    MS-Off Ver
    Office365
    Posts
    1

    Compound interest - cell which calculates months in default is not accurate.

    Hello everyone,

    I have recently created a spreadsheet which shows total money owned and total months in default.

    I use this function which change the date =IF(TODAY()>=F19,DATE(YEAR(TODAY()),MONTH(TODAY())+IF(DAY(TODAY())>12,0),12),F19) - and that I calculates total month in default.

    Let's say that I lent 10k on 12/5/2018 and due back was on 12/8/2018 - total money back 12k(include interest etc).
    The borrower did not pay back and my default interest is 2%.

    My function for calculating the money owned by borrower is =G4*(1+J4)^K4 (G4 is the amount due back*(1+default(2%))*total months in default which I calculate seperately((today's - due back)/30.4(month)).

    I found that the function ((today's - due back)/month or 30.4) gives you an incorrect number in money owned by the borrower. When I rewrite the cell with the total months in default and i input only number than the momey owned by borrower is correct.

    Could someone help me why does it do that?

    Thank you

  2. #2
    Banned User!
    Join Date
    01-26-2019
    Location
    United States
    MS-Off Ver
    2010
    Posts
    32

    Re: Compound interest - cell which calculates months in default is not accurate.

    Hi,

    Can you please attach a sample file showing what you expect to happen? To attach reply and select 'Go Advanced'. Then scroll down to 'Manage Attachments'. Upload the file.

  3. #3
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Excel for Windows & Mac - all versions.
    Posts
    24,974

    Re: Compound interest - cell which calculates months in default is not accurate.

    As Bill says a file would be helpful

    But I presume if you're after the amount of interest due then the following is what you want

    A1: 12/5/2018 Loan date
    A2: 10000 Loan Amount
    A3: 12/8/2018 Due Date
    A4: 12000 Due Amount
    A5: 2% Annual Interest rate
    A6: =TODAY() Today's date

    Interest due formula:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    which today gives 121.59 interest
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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