Analysis of a Series of Constant Periodic Cash Flows (an Annuity) in Excel

Excel Cash Flow Functions

On this page, we consider the built-in Excel functions that are used to analyse a series of constant periodic cash flows for which interest is calculated and compounded periodically.

A brief description, along with a simple example, is provided for each of the listed functions. However a link is also provided, which takes you to a full description of the function with further examples.

Note that the functions follow the Cash Flow Sign Convention that positive numbers represent cash inflows and negative numbers represent cash outflows. This will be illustrated in the function examples.


Present Value Calculation

The Excel PV function calculates the present value of an annuity.

Function syntax:

PV( rateThe interest rate per period, nperThe number of periods for the lifetime investment, [pmt]The payment per period, [fv]The future value of the investment, [type]Specifies whether the payment is made at the start or the end of each period (0=end; 1=start) )

PV Function Example

If an investment earns an annual interest rate of 6% and returns $5,000 per year for a period of 5 years, the present value of the investment can be calculated as follows:

=PV( 6%, 5, 5000 )

which gives the result -$21,061.82.

Note that the result from the function is negative, representing a cash outflow.

Future Value Calculation

The Excel FV function calculates the future value of a series of constant periodic cash flows.

Function syntax:

FV( rateThe interest rate per period, nperThe number of periods for the lifetime investment, [pmt]The payment per period, [pv]The present value of the investment, [type]Specifies whether the payment is made at the start or the end of each period (0=end; 1=start) )

FV Function Example

If a regular payment of $5,000 is made at the end of each year for 10 years, and earns an annual interest rate of 4.5%, the future value of the investment can be calculated using the FV function as follows:

=FV( 4.5%, 10, -5000 )

which gives the result $61,441.05.

Note that, in this example, the [pmt] argument has been entered as a negative value, and so represents a series of cash outflows.

Calculation of Payment Amount

The Excel Pmt function calculates the constant periodic payment that is required for an investment with a given interest rate, to have a specified value.

Function syntax:

PMT( rateThe interest rate per period, nperThe number of periods for the lifetime investment, [pv]The present value of the investment, [fv]The future value of the investment, [type]Specifies whether the payment is made at the start or the end of each period (0=end; 1=start) )

PMT Function Example

If you have a loan of $10,000 that is charged an annual interest rate of 3.5%, and is to be paid off by 5 equal payments at the end of each year, what is the amount of each of the payments?

This can be calculated using the Excel PMT function as follows:

=PMT( 5%, 5, 10000 )

which gives the result -$2,309.75.

Note that the result that is returned from this example is a negative value, indicating that the payments are cash outflows.

Number of Periods Calculation

The Excel Nper function calculates the number of periods required for a series of constant periodic cash flows, with a given interest rate, to reach a specified value.

Function syntax:

NPER( rateThe interest rate per period, [pmt]The payment per period, [pv]The present value of the investment, [fv]The future value of the investment, [type]Specifies whether the payment is made at the start or the end of each period (0=end; 1=start) )

NPER Function Example

If you invest $2,000 at the end of each year, at an annual interest rate of 8%, how many years will it take for the investment to have a value of $15,000?

This can be calculated using the Excel NPER function as follows:

=NPER( 8%, -2000, 0, 15000 )

which gives the result 6.107033147.

Note that, in this example, the [pmt] argument has been entered as a negative value, and so represents a series of cash outflows.

Required Interest Rate Calculation

The Excel Rate function calculates the required interest rate for an investment of constant periodic payments to have a specified future value.

Function syntax:

RATE( nperThe number of periods for the lifetime investment, pmtThe payment per period, pvThe present value of the investment, [fv]The future value of the investment, [type]Specifies whether the payment is made at the start or the end of each period (0=end; 1=start), [guess]An initial estimate at what the rate will be )

Rate Function Example

If you invest $5,000, at the end of each year for 10 years, what interest rate is required for the investment to reach a value of $80,000 at the end of the 10 years?

This can be calculated using the Excel Rate function as follows:

=RATE( 10, -5000, 0, 80000 )

which gives the result 10.08%.

Note that, in the above function:

  • The [pmt] argument has been entered as a negative value, and so represents a series of cash outflows;
  • The [fv] argument is a positive value and so represents a cash inflow.

How To Analyse a Series of Cash Flows When Interest is Compounded Monthly

If the interest on your annuity is compounded monthly (while being quoted as an annual interest rate), the stated annual interest rate needs to be converted into a monthly interest rate and the number of years needs to be converted into months for the above Excel functions.

I.e.

monthly interest rate   =  annual interest rate / 12
number of months   =  number of years * 12

A similar calculation is required if interest is compounded quarterly, semi-annually, etc.

Example 1

If a series of monthly investments of $500 (invested at the end of each month) earns an annual interest rate of 4.5%, which is compounded monthly, the future value of the investment, after 6 years can be calculated as follows:

=FV( 4.5%/12, 6*12, -500 )

which gives the result $41,240.41.

Example 2

If a monthly investment of $200 (made at the end of each month) earns an annual interest rate of 4.5%, which is compounded monthly, how many months will it take for the investment to be worth $10,000? The NPer function for this calculation is:

=NPER( 4%/12, -200, 0, 10000 )

which gives the result 46.32223654 months.

Note that, as the interest rate supplied to the function is a monthly interest rate, the number of periods returned is a number of months. Therefore, to calculate the number of years of the investment, we need to divide 46.32223654 by 12, to give 3.86 years.