PV of Terminal Value – Full Year Discounting vs Mid-Year Discounting

This is the second post in the Mid-Year discounting series. In the first post, I discussed in detail the workings and logic behind the mid-year convention and touched very briefly upon the terminal year calculations as well. It is strongly recommended to read the first post in conjunction with this one.

Now, unlike the free cash flow discounting during the explicit period, wherein the discounting period varies basis if mid-year convention is adopted or not along with taking note of any stubs therein, the Terminal Value discounting only gets affected by the stubs and not the adoption of mid-year convention.

Let me explain the above with an example:

Assume we are valuing the cash flows for Company A that has its FYE in December. For the sake of simplicity lets have explicit forecast period as 5 years. Also, lets assume WACC as 6.25%.

First lets assume simple discounting convention, where we assume cash flows are received at the end of each period. There are further be 2 cases assumed:

1. Analysis is done on January 1: As can be seen below, while assuming no stub period and that cash flows are received at the end of year, we discount the cash flows by full number of years to bring it to the present.

2. Analysis is done on April 30: Here note that, we are still assuming that the cash flows are received at the end of the year but the only difference being that in the current period the company has already finished with one quarter. Thus, we need to adjust the cash flows as well as the discounting period to only account for the remaining 9 months left for the year end and not include the full year financials.

Year 1 discounting above takes the remaining number of days in the year and divided by total number of days (in this case 365). Years 2 onwards its simply the full year + 9 months of current year to bring the cash flows to the present date and so on.

This was simple case where cash flows were assumed at the year end. As noted in previous post, this assumption is not widely used and rather assuming that since cash flows are earned throughout the year, it is reasonable to assume average. That is the cash flows are received at the mid of each valuation period. I will not go into workings again, as it was all explained in the first post, but for comparisons with normal discounting, let me provide you with the screenshots of how the discounting FCF will look like.

1. Analysis is done on January 1:

2. Analysis is done on April 30:

Discounting-cases-examplesDownload

From the above analysis, it should be little clear how both mid-year convention as well as stub period will have impact on cash flows of explicit period.

Lets turn our attention to the Terminal Value.

Earlier, I mentioned that Terminal Value discounting only gets affected by the stubs and not the adoption of mid-year convention. Let us now try to understand, why this is the case with terminal value.

As you will already know, two of the most common ways of getting to the terminal value figure are – Multiple Value Method and Gordon Growth Method

1. Multiple Value Method:

With this approach, we assume an exit multiple and use the corresponding metrics (EBIT / EBITDA) as of the last year of the explicit forecast period to get our terminal value. Now, there are few things to note here.

• Since we use the full year metric, say EBITDA of Year 5 in our example above, the terminal value so obtained is value at the end of the Year 5. To elaborate, it does not matter what convention of discounting for free cash flow we are assuming (normal vs mid-year), since the implied assumption is that the company is sold basis full year EBITDA, this is the value they will receive “at the end” of the explicit period / full year
• The assumption that EBITDA will be “assumed” to have received at the middle of year 5 in case of mid-year convention does not change the fact that it is a full year EBITDA basis which the company is sold at the end of the period

As such, it should be clear as to why the discounting convention does not play a role in the terminal value.

In our example above, if we are using the Multiple Value Method and assume that the company gets sold at 6.5x EBITDA Multiple and assuming EBITDA for Year 5 is say $235 million:

Terminal Value = 235 x 6.5 = $1,527.5 million

To get the PV of this Terminal Value, use the same discount factor as used in the final year if we are using the simple discounting for the other cash flows of the explicit period. So we will refer back to the “Simple Discounting” cases discussed above (since mid-year convention case is not relevant for terminal value) and use discount period as 5 for the January 1 case and 4.671 for the April 30 stub case. (Refer to the tables above).

However, in case the previous free cash flows of the explicit period are assumed to be discounted using the mid-year convention, then we need to add 0.5 to the final year discount period to account for the fact that it is a full year EBITDA usage. Hence, in the above example we will take (4.5+0.5)= 5 for the Jan 1 case and (4.171+0.5)=4.671 for Apr 30 case. Notice, this is exactly what the discount rate we are using in the “simple discounting” example above. This is exactly what we should be aware of!

2. Gordon Growth Method:

Under this approach, since we are not assuming an abrupt sale of business at the end of explicit forecast period – as is the case with multiple method – we can safely assume that no matter what the convention used (simple or mid-year), we will continue getting the free cash flows to perpetuity in the year end or mid as the case maybe.

