MOD Function – Bridging the Time Gap in Excel

Blog Downloads:

Say you had quarterly data and you want to collect this data onto a yearly page? How would you go about it? Probably a hundred different ideas came to your head when I raised that question. Video Financial Modelling has seen all of these. One sticks out ahead of the rest, the MOD function.

Using the MOD function

The MOD function is quite simply awesome. In simple terms it takes a number and a divisor and spits out a remainder. The equation is shown below.

= MOD(number,divisor)

Does that bring back memories of school maths? Well for all of those people who can’t remember, here are a couple of examples to help the mental juices:

  • mod(11,3) – 11/3 is 3, with a remainder of 2. So MOD gives us 2.
  • mod(12,3) – yes you guessed it. 0.
  • mod(-11,3) – ok so this is getting a bit harder. The answer is 1. Why? Well because the MOD function gives the amount by which a number exceeds the largest integer multiple of the divisor that is not greater than that number. In this case the largest integer multiple that doesn’t exceed -11 is -12. Still confused. Have a play around with the function.

MOD function Example

Ok, so I promised you that I would show you how to grab data from a quarterly page and aggregate it on a yearly page so let’s get started. You can follow along with the example by downloading the spreadsheet and YouTube video in the Blog Downloads area.

We have the following quarterly dates and associated revenue numbers.

We want to aggregate these numbers on an annual page. Let’s firstly put in annual dates in. You can put these on a seperate page or keep it simple and have it on the same page. This is shown below.

Now the magic. Below the quartlery dates, let’s put in the following formula. Don’t worry we will explain the components soon.

= EOMONTH(quarter end date, MOD(month(first period ending date)-month(quarter end date),12)

This should look something like this:

Copy this formula across. Wow magic… hopefully this formula gives you the year ending for each of the dates? It sure does, now how does it work. Let’s break it down:

= EOMONTH(Component 1,Component 2)

  • Component 1: quarter end date
  • Component 2: MOD(month(first period ending date)-month(quarter end date),12)

The first component should be pretty self explanatory, so let’s look at the second component by looking at some brief examples.

Ok given that the first period ending date is always 31 Dec 2012, the month(first period ending date) will always equal 12. Simplifying the formula we get:

= MOD(12-month(quarter end date),12)

There are 4 possible options for the month(quarter end date). 3, 6, 9 and 12. i.e. corresponding to March, June, September and December. Let’s look at each in turn.

  • March – MOD(12-3,12)=9
  • June – MOD(12-6,12)=6
  • Sep – MOD(12-9,12)=3
  • Dec – MOD(12-12,12)=0

If you’re switched on you should see a pattern above. The result is equivalent to the number of months to the year end. So if we combine this with an EOMONTH formula we will get to the year end.

Now if we do a sumif formula (as below) we can aggregate the revenue numbers into yearly results.

We should get the following:

Now you may be wondering why we didn’t just use a Year formula and then a sumif. Well that is definitely another option, but say you now had a monthly construction page and you want to aggregate or lookup this data in a quarterly page. Trust me this happens all the time in project finance deals. Well the answer…. use the above methodology and you can’t go wrong.

If you liked this blog tutorial check out our new advanced training course, Advanced Toll Road Model Training Course.

By | 2011-11-14T18:22:11+00:00 November 14th, 2011|Blog|5 Comments

5 Comments

  1. AnalystGuy November 17, 2011 at 6:20 am - Reply

    Wouldn’t the Year function be simpler to get the year for which the quarter belongs to…

    • admin November 17, 2011 at 7:56 am - Reply

      It definitely is easier with the year function, however check out the associated YouTube video to see how easy it is to change to aggregation of semi-annual figures.

      http://www.youtube.com/watch?v=xbP8rZ-mJWs&feature=youtube_gdata_player

    • Familia February 29, 2012 at 10:42 pm - Reply

      Thanks a lot for these rlealy helpful formulas! At the moment I need those formulas very much and then you give it to me and all the others. This is timing, this is very nice.Thanks and Greets from Germany

  2. ANaidas November 17, 2011 at 2:37 pm - Reply

    Thanks for the insightful explanation of the Mod function. I applied your idea to our scenario which is a fiscal year-end rather than calendar year end. It worked.

    Alternatively, if you take out the Mod function and just use =EOMONTH(Quarter Ending,MONTH(1st Period End Date)-MONTH(Quarter End Date)), it results to the same desired EOY date where you can apply the Sumif formula.

    Keep sharing your ideas, thanks very much.

    • admin November 17, 2011 at 7:12 pm - Reply

      ANaidas

      You’re right you can definitely use the formula above and that would work fine in most situations.

      As you eluded to though using the MOD function comes in particularly handy when you have a fiscal year-end that doesn’t match the calender year end. An aside: Anyone that would like an example of how this works download and open up the associated Excel spreadsheet, go to the Answers tab and enter 28 Feb 2013 in the First period end. You should get the correct year end using the MOD function. The simpler formula above would not work. Try it.

      So ANaidas I guess it then comes down to flexibility vs simplicity. If you only need calender year ends then I’d definitely use your formula.

      Thanks for your comment and we look forward to hearing again from you shortly.

Leave A Comment