Author avatar

Chhaya Wagmi

Working with Finance Function in Excelworking-finance-function-excel

Chhaya Wagmi

  • Aug 12, 2019
  • 12 Min read
  • 9 Views
  • Aug 12, 2019
  • 12 Min read
  • 9 Views
Business Professional
Microsoft Excel

Introduction

In this guide, you will learn how to work with financial functions in Microsoft Excel 2019. There are various financial functions which are present in the Excel 2019 version, as mentioned below:

  • FV
  • FVSCHEDULE
  • PV
  • NPV
  • XNPV
  • PMT
  • PPMT
  • RATE
  • EFFECT
  • NOMINAL

Financial Functions

In this section, you will learn about each of the above financial functions through various scenarios.

The FV Function

The FV function gives us the future value of a particular investment which has a constant interest rate and payments can be periodic, constant payments, or a single lump sum payment.

The FV function has the following syntax:

1
=FV(rate,nper,pmt,[pv],[type])

The arguments used in this function are mentioned below:

  • rate = required, it is the interest rate/period.
  • nper = required, number of payment periods.
  • pmt = required, payment made per period.
  • pv = optional, present value. If pv is omitted, it is assumed to be 0 (zero), and you must include the pmt argument in the function.
  • type = optional, the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0. Zero means it is assumed that the payment has been made at the end of the period and 1 means it is assumed that the payment has been made at the beginning of the period.

Let us consider a scenario where you can learn how to implement the FV function in Excel. Let's consider that five members of a group have made some investments in different banks in 2019. The payment has been made yearly ever since. The interest rate for each one of them is different. What would be the FV for each of those members in 2024?

ABCDEFG
membersratenperpmtpvtypeupdated FV amount in USD
Pam10%51-10000?
Rambo12%51-10000?
Rita8%51-10001?
Sam6%51-10000?
Tina15%51-10001?

In order to calculate the value, let us put the formula =fv(B1, C1, D1,E1,F1) in cell G1 and then apply it in subsequent rows. This will give result as the updated amount as shown:

ABCDEFG
membersratenperpmtpvtypeupdated FV amount in USD
Pam10%51-100001,604.40
Rambo12%51-100001,755.99
Rita8%51-100011,462.99
Sam6%51-100001,332.59
Tina15%51-100012,003.60

The FVSCHEDULE Function

The FVSCHEDULE function helps us to calculate the future value of an investment with a variable interest rate.

The FVSCHEDULE function has the following syntax:

1
=FVSCHEDULE(principal, schedule)

The arguments used in this function are mentioned below:

  • principal = required, it is the present value or the investment
  • schedule = required, it is an array of interest rates that will be applied.

Let us consider a scenario where you can learn how to implement the FVSCHEDULE function in Excel. Let's consider that five members of a group have made some investments in different banks in 2019. The payment has been made yearly ever since. The interest rate for each of them is different every year. What would be the FV for each of those members in 2022?

ABCDEF
membersrate of first yearrate of second yearrate of third yearprincipalfuture value in USD
Pam10%11%13%1000?
Rambo12%13%15%1000?
Rita8%7%10%1000?
Sam6%12%16%1000?
Tina15%16%12%1000?

In order to calculate the value, let us put the formula =FVSCHEDULE(E1, B1:D1) in cell F1 and then apply it in subsequent rows. This will give result as the updated amount as shown:

ABCDEF
membersrate of first yearrate of second yearrate of third yearprincipalfuture value in USD
Pam10%11%13%10001379.73
Rambo12%13%15%10001455.44
Rita8%7%10%10001271.16
Sam6%12%16%10001377.152
Tina15%16%12%10001494.08

The PV Function

The PV function helps us to calculate present value of an investment based on a constant interest rate.

The PV function has the following syntax:

1
=PV(rate, nper, pmt, [fv], [type])

The arguments used here are very similar to the ones that are used in the FV function explained above.

Let us consider a scenario where you can learn how to implement the PV function in Excel. Let's consider that five members of a group have made some investments in different banks in 2019. The payment has been made yearly ever since. The interest rate for each of them is different. If we have the FV amount made in 2024, what was the investment amount in 2019?

ABCDEFG
membersratenperpmtfvtypepv amount in USD
Pam10%511,604.400?
Rambo12%511,755.990?
Rita8%511,462.991?
Sam6%511,332.590?
Tina15%512,003.601?

In order to calculate the value, let us put the formula =fv(B1, C1, D1,E1,F1) in cell G1 and then apply it in subsequent rows. This will result as the present value as shown:

ABCDEFG
membersratenperpmtfvtypepv amount in USD
Pam10%51₹ 1,604.400-1,000.00
Rambo12%51₹ 1,755.990-1,000.00
Rita8%51₹ 1,462.991-1,000.00
Sam6%51₹ 1,332.590-1,000.00
Tina15%51₹ 2,003.601-1,000.00

