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
Search JabSto ::

Custom Search