Thread: Get to grips with calc

In common with Excel, Calc uses formulae to perform complex calculations. Here, we'll use it to work out the present value of spreading lease payments against purchasing a car outright, entering basic if'lformation regarding costs, interest rates and so on.

When comparing interest rates, the typical comparison is made U using what's known as the 'effective' interest rate - this is generally slightly higher than the annual percentage rate (APR). Click the Function wizard button in the toolbal, the worksheet and select Effective from the list of financial functions.

In the dialog box that appears, select the cell where you recorded the stated APR. Next, enter the number of payments for the year (usually 12) and click ok. The spreadsheet will calculate the effective interest rate and return a value for the cell where you entered the formula.

The next step is to create the section of our spreadsheet that will compare results. Before working out the calculations, we need some basic formatting in the spreadsheet in order to display our results visually. For the Payment column, point to the cell where you entered the list price of the car.



Leasing is more complex, because we wish to calculate the 'present' value for our monthly payments. Click the formula button on the toolbar and select the cell for monthly payments, followed by financial function PV. Click next and, under Rate, select the cell containing the APR divided by 12.

Two other fields are required to work out the present value. First, we must select the cell containing the payment period, as well as the amount paid each month in the field headed PMT. When these values are entered into the formula, you're ready for the final part of the equation.

For the final tltal in the Lease column, we also need to enter the present value for the final payment. Click the function button, add a plus sign after the previous value and enter the formula CellX1/(1 +CellX2/12)^CellX3, where CellX1 is the final payment value, CellX2 is the APR and CellX3 is the payment period.

With some formatting applied via the Autofo'rmatting feature in Calc, we can create a decent-looking table that contains the formulae for working out the values of leasing versus immediate payment. By changing the values in the cells at the top of the spreadsheet, this will return different totals below.