How to Calculate the Present Value in Microsoft Excel

In financial terms, the Present Value refers to the total amount that a series of future payments is worth at the present time. To calculate this value, you use the PV function which returns the present value of an investment.

Syntax of the PV function is as follows:

=PV(rate,nper,pmt,[fv],[type])

Rate refers to the interest rate per period, usually per annum. For example, if you invest in a fund, such as an education fund for your kids and the fund pays 10 percent annual interest rate with monthly payments, your interest rate per month is 10%/12, or 0.83%. You would enter 0.83%, or 0.0083, into the formula as the rate.

Nper is the total number of payment periods in an annuity. For example, if investment is over a 10 year period, and makes monthly payments, your investment has 10*12 (or 120) periods. Then enter 120 into the formula for nper.

Pmt is the fixed contribution payment you need to make each period. This amount cannot change over the life of the investment. If pmt is omitted, you must include the fv argument.

The fv and type arguments are optional arguments in the function (indicated by the square brackets). The fv argument is the future value or balance that you want to have after making your last payment. If this value is omitted, Excel assumes a future value of zero (0).

The type argument indicates whether the payment is made at the beginning or end of the period: Enter 0 (or omit the type argument) when the payment is made at the end of the period and use 1 when it is made at the beginning of the period.

Let’s assume you have the followings:

Interest Rate (Rate) is 10%

Total number of payment periods (nper) is 10 years or 12*10

Fixed contribution payment you need to make is $500 per month.

Then the formula would look like:

=PV(10%/12,10*12,500,,0)

And it produces a result of -$37,835.58

Excel produces a negative result because it represents money that you would pay, an outgoing cash flow.

What the result means is that if you’re asked to pay an up-front amount that is less that the present value, it’s a good deal. If this up-front amount is more than the present value, you should choose to pay an annuity or a series of payment. This is because the present value of the annuity, -$37,835.58 in this case, is less than what you are asked to pay.

Let’s look at another example and say you’re purchasing a vehicle. You have 2 choices to finance your purchase. You can either pay up-front which cost you $20,000. You can also finance the purchase of your car by paying $400 per month for 5 years at an interest rate of 10%.

To compare the true cost of this purchase, you need to compare the present value of the monthly payments to the $20,000 you would pay today. The formula in this case would look like:

=PV(10%/12,5*12,400,,0)

Which gives a result of -$18,826.15

This means paying $400 per month for 5 years is a better deal as you would be $1,173.85 ($20,000 – $18,826.15) better off.

Leave a Reply