How to Calculate Present Value of Uneven Cash Flows in Excel

If you want to calculate the present value of uneven cash flows in Excel, you have come to the right place. Here, we will walk you through 3 easy methods to do the task smoothly.

Download Practice Workbook

You can download the Excel file and practice while you are reading this article.


What Is Present Value?

Present value (PV) is the present worth of a stream of cash flow or money that is to be gained in the future. It is the cash value of today compared to money someday in the future. The concept of Present Value is used in the stock market’s valuation, bond price, financial sectors, and in analyzing different investment fields.


What Is Uneven Cash Flow?

When the amount of cash flow is not similar for the years, meaning that one year’s cash flow is different from another year’s cash flow then it is called Uneven Cash Flow. It is also called irregular cash flow.


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

The following dataset has the Year and Cash Flows columns. Along with that, you can see the Discount Rate in cell C11.  We will use this data table to calculate the present value of uneven cash flows. To do so, we will go through 3 easy methods. Here, we used Excel 365. You can use any available Excel version.

Present Value of Uneven Cash Flows in Excel


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

In this method, we will 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

Using this formula, we will 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.

  • First of all,  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.
  • After that, press ENTER.

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

Present Value of Uneven Cash Flows in Excel

As a result, you can see in the cells D5:D9, the present value for each year.

Next, we have to calculate the total present value of uneven cash flows. To do so we will use the Sum function.

  • Afterward, we will type the following formula in cell C12.
=SUM(D5:D9)

Here, the Sum function adds the cells from D5 to D9. Therefore, all the present values of the cash flows will be added up to a single present value.

Present Value of Uneven Cash Flows in Excel

  • Furthermore, press ENTER.

Therefore, in cell C12 you can see the Present Value.

Read More: How to Calculate Future Value of Uneven Cash Flows in Excel


2. Using NPV Function in Excel

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

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

  • First of all, we will type the following formula in cell C12.
=NPV(C11,C5:C9)

Present Value of Uneven Cash Flows in Excel

Formula Breakdown

  • NPV(C11,C5:C9) → the NPV function uses a discount rate and series of cash flows to find out 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.
  • After that, press ENTER.

As a result, you can see the Present Value in cell C12.

Read More: How to Calculate Present Value of Future Cash Flows in Excel


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. Hare, using the PV function we will calculate the present value for a particular year of investment.

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

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

  • First of all, we will type the following formula in cell D5.
=PV($C$11,B5,0,C5)

Present Value of Uneven Cash Flows in Excel

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, which is the total number of payment periods. Here, 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, therefore, pmt is 0.
  • C5 → is the fv, which is the future value of money. Here, fv is the cash flow in year 1. Therefore, it is $1000.
  • PV(13%,1,0,$1000) becomes
    • Output: ($884.96)
    • Explanation: $889.96 is the present value for year 1. Here, the PV function takes the cash flow as cash outflow or cash you pay out. As a result, the present value becomes negative. Therefore, in cell D5, the negative value appears with a red color.
  • After that, press ENTER.

Therefore, you can see the result in cell D5.

  • Furthermore, we will drag down the formula with the Fill Handle tool.

As a result, you can see in the cells D5:D9, the present value for each year.

Moreover, we have to calculate the total present value. To do so we will use the Sum function.

  • Afterward, we will type the following formula in cell C12.
=-SUM(D5:D9)

Here, the Sum function adds the cells from D5 to D9.

Here, the present values in the cells D5:D9 are negative. As a result, we use a negative sign () before the SUM function so that the total present value of the cash flows becomes positive.

Present Value of Uneven Cash Flows in Excel

  • At this point, press ENTER.

As a result, in cell C12 you can see the Present Value.

Read More: How to Apply Present Value of Annuity Formula in Excel


Practice Section

You can download the above Excel file to practice the explained methods.

Present Value of Uneven Cash Flows in Excel


Conclusion

Here, we tried to show you 3 methods to calculate the present value of uneven cash flows in Excel. Thank you for reading this article, we hope this was helpful. If you have any queries or suggestions, please let us know in the comment section below. Please visit our website Exceldemy to explore more.


Related Articles

 

Afia

Afia

Hello, I am Afia Aziz Kona. I graduated in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology (BUET). I have an immense interest in technical writing and content development, therefore, I am working as a content developer at Exceldemy. In my spare time, I travel, watch movies, and cook different dishes.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo