Essential Excel Formulas for Accounting
Accounting Functions in Excel
Use these essential functions to save time and speed up your accounting task. Excel may not take the place of QuickBooks but useful tips to help you clean up your accounting. For more ways to improve your Excel skills, check Master Excel Beginner to Advanced
Compound Interest:
A user can calculate the future value of the investment using the below formula in excel
Formula: P*(1+r)^n
Argument: P: is the principal amount invested
r: is the interest which can be annually, bi-annually, quarterly or monthly
n: is the period of investment is the number of periods over which the investment is made
Example: John invested $200 for 2 years at an annual interest rate of 5%, the future value of this investment can be calculated by typing the following formula into any Excel cell =200*(1+5%)^2 which gives the result 220.5.
Depreciation of Asset:
In Excel user can calculate the depreciation of an asset in the given time period. There are 5 different methods in excel to calculate the depreciation of the asset:
Straight Line Depreciation (SLN):
This is the most basic method to calculate the depreciation. This method depreciates the asset value by a fixed amount over the period of time.
Definition: SLN function in Excel can be used to calculate straight-line depreciation in excel
Formula SLN(cost, salvage, life)
Example: John’s asset cost is $5,000 and has a residual value of $100 after 10 years, we can calculate the annual straight-line depreciation of the asset as =SLN(5000, 100, 10 ) = $490
Decline Balance Depreciation (DB):
This is a compounded depreciation method. In this method, the value of the asset is reduced by a fixed percentage over the period of time.
Definition: DB function in excel can be used to calculate decline balance depreciation.
Formula: DB(cost, salvage, life, period, [month])
Example: John’s asset cost is $5,000 and has a residual value of $100 after 10 years, we can calculate the declining balance depreciation of the asset during year 1 as follow =DB(5000, 100, 10, 1) = $1620
Double Decline Balance Depreciation (DDB):
In this method, the cost of an asset is being charged at the double rate of straight line depreciation. Therefore, in this method the depreciation is the highest in the first period and less in successive periods.
Definition: DDB function in Excel can be used to calculate decline balance depreciation.
Formula: DDB(cost, salvage, life, period, [factor])
Example: John’s asset cost is $5,000 and has a residual value of $100 after 10 years, we can calculate the declining balance depreciation of the asset during year 1 as follow =DB(5000, 100, 10, 1) = $1000
Variable Declining Balance (VDB):
Definition: This function returns the depreciation of an asset for any period specified by the user, including partial periods, using the double-declining balance method or some other method.
Formula: VDB(cost, salvage, life, start_period, end_period, [factor], [no_switch)
Example: John’s asset cost is $5,000 and has a residual value of $100 after 10 years, we can calculate the declining balance depreciation of the asset during year 1 as follow =VDB(5000, 100, 10, 1, 3) = $1440
Sum-of-Years’ Digits Depreciation(SYD):
This is also an accelerated depreciation method. Using this method annual depreciation is being determined by multiplying the depreciable cost by a schedule of fractions.
Definition: SYD function in Excel can be used to calculate Sum-of-Years’ Digits Depreciation
Formula: SYD(cost, salvage, life, per)
Example: John’s asset cost is $5,000 and has a residual value of $100 after 10 years, we can calculate the declining balance depreciation of the asset during year 1 as follow =SYD(5000, 100, 10, 1) = $892
XNPV:
Using XNPV functions a user can calculate the net present value for a schedule of cash flows that is not necessarily periodic.
Formula: XNPV(discount_rate,cash_flows, dates)
Definition: discount_rate: is the rate to apply on cash flow
cash_flows: is series of cash flows that corresponds to a schedule of payments in dates
dates: are the schedule payment dates related to cash flow payments.
XIRR:
It returns the internal rate of return for a schedule of cash flows that is not necessarily periodic.
Formula: XNPV(cash_flows, dates,[guess])
Definition: cash_flows: is series of cash flows that corresponds to a schedule of payments in dates.
dates: are the schedule payment dates related to cash flow payments.
guess: It is an optional parameter. It is a number that user guess is close to the result of XIRR.
IRR:
It returns the internal rate of return for a schedule of cash flows which are periodic.
Formula: XNPV(cash_flows, [guess])
Argument: cash_flows: is series of cash flows that corresponds to a schedule of payments in periodic dates
guess: It is an optional parameter. It is a number that user guess is close to the result of IRR.
MIRR:
The user can also use the MIRR function in case of periodic cash flows. It returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both the cost of the investment and the interest received on reinvestment of cash.
Formula: MIRR(cash_flows, finance_rate, reinvest_rate)
Definition: cash_flows: is series of cash flows that corresponds to a schedule of payments in dates
finance_rate: the rate of borrowing money
reinvest_rate: interest rate received on the cash flows after reinvesting it
PMT:
This is used as a mortgage payment calculator. It calculates the payment for a loan based on constant payments and a constant interest rate.
Formula: PMT(rate, nper, pv, [fv], [type])
Definition:
rate: is the interest rate for the loan
nper: is the total number of payments for the loan
pv: it is the principal amount
fv: It is an optional argument. It refers to as a loan cash balance after the last installment is done. By default, it takes values as 0.
type: It is an optional argument. It indicates when payment is due. “0” is for the end of the period and “1” is for at the beginning of the period.
IPMT:
It calculates the interest on fixed loan payment for the given periodic
Formula: IPMT(rate, per, nper, pv, [fv], [type])
Definition
rate: is the interest rate for the loan
per: is the period user wants to find the interest
nper: is the total number of payments period in an annuity
pv: it is the principal amount
fv: It is an optional argument. It refers to as a loan cash balance after the last installment is done. By default, it takes values as 0.
type: It is an optional argument. It indicates when payment is due. “0” is for the end of the period and “1” is for at the beginning of period.
EFFECT:
For the given the nominal annual interest rate and the number of compounding periods per year, it calculates the effective annual interest rate
Formula: EFFECT(nominal_rate, npery)
Definition:
nominal_rate: is the nominal interest rate
npery: is the number of compounding periods per year
RATE:
Using this formula user can calculate the interest rate on annual basis. RATE is being calculated by iteration and can have zero or more solutions
Formula: IPMT(nper, pmt, [fv], [type], [guess])
Definition:
nper: is the total number of payments period in an annuity
pmt: it is the amount paid each year. It includes principal and interest paid.
fv: It is an optional argument. It refers to as a loan cash balance after the last installment is done. By default, it takes values as 0.
type: It is an optional argument. It indicates when payment is due. “0” is for the end of the period and “1” is for at the beginning of period
guess: It is an optional parameter. It is a number that user guess. By default, it takes 10 percent.
FV:
“FV” stands for “Future Value”. As the name suggests, this formula calculates the future value of the investment based on the constant interest rate.
Formula: FV(rate,nper,pmt,[pv],[type])
Definition
rate: is the interest rate per period
nper: is the total number of payments period in an annuity
pv: it is the principal amount
type: It is an optional argument. It indicates when payment is due. “0” is for the end of the period and “1” is for at the beginning of the period
SLOPE:
It Returns the slope of the linear regression line through data points in known_y’s and known_x’s
Formula: SLOPE(known_y's, known_x's)
Definition:
known_y’s: is the array of dependent values
known_x’s: is the array of independent values