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:
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:
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:
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:
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:
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:
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:
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:
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:
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:
which gives the result 10.08%.
Note that, in the above function:
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:
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:
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.