Present value (PV) is the current value of an expected future stream of cash flow. Present value can be calculated relatively quickly using Microsoft Excel.
The formula for calculating PV in Excel is =PV(rate, nper, pmt, [fv], [type]).
Key Takeaways
- Present value (PV) is the current value of a stream of cash flows.
- PV analysis is used to value a range of assets from stocks and bonds to real estate and annuities.
- PV can be calculated in Excel with the formula =PV(rate, nper, pmt, [fv], [type]).
- If FV is omitted, PMT must be included, or vice versa, but both can also be included.
- NPV is different from PV, as it takes into account the initial investment amount.
Formula for PV in Excel
Again, the formula for calculating PV in Excel is
=PV(rate, nper, pmt, [fv], [type]).
The inputs for the present value (PV) formula in Excel include the following:
- RATE = Interest rate per period
- NPER = Number of payment periods
- PMT = Amount paid each period (if omitted—it’s assumed to be 0 and FV must be included)
- [FV] = Future value of the investment (if omitted—it’s assumed to be 0 and PMT must be included)
- [TYPE] = When payments are made (0, or if omitted—assumed to be at the end of the period, or 1—assumed to be at the beginning of the period)
Some keys to remember for PV formulas is that any money paid out (outflows) should be a negative number. Money in (inflows) are positive numbers.
A higher present value is better than a lower one when assessing similar investments.
NPV vs. PV Formula in Excel
While you can calculate PV in Excel, you can also calculate net present value (NPV). Present value is discounted future cash flows. Net present value is the difference between the PV of cash flows and the PV of cash outflows.
The big difference between PV and NPV is that NPV takes into account the initial investment. The NPV formula for Excel uses the discount rate and series of cash outflows and inflows.
Key differences between NPV and PV:
- The PV formula in Excel can only be used with constant cash flows that don’t change.
- NPV can be used with variable cash flows.
- PV can be used for regular annuities (payments at the end of the period) and annuities due (payments at the beginning of the period).
- NPVs can only be used for payments or cash flows at the end of the period.
Example of PV Formula in Excel
If you expect to have $50,000 in your bank account 10 years from now, with the interest rate at 5%, you can figure out the amount that would be invested today to achieve this.
You can label cell A1 in Excel "Years." Besides that, in cell B1, enter the number of years (in this case 10). Label cell A2 "Interest Rate" and enter 5% in cell B2 (0.05). Now in cell A3, label it “Future Value” and put $50,000 into cell B3.
The built-in function PV can easily calculate the present value with the given information. Enter "Present Value" into cell A4, and then enter the PV formula in B4, =PV(rate, nper, pmt, [fv], [type], which, in our example, is "=PV(B2,B1,0,B3)."
Since there are no intervening payments, 0 is used for the "PMT" argument. The present value is calculated to be ($30,695.66) since you would need to put this amount into your account; it is considered to be a cash outflow, and so shows as a negative. If the future value is shown as an outflow, then Excel will show the present value as an inflow.
:max_bytes(150000):strip_icc()/Clipboard01-618bfd11c29a4e2dbd2a50ea127f34d1.jpg "Present Value (PV): What Is It and How to Calculate PV in Excel (1)")
Special Considerations
For the PV formula in Excel, if the interest rate and payment amount are based on different periods, adjustments must be made. A popular change that’s needed to make the PV formula in Excel work is changing the annual interest rate to a period rate. That’s done by dividing the annual rate by the number of periods per year.
For example, if your payment for the PV formula is made monthly then you’ll need to convert your annual interest rate to monthly by dividing by 12. As well, for NPER, which is the number of periods, if you’re collecting an annuity payment monthly for four years, the NPER is 12 times 4, or 48.
What Is the Difference Between Present Value (PV) and Future Value (FV)?
Present value uses the time value of money to discount future amounts of money or cash flows to what they are worth today. This is because money today tends to have greater purchasing power than the same amount of money in the future. Taking the same logic in the other direction, future value (FV) takes the value of money today and projects what its buying power would be at some point in the future.
Why Is Present Value Important?
Present value is important in order to price assets or investments today that will be sold in the future, or which have returns or cash flows that will be paid in the future. Because transactions take place in the present, those future cash flows or returns must be considered but using the value of today's money.
When Might You Need to Calculate Present Value?
Present value calculations are quite common. Any asset that pays interest, such as a bond, annuity, lease, or real estate, will be priced using its net present value. Stocks are also often priced based on the present value of their future profits or dividend streams using discounted cash flow (DCF) analysis.
The Bottom Line
Excel is a powerful tool that can be used to calculate a variety of formulas for investments and other reasons, saving investors a lot of time and helping them make wise investment choices. When you are evaluating an investment and need to determine the present value, utilize the process described above in Excel.
