+ Reply to Thread
Results 1 to 18 of 18

Calculate interest between two dates with varying interest rates during the period

  1. #1
    Registered User
    Join Date
    06-14-2019
    Location
    Zagreb
    MS-Off Ver
    2003
    Posts
    7

    Calculate interest between two dates with varying interest rates during the period

    Hi guys!

    I am looking to automate the calculation of interests for multiple payments based on the date of payment and the date for which I need the interest calculated in the situation where the interest rates are changed as per below table:
    Start date End Date Interest rate
    01.01.2007 31.12.2007 15,00%
    01.01.2008 30.06.2011 14,00%
    01.07.2011 31.07.2015 12,00%
    01.08.2015 31.12.2015 8,14%
    01.01.2016 30.06.2016 8,05%
    01.07.2016 31.12.2016 7,88%
    01.01.2017 30.06.2017 7,68%
    01.07.2017 31.12.2017 7,41%
    01.01.2018 30.06.2018 7,09%
    01.07.2018 30.06.2019 6,82%

    For example, if the payment of 100 was made on 1st Nov 2007 the interest on date of 14th June 2019 would have to be calculated asf:
    Paymnt date no. days Interest amount
    01.11.2007 31.12.2007 61 15,00% 2,51 formula 100*15%/365*61
    01.01.2008 30.06.2011 1277 14,00% 48,98 100*14%/365*1277
    01.07.2011 31.07.2015 1492 12,00% 49,05 ditto
    01.08.2015 31.12.2015 153 8,14% 3,41
    01.01.2016 30.06.2016 182 8,05% 4,01
    01.07.2016 31.12.2016 184 7,88% 3,97
    01.01.2017 30.06.2017 181 7,68% 3,81
    01.07.2017 31.12.2017 184 7,41% 3,74
    01.01.2018 30.06.2018 181 7,09% 3,52
    01.07.2018 14.06.2019 349 6,82% 6,52
    Total accumulated interest for the period is 129,52

    Any help would be Greeley appreciated.


    Thanks,
    Sime


    P.S. there was a post from 2014 with the attachment done by Bernie Deitrick but it does not work for the above needed calc.

  2. #2
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Calculate interest between two dates with varying interest rates during the period

    Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Calculate interest between two dates with varying interest rates during the period

    It seems simple to the point that I wonder if I am missing something.

    Assuming your dates are actual date serial numbers (not text) then:
    number of days (C1) =B1-A1+1 copy/fill down
    interest (E1) =100*D1/365*C1 copy/fill down
    Total interest (E12) =SUM(E1:E10)

    Is there more to the question than that?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    06-14-2019
    Location
    Zagreb
    MS-Off Ver
    2003
    Posts
    7

    Unhappy Re: Calculate interest between two dates with varying interest rates during the period

    Following Jose's msg, I am attaching a worksheet with the real data and calculation for accrued interests for consecutive 3 payments.

    I am looking to automatize calculation of Accrued interests till 14.06.2019. (COL F) for all extra payments (COL E)

    Thanks in advance,

    Sime
    Attached Files Attached Files

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

    Re: Calculate interest between two dates with varying interest rates during the period

    =sumproduct(lookup(row(index($a:$a,$e2):index($a:$a,$f2)),$a$2:$a$21,$b$2:$b$21)/(day(date(year(row(index($a:$a,$e2):index($a:$a,$f2))),3,))+337))
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    06-14-2019
    Location
    Zagreb
    MS-Off Ver
    2003
    Posts
    7

    Re: Calculate interest between two dates with varying interest rates during the period

    Thanks for your reply Tim201110 including the attached file.

    However I do not understand the contents of attachment as it does not show calculation of accrued interests.

    Can you please explain in more details?

  7. #7
    Registered User
    Join Date
    06-14-2019
    Location
    Zagreb
    MS-Off Ver
    2003
    Posts
    7

    Re: Calculate interest between two dates with varying interest rates during the period

    Quote Originally Posted by MrShorty View Post
    It seems simple to the point that I wonder if I am missing something.

    Assuming your dates are actual date serial numbers (not text) then:
    number of days (C1) =B1-A1+1 copy/fill down
    interest (E1) =100*D1/365*C1 copy/fill down
    Total interest (E12) =SUM(E1:E10)

    Is there more to the question than that?
    I have posted the attachment in my next post in order to make it more clear what I aim to achieve.

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

    Re: Calculate interest between two dates with varying interest rates during the period

    For this task a VBA Userdefined function works best I think..

    This is a selfwritten formula that functions just as any other function except that you define your own calculation.
    I setup this UDF that will also require a 2 column table with the startdate and an interest percentage (enddate is redundant info as it is always the day before the change) DO NOT include an enddate column or the function will not work!

    The UDF is called AccruedInterest() and will ask for
    - an Amount (of the extra payment in this case)
    - a Startdate (the date of payment)
    - the InterestTable, this is the range of cells where all percentages are listed.

    Based on the calculation of column "No days" I change the Paymentdate to the first of the month of the payment date

    The outcome of the function is the same as your manual calculation except for a slight rounding difference of 1 cent.

    look at the attachment for how the function is used.
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,802

    Re: Calculate interest between two dates with varying interest rates during the period

    I'm still not sure I understand exactly what your question is. The calculation seems simple enough. The only problem I see is that your "block" that calculates the total interest does not readily copy down to calculate multiple entries. I would use the same sequence of calculations, just arrange them so they all fit into a single row. Steps I took (starting from the file in post #4).

    1) Select H21:M31 and move this block of cells out of the way. (I moved the block to H37 for now).
    2) Select H7:H16 -> copy -> select H26 -> Paste special -> transpose to make a copy of these dates in H26:Q26. Then, because of the bad reference in H26, I entered the 11/1/07 date into this cell.
    3) Select I7:I16 -> copy -> select S26 -> paste special -> transpose to make a copy of these dates in S26:AB26.
    4) number of days calculation as before =S26-H26+1 in AD26. In case it is needed later as the first day in column H changes, I put this inside of a MAX() function so it will return 0 in those cases =MAX(S26-H26+1,0). Copy and paste into AE26:AM26.
    5) Make a horizontal copy of the interest rates -> select L7:L16 -> copy -> select AO24 -> paste special -> transpose.
    6) Interest for each period as before. In AO26, enter =AD26/365*AO$24*$E26. Note the mix of relative and absolute references for easy copying. Copy AO26 and paste into AP26:AX26.
    7) Total interest is simply the =SUM(AO26:AX26) entered into F26.
    8) Select F26:AX26 -> copy/paste or fill down as far as needed.
    9) I did not understand how you were filling in the first date in column H, but you simply need an appropriate formula in this column to get the right start date for each row.

    That seems to work for me. The main idea is to arrange the calculation so it fits into a single row, and then it is a simple matter to copy/paste/fill that row down to the bottom of the table.

    Will that work for you? What step do you have trouble with? Is there something I am misunderstanding?

  10. #10
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Calculate interest between two dates with varying interest rates during the period

    Hi

    I suppose you want this formula in F26 (copy down)

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

    Note: I use only the table1 with (start_date [L], end_date [M], Interest [P]) in (L3:Q12)
    See the file
    Attached Files Attached Files

  11. #11
    Forum Contributor
    Join Date
    11-21-2013
    Location
    zimbabwe
    MS-Off Ver
    Excel 2003
    Posts
    124

    Re: Calculate interest between two dates with varying interest rates during the period

    Say that
    [Startdate] in range A2:A11
    [Enddate] in range B2:B11
    [Interest] in range C2:C11
    Payment is in D1
    Paymrnt Date Begin in E1, Ended in F1
    Array formula for your requirement is:
    Please Login or Register  to view this content.
    Confirmed with Ctrl+Shift+Enter
    Hope this helps
    Last edited by soledad; 06-17-2019 at 06:09 AM.

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

    Re: Calculate interest between two dates with varying interest rates during the period

    [/QUOTE]Can you please explain in more details?[/QUOTE]
    in the WB there are a table and a formula which completely replaces it
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    06-14-2019
    Location
    Zagreb
    MS-Off Ver
    2003
    Posts
    7

    Re: Calculate interest between two dates with varying interest rates during the period

    Thanks Roel, seems to work fine for the dates i calculated manually.

    I have tried comparing your result for payment no.40 and calculation by hand as I did for the three initial payments; there is difference. Your calculation gives return of 123,59 while calculation by hand gives return of 105,39

    In any case, thanks for your effort.

  14. #14
    Registered User
    Join Date
    06-14-2019
    Location
    Zagreb
    MS-Off Ver
    2003
    Posts
    7

    Re: Calculate interest between two dates with varying interest rates during the period

    Can you please explain in more details?[/QUOTE]
    in the WB there are a table and a formula which completely replaces it[/QUOTE]

    Thanks Tin,
    However I still do not see your calculation of the accrued interests as I did manually in the WB i sent

  15. #15
    Registered User
    Join Date
    06-14-2019
    Location
    Zagreb
    MS-Off Ver
    2003
    Posts
    7

    Re: Calculate interest between two dates with varying interest rates during the period

    Quote Originally Posted by José Augusto View Post
    Hi

    I suppose you want this formula in F26 (copy down)

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

    Note: I use only the table1 with (start_date [L], end_date [M], Interest [P]) in (L3:Q12)
    See the file
    Hi Jose,
    I have tried your formula but for the random calculation, payment no.44 where I get total interests 100,67 while your calculation gives 104,26

    FYI, the last date in my calculation is 14.06.2019.

  16. #16
    Forum Expert José Augusto's Avatar
    Join Date
    10-29-2014
    Location
    Portugal
    MS-Off Ver
    2013-2016
    Posts
    3,329

    Re: Calculate interest between two dates with varying interest rates during the period

    Hi @magi41


    The formula did not take into account whether the year was leap year or not. I used @ tim201110's strategy to fix it.
    =SUMPRODUCT(($M$3:$M$12-$L$3:$L$12+1-($B25>$L$3:$L$12)*($B25<=$M$3:$M$12)*($B25+1-$L$3:$L$12))*$P$3:$P$12
    /(365-28+DAY(DATE(YEAR(B25),3,0)))*E26)

    See the file
    Attached Files Attached Files

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

    Re: Calculate interest between two dates with varying interest rates during the period

    Hi magi41
    There Was A Small error in my function. So I update It and added some Fields.

    I added an end date So you can choose between Today or Any other date.
    I Also edit on optional Field usepayment date. This gives You The Choice to either Use the Actual payment date or full month interest calculation. The default setting is False Which will then use full month interest calculation.

    Please the new attachment for the new function

  18. #18
    Forum Expert Roel Jongman's Avatar
    Join Date
    03-28-2015
    Location
    Netherlands
    MS-Off Ver
    Office 365
    Posts
    1,482
    Attatchment
    Attached Files Attached Files

+ 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. Calculate back pay with varying annual interest rates
    By pennchic01 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-07-2016, 12:10 PM
  4. [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
  5. [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
  6. [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
  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

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