Understanding Excel Time Value of Money Functions

Below is an excerpt from our  Excel Time Value of Money Functions for CPAs course.

There are certain terms and conventions in Excel that are extremely important to understand prior to working with Excel time value of money functions.  The most important to understand are the terms used in Excel.

Most Excel time value of money functions contain four or five basic inputs.  They are:

1. Pv – present value. Used for both single sums and annuities.
2. Fv – future value. Used for both single sums and annuities.
3. Nper – number of periods. Used for both single sums and annuities.
4. Rate – interest rate for period. Used for both single sums and annuities.
5. Pmt – periodic payment. Used only for annuities.

Functions may be entered directly or a function wizard may be used for input.  If the functions are entered directly, the required inputs and structure are below.

1. =Pv(rate, nper, pv,fv, type)
2. =Fv(rate, nper, pmt, pv, type)
3. =Nper(rate, pmt, pv, fv, type)
4. =Rate(nper, pmt, pv, fv, type, guess)
5. =Pmt(rate,nper,pv,fv,type)

Keep in mind that, at a minimum, only three of the basic inputs are required to arrive at the correct answer.  Type (not one of the basic inputs) refers to when annuity payments are made (either at the beginning or end of a period) and, if omitted, the default is at the end of a period.  Guess (also not one of the basic inputs) is literally a guess of the correct answer and helps Excel narrow its calculations. It is not required, but may be necessary in the IRR calculation to arrive at an answer.

Most find it easier to use the function wizard as it is not necessary to remember the function inputs and you can see the function result prior to clicking “OK”.  For the Pv function, the function wizard is displayed below.

Excel uses a sign convention that indicates whether an amount is a cash inflow or cash outflow.  It gets a little confusing.  Basically, if you’re investing, that amount would be reflected as a negative amount – a cash outflow to the investment.  If you’re receiving a loan, that would be a positive amount as it is a cash inflow to you.  A cash payment for a loan (Pmt) would have a negative sign while cash received from an investment would have a positive sign.  Some people are confused when they compute a payment or a present or future value and it results in a negative amount.  However, there are certain calculations where the sign convention is very important.  This will also be covered in greater detail in the specific examples contained in the course.

Of the five (or six) function arguments that are available, make sure that if they’re not needed, they are set to blank or zero.  Anything other than blank or zero will enter into the calculation and make the result incorrect.  As an example, if you want to know the present value of a single sum and your inputs are Rate, Nper, and Fv, make sure that Pmt has a value of blank or zero as this argument is not needed.  While Type should have a value of zero as well, it is ignored in a single sum calculation.  However, in an annuity example, you would need to be more aware of the Type argument as it would have an impact on the result.

Excel does not have an option for compounding frequency.  In Excel, Nper must be the total number of periods and Rate must be adjusted so that it conforms to a per period basis.  As an example, if you’re dealing with a 5 year loan at an annual interest rate of 12% where payments are monthly, there are a total of 60 monthly payments in 5 years.  Nper in this case is 60.  Since the payments are monthly, Rate must also be adjusted from an annual rate to a monthly rate by dividing the annual rate by 12.

The Rate argument must be entered as a decimal.  If you want to enter 12%, it must be reflected as .12.  Having 12 as an input in this example would provide an incorrect result.

The “Type” argument is only used in annuity calculations and is used to establish whether the payment is made at the beginning of the period or at the end of a period.  The default in Excel is at the end of a period.  However, there are instances where the cash flow occurs at the beginning of the period and, in these instances, you must change the Type argument to 1.    In an annuity calculation, the resulting answer will be different depending on the Type argument selected.

While not required, it is suggested that cell references be used as inputs for these functions.  By doing this, it is easier to review and should result in a lower likelihood of input errors.  It also makes it easier to change inputs.

The AGGREGATE Function

The AGGREGATE function is a very powerful function for summarizing an array of data.
It combines 19 different ways to summarize data into one function!
While it is a powerful function, my favorite reason for using AGGREGATE is its ability to SUM data with errors.

The syntax for the AGGREGATE function is =AGGREGATE(function #, options, array).
The function #’s are listed below:

As you can see, your options permit you to ignore nested subtotals, hidden rows as well as error values.
One of my biggest pet peeves is trying to sum data that has error values.  The AGGREGATE function makes this easy.
Assume you have data in cell A1:A6, and that you want to sum it.
However, because there is an error value in A5, the SUM function will return an error as shown in the screenshot on the left.
You can easily work around this by using the AGGREGATE function.

In this example, I clicked in A7 and  typed =AGGREGATE(

• A list of the various function #’s appears.  Note that if you elect to use the Insert Function dialog box, this handy list of functions does not appear.  We want to SUM the data, so function # 9 should be used, followed by a comma.

• After the comma, a list of options is presented.
• In this case, use option number 6, Ignore error values and place a comma after this.

• Lastly, identify the array to be summed.  This will be accomplished by using the cursor to highlight A1:A6.
• Close the parenthesis.
• Press Enter.

The “Must Know” component of the AGGREGATE function is how to SUM data with errors.  It is a very powerful function, though.  You may find other uses for it in your professional assignments.

This is an excerpt from “Must Know Functions for CPAs”.

• “..may I say it is rarer that I ever have gotten a personal reply from a CPE Site! Glad I have found your site.”

- Tom

• “always love your site and prompt customer service !! ”  Dec. 2017

- Thank you :)

• Thank you for providing very informative CPEs. I have enjoyed your courses for years now. Yours is truly my go-to CPE sources.

- Lily