The tutor explains how to make a loan payment table.

Example: Make a table to show the retirement of a $5000 loan, at 5.99% compounded monthly, with monthly payments of $221.58. The loan will be retired in 24 payments.

(Note that the payment is known. For how to find it, see my post here.)

Solution: Five columns are needed: Balance Forward, rate, interest, payment, and End Balance. I’m imagining these are entered in a1 through a5.

I’m imagining the user skips a line under the column headings.

Now, in a3, enter 5000. In b3, enter =0.0599/12. Next, in c3, enter =a3*b3. In d3, enter =-221.58. Finally, in e3, enter =a3 +c3 + d3.

Now, in a4, enter =e3.

Whenever you key in =something, that cell contains a formula, even if that formula is just, for example, =100. Formulas can be copied down columns, etc.

Note that, by the instructions above, formulas have been entered into a4, b3, c3, d3, and e3. Each needs to be copied down to the 26th row to allow for 24 payments.

Right-click a4, then click Copy. Select a4 again, then drag the mouse down to the 26th row and release it. Keeping the mouse in the selected area, right-click, then click Paste. Repeat this process for each cell that contains a formula.

After you finish, you should find that the end balance of the last row is virtually zero (it might be a few pennies due to round off). Hence, the spreadsheet shows the retirement of the loan over 24 payments.

I did this same process in both Excel and LibreOffice Calc.

Jack of Oracle Tutoring by Jack and Diane, Campbell River, BC.