Formulae & Spreadsheets (AQA Level 3 Mathematical Studies (Core Maths))
Revision Note
Formulae & Spreadsheets
Spreadsheets are used widely within finance
They allow information to be tabulated clearly, and calculations to become automated using formulae
Spreadsheets are useful to both individuals and businesses for tracking and modelling their finances and budgets
What do I need to know about formulae and spreadsheets?
Columns are denoted by letters, and rows are denoted by numbers
When entering a formula, start with the character
=this tells the spreadsheet that what follows is a formula and not just textYou should be able to use some basic formulae involving add, subtract, multiply, divide, and SUM
=B1+B2+B3Returns the sum of the values in cells B1, B2, and B3
=E8-E7Returns the value of cell E7 subtracted from cell E8
=4*C6Returns the product of 4 and the value in cell C6
=B7/D8Returns the value of cell B7 divided by cell D8
=SUM(A3:A8)Returns the sum of the values in cells A3 to A8 (6 values in total)
Exam Tip
In an exam you may be asked to write a simple formula that could be entered into a cell to carry out a calculation.
Make sure you include the
=Use cell references, like
C5, rather than the cell values where relevant
Worked Example
A mortgage is taken out for £200 000 at the end of March 2024.
The annual interest is 4.8%, charged monthly at 0.4%. The monthly payment is £1049.
At the end of each month, the interest is added on, and then the payment is deducted. The spreadsheet shows some information about the amount still owed at the end of each month.
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | Month | Monthly Interest (as decimal) | Interest added on | Payment | Balance |
2 | Initial Borrowing | 200 000.00 | |||
3 | April 2024 | 0.004 | 1 049.00 | ||
4 | May 2024 | 0.004 | 1 049.00 | ||
5 | June 2024 | 0.004 | 1 049.00 | ||
6 | July 2024 | 0.004 | 1 049.00 | ||
7 | August 2024 | 0.004 | 1 049.00 |
(a) Write a formula to calculate the value in cell C3.
Cell C3 shows the interest added on for April 2024
The interest added on each month is the balance multiplied by the monthly interest rate
=B3*E2
(b) Write a formula to calculate the value in cell E3.
Cell E3 shows the remaining balance at the end of April 2024
Take the previous balance (E2), add on the interest charged for the month (C3), and subtract the payment made (D3)
=E2+C3-D3
(c) Write a formula to calculate the percentage of the original mortgage still left to pay at the end of August 2024.
The balance at the end of August 2024 is in cell E7
Find this as a fraction of the original balance (E2), and multiply by 100
=(E7/E2)*100
Spreadsheets can be formatted so that decimals are presented as percentages, (e.g. 0.73 will display as 73%), so multiplying by 100 is optional
(d) Write a formula using the SUM function to find the total paid between April and August inclusive.
The amount paid each month is in column D
=SUM(D3:D7)
You've read 0 of your 10 free revision notes
Unlock more, it's free!
Did this page help you?