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