VNA feature: Become a financial expert.

Key information:

The function VNA Excel allows you to calculate the net present value of an investment based on a flow of funds and a discount rate.

How does it work?

Purpose: The NPV function gives us the net present value by determining an interest rate and future flows.
Characteristics: It is a financial function that allows you to know the current value of an investment and compare it with the initial investment, analyzing whether you are going to gain or lose.
Syntax: =VNA(rate; value1, value2; …)
Arguments:
- rate: Interest rate for a period.
- value1: First value that represents future flows.
- value2: Second value that represents future flows.

Understanding the VNA function

The first thing you should know is that Excel's NPV function does not calculate the “net present value” since it does not consider the initial investment. A net present value corresponds to the present value of future flows minus the initial investment. Therefore, the NPV calculates only the present value of future flows.

Tip Ninja: If you want to calculate the net present value (NPV) of an investment, you must calculate the NPV of future flows and subtract the initial investment.

Let's look at an example. Let's consider an investment of $300,000 with an interest rate of 16% and the following future flows.

As we said previously, to calculate the NPV of an investment we must consider only future flows, leaving aside the initial investment.

Ninja Tip: When you have many periods, in the value1 argument you can select the matrix of future flows instead of writing the five values separately.

The elements of the function are:

rate: F4
value1: C3:C7

That is, the VNA function is:

The result we will obtain will be:

So, the future investment flows have a value of $402,264 today.

Now, we want to know whether or not it is advisable to make this investment. For this, we must calculate the net present value, that is, subtract the investment from the NPV.

Tip Ninja: When we calculate the net present value we must look at whether the investment is positive or negative. In the example we see that it appears positive, so we must subtract that cell. In the event that the investment was negative, the cell must be added.

The elements are:

VNA function:
- rate: F4
- value1: C3:C7
Investment: -F2

That is, the VNA function is:

The result we will obtain will be:

We can see that the net present value is $102,264, therefore, since it is positive, it is advisable to make the investment since we obtain profits.

VNA function with positive and negative flows

You should know that the NPV function allows positive and negative future flow values, that is, it allows future profits and losses, let's see an example.

Let's consider an investment of $100,000 with an interest rate of 5% and the following future payments.

We can see that we have two negative flows, in 2012 and 2015.

The elements of the VNA function are:

rate: F4
value1: C3:C7

That is, the VNA function is:

The result we will obtain will be:

Therefore, the current value of the future investment flows is $97,329.

If we calculate the net present value of this investment, we must subtract the investment ($100,000) from the NPV we obtained.

Therefore, since the net present value is $-2,671, it is not advisable to make the investment.

NPV equal to zero

When we have a net present value of zero, it means that the investment does not deliver profits, that is, there is a loss equal to the investment. The interest rate for which the NPV is zero corresponds to the IRR (internal rate of return).

Let's look at an example. Let's consider an investment of $200,000 with an interest rate of 10% and the following future flows.

The elements of the VNA function are:

rate: F4
value1: C3:C7

That is, the VNA function is:

The result we will obtain will be:

We obtained a NPV equal to 0, so it is not advisable to make the investment. Furthermore, we know that the IRR of these future values is 10%.

VNA with different cells

It may happen that among the future flows, there are empty cells, with text values or any representation that cannot be considered as a numerical value. The VNA function will skip all those cells returning the same value if we had continuous cells of future values. Let's look at an example.

Let's consider an investment of $50,000 with an interest rate of 10% and the following future flows with different cells in between. We will compare the case of flows with and without texts in the middle.

If we calculate the NPV we will obtain the same result if the cells had been omitted, that is, Excel eliminates that year completely. For each matrix we calculate the NPV with the interest rate of 10% and obtain the following:

For both cases we obtained a NPV of $114.741. Therefore, we show that by having a cell with some value that is not identifiable as a number, the function will skip it.

Tip Ninja: VNA is similar to Excel's VA (current value) function. The main difference between them is that VNA allows different future flows, whereas in VA they must be constant. If you want to know more about this function, we recommend the following video.