Excel FAQs: Common accounting calculations
Simon Hurst unearths and highlights some of the most commonly recurring themes from Excel Zone’s Any Answers.
Of the many features and functions used in accounting spreadsheets there are some recurring themes evident in the history of Excel Zones Any Answers and also in the comments to past articles.
How do I calculate weighted averages
Weighted average calculations usually involve rows of items with multiple values – such as products in stock where we have a number of products in one column and a value per product in another. To calculate the average value of every item in stock we need to take account, not only of the value of each product line, but also the number of each item in stock. So our weighted average would be the sum of:
(Product 1 number in stock*Product 1 value) through to (Product x number in stock*Product x value)
All divided by the total number in stock. We could achieve this by adding a column to our stock list table calculating the total value of each product in stock then divide the total of this column by the total of the number of products in stock.
There are several alternative formulae available to perform the calculation without the addition of the Total column. They depend on the idea of ‘arrays’. An array is a set of individual values. In this case we want to take two sets of numbers – the column of numbers in stock and the column of values and multiply them together one by one and add up the total. A straightforward Excel formula won’t cope with the arrays and will return an error:
If we use SUM() then we will get the two totals multiplied together, not the total of the individual values multiplied together:
However, we could combine a SUM() function with the use of an array formula to calculate our required total. To create an array formula you need to save your formula (when it is first created and whenever it is edited) by pressing Control+Shift+Enter rather than just Enter. The formula will appear in curly brackets to show it is an array formula:
The array formula will cause each B*C calculation to be performed individually before the overall sum is calculated.
Clever as array formulae are, they can be rather error prone – not everyone understands them and it only takes someone to edit the cell – even if they don’t make a change- and not use Control+Shift+Enter to wreck the formula. An alternative is to use the SUMPRODUCT() function:
Having found a way of calculating the total of the individual product*value products, that total then just needs to be divided by the total number of products:
How do I calculate mortgage repayments or other capital/interest equations?
For any financial calculation that doesn’t have an obvious formula solution it’s worth exploring Excel’s set of financial functions. You can type a function and its arguments directly into a cell, and in Excel 2007 and 2010 'AutoComplete' prompts will be of great help in doing so, but you might find it easier to enter more complicated functions by clicking on the 'Insert Function' button immediately to the left of the Formula Bar. The Excel ‘Insert function’ screen includes a ‘Search’ box where you can type in a short search term and review a list of functions that might be applicable. In this case the term ‘loan repayment’ has brought the PMT() function to the top of the list:
Selecting the function and clicking on OK will prompt you through the individual function arguments:
It’s very easy to get function results completely wrong by misunderstanding the entries required for each argument. The screen includes a short help tip for each argument but for the first time you use a new function it is a good idea to review the detailed help for the function and to double-check the answer that it produces.
How can I find more advanced financial functions?
If you are still struggling on with Excel 2003 then you might find that your list of financial functions is fairly short (about 16 functions) compared with Excel 2007/2010 and its library of over 50. However, there is no need to upgrade to extend your list of available functions – just go to Tools, Add-Ins and install the ‘Analysis ToolPak’. When you return to look at your list of financial functions you should find it expanded to include the 'missing' functions.
Are there any other useful functions in the Analysis Toolpak?
Funny you should ask. There are some useful date functions such as EOMONTH() to calculate the last day of a month a given number of months before or after a chosen date and NETWORKDAYS() to calculate the number of working days between two dates. There’s also the CONVERT() function for converting different units of measure such as miles to kilometres or litres to pints.
It’s a wet afternoon and I can’t think of anything to do – any ideas?
Depending on the circles you move in, you might not want to admit to doing this, but it would be very rewarding to open the 'Insert Function' screen and go through the different categories of Excel functions one by one looking at the different functions that are available. You might not have an immediate use for many of them, but at some time in the future you might realise that a particular function could save you hours.
For further information and ideas have a look at: