I can't find my answer anywhere, but believe it's a simple solution. I need Excel to automatically calculate the daily interest on an amount (which will go up and down with deposits and withdrawals), and then add it to the total, to display the overall total including interest (compounding). This will change daily when someone looks at the value in Excel due to the daily interest being added.

For example. I have $5000. At 5% APR, the daily interest rate would be .0137%. The day after the $5000 is there, the new total should show $5000.68 if I opened and looked at the spreadsheet. If didn't check the spreadsheet, then on day 2, the total should show $5001.37. If I added $5000 more to the total on day 2 (after interest was added) I would then have $10001.37 and the interest rate for the next day should automatically add on to the new total, compounding daily.

To see total with interest can use-

=B2 (B2*0.0137%)*(TODAY()-A1)

CodePudding user response：

Make a named value called DailyInterest with a value of 1.000137.

Then make a table like this...

Then highlight it and click Insert Table from the ribbon.

Tables will give you a few benefits, some cosmetic, but the one I want to show you is that you can define a formula for a column, and it's ONE formula. It doesn't get copied down to each row, it just seems that way, but the reality is that the formula stored once, internally, and applied to every row.

So, then, change that amount of 0 with

=IFERROR(OFFSET([@Amount],-1,0)*POWER(DailyInterest,[@DATE]-OFFSET([@Date],-1,0),0) N([@Change])

Within the table, [@Date] refers to the value in the Date column for that row.

I used N() so that nonvalues in the Change column are treated as zeroes.

Now, each time you fill in the Date and Change column, the Amount column will be automatically populated.