Search Not Just Numbers

Loading...

Tuesday, 9 December 2014

Excel Tip: Calculating Net Present Value (NPV)

Last week we covered the IRR function, which enabled us to calculate the Internal Rate of Return of a project.

This week I am going to introduce the NPV function which allows us to calculate Net Present Value - a function closely related to IRR.

The Net Present Value of a series of cash flows, gives a value of those cash flows today discounted by a required rate of return.
The required rate of return represents represents the investor's time value of money. This is often the rate of return achievable in alternative investments.

So, for example, if we have the following series of cash flows (the same example as used for the IRR post):

Initial outlay  £20,000
Year 1 positive cash flow  £500
Year 2 positive cash flow  £5,000
Year 3 positive cash flow  £8.200
Year 4 positive cash flow  £9,000
Year 5 positive cash flow  £9,000

and we require a 10% annual rate of return.

The syntax of the NPV function is:

=NPV(rate,value1,[value2],.....)

where rate is the required rate of return and value1, value2, etc. are a series of cash flows at the same regular interval as the rate, so if the rate is an annual rate, then the cash flows should be a year apart.

value1 is required, whereas [value2] onwards are optional. These can be entered as values or refer to a range. Note that these cash flows are assumed to be at the end of each period.

So we could write the function for our example as follows:

=NPV(0.1, -20000,500,5000,8200,9000,9000)

which returns £2,257.25.

More likely though, we will have the values held in a range of cells as we did in the IRR example:

B2  -20000
B3   500
B4   5000
B5   8200
B6   9000
B7   9000

In this case our function would be:

=NPV(0.1,B2:B7)

Obviously, we would normally also refer to a cell to provide the rate as well, rather than enter it directly into the formula.

NPV relates to IRR  because IRR is the rate that produces zero NPV, so:

=NPV(IRR(B2:B7),B2:B7)

will always return zero.




If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

12 comments:

  1. You can travel to an honest store associated with classic gucci replica along with other add-ons to find the classic product you are searching for. You will find a lot of shops which focus on classic bags. Glazing through hermes replica as well as necklaces in order to diamond earrings as well as necklaces, you will discover a variety of jewellery within these types of shops. If you're able to adjustable rate mortgage your self having a high quality attention darkness inside your favorite tag heuer replica, a very great mascara, as well as long-lasting eyeliner, you are able to arranged your own eye away in order to amazing as well as bedazzle. The eye tend to be therefore distinctive an attribute in order to both of all of us, these people genuinely help to make all of us that we're therefore there isn't any stage within getting techniques right here individuals. I really like the actual rolex replica uk for those points eye. For that regular customer, picking out a bags is extremely severe.

    ReplyDelete
  2. Commenting on this appointment, replica handbags Philippe Pascal, President of the Wines and Spirits business group, said: "Pommery is a great Champagne house, which louis vuitton replica has developed its international appeal and rejuvenated its image over the past five years under Jean-Marie breitling replica Lefevre. From now on he will dedicate omega replica himself to the art world, a field he knows well.

    ReplyDelete
  3. informative post!
    learning excel is very important not only for business purpose but it is helpful for many other purposes as well. it has different meanings and uses for different persons. here very easy methods has been told to calculate NPV. this method is helpful not only for professionals but also for students.

    ReplyDelete
  4. informative post! Thanks for share

    ReplyDelete
  5. This calculation or there, I will learn and practice
    juegos de autos gratis ,jogos friv

    ReplyDelete
  6. Regarded as a classically feminine line, Lady replica Tag Heuer watches got its title from the quilted pattern in the patent leather which is named Cannage stitching. It is created of buttery soft and supple quilted lambskin leather with Cannage pattern embroidery. It features dangling silver colour Tag Heuer letter charms which include some baling to this glamorous replica Tag Heuer watches. You will look prettier.Know the outdated sayings "if it's also excellent to turn out to replica watches be true it possibly is" and "you get that which you shell out for" keep precise. It is feasible to be assured that pretty Fendi Spy Bag on that on-line auction, the 1 distinct commencing at $249.00, the one with all the vibrant yellow swiss fake watches tag isn't the real element.

    No 1 would provide an Fendi bags Spy Bag for $249.00.Prada messenger bag is a chic oversized handbag making a fashion statement. Coming in three colours, orange, fire red and white, this Gucci replica handbags is coated in the appealing yet strong calf buck leather. The all round style may be basic and informal, but it means that you can tote this with any outfit in your closet at anytime of the day. Aside from, the huge big has a slimming effect. Unless of Gucci replica handbags uk course you are incredibly skim,oversized bag usually make your personal search delicate in comparison.

    ReplyDelete
  7. http://www.modserap.com/breitling-transocean-chronograph-unitime-pilot-replica-australia-for-sale.html 's by Breitling - Chronomat's Swiss Watch model It's very hard to please a woman. Especially when we come armed with knowledge, taste, and high expectations. It must be even more difficult for such a traditional manufacture to create a dynamic timepiece for modern breitling Super Avenger II replica that includes technological performance and aesthetic appeal.But I must admit that Breitling has done just that with its latest Chronomat's models.

    Since 1865, the watchmakers of Breitling have toiled to maintain their image of perfection and selectivity. The exclusivity of this brand makes it that much more desirable. Known for the precision of their slim movements, breitling navitimer replica is brand that stays true to itself and its traditions, even in today's fluctuating markets.Breitling - Nothing But Watchmaking - was able to create a dynamic, versatile timepiece that is distinctively feminine yet a still a fine timepiece. Their use of different textures within the dial creates an immediate, emotional response that cyclically moves your eyes across its dial.

    ReplyDelete
  8. The second thing is, be sure and send an Replica Watches UK age-send prior to putting in an order need to if the desired watch seems the same as the Replica Watches within the pictures. Convey to the company you will straight away contact your credit-based card business you get stolen if you achieve what that is different from this timpiece within the Replica Watches UK pictures. Also, you need to observe how expert the company was in answering and adjusting your age-send. Whenever they tend not to react to your age-send in the future, then certainly not have business dealings with this company. Forever use a credit card with 100Pct deception security. It's also possible to choose to cover your watch by money shipping in case you are made it possible for to see you buy the Replica Watches car and then make a decision whether or not youare going to pay or perhaps not. Under no circumstances purchase Replica Watches uk below wholesale that won't offer you a money back guarantee.

    ReplyDelete
  9. Article very interesting and easy to understand kizi games , jogos pou , kizi

    ReplyDelete
  10. Excel is best source to store data & files. I am sharing top essay writing services site.

    ReplyDelete