Microsoft Excel as a Financial Calculator Part I

Excel (and other spreadsheet programs) is the greatest financial calculator ever made. There is more of a learning curve than a regular financial calculator, but it is much more powerful. This tutorial will demonstrate how to use Excel's financial functions to handle basic time value of money problems using the same examples as in the calculator tutorials. I will keep the examples rather elementary, but if you already understand the basics of using Excel, this tutorial will help you to understand the financial functions.
Analogy to Calculator Financial Keys
All financial calculators have five financial keys, and Excel's basic time value functions are exactly analogous. The table below shows the equivalency between the calculator keys and Excel functions:
Purpose
Calculator Key
Excel Function
Solve for Number of Periods
N
NPer(rate, pmt, pv, fv, type)
Solve for periodic interest rate
I/Yr
Rate(nper,pmt,pv,fv,type,guess)
Solve for present value
PV
PV(rate,nper,pmt,fv,type)
Solve for annuity payment
PMT
PMT(rate,nper,pv,fv,type)
Solve for future value
FV
FV(rate,nper,pmt,pv,type)
Just as you have to supply at least three of the variables to solve a TVM problem in a financial calculator, you also have to supply at least three of the arguments to each Excel function. Note that in the table, the bold function arguments are required while those in italics are optional.
You don't need to memorize the order of the function arguments. You can use the Insert Function dialog box, which will prompt your for the arguments by name. Or, if you prefer to type the function directly, Excel will display a Smart Tag that shows the order of the arguments as you type.
Example 1 - Future Value of Lump Sums
We'll begin with a very simple problem that will provide you with most of the skills to perform financial math using Microsoft Excel:
Suppose that you have $100 to invest for a period of 5 years at an interest rate of 10% per year. How much will you have accumulated at the end of this time period?
In this problem, the $100 is the present value (PV), NPer is 5, and Rate is 10%. Open a new workbook and enter the data as shown below, but leave B5 blank for now.

To find the future value of this lump sum investment we will use the FV function, which is defined as:
FV(rate,nper,pmt,pv,type)
Select cell B5 and then type: =FV(B3,B2,0,-B1) and then press Enter. The answer that you get should be 161.05.
A Couple of Notes
1. Every time value of money problem has either 4 or 5 variables (corresponding to the 5 basic financial variables). Of these, you will always be given 3 or 4 and asked to solve for the other. In this case, we have a 4-variable problem and were given 3 of them (Nper, Rate, and PV) and had to solve for the 4th (FV). Be sure that any variables not in the problem are set to 0, otherwise they will be included in the calculation. In this case, we did not have an annuity payment (PMT), so the third argument in the FV function was set to 0.
2. Note that we left out the optional Type argument. In all of these functions, the Type argument tells Excel when the first cash flow occurs (0 if at the end of the period, 1 if at the beginning). This argument is identical to setting your financial calculator to End Mode or Begin Mode, and only affects the answer when there is an annuity payment. When solving lump sum problems such as this, the argument has no effect. If you had typed =FV(B3,B2,0,-B1,1) you would have gotten the same answer.
3. Note that, unlike most financial calculators, there is no argument to set the compounding frequency. This is actually a good thing, in my opinion, because those settings on financial calculators cause all kinds of trouble when people forget to set them correctly. In Excel functions, you must set NPer to be the total number of periods, Rate to be the interest rate per period, and PMT to be the annuity payment per period. So, if this