**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.

Almost all professionals have used MS Excel in some form or the other. Most haven’t taken up any formal Ms Excel tutorial. Through self-study, they’ve picked up basic excel formulas and features to help them with their day-to-day tasks.

I think your statement highlights Excel’s versatility. One doesn’t have to take any formal classes to start using; it’s a rather intuitive application, which allows us to build upon our foundation.

Alex