Mid-year DCF - Terminal Value Technical Question

I'm looking over DCF mid-year conventions and came across this formula:

=IF(midyear, MAX(YEARFRAC(start date, period date,1)-0.5, YEARFRAC(start date, period date,1)/2), YEARFRAC(start date, period date,1)

If anyone could explain the reasoning to me behind it, it would be greatly appreciated. Thank you.

WSO Elite Modeling Package

  • 6 courses to mastery: Excel, Financial Statement, LBO, M&A, Valuation and DCF
  • Elite instructors from top BB investment banks and private equity megafunds
  • Includes Company DB + Video Library Access (1 year)

Comments (5)

Most Helpful
Apr 28, 2020 - 7:51pm

That's not a complete formula and it's a little out of context, but the idea is that if the "categorization" cell (my own words) is set to "midyear" then it'll return one value. If it's not, it'll return the other. One problem here is that your "midyear" argument doesn't have a cell designated that it's checking to see if it's set to "midyear". You also didn't include the final closing bracket.

For the logic, the argument is supposed to check if your designated cell says "midyear". If it does, it will return the largest of the 2 values calculated with the following two arguments (the fraction of the year - 0.5, or the fraction of the year divided by 2). If it's not set to "midyear", it will give you the full fraction of the year.

You're determining how to run the DCF, deciding how much to discount the cash flows. If you're treating it as a midyear calculation (common with companies that gain money steadily through the year vs. companies that are seasonal and gain most of their money near the end of the year) then it will lead to a higher valuation returned because it's discounted less due to the time value of money. If you're not treating it as a midyear valuation, it will give you a slightly lower valuation because the cash flows are discounted entirely at the end of the year.

Apr 28, 2020 - 9:11pm

^What the poster above said. It's typically used to represent even cash flows throughout the year, opposed to all at the end of the year if you don't use mid-year convention. So using mid-year convention, it would be ^n-0.5, instead of ^n. This will lead to a higher valuation as cash flows are get discounted in the middle of the year rather than at the end.

Apr 28, 2020 - 9:13pm

Dolorum labore aut voluptates cumque aperiam culpa est. Error sit aspernatur possimus aut. Incidunt perferendis necessitatibus esse. Et illo dolorum porro quos qui veniam voluptatem. Officiis qui aut deleniti ad similique molestias. Ut quidem eligendi at qui. Harum est explicabo occaecati saepe illo harum ipsa. Earum et tempora mollitia qui atque aspernatur.

Quia dolores quo vel cum voluptatem. Perspiciatis omnis modi iure tempora quis atque officia.

Start Discussion

Total Avg Compensation

October 2021 Investment Banking

  • Director/MD (10) $853
  • Vice President (39) $363
  • Associates (229) $233
  • 2nd Year Analyst (139) $155
  • 3rd+ Year Analyst (32) $149
  • Intern/Summer Associate (105) $142
  • 1st Year Analyst (504) $135
  • Intern/Summer Analyst (387) $83