[3/5] How to calculate Year-To-Date (YTD) and Financial-Year-To-Date (FYTD) figures

  • Challenge level: 3 out of 5

  • Estimated reading time: 7 min

  • Excel functions covered: SUM(), OFFSET() and MATCH()

  • Excel attachment reference sheets: template_Excel

BEFORE YOU START: Get your FREE Excel template that accompanies this five-part tutorial by subscribing here!

We can easily obtain YTD figures at this point by slightly tweaking the L12M formula.

Excel formula for year-to-date (YTD) using SUM, OFFSET, MATCH
Excel formula for year-to-date (YTD) visually explained

A slight tweak to the formula gives us FYTD figures in case your company follows a financial year different from the calendar year.

Excel formula for financial-year-to-date (FYTD) using SUM, OFFSET, MATCH
Excel formula for financial-year-to-date (FYTD) visually explained

To get YOY figures for YTD and FYTD, the best way to go about this is to calculate the previous year’s YTD and FYTD figures as well so that we can employ the simple mathematical equation of dividing the difference between current and previous years’ figures by the previous year figure.

To get previous years’ YTD and FYTD figures, we need to only tweak the YTD and FYTD formulas so that they are referencing cells that contain Last Year date figures (i.e. column H instead of column G).

YTD_last year example:

Excel formula for year-to-date (YTD) last year using SUM, OFFSET, MATCH
Excel formula year-to-date (YTD) last year visually explained

We have finally covered all the formulas you need to know to build a template that gives you L12M, L12_Average, YTD, FYTD and YOY figures. More importantly, these formulas have been structured to automatically update these figures when there are time period changes. 

If you were secretly wishing for an easier way to to do this, you’re in luck! There is a more efficient way. We'll be introducing you to it tomorrow, stay tune!