For an audio version of this post, please click on the speaker icon (top left).
I don’t want to brag but my daughter is one smart cookie.
You see, although her dad is smart enough to use savings rate arbitrage to take advantage of online savings rates versus rates found at brick and mortar places, my daughter took it to another level.
Her bank of choice is super convenient and, on top of that, its interest rates blow everything else out of the water.
I am speaking of course of, “The Bank Of Daddy.”
I credit Physician on FIRE for inspiring me with his post, “Teach Your Kids About Money With The Bank Of Mom And Dad.”
I have always encouraged my daughter to save.
To be honest, she was doing fairly well without any additional monetary incentives needed.
But I really wanted to drive home the point of how compound interest is truly a remarkable weapon in her financial armamentarium.
Unfortunately there were two issues that made things difficult:
- My bank required that a child had to be 16 years old with valid ID to open a savings account.
- With the current interest rate environment being so low (0.05% for brick and mortar and 1.8% online), any interest on her balance she would receive would barely register a yawn for her.
So when I read Physician On FIRE’s article about how he created a spreadsheet for his children and essentially took the role of the bank, I knew I had to enact a similar structure for my daughter.
I wanted to create something similar to how real banks treat savings accounts and therefore researched the best way to create a spreadsheet to mimic their practices.
My online bank calculates interest accrued by assessing daily balances.
Instead of having the balance at the end of the month determine how much interest is paid, the interest accrued truly reflects how much money was in play at any given time.
This was therefore how I wanted to design my daughter’s “Bank.”
Building Your Bank:
Now I am certainly no expert in Microsoft Excel, and this will not be mistaken for a masterclass in any sense of the word, but I thought I would share what I came up with so that you might implement a similar spreadsheet for your own family and create your own “Bank.”
I think it is best to first show you the finished product:
What?!??!?
A 12% interest rate! Have you lost your mind, Xrayvsn?!?!?!
Yeah, I know, I know.
So why did I choose 12%?
Well given what little remaining time my daughter has under my roof (she just turned 14) and the fact that I wanted to really highlight the beauty of compound interest so that it can be ingrained into her financial psyche, I wanted to choose an interest rate high enough to make a meaningful impact on her end balance.
That interest rate also happens to coincide with the rate of return Dave Ramsey uses in his financial examples.
Formatting Excel Cells:
In order for this to work, it is very important that Column A Cells be formatted under the “Date” Category.
Using the same methodology, Column B should be formatted under “General” and Columns C-F under “Currency.”
The interest rate cell (H1) should be formatted under “Percentage.”
Entering Data And The Formulas:
Fill out Column A and B with the date and whatever transactional event occurred.
For my very first entry (row 3), I manually inputted all the values shown.
Every subsequent row will then be filled automatically according to the formulas assigned to the specific cell which I will elaborate below:
The subsequent starting balances (Column D) for each row will be calculated according to this formula (=F3+C4):
You will only have to manually enter this formula once and any value below in this column can automatically be filled in by using a trick that will be demonstrated later.
Column E is where the magic begins, as it will calculate whatever interest rate you decide on (H1) for the daily balance in the account with the following formula (=F3*((A4-A3)/365)*(H$1)):
Column F, or the Ending Balance, is pretty straightforward using the following formula (=D4+E4):
Remember above when I said you only have to enter the formulas for the 2nd row (Row4 in my example) and it can then be extrapolated to any data below?
I am sure there are a lot of people who already know the following trick but I will share it so everyone is on the same page.
For every new entry, you will have to manually input the data for column A, B, and C.
The values for columns D, E, and F will be instantly calculated if you do the following:
After you manually input the 1st three columns of data, go to the row above in the 4th column (D) and click on it.
It will now have a green frame around it and in the bottom right corner there will be a small green square (arrow pointing in above example).
Bring your cursor to that green square (it will change to a crosshair) and then drag it down to the cell below.
The new calculated value will automatically populate that cell.
Repeat this process for columns E and F.
Voila!
Your Family Bank is now open for business.
The Impact Of Having The Bank Of Daddy Open For Business:
It took me quite a long time for me to come up with the above spreadsheet and formula, piecing tidbits from various sources on the internet and tweaking it for my particular wants.
When I first told my daughter about me trying to create a “virtual bank” with a spreadsheet, the sound of silence was almost deafening.
You could hear crickets chirping in the distance as she really had no clue what I was trying to accomplish or that this was all for her benefit.
[I do believe she did mutter, “you are such a nerd,” at one point though.]
I finally got the Bank Of Daddy fully functioning and was then able to show her the magic of compound interest first hand.
I showed her the amount of interest already accumulated for the money she had not spent.
That is when I could see it all sink in, as her mouth started dropping and that “light bulb, aha! moment” occurred.
I could see the wheels and gears turning in her head as she realized the potential financial impact it had with her money.
I even further emphasized the beauty of compound interest by showing her what her ending balance could be on a given date (such as the test in my example).
That’s when I knew I had her, and that she might have indeed been bitten by the savings bug.
Note:
If you are in search of financial help, please consider enlisting the service of any of the sponsors of this blog who I feel are part of the “good guys and gals of finance.”
Even a steadfast DIY’er can sometimes gain benefit from the occasional professional input.
-Xrayvsn
NOTE: The website XRAYVSN contains affiliate links and thus receives compensation whenever a purchase through these links is made (at no further cost to you). As an Amazon Associate I earn from qualifying purchases. Although these proceeds help keep this site going they do not have any bearing on the reviews of any products I endorse which are from my own honest experiences. Thank you- XRAYVSN