Comparing CDs

When you cash in or redeem your certificate of deposit (CD) at maturity, you receive the money you originally invested plus any accrued interest. In some cases, it may be advantageous to acquire a high yield CD and redeem it early (also known as "breaking" the CD), rather than invest in a less attractive alternative.

CD issuers protect against this practice by penalizing owners with an early withdrawal penalty, in which a portion of the interest earned is forfeited. The CD issuer sets the penalty terms and conditions.

By forfeiting interest, the CD's value is "rolled back in time" so that the owner receives original investment plus accrued interest up to the point in time where the penalty was imposed. For example, a 3 year CD with a 6 month penalty is redeemed after 2 years (24 months). The accrued interest will only be paid up to:
 * 18 months accrued interest= 24 months accrued interest at time of redemption - 6 month penalty

Since the CD was held for 2 years, the total accrued interest is spread over 2 years; thereby resulting in a lowered effective yield.

A CD comparison spreadsheet is available for download below.

Compounding period
The time period used to calculate interest is important. Differences between compounding periods; such as daily, weekly or monthly, will affect results.

In the manner of the day count conventions used for bonds, the CD spreadsheet defines the compounding time period (N) as:
 * 365 days per year, which does not account for leap years
 * One month = 30.41666 days per month (30.41666 = 365 days per year / 12 months per year)

Since the compounding period is already normalized to 365 days/year, conversion to APY is implied. There's no need to use the EFFECT formula as shown below.

Effective yield
Building on the concepts in comparing investments, Excel (or LibreOffice Calc) can be used find the new effective yield. This provides an easy way to compare results with other CDs or investments.

The calculations are done in two steps:
 * Find the CD's value, original investment and accrued interest, at the penalized time of redemption
 * Find the new interest rate at the time of redemption (with no penalty)

Example: A $100 3-year CD with an APY of 4.56 % and a 6 month penalty is redeemed after 2 years. Find the new APY.

First, define the financial variables and cash flow :


 * I = 4.56% APY, which will be converted to a nominal rate
 * N = 18 (18 = 24 - 6, with penalty imposed), the compounding period in months
 * PMT = 0
 * FV = (solve for this)
 * Type = 0 (end of period payments)
 * PV = -100 (sign is negative because money flows out of our hand)


 * {| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"


 * align = "right"|Initial Investment (PV):
 * align = "center"|$100
 * align = "right"|Term:
 * align = "center"|36 months
 * align = "right"|Interest Rate (APY):
 * align = "center"|4.56 %
 * align = "right"|Redemption at:
 * align = "center"|24 months
 * align = "right"|Penalty:
 * align = "center"|6 months
 * align = "right"|Compounded:
 * align = "center"|monthly
 * }
 * align = "center"|6 months
 * align = "right"|Compounded:
 * align = "center"|monthly
 * }
 * }

There are two methods of determining the interest penalty. Simple interest or compounded interest.

Compound interest penalty
From the above information, convert the APY to a monthly rate (I) and find the CD's value (FV) at the penalized redemption time of 18 months (N).


 * {| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"


 * align = "center"|Description
 * align = "center"|Result
 * align = "center"|Formula
 * align = "right"|Nominal Rate (I):
 * align = "center"|0.372%
 * align = "left"|=NOMINAL(4.56%,12)/12
 * align = "right"|Future Value:
 * align = "center"|$106.912
 * align = "left"|=FV(0.372%,18,0,-100)
 * }
 * align = "left"|=FV(0.372%,18,0,-100)
 * }

Then, find the interest rate (I) using the actual redemption time (N = 24); this spreads the accrued interest over the full time the CD was held. Then, convert to APY.


 * {| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"


 * align = "center"|Description
 * align = "center"|Result
 * align = "center"|Formula
 * align = "right"|New Interest Rate (I):
 * align = "center"|0.279%
 * align = "left"|=RATE(24,0,-100,106.912)
 * align = "right"|New APY:
 * align = "center"|3.40%
 * align = "left"|=EFFECT(0.279%*12,12)
 * }
 * align = "left"|=EFFECT(0.279%*12,12)
 * }

Note that the CD's investment value or length of term is not needed to calculate the final yield. However, a value is needed for the interim calculations.

Simple interest penalty
Instead of rolling back the CD over time with the compounding formula (FV), the penalty is calculated using simple interest:
 * Find the CD's value at the time of redemption
 * Subtract the interest penalty, which is the (interest rate per period) * (number of periods). For example:
 * CD's penalized value = Value at redemption - (Value at redemption * (interest rate per month) * (number of months penalized))
 * Find the interest rate (APY) at the time of redemption


 * {| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"


 * align = "center"|Description
 * align = "center"|Result
 * align = "center"|Formula
 * align = "right"|Nominal Rate (I):
 * align = "center"|0.372%
 * align = "left"|=NOMINAL(4.56%,12)/12
 * align = "right"|Future Value:
 * align = "center"|$107.089
 * align = "left"|=FV(0.372%,24,0,-100) - 100*6*0.372%
 * }
 * align = "left"|=FV(0.372%,24,0,-100) - 100*6*0.372%
 * }

As before, find the interest rate (I) using the actual redemption time (N = 24); this spreads the accrued interest over the full time the CD was held. Then, convert to APY.


 * {| style="margin:1em auto 1em auto;" cellpadding= "3" border="1" style="border:1px solid black; border-collapse: collapse;"


 * align = "center"|Description
 * align = "center"|Result
 * align = "center"|Formula
 * align = "right"|New Interest Rate (I):
 * align = "center"|0.286%
 * align = "left"|=RATE(24,0,-100,107.089)
 * align = "right"|New APY:
 * align = "center"|3.49%
 * align = "left"|=EFFECT(0.286%*12,12)
 * }
 * align = "left"|=EFFECT(0.286%*12,12)
 * }

Spreadsheet overview
The spreadsheet, downloaded below, utilizes the above equations in a more compact form. Just enter basic information and the spreadsheet goes to work. Compare results in either years or months. Put an "x" in the Simple Interest Penalty cell if the penalty is simple interest, otherwise compound interest is used.

As the equations are configured, the initial investment is not needed. A value of $1 is used for the interim calculations. If the actual value over time is desired, click on the "Future Value" tab.

Forum discussions

 * Allan Roth article on using CDs instead of treasuries
 * Penfed Early withdrawal penalties
 * PenFed Lowers CD Rates
 * Wiki - CD Penalty Withdrawal Calculations - DIY!
 * “Worth-It to Break CD” APY: Help with calculation