NPV Calculator and IRR Formula in Excel - Excel TV
Excel TV
Share The LOVE

NPV Calculator and IRR Formula in Excel

Excel makes it possible for simple buy / not-buy or do / not-do financial decisions very easy to arrive at. The underpinning concept behind making such decisions is that a dollar tomorrow is worth lesser than a dollar today. That’s right, it’s because of the additional return you can get by “investing” a dollar today.

There are two notable functions in Excel which are particularly helpful in evaluating financial decisions: NPV and IRR. Yes, for the uninitiated in Finance, they might appear a bit off-putting.  But don’t you worry, Rick Grantham is here to teach us the A, B, C through to Z of all this.

What are you waiting for? Let’s get started.

1 – The Setup

The setup is easy. Given the situation you are evaluating, all you need to know are two things:

  1. What amount of cash would be coming in / going out and in which year,
  2. What rate of return would you expected if you invested your money somewhere else.

As an illustration of the first part, look at the data in the picture.

1

LawnMower & Co. is considering buying a new type of lawnmower and these are the costs and benefits that will result. Note that time here represents the year. The second part seems a bit tricky, but we’ll explain a workaround to tackle this. For now, we will assume a rate of 15%.

2 – The Calculation

The first step in evaluating this financial situation would be to calculate the net cashflow or the net benefit for each year. Since we are already taking cost as negative numbers, we can just sum the cost and benefit to arrive at the figures required.2

Net Present Value (NPV) is the widest used metric when it comes to making financial decisions. All it does is that it sums up the entire net benefit to be realized over many years but in today’s dollars. We use the following formula to calculate NPV in Excel:

3

=NPV(rate,values) + A

NPV is a built-in formula in Excel. For ‘rate’, we will refer to the cell containing the rate of return, i.e. 15% in our case. For ‘values’, we will select the array of net cashflows or net benefits from Year 1 till the end. ‘A’ is just the net benefit at Year 0, or today.

Internal Rate of Return (IRR) is another metric widely used in evaluating financial decisions. It simply calculates the rate at which NPV would become zero. Practically, it gives the rate of return at which you should be indifferent in your buy / not-buy or do / not-do decision. The formula is very simple. =IRR(values), where ‘values’ is the array of net benefits from Year 0 till the end.4

3 – Evaluation

When we evaluated our example, the picture shows what we got. The NPV is negative. It means that if we can fetch 15% return on our investment somewhere else, than you should not buy this lawnmower.

5

4 – The Tricky Case

In big corporations, their Finance or Treasury departments are responsible for providing this figure. But for most small business owners, that’s not an option. In this case, IRR comes in handy. IRR gives the rate at which you will be indifferent between buying or not-buying. So, if you know you will always get less than 13.1% return on your money somewhere else (let’s say a bank deposit or some other investment), than surely go for this lawnmower.

What’s next?

Get your hands dirty and learn this awesome technique. Try out the example Rick explains in the video. And when you get comfortable with this, start employing it in your major financial decisions.

And do not forget to share this technique with your friends or colleagues, and write to us with your experiences in the comments section below.

>

Excel Power User's Quick Guides

by Szilvia Juhasz
FREE Download

"Laminate Worthy" Quick Guides for the Excel Power User. Includes:

  • Shortcuts & Timesavers for the Power User
  • Quick Reference Guide for Lookups and Conditional Calculations
x