Comparing CDs
Comparing CDs helps determine whether the early withdrawal penalties invoked by breaking an existing certificate of deposit (CD) will be offset by the better terms of a new CD. To say another way, which is the better investment  breaking an existing CD early and purchase a new CD, or, keep an existing CD?
CDs are issued for a fixed period of time at a set interest rate. Redemption of a CD prior to its maturity date invokes a penalty as a reduction in the amount of accrued interest. Based on market conditions, such as higher interest rates, it may be beneficial to redeem an existing CD early (known as "breaking" the CD) in order to pursue a more attractive alternative.
Redeeming a CD prior to its maturity date
When you cash in or redeem your CD at maturity, you receive the money you originally invested plus any accrued interest.
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.^{[note 1]}
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^{[note 2]}
 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. Be careful to use the same units and compounding periods.
 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)
 PV = 100 (sign is negative because money flows out of our hand)
Set Type = 0 (end of period payments).
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.
Notes
 ↑ Some credit unions do NOT give interest credit to the day of early withdrawal, but only to the last POSTED interest amount. So, for example, if you have a CD (certificate) at a credit union that posts interest calendar quarterly (end of March, June, September and December) and you withdraw early on, say, September 29  you MIGHT lose the accrued, but unposted, interest from July 1  September 29 AND be assessed the penalty as well. See: Re: Question on breaking a CD, forum discussion.
 ↑ Some credit unions may pay interest on the 366th day. See: Re: Question on breaking a CD, forum discussion.
See also
References
 ↑ APY Annual Percentage Yield
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
