Having just completed a very large project at work involving six horrendous weeks of brutal data pulling, formatting, and analysis, and resulting in two 400+ page books and two 150+ page books of stuff that nobody will ever really care about, I am now free to write a new post! Today’s topic is creating custom formulas for Excel.
Excel is nothing if not configurable, but few people have the technical acumen, the desire, or the time to spend customizing it. Hopefully, this post will help to boost the technical acumen of the reader, but as for the desire and time, you will have to be the judge. I think you will find that performing some simple tasks such as this will free up much more time in the future, and such should feed your desire.
Ok, let’s get crackin’. The example I’m going to use throughout this tutorial is for a formula which we use frequently around the office, but of which few people are aware. It is for the Compounded Annual Growth Rate, or CAGR. When you have two figures occuring in different years, say, the population of a county in Florida, and you want to know how much this figure grew or will grow in each year in between, you use the CAGR.
Now I know what you’re thinking. The growth rate should be as simple as taking the quotient of the the difference between the first and last numbers over the first number, i.e. (x–y)/x, and then dividing the result by the number of years in between.
What that will give you is, in fact, a simple solution to this problem. If the population of Orange County is 100,000 in 2007 and is slated to be 200,000 in 2012, then it will grow (200,000-100,000)/100,000, or 100%. Divide this by the number of years, and you get 100%/5, or 20% per year. However, if the population truly grew 20% per year, then the interest would compound each year, resulting in a final population of 248,832. This is the same way credit card companies make money. This is also the way anyone can make money just by having money.
Anyway, what we want to get at is what percentage of the total population would grow each year in order to reach 100% in five years. This is calculated using the CAGR. The formula looks like this:
CAGR(t0,tn) = (V(tn) / V(t0))^(1 / (tn – t0)) – 1
Where V(t0) is the start value, V(tn) is the finish value, and (tn – t0) is the number of years in between.
Written as a function, accepting start_value, finish_value, and number_years, it would look like this:
CAGR = ((finish_value / start_value)^(1 / number_years)) – 1
Sort of a mouthful, and quite a bit to remember. Even when memorized, it’s quite a bit to have to type every time you want to use the formula. It would be nice of Excel to include this as a standard formula, but they don’t, and that’s probably because only a small amount of users would ever need it. There are probably thousands of formulas that some people use every day that I will never even need to know. That’s why Excel is customizable. So, on to the point of all of this.
Wouldn’t it be nice if instead of writing that formula over and over again, one could just type
into a cell and get the yearly growth rate? Well, you can! And you do it using Excel VBA, which stands for Visual Basic for Applications. Visual Basic is a programming language, but as the name implies, it is very simple and intended for the average or slightly above-average user. Once you understand some of the basic syntax, you can do just about anything.
You get to the VBA section of Excel by clicking on Tools -> Macro -> Visual Basic Editor. Once there, you need to create a module, or a file containing programming code, in your workbook. Do this by clicking Insert -> Module. You now have a blank canvas in front of you, and you are ready to create magic.
For our CAGR formula, type the following into this blank canvas:
Function CAGR (start_value as double, end_value as double, num_years as integer) AS double
CAGR=((end_value / start_value)^(1/num_years))-1
What does this mean? Well, the first line tells VBA what the name of your function is, and what parameters, or variables, to expect. We know that in our calculation of CAGR, we need to know both values and the number of years that separate them. So we tell VBA that we will be using these values. The first two are accepted as double, which means they are registered as double-precision floating point value, which is a fancy way of saying the number can be really big, or with a lot of numbers after the decimal point. It’s the biggest number type that VBA can work with, and it’s probably way more than most people will ever need. The number of years is registered as an integer, which means it can be any number up to 32,767 and can only contain whole numbers (it will be rounded if necessary). This should be more than sufficient for any CAGR calculations we will do.
The second line uses the name of the function (CAGR) and defines its value based on the parameters. Since it is the only line within our function, the last line (“End Function”) tells VBA to stop working, and just return the value of CAGR to the cell in which the function was entered.
Just close your VBA window to get back into Excel and start to have fun! Create a spreadsheet like this:
Then type in the formula to calculate the five-year growth rate (btw, this could be stored as a function in VBA as well, but you will have to name it something other than “growth” because Excel has a built-in function with that name):
Then type in our CAGR function in the adjactent cell:
And hit enter. Ta-da! We have our calculation!
Turns out it would actually grow 14.9% per year, and after five years, it will have grown 100%.
That’s it for now. The thing to remember about these saved formulas is that they’re only available in the workbook in which they are saved (or in other workbooks so long as that workbook is open), so you’ll have to recreate the VBA code each time you want to use it (however, once it is calculated, the value will remain in the cell whether or not the function is present). In the next post, I’ll show you how to save your VBA code as an Excel plug-in, so it will be available every time you open Excel.