Microsoft Excel is one of the foremost applications for an accountant or financial auditor. Becoming pro in excel for accountants is not an easy task. It takes time and effort. If you are an accountant, you must be familiar with the useful Excel formulas and functions to make the process easy.
Here, we are going to list some of the important useful, and most used Excel formulas for financial auditors and accountants.
Consider one single cell here as an example.
1. To double the width of all columns
Ctrl+ `
2. Displaying and highlighting formulas –
1) Highlight the cell that have formulas.
Click on single cell >> go to Home tab >> find and select >> formulas.
2) Highlight the cell that have no formulas.
Click on any single cell >> go to Home tab >> find and select >> go to special >> check the box for ‘Constant’ and ‘Numbers’ and uncheck the rest.
3. Auditing Tool
1) Find all dependencies of cell.
Jump to Formulas Tab >> Trace dependents
To find the dependent cell, just keep clicking repeatedly on trace dependents.
2) Highlight dependent cell
Without displaying arrows -- Ctrl+shift+)
4. Entire row and column references –
1) Add data from two different rows - (use Alt= or =sum) >> =sum(select rows)
2) Add data from two different column - (use Alt= or =sum) >> =sum(select the column)
3) Calculate average of data of entire column (use Alt= or =sum) >> =average(select the column)
4) Calculate median of the data of entire column (use Alt= or =sum) >> =median(select the column)
5) Calculate years till date from the given date
=datedif(A1,today(),”Y”)
A1 is the column number
6) Update cell values without formulas –
1) Increment or decrement the value from all rows or column without using formulas.
If you want to increase the cell values by 4
Type 4 in any blank cell >> now select all the rows and columns where you want to add 4 >> go to “paste special” or Ctrl+Alt+V >> Check “add” >> ok.
If you want to increase value by 10%
Type multiplier of percentage value (Here 10% i.e 1.1) in any cell >> Copy that value >> now select all the rows and columns where it needs to modify >> go to “paste special” or Ctrl+Alt+V >> Check multiply >> ok
You can use the same process of “Paste Special” for subtracting or dividing values
Date & Time
| YEARFRAC() | Purpose of this formula to get fraction of a year between two dates. It returns a decimal number. |
| DATEDIF() | This function will return the number of days, weeks, or years between two dates. |
| DAYS360() | This function is used in accounting calculations to get the number of days between two dates based on a 360 – day year (i.e 12 - 30 days- months). |
| DAY(), MONTH(), YEAR() | This function is used to returns the integer day, month, or year of a given date. |
Present Value & Rate of Return
| XIRR() | This is a financial function that returns the internal rate of return (IRR) that doesn’t have regular period cash flows. This function will help us to understand the rate of return earned on an investment. |
| XNPV() | It is also a financial function that is used to calculate the net present value (NPV) of an investment by using a discount rate and a series of cash flow that occur at irregular intervals. |
| IPMT | It returns the interest amount of loan payment in a given period, assuming the interest rate and the total amount of payment are constant in all periods. |
| IRR() | Internal Rate of Return (IRR) function is used to calculate the investment’s rate of return. |
| ISPMT | This function is used to calculate the interest paid during a specific period of investment |
| ACCRINT | This function is used to calculate the accrued interest for a security that pays interest on a periodic basis. |
| ACCRINTM | This function is used to calculate the accrued interest for a security that pays interest on maturity. |
| CUMIPMT | This function is used to calculate accumulative interest paid on a loan between any two payment periods. |
| CUMPRINC | This function is used to calculate accumulative principal amount paid on a loan between any two payment periods. |
| DB | This function will return the depreciation of an asset for a given time period based on the fixed declining method. |
| DISC | This function is used to get the discount rate for securities. It helps you to know the discount rate when you know the other detail of bond. |
| DURATION | The function calculates the duration of security (Macaulay Duration) that pays interest on a periodic basis with a par value of $100. |
| EFFECT | This function will calculate the annual interest rate with the number of compounding periods per year from nominal annual interest rate. |
| FV | This function is used to calculate the future value of an investment assuming periodic, constant payments with a constant interest rate. |
| FVSCHEDULE | This function will calculate the future value of an investment assuming a variable interest rate. |
| INTRATE | This function is used to calculate the interest rate for a fully invested security. |
| MDURATION | It returns the Macaulay modified duration for a security with par value of $100. |
| MIRR | It calculates the modified internal rate of return for a series of cash flows that occur at regular intervals. |
| NOMINAL | It will return the nominal annual interest rate with the given effective rate and number of compounding years. |
| PMT() | This function is used to calculate the total payment for a loan (principal + interest ) which is required to settle a loan or an investment with a constant interest rate over a specific time period. |
| NPER() | This function is used to get the payment periods for a loan based on amount, interest rate, and periodic payment amount. |
| NPV | This function is used to calculate the net present value (NPV) of an investment using a discount rate and a series of future cash flows. |
| PDURATION | It calculates the number of periods required for an investment to reach a specified future value. |
| PPMT | It is used to calculate the principal amount of investment or loan of any given period. |
| PRICE | It is used to calculate the price of security /bond per $100 face value that pays periodic interest. |
| PRICEDISC | It is used to calculate the price of discounted security /bond per $100 face value. |
| PRICEMAT | It is used to calculate the price of security /bond that pays interest at maturity per $100 face value. |
| PV | This function is used to calculate the present value of an investment or loan. |
| RATE | It is used to calculate the periodic interest rate i.e per period of annuity. |
| RECEIVED | It is used to get the amount received at maturity for a fully invested security. |
| SLN | It is used to calculate depreciation of an asset for one period based on initial asset cast, salvage value, and the number of periods over which the asset is depreciated. |
| SYD | It is used to calculate the depreciation of an asset, specifically the sum-of-years’ digits depreciation for a specified period in the lifetime of an asset. |
| TBILLPRICE | It is used to calculate the fair market value of Treasury bill/ bond per $100 face value. |
| TBILLEQ | It is used to calculate the bond-equivalent yield for a Treasury Bill. |
| TBILLYIELD | It is used to calculate the yield on a Treasury Bill. |
| YIELD | It is used to calculate the yield on a security that pays periodic interest that is bond yield. |
| YIELDDISC | It is used to calculate the annual yield for a discounted security like Treasury bill. |
| YIELDMAT | It is used to calculate the annual yield of a security that pays interest at maturity |
Lookup
| vlookup/index/match | To perform more advanced data lookups, these functions are most important. With the use of these functions, you can do horizontal and vertical lookups, left lookups, 2-way lookups, and lookups based on multiple criteria. |
| sumifs/countifs | Based on single criteria with the use of these two functions, you can sum or count cells conditionally. |
| If | This function will allow you to make logical comparison and returns one value if true and another value if false. |
Other -
| Pivot tables | It is used to recognize, sort, summarise, group, count, total or average data store in a more extensive table. |
10 January, 2023
18 October, 2022
22 September, 2022
02 September, 2022
08 August, 2022
15 July, 2022