Personal Finance

If I were doing this, I would get an average balance for the month (start of month balance + end of month balance divided by 2) and multiply by monthly interest rate (interest rate divided by 12). I would add that interest payment to the end of month balance and that would become the next months starting balance. My spreadsheet columns might look like this:

• Month
• Beginning Balance
• Deposits
• Withdrawals
• Ending Balance
• Interest Earned

Beginning Balance formula would be =sum(Ending Balance, Interest Earned) from the previous line

Deposits and Withdrawals would be numerical entries

Ending Balance formula is =Beginner Balance + Deposits - Withdrawals

Interest formula is =average(Beginning Balance, Ending Balance) * rate / 12

I just calculate it at the end of every month by taking the balance and multiplying by the monthly interest rate. So if you offer 6% per year, multiply by 6/12, or 0.5%. So I have a column called "interest" and the next month's balance is the interest plus the previous month's interest, and then I drag both columns down as needed.

To make things easier to see, I also have a "deposits" and "withdrawals" column for each month and calculate that in to the current month's interest (so current month is previous balance - withdrawals + deposits + previous month interest, and interest is current month * monthly interest rate).

This isn't how banks do it (they do average daily balance), but it's good enough and encourages my kids to keep month invested through the end of the month.