In Excel, we can calculate the fixed value over N months with the combination of IF, MOD, COLUMN, MONTHS functions or any set that makes logic. This tutorial will give you an overview and a specific example of how to find the fixed value every N months.

**Formula:**

** =IF(MOD(COLUMN(A1) - offset, N) = 0, amount,0)**

The **MOD function** in excel is widely used to get a fixed value of any metric after a fixed interval. It can be monthly values, or quarterly payment cycles or any other parameter.

## Example

As you can see in the example given above, the formula when applied gives a predetermined value every quarter.

**=IF(MOD(COLUMN(A10 - 1,3) = 0,$D$5,0)**

After applying this formula you can get a fixed value that is 60 every 3 months.

*Figure 1.How to Get Nth Value after Fixed Intervals*

**Explanation**

- Firstly, the
**MOD function**, the MOD used two values, a number and other its divisor. After division, it returns the remainder. Simply put it can be used in a situation where you want to do something every Nth time. For instance, paying premiums every quarter, or collecting rent every 2nd month etc. - In our case, this number is created with the COLUMN function inserted in the MOD function. The
**COLUMN function**returns the value of cell specified in the formula and the two numbers inserted in the syntax. This value is called on**OFFSET**, which ensures that the count starts from 1. - Now, we have the divisor as 3, because we want that the value is returned every 3 months. If the test has zero as the remainder a similar formula will work for every consecutive 3rd month.

**The function of IF:**

- Why IF? Well, the role of this function is simple, it only recognizes the MOD function and returns the value in A10. This works when the
**MOD function**holds true and if the return value is 0 the function does not hold true. - To make the function even more legible you can use the date instead of a month. This can be done inserting the dates in the column where months are entered. Here is the formula to make things better:

**=IF(MOD(MONTH(A9),3)=0,$D$5,0)**

## Leave a Comment