Posts Tagged ‘cost benefit’

Calculating the Correct Net Present Value using Excel

Monday, June 20th, 2011

In Six Sigma we are always wanting to be able to show a return on investment to management. Most of the time in Dollars. To do that we need to consider both the cost and the benefits the project will obtain. But costs and benefits do not happen in one lump sum. Usually they fluctuate over time. This is referred to as a cash flow or a cash flow stream. The best tool to look at both the cost and benefits to see what the return would be is calculation what is called “Net Present Value” (NPV).

Definition of Net Present Value (NPV): The difference between the present value of cash inflows and the present value of cash outflows. NPV is used in budgeting to analyze the profitability of an investment or project[1].

In Excel there is a function [NPV(rate,value1,value2, …)] to calculate NPV, but this formula can be misleading if you do not understand what it is doing. The function help on this function explains it all be rarely do we read these unless we really don’t know what goes where. In this functions case most people need to read it. In the above formula the rate is applied to every value listed. That is OK but in many projects the first value is costs/benefits obtained before the end of the first year (or period of the rate) so the rate should not be applied. That why in many projects we list the first year as “year 0”. These cost/benefits should be added to the above formula thus eliminating the rate from being applied.

Example: Lets say you have a project that will cost you $10,000 this year and $2000 next year to implement, but next year you will see a benefit of $500 and the year after that 5,000, Then in the third year $10,000 benefit and in the last two years $15,000 each year in benefits. Lets also say management want to see a rate of return of 10% over a five year period. You want to know if you can meet that with these figures. Below is the table in Excel I would create to show these figures and calculate the NPV.

You can see that the formula calculating NPV has year 0 being added to the formula so the interest rate is not applied to calculating the present value of it this year (year 0 is the present). But year 1-5 we did have the rate applied as we what to make sure that the expected rate of return is met by year five. This show we have meet managements expectations because it is a positive number.

The reason for doing this is because not all investments have cost/benefits in year 0. Take for instance the purchase of a new piece of equipment that the purchase price is not paid for 12 months. In this case year 0 maybe to delivery and setup of the machine which is included in the cost of $12000. Let say on this example we have the same benefits and expected return. Here is the table and calculations for this one:

You can see that this can be big if you do it wrong, so make sure that you apply this formula correctly. In most, not all, Six Sigma projects you will use the first way. But if you make sure to include always a year 0 and put it in the formula even the lower table will be correct using “=NPV(D2,D4;D8) + D3”.

Well there you have what to look out for in using the NPV formula in Excel. If you have questions or comments please feel free to contact me by leaving a comment below, emailing me, calling me, or leaving a comment on my website.


Bersbach Consulting
Peter Bersbach
Six Sigma Master Black Belt



6 Sigma Cost Benefit Analysis

Friday, May 22nd, 2009

This is a feature that we are implementing with each post. It is here because we provide Six Sigma training coaching and support across Arizona, including the Tucson, Phoenix, Scottsdale, and Glendale areas. At this time we would like to thank our friends and clients for their support. If you have landed here looking for our Six Sigma training, coaching or support services in Tucson, then please follow this Six Sigma Training link.

Cost Benefit Analysis Article

Someone asked me for information on Cost Benefit Analysis as to how to implement this in a software project and analyze results.

Cost benefit analysis is the same for any type of project (i.e. Mfg. Process, Service Process, or Software project).  This Analysis can be used at any point in a project and I’d recommend that you do it at the very beginning and then update it at the completion of every step of the DMAIC process. In the “Define” step of the DMAIC process almost all the numbers will be estimates but I’d have a person from finance validate your estimates. That said what do you do?

1.      This all does start in Define where you put together your project charter. In the charter you will define several things that will be associated with Costs. Things like:

  • Project Schedule – Defining each step of the DMAIC processes Start and finish date.
  • Project Team members and the time they will spend on the project.
  • Other Resources: (materials, equipment, floor space, etc.) that at this time you think you might need to complete this project.

2.      Next, from the project charter again, what are the benefits? Now in that charter you will list the benefits but you also need to list the metric that will show the benefit improvement.

  • % decrease in bugs/errors
  • Faster speed
  • New Features
  • Easier customer use

3.      Now you need to convert these to $$$. This is where your team comes in. Team members (which should include a financial person) know best how these translate to $$$. For instance (I am not a Software Expert by any means):

  • Decrease in bugs could translate into a decrease in unhappy Customer calls to Tech support. This decrease the time the company spends on answer these complains. That is $$$ spent on support personal.
  • Faster speed translates into increase performance which increases customer satisfaction. That could translate into increase sales from loyal customers telling friend about the products performance.

4.      Now we go back to the charter and what does management want as a return on their investment and how quickly.

5.      Once you have all of that you can build a excel Spread sheet to see, and track, your projects performance. In excel there are functions for the NPV & IRR listed below.


























Return wanted=


Net Present Value



Internal Rate of




Now you can see in the example that our expected rate of return is 14% which is higher than what management wanted which was 6% Note the Financial person can help you do the numbers the way your companies management understands them. The hard part is working the costs and benefits out to values everyone will agree to. I’d be conservative at first. By in the end you your results will look much better.

If your business is located anywhere in the World including the US, Tucson, Oro Valley , Oracle, Phoenix, Glendale, and Scottsdale, Arizona or beyond and you would like to learn more about our Six Sigma training, coaching and support services please call Bersbach Consulting at 1-520-721-2077 or SKYPE me  Now!

My status