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.
=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.
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
- 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: