An NPV Profile or Net Present Value Profile is a graph that looks like this:

npv_profile_1

The horizontal axis shows various values of r or the cost of capital and the vertical axis shows the Net Present Values (NPV) at those values of r. The point at which the line or curve crosses the horizontal axis is the estimate of the Internal Rate of Return or IRR.

To prepare an NPV Profile we need to have set up a capital investment appraisal example and then either set up a table showing the calculation of the NPV at a range of different values of r, like the ones reflected in the NPV profile above or, as we are discussing here, setting up a Data Table in Excel to do most of the work for us!

Base Case Data

npv_profile_3

We can, for example, leave this table as it is since cell B15 contains the NPV value and all we have to do is to change the value of r in cell B17 until we find an estimate of the IRR of this project. You can see that I have programmed the IRR in cell B16 anyway but the NPV profile shows the IRR in the context of the whole project: how large or small the IRR is versus the organisation’s cost of capital, r; and so on. That is, the NPV Profile contains additional information.

The file I have created, download link below, then contains two NPV Profiles: you have seen the graph at the start of this page and the base case data on which it is based. Using this information I have prepared a one way data table to give me the data for that NPV Profile:

npv_profile_4

This NPV Profile table shows us the NPVs over a range of r values:

  • if r = 2.5%, the NPV for this project will be $1,610,235 …
  • if r = 6.5%, the NPV for this project will be $677,034 …
  • if r = 12.5%, the NPV for this project will be $(334,355) …

Creating the One Way Data Table

Creating the NPV Profile of a ten year project by creating a one way data table

To create this one way data table do this:

  • set up your input table or base case model, as above, in the range A3:B18
  • including headings, the NPV Profile tables is in the range K3:L23
  • in cell L4 enter =B15, which is the NPV result from the input table
  • in cell K5 type the initial value of r you would like your NPV Profile to start with, 2.5% in my case … this will vary from case to case … and then fill down by increasing in steps of 0.5% or 1% or x% as you choose
  • select the range K4:L23
    • Data
    • What if Analysis
    • Data Table
  • leave the Row Input Cell box empty
  • in the Column Input Cell box enter B17 which is the r value from the input table
  • OK

Done!

Now draw the graph of your NPV Profile table and that’s it: you can see your estimate of the IRR for this project, you know r = 10% to start with so you can see your margin of safety: with r = 10% and IRR = 10.26%, is just 0.26%. This is very risky because it means that if you have made just a small error with your assumptions and so on, this project could easily turn from having a positive NPV to having a negative NPV.

Now you can see this margin of safety very clearly, you can make changes to the project, rework your ideas … or accept that things might be tough!

Changing the Input Variables

I have set up this base case so that you can easily change the value of r in cell B17 and I have put the initial cost in its own input cell,B18. You are free to change anything in the base case, of course but I think these are two values that you might most likely want to play with.

Try it! Change r from 10% to 5% or the initial cost to $5 million or $1 million and see how the NPV Profile changes too.

Two Way Data Table

So far so good: we have created a very useful schedule and graph and called it the NPV Profile using a One Way Data Table. Let’s enhance out output now and create a two way data table that will allow us the show, for example, both a range of r values and a range of initial cost values in the same table:

npv_profile_5

When you are dealing with a situation like ours, with a very small margin of safety, it is useful to see options around the base case and we can do that by creating this new table that shows the effect of different values of r AND different initial cost values. In the Excel file I have copied the tab we have just been working on and then done the following to create a TWO way data table:

  • we are using the same base case data as before, from the range A3:B18
  • I have linked the cash inflow data in this worksheet to the cash inflow data in the one way data table worksheet but you are free to change this. This allows you to compare the two worksheets easily.
  • including headings, the NPV Profile tables is in the range K3:T23
  • in cell K4 enter =B15, which is the NPV result from the input table … note it is in a different cell in this two way table
  • in cell K5 type the initial value of r you would like your NPV Profile to start with, 2.5% in my case … and then fill down by increasing in steps of 0.5% or 1% or x% as you choose
  • in cell L4 enter the first initial cost you want to include and then fill this right to cells M4:T4 in steps of $100,000 in my case … use $100,000 or $50,000 or $250,000, you are free to choose
  • select the range K4:T23
    • Data
    • What if Analysis
    • Data Table
  • in the Row Input Cell box enter B18, the initial cost in your input table
  • in the Column Input Cell box enter B17 which is the r value from the input table
  • OK

Done!

Draw the graph of your two way data table now:

npv_profile_2

Conclusions

Not everyone draws NPV Profiles and not everyone has seen one but by using a one and two way data table to prepare them, at least it is not such a daunting prospect to draw them now.

Download the file by clicking this link: npv_profiles_data_tables

Duncan Williamson

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: