A to Z of Excel Functions: The FV Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the FV function.

The FV function

If you have ever been involved calculating financials, you will appreciate interest is a fundamental aspect. Annuities often need to be calculated, i.e. regular, periodic payments of identical amounts earning a similar rate of return.

Perhaps the easiest way to think of it is as follows:

• Let’s assume interest is set at 10% (and we will assume this is after tax)
• Something that is invested at $100 this year will increase by 10% next year, i.e. be valued at $110
• Something that is invested at $100 this year will increase by 10% over the next two years, i.e. be valued at $121
• Something that is invested at $100 this year will increase by 10% over the next three years, i.e. be valued at $133.10. etc.

Note that all of these valuations are for a point of time not a period.  This is a common mistake in modelling. 

The FV function calculates the future value of an investment based on such a constant interest rate.  You can use FV with either periodic, constant payments or a single lump sum payment.

The FV function employs the following syntax to operate:

The FV function has the following arguments:

• rate: this is required and represents the interest rate per period
• nper: this is also required.  This represents the total number of payment periods in an annuity
• pmt: again, this is required.  This is the payment made each period; it cannot change over the life of the annuity.  Typically, pmt contains principal and interest but no other fees or taxes.  If pmt is omitted, you must include the pv argument
• pv: this argument is optional.  This is the present value, or the lump-sum amount, that a series of future payments is worth right now.  If pv is omitted, it is assumed to be zero (0), and you must include the pmt argument
• type: this is also optional.  The type should either be zero (0) or one (1) and indicates when payments are due.  If type is omitted, it is assumed to be zero (0).

Make sure that you are consistent about the units you use for specifying rate and nper. If you make monthly payments on a four-year loan at 12% annual interest, use 12%/12 for rate and 4*12 for nper. If you make annual payments on the same loan, use 12% for rate and 4 for nper.

For all the arguments, cash you pay out, such as deposits to savings, is represented by negative numbers; cash you receive, such as dividend receipts, is represented by positive numbers.

Sometimes, the numbers aren’t quite what you expect for this function. That’s because Microsoft uses the following relationship to be consistent across its related financial functions:

Please see my example below:

We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every business day.

A full page of the function articles can be found here.