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.
