So for our kids, who are still quite young, instead of opening an entire other savings account for them, we decided to do “virtual” savings accounts.

Essentially, we have a spreadsheet with what money they have gotten from birthdays and other events, then we just deposit it in our savings. They can withdraw whenever they want.

In this spreadsheet, I’ve been trying to keep track of interest (in a basic way), to show how saving can also help them “earn” money. However, I don’t think I’m doing it correctly.

See Google sheet: dates are not correct and interest rates are not accurate. I just wanted to show that we are attempting to give the “correct” interest rate for the given date. (I know interest rates fluctuate all the time, just trying to not make it not too difficult to maintain)

https://docs.google.com/spreadsheets/d/1rwwIFVOGYt-lIx8Dtuv_6PGz28jSNQbH7LcZG2qKlfg/edit?usp=sharing

Thank you for taking a look, I’ve been trying to get this right for a while.

  • sugar_in_your_tea@sh.itjust.works
    link
    fedilink
    English
    arrow-up
    7
    ·
    1 year ago

    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.

  • randomTingler@lemmy.world
    link
    fedilink
    English
    arrow-up
    5
    ·
    edit-2
    1 year ago

    I used my excel skills to create a Google sheet to fulfill your requirement.

    https://docs.google.com/spreadsheets/d/18s_qFviyI2n9ONhd_MLDb9kIYWVfHccHlvqKvtjbabM/edit?usp=sharing

    The cumulative interest is calculated at month level, I guess calculating at day level would be overall kill.

    Transactions:

    • Date: Transaction date
    • Amount: the amount you like to deposit and withdraw (deposits in +ve and withdrawals in -ve)
    • Comment: Any comments if you want to put
    • Month: derived from the date col - used in the Monthly summary sheet
    • Year: derived from the date col - to get per day interest rate
    • Days: derived from the date col - to identify the number of days to apply interest
    • Interest for days: Calculated interest on the month of deposit / withdrawal.
    • The first 3 are inputs and remaining 4 columns are to be copy/paste from previous row.

    Interest Rate:

    • Year: Just the year
    • Interest Rate Per Year: Input the interest rate per year
    • Interest Rate Per Month: Interest Rate Per Year / 12 months
    • Interest Rate Per Day: Interest Rate Per Year / number of days in a year
    • The first two columns are input and remaining 2 columns are calculated

    Monthly Summary:

    • Month: Year and Month (the date is always 1 - check the formula bar). This is used to calculate cumulative interest and to get the deposit / withdrawals.
    • Opening Balance: Closing balance of previous month
    • Deposit / Withdrawals: Sum of deposit and withdrawals in a month - comes from ‘Transactions’ sheet
    • Interest for deposits/WD: Day level interest calculated for the deposits and withdrawals for each month. This also comes from ‘Transaction’ sheet.
    • Interest on balance: This calculates the cumulative interest portion. Interest rates are taken from ‘Interest Rate’ sheet at month level.
    • Closing Balance: Sum of all the above.
    • No inputs are needed in this sheet.

    Hope it will be helpful and all the best for your kids future.

    I do save something for my kid using this scheme. I try to put the maximum amount in a year. I wish my parents saved something for me, which would have supported my education.

    • wulf@lemmy.worldOP
      link
      fedilink
      English
      arrow-up
      4
      ·
      1 year ago

      Super complete answer, thank you!

      Also, that’s a nice amount of interest!

  • warcho@lemmy.world
    link
    fedilink
    English
    arrow-up
    3
    ·
    edit-2
    1 year ago

    interest rates are usually an annual rate and accrue monthly. You seem to have applied the interest percentage of 1% to every deposit which is incorrect. Each deposit should gain that amount every year. For example, if you deposited $10 last year now it would be $10.10. If you deposited $10 2 years ago it would now be $10.201 due to the interest compounding.

    • RecallMadness@lemmy.nz
      link
      fedilink
      English
      arrow-up
      4
      ·
      1 year ago

      Interest is usually calculated as a daily accrual of the EOD balance, then applied monthly.

      Some systems will actually do the accrual daily and store the balance in a shadow account, others will just calculate the interest when it is applied.

      So if you had $100,000 in your account for just one day and nothing else for the rest of the year, at 5% interest you would earn 100000 * 0.05 / 365 = $13.69

      • wulf@lemmy.worldOP
        link
        fedilink
        English
        arrow-up
        1
        ·
        1 year ago

        This seems like the best answer, it’s still not exact since interest changes daily (at least in the U.S) and interest compounds monthly.

        But I changed the interest formula to:

        Number of Days * Interest Rate * (Last Balance + Deposit) / 365

        That seems to be more accurate.

  • sevan@lemmy.world
    link
    fedilink
    English
    arrow-up
    2
    ·
    1 year ago

    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