Chapter 11: Borrowing and Investing Formulas

 Download CD Content

It's a safe bet that the most common use of Excel is to perform calculations involving money. Every day, people make hundreds of thousands of financial decisions based on the numbers that are calculated in a spreadsheet. These decisions range from simple (Can I afford to buy a new car?) to complex (Will purchasing XYZ Corporation result in a positive cash flow in the next 18 months?). This is the first of three chapters that discuss financial calculations that you can perform with the assistance of Excel.

Financial Concepts

Before you start using Excel's financial functions, you must be familiar with some basic concepts. These concepts are not specific to Excel, but they are necessary when constructing financial formulas. If you're already well versed in finance and financial terminology, a quick skim of this section is all that is needed. If you're new to creating financial formulas, make sure you have a solid understanding of the following concepts.

Time Value of Money

The concept of Time Value of Money, or TVM, simply means that money has a different value depending on what time it is. That is, not the time of day, but the time relative to right now (or some other defined time). If I give you $1,000 today, it's worth precisely $1,000. However, if I give you $1,000 in a year, that money will be worth $1,000 when I give to you, but it's worth something different today.

If you had the $1,000 today, you could put it in a savings account, invest it in stocks and bonds, or go on a wild shopping spree. You would have control over the money, and you could put it to work for you. Because you're not going to get the money for a year, it's worth less to you now. In fact, you may be willing to take a lesser amount if you got paid today.

These are all practical implementation of the concept of TVM:

• Banks charge and pay interest.

• Lotteries pay out less when you take the lump sum option.

• Vendors give a discount when you pay earlier than the normal terms.

Cash In and Cash Out

All financial formulas are based on cash flows: that is, cash that is flowing out (payments) and cash that is flowing in (receipts). Even those financial transactions that don't seem to be dealing with cash flows, really are. If you buy a car on credit, you get a car, and the bank gets a promise. No cash, right? From a financial perspective, the bank is giving you cash to buy a car (positive cash flow to you); and, in the future, you will pay back that money (negative cash flow to you). The fact that the money was used to buy a car doesn't change the underlying financial transaction. Always think in terms of cash in or out.

The first decision you make when constructing a financial formula is who is asking the question. Because you must designate the cash flows as either positive or negative, you need to determine from whose pocket the cash will be flowing:

• If you buy a house and calculate your mortgage payments, the cash flows are from your perspective:

  • When you borrow the money for the house, it's a positive cash flow.

  • When you make mortgage payments, it's a negative cash flow.

• The cash flows are the opposite if the bank is doing the calculation.

In Excel's financial functions, positive cash flows are shown as positive values, and negative cash flows are shows as negative values.

Tip 

When a formula returns a result that you know is wrong, the first place to look is at the signs in front of the cash flow numbers.

Matching Time Periods

A common problem that you may encounter when working with Excel's financial functions is the matching of time periods. It's a simple concept, but it is often overlooked. Simply put, the time period that your payment covers must match the time period of your interest rate. If you put a monthly payment into a financial function, along with an annual interest rate, the result will be wrong. In this case, you need to convert the interest rate to a monthly rate so it matches the payment frequency.

The examples in this chapter deal with the issue of matching time periods explicitly. When you see an interest rate divided by 12, it probably means that an annual interest rate is being converted into a monthly interest rate.

Timing of the First Payment

The final concept to keep in mind when constructing financial formulas is the timing of the first payment. Sometimes the first payment is made right away. Usually, the first payment is made after the first month (or whatever period payments are normally made). For example, if you get a car loan on May 15, you probably don't have to make the first payment until June 15.

In Excel formulas, first payment timing is handled in the Type argument of various functions:

• If the first payment is made in arrears (after the first period), you use a Type of 0 (zero), which is generally the default.

• If the first payment is made in advance, use a Type of 1.

Note 

Down payments are not considered regular payments, so they don't affect which Type argument you specify. Many of the examples in this chapter use down payments, and their handling is discussed then.