Back when financial institutions wanted your money and actually competed for your deposits, there were all kinds of gimmicks to entice you to deposit your money with them. One such gimmick was a credit union that was offering to pay the interest upfront on their certificates of deposit (CDs). If you are reading this blog, I probably don’t have to tell you that paying the interest upfront raises the yield. In fact it may seem ridiculous to have a whole spreadsheet to prove it, but you may need something like this someday.

The reason I bring this up was that I was having a hard time convincing a co-worker that if we did this, we would be paying a higher yield. The co-worker wanted to consider what we would use the deposits for (i.e. make mortgages) or that we were bringing in funds at a lower rate elsewhere, to prove that the rate was not higher. What we did with the money or other sources that were cheaper, of course, had nothing to do with the fact that we would be paying a higher yield. This did not deter him. When he left my office, I don’t think he was a believer.

I decided that I would have a spreadsheet ready for the next time this might happen that would help explain it. You can download it here.

Take the example of a 5-year CD, at 5% APY, for $10,000. The interest would be:

=(1+0.05)^5*10000-10000

or $2,762.82.

If the interest went back to the depositor immediately, the bank actually would net only:

$7,237.18

So the real APY calculation would be:

=(10000 / 7237.82)^(1/5)-1

or 6.679% not 5.000%

The first thing you see is a table of actual APYs given a stated APY down the left hand column and years (term of CD) across the top row. The yellow cells are inputs, so you can change the APY and Term. The table is conditionally formatted for cells with interest larger than the deposit (green “Infinite” yield) and the brown cell represents the Term and APY inputs in the “Buying Additional CD’s”, which is to the right of the table on the spreadsheet:

This is the way I decided to show someone why upfront interest is higher than the stated APY. Assume for a moment that there was no minimum deposit for buying a 5-year CD. So the depositor could immediately take the upfront interest, move to the next teller, and buy another CD with the interest. Then with the upfront interest from that CD, purchase another CD, etc., etc., etc. So finally, in five years, the total interest at the maturity of all eight CDs would be $3,817.40, vs. interest on a regular CD that pays at maturity of $2,762.82. A difference if $1,054.58.

I hope you never have to use this spreadsheet.

Download workbook “Time_Value_of_Money” from:

Downloads Written in Excel 2013

Pingback: U.S. Treasury Bill Math | Excel@CFO