Financial Functions: Creating a Payment Chart
One of the most useful and widely used financial functions available in OpenOffice Calc is PMT. We have discussed this
before.
It’s use is illustrated below in a simple example showing how to calculate the payments on a loan with fixed interest rate over a specified repayment term.
The advantage of using a spreadsheet in this manner is that we can plug different parameters into the equation and instantly see how it affects the payment we would need to make.
I will now extend this capability by showing how to create a table of payments for a range of loan amounts and interest rates. We first set up a blank table with the desired loan amounts on the side and the interest rates along the top.
In cell G4, we enter the following formula
We have already used this technique to create a multiplication chart
here
Using the ‘hybrid’ addressing mode results in OOo Calc generating the correct results when we autofill the formulae down the first column. More information on the autofill mechanism
here.
Likewise, we can autofill the complete table by dragging the first column across with the fill handle as shown below.
The above example spreadsheet can be downloaded
here
(ODF format)