« Merry Christmas | Main | Splitting an address (US) into separate cells »
December 27, 2005
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)
Posted by Dave at December 27, 2005 12:04 PM