There is a TL/DR in yellow at the bottom.
REAL LIFE SCENARIO: I wanted to calculate the average rent I would be paying over 3 years given that I know that the prices are going to go up by a certain percentage each year. Of course you can recycle this method to calculate for a budget for a marketing campaign, your income, etc.. Useful, huh?
NOTE THAT what I go over in this blog entry is not the whole way. You need to know the other factors like inflation rates, real estate value variation, and account for irregularities and risks among other things. I will not go over all that because I am not a real estate broker. I am just giving you a rough idea.
You might know that there is a function built-in Excel called SERIESSUM that can be used to get that calculation. Indeed, this is what we will be using, with a sprinkle of elegance.
I will work with the rent case, because it is the most tangible and so you will digest it easier:
I will assume you know:
- Current yearly rent. Put that in, let's say, cell B1. Let's go with MOZ 1,000 .
- Percentage of the increment that will be added up annually. Let's put that in cell B2, and give it a value of 5, which equates to 5%.
- For how many years will you be paying that rent. Let's go with 3 years, in cell B3.
A- The non-elegant, static way:
SERIESSUM adds up a number of terms equal to the number of coefficients you supply. I am sure I lost some people there.
Demystification: in our example, the rent of each month in one year is the same. That means that we need to multiply the one thousand MOZ by ... one, right? Exactly.
Simpler Demystification: let's say we are going to need the home starting JAN 2014. We know that all over 2014 we will be paying the same monthly rent; it will not increase from month to month. So, we can say that the rent of FEB is equal to the rent of JAN multiplied by "one". Excel needs to know that from you. This "one" is what Excel asks of you for some reason and won't let you get away without supplying it. This "one", ladies and gentlemen, is what is called a coefficient.
So, assuming we are doing this assessment over 3 years, we can write the function in cell B4 as follows:
=(B1*SERIESSUM((1+(B2/100)),0,1,{1,1,1})/12*B3
I will not go through why it looks like this, but I will pickup the coefficients and talk about it. Because in this form, this function will give you the average monthly rent for 3 years only. It will not work if you want to change that duration.
The part of the function in red is the coefficients bracket. Without repeating myself we now know that this needs to be a number of ones equal to the number of years (number of terms) that we want to want to average out. So if you want to see what the average would be if you change the period to 5 years, you need to add 2 more ones into that red bracket.
This will work, but as you see it is not dynamic and is not elegant, and quite prone to error especially when you want to consider getting this average for 20 years for example.
NOTE: A second method which doesn't involve the bracket needs you to write a whole column of 1's, and is dynamic. But it is not the sexiest either. So I will leave it out too.
B- Now the hocus pocus:
Keep everything as it is and replace the ones bracket with this expression:
I won't explain anything here. If you need that, click the links. All you need to know to do the job is that as you change the value in B3, you will automatically get the new average.
The full expression should be:
TL/ DR:
This whole thing is to get the sum of a series. SERIESSUM function requires you to give it an array of coefficients, which usually are either static, hard-coded (ex.: {1,1,1,} or people have to put a column of ones and define a range into it in, which leaves that column in your spreadsheet for no other reason. This is a leaner method. This expression:
INDEX((ROW(INDIRECT("1:"&B3))>0)*1,,1)
gives you a dynamic number of terms in the series ( in this example, B3 terms ). So, after inserting that into your SERIESSUM function, it should look like this:
I will ask you to read the non-TL/DR section or to your own wits if you want to know the rationale behind this function as it is and dissect the mathematics.
Related Posts:
Related Posts:
No comments:
Post a Comment