Group items tagged

Management Accounting and Excel spreadsheets Excel spreadsheets are the most common and indispensable tool used by accountants, enabling them to analyse, report and share financial information. Much of this can be accomplished using only a fraction of the wealth of functions and options within the Excel program. Basic arithmetic, the SUM() function and some cell border formatting will let you produce useful models and perform some quite complex calculations. By expanding your knowledge with just a few other functions, Excel can become a much more versatile tool. Very occasionally Excel does not contain a function that you may expect, and it is necessary to combine other functions to perform tasks such as calculating weighted averages. Accountants have a professional responsibility to present accurate data. The IF() and TEXT() functions are essential for incorporating error checking messages to demonstrate that the spreadsheet models have been reconciled and are performing properly. A spreadsheet presentation gives reports a degree of unearned credibility. This is frequently ill-deserved and research shows that a high proportion of large models contain critical errors. Your spreadsheets must be well designed and thoroughly checked. Keep in mind the importance of any decisions which might be based upon your model. If you wish to be selective with your data and only perform calculations on items that meet certain criteria, both Database and Array functions are useful, but SUMPRODUCT() is the most versatile and wonderful alternative. It is able to extract all sorts of values from a table of data and can be used as an alternative to a great many functions. Graphs are a great way to present information, but keep them simple. Don't get carried away with 3D formats which can make it difficult to read important axis values. If you update a chart on a regular basis, you are very likely to occasionally forget to manually alter any titles which contain period information. It is best to link chart titles to cell contents which can be automatically updated.   A popular, modern Financial Management technique is that of balanced scorecards which often incorporate traffic light indicators. Conditional formatting for cell ranges is not only useful for highlighting exceptional or incorrect values but can be used to automatically colour cells to show good or bad performance. In order to report on tables of data with separate columns or rows for each accounting period, the OFFSET() function lets you select data for any single or cumulative period. There are numerous financial functions for performing interest and investment calculations. These can greatly simplify the long formulae previously required for things such as loan repayments - but always beware. The built in functions may perform differently to your own (textbook) formulas. They may invert the sign and show negative results where you expect positive; they may use a base period of p0 where you assume it is p1. Once you have tested the financial functions using a variety of situations and confirmed the results, they make tasks such as calculating the Net Present Value much easier. Finally, it is always helpful to make your models friendly to other users. Protecting or restricting the values that can be placed in cells will prevent unforeseen errors. Drop down boxes are a splendid way of getting values from users and hyperlinks can guide them around your models. By incorporating dates and filenames in default headers and footers, anyone can trace the source of your masterpiece.

Calculating interest and repayments on a loan Banks generally set repayments on loans and mortgages in equal payments over a fixed period of time. Within these regular payments the mix of interest and principal changes as time passes. A single formula can easily calculate the repayments on a loan of £x,000 at y% over z years. The real world is often more complicated and interest rates change at irregular intervals, often part of the way through a particular repayment period. The initial stage of this example is simple. £35,000 is borrowed and to be repaid over 1 year in equal monthly payments at an interest rate of 6%. The Excel PMT() function is used in cell C7 to calculate the monthly repayment. It takes the form: PMT(InterestRate, NumberOfPeriods, Principal, FutureValue, PaymentsDue). The interest rate should relate to a single repayment period and not the annual rate. In this example it is 1/12th of 6%. [$C$7] = - PMT(C6, B4, B3, 0, 0) Note that in this example a negative operator (-) is placed in front of the function in order to return a positive value. By default Excel will display repayments as negative amounts.

For loans which are to be repaid over a long period of time it is possible that the interest rate will change and the monthly repayments will be revised to reflect the new rate. Calculating an interest rate change part way through a period can be done in different ways and I don't know whether there is a standard procedure adopted by all banks. The result can vary depending upon factors such as whether they treat all months as equal 12ths of a year in terms of days. The Excel CUMPRINC function can only deal with whole months (or periods) and treats any period value as an integer. We can make the example more complicated and apply a new rate of interest (5.0%) that comes into effect after 12 February. February is in period 10 of the loan. It is necessary to perform a separate calculation for February (when the rate changes part way through the month) and then for the subsequent months. For the purposes of calculations, the unpaid balance of the loan can then be treated as a new loan to be repaid in equal amounts over the remaining number of periods. A weighted average of the two interest rates determines the combined rate for February (0.452%). This is based upon the number of days at which each rate is charged.

[C13] =B13/B15  [C14] =C15-C13  [D16] =SUMPRODUCT(C13:C14, D13:D14)  The new rate of 5% (=0.417%) will then apply to the whole months of March and April. Two new functions can be used to calculate the interest and the principal amounts for any period (or range of periods) of the loan. The sum of the interest and the principal equals the result of the PMT() function. The CUMPRINC function calculates the amount of principal repaid in between a specified range of periods. It requires the following series of arguments: CUMPRINC(InterestRate, NumberOfPeriods, Principal, Start_period, End_period, PaymentsDue). Similarly the CUMIPMT function returns the interest paid in between a specified range of periods.  CUMIPMT(InterestRate, NumberOfPeriods, Principal, Start_period, End_period, PaymentsDue). In February the number of periods will be 3 (Feb - Apr) and the start and end period will both be 1 because the interest rate applies to that period only. The principal will be the remaining unpaid amount (£8,947) and not the original £35,000. Note that in common with many other Excel financial functions, these both have a final parameter (PaymentsDue) which refers to whether the payment is made at the beginning or end of the period. In this example the value of '0' indicates the end of the period.