Finance · Topic 15 of 19
Accumulation: Investment Schedules
Theory
When saving for a long-term goal (like retirement or a deposit for a house), making regular monthly payments over many years requires hundreds of repetitive calculations. Instead of doing this manually using the Chronological Method, we use spreadsheet software (like Microsoft Excel) to automate the process by creating an Investment Schedule.
1. Setting up an Investment Schedule
A standard investment schedule tracks the balance of an account period by period. It typically features the following columns:
- Time (Months/Years): The specific time period.
- Opening Balance: The amount of money in the account at the start of the period.
- Interest Earned: The interest added to the account during that specific period.
- Deposit: The regular payment added to the account.
- Closing Balance: The final amount at the end of the period (Opening Balance + Interest + Deposit).
2. The Interest Rate Conversion Formula
In spreadsheet tasks, you will frequently be given an annual effective rate of interest, but you will need to calculate a monthly schedule.
You must instruct the spreadsheet to calculate the monthly rate using a fractional power.
3. Essential Spreadsheet Functions and Syntax
To gain full marks in the exam for spreadsheet formulas, you must use the correct syntax:
- Absolute Cell References ($): When you drag a formula down a column, spreadsheet software automatically changes the cell references. To "lock" a reference to a specific cell (like a fixed monthly interest rate or a fixed regular deposit), you must use dollar signs (e.g.,
$B$2). - The ROUND Function: In finance, money must always be rounded to two decimal places (the nearest penny). When calculating interest, you must use the
ROUNDfunction, or your schedule will accumulate hidden fractions of a penny and become inaccurate over time.
=ROUND(Opening_Balance * $Interest_Rate$, 2)
Worked examples
Example 1
Example 1: Time Frequency Conversion in Excel
Cal opens a savings account that offers an effective rate of interest of 4.2% per annum. He intends to create a monthly investment schedule in a spreadsheet.
He types his annual interest rate (4.2%) into cell B1. Write down the exact formula Cal should type into cell B2 to calculate the equivalent monthly effective rate of interest.
Cal must add 1 to the annual rate, raise it to the power of 1/12, and then subtract 1.
Example 2
Example 2: Writing Spreadsheet Formulae
A spreadsheet is set up to model a savings account.
- Cell B2 contains the monthly effective rate of interest.
- Cell B3 contains the fixed regular monthly deposit of £150.
- The schedule begins on row 6.
- For Month 2 (Row 7), the Opening Balance is in cell C7.
(a) Write down the formula that should be entered into cell D7 to calculate the interest earned for Month 2.
(b) Write down the formula that should be entered into cell F7 to calculate the Closing Balance for Month 2.
Solution (a):
We must multiply the opening balance by the interest rate, use absolute references to lock the interest rate cell, and round to 2 decimal places.
Solution (b):
The closing balance is the sum of the opening balance, the interest earned, and the fixed regular deposit (locked using absolute references).
(Alternatively, =SUM(C7:D7)+$B$3)
Example 3
Example 3: Manual Schedule Calculation (Exam Style)
Anika opens a new savings account to save for a deposit on a flat. The account offers an effective rate of interest of 0.35% per month. Anika deposits £400 on the 1st of every month. Interest is calculated and added at the end of the month.
Complete the investment schedule below to calculate the Closing Balance at the end of Month 2.
| Month | Opening Balance (£) | Interest Earned (£) | Deposit (£) | Closing Balance (£) |
|---|---|---|---|---|
| 1 | 0.00 | 0.00 | 400.00 | 400.00 |
| 2 | 400.00 |
You must calculate this exactly as a spreadsheet would, row by row.
- Opening Balance (Month 2): This is simply the closing balance from Month 1.
Opening Balance = £400.00 - Interest Earned (Month 2): Multiply the Opening Balance by the monthly rate (0.35% → 0.0035).
Interest Earned = £1.40 - Closing Balance (Month 2): Add the Opening Balance, Interest Earned, and the new Deposit together.
Closing Balance = £801.40
(The completed row should read: Opening: £400.00 | Interest: £1.40 | Deposit: £400.00 | Closing: £801.40).