+ Reply to Thread
Results 1 to 8 of 8

Need macro to calculate total interest paid over a period with changing interest rates

  1. #1
    Registered User
    Join Date
    05-30-2016
    Location
    Adelaide
    MS-Off Ver
    2010
    Posts
    6

    Need macro to calculate total interest paid over a period with changing interest rates

    Hi All,

    I'm new to this Forum and would really appreciate your help!

    I need to calculate total interest payable over a period of time (assume not compounding), the problem is that different interest rates apply at various points during this period.

    Please see attached example, assume that $10m is borrowed for 97 days between the 23rd February 2016 and the 30th May 2016. During this period three different interest rates apply.

    To calculate the total interest paid over this period I have to identify the applicable interest rates, work out the number of days for each interest rate and calculate each interest component as shown in the attached example. This is very time consuming and prone to error.

    I was thinking perhaps a macro could speed up the process, unfortunately I don't know how to write macros so was hoping someone might be able to write a code I could use in Excel 2010!

    Or if you have any other ideas about how to speed up this process that would be much appreciated!

    Thanks,

    Faina
    Last edited by fmcg; 06-08-2016 at 10:32 AM. Reason: Solved - thanks to LordLoki

  2. #2
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: Need macro to calculate total interest paid over a period with changing interest rates

    Does the Design matter?
    I would set it up different to be more dynamic

  3. #3
    Registered User
    Join Date
    05-30-2016
    Location
    Adelaide
    MS-Off Ver
    2010
    Posts
    6

    Re: Need macro to calculate total interest paid over a period with changing interest rates

    Hi LordLoki,

    Thanks for looking into this!

    The design doesn't matter at all, I'm just looking for any ideas to make this calculation easier.


  4. #4
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: Need macro to calculate total interest paid over a period with changing interest rates

    cool i will check it out later and come back to you

  5. #5
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: Need macro to calculate total interest paid over a period with changing interest rates

    Hi Fmcg,

    Find attached a sheet that throws out the interest rate for multiple loans just add them to the list in the settings sheet and push the button
    Only thing that must be a given is that the Rates are sorted by the date they apply so rate 1 can not be starting in march and rate 2 in january unless its january next year

    The Results will be put in the result sheet. i did not add any formatting beside from spacing.

    Try it out and see if it works.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    05-30-2016
    Location
    Adelaide
    MS-Off Ver
    2010
    Posts
    6

    Re: Need macro to calculate total interest paid over a period with changing interest rates

    Hi LordLoki,

    This is brilliant, thank you so much! This will save me hours!!

    I should have explained why I need this, I'm trying to calculate the benefit on investments compared to the interest rate paid on the funds invested, i.e. the arbitrage benefit on investments. The interest rate earned on each investment is fixed for its term, however, the cost of the funds changes during the term.

    Please see attached Example 2. I've added a couple of new columns to the Settings tab. Would you mind changing the macro so that it brings back additional columns in the Results tab which are highlighted in orange please?

    Thanks again!
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    04-20-2015
    Location
    Switzerland
    MS-Off Ver
    2010
    Posts
    312

    Re: Need macro to calculate total interest paid over a period with changing interest rates

    Happy that you like it.
    I added the new Columns and made it so that the Result Sheet gets wiped when you start the script

    Greets
    Loki
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    05-30-2016
    Location
    Adelaide
    MS-Off Ver
    2010
    Posts
    6

    Re: Need macro to calculate total interest paid over a period with changing interest rates

    Yay! Thanks so much, this is perfect!!!

    Thanks for all your help and for such a quick turnaround

+ 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: 8
    Last Post: 02-28-2022, 04:16 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] Calculate of interest with different interest rates per
    By nikosbox in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 01-02-2014, 08:09 AM
  4. Replies: 6
    Last Post: 04-22-2013, 12:50 AM
  5. [SOLVED] need to calculate interest on invoices each day past due until paid, I have varying rates
    By nanonetwork in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-03-2012, 02:12 PM
  6. Replies: 1
    Last Post: 08-11-2011, 12:51 AM
  7. 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
  8. Replies: 3
    Last Post: 02-14-2008, 11:27 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