ExcelFunctions.net

Search Site:

Related Page:

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

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.

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

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.

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

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 )

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

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 )

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

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 )

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

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.

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

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