PV vs NPV: Excel’s Present Value Functions Compared

Written by Kasper Langmann

The world of finance is filled with complex calculations and terminologies that can be overwhelming to the uninitiated. Two such terms that are often used interchangeably, but are fundamentally different, are Present Value (PV) and Net Present Value (NPV). Both of these concepts are integral to financial analysis and are frequently used in Excel for various financial calculations. In this comprehensive guide, we will delve deep into the differences between PV and NPV, and how they are used in Excel.

Understanding Present Value (PV)

The concept of Present Value (PV) is based on the principle that a dollar today is worth more than a dollar tomorrow. This is due to the time value of money, which accounts for the earning potential of money over time. PV is the current worth of a future sum of money or stream of cash flows given a specified rate of return.

Excel has a built-in function, known as the PV function, which calculates the present value of a loan or an investment. It takes into account the future value, rate of interest, and the number of periods. The syntax of the PV function in Excel is: =PV(rate, nper, pmt, [fv], [type]).

Parameters of the PV Function

The PV function in Excel has five parameters, three of which are required and two are optional. The ‘rate’ is the interest rate for each period. ‘nper’ is the total number of payment periods in an annuity. ‘pmt’ is the payment made each period and cannot change over the life of the annuity. ‘fv’ is the future value, or a cash balance you want to attain after the last payment is made. If ‘fv’ is omitted, it is assumed to be 0. ‘type’ is the number 0 or 1 and indicates when payments are due. If ‘type’ is omitted, it is assumed to be 0.

It’s important to note that the rate and nper must be in the same units. For instance, if you make monthly payments on a four-year loan at an annual interest rate of 12 percent, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.

Understanding Net Present Value (NPV)

Net Present Value (NPV) is a financial metric that is widely used in capital budgeting and investment planning. NPV analysis is a form of intrinsic valuation and is used extensively across finance and accounting for determining the value of a business, investment security, capital project, new venture, cost reduction initiative, and anything else that involves cash flow.

Excel’s NPV function calculates the net present value of an investment by using a discount rate and a series of future payments (negative values) and income (positive values). The syntax of the NPV function in Excel is: =NPV(rate, value1, [value2], …).

Parameters of the NPV Function

The NPV function in Excel has two parameters, ‘rate’ and ‘value’. The ‘rate’ is the rate of discount over the length of one period. ‘value1, value2, …’ are 1 to 254 arguments representing the payments and income. Value1, value2, … must be equally spaced in time and occur at the end of each period. NPV uses the order of value1, value2, … to interpret the order of cash flows. Be sure to enter your payment and income values in the correct sequence.

The NPV investment begins one period before the date of the value1 cash flow and ends with the last cash flow in the list. The NPV calculation is based on future cash flows. If your first cash flow occurs at the beginning of the first period, the first value must be added to the NPV result, not included in the values arguments.

Comparing PV and NPV in Excel

While both PV and NPV in Excel are used to determine the value of investments, they are used in different scenarios and their calculations are not the same. The PV function is used when the cash flows are periodic and the intervals between them are the same, while the NPV function is used when the cash flows are not periodic and the intervals between them are not the same.

Another key difference between PV and NPV is that PV does not take into account any cash flows that occur after the payback period, while NPV includes all cash flows that occur during the life of the project. This makes NPV a more comprehensive measure of an investment’s profitability.

When to Use PV vs NPV

The choice between using PV or NPV in Excel depends on the specific circumstances of the investment. If you are considering an investment with a series of equal, periodic cash flows, the PV function is the appropriate choice. This is often the case with annuities or loan payments.

On the other hand, if you are evaluating an investment with a series of unequal cash flows, the NPV function is the better choice. This is often the case with business projects where cash inflows and outflows can vary from period to period.

Conclusion

Understanding the difference between PV and NPV, and knowing how to use them in Excel, is crucial for anyone involved in financial planning, investment analysis, or capital budgeting. While they may seem similar on the surface, they are used in different scenarios and can yield very different results. By understanding these differences, you can make more informed decisions and better evaluate potential investments.