A Certificate of Deposit, or CD, is a time deposit that allows you to invest a certain amount of money at a specified interest rate and period of time. There’s a lot of CD Calculators online, but usually they don’t match your exact start date and compounding period so the accrued interest isn’t correct.
I track the value in a spreadsheet and wanted a formula that always displayed the current value, rather than manually updating it every month when I receive a statement.
Information You’ll Need
- CD Principal (initial amount you invested)
- CD’s Interest Rate (APY)
- Purchase Date of CD
- Compounding Rate.
Most CDs compound daily, but yours might compound monthly, quarterly or yearly. If you’re not sure, start with daily and adjust if the numbers don’t match your statement.
Spreadsheet Formula
=E12*(1.022^(1/365))^(DATEDIF(DATE(2015,9,3),TODAY(),"D"))
This assumes cell E12 holds the principal value, APY is 2.2%, it compounds daily, and the CD was purchased on 9/3/2015.
- Set E12 equal to your principal
- For the interest rate, change the 1.022 above to 1 + your APY
- For the purchase date, change DATE(2015,9,3)
- If your compounding rate is something other than daily, change 365 and change the “D” in the DATEDIF() function (available parameters)
General Formula for Calculating CD Value
I’ve separated into two formulas so it is easier to read. The first formula is the standard interest rate formula. The second formula converts APY into the periodic interest rate used in the first formula. If your compounding rate is daily, periods (n) = 365.
The spreadsheet formula above combines these two into a single function so it’s easier to use in a spreadsheet.
V = current value
P = principal
APY = annual percentage yield (interest rate bank quotes you)
r = period interest rate (calculated using second formula)
n = periods
David says
Hi Bill
WOW not what I expected this morning hahahah, I am in Australia and looking to invest some money as I assume you are, can I ask what promoted you to post this? is this some kind of psychological test on your users for UI/CX purposes :).
When I have money in the bank its only getting a few % but I found that Challenger annuities has returned an average of 12% for the last 6 years, which I think is pretty good in the current climate. I will say what ever you do don’t risk your money its just not worth it stick with GOV guaranteed stocks or bonds etc, can you get investment insurance? never thought of that?
Thx David
Bill Erickson says
Two months ago I put some cash in a Barclays 5 YR CD. It yields 2.2% and has a 6 month early withdrawal penalty. So as long as I hold it at least a year, I can withdraw at any time and still do better than keeping it in a savings account or shorter duration CD (if I pulled it out in 1 year, effective yield would be 1.1%). The biggest hassle was logging in every month to copy the current value to my spreadsheet, which is why I put this together and thought others might find it useful.
I’ve never heard of Challenger annuities, but they seem limited to Aussies.
If you’re looking for investment advice, I highly recommend Bogleheads.
Leif Q says
I’m very familiar with Vanguard and can agree with your Bogle recommendation… but man – that site! The Motley Fool popped up right around the same time I started learning about investing in the mid 90s and have been my go to source for financial education ever since. Cool to see you write on things outside the realm of your vocation.
Robert E. Turner says
I am looking for a formula to calculate how much money to put into a CD now so that I will have a matured value of $250,000. I am given the rate, the yield, the compounding frequency and the number of months for the CD. I am unable to find any formula for this. Can you help me?
Bill Erickson says
A Present Value Calculator can help with that.
Assumptions:
Type that into the calculator above and you get a present value of $214,608.38, which is the amount you would need to put in a 5-yr CD at 3.1% to have $250,000 mature.