How to avoid the unnecessary pitfalls in your ExCel cash flow valuation

There are a lot of pitfalls when using ExCel’s valuation and data analysis formulas, and here are the easiest ones to avoid.

Valuation formulas

XNPV formula: It will return the net present value if you give it a row with cash flows, a discount rate, and a set of dates that match with your cash flows.

The valuation starts in the first cell of dates for your cashflows. So if you want to know what the value is at 31.03.2018, but your cash flows start at 30.06.2018, you need to create a cell for the date with 0 cash flow for it to work.

XIRR formula: This works the same way as XNPV above. You give the same inputs, (except for the discount rate), and it gives you the approximate IRR for the cash flows.

If you have a cash flow that fluctuates between negative and positive numbers, don’t trust the IRR this formula spits out. This has nothing to do with ExCel, but with the concept of IRR itself.

The cash flow errors you want to avoid

For your valuation to be correct, the cash flows need to be correct as well. The most common mistakes for cash flow models include paying out cash that doesn’t exist in the model. Luckily, there are easy ways of fixing this.

Min function: This chooses the minimum of two values. Smart if you want to build options, or if you want to pay back debt. But only as long as there is debt left to pay back, and as long you have cash.

A simple example of the last could be =Min(Cell with debt left, Planned debt payback, Cash available).

Max function: The same use as the Min function, just the opposite. Combinations of the two are quite powerful, and you can create very functional expressions.

In the first example below, the company has cash to pay down its debt each year.
In the second example, there isn’t enough cash to pay back all the debt it’s supposed to. If the model doesn’t stop reducing the debt size, this will result in a wrong valuation.

In the third example, the debt is already paid back, so no cash flow should go to the downpayment of debt.

Data and analysis

This is where you get to use the model you have put hours of work into. And if the model is set up correctly, these results should give some intuitive results.

Data validation: Alt+a+v+v  You often choose to use data validation with an input cell. You want people to be able to change the inputs, but not to something that doesn’t make sense.

If you have three scenarios for Opex, you want the user to be able to choose those three. You don’t want him to put in a negative number that is clearly unrealistic.

You get several “validation” options here. The most useful is to make it choose from a list that you have already created. If you have three opex scenarios somewhere in your model, called HighCase, LowCase and BaseCase, you link to these cells. Then you get a drop-down menu in your input cell where these are the only options to choose from.

Goal seek: Alt+a+w+g This is very useful if you want to look at scenarios. Let’s say you are buying a company that sells one product. You want to see how low the price of this product can move for the value of the company to fall down to $400m. This is where you do it.

Data table: Alt+a+w+t This is useful to check several scenarios. If we continue with the acquisition of the company from above, you want to know what happens with the price if the product price is $4, $5, $6. or $7.

Here is where you go back to the ExCel options and change the formula updates to Automatic, as I showed at the beginning of this guide.

Creating a data table for the first time can be quite tricky, so there’s no shame in following a tutorial from youtube the first times you try.

Macros and circular references

One common problem in a model is to have circular references when calculating cash or debt levels.

Many people will use macros to avoid circular references when calculating optimal debt sizing for a company. If you need this, know that your macro won’t run every time ExCel does an iteration in a data table for example. Which means your numbers will come out wrong.

This doesn’t mean that you should never use macros, but you need to understand how this limits your models later.

Building macros: I’m not going to do a full guide on how to build macros here. It’s pretty easy if you have some basic knowledge of programming. Otherwise, you may struggle.

More articles in the same series:

Over the next week, I will post more articles in the same series, helping you to more easily build a model.

• Basic ExCel shortcuts you need to learn when you start in investment banking
• How to quickly nail the formatting of your first EcXel model in banking
• Understanding how to link correctly and roll out big ExCel models quickly
• How to avoid the unnecessary pitfalls in your ExCel cash flow valuation
• Error checking in EcXel: Tools to discover your mistakes before it’s too late