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.
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.
How to Calculate Present Value of Uneven Cash Flows in Excel: 3 Methods
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.
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)^nHere,
- 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.
- Moreover, 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.
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.
- Furthermore, press ENTER.
Therefore, in cell C12 you can see the Present Value.
Read More: How to Calculate Present Value in Excel with Different Payments
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) 
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)
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.
- 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.
Download Practice Workbook
You can download the Excel file and practice while you are reading this article.
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.
Related Articles
- How to Calculate Future Value of Uneven Cash Flows in Excel
- Apply Future Value of an Annuity Formula in Excel
- Calculate Future Value in Excel with Different Payments
- How to Make a Time Value of Money Calculator in Excel
- Calculate Present Value of Lump Sum in Excel
- How to Calculate Future Value of Growing Annuity in Excel
- How to Calculate Future Value with Inflation in Excel
- Calculate NPV for Monthly Cash Flows with Formula in Excel
<< Go Back to Time Value Of Money In Excel | Excel for Finance | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
