Interest Rate calculation

[Aland2929]

I would like to calculate the interest earned based on changing interest rates over a 3-year period. I have attached a sample workbook.
Thanks in advance

[shg]

What accounts for the changing balances? Are there payments, and if so, on what dates?

[Aland2929]

The balances are amounts as that date in the account. The interest on the September 2010 amount (367 777) must be calculated at 10% until the 5th November 2010, 9.5% until the 5th November 2011 etc from the 30 September 2010. The October 2010 amount (614 423) must be calculated at 10% until the 5th November 2010, 9.5% until the 5th November 2011 etc from the 30 October 2010.
Thanks in advance

[shg]

Interest on the 30Sep amount from 30Sep to 05Nov, interest on the 31Oct amount from 31Oct to 05Nov?

[shg]

       -----A----- -----B----- -C-- --D-- -----E----- -----F------
   1       EndDate Aug 31 2012                                    
   2         Month      Amount Days  Rate        Date Interest Due
   3   Sep 30 2010    367,777   701 10.0% Sep 01 2010             
   4   Oct 31 2010    614,423   670 10.0%                         
   5   Nov 30 2010     68,425   640  9.5% Nov 05 2010    4,486.97 
   6   Dec 31 2010    661,342   609  9.5%                         
   7   Jan 31 2011    113,758   578  9.5%                         
   8   Feb 28 2011    424,036   550  9.5%                         
   9   Mar 31 2011    558,110   519  9.5%                         
  10   Apr 30 2011    319,783   489  9.5%                         
  11   May 31 2011    348,717   458  9.5%                         
  12   Jun 30 2011     61,988   428  9.5%                         
  13   Jul 31 2011    218,659   397  9.5%                         
  14   Aug 31 2011    568,824   366  9.5%                         
  15   Sep 30 2011    251,199   336  9.5%                         
  16   Oct 31 2011    688,708   305  9.5%                         
  17   Nov 30 2011    937,577   275  9.5% Nov 05 2011  183,044.82 
  18   Dec 31 2011    809,870   244  9.0%                         
  19   Jan 31 2012    378,156   213  9.0%                         
  20   Feb 29 2012    851,560   184  9.0%                         
  21   Mar 31 2012    726,299   153  9.0%                         
  22   Apr 30 2012    379,954   123  9.0%                         
  23   May 31 2012    841,640    92  9.0%                         
  24   Jun 30 2012    959,770    62  9.0%                         
  25   Jul 31 2012    837,875    31  8.5% Jul 19 2012  188,075.66 
  26   Aug 31 2012    147,866     0  8.5%

See attached.

I sorted the dates ascending so I could get my brain around it.

Select F5 and see the definition of the dynamic named range tbl.

[Aland2929]

I will need some time to understand your formula but does it calculate the interest as follows: The interest for September 2010 needs to be calculated for 701 days at the different rates of interest. The interest for October 2010 needs to be calculated for 670 days at the different rates of interest. and so on... I suspect that is what it is doing? Please explain the dynamic formula tbl?
Thanks in advance

[shg]

The formula in F5 calculates the interest on

o the 30Sep amount from 30Sep to 05Nov at the rate in D3, and

o the 30Nov amount from 30Nov to 05Nov at the rate in D4

For a cell in a row having a date in col E, tbl is the range from

o col A in the row where the prior date appears in col E to

o the cell in col D one row above where the formula appears

[Aland2929]

I probably did not explain it properly. The interest needs to calculated as explained above. If the interest rate did not change during the period, then the calculation would be quite simple but the changing rate of interest means the interest must be calculated for a specific number of days at an interest rate and then a different number of days at a different rate of interest etc.
Thanks in advance

[shg]

Please mark this up to explain what is incorrect:


The formula in F5 calculates the interest on

o the 30Sep amount from 30Sep to 05Nov at the rate in D3, and

o the 30Nov amount from 30Nov to 05Nov at the rate in D4

For a cell in a row having a date in col E, tbl is the range from

o col A in the row where the prior date appears in col E to

o the cell in col D one row above where the formula appears

[Aland2929]

For the amount on the 30 Sep 2010, interest must be calculated from the 30 Sep 2010 to 31 Aug 2012. For the amount on the 31 Oct 2010, interest must be calculated from the 31 Oct 2010 to 31 Aug 2012 and so....
Thanks in advance