Since the terminal value assumption matches with the assumption so taken for the explicit forecast period, we can use the same discounting period as used for the final year of forecast. So here, for simple discounting it will be 5 for Jan 1 case and 4.671 for the Apr 30 case. And for the mid-year assumption, it would be 4.5 for Jan 1 case and 4.171 for the Apr 30 case.

To get the Terminal Value using this approach, lets continue with above example where final year EBITDA was $235 million. If the WACC was 9.88%, growth rate was 2.5% and last year FCF of forecasted period was $109.98 million, the TV using the Gordon Growth Method is:

TV = (109.98 x (1 + 2.5%)) / (9.88% - 2.50%) = $1,527.5

It is not a surprise that the TV derived from both the methods is exactly the same. This happened as I used the implied growth rate from the Multiple method and applied it here. Though in real life scenarios you will be using either one of the methods, but it is good to understand the relation between the two.

• Formula for getting the implied growth rate from the multiple method is:
  • ( Terminal Value * Discount Rate – Final Year FCF ) / (Terminal Value + Final Year FCF)
• Formula for getting the implied EBITDA multiple is pretty straight forward:
  • Terminal Value from Gordon Method / Final Year EBITDA

• Please keep in mind that in case mid-year convention is used for the explicit period and you wish to compare the terminal value from both the methods (Multiple and Gordon Growth), you would need to multiply the TV from Gordon Growth by (1 + Discount Rate)^0.5, to bring it to the end of the explicit period. This is required since in Multiple Method it is always assumed that the TV is received at the “end” of the year. Note, such comparisons are quite rare in real life scenarios.

Excelling Excel Chart Series 1 – Waterfall Charts

Chances are that since you are looking for step-by-step guide to create a waterfall chart, you already know the usefulness of it and what it helps in achieving. But, to give a very short introduction into it – “Waterfall charts are few of the common charts used to provide a comprehensive view on the cumulative effects of various variables / data items on the overall value. It helps to view the sequential effect from the initial value to the final value.“

Let dig right into the steps to create a Waterfall Chart.

If you are using the latest version of MS Excel, then excel has made our life easy by providing a direct option to create a waterfall chart, similar to how we create pie charts or bar charts. However, in case you are using previous version, then be rest assured, this blog will help you with detailed instructions for earlier versions of Excel as well. Hence, I will divide the steps into 2 segments: One for the most updated version and one for the earlier version. I will also attach the corresponding excel file with charts made basis both the version here for your ready reference and template.

In both of these cases, we will use the following data set to build our chart. The data set simply represents what components went into growth of Sales from 2020 to 2021. As we can se Acquisitions and Price Inflation contributed to increased sales, however closed location and organic sales decline resulted in a decrease of sales in 2021. Below is the table with the raw data:

Steps to Create A Waterfall Chart in Office 2016, 2019 and 365:

• Select the data set from 2020 Sales to $972.6
• Press ( ALT + N + I1) or Go to Insert –> Charts –> Select Waterfall Charts (as shown in picture below)

• You will get a chart similar to below:

• Before moving ahead, it is important to make the chart little cleaner. To do that clear our the gridlines, chart title (if not needed) and legends

• Next (note this is very important step to ensure the ending value is correctly reflected in the chart) – Right click on the last bar (2021 Sales) and select the second last option (Set as Total)

• Change the Vertical axis basis the data range (in our case can be $900 – $1,010) and recolor the bars by fill option – with positive values shown as Green and negative values as red (as shown below)

• For the final step, we can hide the vertical axis and also remove the connector lines between bars by selecting the connector lines and pressing delete
  • Alternatively, right click inside the chart and go to format plot area. In the panel on right, select “Series Option” and untick the “Show connector lines” radio button

• Final chart will look as below:

Steps to Create A Waterfall Chart in Earlier Office Versions:

Unlike the above steps, where excel did all the work for us and left the formatting part to the user. There are more steps required to be performed in cases where the pre-existing charting tool for waterfall chart is not available. The manner in which the raw data needs to be organized is also very specific, without which we will not able to achieve the required results.

• As noted, since the excel cannot itself recognize the data for waterfall chart, we need to alter the raw data in various columns for easier chart construction
• Whenever one creates the waterfall chart, they will need to organize the data into 5 columns namely – Label, Base, Rise, Fall and Value
  • Label: It just represents the label of the data set that will be shown on our Y-axis. In the example we have have been using, the label column will show 2020 Sales, Acquisitions, Price Inflation, Closed Locations, Other Organic and 2021 Sales
  • Rise: Represents the column for positive values
  • Fall: Represents the column for negative values
  • Value: Represents the column that we will finally use to label the data points
