changing balance compounded interest calculator with changing rates

[primuspaul]

How would one go about designing an excel sheet that calculates interest like a bank's savings account, meaning balances that change from day to day and interest rates that change during the year? Preferably, the interest rates would come from a table.

 

[Dave Patton]

You did not provide relevant information.
- how is the interest calculated
- is interest compounded monthly
- when do the rates change
- etc.
You did not show an example of your data

A quick idea is presented below

Excel 2010
	A	B	C	D	E	F
1					Rate	3.0%
12
13		Transactions	Balance
14	1-Jan-19		25,000.00
15	10-Jan-19	5,000.00	30,000.00
16	20-Jan-19	-10,000.00	20,000.00
17	28-Jan-19	2,000.00	22,000.00
18	30-Jan-19	-15,000.00	7,000.00
19	20-Feb-19	22,000.00	29,000.00
20	1-Mar-19		29,000.00	93.45
5a

Cell Formulas
Range	Formula
C15	=C14+B15
C16	=C15+B16
C17	=C16+B17
C18	=C17+B18
C19	=C18+B19
C20	=C19+B20
D20	=SUMPRODUCT(--(A15:A20-A14:A19),C14:C19)/365*F1

 

[primuspaul]

You did not provide relevant information.
- how is the interest calculated
- is interest compounded monthly
- when do the rates change
- etc.
You did not show an example of your data

A quick idea is presented below

Excel 2010

	A	B	C	D	E	F
1					Rate	3.0%
12
13		Transactions	Balance
14	1-Jan-19		25,000.00
15	10-Jan-19	5,000.00	30,000.00
16	20-Jan-19	-10,000.00	20,000.00
17	28-Jan-19	2,000.00	22,000.00
18	30-Jan-19	-15,000.00	7,000.00
19	20-Feb-19	22,000.00	29,000.00
20	1-Mar-19		29,000.00	93.45

<tbody>
</tbody>

5a​

Worksheet Formulas Cell Formula C15 =C14+B15 C16 =C15+B16 C17 =C16+B17 C18 =C17+B18 C19 =C18+B19 C20 =C19+B20 D20 =SUMPRODUCT(--(A15:A20-A14:A19),C14:C19)/365*F1 <tbody> </tbody>

<tbody>
</tbody>

Interest is calculated based on the date and pulls the data from a chart that contains the info, for example:

start date,end date,interest
1/1/2016,10/5/2016,6%

It is compounded daily
Rates can change at any time. Could be in the middle of a month.

That's the thing, besides the interest rate chart, I really don't know how to organize the data. I know when certain events happen: I have a list of deposits. That could also be in a chart:

date,amount
1/5/2016,105.50
2/9/2016,-10.15