Search

# Accumulated Depreciation using Mixed References

Share

Last time we had discussed the use of SumProduct() to ease your life for calculation of consolidated revenues and depreciation. This time we would be using the sum function! Yes you heard it right – The Sum function.

But we would use the Sum function with a small trick! We would use it to calculate running cumulative sum! And believe me, you would need this function so many times – to calculate accumulated depreciation, cumulative debt, Profits to Retained Earnings and almost all the accounts that would consolidate into the balance sheet.

### Using Sum function to calculate cumulative values

Most of the accounts in the Balance sheet are cumulative accounts (For example, cumulative debt, accumulated depreciation, etc.). In models, we need to have these as running numbers

You would encounter this problem of calculating running cumulative values often and Sum formula (Used with slight intelligence) does come in very handy for the purpose.

### Using Sum() function for calculating running cumulative values

Sum function uses an array as an input. If used intelligently – starting element of the array with fixed reference and the second part of the array as moving, it can help you get a running cumulative value. [read more: Relative & Absolute References in Excel Formulas]

The basic trick is very simple. In the first cell in the sum function, I fix the first array argument using “\$” and sum till the same cell.

Now when I copy this function to the right, the first reference remains constant, whereas the second one keeps moving (as it is relative). This results in a growing array and hence a cumulative sum for accumulated depreciation.

### Where else can this function be useful in Finance?

As I had pointed our earlier, most of the balance sheet numbers are accumulated numbers (Balance sheet is like a bucket, which accumulates values) and hence you can find the application of this running sum in almost all such accounts. I have used it (Can’t remember how many times!!) for converting Profits to Retained Earnings (when there are no dividends paid out), Debt Raised to accumulated debt (when there are no repayments), Debt for Interest During Construction, etc.

### How do you accumulate numbers?

There are obviously multiple ways of doing the same thing in excel. I have shown you one way to get accumulated values. Conceptually accumulation is very simple – New Accumulated value = Old Accumulated Value + New Value. You can use this concept (maybe I will demonstrate in my next tutorial) to get the accumulation.

How do you accumulate numbers in excel. I would encourage you to share your experience!

I have created a template for you, where the subheadings are given and you have use the functions to get the right values for you! 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!

We are glad to inform that our new financial modeling & project finance modeling online class is ready for your consideration.

The article is written by Paramdeep from Pristine.

Chandoo.org has partnered with Pristine to launch a Financial Modeling Course. For details click here.

### Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

### Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Excel School made me great at work.
5/5

– Brenda

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

### Announcing Power BI Dashboard Contest (win \$500 prizes!)

Hey there, I have a SUPER exciting announcement! April is about to get a whole lot sweeter with our Power BI Dashboard Contest! Your mission, should you choose to accept it: Craft the most EPIC dashboard for the Awesome Chocolates CEO with sales & financial insights! Winners stand a chance to score up to \$500 in Amazon Gift Cards, plus some serious bragging rights!

## Related Tips

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

Excel Howtos

### How to fix SPILL Error in Excel Tables (3 easy solutions)

Financial Modeling

### 10 Responses to “Accumulated Depreciation using Mixed References”

1. Stephen says:

This is a useful skill to know. It all revolves around the ability to "freeze ranges". I think the true benefits of freezing ranges (using the F4 key) need fully explaining, but this is a good example of where it is useful.

2. Nilay says:

well in the example above you cna always use "F15 = E15 + F14" using all relatove referencing 🙂

3. @Stephen: Right! It is a useful skill. In fact you can use this technique in multiple instances. For example, if you want to calculate NPV of the project till various years, it can be used.
@Nilay: Yes, that is also a mechanism and a nice one too. I use both the techniques....

4. Anil says:

5. m-b says:

This reminded me of a recent post on the Fast Excel blog:

http://fastexcel.wordpress.com/2011/07/07/making-the-most-of-your-xips-part-1-counting-xips-and-reducing-them-to-make-excel-calculate-faster/

Worth reading when you're doing these (and other) calculations on large data sets.

6. paramdeep@gmail.com says:

Dear M-B,
This is interesting! I would say that if the calculations are very large in number (in the sum formula atleast), then it would make sense to take care in terms of excel operations! Usually in financial models (in equity side) the number of calculations is not so large. You work with data that would be in the range of 5-10 years. So I never thought about optimizing my code!
Your post reminds me of coding and optimizing code at the assembly level. It is quite interesting! 🙂

7. Using the F4 key to effective anchor the base point of a calculation, such as producting moving cumulatives is acutally a fairliy basic thing that everyone doing any kind of financial modelling needs to be aware of.

8. Budana says:

I am still trying to find the cumulative sum as this one applied to a table and could grow, but still could not find a correct formula.

9. Aman Thakur says:

Hello, Sir,
Actually, I have applied to the table and could grow, but still facing the same problem please help me out.

10. Private Proxies says:

Great info and straight to the point. I am not sure if this is in fact the best place to ask but do you guys have any thoughts on where to hire some professional writers? Thank you 🙂

### Get FREE Excel & Power-BI Newsletter

One email per week with Excel and Power BI goodness. Join 100,000+ others and get it free.