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