Using financial functions

The idea of borrowing money for a specified rate of interest or earning interest on an investment is something that we are all familiar with. Interest is a percentage but one that has a time based component. Interest is calculated and paid at a regular intervals and this makes its behavior rather more varied than a simple static percentage.

Many financial arrangements are specified in terms of interest which is a percentage of the total per time period. Interest is a percentage rate - so many percent per month, so many percent per year and so on.

It is a rate in the sense of something that involves the passage of time - miles per hour, kilometres per second and 10% per month are all rates - and 10% per month is a very different amount of money from 10% per annum. Clearly converting between interest rates quoted for different time periods is something that we are going to have to be aware of.

The other point to note is terminology and convention.  Present Value, PV, is used for the sum of money involved at the start of a loan or investment and Future Value, or FV, for the final amount. In other words, FV is what results after interest has acted on PV.

This jargon applies equally to investments or loans. In the case of an investment the amount of money that is deposited or invested is the PV and the final balance is the FV. In the case of a loan the sum borrowed is the PV and balance after a series of payments is made is the FV. In all cases cash paid out is negative and cash paid in is positive. So for example in the case of an investment the cash flow out is negative but its future value is positive. For a fuller explanation of this see Introduction to Cashflow.

Loan repayments

Let’s start with one of the simplest of the financial functions – PMT, standing for payment.

Suppose you want to borrow $8000 over a period of 4 years at an interest rate of 6.9%, how much will you have to repay every month? To investigate this we will create a Loan spreadsheet.

Type the title "Loan Spreadsheet" into A1 then type the labels “Amount” in A3, “Years” in A4, “Interest Rate” in A5, and “Monthly payment” in A6 and enter the values 8000 in B3 and 4 in B4.

Next type 6.9% into B5. You may be surprised to see that the spreadsheet displays 7%. This is because by default the percentage display doesn’t have decimal places.

To remedy this right-click on B5 and select Format Cells from the menu. In the pop-up box that opens Percentage is already selected so all that you have to do is replace 0 in the Decimal places box by 2 and click OK.

Now place the cursor in B6 but rather than type anything in the entry line click instead on the fx icon to its immediate left to open the Insert Function dialog box. Click on the arrow to the right of Select a category and choose Financial from the drop down list, the scroll through the options to find PMT and click on OK.

The advantage of using the Function Arguments box is that you can point to the cells that you want to include. However, there is still some typing required.

For the Rate box click on B5 but then to convert it from an annual interest rate to a monthly one add“/12”. Notice that the value used as the Rate is 0.00575.

Move to the next box Nper (number of periods) using the mouse or by pressing the Tab key. Then click on B4, which again needs attention – this time to convert from years to months by multiplying by 12, i.e. typing “*12” on the same line.

Move to the Pv (Present Value) box and click on B3, the amount you want to borrow. At this point notice that the formula on the entry line reads =PMT(B5/12,B4*12,B3) and the result of the formula has appeared at the bottom of the dialog box.

Click OK. Notice that the amount of the monthly payment, $191.20, displays in B6 with a negative sign and in red, signifying that it is something you have to pay out.

<ASIN:0955459915>

<ASIN:0470506938>