# 6.04 Analysing investments with periodic payments

Lesson

### Tables and sequences for investments with a regular payment

Often an investor will make a regular payment to contribute to the growth of the investment. Tables and recursive rules are useful for more complex finance problems, such as a compound interest investment with regular deposits, or a reducing balance loan with regular payments. Investments involving compound interest are often displayed in a table of values so that the growth in the value of the investment can be clearly seen.

Sequence - Investment with regular payment

For a principal investment/loan, $P$P, at the compound interest rate of $r$r per period and payment $d$d per period, the sequence of the value of the investment over time forms a first order linear recurrence.

The sequence which generates the value, $V_n$Vn, of the investment/loan at the end of each instalment period is:

• Recursive sequence:

$V_n=V_{n-1}\times(1+r)+d$Vn=Vn1×(1+r)+d, where $V_0=P$V0=P

The sequence which generates the value, $V_n$Vn, of the investment/loan at the beginning of each instalment period is:

• Recursive sequence:

$V_n=V_{n-1}\times(1+r)+d$Vn=Vn1×(1+r)+d, where $V_1=P$V1=P

#### Worked example

##### Example 1

Fiona invests $\$14000$$14000 in an investment account that compounds annually. To further grow the investment she also makes a regular contribution of \2000$$2000 per year. The progression of the investment is shown in the table below.

Year Account balance at
start of year ($) Interest ($) Payment ($) Account balance at end of year ($)
1 $14000$14000 $350$350 $2000$2000 $16350$16350
2 $16350$16350 $408.75$408.75 $2000$2000 $18758.75$18758.75
3 $18758.75$18758.75 $468.97$468.97 $2000$2000 $21227.72$21227.72

(a) Determine the interest rate for the investment.

Think: We can use any of the table rows to do this. Use the formula

$\text{interest rate }=\frac{\text{amount of interest in year n}}{\text{account balance start of year n }}\times100$interest rate =amount of interest in year naccount balance start of year n ×100%

Do: Using the figures from Year 1 we find:

 Rate $=$= $\frac{350}{14000}$35014000​ $=$= $0.025$0.025 $=$= $2.5%$2.5%

Reflect: We can check we obtain the same result using the values from Year 2 or Year 3.

(b) Write a recursive rule for $V_n$Vn in terms of $V_{n−1}$Vn1 that gives the value of the account after $n$n years and an initial condition $V_0$V0.

Think: Using the sequence which generates the value, $V_n$Vn, of the investment at the end of each instalment, $V_n=V_{n-1}\times(1+r)+d$Vn=Vn1×(1+r)+d, where $V_0=P$V0=P. We have an initial investment of $V_0=14000$V0=14000, a regular deposit of $d=2000$d=2000 and the rate we found in part (a) to be $r=2.5%$r=2.5%.

Do:

$V_n=V_{n-1}\times(1+0.025)+2000$Vn=Vn1×(1+0.025)+2000, where $V_0=14000$V0=14000.

Select the brand of calculator you use below to work through an example involving compound interest investments with regular payments using sequences.

Casio Classpad

How to use the CASIO Classpad to complete the following tasks involving sequences in a compound investment context with a regular payment.

Nadia opens an account to help her save for a motorcycle. She opens the account with an initial deposit of $\$4000$$4000 that is compounded monthly at a rate of 3.5%3.5% per annum. She makes further deposits of \250$$250 at the end of each month.

1. Using a recursive relationship, generate the value of the investment at the end of each month for the first $6$6 months.

2. Find the balance in the account at the end of $1$1 year.

3. If the motorcycle costs $\$11500$$11500, at the end of which month will Nadia have enough saved to buy it? TI Nspire How to use the TI Nspire to complete the following tasks involving sequences in a compound investment context with a regular payment. Nadia opens an account to help her save for a motorcycle. She opens the account with an initial deposit of \4000$$4000 that is compounded monthly at a rate of $3.5%$3.5% per annum. She makes further deposits of $\$250$$250 at the end of each month. 1. Using a recursive relationship, generate the value of the investment at the end of each month for the first 66 months. 2. Find the balance in the account at the end of 11 year. 3. If the motorcycle costs \11500$$11500, at the end of which month will Nadia have enough saved to buy it?

#### Practice question

##### question 3

An investor deposits $\$20000$$20000 into a high earning account with interest of 4.5%4.5% p.a. compounded weekly and makes \150$$150 weekly deposits into the account.

1. If $N$N is the number of payments, complete the table of values showing the variables required to use a financial solver to determine how long it takes for the original investment to double in value.

Assume there are $52$52 weeks in a year.

