ExcelFunctions.net

Search Site:

Related Page:

Single CashFlow Analysis

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.

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

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

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

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

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

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

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

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

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 )

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

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

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