Personal Finance

3857 readers

1 users here now

Learn about budgeting, saving, getting out of debt, credit, investing, and retirement planning. Join our community, read the PF Wiki, and get on top of your finances!

Note: This community is not region centric, so if you are posting anything specific to a certain region, kindly specify that in the title (something like [USA], [EU], [AUS] etc.)

founded 2 years ago

MODERATORS

[email protected]

Calculating Interest on a "Virtual" Savings Account (self.personalfinance)

submitted 1 year ago by wulf to c/[email protected]

8 comments fedilink hide all child comments

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.

you are viewing a single comment's thread
view the rest of the comments

[–] randomTingler 5 points 1 year ago* (last edited 1 year ago) (1 children)

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 4 points 1 year ago

Super complete answer, thank you!

Also, that's a nice amount of interest!