Percentage Change

The remaining debt when taking an annuity loan.

Exercise 1 - Linear and percentage increase


Adam and Eve both have a monthly salary of 20 000 SEK. Adam negotiates to get a pay raise of 1400 SEK per year in monthly salary. Eve negotiates to get 5% increased monthly salary per year.

1.a - Show the pay raises by using fix numbers

Use two columns to represent the years, column A for Adam and column C for Eve. Make them by using relative copies.

Enter 20000 into B2 and D2. Write a spreadsheet-formula for Adam's pay raise by 1400 SEK in B3. Make relative copies along column B. Find the factor of change representing a percentage increase by 5%, and use this factor to write a spreadsheet-formula for Eve's pay raise in cell D3. Make relative copies along column D. The cells should show the salaries for the next 20 years.

Make two lists of points, one for Adam and one for Eve. Make sure that the points are shown in the drawing pad.

1.b - From fix numbers to variables

Store the value 1400 in a variable a, and the factor for Eve in a variable e (enter the variables in the input bar below the drawing pad). Change the spreadsheet-formulas in cells B3 and D3 so the variables are used instead of the fix numbers. Make new relative copies along columns B and D.

Make two new lists of points. Change the values of the variables by right-clicking on them and enter the properties window. Change the value for Adam to 2000 SEK and make Eves yearly increase 8%. When you change the value of a variable, all cells that depend on that value are changed and also all points.


1.c - From variables to sliders

The two variables can be seen in the algebra view. Click on the small circles to make them filled. By doing so, two so called sliders are created. Using a slider, you can change the value of a variable.

Open the properties window for each of the sliders. See to it that Adam's pay raise can be varied between 0 and 10000 SEK, and that Eve's can vary between 0 and 50%.

Save the worksheet!

Exercise 2 - Annuity loan using variables


When borrowing money using an annuity loan, you repay the same amount of money each year. When doing calculations using an annuity loan, you use so called geometric series. You can, however, show the debt over time by using a spreadsheet without using geometric series.

To model an annuity loan you need three variables; one to represent the loan, one to represent the annual interest rate (or rather the factor of change calculated from the interest rate), and one to represent the annual payment. Make three variables and let the loan be 1 500 000 SEK, the interest rate 6%, and the annual payment 120 000 SEK.

Let column A represent the years and column B the remaining debt. See the picture to the right. Enter the variable for the loan in cell B2. Cell B3 should contain the spreadsheet-formula for the debt after one year, after first increasing the debt by using the factor for percentage increase and then decreasing the debt by one annual payment. Make relative copies to show the remaining debt for 30 years.

Visualize the remaining debt in the graphics view as points.

Change all variables to sliders, the result should be similar to the applet at the top of this page.

Let the annual interest rate be 8% and the let the loan be 1 500 000 SEK. Try out various annual payments in order to answer the question:

How large must the annual payment be in order to guarantee that the loan has been repayed in at most 30 years if the loan is 1 500 000 SEK and the annual interest rate is 8%?

Linear and exponential growth

Salaries of Adam and Eve.


"Altogether, Adam lived 930 years, and then he died.."

Genesis 5:5

Zoom out to check out Eve's salary 1000 years later!

further info:

SEK?: The Currency Converter - Calculator for Swedish Krona (SEK)

by Malin Christersson under a Creative Commons Attribution-Noncommercial-Share Alike 2.5 Sweden License