Analysis of a Single Cash Flow in Excel

Related Page:
Annuity Analysis

Excel Cash Flow Functions

Excel provides a set of built-in functions that are designed to analyse a single cash flow or a series of cash flows. On this page, we consider the functions used for a single cash flow 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 some of the functions that we use for cash flow analysis follow the Cash Flow Sign Convention that positive numbers represent cash inflows and negative numbers represent cash outflows. This will be made clear by the individual function descriptions.


Present Value Calculation

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

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

This function is generally used for a series of cash flows. However, it can also be used to calculate the present value of a single invested amount, by setting the argument for the regular payments (i.e. the [pmt] argument) to zero.

Note that the PV function uses the cash flow sign convention in that positive values are treated as cash inflows and negative values are treated as cash outflows.

PV Function Example

To calculate the present value of an investment over 5 years that has an annual interest rate of 4% and a future value of $15,000, type the following into any Excel cell:

=PV( 4%, 5, 0, 15000 )

which gives the result -$12,328.91.

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

Future Value Calculation (Constant Interest Rate)

The Excel FV function calculates the future value of an investment.

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

This function is generally used for a series of cash flows. However, it can also be used to calculate the future value of a single invested amount, by setting the argument for the regular payments (i.e. the [pmt] argument) to zero.

Note that the FV function uses the cash flow sign convention in that positive values are treated as cash inflows and negative values are treated as cash outflows.

FV Function Example

To calculate the future value of an investment of $10,000 that earns an annual interest rate of 4% over 5 years, type the following into any Excel cell:

=FV( 4%, 5, 0, -10000 )

which gives the result $12,166.53.

Note that, in this example, the [pv] argument has been entered as a negative value, and so represents a cash outflow.

Future Value Calculation (Variable Interest Rate)

The Excel FVSchedule function calculates the future value of an investment, that has been subject to a series of variable compound interest rates.

Function syntax:

FVSCHEDULE( principalThe present value of the investment, scheduleAn array of values that provides the schedule of interest rates to be applied )

FVSchedule Function Example

  A B
1 Present Value: 10000
2 Schedule: 6%
3   4%
4   4%
5   4%
6 Future Value: =FVSCHEDULE( B1, B2:B5 )

The above spreadsheet on the right shows the FVSchedule function used to calculate the future value of an investment of $10,000 that earns 6% during the first year of investment and then earns an annual interest rate of 4% for the following three years.

In this case, for clarity, the amount of the original invested amount has been stored in cell B1 of the spreadsheet and the interest rates for each of the four years of the investment have been stored in cells B2-B5 of the spreadsheet.

As shown in cell B6 of the spreadsheet, the FVSCHEDULE function is:

=FVSCHEDULE( B1, B2:B5 )

which gives the result $11,923.56.

Duration (Number of Periods) Calculation

The Excel Pduration function calculates the number of periods required for an investment to reach a specified value.

(The Pduration function was first introduced in Excel 2013 and so is not available in older versions of Excel. However, if you have an older version of Excel, you can use the Excel Nper function, with the [pmt] argument set to zero.)

Function syntax:

PDURATION( rateThe interest rate per period, pvThe present value of the investment, fvThe future value of the investment )

PDuration Function Example

If you have an investment of $1,000 that is earning interest of 7% per year, how many years will it take for the value of the investment to reach $2,000?

This can be calculated using the Pduration function as follows:

=PDURATION( 7%, 1000, 2000 )

which gives the result 10.24476835.

Required Interest Rate Calculation

The Excel RRI function calculates the required interest rate for an investment to grow to a specified future value.

(The RRI function was first introduced in Excel 2013 and so is not available in older versions of Excel. However, if you have an older version of Excel, you can use the Excel Rate function, with the [pmt] argument set to zero.)

Function syntax:

RRI( nperThe number of periods for the lifetime investment, pvThe present value of the investment, fvThe future value of the investment )

RRI Function Example

If you require an initial investment of $5,000, to reach a value of $10,000 over 12 periods, the required interest rate can be calculated using the RRI function as follows:

=RRI( 12, 5000, 10000 )

which gives the result 0.059463094, or 5.94%.

How To Analyse a Single Cash Flow 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 an initial investment of $10,000 earns an annual interest rate of 4%, which is paid monthly, the future value of the investment can be calculated as follows:

=FV( 4%/12, 5*12, 0, 10000 )

which gives the result -$12,209.97.

Example 2

If an initial investment of $10,000 earns an annual interest rate of 4%, which is paid monthly, and has a future value of $12,000, the duration of the investment can be calculated as follows:

=PDURATION( 4%/12, 10000, 12000 )

which gives the result 54.78757726 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 54.78757726 by 12, to give 4.57 years.