Plain-spoken finance

excel

by

Financial Literacy #11: Intro to Investing

Now let’s get to the good stuff. We can get to the good stuff (making money with investments) because we’ve covered the bad stuff (paying money in interest.) If you avoid paying $20,000 in interest on your credit cards, you can invest that $20,000 and maybe turn it into $100,000. Remember: compound interest!

Here’s the key to investing. Look at the following sentence, read it a few times, and ponder it for a minute.

In general, the higher the risk you take in an investment, the higher your return will be.

Think for a minute, and then read on.

Ready? Does that make sense? Suppose you have $100, and a choice of only two places to put it. First, you can put it in a savings account. Or, you can invest it in the stock market.

The stock market investment is risky. When coronavirus hit, you would have lost 30% or so of your money. No one would ever invest in the stock market if the expected return wasn’t higher. So yes, the sentence above makes sense. Higher risk needs to come with higher return.

But wait … does that really make sense? The riskier investment has, well, risk! I just told you you would have lost 30% in early 2020! How can the return be higher?

The key is in those wiggle words I stuck at the beginning of the sentence: “In general.” The riskier investment might beat the less risky investment 75% of the time. Not every time, but most of the time. And on average, the riskier investment returns more.

Let’s take a look at some actual data. Here’s a spreadsheet; I hope you are able to click and download it:

Data-Lesson-11Download

That’s an Excel file. If you’re using Excel, just download it, find the file, and open it in Excel. If you’re using Google Sheets, download the spreadsheet open Google Sheets and click on the “file picker.” It looks like a folder on the right side of the screen:

This shows historical investment performance data from an NYU professor. I just included 4 assets:

  • The S&P 500 index. This is an index of 500 large stocks, so this is our proxy for investing in the stock market
  • 3-month T.Bill. T Bills are short-term bonds you buy from the United States Government. In other words, you loan the government money and they will pay you back in 3 months, with a little bit of interest. This is our proxy for cash. The government can print money, and so this is a really safe investment. A good savings or money market account should yield roughly the same rate.
  • Treasury Bonds. These are the same as Treasury Bills, except Treasury Bonds don’t return your money to you until 5 or 10 or 30 years later.
  • Baa corporate bonds. Baa makes it sounds like a sheep. But that’s a credit rating. Baa is pretty good, but not as good as, say Aaa. It’s kind of like school but with more grades. Here you are lending to a company, not the government. The risk of a company going out of business is still low, but not as low as the government going out of business. Higher risk leads to higher returns, long-term.

Don’t worry– you don’t need to understand all of this now. We’ll look at each of these in more detail in the next few lessons. For now I just want you to play with the risk and returns.

First let’s freeze the pane so the top labels (which investment) and the side labels (which year) always stay on the screen. Above I clicked on cell B6 and then clicked View–> Freeze Panes.

Now you can scroll to the bottom, to the most recent data. I held down the Control key (Command on a Mac) and pressed the down arrow. This will take you to the bottom of a column of data.

Then I used the average function and the stdev function. There’s something weird about my spreadsheet; when I tried to highlight only the cells in Column B, Excel highlighted everything in column B plus columns D, E, and F. That’s why I started with column D and copied backwards.

Average is of course the average of all of the data, and stdev is the standard deviation. We’re not going to get into too much of the math here. But the Standard Deviation is a measure of how much the numbers vary– how much they deviate from that average.

Roughly 68% of the values should fall within a range defined by the following 2 points:

The average PLUS the standard deviation

The average MINUS the standard deviation.

There’s a whole lot of assumptions you need to make for that 68% to be true. You can argue that the stock market return data is not what is called normally distributed; there tend to be more extreme years than you would expect from a normal distribution. But for our purposes, the standard deviation at least lets us see differences in the returns of the various asset classes.

Take a look at the data you calculated:

The average return of the stock market is 11.57% per year, with a standard deviation of 19.6% per year. So you would expect 68% of years to fall above a return of -8.01% but below a return of 31.16%.

It’s kind of crazy to think that nearly a third of the time, stocks will fall more than 8% or rise more than 31%. That’s some serious volatility!

Compare those numbers to the other investments. The other investments have lower average returns, but also lower volatility. Note that the higher the investment, the higher the volatility that comes with it. (Instead of using the word “volatility” here, I could easily use the word “risk” instead.) That’s not 100% true, since corporate bonds have a higher return and a slightly lower risk than the US Treasury Bonds. Still, higher returns generally come with higher risk.

