An annuity is a periodic payment into an account that accrues interest.
If the annuity is an investment, then an initial amount is invested at the end of year zero and further, usually constant, amounts are invested at the end of each succeeding year for a finite number of years.
The future value of an investment annuity is the sum of invested amounts compounded. If the investment at the start of t = 0 is A0, the periodic investment is A dollars and the interest rate is i percent compounded annually, then:
At the end of the first year, the value V1 is \[ \begin{align} V_1 &= A_0 (1 + \frac{i}{100}) + A \end{align}\]
At the end of the second year, the value V2 is \[ \begin{align} V_2 &= V_1 (1 + \frac{i}{100}) + A \end{align}\] and a general expression for the process is \[ \begin{align} V_{n+1} &= V_n (1 + \frac{i}{100}) + A \end{align}\] If the compounding rate is more frequent then the expression is \[ \begin{align} V_{n+1} &= V_n (1 + \frac{i}{100m}) + A \end{align}\] where m is the number of compounding periods per year.
This is a recurrence relation for the value one year in the future of the investment.
If \(A_0 = 0\), i = 3% pa find V1.
If \(A_0 = 0\), i = 3% pa find V2, V3.
Example : A0 = 0
You invest $1000 at the end of every year for 2 years at an interest rate of 5% compounded half-yearly.
In this case, the number of compounding periods is 4 and the rate per period is the nominal rate divided by the number of compounding periods per year. So \[ \begin{align} i &= \frac{5}{100 \times 2} \quad \end{align}\ \]
Then after two years(four compounding periods): \[ \begin{align} V_1 &= 0 (1.025) + 1000 \\ &= 1000 \\ V_2 &= 1000(1.025) + 1000 \\ &= 2025 \\ V_3 &= 2025 (1.025) + 1000 \\ &= 3075.63 \\ V_4 &= 3075.63 (1.025) + 1000 \\ &= 4152.52 \end{align}\]
Example : A0 is not 0
You invest $2000 initially and then $1000 at the end of every year for 2 years at an interest rate of 5% compounded half-yearly.
The number of compounding periods is 4 and the rate per period is the nominal rate divided by the number of compounding periods per year. So \[ \begin{align} i &= \frac{5}{100 \times 2} \quad \end{align}\ \]
Then after two years(four compounding periods): \[ \begin{align} V_1 &= 2000 (1.025) + 1000 \\ &= 3050\\ V_2 &= 3050(1.025) + 1000 \\ &= 4126.25 \\ V_3 &= 4126.25 (1.025) + 1000 \\ &= 5229.41 \\ V_4 &= 5229.41 (1.025) + 1000 \\ &= 6360.15 \end{align}\]
The interest earned is the future value less the total amount invested. For the last example above, \[ \begin{align} \text{Interest Earned} &= 6360.15 - 6000 \quad \\ &= 360.15 \end{align}\ \]
The present value of an investment annuity is the amount that if invested now, would compound to the future value of an annuity.
Using the compound interest formula \[ \begin{align} FV &= PV(1 + \frac{i}{100})^n \\ PV &= \frac{FV}{(1 + \dfrac{i}{100})^n} \end{align}\]
Example For the investment annuity above, the present value is \[ \begin{align} PV &= \frac{6360.15}{(1.025)^4} \\ &= 5761.98 \end{align}\]
Find PV if FV = 1000, i = 4.5% pa and n = 5.
If you borrow a sum of V0 dollars from a bank at an interest rate of i percent per year compounded annually, and agree to repay A dollars per year:
At the end of the first year, the value of the balance outstanding V1 is \[ \begin{align} V_1 &= V_0 (1 + \frac{i}{100}) - A \end{align}\] At the end of the second year, the value of the loan outstanding V2 is \[ \begin{align} V_2 &= V_1 (1 + \frac{i}{100}) - A \end{align}\] and a general expression for the process is \[ \begin{align} V_{n+1} &= V_n (1 + \frac{i}{100}) - A \end{align}\]
If the compounding rate is more frequent then the expression is \[ \begin{align} V_{n+1} &= V_n (1 + \frac{i}{100m}) - A \end{align}\] where m is the number of compounding periods per year.
This is a recurrence relation for the value one year in the future of the loan.
Find V1 if i = 4.8% pa, V = 1000 and A = 100.
Find V2 if i = 4.8% pa compounded half-yearly, V = 1000 and A = 100.
Guided Examples
O E Qs
Example You borrow $4000 at an interest rate of 8% compounded quarterly and agree to repay $1000 at the end of every quarter for four quarters, with an additional repayment of the outstanding balance at the end of the year.
In this case, the number of compounding periods is 4 and the rate per period is the nominal rate divided by the number of compounding periods per year. So \[ \begin{align} i &= \frac{8}{100 \times 4} \quad \end{align}\ \] Then after one year: \[ \begin{align} V_1 &= 4000 (1.02) - 1000 \\ &= 3080 \quad \\ V_2 &= 3080 (1.02) - 1000 \\ &= 2141.60 \quad \\ V_3 &= 2141.60 (1.02) - 1000 \\ &= 1184.43 \quad \\ V_4 &= 1184.43 (1.02) - 1000 \\ &= 208.12 \quad \end{align} \ \] The interest paid on the loan is the total amount repaid plus the outstanding balance less the loan amount \[ \begin{align} \text{Interest Paid} &= 4000 + 208.12 - 4000 \\ &= 208.12 \end{align}\] The future value of a loan at the end of the agreed number of periods is of course zero.
Calculating the future value from the recurrence relation quickly becomes tedious.
Spreadsheets have functions for calculating parameters of investment annuities and loans.
The most important functions are:
Function | Parameters | |
Future Value | FV(rate, nper, pmt,[pv],[type]) | rate - the rate per compounding period nper - the number of compounding periods pmt - the payment per compounding period |
Present Value | PV(rate, nper, pmt,[pv],[type]) | rate - the rate per compounding period nper - the number of compounding periods pmt - the payment per compounding period |
Payment | PMT(rate, nper, pv,[fv],[type]) | rate - the rate per compounding period nper - the number of compounding periods pv - present value |
Important
Read the help text for the functions. They may not work the way you think.
Tips For Using Functions
Use cells at the top of the sheet for data entry
Calculate the period and rate from the description. An example:
Now use these calculated values as inputs to the spreadsheet functions.
Example To find the Future Value of investing $2000 initially and then $1000 at the start of every year for 2 years at an interest rate of 5% compounded half-yearly
Future Value functions generally expect A0 to be zero and the periodic payments to be constant. In this case A0 is $2000 so you need to split the problem into two - compound A0 for nper periods, calculate the future value of the remaining annuity, and add the results.
Future value of 2000 \[ \begin{align} \text{FV1} &= 2000(1.025)^4 \\ &= 2207.63 \end{align}\] For the future value of the annuity, using the future Value function gives \[ \begin{align} \text{FV2} &= FV(0.025, 4, -1000)\\ &= 4152.52 \end{align}\] and the future value is 2207.63 + 4152.52 = 6360.15.
To find the Present Value of the above annuity, use the Present Value function on the remaining annuity and add the present value of 2000 \[ \begin{align} \text{PV2} &= PV(0.025, 4, -1000)\\ &= 3761.97 \end{align}\] and the present value is 3761.97 + 2000 = 5761.97
A spreadsheet Payment function calculates the periodic payment that would be required to reduce the outstanding amount to zero at the end of the loan.
Example To find the Payment required on the loan example above
Using the Payment function gives \[ \begin{align} \text{Payment} &= PMT(0.02, 4, -4000)\\ &= 1050.50 \end{align}\]
You can also use the PMT function to calculate the periodic payments that would be required to produce a given future value.
Example To find the payment required to produce a future value of $5000 in three years. The available rate is 7% compounded half-yearly.
Using the Payment function with a present value of 0 gives \[ \begin{align} \text{Payment} &= PMT(0.035, 6, 0, -5000) \\ &= 763.34 \end{align}\] Note the difference in the number and values of parameters from the previous example.