How to Calculate Present Value in Excel (With Examples)

Determining the initial cash injection for a target investment, evaluating the better option between two investments, calculating the current worth of an investment. These are all the requirements leading up to wanting to find the present value. Present value is the financial value of a future income stream at the date of valuation.

In Excel, you will find the PV function is quite the handy present value calculator. The type and nature of investment will however determine the variables for the PV function. The three broad categories we'll cover for calculating the present value are annuities, perpetuities, and one-time payouts.

Are you confusing present value with net present value? Let's establish that the two are quite far from equal. How you ask? Read below!

PV Vs NPV

Present value is the current value of an investment now with a projected income stream as per the set interest rate. PV is the figure you calculate when you want to compute, for example, the initial amount of investment to be made to achieve a certain target in a given number of years.

Or for computing the amount to be paid now given the interest rate and future payments. The present value calculation assumes fixed interest rates, payments, and intervals between payments. It can also account for different annuity types (end of period or beginning of period payment).

While net present value also signifies a present value, it is indicative of the profitability of an investment. Both PV and NPV incorporate discounted cash flows. What makes NPV a net figure is the adjustment of the initial investment to outline profitability. As opposed to regular cash flow, NPV deals with uneven cash flow. All in all, NPV calculates the present value of net cash flow over a period of time.

Having outlined the distinctions between the two, we can now proceed to explore the methodology for calculating the present value for investments.

Let's get presenting present values!

Method #1 – PV Formula of Single Cash Flow

Let's start with the least variables and a simple investment concept. This medium calculates the present value of a single payment using the PV function in Excel. The PV function returns the present value figure; the amount that future payments are worth now. To explain the following case example, right now we will just focus on a single instance of a future payment instead of multiple instances.

As an example to carry this out, let's say Cal is targeting to gather $4,000 for a project in 2 years and another $1,000 by the third year. He finds a couple of investment options and wants to weigh out how much he must initially invest in either option. In other words, this initial investment will be labeled as the present value, and the target figure as the future value of the investment.

This formula is for calculating the present value of a lump sum:

And here would be the outcome of this formula:

But with Excel, the PV function can do the job. A very simple formula is shown below employing the PV function for the task:

The first argument requires the interest/discount rate which we have entered as C3. The second argument, denoting the number of payment periods is fed as 3 years here. The next argument is left blank (you will see its use in the upcoming section) and finally, the future value is entered as the fourth argument.

Just as the general present value formula would operate, the PV function has computed the present value of the first investment option as $4,081 indicating the set-up amount that this choice will require.

Also, note that the resulting value of the PV function is negative from the point of view of the investor. For the issuer, the future value will be entered as negative, deriving a positive present value figure. Nevertheless, you can still have the final figure positive by starting the formula with a minus sign like so:

Single Cash Flow with Compound Interest

The compounding frequency in the previous example was yearly. If Cal was faced with investment options with more frequent compounding, this is how the present value computation would be altered:

We'll suppose that the options in the example involve monthly and quarterly compounding respectively which we have incorporated in row 4. The two things in the formula that would be affected by compounding frequency are the interest rate and the number of payment periods.

To be converted into a monthly interest rate, 7% will be divided by 12 (as done in the first argument where C3/C4). Also, the number of periods in 3 years with monthly compounding will be 3 times 12 (reflected in the second argument).

In comparison to $4,081 with yearly compounding, monthly compounding requires $26 less to be invested now.

Method #2 – PV Formula of Series of Cash Flows (Annuity)

Next up, we'll calculate the present value of an annuity in Excel, again courtesy of the PV function. An annuity comprises a series of consistent payments made at regular intervals, whether yearly, quarterly, monthly, weekly, etc. You probably didn't know them as annuities, but popular examples include home mortgage and pension payments.

Compared to a single payment, two elements of the PV function will come into play with annuities: the pmt and type arguments. pmt takes the periodic payment and type refers to the type of annuity – ordinary or due.

An ordinary annuity has end-of-the-period payments while annuity-due has beginning-of-the-period payments. The difference the type brings to the valuation of the annuity is that with annuity-due, each payment is compounded for one extra period.

Let's have a show of the Excel effects of this cash flow with the following case example.

Cal's two options are to either receive a lump sum of $1,000 upfront or an annuity that can pay out $200 a year for the next 5 years. To evaluate which pick would be more favorable, Cal needs to calculate the present value of the annuity. Using the PV function, here's how to calculate the present value of an annuity in Excel:

The first two arguments are 7% interest and 5 payment periods. The pmt argument is filled with the payment per period ($200 in this case, supplied as a negative figure showing outflow for Cal). The future value is disregarded here while the next argument confirms the annuity type as regular or due. 0 is mentioned in the first instance but you may leave the cell blank or skip this argument as it would default to 0 anyway. For annuity-due, this argument will have to be filled as 1, like in the second instance.

The present value of annuity-immediate is $820 and that of annuity-due is $877. By comparison, it would be more favorable for Cal to take up the lump sum of $1,000.

Note: As mentioned earlier, annuity payments may be at any regular time intervals (such as yearly, monthly, etc. If it were monthly, let's assume, the calculation will require adjustments with the interest rate and number of payment periods (just like how we saw with compound interest). Following is the adjustment made in the formula for you:

For monthly payouts, rate is divided by 12 and nper is multiplied by 12.

For different intervals, the values would be:

• Biannually: 2
• Quarterly: 4
• Monthly: 12 (as shown above)
• Weekly: 52

Method #3 – PV Formula of Perpetuity

Now that you are familiar with annuities, we can transition into the how and what of perpetuities. An annuity with an indefinite number of payment periods is a perpetuity. In essence, the present value of a perpetuity is the present value of the future cash flows (no principal involved).

While there are some examples of past and present perpetuities (e.g. perpetual bonds, Gordon Growth model for stock valuation), they are rare. But even so, here's a simple example to compute the present value of a perpetuity in Excel.

For evaluating the price of two different perpetual bonds, we do not need to use the PV function. Instead comes the simple formula:

The coupon amount is divided by the discount rate and that results in the present value of the perpetuity. Since the payments are infinite, there is no consideration of the number of payment periods.

You could be questioning how we can assess the present value of perpetuities if the payouts are indefinite. That is because as per the time value of money, payments received way ahead in the future have dwindling and very low value enough to be defined in the present.

With that, we have concluded our chapter on calculating present value in Excel. Unlock more Excel chapters by reconnecting with us. We've got a book-load!