Tag: personal finance

The Bank for Kids – an educational bank ledger spreadsheet for parents

According to Scholastic, the recommended age to start giving kids an allowance is around 5-6 years. As parents of kids around that age, this makes sense: our kids are learning about coin values in school and starting to ask questions like “why can’t I buy a museum gift shop stuffed animal when you just spent four times that amount on groceries?”

With this newfound weekly cash flow, it’s a good time to introduce the concept of spending vs. saving, which is the cornerstone of many personal finance websites. When kids save, I suppose they could just keep the cash in a jar, but a more effective teaching tool would be to have them deposit it in the “Bank of Mom and Dad”, for not only safe-keeping, but also to earn an interest return on that saved money. The hope would be to illustrate the value of saving and for that savings to be “put to work” in generating even more money for them from interest.

Spreadsheet Inputs

We’ll use Google Sheets for today’s spreadsheet with the thought that we’d like our kids to view the spreadsheet (but of course not edit…).

We’ll set the spreadsheet up like one of the old “passbook accounts” that I had as a kid (and from which my subconscious is likely drawing this post from):

Passbook - Wikipedia

We’ll also have an input for the monthly interest rate. This is an interesting decision as real monthly interest rates are perhaps only 0.04% and may not actually entice your child to save any money to earn no interest. I’ll put in 3% in our example. If little Abby finds a way to borrow a million dollars at 1% and put it in our bank, we might go bankrupt (although that’ll be promising for her Wall Street career…).

Here’s how our spreadsheet looks so far:

Spreadsheet Logic and output

There’s not really much to the logic: the Date, Description, Deposit, and Withdrawal items will typically be manual inputs depending on what is happening (allowance, withdrawal to buy stuffed animal, gift from Grandma, etc.).

The balance will just be a formula (last balance + deposits – withdrawals).

For interest payments, the formula will go in the deposits. One complication is whether to pay a full month’s interest on money that was deposited during the month by just using the last balance for the interest payment. In the name of simplicity, I think that’s fine. If little Abby figures out a way to arbitrage you by putting a lot of money in on the 30th of the month and then taking it out on the 1st…her financial education would be complete.

Check out the spreadsheet here, The Family Bank. Save a copy by going to File – > Make a Copy

Build a debt amortization spreadsheet

The topic of debt can lead to some heated debate within the personal finance community. While some shun it, others see the value in borrowing for certain things, such as a house, education, a new tv (okay maybe not the tv). While this isn’t exactly a personal finance website, we can use spreadsheets to help visualize how much future money will be needed to repay the loan.

Today’s spreadsheet will walk through how to create a debt amortization spreadsheet. It will take a user’s inputs such as the amount borrowed, interest rate, term, and spit out an interest and payment table.

Spreadsheet Inputs

  • The amount borrowed, which is also known as the “principal.” This is the total loan amount.
  • The annual interest rate, which is about 3% for mortgages these days.
  • The term, which is the number of months or years over which you are borrowing.
  • Whether you want to calculate your payments monthly or annually.

Here’s what we have so far:

Spreadsheet Calculations and Output

The real magic of calculating debt payments is in the finance function PMT(). This function will take your principal, interest rate, and term, and calculate the level amortizing periodic payment. Here’s how we use it in our spreadsheet to calculate the annual payment for our example mortgage:

Next we make a schedule of each of the payment periods, where we keep a running tally of the beginning principal owed, the interest paid, the principal paid, and the ending principal owed.

Now we fill out the table. The first beginning principal comes from cell B5, the payment comes from cell B12, the interest paid is calculated by multiplying the beginning principal with the interest rate, the principal paid is the difference between the payment and interest paid, and the ending principal is the beginning principal minus the ending principal. The next period’s beginning principal is the previous period’s ending principal.

Was that clear?

That’s pretty much it. Feel free to walk through it yourself. I added in the logic to make it flexible for monthly or annual calculations.

Excel: Debt Amortization Spreadsheet

Google Sheets: Debt Amortization Spreadsheet  (you can go to File –> Save a Copy to copy it to your Google Drive to edit it)

Do you want the stock market to go up or down?

At first glance, it sounds like an obvious question – surely it is better when stocks go up, right? From watching the ads on CNBC, it would seem that higher stock prices directly translate into more steak dinners and golf vacations while lower stock prices mean bringing your own peanut butter sandwiches to work (jelly is for bull markets). Continue reading “Do you want the stock market to go up or down?”

Build a personal finance spreadsheet model

Use a spreadsheet to help see the effects of three key personal finance tips: Earn more, spend less, invest wisely

A quick search for “personal finance” on Amazon.com will reveal a vast amount (100+ pages of results) of literature on this important topic.  We imagine that a fresh 22 year old college graduate could easily get overwhelmed by the chorus of advice and opinions.  For them, we offer this post on how to use a spreadsheet to forecast your financial future, and see how your decisions will affect your finances in future years.  Continue reading “Build a personal finance spreadsheet model”