• Base
  • First row will be linked to the starting value. In our example Base column first row will be linked to 2020 Sales data
  • From second row onwards base column will have the following formula:
    • Base ₂ = Base ₁ + Rise ₁ – Fall ₂ ; meaning base row 2 will be equal to base value in row above (row 1 here) + rise value in row above – fall value in same row (row 2)
    • Base ₃ = Base ₂ + Rise ₂ – Fall ₃ and so on
• Rise
  • Since there will be no rise value in starting figure, as this is our base value, we will not put any formula there (2020 Sales row in our case)
  • For each subsequent label (acquisitions etc), we will put either of the below formula in the respective rise column
    • Max (Value of respective label, 0)
    • If(Value of Label<0,0,Value)
• Fall
  • Again, since there will be no fall value in starting figure as it is our base value, we will keep this row under fall column as blank
  • For each subsequent label (acquisitions etc), we will put either of the below formula in the respective rise column
    • – Min (Value of respective label, 0)
    • If(Value of Label>0,0,-Value)

Once organized as above, our data set should look like this:

To build the chart select data from 2020 Sales to Fall column of 2021 Sales and follow the steps below:

• Go to Insert >> Charts >> 2D Stacked Column Chart

• Clean up the chart by removing the background grid lines (select the gridlines and press delete), delete the legend, chart title (if not required) and remove the vertical axis by unchecking the Primary Vertical option under Chart elements (as shown below)

• Select the “Base” columns (blue bars) and select no fill color option
• Recolor the starting (2020 Sales) and ending (2021 Sales) bar with one color
• Recolor the rise bars with Green and Fall bars with Red color
• Finally add data labels, but please note you will have to manually link each label to the Value column (this step is little tedious!)
• Finally, you have your finished chart as below

Below is the file that contains chart using both the methods.

excel-bridgeDownload

Mid-Year Discounting With Stubs – Straight & Simple Explanation !!

You will find many resources explaining the concept of mid-year discounting. But just like conventional discounting method, which takes overly simplistic view of real life problems, knowing basic mid-year convention too will not suffice. What you need is to understand how to apply this mid-year convention when you are sitting in say Q2 or Q3 of fiscal year and not the beginning of year (as most will explain). This article will help you in exactly that!

Why Mid-Year Discounting (and what’s with STUBS !?!) ?

If you are here understanding mid-year conventions, chances are you already know why we would use mid-year convention, over traditional text-book taught end-of-year discounting. But a quick refresher doesn’t hurt anybody, no ?

So to answer the question of why we would prefer to use mid-year convention to discount the cash flows, let us understand what really is our objective when we are discounting these cash flows. In the most basic sense, we are trying to determine what will be the today’s value of say $100 received at the end of the year. So we have 2 time periods here: Day we expect to receive the amount and the present day – the day on which the value is ascertained.

Conventional method assumes these time period to be the beginning and end of the year. Thus, there is an implicit assumption in end-of-year discounting that entire cash flows for the year are received at the end of the year. Does this really happen in real world? Answer is simple NO! Companies earn such cash flows on daily basis, but since it will be too tedious to calculate each penny earned basis the timings, we arrive at a compromise – we take an average. This is where the mid-year convention comes into the picture.

We use mid-year convention to represent the fact that company’s cash flows do not come all at once ie., at the end of each year, rather it comes evenly throughout the year

Example: End-of-year vs Mid-Year

Example 1: Assume the company expects $100 cash flow for four years with 12% discount rate.

	Year 1	Year 2	Year 3	Year 4
Cash Flow	$100	$100	$100	$100
Discount Period – Conventional Method	1	2	3	4
PV of Cash Flow – Conventional Method	$89	$80	$71	$64
Discount Period – Mid-Year	0.5	1.5	2.5	3.5
PV of Cash Flow – Mid-Year	$94	$84	$75	$67

Click on the link below to download the excel file for the workings of above table

Example-Mid-Year-ConventionDownload

Explanation: Few things that are important to repeat before explaining the calculations above:

• Conventional method assumes that cash flows are received at the last day of each year, hence the discounting by full year
• Mid-year convention assumes that the cash flows are received in the middle of the year

Conventional Method:

