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

Share

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

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.

Share on facebook
Facebook
Share on twitter
Twitter
Share on linkedin
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Chandoo is an awesome teacher
5/5

– Jason

Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

17 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?

  16. Manjeet Singh says:

    Hi Sir,

    I've got a glitch, Please help me. when the year=1, then the amount has to be 1000 not 1100, as think practically if I've to pay someone with escalation of 10% every year, So I'll pay basic amount for the first year and from second year onwards I'll pay (Basic)*(1+10%).

    Please check.

Leave a Reply