Smoothing capital expenditure in Excel

Often, we assume a project or a business will go on in perpetuity. Well, we don’t have enough columns in Excel to handle that, so once cash flows start to settle into more predictable growth patterns, we will often calculate a final figure. This number, known as the terminal value, is based upon the final period of a cash flow to represent the value of future cash flows after this point in time.

That sounds simple, and the initial formula is, but real life is rarely so straightforward, especially if valuations are involved. This article continues the valuations theme from last month’s article, again considering the idea of a discounted cash flow (DCF). To recap, perhaps the easiest way to think of it is as follows:

• Let’s assume inflation is running at 10% (and we will assume this is after tax as we all earn our wages after tax and increases in spending affect this after-tax wage).
• Something that costs $100 this year will cost 10% more next year, ie, $110.
• Something that costs $110 next year will cost 10% more the year after, ie, $121.
• Something that costs $121 in that year will cost 10% more the following year, ie, $133.10.
• However, they are all worth the equivalent of $100 now (as we “discount” these future values back to their present values).

Note that all of these valuations are for a point of time, not a period. This is a common mistake in modelling. We have to understand when we assume the cash flows will occur.

The three most common assumptions are at the start, the middle, or the end of the period in question. This assumption will obviously vary the overall valuation as a consequence.

Valuations include both cash inflows and cash outflows. Adding up all these positive and negative present values provides a netted off total: the net present value (NPV). The business aim in pursuing a project is to generate a positive return (a positive NPV) for a given rate of discounting, known as the discount rate.

All caught up? Great! Now let’s turn again to terminal value, which typically is calculated in perpetuity (as mentioned above) and uses this formula:

Now that’s fine if the cash flows have indeed settled down, but that’s not always the case (told you real life wasn’t so simple). The most significant figure that often refuses to grow smoothly is capital expenditure. That’s because we often have to acquire large ticket items on a cycle (eg, economic life of four years for vehicles, ten years for plant and machinery) and taking any one year’s figure will not adequately represent upcoming costs. Given the terminal value is often a material figure in the NPV, getting capital expenditure right is essential.

There are ways to smooth out the capex to make it predictable so that we may have a representative number for the terminal value:

• Some simply average the expenditure over time. Depending upon how this is done, this may not consider inflation, nominal or real considerations, or other time-value-of-money issues.
  
• Some use depreciation as a proxy. This is a common approach, especially when calculating tax, but it depends upon whether depreciation is a good approximation to the average (it may not be if an item is still in use that has been fully depreciated) or what method of depreciation is used (eg, straight line or declining balance).
  
• More sophisticated methods use annual equivalent discount factors and segregate the capital expenditure by useful economic lives.

Well, guess what? I am not going to do any of those. I am going to demonstrate another method that will also hold up to scrutiny as I will replace a capital expenditure profile for another “smoothed” one over the same time period that will have an identical NPV. This approach has issues (eg, if the capex profile provided is not representative), but many of the problems associated with this technique could be levelled at any or all of the methods out there.

To demonstrate this approach, I am going to use this Excel file as an illustration.

Consider the following:

Here, I consider ten full years (ie, no “stub” beginning or end periods) with percentages quoted per annum. The same technique employed here may be used for full months, quarters, or half-years, too, provided the percentages are calculated accordingly. Remember, our calculations will have no terminal value as we are doing this in order to calculate what is required for the terminal value.

It’s also worth noting in the data (above):

• The nature of the discount rate is frequently debated. Some will argue that this must be the rate implicit in the equivalent leasing of the expenditure, others the inflation rate in the item, and still others the discount rate of the larger valuation. I will leave that to better minds to argue. That’s the beauty of being a modeller — it’s just an input figure to me!
  
• The growth rate is much more self-explanatory. The idea is to replace the profile pictured with a base “smoothed” capex figure that may then be grown at this growth rate each period. This will produce the same NPV as the original cash flows.
  
• The cash flow timing has three alternatives: Cash flows occur at the start of the period, the middle, or the end. This has an impact on the calculated discounted factors.
  
• The capex numbers are the original outflows. The example file allows you to put a profile in for up to 30 periods, even allowing for an initial delay.