Scroll back up the top and look at the S&P 500 returns from 1929-1932:

The sum will hopefully be displayed at the bottom of your screen. (If not, just use the SUM function.)The sum of those 4 years equals -85.90%! Now, you wouldn’t lose that much money; negative compounding works in yoru favor here. The second year you lose fewer dollars since you already lost some last year.

Let’s see what your actual loss was, assuming you started with $100 invested at the end of 1928. We’ll conveniently leave off the 43.81% return you would have gotten in 1928, and you’ll invest right at the start of the Great Depression:

Your $100 is now worth just $35.23. If you copy the formula down further, you will see that you get back to your original $100 4 years later. Still, when you invest you can’t see the future; sitting there with your $35.23 would be very painful! That’s the potential pain of higher risk.

Let’s do one last thing and see that $100 invested in all of these investments would have yielded over the entire period. get rid of our little $100 experiment above and start with the $100 before 1928:

The numbers get big, so you’ll need to resize the columns. You can double click on the divider between columns one at a time, or highlight multiple columns and do them all at once. I do both here, so the second one doesn’t do anything for me:

Take a guess as to how much our $100 would be worth at the end of 2019. Remember that this is a 90+ year investment! Then scroll to the bottom to see what happened to your $100.

The $100 you (well, someone else) invested in the S&P 500 at the end of 1927 is now worth over half a million dollars! That seems crazy. But, remember a couple of things:

  • Today, you can buy a fund that tracks the S&P 500 very closely. But you couldn’t in 1928. It was much more common to buy individual stocks. Some would have done much better than the index data here (Coca Cola), but some would have done much worse (Studebaker).

Still, it’s clear that you’re taking on more risk with stocks, but also getting a much better return. We’ll look more closely at stocks in the next lesson.

by

Financial Literacy #9: Credit Cards vs. Debit Cards

In the last lesson, we looked at credit card debt and how much interest you can end up paying. Credit cards are one of the best financial tools out there if you pay your balance in full every month, and one of the worst if you don’t.

Debit cards are somewhat similar to credit cards. But there’s a big difference in how you pay for purchases. With a debit card, the bank isn’t fronting the money to the store where you make a purchase. The money comes right out of your checking account. There are no grace periods; usually the money is taken out of your account within 24 hours.

[Read more…] about Financial Literacy #9: Credit Cards vs. Debit Cards

by

Financial Literacy #8: A Credit Card Example

When we looked at mortgages, the payment amount was set so that the balance was paid off in 30 years. When you carry a balance on a credit card, the credit card company isn’t going to promise a payoff date. The time needed to pay off a $1,000 balance is going to vary based on the interest rate and the policies of the credit card company. Each credit card company sets your minimum payment slightly differently.

About 15 years ago, some credit cards actually had a minimum payment so low that you would never pay off your balance! If you pay less than the interest amount each month, your balance increases. This is not good.

I’m not sure whether this is still legal or not. But it almost never happens anymore, because of a clever change in the law. In 2010, the Federal Reserve started making credit card companies tell you how long it will take to pay off your balance:

Your monthly credit card bill will include information on how long it will take you to pay off your balance if you only make minimum payments. It will also tell you how much you would need to pay each month in order to pay off your balance in three years

If you would never pay off your balance, your credit card statement would need to say that, and I don’t think many companies want to say that outright! Let me know if you ever see an example of this, though.

Here’s what the new information section can look like:

Here’s the question: If you make no additional charges and pay the minimum amount each month, how long will it take you to pay off the balance? The Federal Reserve already forced your credit card company to tell you the answer: At the minimum payment level, it will take you 20 years to pay off this balance. We’re going to confirm this now.

You need some additional information before you can start working. First, the interest rate is a whopping 17.99%! That information is buried at the very end of the statement:

In this example, the account holder is paying their balance off every month, so there’s no interest being charged. Good thing, because 18% is just a silly interest rate to pay. Let’s assume from here on out you only make the minimum payment, and don’t make any additional purchases on the card.

This credit card company’s policy is that the person must pay 1% of the principal balance each month, plus whatever interest is charged. The sum of these is of course greater than just the interest payment, so the balance will go down.

