Mod() function in excel to Implement Escalation Frequency [Financial Modeling Tutorials]

Posted on May 24th, 2011 in Financial Modeling , Learn Excel - 16 comments

You take an apartment on rent at $1000 per month and the owner puts an escalation clause saying 10% increment each 3 years. How do you model this in excel? In this tutorial we understand how escalations at certain frequency can be implemented using the mod function in excel.

What is the mod() function

Simply speaking, mod function calculates the remainder in division. For example,

clip_image001

The function is simple, it calculates the remainder, but it can achieve some very complex tasks.

So what would we like to do?

We want to create a model, where the user can change the escalation freq. For example, if you are able to negotiate for an escalation every 4 years, the model should be flexible enough to incorporate that.

clip_image003

How do we implement this?

First of all we create a flag to find, if it is an escalation year or not. Here we use the mod() function. For example, if it is a 3 year escalation clause, then we want to take the year number mod 3. If the remainder is non zero, it implies an escalation year (I know it can be confusing… So my suggestion – Play with the mod() function).

clip_image005

Once the escalation is figured out, we can sum-up the escalations and find the total escalations till a particular year.

clip_image007

Then we can simply take the base figures, and implement the number of escalations till that point of time.

clip_image009

Few more places, where you can use mod function

I have no doubt in my mind that Mod is a powerful (and confusing) function. Confusion arises because of the non-linear nature of the function (It sometimes gives increasing results and sometimes decreasing results!).

If you are planning to implement anything that has cyclicality for example, you want to color every 5th row of your excel, or you want to select every 4th day of a week, or anything of that nature, mod() function can be a very handy function!

How do you implement cyclicality in your models?

I know the easiest way to do that is to copy paste values in your model and update that manually. It is easy to understand but at the same time is not flexible. So how do you implement such functionality in your models?

Templates to download

I have created a template for you, where the subheadings are given and you have to link the model to get the cash numbers! You can download the same from here. You can go through the case and fill in the yellow boxes. I also recommend that you try to create this structure on your own (so that you get a hang of what information is to be recorded).

Also you can download this filled template and check, if the information you recorded, matches mine or not!  
Financial Modeling using Excel - Online Classes by Chandoo.org & Pristine
For any queries regarding the cash impact or financial modeling, feel free to put the comments in the blog or write an email to paramdeep@edupristine.com

More Resources on Excel Financial Modeling

Financial Modeling is one of the frequent uses of Excel. Please go thru below articles to learn more,

Join our Financial Modeling Classes

We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.

Please click here to learn more about the program & sign-up.
clip_image010

This article is written by Pristine. The author can be contacted on paramdeep@edupristine.com.
Pristine is an awesome training institute for CFA, PRIMA, GARP etc. They have trained folks at HSBC, BoA etc. Chandoo.org is partnering with Pristine to bring an excel financial modeling online training program for you.

Written by paramdeep@gmail.com
Tags: , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

16 Responses to “Mod() function in excel to Implement Escalation Frequency [Financial Modeling Tutorials]”

  1. kishor reddy says:

    Hi chandoo,

    Pls send formula tips to me, and how to write the formula ,

    Rgds...'
    Kishor

  2. Dave says:

    This is a good example of mod, and I find it useful when I have a range I want to wrap around and loop through.

    But I'm curious. In this example why wouldn't you use a single line for escalations? You can get the same effect in generating number of escalations with = Int((E1-$E$1)/$D$3). Effectively focusing on the numerator and not the denominator.

  3. Abhijit Sharma says:

    Hi Chandoo,

    Interesting.
    can u tell what is simple way to calculate age?
    Regards,
    Abhijit

  4. seema sinha says:

    Thanks to you i have got a hike in my salary.I am a data entry operator in small firm.I have learnt a lot from your site.Thanks.

  5. Luke M says:

    @Abhijit
    =DATEDIF(Cell_with_birthdate,TODAY(),"y")

  6. Rajiv says:

    A slight variation can reduce computations and speed up the sheet. There is no need to have no of escalations. Escalation flag is sufficient to make it work. Formula for cost with escalation will change to:

    Formula in F10: if (F7=1, E10*(1+$D$5), E10)

  7. paramdeep@gmail.com says:

    @Dave: Yes. Thanks for suggesting. In fact its quite elegant, if you are looking for the cumulative changes.
    @Rajiv: Again correct. You can simply calculate based on the last figures. Thanks!
    What I suggested was just one way! 🙂 There would be many more... why don't you guys suggest more and we can learn a lot about it from here!

  8. Saurabh says:

    Hi there, Nice article, simple, straight and sweet explanation of excel. Thanks...

  9. Thanks for your informative articles on xl.

  10. How can i calculate mod in xl?

  11. Hui... says:

    =Mod(number, divisor )
    =mod(10, 4)= 2
    10 divide 4 has a remainder of 2

  12. SANJAY K JAIN says:

    HOW TO USE THE FUNCTION -- LIST --

  13. ollehseidob says:

    Hi everyone. I have 2 numbers in A1=4 and B1=5 I want 2*A1+2*B1 if >40 then -40 how to write that.

    • Hui... says:

      @Ollehseidob
      Not quite sure what you mean by -40 but one of the following 2 solutions will be it

      =IF(2*(A1+B1)>40,2*(A1+B1)-40,2*(A1+B1))

      or

      =IF(2*(A1+B1)>40,-40,2*(A1+B1))

  14. olleiseidob says:

    thank you HUI, you are great.

  15. Francy says:

    How do we do this on a monthly cashflow instead of an annual one?

Leave a Reply