Category Archives: Financial Functions

Understanding Excel Time Value of Money Functions

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 mExcel time value of money ;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.

RRI Function

Below is an excerpt from our Excel Time Value of Money course written by Joseph Helstrom,CPA.

RRI

RRI is a new function that was introduced in Excel 2013. It returns the equivalent interest rate for the growth of an investment.  The inputs required are the number of periods, the present value and the future value.
As an example, let’s say you invested $100,000 for 8 years compounded annually and the investment grows to a value of $150,000.  What is the equivalent rate of return?  In these examples, we will type the formula directly into the cell and not use the function wizard.
Start in the cell with =RRI( and then type the inputs as shown below.
RRI function

 

 

 

The answer:

RRI - Time value of money function

About 5.2%.

Remember, compounding periods are important.  What if the investment was for 8 years compounded quarterly?  You would need to adjust NPER for the quarterly compounding so it would be 8 years x 4 compounding periods per year = 32.  You will also need to adjust the result if the desired answer is an annual rate.  You can type a new formula by starting with =RRI( and then typing the new inputs or selecting the cell and pressing F2 to edit the existing formula.
RRI function

 

 

The answer:

RRI -Time Value of Money function

 

 

This is the answer for a quarterly rate.  When multiplied by 4 to translate it into an annual rate, the answer is .012751 x 4 = .051004 or about 5.1%.
The quarterly compounding slightly reduced the rate necessary to achieve the same result.

  • You guys do a great job!  As a person in industry it can be really expensive to stay current and you have great options that are actually useful as well as cost effective!

    - Sue

  • Overall I was very pleased with the course. Previously I knew just enough about pivot tables to be dangerous. Now I see the power that they have and I feel much more confident using pivot tables. The course materials were very clear and well prepared. I’ll be looking at your website for future CPE hours.

    - Scott

  • Your ethics course was the most interesting and clear course I have taken. I usually dread the years it is required but this year was a pleasant surprise.

    - Terry