Author avatar

Chhaya Wagmi

Working with the Finance Function in Excel

Chhaya Wagmi

  • Sep 14, 2020
  • 12 Min read
  • 723 Views
  • Sep 14, 2020
  • 12 Min read
  • 723 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

The FV Function

The FV function gives you "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," according to Excel documentation.

The FV function has the following syntax:

1
=FV(interest_rate,totper,payment,[present_value],[type])

To implement the FV function in Excel, 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
membersinterest_ratetotperpaymentpresent_valuetypeupdated 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, 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
membersinterest_ratetotperpaymentpresent_valuetypeupdated 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 you to calculate the future value of an investment with a variable interest rate.

The FVSCHEDULE function has the following syntax:

1
=FVSCHEDULE(investment, rates_arr)

To implement the FVSCHEDULE function in Excel, consider the same example from the previous section with the given data:

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, 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 you "to calculate present value of an investment based on a constant interest rate," according to Excel's documentation.

The PV function has the following syntax:

1
=PV(interest_rate, totper, payment, [fut_value], [type])

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

To implement the PV function in Excel, consider the same example as described in the above sections. If the FV amount is made in the year 2024, what was the investment amount in 2019?

ABCDEFG
membersinterest_ratetotperpaymentfut_valuetypepv 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, put the formula =fv(B1, C1, D1,E1,F1) in cell G1 and then apply it in subsequent rows. This will result in the present value as shown:

ABCDEFG
membersinterest_ratetotperpaymentfut_valuetypepv 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. "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)," according to Excel's documentation.

Its syntax is given below:

1
NPV = (interest_rate, Value1, Value2, Value3…)

To learn this function, 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 you provide dates for each return. The syntax is given below:

1
=XNPV(Rate, Values, Dates)

Consider the above example but this time you also have 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," according to Excel's documentation. The syntax is given below:

1
=PMT(interest_rate, n, present_value, [future_value], [type])

To implement the function in Excel, consider a case where you 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, use the function as shown:

1
=PMT(B2, B3, B4)

This outputs the value -1154.87. Note that you have not included the present_value and type arguments for this 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," according to Excel's documentation. Here's the syntax:

1
=PPMT(interest_rate, period, n, present_value, [future_value], [type])

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

Here's the PPMT for first, second, and third years for the same example which is considered for the PMT function:

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(n_period, PMT, present_value, [future_value], [type], [guess_rate])

Consider an example of a person who has taken out a loan of USD 50000 from a bank which he paid back in six years with USD 10000 yearly. You 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 the rate as 2%, use:

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

This gives you 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," according to Excel documentation. The syntax for this function is given below:

1
=EFFECT(Rate, N_COMP_YEAR)

To understand this, consider a nominal interest rate of 10% and the number of times it compounds per year as 12, then you 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," according to Excel's documentation. The syntax is given below:

1
=NOMINAL(Rate, N_COMP_Y)

Consider the following tabular data to implement the NOMINAL function:

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

which gives you the result 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.

17