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:
- Pv – present value. Used for both single sums and annuities.
- Fv – future value. Used for both single sums and annuities.
- Nper – number of periods. Used for both single sums and annuities.
- Rate – interest rate for period. Used for both single sums and annuities.
- 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.
- =Pv(rate, nper, pv,fv, type)
- =Fv(rate, nper, pmt, pv, type)
- =Nper(rate, pmt, pv, fv, type)
- =Rate(nper, pmt, pv, fv, type, guess)
- =Pmt(rate,nper,pv,fv,type)
Keep in m
ind 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.