Excel For Finance: Top 5 Financial Functions

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.

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


Click here to view the resourceClick here to download the resource
Begin your simulation journey today

Start learning new skills with the help of KeySkillset courses and our learning management system today!