How to Calculate Present Value in Excel and Financial Calculators
Present value (PV) is the current value of a future cash flow, given a specific rate of return. It allows analysts and investors 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 is also an effective tool for stock and bond pricing, banking, financial modeling, and more.
It’s possible to calculate PV on your own, but it’s much easier with a present value calculator like Excel. Here, we’ll show you exactly how to calculate present value in Excel and financial calculators.
How to Calculate Present Value in Excel
Excel makes calculating present value (and a number of other financial formulas) easy, thanks to its auto feature. The 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 will allow 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])
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)
The Exact Steps to Calculate PV in Excel
Now that we’ve established the formula and 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 would use the following inputs to find out how much you would need to put into the account today to meet your savings goal:
PV: leave blank
Pmt: $0 (because we are not making payments)
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 at this point. You will need to follow through with the next step in order to calculate 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 Calculate Present Value Using a Financial Calculator
If you don’t have access to Excel (or you prefer to do the calculations by hand), you can also solve for present value using a financial calculator. Most financial calculators have the present value formula built in, but if yours does not, you can use the present value formula.
Present Value Formula
If you are calculating present value manually, you’ll need to use the present value formula:
PV = --------
PV: Present value
FV: Future value
R: Rate of return
N: Number of periods
How to Calculate 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.
Pmt: $0 (because we are not making payments)
1. Start Fresh
Start out by hitting the clear button to clear your screen. This ensures that previous calculations or numbers will not interfere with your calculation of 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.
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:
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)
Rate: Discount rate
Values: Values of cash flows
Dates: Dates of cash flows
Ask the Experts about Present Value Calculations
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.
Personalized Financial Plans for an Uncertain Market
In today’s uncertain market, investors are looking for answers to help them grow and protect their savings. So we partnered with Vanguard Advisers -- one of the most trusted names in finance -- to offer you a financial plan built to withstand a variety of market and economic conditions. A Vanguard advisor will craft your customized plan and then manage your savings, giving you more confidence to help you meet your goals. Click here to get started.