Excel For Finance: Top 5 Financial Functions

Introduction to Excel Financial Functions

‍

Financial functions in Excel have been made available to execute a variety of financial calculations, including calculations of yield, investment valuations, interest rates, internal rate of return, asset depreciation, payments and more.

Today we are going to look at 5 most frequently used financial functions that are illustrated in the table below.

‍

Present Value (PV)

• PV helps you to find the present value of money.
• PV(Rate, Nper, Pmt, Fv), where:
• Rate     – interest rate;
• Nper – the number of periods;
• Pmt     – payment;
• Fv     – future value

Let’s find PV (present value of money) for the question on the screen.

$2.92 Million is the PV of $7 Million in 5 years, which means that in order toget $7M in 5 years, you have to have $2.92M now.

Interest Rate Calculation (RATE)

RATEfunction calculates the interest rate required to pay off a loan or to reach atarget amount on investment, over a given period.

• RATE (Nper, Pmt, PV, FV), where:
• Nper – the number of periods;
• Pmt     – payment;
• PV     – present value;
• Fv     – future value

Let’s find RATE for the question on the screen.

As you can see from the answer we’ve got, you need 6% of the interest rate onyour investment in order to get $7 million in 5 years.

‍

Internal Rate of Return (IRR)

Internal Rate of Return is a metric used in capital budgeting to estimate the profitability of potential investments.

Net Present Value (NPV)

Net Present Value (NPV) is the calculation usedto find today’s value of a future stream of payments.

• NPV (Rate,Value1,Value2,…), where:
• Rate  – Interest Rate;
• Value 1, Value 2, etc. – Cash Flows over the years

In ourexample, the net present value is $34.36, which is the current value of the 4 year cash flow.

Payment (PMT)

PMT is used to get the periodic payment of the loan.

• PMT(Rate,Nper,Pv,Fv), where:
• Rate     – interest rate;
• Nper     – number of periods;
• Pv     – present value;
• Fv     – future value.

Let’s find PMT for the question on the screen.

‍
As you can see, $1,186.98 is the annual payment you need to make to pay off the$5,000 loan in 5 years.

‍

Excel Financial Functions List

A list of Excel Financial Functions is provided below for your reference:

• Investment value functions     including FV, FVSCHEDULE, NPV, PV, RECEIVED, XNPV.
• The internal rate of return     functions including IRR, MIRR, XIRR.
• Asset depreciation functions     including AMORDEGRC, AMORLINC, DB, DDB, SLN, SYD, VDB.
• Payment functions including:     CUMIPMT, CUMPRINC, IPMT, ISPMT, PMT, PPMT.
• Dollar conversion including:     DOLLARDE, DOLLARFR.
• Yield functions including     YIELD, YIELDDISC, YIELDMAT, ODDFYIELD, ODDLYIELD, TBILLEO, TBILLYIELD.
• Price functions including:     PRICE, PRICEDISC, PRICEMAT, ODDFPRICE, ODDLPRICE, TBILLPRICE.

If you want to learn more about the latest trends in Excel, follow our upcoming articles,get weekly updates and try our Game.

‍