How to calculate present value in Excel with different payments

Cash flows represent the inflow of cash for a business. Regular cash flow and proper planning thereof are very crucial for all businesses. However, in the real world, there is no c

How to calculate present value in Excel with different payments

Cash flows represent the inflow of cash for a business. Regular cash flow and proper planning thereof are very crucial for all businesses. However, in the real world, there is no certainty of the amount or the timing of cash flows for an active business. This means a cash flow of one year would not be the same as of other years and can vary month on month, year on year, and so on. This variation or fluctuation in regular cash flow is referred to as uneven cash flows. In order to get an accurate picture of a business, we need to calculate the present value of uneven cash flows. PV calculation will even out this variation and timing of the cash flows.

Apart from a business, one can relate the concept of cash flows with most of the assets. And most assets generate uneven cash flows. One good example of an uneven cash flow is the dividends on the common stock or interest from a floating-rate bond.Table of Contents

  1. Present Value of Uneven Cash Flows
  2. Formula to calculate PV of Uneven Cash Flows
  3. Example

Present Value of Uneven Cash Flows

The timing and amount of cash flows can be uneven in the real world. Such cash flows are termed as uneven or irregular. We can also say that the cash flows that dont adhere to the principles of annuity are uneven cash flows. For example, if the cash flows of a company are $50, $50, $40, $70, and $70, these are uneven cash flows.

So, when cash flows are unequal and irregular, the usual formulas to calculate the present value or annuity wont work. Therefore in order to calculate the PV (present value) of such uneven cash flows, we need to calculate and arrive at the present value of each cash flow separately. And then finally, add all the resultant values to get the PV for all the cash flows under consideration.

This is what makes the calculation of the PV of uneven cash flows cumbersome and time-consuming. However, we do have online calculators and excel to speed up the calculation.

Formula to calculate PV of Uneven Cash Flows

As discussed above, to calculate the PV of all the cash flows, we first need to find the PV of each cash flow. And then add all those present values.

For calculation, you can use PV Uneven Cash Flow Calculator.

Following is the formula to calculate the PV of uneven cash flows:CF0CF1CF2CFNPV=++++(1 + r)0(1 + r)1(1 + r)2(1 + r)N

In simple words, we can put the above formula as:

PV = Sum of CFn/(1 + r)^n

In the above formula,

n is the number of years, CFN is the cash flow for the year,and n and r is the discount rate for the year. The discount rate is generally the opportunity rate or interest that an asset could generate elsewhere.

You can also take a short quiz here to check your learnings on the topic:

Quiz on Present Value of Uneven Cash Flows

This quiz will help you to take a quick test of what you have read here.

Start Quiz

Question

Your answer:

Correct answer:NextYou got {{SCORE_CORRECT}} out of {{SCORE_TOTAL}}

SHARE YOUR RESULTSShareTweetPinEmail

Your Answers

Example

The cash flows of Company A for next five years are $1,500; $1,850; $2,100; $2,500 and $2,950. Assume the discount rate is 15.75%. Now, calculate the PV of these uneven cash flows.

Putting the values as per the formula above

PV = [$1,500/(1+15.75%)1] + [$1,850/(1+15.75%)2] + [$2,100/(1+15.75%)3] + [$2,500/(1+15.75%)4] + [$2,950/(1+15.75%)5]

PV = $1,295.90 + $1,380.80 + $1,354.12 + $1,392.69 + $1,419.77

So, the present value of the uneven cash flows is $6,843.27.

In case we find the manual calculation difficult, you can take the help of several online calculators available online. To avail of online calculators, all we have to do is put the details in the relevant boxes, and you will get the result automatically.

Microsoft Excel can also be used to easily compute the present value of uneven cash flows. In excel, you need to use the NPV function to compute the present value of cash flows. Here, you need to prepare a worksheet with details, including discount rate, cash flows, etc.

Lets see how to prepare worksheets in excel to get the PV of uneven cash flows.

Now, the excel formula will be =NPV(B1, B3:B6) = 242.16. This will calculate and provide us with the present value of these uneven cash flows, i.e., $242.16.



  • Show References
  1. Present Value of Uneven Cash Flows [Source]
  2. Time Value of Money Terminology [Source]
  3. Present Value and Future Value of Uneven Cash Flows [Source]
  4. Time Value of Uneven Cash Flows [Source]
  5. Present Value of Uneven Cash Flows [Source]

TAGSCash FlowCompounding and DiscountingDiscount RateTime Value of Money

  • PV of Uneven Cash Flows Calculator
  • Terminal Value
  • Discounted cash flow
  • Payback Period (PBP)
  • Incremental Cash Flows
  • Cash Flow Coverage Ratio Calculator

Video liên quan