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,
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.
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).
Once the escalation is figured out, we can sum-up the escalations and find the total escalations till a particular year.
Then we can simply take the base figures, and implement the number of escalations till that point of time.
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!
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,
- Financial Modeling using Excel – 6 part tutorial
- Introduction to Project Finance using Excel
- Modeling Interest during Construction in Excel
- Displaying and Selecting Scenarios using VBA
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.
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.
17 Responses to “Mod() function in excel to Implement Escalation Frequency [Financial Modeling Tutorials]”
Hi chandoo,
Pls send formula tips to me, and how to write the formula ,
Rgds...'
Kishor
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.
Hi Chandoo,
Interesting.
can u tell what is simple way to calculate age?
Regards,
Abhijit
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.
@Abhijit
=DATEDIF(Cell_with_birthdate,TODAY(),"y")
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)
@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!
Hi there, Nice article, simple, straight and sweet explanation of excel. Thanks...
Thanks for your informative articles on xl.
How can i calculate mod in xl?
=Mod(number, divisor )
=mod(10, 4)= 2
10 divide 4 has a remainder of 2
HOW TO USE THE FUNCTION -- LIST --
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.
@Ollehseidob
Not quite sure what you mean by -40 but one of the following 2 solutions will be it
or
thank you HUI, you are great.
How do we do this on a monthly cashflow instead of an annual one?
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.