IRR of a Perpetuity

I subscribe to quora.com and on there I find some interesting and useful questions. I came across one such question yesterday and provided an answer. ere is that answer; although this answer is more extensive than the answer I gave on quora.com

The question is, how can one program the IRR of a project in perpetuity. I answered this question with this in the back of my mind: a perpetuity will probably stabilise after n years and if we can predict the value of n then that is an answer to the question.

I set up two simulated projects and you will find both of them in the Excel file that you can download here, link at the bottom of the page. Project 1 is an annuity and Project 2 is a perpetuity. I am only talking about the perpetuity here.

What I did was to set up a project schedule with an initial cost of $1 million in year zero and then for the perpetuity I used the RANDBETWEEN function with lower and upper values of 75000 and 125000 respectively for the annual cash flows. There is no real reason for 75000 and 125000 except that they are either side of 100,000 which is what I set as the cash flows for the annuity.

I started by assuming a five year project, then six years, seven years and so on and eventually stopped at 60 years. As you can see from the file, the cash flows, the NPV and the IRR all change as the project lengthens and as everything settles down.

I expected that at about 30 years I would find that the IRR had stabilised and I wasn’t far off but to be fair, you can see below what I really found in terms of when the IRR stabilised.

To help, I set up the NPV, IRR and NPV profile calculations as well as IRR calculations year by year for the annuity and the perpetuity.

I have attached a screenshot of my initial project but not the second version.

For the perpetuity only

In my simulation the IRR stopped changing after

  • 58 – 60 years with the initial capital cost of $1 million and annual cash flows set by =RANDBETWEEN(75000,125000)
  • 56 – 61 years with an initial capital cost of $0.75 million and annual cash flows set by =RANDBETWEEN(75000,125000)
  • 48 – 59 years with an initial capital cost of $0.5 million and annual cash flows set by =RANDBETWEEN(75000,125000)

The change years vary because I was using random numbers for the perpetuity example.

Since the two projects I modelled were really very similar, I copy that work sheet and left the annuity alone but changed the initial cost of the perpetuity to $2 million and made RANDBETWEEN(275000,325000). You will now see two completely different sets of results now!

Screenshot:

irr_annuity_perpetuity

That’s my tabular version of finding the IRR of a perpetuity where annual cash flows are all positive but different from each other.

Download the file from here: irr_annuity_perpetuity

Duncan Williamson