Present value (PV) is the current value of an expected future stream of cash flow. PV can be calculated relatively quickly using excel. The formula for calculating PV in excel is =PV(rate, nper, pmt, [fv], [type]).
- Present value (PV) is the current value of a stream of cash flows.
- 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 includes 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.
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 PV of cash flows and 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.
Now, 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.
Example of PV Formula in Excel
If you expect to have $50,000 in your banking 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 "Year." Beside that, in cell B1, enter the number of years (in this case 10). Label cell A2 "Interest Rate" and enter 5% in cell B2. 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 was shown as an outflow, then Excel will show the present value as an inflow.