The NPV Function

The NPV or Net Present Value is the sum total of positive and negative cash flows over the years. In other words, it calculates the net present value of an investment at a given discount rate, a series of negative values (future payments), and positive values (income).

It's syntax is given below:

1
NPV = (Rate, Value 1, [Value 2], [Value 3]…)

To understand the function, let us consider a case of an individual who made an initial investment of USD 5000 at an annual discount rate of 0.5%, along with five corresponding yearly returns as 2000, 1500, 1500, 1800, and 1800 respectively.

AB
DetailsIn USD
Rate5%
Initial Investment5000.00
First-year return2000.00
Second-year return1500.00
Third-year return1500.00
Fourth-year return1800.00
Fifth-year return1800.00

To implement the NPV function use the following:

1
=NPV(B2,B4:B8)-B3

which gives an output of USD 2452.27.

The XNPV Function

The XNPV function is quite similar to the NPV function except that here we provide dates for each return. The syntax is given below:

1
=XNPV(Rate, Values, Dates)

Let us take the above example but this time we also mention dates corresponding to each value:

ABC
DetailsIn USDDates
Rate5%
Initial Investment-5000.0001 January 2000
First-year return2000.0001 February 2001
Second-year return1500.0001 February 2002
Third-year return1500.0001 April 2003
Fourth-year return1800.0001 August 2004
Fifth-year return1800.0001 September 2005

Now, to implement the XNPV function, you need to make sure that the initial investment value is present in the negative format and then pass the values in the function, as given below:

1
=XNPV(B2,B3:B8, C3:C8)

The above will output the value of USD 233547.

The PMT Function

The PMT function denotes the periodic payments required for pay off for a particular period of time with a constant interest rate. Let’s have a look at how to calculate it in excel:

1
=PMT(Rate, Nper, PV, [FV], [Type])

The arguments stand for Nper (Number of periods), PV (Present Value), and FV (Future value).

To learn to implement the function in Excel, let's consider a case where we need to find the PMT for a person who needs to pay an amount of USD 5000 in five years at an interest rate of 5%.

AB
DetailsIn USD
Rate5%
Number of periods5.00
Present Value5000.00

To calculate PMT, we use the function as shown:

1
=PMT(B2, B3, B4)

This outputs the value as -1154.87. Note that we have not included the FV and Type value for our example.

The PPMT Function

The PPMT function is a variation of the PMT function where the payment is calculated on the principal with a constant interest rate and constant periodic payments.

Here's the syntax:

1
=PPMT(Rate, Per, Nper, PV, [FV], [Type])

All the arguments have the same meaning as that of the PMT function except Per which denotes the period for which the principal is to be calculated.

Let's consider the same example as that of PMT function and try to find out the PPMT for first, second, and third years.

YearPPMTResult
First Year=PPMT(B2, 1, B3, B4)-904.87
Second Year=PPMT(B2, 2, B3, B4)-950.12
Third Year=PPMT(B2, 3, B3, B4)-997.62

The RATE Function

The RATE function helps to answer the interest rate needed to pay off the loan in full for a given period of time.

Here's the syntax:

1
=RATE(NPER, PMT, PV, [FV], [Type], [Guess])

The new arguments are NPER (number of periods), PMT (amount paid per period), Guess (your guess on what the interest rate should be ).

Let's consider an example of a person who has taken out a loan of USD 50000 from a bank which he paid back in 6 years with USD 10000 yearly. We need to calculate the interest rate in this situation.

Here's the data in tabular format:

AB
Years6
PMT-10000
Loan50000

To implement the RATE function on the given table with a guess of rate as 2%, use:

1
=RATE(B2, B3, B4, 0, 0, 0.02)

This gives us the estimated rate as 5%.

The EFFECT Function

The EFFECT function is used to find the effective annual interest rate when you're given the nominal interest rate and when the interest compounds each year. The syntax for this function is given below:

1
=EFFECT(Nominal_Rate, N_COMP_YEAR)

To understand this, we’ll look at an example. Consider a nominal interest rate of 10% and the number of times it compounds per year is 12, then we can find the effective annual interest rate as shown:

AB
Nominal rate10%
N_COMP_YEAR12
1
=EFFECT(B1, B2)

This gives us the result of 10%.

The NOMINAL Function

The NOMINAL function helps to find the nominal interest rate when you're given the effective interest rate and the number of times it will compound per year. The syntax is given below:

1
=NOMINAL(Effect_Rate, N_COMP_Y)

Let us consider the following tabular data to implement the NOMINAL function:

AB
Effective rate10%
N_COMP_YEAR12
1
=NOMINAL(B1, B2)

which gives us the value as 10%.

Conclusion

In this guide, you've learned about various available financial functions in Excel 2019 like PMT, FV, RATE, etc. Apart from these, there are various other financial functions available in Excel which you can explore, as per your needs.

1