Some months into starting as a financial planning specialist, I was handed over the Business Plan to revamp and make as flexible and automatic as possible. Then there was a working capital debt renegotiation with complex terms that I needed to include in the model. The complexity was that some of the working capital debts had repayments scheduled for every 2 months whilst others had repayments scheduled for every 3 months and heaven forbids having to go in manually to calculate repayment, especially when the payment start date is liable to change during scenario analysis. With the MOD function, we were able to create a model that would automatically register the next period of payment. Don’t worry, we would treat an example later in this post. First, let’s introduce you to the Syntax and Arguments.
Syntax & Arguments
The Syntax of the MOD Function is:
=MOD(number, divisor)
There are just 2 arguments in this function:
- Number: The number to be divided, could be hardcoded number or derived from a function or arithmetic operation.
- Divisor: The number to divide with.
Usage notes
There are some things you need to note when using this function. In this section we would take you through all that. Let’s begin, shall we:
- The MOD function returns the remainder after division occurs. This means that if the number is divisible by the divisor, the MOD function would return zero (0)
- Both Arguments are required, not providing a divisor would result in a #div error because excel assumes that the divisor is zero (0), whilst not providing the number would output zero (0) as excel assumes that the number is zero (0). And remember zero divided by any number must equal zero.
- Both arguments must be numerical, if either argument is not numerical, the function would return a #VALUE error.
MOD Function Exampless
Positive numbers
Let’s look at some examples with hardcoded numbers
=MOD(44,3) // Returns 2
=MOD(55,7) // Returns 6
=MOD(5.75,1) // Returns 0.75
Negative numbers
In the above section, the divisors were all positive numbers, let’s see what happens when we have negative divisors. Remember the mathematical rule (Negative / Negative) = Positive, and (Positive / Negative) = Negative, it is also applicable in the MOD Function.
=MOD(-5,-4) // Returns 1
=MOD(-5,4) // Returns -1
Large numbers
One major shortcoming of the MOD function is that it doesn’t handle large numbers well. By large numbers we mean numbers with 15 characters or more. If used on these kinds of numbers, it returns a #num error. Dont worry however, we can still calculate the remainder with another function
Count Odd & Even numbers
You can count the number of even or odd numbers in a data set by combining the MOD and SUMPRODUCT Functions. Let’s show you how to do it. In our example, we have 10 random numbers, and our goal is to count the number of even numbers and number of odd numbers in our data set.
A major characteristic of even numbers is that they are all divisible by 2, meaning that the remainder would always be zero (0). Now that we are aware of this characteristic of even numbers we can use the number 2 as the divisor. To count the number of even numbers, we use the formula below:
Next is counting odd numbers by using the MOD function. Almost similar to even number, odd numbers also have a common characteristic which is that whenever they are divided by the number 2, they always leave a remainder of one (1). We would use the formula below to count the number of odd numbers in our data set.
Practical use of the MOD Function in Modeling
Ok, so back to our opening discussion in this post, I had to model a working capital debt repayment schedule based on some specified payment frequency. In this section, I would show you how you can achieve that by using the MOD Function.
Let me introduce you to our example in this section. I have 2 outstanding working capital debt and I need to create a repayment schedule with one having a quarterly repayment plan and the other having a Bi-Annual repayment plan.
Vendor 1 Repayment Schedule
To begin, we would create a section that shows the opening balance, payments / additions and closing balance for each of the vendors. Let’s start with Vendor 1. We would add a count of the months with a nested IF statement. For Vendor 1, Jan-23 which is the start month should have a number 3 attached to it since it is quarterly payment frequency. Then the following months would be previous value + 1.
The idea is that since it is a quarterly payment frequency, the payment months must be divisible by 3. Let’s see the Nested IF Statement for implementing the number count for each month.
Now that we have numbers assigned to each month, we can use the MOD function to identify the months we are to make payment in. For that, we use the formula below for Jan-23 and copy across to other months.
Result: Excel returns payment every 3 months with the IF Statement and MOD combination.
Vendor 2 repayment schedule
Done with Vendor 1, now let’s move on to Vendor 2. For this vendor, payment is to be made once in 2 months. We would assign our numbers to each month with a slight tweak. See the Nested IF statement for this below:
The IF Statement and MOD function combination would also be similar to our combination in Vendor 1 repayment schedule. We only need to change our divisor from 3 to 2.
