HIRR Function

This function calculates IRR in a manner that is consistent with the NETPV using the half year convention. If you do not want to use the half year convention the standard IRR function will provide an answer.

Row/Col

B

C

D

E

F

G

H

17

Discount Rate

10.00%

18

0

1

2

3

4

19

Cashflows

-$985

$75

$120

$100

$1,200

20

21

Using the NetPV functions

Contents of left Cell

22

14.55346%

=HIRR(C19:G19)

23

($0.00)

=NetPV(B22,C19:G19,1)

24

12.36620%

=IRR(C19:G19)

(Using regular IRR, understates the IRR)

25

Why the half year convention?

The half year convention adjusts the present value to reflect the fact that cash flows in most situations are received throughout the year rather than on the last day of the year. The assumption of getting all the cash flows at the end of the year lowers the NPV of the project. The half year convention adjusts the NPV and assumes the cash flow is received in the middle of the year. This will in general increase the NPV of the project and is frequently a more accurate description of the actual cash flows