Today I’m going to show you how to calculate the yield and compound interest for a certificate of deposit or a loan with the FV() function in Microsoft Excel. You can find the video explaining this on my YouTube Channel @In30Minutes .
Option 1: Using a Calculator
There are two ways you can calculate yield of a CD. However, the first way you can calculate yield is in a less efficient way by using a calculator.
Let’s say that you have a $10,000 certificate of deposit. What you’ll just do is simple multiplication. Let’s say it has a 2% interest rate. Then you’ll press the equal button ten times, and it’ll give you a value.
The problem with this method is it’s not really calculating interest the way banks calculate interest. It may give you the wrong value not only because it’s not calculating monthly interest, but also because you might press the equal button eleven times or nine times if you’re not being careful.
Option 2: Pasting a Formula into Excel
So, the way that you should do it is by using Excel, which fortunately has a formula that will make it really easy to calculate. It might seem daunting at first, however once you understand the way it works, it’s actually quite easy. Here is the formula: =FV(RATE, NPER, PMT, PV).
Calculate a certificate of deposit’s future value:
- Use =FV(RATE, NPER, PMT, PV) formula
- FV is the future value
- RATE is the interest rate for the period
- NPER is the number of periods (months, years)
- PV is the present value or the principal
- PV is negative to reflect a deposit
- PMT is zero (no additional payments)
For example, to input yield for $10,000, 5-year CD at 2% interest compounded monthly into the formula would look like: =FV(0.02/12,5*12,0,-10000). So, you can take this formula and paste is in Microsoft Excel, when you press enter it will calculate it automatically. In this case, the final value of account is $11,050.79.
Option 3: Calculating a Column in Excel
There’s another way you can calculate the yield on a CD using the above formula If you have numbers that are already inserted into a column. Instead of typing in the value into the last place, (PV), of the formula, you would type in the cell of the Excel column.
Then we’re going to just paste that formula and press enter, to give you the final value. You can drag this formula down and press enter to generate the final value of the other numbers in the column.
This is a quick way to calculate the value of a deposit or any other sort of loan, borrowing, or any scenario where you want to calculate the interest on a compounded monthly basis.
If this helped you out, please take a moment to browse my website and check out my YouTube channel @In30Minutes for other helpful tips and tricks. For more information on how to get the most out of excel, check out my book Excel Basics: in 30 Minutes, as well as the Excel cheat sheets that are created with examples like this.
