How to Calculate Present Value Using Excel or a Financial Calculator

By Michael Broker
February 18, 2011

Present value is one of the most important concepts in finance.

Luckily, once you learn a few tricks, you can calculate it easily. All you need to do is use Microsoft Excel or a financial calculator.

But we do understand that it can be a little daunting if you've never done it before. So we'll walk you through the process.

Let's start with Excel...

How to Calculate Present Value Using Excel:

Let's look at an example to illustrate the process.

Assume you want to have $1,000 in an account at the end of a three-year period.  The account pays interest at 5% per year.  How much money do you need to initially invest in order to have $1,000 at the end of three years?

1. The process will be easiest if you use the spreadsheet as a table to keep track of the different variables and periods you'll need for your calculation.

First, label the cells in column A as follows:

      A1 is the period of time we're dealing with -- in this case, A1 = Years

      A2 = the Periodic Rate

      A3 = Future Value

      A4 = Present Value

2. Next, fill in the information for the cells in each row. 

     B1-E1 = Years 0 - 3

     B2-E2 = 5%

     E3 = $1,000

Note that because no interest is earned today (at time 0), we put a 0 in for the periodic rate at time 0.

Also note that the future value is only listed in year 3, because we want to have $1000 at the end of the time period.

Your table should look like this so far:


 

3. Now that we have our table, we are ready to calculate PV.  First, select the B4 cell.

Next, click on the function button (fx), which is located right above the column labels.

Once you click fx, a box will pop up.

 

4. Select the "Financial" category from the drop down menu and choose the PV function from the list.

Then click "OK."

5. A new box will pop up asking you to type in the amounts for each variable given. 

Use your table to help you fill in the boxes -- you're being asked for all the information you already used to create your table. Once you have entered the amounts, the solution to PV will automatically appear in the bottom left corner of the box and be labeled "Formula result."

6. When you click "OK" to accept the solution, the solution will be displayed in the cell you selected next to PRESENT VALUE. Now, with the PV correctly calculated, your spreadsheet should look like this:


 

(Note: the present value is negative because it is a cash outflow with respect to the investor.)

 

And that's it. 

Now let's move on to the process for using a financial calculator...

 

How to Calculate Present Value Using a Financial Calculator:

Note: The steps in this tutorial outline the process for a Texas Instruments BA II Plus financial calculator.

1. Using the same example we will now find the present value of an investment by using a financial calculator.  Before we start, clear the financial keys by pressing [2nd] and then pressing [FV].

2. Now we're ready to enter in all the information from our example. First, enter in the number of payments by pressing [3] and then [N].  The screen should now say N = 3.

3. Next, enter the annual interest rate. To be precise, hit [CE/C] for a clear screen.  For our 5% annual interest rate, press [5] and then [I/Y]. (Note: For whatever reason, you don't key in 0.05 for 5% when using a financial calculator -- you key in the whole number "5." If you forget this, you will end up grossly under-calculating the interest rate used in the calculation.)

4. Finally, enter the future value amount ($1,000) and press the [FV] key.

5. Now you are ready to command the calculator to solve for present value.  To calculate PV, simply press the [CPT] key and then [PV].  Your answer should be exactly -$863.84. If you are off by a few cents, it is probably because your calculator is set to display a different amount of digits after the decimal place.  Again, the present value amount is negative because it is an outward cash flow.

Now that you've mastered present value, click here to learn How to Calculate Future Value Using Excel or a Financial Calculator. Or click here to see the financial calculators we've developed especially for InvestingAnswers' readers, including Return, Mortgage and Yield Calculators.