Compound interest, varying per annum

[BrutalLogiC]

Hello there.

I was wondering what formula to use to calculate compound interest where the interest rate is not fixed each year

e.g. investment of £400k
interest rate months 1-12 @ 3%
interest rate month 13-24 @ 3.5%
interest rate month 25-100 @ 3%
etc

Hope that makes sense. I am using =FV(C6/E6,D6*E6,0,-B6) to calculate sum based on fixed interest rate but want to amend to different percentage each month or year
B is the sum of money
C is the interest rate
D is the years
E is the internals per annum

Cheers

 

[ryan0521]

you should have another cell, that contains the date of investment.

 

[ryan0521]

change C from interest rate to date of investment and use this formula.

=FV(IF(DATEDIF(C6,TODAY(),"m")>=12,3%,IF(AND(DATEDIF(C6,TODAY(),"m")>=24,DATEDIF(C6,TODAY(),"m")<=13),3.5%,IF(DATEDIF(C6,TODAY(),"m")<=25,3%,"")))/E6,D6*E6,0,-B6)

 

[joeu2004]

I was wondering what formula to use to calculate compound interest where the interest rate is not fixed each year
e.g. investment of 400k
interest rate months 1-12 @ 3%
interest rate month 13-24 @ 3.5%
interest rate month 25-100 @ 3%
etc

It is not clear to me how flexible you want the design to be. Usually, we rely on you to tell us the design and the design requirements.

Perhaps the following paradigm will help.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TH][/TH]
[TH]B
[/TH]
[TH]C
[/TH]
[TH]D
[/TH]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: right"]Loan
[/TD]
[TD="align: right"]Annl Rate
[/TD]
[TD="align: right"]Months
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: right"]400,000
[/TD]
[TD="align: right"]3.00%
[/TD]
[TD="align: right"]12
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3.50%
[/TD]
[TD="align: right"]12
[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD="align: right"][/TD]
[TD="align: right"]3.00%
[/TD]
[TD="align: right"]76
[/TD]
[/TR]
</tbody>[/TABLE]


The FV can be calculated by either of the following equivalent formulas:

{ =B2 * PRODUCT( (1 + C2:C4/12)^D2:D4 ) }
or
{ =FVSCHEDULE(B2, (1 + C2:C4/12)^D2:D4 - 1) }

Formulas displayed with curly brackets {...} are array-entered. Type the formulas without the curly brackets, then press ctrl+shift+Enter instead of just Enter. Excel displays the curly brackets in the Formula Bar to indicate that the formula is array-entered.

You can use the FVSCHEDULE formula if the Excel file is saved as "xlsx" or "xlsm". Use the PRODUCT formula if the Excel file is saved as "xls".

PS.... Those formulas calculate the FV, as you did in your example. If you want just the compounded interest, subtract B2 at the end of each one.