Variable Value
$N$N -
$I\left(%\right)$I(%) $\editable{}$$%% PVPV \editable{} PmtPmt \editable{} FVFV \editable{} P/YP/Y \editable{} C/YC/Y \editable{} 2. Calculate the number of whole weeks it will take for the investment to double. 3. How much should the investor deposit each week in dollars if they want the original investment to double at the end of three years? Again, assume there are 5252 weeks in a year. Round your answer to the nearest cent. ### Using a spreadsheet to model compound interest investment Let's explore this interactive compound interest spreadsheet. When we explore different options with a financial problem we call it "what if analysis".  Created with Geogebra You can change the amount invested (the blue cell) to any value you'd like to invest. You can change the annual interest rate (the green cell) to any value. You can change the number of compounding periods (the pink cell) to quarterly (44), monthly (1212), weekly (5252) or perhaps daily (365365). #### Investigate • What happens as you increase the number of compounding periods? • What happens as you increase the annual interest rate? • How has the value in cell C10 been calculated? • How has the value in D12 been calculated? ### Creating a spreadsheet to model a compound investment with payments Spreadsheets can also include payment details and are a useful tool for solving financial problems as the progression of the investment can be clearly seen as well as the effect of changing interest rates and payments. #### Worked example ##### Example 2 Thomas needs \17000$$17000 for a house deposit. He invests$\$10000$$10000 in the bank with an interest rate of 8%8% p.a. compounded monthly and also makes a payment of \150$$150 per month. He creates the following spreadsheet to help him do "what if analysis" to examine the problem. Some of the formulae Thomas used to create this spreadsheet are shown in the table below. A B C D E 5 Month Balance start Interest Payment Balance end 6 1 =B1 =$B$2/12*B6 =$B$3 =B6+C6+D6 7 =A6+1 =E6 8 Note: • The spreadsheet is designed so that if the values in cells B1, B2 and B3 are changed, the whole page instantly updates. This makes it quick and easy to investigate different investment options. • The$ signs in the cell references make the reference absolute. That means the cell name will not change as the formula is copied down the column.
• The month numbers here have been calculated using a formula in cell A7.

(a) What is the purpose of the formula in cell C6?

Think: Ignore the $signs. The formula is =B2/12 * B6 Do: Write that it calculates the amount of monthly interest by taking the yearly interest rate (cell B2) and dividing by$12$12 then multiplying by the amount at the start of the month (cell B6). (b) What would the formula in cell C6 be if the interest was to be compounded quarterly instead of monthly? Think: We would divide the yearly rate by four instead of dividing by twelve. Do: Write the formula would be =$B$2/4*B6 or =B2/4*B6 (without an absolute reference). (c) What is the purpose of the formula in cell E6? Think: The formula is =B6+C6+D6 so it is added three values together. Do: Write that it calculates the balance at the end of the month by calculating start balance + interest + payment (d) Write the formula that will be in cell E7. Think: This will be the start amount + interest + payment for row 7 Do: Write the formula is =B7+C7+D7 (e) What is the purpose of the formula in cell B7? Think: The formula is =E6 and E6 contains the amount at the end of month 1. Do: Write that it copies the balance at the end of month 1, to the start of month 2. (f) Use Microsoft Excel, Google Sheets or your CAS calculator to create this spreadsheet. How long does it take Thomas to save the$\$17000$$17000? Reflect: The spreadsheet should show that it takes him 3030 months to save the money. #### Practice question ##### question 4 The spreadsheet below shows the first year of an investment with regular deposits.  AA BB CC DD EE 11 Year Beginning Balance Interest Deposit End Balance 22 11 60006000 660660 500500 71607160 33 44 55 1. Calculate the annual interest rate for this investment. 2. Write a formula for cell BB33. Enter only one letter or number per box. BB33 == \editable{}$$\editable{}$

3. Write a formula for cell $C$C$6$6 in terms of $B$B$6$6.

Enter one letter or a number per box.

$C$C$6$6 $=$= $\editable{}$$\ast$$\editable{}$$\editable{}$

4. Which of the following is the correct formula for cell $E$E$5$5?

$B$B $5$5 $\ast$ $C$C $5$5 $+$+ $D$D $5$5

A

$B$B $5$5 $+$+ $C$C $5$5 $-$ $D$D $5$5

B

$B$B $5$5 $+$+ $C$C $5$5 $+$+ $D$D $5$5

C

$B$B $5$5 $+$+ $C$C $5$5 $\ast$ $D$D $5$5

D

$B$B $5$5 $\ast$ $C$C $5$5 $\ast$ $D$D $5$5

E

$B$B $5$5 $-$ $C$C $5$5 $-$ $D$D $5$5

F
5. Use technology to reproduce this spreadsheet and determine the end balance for the $4$4th year.

6. Calculate the total interest earned over these $4$4 years.

### Outcomes

#### ACMGM099

use a recurrence relation to model an annuity, and investigate (numerically or graphically) the effect of the amount invested, the interest rate, and the payment amount on the duration of the annuity

#### ACMGM100

with the aid of a financial calculator or computer-based financial software, solve problems involving annuities (including perpetuities as a special case); for example, determining the amount to be invested in an annuity to provide a regular monthly income of a certain amount