Excel Formulas vs. Functions – Auto Loan Calculator (PMT)

         If you are like me, you are using terms Excel Formulas and Functions interchangeably. However, these expressions represent distinctly different concepts. We can think of a formula as a statement that returns results of a certain user-defined calculation. Formulas can contain values (numeric, date, text,) cell references, ranges of cells, named ranges; they can perform any mathematical operation (addition, subtraction, multiplication, division, exponentiation,) as well as evaluate results of Excel’s built-in functions…A function is a predetermined formula that helps us simplify complex procedures and operations. For example, great folks at Microsoft programmed different functions that allow us to sum a group of numbers, search for a specific word within a text string, determine current date and time, or even figure out amount of our loan payment. Ability to construct complex Excel formulas , using multiple Excel functions is one of the prerequisites for becoming an Excel expert. Formulas always start with the equal sign ( = ), and we always enclose functional arguments (if any) inside parentheses.

          Let’s suppose that you need to calculate auto loan payment for your dream car – 2015 Ford Mustang GT. According to TrueCarPrice , for the base V8 model, you should expect to shell out $ 32,864 of your hard-earned dollars for this car; if you reside in the state that Ford Motor Company calls home. This price is comprised of $ 29,612 true value estimate, $ 567 market premium, $ 825 delivery charge, and state sales tax. According, to Bank Rate, you can realistically finance this purchase with a 3% interest rate loan. Since you just received your Holiday bonus, you want to put it towards a $5,000 down payment on your new car. Let’s calculate your monthly car payment using both: formula method without utilizing Excel functions: =P*i*(1+i)^n/((1+i)^n-1) , as well as through a formula incorporating Excel’s PMT function. Both of these methods rely on formulas to perform the necessary calculations, but only the second method makes use of an Excel function.

          =P*i*(1+i)^n/((1+i)^n-1) loan payment formula has the following parameters: P is the amount of loan principal, or amount to be financed. In our scenario, you will be financing $ 27,864 (vehicle price of $ 32,864 less $ 5,000 down-payment.) i is the interest rate per period. Note that our loan is quoted using annual interest rate, while we will be making monthly loan payments. This means that our interest rate will be 3% divided by 12 (number of months/payments per year) or 0.25%. Finally, n is the number of periods or number of payments that you will make over time. Since your loan matures in 5 years, you will be making 60 payments before paying off your Stang. Don’t forget that Excel follows math rules in establishing order of operations – Please Excuse My Dear Aunt Sally:

          In our worksheet, your Loan Amount is found in cell D19, Interest Rate is in D21, while Loan Term is in cell D23. Using loan calculator formula, your loan payment is determined via following Excel formula:
=D19*(D21/12)*(1+D21/12)^(D23*12)/((1+D21/12)^(D23*12)-1) .

          Nothing fancy, just a simple substitution of algebraic arguments with Excel cell values. It won’t be easy to make monthly payments of $ 500.68 each and every month for the next 5 years. To make things worse that low interest rate of 3% will add $ 2,127 to your total car cost, making it $ 35,041.

          We can simplify this formula using Excel’s Named Ranges (Formulas-Name Manager) and performing supporting calculations in the argument cells, instead of the output cell. To demonstrate this method, let’s assign name P to cell D19. Let’s then call cell D21 i , and cell D23 n . Furthermore, let’s make sure that D21 = 3%/12, and D23 = 5*12. Our calculation now simply becomes:

=P*i*(1+i)^n/((1+i)^n-1) .

          By now you are prepared to truly appreciate the value added by hundreds of Excel’s built-in functions. One of the Financial functions, called PMT is designed to perform the above calculation without aggravating you in the process. It has only three required parameters: rate (interest rate), nper (number of periods/payments), and PV (present value of your loan.) Passing our arguments to this function, we can determine our loan payment:
=-PMT(D21/12,D23*12,D19) without supporting calculations, or
=-PMT(D21,D23,D19) using supporting calculations.

          I used the negative sign – in front of this function to multiply it by -1, since Excel interprets a loan payment to be a negative number (cash outflow.) This function will work with any loan you might have: credit card, student, or even your mortgage:

          Go ahead, and download my Excel workbook solution.