A Certificate of Deposit (CD) is a type of time deposit that lets you lock up a sum of money for a specified term in exchange for a fixed interest rate. Many online CD calculators provide rough estimates, but they often don’t match your exact start date or compounding interval, so the accrued interest can be off. To track your CD balance accurately, I use a spreadsheet formula that displays the current value automatically without monthly manual updates.
Information You’ll Need
- CD principal (the initial amount you invested)
- CD interest rate (APY as quoted by the bank)
- Purchase date of the CD
- Compounding frequency (daily, monthly, quarterly, or yearly)
Most CDs compound daily, but some compound monthly, quarterly, or annually. If you’re unsure, try daily first and adjust the compounding frequency to match your account statements.
Spreadsheet Formula
=E12*(1.022^(1/365))^(DATEDIF(DATE(2015,9,3),TODAY(),"D"))
This example assumes the principal is in cell E12, the APY is 2.2% (entered here as 1.022), compounding is daily (365 periods per year), and the CD purchase date is September 3, 2015.
- Set E12 to your principal amount.
- Replace 1.022 with 1 + your APY (for example, for 3% APY use 1.03).
- Change DATE(2015,9,3) to your CD purchase date.
- If compounding is not daily, replace 365 with the number of periods per year and adjust the DATEDIF unit if needed (e.g., “M” for months). Ensure the periodic exponent and the DATEDIF interval match the compounding frequency.
General Formula for Calculating CD Value
For clarity, the calculation can be viewed as two related formulas. The first is the standard compound interest formula; the second converts APY into the periodic interest rate used in the first formula. If compounding is daily, the number of periods per year (n) is 365.
r = {(1+APY)}^{1/n} – 1
V = current value
P = principal
APY = annual percentage yield (the bank’s quoted interest rate)
r = periodic interest rate (derived from APY)
n = number of compounding periods that have elapsed
In a spreadsheet, combining these steps into a single expression (as in the example formula) lets the sheet compute the current value on any given day automatically. That approach avoids rounding differences and keeps your balance up to date based on the exact purchase date and compounding schedule.
Additional Reading
- Display treasury bond price using CUSIP in Google Sheets
- How to accurately calculate the value of a CD
- Consider iBonds for your Emergency Fund