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 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 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 50 years ago and you had placed all that money in an investment vehicle with an annual return of 5%, you would pocket $5.733 today, or $500 * (1 + 5%)^50. 

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. (For related reading, see also: Time Value of Money: Determining Your Future Worth.)

The Principle of NPV

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. In general, if the result is greater than $0, we accept the project. Otherwise, we will choose to drop it. In other words, realizing a project with an NPV greater than $0 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 this 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:

.

 

.

And if we assume a 10% discount rate and a machine lifespan of 15 years, this is what we would get:

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 will experience growth of $63,116.

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 first is the discount rate and the second is the cash-flow range. (For more, see: Improve Your Investing With Excel.)

 

Cash flow at the START of the period

In the event that the cash flows are always held on the same date every year, but earlier, 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).

 

Flows at different moments in time

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 first is the discount rate, the second is the series of cash flows and the third is the range of dates when the cash flows are received in time.  

Excel makes calculating NPV quick and (relatively) easy.

 

Want to learn how to invest?

Get a free 10 week email series that will teach you how to start investing.

Delivered twice a week, straight to your inbox.