A sum of money is not the same from one period to the other in time. For example, if you won $500 in the lottery 50 years ago you would have been richer than if you had won it yesterday. This rule reflects the power of accumulated interest.

To evaluate the profitability of an investment project, you may use the net present value (NPV). NPV is the calculation of the net cash input that a project should obtain in today's dollars, considering the value of money over time. Although it is possible to calculate NPV with conventional mathematical functions, Excel has a dedicated function to calculate NPV.

The following article explains how to use this function in the case of a calculation of NPV with cash flows that are collected in the same periods each year (end of period), and in the case of a calculation of NPV with cash flows that are collected in the same periods every year (but at the beginning of the period), and in the case of a calculation of NPV, with cash flows happening at different time periods.

To take the previous example, if you had won $500 some 50 years ago and you had placed all that money in an investment vehicle with an annual return of 5%, it would have grown to $5,733 today, or $500 * (1 + 5%)^50. 

Key Takeaways

  • To look at the value of an investment over time, take a look at the net present value (NPV).
  • The NPV looks at what net cash input a project should obtain in today's dollars while taking into account how accumulated interest impacts money over time.
  • You can calculate the NPV by hand but excel provides a function that enables faster and more accurate calculation.
  • Generally speaking, a project that has an NPV of better than zero dollars will add to a company's value and is probably worth the investment, all other factors considered.
  • A project with an NPV of less than zero dollars is likely not valuable for a company and is most likely one to pass on.

How to Calculate NPV in Excel Using XNPV Function

To calculate the NPV of an investment project, you must consider the present value of all cash receipts and all cash disbursements related to the project. Generally, if the result is greater than zero dollars, we should accept the project. Otherwise, we should choose to drop it. In other words, realizing a project with an NPV greater than zero dollars will add to a company's value.

The choice of a discount rate is usually linked to the level of risk for the project. If the project is equivalent to the average risk of the company, we can use the weighted average cost of business capital.

Thus, if we take the table of cash flows related to an investment project:

.

.

Additionally, if we assume a 10% discount rate and a machine lifespan of 15 years, this is the result:

The Excel net present value formula:

NPV = -$232,000 + $38,800 (1+0,10)-1 + $38,800 (1+0,10)-2 + $38,800 (1+0,10)-3 + … + $38,800 (1+0,10)-15

NPV = $63,116 

This means that by choosing to allocate $232,000 today to replace the machine, the company should ultimately experience growth of $63,116.

Calculating the present value of an amount to be received in the future is called discounting. When the future value of a present amount is calculated, it is called capitalization.

Cash Flows at the End of the Period

In the event that the cash flows are always collected on the same date each year—the end of a period—you can simply use the basic Excel function, NPV. This function (as illustrated below) requires two parameters: the discount rate and the cash-flow range. 

Cash Flows at the Start of the Period

In the event that the cash flows are always collected on the same date every year, but an earlier date than the period's end, simply multiply the NPV by (1 + rate). Indeed, the basic Excel function assumes that the cash flows are received at the period's end. Similarly, in such a scenario, the first stream should be considered at time 0, so we could simply exclude the NPV function and add it to the NPV of three other flows, which would then be considered at the cash-flow period end but with a lag of a year (see example below).

Cash Flows at Different Moments in Time Using XNPV

Finally, if you try to calculate an NPV of a project that generates cash flows at different moments in time, you should use the XNPV function, which includes three parameters: the discount rate, the series of cash flows and the range of dates when the cash flows are received in time. 

Using Excel can make calculating NPV quick and (relatively) easy.