Determining the initial investment needed to reach a financial target, comparing different investment options, or assessing the current value of a future investment all require calculating present value. Present value represents the worth of a future stream of income at the current time.
Excel provides the PV function, a versatile tool for present value calculations. However, the specific type of investment dictates the variables needed for the PV function. We will cover calculating present values for single cash flows, annuities, and perpetuities.
Method #1 – PV Formula of Perpetuity
Now that you are familiar with annuities, we can transition into perpetuities. A perpetuity is essentially an annuity that continues indefinitely. Calculating the present value of a perpetuity determines the current worth of those infinite future cash flows.
While true perpetuities are rare, here’s how you can calculate them in Excel. You won’t need the PV function for this. 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:
=C4/C3
Step 1: In cell C3, enter the discount rate.
Step 2: In cell C4, enter the coupon amount (periodic payment).
Step 3: In another cell, enter the formula =C4/C3.
The coupon amount is divided by the discount rate, resulting in the present value of the perpetuity. Because payments are infinite, the number of payment periods is irrelevant.
You might wonder how we can assess the present value of perpetuities if the payouts are indefinite. It’s because, according to the time value of money, payments received far in the future have a minimal value in the present.
Method #2 – PV Formula of Series of Cash Flows (Annuity)
Let’s explore how to calculate the present value of an annuity in Excel using the PV function. An annuity consists of a series of consistent payments made at regular intervals. Common examples of annuities include home mortgages and pension payments.
Compared to a single payment, two arguments of the PV function are essential for annuities: pmt and type. pmt specifies the periodic payment, and type indicates whether the annuity is ordinary or due.
An ordinary annuity features payments at the end of each period, while an annuity due has payments at the beginning. The difference lies in the compounding: annuity-due payments compound for one extra period.
Here’s how these differences play out in Excel:
Step 1: Enter the interest rate in a cell (e.g., C4).
Step 2: Enter the number of payment periods in a cell (e.g., C5).
Step 3: Enter the payment per period in a cell as a negative value (e.g., C6).
Step 4: To calculate the present value of an ordinary annuity, use the following formula:
=PV(C4,C5,C6,,0)
Step 5: To calculate the present value of an annuity due, use the following formula:
=PV(C4,C5,C6,,1)
The first two arguments represent the interest rate and number of payment periods. The pmt argument takes the periodic payment (supplied as a negative figure showing outflow). The future value argument is left blank here, and the final argument confirms the annuity type as regular (0) or due (1). If you omit the last argument, it defaults to 0 (ordinary annuity).
Note: Annuity payments can occur at various regular intervals, such as yearly, monthly, etc. For monthly payments, you’ll need to adjust the interest rate and number of payment periods.
=PV(C4/12,C5*12,C6,,C7)
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.
- Weekly: 52.
Method #3 – PV Formula of Single Cash Flow
This method calculates the present value of a single future payment using the PV function in Excel. The PV function determines the amount that future payments are worth today. We’ll focus on a single instance of a future payment instead of multiple payments.
The following formula is for calculating the present value of a lump sum:
PV = FV/(1+i)^n
With Excel, the PV function simplifies the job.
Step 1: Enter the interest rate in a cell (e.g., C3).
Step 2: Enter the number of periods (years) in a cell (e.g., C4).
Step 3: Enter the future value in a cell (e.g., C5).
Step 4: Use the following formula:
=PV(C3,C4,,C5)
The first argument requires the interest/discount rate, the second denotes the number of payment periods, the third argument is left blank, and the future value is entered as the fourth argument.
Also, note that the resulting value of the PV function is negative from the point of view of the investor. You can have the final figure positive by starting the formula with a minus sign like so:
=-PV(C3,C4,,C5)
Single Cash Flow with Compound Interest
If faced with investment options with more frequent compounding, here’s how the present value computation would be altered:
=PV(C3/C4,C5*C4,,C6)
Step 1: Enter the annual interest rate in a cell (e.g., C3).
Step 2: Enter the compounding frequency (e.g., monthly = 12, quarterly = 4) in a cell (e.g., C4).
Step 3: Enter the number of years in a cell (e.g., C5).
Step 4: Enter the future value in a cell (e.g., C6).
Step 5: Use the following formula:
=PV(C3/C4,C5*C4,,C6)
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).
That wraps up our guide on calculating present value in Excel.