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


Car Loan Calculator using PMT Function in Excel

         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.
Continue reading

AT&T iPhone6 Upgrade: 2-Year contract vs. Next plans

iPhone6Upgrade Options with AT&T

         

         If you are getting ready to upgrade to iPhone6 through AT&T, you might be in for a surprise or two. The first surprise is that back in July of last year, AT&T changed its phone upgrade eligibility date from 20 months to 24 months.

         Using a 2-year contract option, this change most likely means that you did not qualify for an iPhone6 pre-order last Friday, and will probably have to wait till after September 19th’s official release to place your upgrade order. If you are not willing to wait, you can pay AT&T an upgrade surcharge ($40 if your upgrade date is later this month) to upgrade immediately.

         Alternatively, you can take a completely different route, and go with a Next 12 or Next 18 installment plans and forget about service contracts and early termination fees altogether. At first glance, it’s pretty hard to follow AT&T’s case for the Next plans.

         In this post I will present my own calculations and conclusions pertaining to different scenarios when either a 2-year contract or one of the two Next plans works better. Since Samsung Galaxy S5 is offered at the same price as iPhone6, these calculations will work for all of the Android lovers out there as well.

Continue reading