Search
blog-1
17
Jun

Most-Used Excel Formulas/ Functions For Financial Auditors Or Accountant

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.

Connect with Us

Copyright © 2021 Excel Accountant. All Rights Reserved.