[shg]

For the amount on the 30 Sep 2010, interest must be calculated from the 30 Sep 2010 to 31 Aug 2012.

At what interest rate(s) in what periods?

[Aland2929]

At the interest rate in column D. 10% from the 30 Sep 2010 until the 15 Nov 2010; 9.5% from the 16 Nov 2010 until the 4 Nov 2011; 9% from the 5 Nov 2011 until the 18 Jul 2011; 8.5% from the 19 Jul 2012 until 31 Aug 2012. The dates in column E are when the interest rate changed.
Thanks in advance

[shg]

10% from the 30 Sep 2010 until the 15 Nov 2010

The date 15Nov does not appear; do you mean 05Nov?

[shg]

New approach:

       -----A----- -----B----- -----C----- -----D----- -----E----- -----F-----
   1     EndDate                                                              
   2   Aug 31 2012 Sep 01 2010 Nov 05 2010 Nov 05 2011 Jul 19 2012 Aug 31 2012
   3      Date       Amount            10%        9.5%        9.0%        8.5%
   4   Sep 30 2010    367,777     371,422     408,432     435,149     439,528 
   5   Oct 31 2010    614,423     615,266     676,574     720,830     728,084 
   6   Nov 30 2010     68,425      68,425      74,755      79,645      80,446 
   7   Dec 31 2010    661,342     661,342     716,720     763,602     771,286 
   8   Jan 31 2011    113,758     113,758     122,293     130,292     131,604 
   9   Feb 28 2011    424,036     424,036     452,541     482,143     486,995 
  10   Mar 31 2011    558,110     558,110     590,842     629,491     635,825 
  11   Apr 30 2011    319,783     319,783     335,905     357,877     361,479 
  12   May 31 2011    348,717     348,717     363,354     387,122     391,018 
  13   Jun 30 2011     61,988      61,988      64,088      68,280      68,967 
  14   Jul 31 2011    218,659     218,659     224,249     238,918     241,322 
  15   Aug 31 2011    568,824     568,824     578,678     616,531     622,735 
  16   Sep 30 2011    251,199     251,199     253,563     270,150     272,868 
  17   Oct 31 2011    688,708     688,708     689,604     734,713     742,106 
  18   Nov 30 2011    937,577     937,577     937,577     992,769   1,002,759 
  19   Dec 31 2011    809,870     809,870     809,870     851,015     859,579 
  20   Jan 31 2012    378,156     378,156     378,156     394,342     398,310 
  21   Feb 29 2012    851,560     851,560     851,560     881,683     890,555 
  22   Mar 31 2012    726,299     726,299     726,299     746,266     753,776 
  23   Apr 30 2012    379,954     379,954     379,954     387,522     391,422 
  24   May 31 2012    841,640     841,640     841,640     851,869     860,441 
  25   Jun 30 2012    959,770     959,770     959,770     964,276     973,979 
  26   Jul 31 2012    837,875     837,875     837,875     837,875     843,945 
  27   Aug 31 2012    147,866     147,866     147,866     147,866     147,866 
  28                                                                          
  29                                4,487     281,364     548,060     126,668

In C4 and copied across and down are account balances:

=IF(C$2 <= $A4, B4, (1 + C$3/365)^(C$2 - MAX(B$2, $A4)) * B4)

In C29 and copied across is interest paid:

=SUM(C4:C27)-SUM(B4:B27)

[shg]

See attached.

[Aland2929]

I have attached a workbook with interest calc's for the first two months which differs from your figures?
Thanks in advanceEF855678 - Interest Paid.xlsx

[shg]

Mine treats the specified interest rate as an APR and compounds daily; so a 10% APR compounded daily for 365 days is an APY of 10.52%:

=(1+10%/365)^365 - 1 ~ 10.52%

Yours compounds only when interest is paid.

Pick the one that's relevant to your case.

[Aland2929]

I still can't see beyond the 15th response? I can't get to page 2 to see 16 & 17?

[shg]

If it's still broken for you, NBVC made the excellent suggestion that you change your settings to show more posts per page. I have mine set to 40, which is probably why I don't have the problem.

[Aland2929]

Once again, thanks for all your help. The rate should be compounded.