+ Reply to Thread
Results 1 to 6 of 6

Monthly Interest Amount Per with Dynamic Rates

  1. #1
    Registered User
    Join Date
    04-22-2020
    Location
    North Dakota
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    3

    Monthly Interest Amount Per with Dynamic Rates

    Hello!
    I am new to the forum, but appreciate all the help this place provides!

    I'm not entirely sure if this is the correct spot to post so if its wrong, please point me in the right direction.

    What I am trying to do is calculate the interest we have to pay on an item when it starts accruing interest on a certain date. While doing this would be easy normally for me, this one has me stumped as the interest changes at the beginning of each month. I could use some help with the formula.

    What I have:
    • Amount Owed
    • Date it starts accruing interest
    • Rates each month

    This is where I am at right now:
    snip1.PNG
    But I am trying to get this to calculate dynamically with many different start dates and so the next month can start calculating at a different rate.

    I appreciate any insights on this!

  2. #2
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Monthly Interest Amount Per with Dynamic Rates

    Hi there,

    It would be appreciated if you could post a copy of your workbook here - it's much easier to work with an original workbook than to try to recreate it from a screen shot.

    Regards,

    Greg M

  3. #3
    Registered User
    Join Date
    04-22-2020
    Location
    North Dakota
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    3

    Re: Monthly Interest Amount Per with Dynamic Rates

    My mistake, here is the work book.
    Attached Files Attached Files

  4. #4
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Monthly Interest Amount Per with Dynamic Rates

    Hi again,

    Thanks for posting your workbook.

    I'm looking at it and trying to figure out what you're calculating, but one thing seems a bit unusual - if you look at row 11, the interest does not appear to be compounded from month to month, i.e. the interest calculated for March is based only on the initial amount, not on the initial amount plus the interest due for February. Is this intentional?

    Also, column E is entitled "Final Due Date", but based on row 7, the date shown appears to be the INITIAL (drawdown) date.

    Is the value shown in column D ("Days In Inventory") taken into account anywhere in the calculations?

    Regards,

    Greg M

  5. #5
    Registered User
    Join Date
    04-22-2020
    Location
    North Dakota
    MS-Off Ver
    Microsoft Office 365 ProPlus
    Posts
    3

    Re: Monthly Interest Amount Per with Dynamic Rates

    I have attached a better example of what I am trying to accomplish with a formula. I would like to have a bunch of items in the first column with different dates when it becomes interest bearing. Once that happens I want to use the =Today() formula to update how much an item is costing us in interest if we don't get it settled.

    No, the days in inventory does not matter.

    The final due date is the date that triggers when interest starts collecting.
    Attached Files Attached Files

  6. #6
    Forum Expert Greg M's Avatar
    Join Date
    08-16-2007
    Location
    Dublin. Ireland
    MS-Off Ver
    Office 2016
    Posts
    4,467

    Re: Monthly Interest Amount Per with Dynamic Rates

    Hi again,

    Thanks for that update.

    But my previous question still stands - the interest does not appear to be compounded from month to month. You're calculating the interest on only the principal amount for each of the various months for which the amount remains unrepaid, and then adding the interest for each of those months.

    In most situations however, the interest payable in month 2 is calculated on (principal amount + interest due for month 1), the interest payable in month 3 is based on (principal amount + interest due for month 1 + interest due for month 2) etc.

    Does such a method of calculation not apply in your case?

    Regards,

    Greg M

+ 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. Replies: 17
    Last Post: 06-20-2019, 12:08 PM
  2. [SOLVED] Calculate interest between two dates with varying interest rates in the period
    By Topic in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 02-28-2017, 02:23 PM
  3. [SOLVED] Need macro to calculate total interest paid over a period with changing interest rates
    By fmcg in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-08-2016, 10:29 AM
  4. Replies: 6
    Last Post: 04-22-2013, 12:50 AM
  5. Replies: 1
    Last Post: 08-11-2011, 12:51 AM
  6. Comparing interest charges with fixed and variable interest rates.
    By carloski6 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-23-2010, 08:53 AM
  7. [SOLVED] Excel formula for monthly interest rates
    By Bluie2407 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 09-11-2005, 06:05 PM

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