But, if you keep paying 1% of the balance off each month, you never actually finish paying it off! As the balance gets smaller, your payments also get smaller. At a $100 balance, you’d pay $1 (1% of the principal) plus $1.50 in interest (18% a year is 1.5% a month, ignoring compounding.) That’s just $2.50 total. At $1 you’d pay 2.5 cents. You’d never get to $0!

To fix this problem, the credit card company set a minimum payment amount of $25. As soon as you get below a $25 payment, the percentage of the principal that you pay off each month increases. And at a balance of $24.63 or so, you’d still pay that minimum of $25, which would bring your balance to $0 ($24.63 in principal plus $0.37 in interest.)

This spreadsheet will be more complicated than the mortgage spreadsheet, since we need to determine whether or not that $25 minimum kicks in for any given month. And, it’s going to have a lot more rows since we’re going to look at monthly payments for the first time.

First, create a month column, and make sure you have 240(!) numbers there. Add in the starting principal, and the payment (before interest) of 1%.

I probably should have labeled column C something besides Payment, since this isn’t your total payment. It’s really just a working column … maybe “Working Principal Payment”?

Next, let’s add in our constant for the interest rate, and then the interest. The sum of the Payment plus Interest is the actual payment, unless that’s less than $25. We’re going to use the Max function, which returns the larger of a set of values.

Here are a couple of examples of how the Max function works:

=Max(1, 3) returns 3.

=Max(3, 1) also returns 3.

=Max(3,1,3,4,3,1,2,3,4,1) returns 4. Max doesn’t care about the number of numbers in there, or their order, or if there are duplicates.

In the first row above, the formula in E2 for the actual payment simplifies like this:

=MAX(25,C2+D2)
=MAX(25, 56+85.15)
=MAX(25, 141.15)
=141.15

Next month, the comparison will be:
=MAX(25, 140.31)

The second number keeps getting smaller until eventually 25 is larger. Then MAX will return 25 for the rest of the rows.

Now all we have to do is set next month’s principal lower by the amount of principal we paid off. Note that we don’t apply the total payment as a reduction in the principal– the interest payment just goes to the bank!

Here’s where that $25 minimum gets confusing. The amount of principal we pay down isn’t just the value in column C. It is exactly that value for the first payments. But eventually, once the $25 minimum kicks in, the principal payment is more than the small value in Column C. Scroll down and see when that $25 kicks in:


Notice I used the Freeze Panes button. My cursor was in cell A2 before I pressed Freeze Panes. This leaves row 1 frozen, so I can see my header labels as I scroll down. (If you’re using Google Sheets, click on a call in row 2 and then choose View–> Freeze–> 1 row.

Notice how once the actual payment is fixed at $25, the amount of that $25 that goes to principal increases each month. This makes sense: as the balance goes down, the interest I owe goes down. Since the payment stays fixed, more of that payment can go to principal. You can graph column F to see how this kicks in:

I forgot to answer the question. How long does it take to pay off your balance? Scroll down and see when the principal is finally paid off. I see the last positive balance at month 235. Divide 235 months by 12 and that’s 19.583 years, which rounds to 20 years. Just like the credit card company told us!

This isn’t so important, but you can try one more thing if you don’t have spreadsheet fatigue yet. Notice that the minimum payment in the first image was exactly $56:

The credit card company makes you pay 1% toward the principal, but they round down to the nearest dollar. We can use the ROUNDDOWN function to get even more accurate:

=ROUNDDOWN(B2*1%,0) says to round the value down, and leave 0 decimal places. The truncate function would also do the same thing: =TRUNC(B2*1%,0)

Now our principal payments are a little bit lower each month, or at least never higher. So it should take even longer to pay off the balance. And when I scroll down, now it takes until month 239 until the balance is paid off. 240 is exactly 20 years, so we’re right at 20 years now.

You can use the spreadsheet to see how much you paid in interest. I see about $7,686 in interest, and about $13,366 of total payments (interest plus the initial principal amount.) This is very close to the image above; it’s within $60 of the value the credit card company says.

These numbers aren’t that different from a mortgage! At 6% we paid a $117,000 in interest on a $100,000 principal. Here we paid $7,686 in interest on a $5,680 balance. The difference is the 1% principal payment each month. At the beginning of the mortgage, you’re only paying off 0.11% of the principal each month! Lower payments mostly cancel out the lower interest rate.

But there is one fundamental difference between a mortgage and credit card debt: Your mortgage is paying for an asset that usually retains or increases in value, which most credit card purchases decrease in value!