Focus video player for keyboard shortcuts
Auto
  • 720p
  • 540p
  • 360p
1.00x
  • 0.50x
  • 0.75x
  • 1.00x
  • 1.25x
  • 1.50x
  • 1.75x
  • 2.00x
cc

We hope you enjoyed this lesson.

Get the Financial Modeling Basics course for more great video tutorials.

Start free trial

Cool lesson, huh? Share it with your friends

About this lesson

Illustrations and issues with the MOD function.

Exercise files

Download this lesson’s related exercise files.

MOD
9.8 KB MOD
10.1 KB

Quick reference

MOD

Discover how to use MOD function in a formula.

When to use

The MOD function, MOD(Number,Divisor), returns the remainder after the Number (first argument) is divided by the Divisor (second argument). The result has the same sign as the Divisor.

Instructions

Overview

  • For example, 9 / 4 = 2.25, or 2 remainder 1. MOD(9,4) is an alternative way of expressing this, and hence equals 1 also. Note that the 1 may be obtained from the first calculation by (2.25 – 2) x 4 = 1, i.e. in general:
    • MOD(n,d) = n - d*INT(n/d)
    • where INT() is the integer function in Excel.

Example

This function has various uses and a common example is shown below:

Calculations at regular time intervals: Consider tax payments as an example. Many companies make tax payments quarterly (i.e. once every three months). If we assume these payments are made in March, June, September and December then we can formulate the payment as IF(MOD(Month_Number)=0,Make_Payment,0), etc.

Login to download
  • 00:05 I'm just going to go straight into MOD.
  • 00:07 Because I think it's easier to just explain in Excel, see what you think?
  • 00:14 MOD is a good old fashioned mathematical function.
  • 00:17 Some people think it has no place in financial modeling.
  • 00:20 They'd be wrong, it's very, very useful.
  • 00:24 MOD is basically the good old clock arithmetic you learned from school.
  • 00:28 Let's take an example,
  • 00:30 using the illustration in row 7 here, 3 divided by 4.
  • 00:35 Now, 3 divided by 4 is 0, 0.75, but I don't want to show it that way.
  • 00:40 I want to say it's not remainder 3.
  • 00:43 See what I'm saying?
  • 00:44 It's, what's the whole number and what's the remainder left over?
  • 00:47 Well, the whole number's easy to calculate.
  • 00:50 There's a good old Yorkshire function.
  • 00:51 Intit equals INT for integer and we just take that divided that and
  • 00:57 it just takes the whole number part.
  • 01:00 So it's zero, but what about the remainder?
  • 01:04 Well, I could too okay, the remainder's
  • 01:08 going to be equal to this divided by that,
  • 01:14 0.75, minus the integer.
  • 01:22 So you got 0.75, multiplied by this will do it.
  • 01:28 Isn't that easy to follow?
  • 01:29 We're not gonna do it that way, there's an easier way.
  • 01:35 The MOD function works out the remainder for you.
  • 01:38 It counts, it does the modules.
  • 01:40 So it goes =mod, and it's just simply the number and the divisor.
  • 01:47 Is that simpler?
  • 01:50 If I copy these both down, I see it,
  • 01:54 13 divided by 6, is 2 remainder 1.
  • 01:58 23 divided by 8 is 2 times 8 is 16, plus 7 is 23.
  • 02:03 This is what MOD does, is it gives you the remainder.
  • 02:07 So, if I have a count down here, we can see how it actually works in the model.
  • 02:12 I can just go, =mod of this counter comma, the modules.
  • 02:22 And it goes therefore, copying it across.
  • 02:26 1, 2, 0, 1, 2, 0, 1, 2, 0, because 1 divided by 3 is 0 remainder 1.
  • 02:32 2 divided by 3 is 0 remainder 2, 3 divided by 3 is 1 remainder 0, and so on.
  • 02:39 Now if I'm in the model, and
  • 02:40 I'm doing something quarterly, I'll have three months in that quarter.
  • 02:44 So, I'll have month, one month, two month, three of quarter one.
  • 02:46 Then month, one month, two month, three of quarter two and so on.
  • 02:49 Wouldn't it be better to have 123, 123.
  • 02:53 What I could do is I could go equals mod open brackets.
  • 02:57 Of this comma that.
  • 03:00 Plus if all of this mod of that by
  • 03:05 this happens to equal nau ght
  • 03:10 then I want it to be the modulus.
  • 03:17 And we just need to make sure everything that C15 is absolute.
  • 03:23 And if you're thinking that's a horrible formula, you'd be right, but it works.
  • 03:30 An easier way is to do the following.
  • 03:33 You do =mod of this, -1,
  • 03:38 comment the Modulus +1 outside of it.
  • 03:44 And believe it or not, that's the same thing, because what happens is,
  • 03:51 if 1-1 is 0, 0 divided by 3 is 0, plus 1 is 1.
  • 03:56 2 minus 1 is 1, 1 divided by 3 gives you a remainder 1, plus 1 is 2.
  • 04:02 But this one, when I get to 3,
  • 04:04 that one would give me module of 0 before, it's actually gonna be,
  • 04:08 3 minus 1 is 2, 2 divided by 3 is not remainder 2 plus 1 is 3.
  • 04:11 See how it works?
  • 04:13 If I change it to 4, I can have a data validation here that's 4.
  • 04:17 It's all working, 6 and so on.
  • 04:21 Let's say I have to pay tax quarterly.
  • 04:27 So, let's go back to Modulus 03.
  • 04:29 And I have to pay it in the second period of each quarter.
  • 04:33 Do you see how easy it is with flags to do this?
  • 04:36 You just say equals, this here equals that number.
  • 04:47 And then we copy that across, and wherever it's a one, that's where we pay our tax.
  • 04:52 Or we can do a dividend calculation or we can actually do a GST payment or
  • 04:57 another indirect tax or whatever it is in our model.
  • 05:00 These are things you have to do all the time and people build much
  • 05:03 more complex calculations, to do this where MOD would come to their rescue.
  • 05:08 Simple as that.
  • 05:09 MOD, not so well known, but a very, very useful function for financial modeling.

Lesson notes are only available for subscribers.

Gift this course