Follow
The use of Microsoft Excel for accounting, bookkeeping, and other financial responsibilities is as old as the program itself, and is the foundation for Microsoft’s own highly reputable office certification programs. Unfortunately, many accounting students are unaware of some of the most basic functions that the program has to offer, an oversight which can lead to a lot of wasted time and effort. These are resources that, where possible, need to be spent in other areas besides building and populating spreadsheets. Some people even go on to otherwise successful careers in accounting, spending extra hours each month on setting up spreadsheets — sometimes for years.
Here are 5 must-know Microsoft Excel functions which will make any professional’s life easier when using Excel for accounting:
AMORDEGRC
Accountants work with depreciation many more times than they do with its opposite. This function uses coefficients to calculate and display the depreciation for a given accounting period. The syntax for AMORDEGRC is “AMORDEGRC( cost, purchase_date, first_period, salvage, period, rate, [basis] )” and the value which it returns can be referenced in other cells for greater convenience, as with any formula. This allows the accounting professional to save a lot of time, as well as reducing the potential for human error in a frequent, extremely important calculation.
Related resource: Top 10 Best Online Master of Accounting Degree Programs
COUPDAYBS
Coupon periods and settlement dates are two of the most important reference points in an accountant’s timeline. This simple function returns the number of days between the beginning of a coupon period, and its corresponding settlement date. “COUPDAYBS(settlement, maturity, frequency, [basis])” is the syntax for the COUPDAYBS function. As with other, similar functions, [basis] is an optional parameter, which in this case allows for the accountant to redefine the type of day count to be used in the calculation.
SLN
This deceptively short function fills a vital role in the use of Excel as accounting software. Its use provides the accountant with an asset’s straight-line depreciation over a single fiscal period, making it an extremely useful reference (for the accountant, but also to be used in other formulas elsewhere in a spreadsheet). The syntax for the SLN function is “SLN( cost, salvage, life )” with the asset’s original cost, its value at full depreciation, and its useful lifespan all taken into consideration.
YIELD
Surprisingly, this is one of the most common functions to have somehow frequently avoided being used to its fullest potential. The YIELD function returns the yield on a security that pays out periodic interest, allowing that value to be incorporated elsewhere within a given project (and being conveniently searchable). The function’s syntax is “YIELD(settlement, maturity, rate, pr, redemption, frequency, [basis]),” which offers a little more complexity than many other basic Excel functions for accounting, but is also extremely precise in its results. Here, as elsewhere, [basis] is an optional variable, referring to the type of day count basis to employ in the context of this use of the function.
“Define Name” and Name Your Formulas!
Technically, this isn’t a “function,” which is typically defined as a specific formula available within the Excel program. However, this handy Excel feature is too often overlooked, even by seasoned professionals. Its absence leads to a lot of confusion when accountants at different levels of professional experience find themselves having to work together on the same project. By going to the “Formulas” tab, you can click “Define Name,” and assign a name to a formula you’ve written by pointing to the cell in which it is located. Name the primary formula you’ve been using for accounts receivable “AccountsReceive1,” for example. This has the potential to reduce a lot of confusion as to what a formula does, and where it should be used, by those who lack a certain project-specific familiarity.
By mastering the basic and essential functions involved in using Microsoft Excel for accounting, you will save yourself a great deal of time, and a lot of headaches. These and other functions make working with other accountants on large projects much easier, as well as reducing the potential for human error under any circumstances.