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.
Contents
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 3year CD with an APY^{[1]} 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^{[2]}:
 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)
Initial Investment (PV): $100 Term: 36 months Interest Rate (APY): 4.56 % Redemption at: 24 months Penalty: 6 months Compounded: 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).
Description Result Formula Nominal Rate (I): 0.372% =NOMINAL(4.56%,12)/12 Future Value: $106.912 =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.
Description Result Formula New Interest Rate (I): 0.279% =RATE(24,0,100,106.912) New APY: 3.40% =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
Description Result Formula Nominal Rate (I): 0.372% =NOMINAL(4.56%,12)/12 Future Value: $107.089 =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.
Description Result Formula New Interest Rate (I): 0.286% =RATE(24,0,100,107.089) New APY: 3.49% =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.
See also
References
 ↑ APY Annual Percentage Yield
 ↑ Be careful to use the same units and compounding periods.
External links
 Excel 2010 source file: Comparing CDs.xlsx, on Google Drive (Ignore "This spreadsheet is too large." message, use menu File > Download)
 Compare CDs by month or year, simple or compound penalty interest, CD value over time (Future Value tab)
 The original Excel file: CD_penalty_calculation.xls, from flexibleRetirementPlanner.com (permission given to modify and use for the wiki)
 Certificates of Deposit: Tips for Savers, from the FDIC
 When to Break a CD  Is the improved interest worth an early withdrawal penalty?, online calculator from DepositAccounts.com
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!
 “WorthIt to Break CD” APY: Help with calculation
