by ·2 Comments

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

by ·2 Comments

Create a Holiday Email in Excel, using HYPERLINK function


Create Holiday Greetings Email in Excel, using HYPERLINK function

         It seems like this year passed by particularly fast, and now we are at a point when people expect to receive Holiday Greetings from us. Did you know to that you can compose your holiday email entirely in Excel, using the HYPERLINK function? We need to meet two basic requirements: 1) set up a default email client (Outlook, LotusNotes, Thunderbird, GroupWise, etc.,) AND 2) your entire message (including To/CC/BCC/Subject/Body fields) needs to be 255 characters long or shorter. While this method requires no macros, nor VBA code, it cannot actually “send” the email for you, it will open it in your default email client, but you still need to click the SEND button.

          HYPERLINK function has the following syntax (link_location,[friendly_name]). This means that the only required parameter is link_location, passing a sample email address argument, we can create a simple email link using this syntax: =HYPERLINK(“mailto:Email1@example.com”). I would strongly advise to also name your hyperlink, otherwise the cell containing it will appear to be blank. Unfortunately, we can find rather limited documentation on HYPERLINK function within Excel Help section. However, Microsoft did publish additional information about this function. This article can help us easily add To,CC,BCC,Subject, and Body fields to our email template.
Continue reading

by ·0 Comments

Top 10 Free resources to learn Microsoft Excel

Top 10 Free resources to learn Microsoft Excel

         Students often ask me to recommend best online Excel resources for further reference. After fighting the urge to LMGTFY them 🙂 , I suggest to utilize the largest search engine in the World, and trust Google to present the most relevant results for their query. (Searching for specific terms within relevant time frame would improve your search results dramatically.)

         In addition, the second largest search engine in the World can stream very helpful How-To videos on magnitude of topics. This means that one can search YouTube to learn not only how to unclog a toilet, but also how to create a PivotChart.

         If you are in a hurry, and don’t feel like watching a 10 minute video;SlideShare comes to the rescue, and offers a plethora of presentations, including Excel tutorials. Heck, you can browse through the SlideShare version of this post below.

         That being said, some of us feel safer when using credible and trusted sources for all of our Excel needs. I started compiling the list of useful resources before YouTube and Slideshare came to existence, and added others over time. I hope that this post will satisfy your thirst for Excel knowledge. Many of these sites include tutorials, blog entries, discussion forums and YouTube videos.

Continue reading