How to Calculate Present Value in Excel & Financial Calculators

Updated June 21, 2021

Present value (PV) is the current value of a future cash flow, given a specific rate of return. Analysts and investors are able to account for the time value of money, which states that an amount of money today is worth more than that same amount in the future (due to its future earning potential).

Present value is useful for determining how much money must be invested today in order to be a certain amount in the future. It’s also an effective tool for stock and bond pricing, banking, financial modeling, and more. 

You can absolutely calculate PV on your own, but it’s much easier to find present value in Excel or a financial calculator – which we teach you here.

How to Calculate Present Value in Excel 

Excel’s auto feature automatically calculates various functions and recalculates every time a value, formula, or name is changed. Once the PV function has been entered, the auto feature allows you to calculate present value – simply by entering the appropriate values.

The CPT PV Formula in Excel 

In order to calculate present value in Excel, you’ll need to use the CPT PV formula:

= PV(rate, nper, pmt, [fv], [type])

Where:

PV: Present Value
Rate: Interest rate per payment period
Nper: Number of payment periods
Pmt: Payment per period (amount, including principal and interest)
Fv: Future value. If left blank, value is assumed to be 0
Type: When payments are due (beginning or end of period) 

Calculating PV in Excel Step-by-Step

Now that we’ve established the CPT PV formula and the required information, you can calculate present value in Excel.

Let’s say you want to reach a savings goal of $25,000 in 10 years and your savings account pays 5% interest per year. In this situation, you’d use the following inputs to find out how much you would need to put into the account today in order to meet your savings goal:

PV: leave blank
Rate: 5%
Nper: 10
Pmt: $0 (because we are not making payments)
Fv: $25,000

Create a Table

Start by creating a table to organize your information.

Enter Your Information

Next, you’ll enter the required information from above. While you can add things like dollar and percent signs, they are not necessary: Excel can handle the calculation with or without them.

Enter the Present Value Formula

Enter the present value formula. Click the blank cell to the right of your desired calculation (in this case, C7) and enter the PV formula: = PV(rate, nper, pmt, [fv]).

Note: The calculation will not work yet. You will need to follow through with the next step in order to calculate the present value based on your inputs.

Select The Corresponding Cells

Change the values in the PV formula (rate, nper, pmt, [fv]) to the cells that correspond with your information. In this situation, it is cells c3,c4,c5, and c6.

 

Complete Your Calculation

Once you’ve selected the relevant cells, hit ‘enter’. The cell will now show the amount that you must put into the account today in order for it to grow to $25,000 in 10 years.


Note: The present value will be negative because it is considered a cash outflow.

How to Use a Financial Calculator to Find PV

If you don’t have access to Excel (or you prefer to do the calculations by hand), you can also find PV on a financial calculator. Most financial calculators have the present value formula built in, but if yours doesn't, you can use the present value formula. 

What Is the Present Value Formula?

If you're calculating present value manually, you’ll need to use the present value formula:

           FV
PV =   --------
         (1+r)n

Where:

PV: Present value
FV: Future value
R: Rate of return
N: Number of periods

Step-by-Step: Calculating PV on a Financial Calculator

The exact steps for calculating PV on a financial calculator can vary, depending on the make and model, so it is important to check the instructions before moving forward. In any case, this section provides general instructions on how to calculate PV on a financial calculator.

For consistency, we’ll use the same values as the example above: Let’s say you want to reach a savings goal of $25,000 in 10 years and your savings account pays 5% interest per year.

PV: unknown
Rate: 5%
Nper: 10
Pmt: $0 (because we are not making payments)
Fv: $25,000

1. Hit "Clear"

Start out by hitting the clear button to ensure that previous calculations or numbers won’t interfere with your calculation of the present value.

2. Enter Info

The next step is to enter the information above as whole numbers, starting with the interest rate. To do this, you’ll enter the value (5 in this example), then press I/Y. Then, you’ll enter the number of periods (10 in this example) and press N. Next, enter the value of the payment (0 in this example) and press Pmt. Finally, enter the future value ($25,000 in this example) and press FV.

3. Compute

Now that all of the information has been added to the formula, you are ready to solve for present value. To do this, press compute (usually “CPT”) then the PV key. The resulting value is the amount you must put in your savings account today in order for it to grow to $25,000 in 10 years.

What Other Calculations Can You Run in Excel Using PV? 

Excel can also be used to run other calculations related to present value.

NPV Function in Excel

Based on the information from your present value calculation, you can also solve for net present value NPV in Excel. Net present value is the difference between the present value of future cash inflows and outflows, discounted to the present. 

In other words, it is the amount left over after subtracting the present value of future cash outflows from the present value of future cash inflows. You can calculate NPV in Excel using the following formula:

=NPV (Rate,value1,value2,Value3,...)

Where:

Rate: Discount rate per period
Value1: Value of first cash flow
Value2: Value of second cash flow
Value3: Value of third cash flow

The number of value variables will depend on the number of cash flows in your series.

XNPV Function in Excel

The main issue with the NPV function is that it assumes that all time periods are equal. This can lead to less precise results if the time periods between your cash flows are not all equal.

The XNPV function in Excel resolves this issue by requiring the dates of each cash flow. By matching each cash flow to its corresponding date, the XNPV function discounts each cash flow more accurately than the NPV function. The formula for the XNPV function in Excel is:

=XNPV (Rate, values, dates)

Where:

Rate: Discount rate
Values: Values of cash flows
Dates: Dates of cash flows

Activate your free account to unlock our most valuable savings and money-making tips
  • 100% FREE
  • Exclusive money-making tips before we post them to the live site
  • Weekly insights and analysis from our financial experts
  • Free Report - 25 Ways to Save Hundreds on Your Monthly Expenses
  • Free Report - Eliminate Credit Card Debt with these 10 Simple Tricks
Ask an Expert
All of our content is verified for accuracy by Rachel Siegel, CFA and our team of certified financial experts. We pride ourselves on quality, research, and transparency, and we value your feedback. Below you'll find answers to some of the most common reader questions about How to Calculate Present Value in Excel & Financial Calculators.

What Is An Example of Present Value in Real Life?

Present value can be used for a number of applications. In real life, PV can be used to determine how much money to invest/save in order to reach a future savings goal.

Why Does the PV Function Show an Error?

One common issue with the PV function in excel is the ##### error. After entering your information into the present value formula, the resulting value may show “#####” instead of a number. This means that the column is not wide enough to show the full value. To fix this, simply double click on the right side of the column where your pv function is entered. 

Can the Present Value Function Be Used For Other Applications?

Yes. Beyond helping to plan for savings goals, the PV function can also be used to determine the present value of an annuity, the present value of a loan, the present value of an investment, the effects of inflation on the purchasing power of money, and more.

What Is the Present Value of Annuity? 

The present value of an annuity is the current value of all future annuity payments. It is the total amount that all of the cash flows are worth after discounting to the present. It is helpful for determining whether it is better to receive a lump sum or a series of annuity payments.

Rachel Siegel, CFA
CFA logo

CFA Charterholder

Chartered Financial Analyst

Rachel Siegel, CFA is one of the nation's leading experts at ensuring the accuracy of financial and economic text.  Her prestigious background includes over 10 years creating professional financial certification exams and another 20 years of college-level teaching.

If you have a question about How to Calculate Present Value in Excel & Financial Calculators, then please ask Rachel.

Ask a question Read more from Rachel
Rachel Siegel, CFA - profile
Ask an Expert about How to Calculate Present Value in Excel & Financial Calculators

By submitting this form you agree with our Privacy Policy