Awards
Join OfficeTipsAndMethods

Have some suggestions to share with the world?

Join OfficeTipsAndMethods and share your insight in a Comment. You must be a member to comment and all comments are moderated before being published. Membership is free and we would never share your personal information with anyone.

Become an OTM Member

Log In

YouTube Video Channel
OfficeFix
Article Categories

Posts Tagged ‘NPV’

Excel Financial Functions–Calculating NPV

We’d like to welcome back guest blogger Nick Williams. This week and for the next two weeks, Nick will be publishing a series of Excel financial calculation tutorials. Nick is an Access tutor based in the U.K.

One area where Excel is used extensively is in financial forecasting and modeling. It contains lots of financial formulas which can save hours of time.

As ever the trick is to give Excel the right data in the right way … and then let it do the work for you.

In this article we’re going to have a quick look at three of the basic formulas that Excel has in its kit bag IRR, XIRR and NPV.

NPV – Net Present Value

The NPV returns a value amount. You insert all of the forecast cashflows (both investment and return) relating to a project and also your cost of capital. NPV will then tell you if the project will return a ‘profit’. Profit in this context is that it returns more than your cost of capital for the period that your capital is invested. Put the other way round the value of all of the projects future cashflows, today, is greater than nought.

The basis of the calculation is that a pound now is worth more than a pound in future. Your cost of capital (or discount rate) is the difference in value between a pound now and a pound in a year’s time.

To make this clearer a very simple illustration would be:

Imagine you are investing $10 today. You think that it will return $12 in one year’s time. You like to make at least 10% return on your money when it is invested so your cost of capital is 10%.

The net present value of the $12 today is $10.9 (=£11/(1+10%)) so the $12 in one year is worth more than the $10 today, meaning that you should invest as the project will earn you more than the 10% return that you need.

Syntax of the NPV function in Excel is NPV( rate, value1, [value2], [value3], … )

rate is a required parameter and is the discount rate or cost of capital over a single period.

value1, value2,… are values of future income (positive value) and/or payments (negative value). Value 1 is a required parameter and subsequent values are optional. You will need to put a value, even if it is zero, in for each period of the project.

Below is a more complex example. We have a $200,000 investment which returns different amounts each year for seven years.

We put the initial investment in cell B3. Initially we have the forecasted seven years of income in cells B4:B12. The cost of capital or annual discount rate is in cell B2.

After 7 periods our NPV is going to be:

clip_image001clip_image003clip_image005

If we add income for two additional periods, NPV is going to change:

clip_image007