Hi!

I’m writing this after a refreshing weekend of skiing in Breckenridge and Aspen with my former colleagues from Pearl Street Capital Group. I do love building models, but steep turns on good snow, well, that’s pretty special. Hope you find your happy place this next weekend.

Hope you’re doing well and we can catch up soon.

Best,

David

Having trouble with timing of different events in a model?

I recently created a new Master Calendar methodology for a client which has greatly simplified how they did their financial forecasting.

Curious? Contact me for a demo.

What To Do When Excel Cannot Calculate an IRR

I’ve addressed Internal Rate of Return (IRR) calculations before in these newsletters (comparing them to multiples in 2013 and considering whether you can do weighted averages of different IRRs in 2015). But what do you do when Excel won’t even calculate an IRR, when you get the dreaded #NUM! result from an IRR formula?

1. Change the guess
   Microsoft’s documentation on the IRR function notes that the syntax is
   
   =IRR(values, guess)
   
   
   
   Where the values are the cash flows and the guess is your estimate of the IRR. In my experience, most people put in 0.1 (10%) and hope for the best. I’ve found that using -0.9 (-90%) or even -0.1 (-10%) often gives better results.


2. Use XIRR.
   
   
   
   XIRR requires a date stream as well as a cash flow stream. For models where you have odd timing between the cash flows, and particularly places where you have relatively few cash flows to consider, XIRR gives great results.

But there are still times when Excel gives an error that these two solutions cannot address. Why is that? Recall that IRR is essentially asking the question, what discount rate do I need to choose to get the NPV to be zero? The algorithm that Excel uses is quirky. First of all, this article asserts that Excel doesn’t get NPV to zero, but to 1.79E-09. That’s pretty close to zero, but kind of random. It appears that Excel starts with your guess and then adjusts the rate up or down. Excelfuctions.net says that the #NUM! error occurs if the answer fails to converge after 20 iterations. So what to do then?

Recently I created a forecasting system for a home builder. The system takes in hundreds of inputs, obviously, as the company forecasts all the variables and timing around a new residential development. In the end, we calculate an IRR. Some of these projects are built in phases, so the timeline can be six years long: 72 months. And there are odd cash flows at the end: release of significant surety funds, debt repayments, etc. What I found was that, at times, the IRR calculations would return #NUM!.

The solution involves breaking the cash flows into two streams: the positive ones and the negative ones. Then I kept track of what percentage of the positive cash flows we had achieved by each date, and the same for the negative cash flows. I multiplied the two together to get a measure of “cash flow maturity.” Obviously, that number hits 1 at the end of the project. Then I had an input where I could specify that I wanted to calculate the IRR not for all the cash flows (100% of them), but for, say, 99.8% of them. The model calculated the period at which the cash flows hit that input percentage, and the IRR limited itself to that many periods using the OFFSET function like this: =(1+IRR(OFFSET(J452,0,0,1,G458),-0.1))^12-1 where the (1+IRR() )^12-1 changes from a monthly IRR to the more common annual IRR and the OFFSET(J452,0,0,1,G458) clause says “choose the cash flows starting at J452, go down 0 rows, go over 0 columns, and then grab the data 1 row deep and G458 rows wide.” In this case, G458 was the answer for how many periods got us to 99.8% cash flow maturity.

I like this solution. It automated what had been a manual process of cutting off cash flows which the function evaluated and works around some of the arbitrary oddness of the IRR calc in Excel.