The Start_Period is given by the formula

=IFERROR(MATCH(TRUE,INDEX(Capex_Profile<>0,),0),1)

This formula is the opposite of the usual INDEX(MATCH) syntax. The calculation INDEX(Capex_Profile<>0,) creates a virtual vector (list) of TRUE and FALSE values depending upon whether each item in the range Capex_Profile is blank or zero (FALSE) or not (TRUE).

MATCH(TRUE,INDEX(Capex_Profile<>0,),0) simply returns the first non-zero or non-blank occurrence, and IFERROR is used just in case there are no values. In other words, Start_Period identifies the position of the first non-blank or non-zero in the range.

The Final_Period is given by the array formula (entered using Ctrl+Shift+Enter)

{=MAX(IF(Capex_Profile>0,LU_Counter))}

and returns the largest value in LU_Counter where LU_Counter is the period number (hidden in the graphic above), ie, it gives the final period where the expenditure is greater than zero (this could be replaced by not equal to zero (<>0), for example).

The Number_of_Periods is then easy. It is simply equal to

=Final_Period-Start_Period+1

Do remember to add the one (it’s a common mistake otherwise)!

To calculate an NPV, we are going to need some discount rates: three in fact.

The formulas in the attached Excel file are more complex as they allow for delays and the number of periods to be flexed, but essentially the formulas simplify to

=1/(1 + Discount_Rate)^(Period_No – 1 + Adjustment Factor)

Where the Adjustment_Factor is:

• 0 if the cash flows occur at the beginning of each period.
• 0.5 if the cash flows occur in the middle of each period.
• 1 if the cash flows occur at the end of each period.

The final row simply uses INDEX(MATCH) to match the timing chosen with the row label (Start, Middle, or End) and return the correct discount factor from the list of three in each column, eg,

=INDEX(Rates_Calculated,MATCH(Cashflow_Timing,Row_Labels,0))

With cash flows and discount factors now known, calculating the NPV of the original profile is straightforward, as shown below.

Now comes the tough part. We need to replace this capex profile with a “smoothed” equivalent, where we calculate an amount for the first period, grow it by the Growth_Rate for the same number of periods, and yet still get the same NPV. To do that we need to use the generic formula shown below to get the base capex figure to use.

Seems simple enough, right? No? OK, this might require a bit of explanation.

Most of the variables in the formula above are self-explanatory, but just to be clear, g is the Growth_Rate and w is the Discount_Rate (cited as w because often this is the weighted average cost of capital, or WACC for short). The exponent (1+g)/(1+w) appears a couple of times, so I have taken this into account when computing this in our Excel example, as shown below.

The Base Exponent (pictured above) is simply

=(1+Growth_Rate)/(1+Discount_Rate)

and the Timing_Factor is 0, 0.5, or 1, depending upon whether cash flows occur at the start, the middle, or the end of each period. The key formula is the Base Amount, which is a simplification of the horror formula above:

=(NPV_of_Original_Capex_Profile*(1-Base_Exponent)/
(1-(Base_Exponent^Number_of_Periods)))*(1+Discount_Rate)^Timing_Factor

This is then multiplied by the (1+Growth_Rate) for each period after the first in the NPV calculation, and the discount rates and NPV calculation are then simply as before. This will give the same NPV — and I have even included a check to verify this.

There you have it: a profile you may use instead of your “erratic actual” one, which can compute the capex number to include in the terminal value (in this instance, it would be the final period value of 572, rather than the original 556).

Word to the wise

This article is not intended to be a comprehensive discussion on valuations. It is useful to calculate smoothed capex, not just for valuations, but also when normalising financial data for analysis purposes, for example.

This technique will work only when all periods are equal in length and percentage rates are converted to the rates for the period in question (eg, monthly, quarterly, annually). The Growth_Rate used should ideally be the one used in the terminal value calculations (where applicable); the argument for the Discount_Rate, however, is less well defined.

If these conditions are not met, using NPV to generate an equivalent Base Amount is still a reasonable technique. Instead of using the formula above, you may need to use Goal Seek as an alternative.

— Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.