Daily Compound Interest

[gambils]

Paying off a small bill for my grand-daughter and want to use the opportunity to teach the evils of credit cards by charging her daily compounding interest until she pays me back. Want a formula which updates the daily increasing balance owed so for instance if she owes $100 and interest rate is 20% she can open up the spreadsheet daily and see the payoff amount growing.

 

[Dave Patton]

You did not specify that 20% is the annual rate.

T202209a.xlsm
	A	B	C	D
1	Amount	100
2	Rate	20%
3	Future Value
4	Days
5	10	$100.55		$619.17
6	30	$101.66		$23,737.63
7	365	$122.13
8
9		Annual		Daily
10		Rate		Rate
11	Day	100.00		100.00
12	1	100.05		120.00
13	2	100.11		144.00
14	3	100.16		172.80
15	4	100.22		207.36
16	5	100.27		248.83
17	6	100.33		298.60
18	7	100.38		358.32
19	8	100.44		429.98
20	9	100.49		515.98
21	10	100.55		619.17
4b

Cell Formulas
Range	Formula
D5:D6	D5	=FV($B$2,A5,0,-$B$1)
B5:B7	B5	=FV($B$2/365,A5,0,-$B$1)
B11	B11	=B1
B12:B21	B12	=B11*(1+$B$2/365)
D11	D11	=B1
D12:D21	D12	=D11*(1+$B$2)

 

[gambils]

Yes, I meant annual rate So if I put in a simple formula subtracting the start date from the current date to get my number of days, I could drop that in for the number of days and I would not have to copy the formula down the column ad infinitum, right? It would just update every day. Thanks so much for your reply!

 

[gambils]

Never mind on my follow-up. I wrote it at 4:25 A.M. I was correct on my follow-up question. Thanks, again!