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
Rate
Transactions	Balance

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"]3.0%[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]13[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]1-Jan-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]25,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]10-Jan-19[/TD]
[TD="align: right"]5,000.00[/TD]
[TD="align: right"]30,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]20-Jan-19[/TD]
[TD="align: right"]-10,000.00[/TD]
[TD="align: right"]20,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]28-Jan-19[/TD]
[TD="align: right"]2,000.00[/TD]
[TD="align: right"]22,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]30-Jan-19[/TD]
[TD="align: right"]-15,000.00[/TD]
[TD="align: right"]7,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]19[/TD]
[TD="align: right"]20-Feb-19[/TD]
[TD="align: right"]22,000.00[/TD]
[TD="align: right"]29,000.00[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]20[/TD]
[TD="align: right"]1-Mar-19[/TD]
[TD="align: right"][/TD]
[TD="align: right"]29,000.00[/TD]
[TD="align: right"]93.45[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

5a​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C15[/TH]
[TD="align: left"]=C14+B15[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C16[/TH]
[TD="align: left"]=C15+B16[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C17[/TH]
[TD="align: left"]=C16+B17[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C18[/TH]
[TD="align: left"]=C17+B18[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C19[/TH]
[TD="align: left"]=C18+B19[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C20[/TH]
[TD="align: left"]=C19+B20[/TD]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D20[/TH]
[TD="align: left"]=SUMPRODUCT(--(A15:A20-A14:A19),C14:C19)/365*F1[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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