Introduction

24

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 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?

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 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:

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 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?

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 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.

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:

`1`

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

which gives an output of **USD 2452.27**.

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:

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:

`1`

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

The above will output the value of **USD 233547**.

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%.

A | B |
---|---|

Details | In USD |

Rate | 5% |

Number of periods | 5.00 |

Present Value | 5000.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 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:

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 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:

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:

`1`

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

This gives you the estimated rate as **5%**.

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:

A | B |
---|---|

Nominal rate | 10% |

N_COMP_YEAR | 12 |

`1`

`=EFFECT(B1, B2)`

This gives us the result of **10%**.

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:

A | B |
---|---|

Effective rate | 10% |

N_COMP_YEAR | 12 |

`1`

`=NOMINAL(B1, B2)`

which gives you the result **10%**.

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.

24