Working with the Finance Function in Excel
Sep 14, 2020 • 12 Minute Read
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:
=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?
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| members | interest_rate | totper | payment | present_value | type | updated FV amount in USD |
| Pam | 10% | 5 | 1 | -1000 | 0 | ? |
| Rambo | 12% | 5 | 1 | -1000 | 0 | ? |
| Rita | 8% | 5 | 1 | -1000 | 1 | ? |
| Sam | 6% | 5 | 1 | -1000 | 0 | ? |
| Tina | 15% | 5 | 1 | -1000 | 1 | ? |
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:
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| members | interest_rate | totper | payment | present_value | type | updated FV amount in USD |
| Pam | 10% | 5 | 1 | -1000 | 0 | 1,604.40 |
| Rambo | 12% | 5 | 1 | -1000 | 0 | 1,755.99 |
| Rita | 8% | 5 | 1 | -1000 | 1 | 1,462.99 |
| Sam | 6% | 5 | 1 | -1000 | 0 | 1,332.59 |
| Tina | 15% | 5 | 1 | -1000 | 1 | 2,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:
=FVSCHEDULE(investment, rates_arr)
To implement the FVSCHEDULE function in Excel, consider the same example from the previous section with the given data:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| members | rate of first year | rate of second year | rate of third year | principal | future value in USD |
| Pam | 10% | 11% | 13% | 1000 | ? |
| Rambo | 12% | 13% | 15% | 1000 | ? |
| Rita | 8% | 7% | 10% | 1000 | ? |
| Sam | 6% | 12% | 16% | 1000 | ? |
| Tina | 15% | 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:
| A | B | C | D | E | F |
|---|---|---|---|---|---|
| members | rate of first year | rate of second year | rate of third year | principal | future value in USD |
| Pam | 10% | 11% | 13% | 1000 | 1379.73 |
| Rambo | 12% | 13% | 15% | 1000 | 1455.44 |
| Rita | 8% | 7% | 10% | 1000 | 1271.16 |
| Sam | 6% | 12% | 16% | 1000 | 1377.152 |
| Tina | 15% | 16% | 12% | 1000 | 1494.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:
=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?
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| members | interest_rate | totper | payment | fut_value | type | pv amount in USD |
| Pam | 10% | 5 | 1 | 1,604.40 | 0 | ? |
| Rambo | 12% | 5 | 1 | 1,755.99 | 0 | ? |
| Rita | 8% | 5 | 1 | 1,462.99 | 1 | ? |
| Sam | 6% | 5 | 1 | 1,332.59 | 0 | ? |
| Tina | 15% | 5 | 1 | 2,003.60 | 1 | ? |
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:
| A | B | C | D | E | F | G |
|---|---|---|---|---|---|---|
| members | interest_rate | totper | payment | fut_value | type | pv amount in USD |
| Pam | 10% | 5 | 1 | ₹ 1,604.40 | 0 | -1,000.00 |
| Rambo | 12% | 5 | 1 | ₹ 1,755.99 | 0 | -1,000.00 |
| Rita | 8% | 5 | 1 | ₹ 1,462.99 | 1 | -1,000.00 |
| Sam | 6% | 5 | 1 | ₹ 1,332.59 | 0 | -1,000.00 |
| Tina | 15% | 5 | 1 | ₹ 2,003.60 | 1 | -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:
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.
| A | B |
|---|---|
| Details | In USD |
| Rate | 5% |
| Initial Investment | 5000.00 |
| First-year return | 2000.00 |
| Second-year return | 1500.00 |
| Third-year return | 1500.00 |
| Fourth-year return | 1800.00 |
| Fifth-year return | 1800.00 |
To implement the NPV function use the following:
=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:
=XNPV(Rate, Values, Dates)
Consider the above example but this time you also have dates corresponding to each value:
| A | B | C |
|---|---|---|
| Details | In USD | Dates |
| Rate | 5% | |
| Initial Investment | -5000.00 | 01 January 2000 |
| First-year return | 2000.00 | 01 February 2001 |
| Second-year return | 1500.00 | 01 February 2002 |
| Third-year return | 1500.00 | 01 April 2003 |
| Fourth-year return | 1800.00 | 01 August 2004 |
| Fifth-year return | 1800.00 | 01 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:
=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:
=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%.
| A | B |
|---|---|
| Details | In USD |
| Rate | 5% |
| Number of periods | 5.00 |
| Present Value | 5000.00 |
To calculate PMT, use the function as shown:
=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:
=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:
| Year | PPMT | Result |
|---|---|---|
| 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:
=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:
| A | B |
|---|---|
| Years | 6 |
| PMT | -10000 |
| Loan | 50000 |
To implement the RATE function on the given table with a guess of the rate as 2%, use:
=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:
=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:
| A | B |
|---|---|
| Nominal rate | 10% |
| N_COMP_YEAR | 12 |
=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:
=NOMINAL(Rate, N_COMP_Y)
Consider the following tabular data to implement the NOMINAL function:
| A | B |
|---|---|
| Effective rate | 10% |
| N_COMP_YEAR | 12 |
=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.