Year 1: Since the method assumes that $100 is received at the period end, the first year cash flows are discounted for full year to get the present value. Thus, ($100)/(1+12%)^1 gives us $89

Year 2: Again, for next year’s $100, the assumption is same that it arrives at the end of year 2. Thus, we need to discount it by full 2 years to get the present value. Thus, ($100)/(1+12%)^2 gives us $80 and so on for other years

Mid-Year Method:

Year 1: Now, this method assumes that the $100 in year 1 is received at the mid-year. Thus, to get the present value of this $100 we need to discount only by half a year. ($100)/(1+12%)^0.5 gives us $94

Year 2: This is where the calculations and discount period gets interesting! The second year $100 is again assumed to be received in the middle of that year. However, be little mindful of howto arrive at the discount period for year 2:-

– Let’s divide the time period into 2 parts, year 1 and year 2. Now the cash flow comes in the middle of year 2. Thus when we discount the same by 0.5, we will arrive at the beginning of year 2 or the end of year 1

– To come to present value, we need to discount the above cash flow further so as to know the value today. Since the above cash flow is one year forward, we will discount it by one more year. Hence, the total years by which $100 of Year 2 gets discounted is (0.5 + 1 = 1.5)

Year 3: Let us do another year, $100 received in the mid of Year 3, needs to get discounted by 0.5 to come at the beginning of year 2. But to know its value today, we need to discount it further by 2 years. Thus discount period is (0.5 + 2 = 2.5)

Let us bring in “Stubs” now …

Ok, so what are stubs and why we have been emphasizing on it since the beginning ? In the real world, it is highly unlikely that the company valuations are done right at the start of the year only. Until now, all our examples have been with the assumption that there is a full one year between the cash flow receipt and valuation date. What if we are already a quarter down and now are being asked for this valuation. The remaining period between now and year end is what we call as stub. Thus in this case, we have a stub of 9 months or 0.75 years.

Example 2: Company A has a year end on December 31st of each year. You have been asked to determine the discount periods for cash flows coming at the end of current period and next 3 years into the future.

	Stub (Q2 – Q4)	Year 1	Year 3	Year 3
Discount Period – Conventional Method	0.75	1.75	2.75	3.75
Discount Period – Mid-Year	0.375	1.25	2.25	3.25

Explanation:

Do not let the above numbers confuse you. If you understood the concept of mid-year convention above, this is just a simple add-on to that. Let’s discuss it step-by-step.

Conventional Method:

– Stub: The approach here is pretty straightforward. There is 9 months between end of the year (when cash flows are received) and today, hence first year cash flow (i.e., stub period) is discounted by 9 months or 0.75 years

– Year 1: This build on one year out. Hence, second cash flow is received after 1 year and 9 months. This translates to 1.75 years for discounting and so on

Mid-Year Convention:

– Stub: Use similar logic as above. If there are 9 months between today and end of period and we are using mid-year convention, then by the very assumption of mid-year rule, the cash flows are received mid of this 9 months period. Thus, the stub cash flows are discounted by (0.75/2 = 0.375 year)

– Year 1: Again, lets divide this into 2 parts – Stub + 1 Year. We are receiving cash flow in the middle of Year 1, thus we discount the cash flow for that year by 0.5. But on doing just 0.5 years, we have brought this cash flow to the end of stub period. Thus, we need to discount further by 9 months to bring that cash flow to today. Thus, for Year 1 we get (0.5 + 0.75 = 1.25 yrs)

Key Note: Mid-Year discounting is about when you receive cash in the year. In Year 1, the stub of 9 months is not halved i.e., we did not do 0.5 + (0.75/2), because we do not receive any Year 1 cash flow in this stub. The stub discounting is purely to get the mid-year discounted cash flow which represents the value at the start of that year to the present day.

Terminal Value With Mid-Year

I will very briefly touch upon the terminal value concept when using mid-year convention. Discounting changes basis the method used:

1. Multiple Method: Here add 0.5 to the final year discount number to reflect that you are assuming that the company gets sold at the end of the year (not mid)
2. Gordon Method: Use the final year discount number as is since you are assuming that the cash flows grow into perpetuity and are still received throughout the year rather than just at the end

Key Takeaways

• With same cash flows and discount rate, mid-year convention results in higher present value due to lower discount period
  • Higher the discount rate, more pronounced the difference between the end-of-year present value and that from mid-year convention
  • Longer the period more larger the difference between end-of-year and mid-year present value