Microsoft Office Tutorials and References

In Depth Information

**The Basic Excel Financial Functions**

Matching time periods

A common problem when working with Excel’s financial functions is the matching of time

periods. 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:

h
If the first payment is made
in arrears
(after the first period), you use a type of 0 (zero),

which is generally the default.

h
If the first payment is made in advance, use a type of 1.

Down payments are not considered regular payments, so they don’t affect which type

argument you specify.

The Basic Excel Financial Functions

Excel has five basic financial functions: PV, FV, PMT, RATE, and NPER. I discuss each of these

functions in this section, and also provide examples.

All these functions are related, because they deal with different sides of the same

situation. Many of the arguments are the same from function to function.

Calculating present value

The PV function returns the present value of future cash flows. We know that money in the future

has a different value than money today. This function tells us how much that future money is

worth right now. Its syntax, with required arguments in bold, is