Excel spreadsheet is a common tool used by every accountant or financial analyst to analyze, report and share financial information. Some basic excel functions let you perform complex calculations and produce useful models making your work more effective and easier. Excel can become more versatile tool if you increase your knowledge on more other financial analysis functions.
In this article, I will focus on some of most common accounting and financial formulas you can use for basic to complex calculations.
The function is used to determine the company's worthy using the Net Present Value of a series of discounting cash flows. Unlike the excel NPV, the XNPV function uses specific dates for cash flows
=XNPV (discount_rate, cash_flows, dates)
XIRR determines the internal rate of return for a series of cash flows with specific dates. The XIRR should always be used over IRR if the time periods are different.
=XIRR (cash flows, dates, [estimated_irr])
The estimated_irr parameter is optional. It is your guess at the internal rate of return. If the parameter is omitted, it assumes a constant value of 10%.
Excel NPV and IRR assumes time periods in the cash flow are equal but there are times where the cash flows are not timed evenly therefore XNPV and XIRR are used to solve the problem.
This function is very important in analyzing the internal rate of return. It is modified formula used when cash from one investment is used in a different investment. If the cash for new land is invested in buying new motor vehicle.
=MIRR (cash flows, cost of borrowing, reinvestment rate)
Assuming a company produces an internal rate of return, IRR with a finance rate of 6% and the cash is reinvested in a motor vehicle at a rate of 5%, then MIRR will be higher than the IRR rate.
PMT function is used in real estate, commercial banking or in financial analysis of positions with debt schedule. The formula is mostly used as a mortgage payment calculator. Once the interest rate is provided, the time period and the available mortgage loan, you can easily calculate the payment amount.
=PMT (rate, number of time periods, present value)
IPMT calculates the interest rate of a fixed debt payment. The function works in conjunction with PMT function. To get the principal payment for each period, you can separate the interest payments for each period and take the difference of PMT and IPMT.
=IPMT (rate, current period#, total #of periods, present value)
Example: The interest payment in year 4 is ($4.762.75) on a 30 year loan payment period with an interest rate of 5%.
This function is important in lending and borrowing businesses. It returns annual interest rate for non- annual compounding.
= EFFECT (interest rate, #of periods per year)
This function helps you to predict how much cash you will have in the future, given the starting amount, the regular amount payments and the compounding interest rate.
=FV (rate, # of periods, payments, starting value, type)
This function is used by financial professionals to calculate depreciation expense. This enables them avoid building a large Declining Balance.
=DB (cost, salvage value, life/# of periods, current period)
The rate function can calculate the Yield to maturity for a given security. It is commonly used when determining the average annual return earned from buying bonds.
=RATE (# of periods, coupon payment per period, price of bond, face value of bond, type)
This function returns accrued interest for a security that pays periodic interest.
=ACCRINT (issue_date, first_interest_date, settlement_date, rate, par, frequency, [basis], [calc_method])
Returns depreciation for each accounting period by using a depreciation coefficient.
=AMORDEGRC (cost, purchase_date, first_period, salvage, period, rate, [basis])
Returns the depreciation of asset for each accounting period.
= AMORLINC (cost, purchase_date, first_period, salvage, period, rate, [basis])
This function returns depreciation of an asset for a given period based on double declining method.
=DDB (cost, salvage, life, period, [factor])
SYD function returns depreciation of an asset for a given time period based on the sum of years of depreciation method.
=SYD (cost, salvage, life, period)