Excel can calculate the future value of your investments, such as finding out a return you’ll get on a Certificate of Deposit (CD) or Term Deposit. There’s an inbuilt Excel function to make the task very simple.
It’s just one way Excel can make your financial decisions a whole lot easier.
Future Value of a CD/Term Deposit
Let’s take a look at a few CDs or Term Deposits often offered by banks and other financial institutions and compare their terms.
For the sake of this example, let’s assume you have $10,000 and you’re considering investing it in a CD/Term Deposit.
To compare the CDs accurately, be consistent about the units for specifying rates and “nper” (number of periods invested). If you’re calculating by months but at 2.5% annual interest, you need to divide the interest rate by 12, as shown in the example below.
=FV(A2/12,B2,C2,D2)
Since CDs usually involve a fixed initial investment and a future value payout, the regular payment values in our comparison will be zero.
Now, let’s use the FV formula for each CD you’re considering seeing how they stack up against each other. As shown, a longer period of time for your investment will return a higher future value depending on the interest rates.
Here’s two examples, one with terms as months (the annual interest is divided by 12 to match) or term as years (no interest rate adjustment necessary).
Syntax for FV()
=FV(rate,nper,pmt,[pv],[type])- Rate Required. The interest rate per period.
- Nper Required. The total number of payment periods.
- Pmt Required. The payment made each period; it cannot change over the term. For CD/Term Deposits the value is 0 because all the money is paid at the start.
- Pv Optional. For CD/Term Deposit enter the amount deposited as a negative number.
- Type Optional. When payments are due. 0 = end of period (default). 1 = start of period. for CD/Term Deposits it’ll be 0.
Consider the risk
With any investment, consider the risk factor. Not all financial institutions are the same, so in this example we’ve added fictional names for the borrowers and given each a risk level. 1 is Low, 3 is High. We’ve used numbers because that’s easy to color code with Conditional Formatting (as opposed to Low/Medium/High text labels).
In this example there’s a better return from the Loan Shop but a higher risk and over a longer period.
Important notes
- Deposits should be entered as negative numbers (because you’re handing money over), the future value is given as a positive value (you’re getting the money back).
- You can either enter the interest rate as decimal e.g. 3% = 0.03 or type in the interest rate with % sign and Excel will handle it.
- We’ve added a little Conditional Formatting to the ‘Return at End’ column to highlight the highest return. Conditional Formatting | Top / Bottom Rules | Top 10 items then change the number from 10 to just 1.
Investment Rate of Return is easy in Excel
Track the largest companies market caps, real time, in Excel
Excel can figure out payments to clear credit card debt
