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.
12 Responses to “Analyzing Search Keywords using Excel : Array Formulas in Real Life”
Very interesting Chandoo, as always. Personally I find endless uses for formulae such as {=sum(if(B$2:B$5=$A2,$C$2$C$5))}, just the flexibility in absolute and relative relative referencing and multiple conditions gives it the edge over dsum and others methods.
I've added to my blog a piece on SQL in VBA that I think might be of interest to you http://aviatormonkey.wordpress.com/2009/02/10/lesson-one-sql-in-vba/ . It's a bit techie, but I think you might like it.
Keep up the good work, aviatormonkey
Hi Chandoo,
You might find this coded solution I posted on a forum interesting.
http://www.excelforum.com/excel-programming/680810-create-tag-cloud-in-vba-possible.html
[...] under certain circumstances. One of the tips involved arranging search keywords in excel using Array Forumlas. Basically, if you need to know how frequent a word or group of keywords appear, you can use this [...]
@Aviatormonkey: Thanks for sharing the url. I found it a bit technical.. but very interesting.
@Andy: Looks like Jarad, the person who emailed me this problem has posted the same in excelforum too. Very good solution btw...
Realy great article
"You can take this basic model and extend it to include parameters like number of searches each key phrase has, how long the users stay on the site etc. to enhance the way tag cloud is generated and colored."
How would you go about doing this? I think it would need some VB
Hi,
I found the usage very interesting, but is giving me hard time because the LENs formula that use ranges are not considering the full range, in other words, the LEN formula is only bringing results from the respective "line" cell.
Using the example, when I place the formula to calculate the frequency for "windows" brings me only 1 result, not 11 as displayed in the example. It seems that the LEN formula using ranges is considering the respective line within the range, not the full range.
Any hint?
@Thiago
You have to enter the formula as an Array Formula
Enter the Formula and press Ctrl+Shift+Enter
Not just Enter
Thank you, Hui! I couldn't work out how this didn't work
is there a limit to the number of lines it can analyse.
Ie i am trying to get this to work on a list of sentances 1500 long.
@Gary
In Excel 2010/2013 Excel is only limited by available memory,
So just give it a go
As always try on a copy of the file first if you have any doubts
Apologies if I am missing something, but coudn't getting frequency be easier with Countif formula. Something like this - COUNTIF(Range with text,"*"&_cell with keyword_&"*")
Apologies if I missed, but what is the Array Formula to:
1. Analyze a list of URL's or a list of word phrases to understand frequency;
2. List in a nearby column from most used words to least used words;
3. Next to the list of words the count of occurrences.