Tag Archives: PMT

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.

Computing a Loan Payment & Creating an Amortization Schedule

Computing the Periodic Payment for a Loan

Excel Financial Function

Computing the periodic payment is probably the most used Excel financial function simply because it is something everyone uses.
Let’s walk through an example.
You want to purchase a car and finance $24,000 over 24 months at an interest rate of 4%.   So, the question is –What is the monthly payment?
Here we have a value today (Pv) of $24,000.  We want a monthly payment for 24 months (Nper) at an annual interest rate of 4%.
First, we need to change the annual interest rate to a monthly one by dividing 4% by 12 = .0033333  Let’s reflect this in our worksheet.
Matching the rate to the payment frequency is important.  If you want to compute a monthly payment and have an annual rate, the rate must be divided by 12.  (So many people forget this little fact which is why I am really emphasizing it here.)

Present Value calculation
Click on cell B5, then click on the Fx button at the top left of the formulas tab.
At the top of the window that opens, select the category “Financial, then find and select PMT at the bottom.

Click OPMT-2K.

 

 

 

Fill in the cell references for Rate, Nper and PV as shown below: Rate is B3, Nper is B2 and PV is B1.
PMT-3
Click OK.
The monthly payment is $1,042.20.  Remember, since this was a monthly payment, the annual interest rate was adjusted by dividing it by 12.Also, please note that the answer is negative as it is considered an outflow. Yes- you can tell the programmer was not a CPA 🙂  The easiest way to fix this is to go up to the formula bar and put a negative sign in front of PMT so that it looks like =-PMT(B3,B2,B1)

Let’s  double-check our work by making an amortization schedule.
An amortization schedule starts with the initial loan amount and progresses it forward using the monthly interest rate and computed monthly payment.

Start with the beginning balance of $24,000.  Add interest to this amount at 4% divided by 12 (=B1*.04/12).  Reflect the payment as a negative amount and sum across the row (B4:D4) for the ending balance.

PMT-5

The new beginning balance in B5 should be the result in E4 (the formula in B5 will be =E4).  Copy these down 23 rows (for a total of 23 periods).  The ending balance should be very close to zero, but may not be exactly zero due to rounding (the use of only two decimal points in the payment).

amortization schedule

This proves that the monthly payment for a loan of $24,000 over 24 months is correct and provides us with the interest and remaining balance at the end of each payment period.

Mortgages work identically except that the number of periods is generally longer.

 

  • “..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

  • I wanted to compliment you on the Accounting Fraud – Recent Case Studies (AUCASEMC) course I just took. The ebook was fantastic! Concise, easy to understand, very well structured, and very interesting. Exactly the way I like it. Thank you for putting it together. I feel it took me enough time to be worth 2 CPEs, but it was fantastic nonetheless. Thank you.

    - Mark R.

  • 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