How to Calculate Net Present Values of a Certificate of Deposit Using Excel

The net present value of an investment represents its current value minus the initial investment price. When you purchase a Certificate of Deposit from a financial institution, you will earn a high amount of interest on your money for the duration of your CD. After the CD matures, you can withdraw your initial deposit plus the interest earned. When you are evaluating your financial situation, you can use the Net Present Value function in Excel to calculate the current value of your CD. This function allows you to consider interest already earned on the CD.

Open Microsoft Excel and click cell “A1.” Type “Data” in A1 and press “Enter.” Click cell “A2” and type “Description.” Press the “Enter” button.

Click cell “A1” again, and drag your mouse over to cell “A2” to highlight the cells. Click the “Center” button located in the “Alignment” group on the shortcut ribbon. This feature centers the text inside the selected cells.

Click cell “B2” and type “Interest Rate.” Press the “Enter” button. The active cell should be cell “B3.” If it is not cell B3, click the cell. Type “Purchase Price” in cell “B3,” “First Year Return” in cell “B4,” and “Second Year Return” in cell “B5.” If you’ve received more than two years of interest, continue typing “XXX Year Return” in the subsequent cells in column B.

Click the cell following your last year of return in column B. Type “NPV” or “Net Present Value” and press “Enter.”

Type the percentage rate of your CD in cell “A2.” In this cell, you must list the rate as a percentage instead of a decimal. To make the cell a percentage, click cell “A2” and click the “%” button, which is located in the “Number” section on the shortcut ribbon.

Type the purchase price of the CD in cell “A3.” For example, if you paid $20,000 for the CD, type “20,000” in the cell.

Type each year’s interest earned amount in the subsequent cells in column A. If the interest earned is a profit, write the amount as a positive number. If it is a loss, write the amount as a negative. If you have not received any interest from the CD, enter “0” in each field.

Click the last cell in column A, to the left of the cell labeled “NPV” or “Net Present Value.” Click the “Function” button located on the bottom of the shortcut ribbon. This button is represented by an “fx” symbol.

Type “NPV” in the “Search” box and click the “Go” button. Click “NPV” from the list of results and click the “OK” button. The Function Arguments box opens on your screen, which allows you to create special arguments to calculate the NPV of your CD.

Click the “Rate” field in the “Function Arguments” dialog box and click cell “A2” on your spreadsheet.

Click the “Value1” field in the “Function Arguments” dialog box and click cell “A4.” Drag your mouse down to the final cell that contains your interest earned. For example, if you received six years of interest on the CD, click cell “A4” an drag your mouse to cell “A9.” The Value1 box should display something similar to “A4:A9.”

Click the “OK” button to close the dialog box. Click the cell to the cell to the left of the “NPV” or “Net Present Value” cell. Using the previous example, this cell would be cell A10. Next to the Function (fx) button, a field displays the contents of your formula, which should be something similar to “=NPV:(A2,A4:A9).”

Click the field and place your cursor at the very end of the formula. Press the minus “-“ button on your keyboard and click cell “A3.” Press the enter button. Because the purchase price is an expense against the CD, you must subtract the purchase price from the NPV. The formula in the field should now read something similar to “=NPV:(A2,A4:A9)-A3).” Cell A3 now displays the net present value of your CD.