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