Excel is one of the programs that you can use to carry out different tasks, including those that have to do with economics, investments, accounting... Focusing on investments, two of the most used functions are IRR and NPV. But, How to calculate NPV and IRR in Excel?
That is what we are going to explain to you below so that you know how the formulas should be applied in Excel so that the data appears appropriately and is correct. Let's do it?
What is NPV
Before giving you the formulas to calculate with Excel, it is important that you know what we are referring to with each term and above all that you understand well both what it means and the interpretation that should be given.
NPV stands for Net Present Value, which is an investment criterion. We could say that this tool helps us know what the economic viability of a project is.
It can be conceptualized as the value that determines all positive and negative cash flows to analyze an investment. In other words, they take into account income, expenses and capital costs to know if an investment is possible or not.
The results that can be obtained by applying the formula are three:
- Impact. When it indicates that the investment you want to make can be profitable and therefore it is good to move forward with the project.
- Negative. When the project to invest is not a good idea and it would be better to withdraw.
- Zero. A zero NPV implies that the investment is neither good nor bad. In this case, other types of values ​​that could unbalance the balance to one side or the other would have to be taken into account.
What is the IRR
Now that we have commented in general terms and without going into details, what NPV is, we are going to do the same with IRR.
IRR stands for Internal Rate of Return. and, just like they go, it is used to analyze the viability or profitability of an investment.
It is defined as the percentage of income to be obtained from an investment. That is, what you earn for having invested. Or what is lost, it must be said.
Of course, what you win or lose is often relative, because you don't really know until that moment arrives. But it is an indicative value that many professionals use to make decisions (combining it with other tools).
As for its result, as with the NPV, here you will also have three results:
- IRR greater than zero. It will indicate that the project is viable and acceptable because an adequate profitability will be earned (the higher the figure, the better).
- IRR less than zero. So the project is not viable at all. In fact, it will end up ruining you because you will not make money, but rather you will end up losing it.
- Equal to zero. As with the NPV, here the investment will not give you profits or losses. So to tip the balance to one side or the other, other values ​​or calculations are taken into account that can help make the decision.
How to calculate NPV and IRR in Excel
Now, we are going to focus on the Excel program and make the NPV and IRR calculations according to the formulas that work in this program.
We start with the IRR function, that is, the internal rate of return of cash flows.
To do this, you have to put the following in Excel:
=IRR (matrix containing cash flows)
If no estimated IRR value is assigned, what the program does is use 10%. But in reality you can put whatever value you want on it. Of course, the formula would change and would be like this:
=IRR (matrix containing cash flows; estimated value of the IRR)
Of course, you will have to create the matrix that contains the cash flows yourself so that it determines from which part to which part the data should be taken and that it is consistent with that flow.
For example, imagine that you open an Excel document and in column A you put the period. In column B you establish the flow of funds, which would be that cash flow.
Well, in the formula you cannot put =IRR (matrix that contains the cash flows) but you have to establish from what value to what value it should go.
In this example that we are telling you, if column B is the flows, it will start from B2 (column B, line 2) and continue until the end.
Therefore, the formula will be: =IRR(B2:BX), with X being the last number you have as flow (B10, B12, B242, B4...).
We recommend that you establish the IRR in a column such as C, D, E... but that it allows you to make changes to the flows or other data without deleting it.
Now moving on to calculate the NPV, You should know that in Excel the function that calculates it is called VNA. As with IRR, Excel executes the function in a certain way. And it will take into account future payments, so it is common for the first value you indicate in the payment matrix to be updated to the interest rate.
The formula is as follows:
=NAV(discount rate; matrix containing the flow of future funds)+ initial investment
Now, how do we interpret it with the data you want to provide.
On the one hand, you will have to enter the period and the flow of funds (as was the case with IRR). But that alone is not enough.
You also have the discount rate. This should normally be given to you and always, even if they give you 5%, 10%, 20... you must divide it by 100 to place it in the formula: 0,05, 0,1, 0,2...
On the other hand, you have the initial investment. You do have this because it will be the first value of the flow of funds, that is, if you place it the same as we mentioned before, it would be B2.
Now that you know how to calculate NPV and IRR in Excel, it is time for you to practice to see if you actually get the formula and that the data is correct. This way you can make good decisions in your business. Do you have any doubt left?