Financial Calculation Software, a Snap with MS Excel

Microsoft Excel is a number cruncher par excellance. Lotus 1-2-3 was also a great program that did crunch numbers very efficiently, but that has largely diappeared due to Microsoft's established position and relentless innovations (in MS Excel 2002 you can still open Lotus 1-2-3 files). In spite of a large number of quality financial products in the market, MS Excel continues to hold a preeminent position. It's main competitors are OpenOffice.org Calc, StarOffice and Corel Quattro Pro according to wikipedia.

MS Excel has a large number of built-in functions covering a wide area from manipulating strings to statistical calculations and everything in between. The financial functions built into MS Excel have a large user base that includes banks, insurance, investment, real estate and continues on to an endless list. If you are looking to work in a finance related business, learning to work with financial calculations with MS Excel is a must, as it is the major spread sheet product. In effect, this tutorial will teach you a little about programming in Excel, as well as discuss its functions.

There are over 50 financial functions available in MS Excel (this tutorial uses Excel 2002), however to have access to all these functions you may have to use the Analysis ToolPak add-in from tools. For this tutorial I will be describing in detail how to access a financial function, what the function calculates, how to implement the function, and so on. In particular we will be looking at a very familiar calculation that most of us would have come across while buying a car, or getting a loan from a bank to buy a property.

We will be looking at fixed interest loans which are normally quoted for 30, 15, and 10 years. This type of loan requires you to pay back equal installments over the period of the loan. In the early part of your loan you will be paying more interest and less principal, and towards the end of the loan period more principal and less interest. There is a plethora of financial loan products apart from this kind of loan. What is assumed in the fixed interest loan is that the rate does not vary during the life of the loan.

In this tutorial we will be asking the question, suppose one borrows $10,000.00 at an interest of 8% per annum (per year), to be paid back in 10 months -- what will the monthly payment be? Since banks and loan companies want you to pay on a fixed date each month, you may also want to know the effect of paying at the beginning of the month the loan is due, or at the end of the month the loan is due.

The Excel function PMT calculates the periodic payments that one needs to pay in order to liquidate the loan over a period of time. The syntax of this function is as follows:

This function has five arguments. The first three are mandatory and the last two have default values, if left out. In the definition, the argument rate refers to the rate applicable to a single repayment period. Let us say the annual interest is 8% and the loan is to be paid off in 10 months. This requires us to set rate applicable to monthly rate, or 8%/12. The argument nper is the number of equal payments agreed upon, and the argument pv is the amount that is borrowed. Since the loan is going to be liquidated, the future value, argument fv is 0. If fv is not used in the function then it is assumed to be 0. The argument type has two values, 0 or 1. If the payment is made at the end of loan period, it is zero; if the payment is made at the beginning of the pay period, it is one. The default value for Type is 0.

Functions can be inserted into cells by clicking on insert in the main menu, and clicking on Function... in the drop down as shown in the next picture. The calculated value of the function will be inserted into cell A1.

This brings up the Insert function window, wherein you either search for what you are looking for, or click on the or select a category: drop-down which gives access to a number of categories of functions. From this list choose the Financial Category. There are a large number of financial functions that can be chosen from the Select a function: list. You will observe that as each function is selected, the definition changes in this window as shown. In this case PMT is chosen, and therefore you will see the definition of the function and a short description of what the function accomplishes. There is also a hyperlink, Help on this function.

Clicking on this hyperlink will take you to this online help page as shown, which gives a more detailed explanation of the function with examples of the usage.

On the other hand , if you click on OK in the Insert function window you will pop up an interactive data entry page, Function Arguments as shown in the next picture. This is the interface into which you enter the three mandatory arguments (Rate, Nper, PV), and two optional arguments (Fv and Type).

We are considering taking out a loan of $10,000.00, at an annual rate of 8% to be paid off in 10 months. Since we are paying out every month, the rate to be inserted into this interactive dialog is 8%/12. Start with the first box and enter 8%/12, move to the next and enter 10 for Nper, and then move to the third to enter 10000. Since the next two are optional, you may just click OK. The PMT to be paid out every month is $1,037.03 as shown in the picture that follows. Since values were not entered for Fv and Type, they were assumed to be both 0. This means, by default, the payments have to be made at the end of the period and the loan will be completely paid off.

A simple spread sheet to calculate PMT

If this is all the program can do, it is not interesting and you do not even need a spread sheet. It is possible to transfer values entered into spread sheet cells to be automatically entered into the interactive Function Arguments dialog as described in this section.

In the work sheet for this calculation, set up the cells as shown in the next picture. In cells c2, c3, and c4 enter the input quantities (arguments for the PMT function), 8%/12, 10, and 10000. If you just type in 8%/12 it will be assumed to be string; enter it as =(8%/12), then the calculated value you see in the next picture is entered in c2.

Cell c8 is where you want your calculated value of PMT to appear. Place your cursor in this cell, and from the main kick up the Insert function dialog as described earlier. Choose to use the PMT function, which brings up the interactivedialog. Now click on the button shown in the next picture.

This brings up the next dialog as shown in this picture. Now click into cell C2 so that the value in the cell is transferred to the argument cell pointed by this window. After clicking in C2 click on the button as shown here.

This brings back the interactive dialog. A partial cutout of the screen is shown in the next picture. Whatever has been entered is shown in this picture. Continue in the same manner to enter the three values. You may skip this step for fv and type so that the program may assume defaults.

Now the final value is entered and the screen shows the cells in the arguments' locations rather than hard coded values. Clicking OK will show the result of this calculation in the cell C8. Even before clicking OK, the value is already calculated as shown in the next picture, and clicking OK will insert it into cell C8. Now we can go ahead and change the values in the cell (to test what if scenarios, such as what if the interest rate is 8.5% instead of 8%?) and it will be immediately calculated in cell C8. For example, if you pay off the loan over 12 months instead of 10 months you need only pay $869.88 per month.

The next picture shows two cases where payments were made at the beginning or end of the pay period. Notice the values for Type entered in the cells C6 or C7.

Summary

As demonstrated here MS Excel is truly a RAD tool for financial calculations. As mentioned earlier there are more than 50 functions available that will increase productivity in the work place. The online help is extremely useful. Combined with the full power of the Office products, it is no wonder that it maintains its premier place in the industry.