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

1. ## 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

2. ## 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. ## 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. ## 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. ## 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.

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!

7. ## 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

8. ## 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

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

#### 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