How to Calculate Present Value of Uneven Cash Flows in Excel: 3 Methods

Method 1 – Using Generic Formula to Calculate Present Value of Uneven Cash Flows in Excel

Use a generic formula to calculate the present value of uneven cash flows in Excel.

We know that the generic formula for calculating the Present Value (PV) for a particular year is

PV = CFn/(1 + r)^n

Here,

CFn is the cash flow for a particular year
n is the number of years
r is the interest or discount rate for the year

Calculate the present value for each particular year. After calculating all the year’s present values, we will use the SUM function to add all the present values. This will give us the Present Value (PV) of the uneven cash flows.

Let’s go through the following steps to do the task.

To calculate the present value of year 1, we will type the following formula in cell D5.

=C5/(1+$C$11)^B5

Formula Breakdown

C5 → is the Cash Flow for year 1, which is $1000.
$C$11 → is the discount rate, which is 13%. As the discount rate is the same for all the years, we lock cell D5.
B5 → is the number of years, here B5 is 1.
$1000/(1+13%)^1 → becomes
- Output: $884.96
- Explanation: $884.96 is the present value for year 1.

Press ENTER.

As a result, you can see the result in cell D5.

Drag down the formula with the Fill Handle tool.

See in the cells D5:D9, the present value for each year.

Calculate the total present value of uneven cash flows. Use the Sum function.

Type the following formula in cell C12.

=SUM(D5:D9)

The Sum function adds the cells from D5 to D9. All the present values of the cash flows will be added up to a single present value.

Press ENTER.

In cell C12 you can see the Present Value.

Method 2 – Using NPV Function in Excel

In this method, we will use the NPV function to calculate the present value of uneven cash flows quickly.

Type the following formula in cell C12.

=NPV(C11,C5:C9)

Formula Breakdown

NPV(C11,C5:C9) → The NPV function uses a discount rate and a series of cash flows to determine the net present value of a financing system.
C11 → is the discount rate.
C5:C9 → is the series of cash flows.
NPV(13%,$1000:$2300) → becomes
- Output: $5389.54
- Explanation: $5389.54 is the present value of uneven cash flows.

Press ENTER.

See the present value in cell C12.

Method 3 – Applying PV Function to Calculate Present Value of Uneven Cash Flows in Excel

In this method, we will apply the PV function to calculate the present value of uneven cash flows. Using the PV function, we will also calculate the present value for a particular year of investment.

We will also calculate the present value for each year using the PV function and the SUM function to add up the present values for all the years. Hence, we will get the present values of uneven cash flow.

Yype the following formula in cell D5.

=PV($C$11,B5,0,C5)

Formula Breakdown

PV($C$11,B5,0,C5) → based on a constant interest rate, the PV function calculates the present value of a cash flow.
$C$11 → is the interest rate. In this case, it is 13%.
B5 → is the nper, is the total number of payment periods. The payment period is 1.
0 → is the pmt that is the payment made on each period. As there is no payment made in our case, pmt is 0.
C5 → is the fv, which is the future value of money. Here, fv is the cash flow in year 1. It is $1000.
PV(13%,1,0,$1000) → becomes
- Output: ($884.96)
- Explanation: $889.96 is the present value for year 1. The PV function takes the cash flow as cash outflow or cash you pay out. The present value becomes negative. In cell D5, the negative value appears red.

Press ENTER.

Therefore, you can see the result in cell D5.

Drag down the formula with the Fill Handle tool.

See in the cells D5:D9, the present value for each year.

Calculate the total present value. To do so we will use the Sum function.

Type the following formula in cell C12.

=-SUM(D5:D9)

The Sum function adds the cells from D5 to D9.

The present values in the cells D5:D9 are negative. Use a negative sign (–) before the SUM function so that the total present value of the cash flows becomes positive.