X

Net Present Value and Internal Rate of Return

Introduction

If you are a private individual looking to invest, you will find a multitude of investment opportunities from which to choose. How do you compare different opportunities and decide which option is best? Two common methods are using a Net Present Value (NPV) and/or Internal Rate of Return (IRR) calculation.

This article will attempt to explain the basic concepts and show you, step by step, how to use them. Firstly, let us define some terms and then we can explore how they are used.

Net Present Value (NPV)

NPV is a measure of the value of a property/project/company that has income/cash flows/returns over a number of years, in today’s terms. It takes into account the initial cost/investment, the net cash flows and the sale price/salvage value. The rationale is that money now is worth more than money later and the later the money, the less value it has now (a bird in the hand is worth two in the bush).

The result of a NPV calculation is a Rand-value figure that establishes whether an investment in a particular property/project/company meets your desired returns or not. A NPV of zero means the investment exactly meets your desired return, while a positive NPV indicates a higher return and negative NPV a lower return.

zero = acceptable                          positive = good                      negative = bad

The other useful feature of the NPV is if the result is negative, then the figure shows you by how much the initial investment needs to reduce to match the desired return (i.e. NPV of zero). A positive NPV is, effectively, how much extra you could pay (but don’t) and still achieve the desired return.

Internal Rate of Return (IRR)

The IRR is a measure of the overall yield/return/interest on an initial investment that a particular property/project/company will achieve over a period of years, expressed as a percentage.

There is a direct relationship between NPV and IRR, whereby if the IRR is the same as the desired return, the NPV will be zero (indicates the desired return is met). If the IRR result is higher than the desired return, the investment is better and if it is lower, it does not meet the desired return.

Discount Rate

The discount rate is a critical input variable when calculating NPV and it is important to use an accurate figure. It is your desired rate of return over the life of the investment, factoring in inflation and the risk of investing in a particular asset (risk premium). It can also be described as the rate by which future income streams are discounted (reduced).

desired return + inflation + risk premium = discount rate

If the funds invested are in cash, a good way to think of the discount rate is ‘opportunity cost’ – what returns are you ‘missing out on’ by choosing one investment over another?

The following table shows the average returns, inflation and respective risk premium for each of the four major asset classes in South Africa:

Asset Class

Real Return*

(after inflation)

Average Inflation

Risk Premium

Discount Rate

Cash

0 – 1%

5 – 6%

0 – 1%

5 – 8%

Bonds

1 – 3%

5 – 6%

1 – 2%

7 – 11%

Property

2 – 4%

5 – 6%

2 – 7%#

9 – 17%

Equities

7 – 9%

5 – 6%

3 – 5.5%

15 – 20.5%

* Source: http://www.savingsinstitute.co.za/resources/overview-of-asset-classes/

# risk premium for directly owned properties is 5 – 7% and for portfolio properties 2 – 4%

When comparing investments from different asset classes, use the appropriate discount rate for the respective investment type (e.g. use 9 – 17% for property and 15 – 20.5% for equities).

The risk premium that is applied, and consequent discount rate, is ultimately down to individual choice – there is no single one-size-fits-all. It is also important to understand the fundamentals of the investment, as applying a larger risk premium will not protect you from a poor decision.

Calculating NPV & IRR – a Practical Example

The easiest way to calculate NPV and IRR is using an Excel spreadsheet. For our example, we will consider a property bought for R1 million today at a 10% yield and sold after five years for the same 10% yield. We will use a 12% discount rate, meaning that our desired return is 12%.

Property Details:

Purchase Price

R1 000 000

Net Income, year 1

R100 000

Net Income, year 2

R107 000

Net Income, year 3

R114 490

Net Income, year 4

R122 504

Net Income, year 5

R131 080

Sale Price, year 5

R1 310 796

Discount Rate

12%

Creating an Excel Spreadsheet

Open up a new Excel spreadsheet and make a table with SIX columns (A to F) in row 1 with the following headings:

  1. Year (fill in years 0 – 5  in rows 2 – 7)
  2. Investing Cash Flows
  3. Operational Cash Flows
  4. Total Cash Flows
  5. NPV (Net Present Value)
  6. IRR (Internal Rate of Return)

It should now look something like this:

A

B

C

D

E

F

1

Year

Investing Cash Flows

Operating Cash Flows

Total Cash Flows

NPV

IRR

2

0

3

1

4

2

5

3

6

4

7

5

Now complete the tables as follows, using the details for each property (figures in table below):

  1. In cell B2, put in the Purchase Price as a negative number.

  1. In the Operating Cash Flows (column C), put in each year’s income (Year 1 – 5; row 3 - 7).

  1. In cell B7, put in the Sale Price (income in Year 5 capitalised at 10% = net income year 5 divided by 0.1).

  1. Add up the Investing & Operating Cash Flows and put the result in the Total Cash Flows (column D).

The resulting table should now look like this:

A

B

C

D

E

F

1

Year

Investing Cash Flows

Operating Cash Flows

Total Cash Flows

NPV

IRR

2

0

-R 1 000 000.00

-R 1 000 000.00

3

1

R 100 000.00

R 100 000.00

4

2

R 107 000.00

R 107 000.00

5

3

R 114 490.00

R 114 490.00

6

4

R 122 504.30

R 122 504.30

7

5

R 1 310 796.01

R 131 079.60

R 1 441 875.61

Excel Formulae

Using Excel to calculate the NPV and IRR is very easy, as the program has the mathematical formulae built in, so all we need to do is enter the data and apply the appropriate Excel formula.

The Excel formula structure for NPV is:

=NPV(discount rate %, range of cash flows) – initial investment

Please note: with the NPV calculation, the initial investment is deducted after the range of cash flows.

The Excel formula structure for IRR is:

=IRR(range of cash flows, guess %)

Please note: with the IRR calculation, the initial investment is included in the range of cash flows as the first term.

For the example, select the appropriate cell and enter the corresponding formula:

Cell E7:             =NPV(0.12, D3:D7) +D2              or            =NPV(12%, D3, D4, D5, D6, D7) +D2

Cell F7:             =IRR(D2:D7, 0.1)                           or            =IRR(D2, D3, D4, D5, D6, D7, 10%)

The table should now look like this:

A

B

C

D

E

F

1

Year

Investing Cash Flows

Operating Cash Flows

Total Cash Flows

NPV

IRR

2

0

-R 1 000 000.00

-R 1 000 000.00

3

1

R 100 000.00

R 100 000.00

4

2

R 107 000.00

R 107 000.00

5

3

R 114 490.00

R 114 490.00

6

4

R 122 504.30

R 122 504.30

7

5

R 1 310 796.01

R 131 079.60

R 1 441 875.61

R 152 089.82

15.81%

Results

If you have entered all the information correctly, the resulting figures should be:

Net Present Value: R152 089.82               

Internal Rate of Return: 15.81%


15 Nov 2016
Author Duncan Howard
10 of 11