NPV Vs IRR — Microsoft Excel
In this article, we will take a core drive of NPV and IRR in finance and accounting using excel. As we understand NPV and IRR in simple way than, NPV and IRR are the two major factors to analyze whether a project or business should be invested in. We can actually analyze the the business condition, is it worthy and it will have good financial health so the investors invested in.
Net Present Value and Internal Rate of Return (NPV and IRR)
NPV or net present value is the difference between the current value of cash inflows and outflows over a period. NPV is used to analyze the profitability of a projected investment or project in capital budgeting, stock investments, and investment
planning.
Starting a new project necessitates a thorough evaluation of the future cash flows that
the initiative will produce. We must calculate the estimated sums and the time frame in which they will be paid or received. Making an informed decision is essential, and this is one of the key factors that determine whether a project is realistic.
Because of the time value of money, estimating a project’s value can be difficult. We already know that money gained today does not equal money earned tomorrow. We can account for this approach is by discounting cash flows and determining their
present value.
To purchase a fixed asset, for instance, a corporation would first estimate the future
cash flows that asset would provide, discount those cash flows, and then tally their present values. The investment is potential and will create value if the amount
received exceeds the initial investment, otherwise, it is invalid from a financial standpoint. A positive NPV indicates a profitable investment and a negative NPV indicates a loss. So, the investment with the positive NPV should be considered.
Formula:
Where,
NPV: Net Present Value
FV: Future Value
R: Discount Rate of Interest and n:Number of Year
Let us take an example to understand NPV better.
Assume that we are investing in a toy manufacturing machine that will produce cash in the next 6 years, all values in lakhs, year 1 – 20, year 2 – 30, year 3 – 30, year
4 – 35, year 5 – 25 and year 6 – 10. After that, we will sell the machine for ₹15 lakh as scrap. Our initial cost is ₹1 crore and discounted rate decided for the project is 10%.
We create a timeline as per the preceding numbers and consider Year 6 as ₹25 lakh because we will get ₹10 lakh form its operations and ₹15 lakh as its scrap value.
The above figure, shows that Year 6 is equal to 25 as it is the sum of scrap value and cash produced, which are 15 and 10, respectively. First, we calculate every year’s PV and add them all, including the initial payment. We got 19.06; it is a positive NPV, so we can go ahead with this project/investment.
Excel has an inbuilt NPV formula, so let us calculate it,
NPV argument
=NPV(discount rate, series of cash flow)
Let’s apply this formula in our example.
As we can see in Figure 5.6, the values of S. No. E and S. No. F are not the same, but they should be equal. For this, we need to make some adjustments.
Currently, we have used all cells, including initial payment cell (Cell C7) for series of cash flow, though it is always same. This will add the initial payment cell with NPV
of Years 1 to 6. The figure below shows, how it works.
=NPV(C2,D7:I7)+C7
The preceding is the formula for NPV.
As we can see in the above figure, both values are the same.
The NPV function should be as follows.
=NPV(discounted Rate, series of cash flow from Year 1 to Year N)- Initial Payment
IRR or the internal rate of return is the compounded rate of return on an investment.
IRR is a discount rate that, in a discounted cash flow analysis, reduces all cash flows’ Net Present Values (NPVs) to zero. The project’s anticipated return can then be
thought of as the discount rate.
The project is approved if the IRR exceeds a predetermined percentage threshold and
it is abandoned if the IRR falls short of the desired level. We get at the calculation by thinking about the definition. Instead of earnings, the IRR employs cash flows more specifically, the cash flows that are pertinent to a project. To make the calculation, we must first determine the discount factor that would result in a project’s NPV being zero.
The greater the internal rate of return, the more favorable an investment is. IRR is the best tool available to comprehend and evaluate probable rates of annual return over time for capital budgeting projects.
Formula for IRR:
Where,
NPV: Net Present Value,
FV: Future Value,
IRR: Internal Rate of Return,
N: Number of Year
NPV is considered zero to find the value of IRR.
Let us take the same example that we used for NPV.
Let us assume that we are investing in a toy manufacturing machine that will produce cash in the next 6 years, all values in lakhs, year 1 – 20, year 2 – 30, year
3 – 30, year 4 – 35, year 5 – 25 and year 6 – 10. After that, we will sell the machine for ₹15 lakh as scrap. Our initial cost is ₹1 crore and discounted rate decided for the project is 10%.
It is difficult to put the formula to calculate IRR manually.
There are two options available, one is to use the IRR formula in MS Excel and the other is to use the Goal Seek Feature in the NPV formula. Currently, the NPV is the same, as shown in the figure below.
Now we will apply Goal Seek in Cell No. C10 of the figure below.
In the figure below, we can see the Goal Seek Dialog Box where set cell C10, to Value 0 because NPV is always zero to calculate IRR by changing cell C2 where we have
entered our discounted rate. Now, Click on OK. We will get our desired IRR in cell C2.
As we can see in the above figure, we have our desired IRR as 16.09%, which is more than
our discounted rate, as decided, it means the project is worthful and we can invest in it.
Augment:
IRR(values,[guess])
Where values are series of cash flow, including the initial cash flow.Now we will put the formula in our current Excel sheet.
As we can see in the above figure, IRR is equal to 16.09%, which is the same as cell C10, which we calculated with the help of Goal Seek.
Comparing multiple projects can be better accomplished by using the Internal Rate of Return (IRR) method, while the Net Present Value (NPV) method may be more suitable for situations where cash flows may change direction or fluctuate between
positive and negative over time, or when different discount rates are involved.IRR is used in percentages and NPV is determined as currency.
The profitability index is derived as the difference between the project’s initial investment and the present value of predicted future cash flows. A project will be deemed more attractive if its PI is higher. A PI value greater than 1.0 is regarded as a solid investment and higher values are associated with ventures that are more alluring.
Formula:
Where,
PI: Profitability Index.
PV (Inflows ): Present Value of Inflows.
PV (Outflows): Present Value of Outflows.
Let us take the same example as we worked with for NPV.
The above figure shows, that the profitability index is 1.19, so we can comfortably invest in this project.
Conclusion
Finally, we will take a descriptive drive of learning NPV and IRR in finance and along with that we also covering the fundamental principles of investment in any business accounting to their financial conditions and health. We can analyze the company financial situation so that investors can invest in that particular business and know that, is it worthy or worthless for him.
