Microsoft Excel Functions Vol 2
()
About this ebook
Do not be left behind by the digital divide!
Messages such as the above are often seen on television and major newspapers. With the country’s push for information and communication literacy, the time is ripe for those wanting to enhance their basic knowledge of software applications, namely Microsoft Excel. Excel is used for many areas of decision making. With its built-in functions such as financial, date and time, and text, this book gives the reader an insight of how these functions can be used for analyzing data and converting it to information.
The book is written using a step-by-step approach for all users wanting to learn some of the advanced functions of Excel such as FV, PV, NPV, IRR, DATE, MONTH, YEAR, NOW, WEEKDAY, CONCATENATE, FIND, REPLACE, SUBSTITUTE, etc. All of these functions and many more are illustrated with practical examples with plenty of screen shots to give readers a better understanding in the application of these functions.
The book assumes that the reader is already familiar with the basics of Microsoft Excel such as copying, editing, formatting, and performing basic calculations. It is highlighted with visual graphics wherever possible to enhance the understanding of the reader further.
Read more from Palani Murugappan
Microsoft Excel Statistical and Advanced Functions for Decision Making Rating: 4 out of 5 stars4/5Financial Modelling and Analysis Using Microsoft Excel - For Non Finance Personnel Rating: 0 out of 5 stars0 ratingsCost Reduction Strategies for the Manufacturing Sector With Application of Microsoft Excel Rating: 0 out of 5 stars0 ratingsEffective Budgeting Using Microsoft Excel Rating: 0 out of 5 stars0 ratingsMastering Presentation Skills Using Microsoft Powerpoint Rating: 0 out of 5 stars0 ratingsCost Reduction Strategies Rating: 0 out of 5 stars0 ratingsEffective Budgetting Rating: 0 out of 5 stars0 ratingsExcel Functions for the Daily User Rating: 0 out of 5 stars0 ratingsExcel Functions for the Daily User - Vol 2 Rating: 0 out of 5 stars0 ratingsInternet Explorer Rating: 0 out of 5 stars0 ratings
Related to Microsoft Excel Functions Vol 2
Related ebooks
Pivot Tables for everyone. From simple tables to Power-Pivot: Useful guide for creating Pivot Tables in Excel Rating: 0 out of 5 stars0 ratingsMicrosoft Excel Functions Vol 1: 1 Rating: 1 out of 5 stars1/5Excel 2016 Hands-On Lab Rating: 0 out of 5 stars0 ratingsExcel 2019 – Business Basics & Beyond Rating: 0 out of 5 stars0 ratingsHidden Gems of Microsoft Excel Rating: 0 out of 5 stars0 ratingsData Analysis with Excel: Tips and tricks to kick start your excel skills Rating: 0 out of 5 stars0 ratingsConditional Formatting: Easy Excel Essentials, #2 Rating: 0 out of 5 stars0 ratingsIntermediate Excel 365: Excel 365 Essentials, #2 Rating: 0 out of 5 stars0 ratingsMore Excel Outside the Box: Unbelievable Excel Techniques from Excel MVP Bob Umlas Rating: 0 out of 5 stars0 ratingsExcel Pivot Tables & Charts Rating: 0 out of 5 stars0 ratingsExcel Formulas That Automate Tasks You No Longer Have Time For Rating: 5 out of 5 stars5/5Microsoft Office Excel 2013 PivotTables Rating: 4 out of 5 stars4/5The IF Functions: Easy Excel Essentials, #4 Rating: 0 out of 5 stars0 ratingsAdvance Excel 2016: Training guide Rating: 0 out of 5 stars0 ratingsExcel for Beginners 2023: A Step-by-Step and Comprehensive Guide to Master the Basics of Excel, with Formulas, Functions, & Charts Rating: 0 out of 5 stars0 ratingsExcel Custom Functions: Straight to the Point Rating: 0 out of 5 stars0 ratingsExcel 101: A Beginner's Guide for Mastering the Quintessence of Excel 2010-2019 in no time! Rating: 0 out of 5 stars0 ratingsExcel Subtotals Straight to the Point Rating: 0 out of 5 stars0 ratingsLearning Excel Made Easier Rating: 0 out of 5 stars0 ratingsExcel VBA - Intermediate Lessons in Excel VBA Programming for Professional Advancement: 2 Rating: 0 out of 5 stars0 ratings15 Most Powerful Features Of Pivot Tables: Save Your Time With MS Excel Rating: 0 out of 5 stars0 ratingsSecrets of Lookup: Become More Poductive With Vlookup Free Your Time Rating: 0 out of 5 stars0 ratingsExcel 101: A Beginner's & Intermediate's Guide for Mastering the Quintessence of Microsoft Excel (2010-2019 & 365) in no time! Rating: 0 out of 5 stars0 ratingsExcel 2019 Conditional Formatting: Easy Excel Essentials 2019, #3 Rating: 0 out of 5 stars0 ratingsExcel Essentials Rating: 0 out of 5 stars0 ratingsExcel : The Complete Ultimate Comprehensive Step-By-Step Guide To Learn Excel Programming Rating: 0 out of 5 stars0 ratingsJuggling Your Finances: Basic Excel Primer: Juggling Your Finances Rating: 0 out of 5 stars0 ratings
Finance & Money Management For You
Set for Life: An All-Out Approach to Early Financial Freedom Rating: 4 out of 5 stars4/5The 7 Habits of Highly Effective People: 15th Anniversary Infographics Edition Rating: 5 out of 5 stars5/5Financial Words You Should Know: Over 1,000 Essential Investment, Accounting, Real Estate, and Tax Words Rating: 4 out of 5 stars4/5The Intelligent Investor, Rev. Ed: The Definitive Book on Value Investing Rating: 4 out of 5 stars4/5Principles: Life and Work Rating: 4 out of 5 stars4/5The Psychology of Money: Timeless lessons on wealth, greed, and happiness Rating: 5 out of 5 stars5/5Capitalism and Freedom Rating: 4 out of 5 stars4/5Buy, Rehab, Rent, Refinance, Repeat: The BRRRR Rental Property Investment Strategy Made Simple Rating: 5 out of 5 stars5/5Die With Zero: Getting All You Can from Your Money and Your Life Rating: 4 out of 5 stars4/5The Richest Man in Babylon Rating: 4 out of 5 stars4/5The Total Money Makeover by Dave Ramsey: Summary and Analysis Rating: 4 out of 5 stars4/5Just Keep Buying: Proven ways to save money and build your wealth Rating: 5 out of 5 stars5/5The Great Reset: And the War for the World Rating: 4 out of 5 stars4/5Good to Great: Why Some Companies Make the Leap...And Others Don't Rating: 4 out of 5 stars4/5The Great Awakening: Defeating the Globalists and Launching the Next Great Renaissance Rating: 4 out of 5 stars4/5The Tax and Legal Playbook: Game-Changing Solutions To Your Small Business Questions Rating: 3 out of 5 stars3/5The Freedom Shortcut: How Anyone Can Generate True Passive Income Online, Escape the 9-5, and Live Anywhere Rating: 5 out of 5 stars5/5All Your Worth: The Ultimate Lifetime Money Plan Rating: 5 out of 5 stars5/5How to Make Money in Stocks: A Winning System in Good Times and Bad, Fourth Edition Rating: 5 out of 5 stars5/5Retire Before Mom and Dad: The Simple Numbers Behind A Lifetime of Financial Freedom Rating: 4 out of 5 stars4/5How Rich People Think: Condensed Edition Rating: 4 out of 5 stars4/5A Study of the Federal Reserve and its Secrets Rating: 4 out of 5 stars4/5
Reviews for Microsoft Excel Functions Vol 2
0 ratings0 reviews
Book preview
Microsoft Excel Functions Vol 2 - Palani Murugappan
PREFACE
Hello once again!
Welcome to the second of my Microsoft Excel Functions offering titled Simplified Advanced Excel Functions
.
Many books have been written on Microsoft Excel. However, not many emphasize on Microsoft Excel’s advanced built-in functions. This book does just that. It covers functions such as FV, PV, NPV, PMT, NPER, IRR, SLN, VDB, NOW, DATE, WEEKDAY, CONCATENATE, FIND, REPLACE, and many more.
Readers are then guided to these functions using a step by step approach. The book is catered for new and experienced Excel users. New users should not feel intimidated by these functions.
Instead of relying on text instructions to accomplish a task, this book shows you how and when the function is used. Along the way, the examples illustrated with graphics wherever possible, is used to enhance the understanding of the reader. Most of the examples are explained in detail and the resulting answer.
Learning something new should always be exciting. That is the precise reason why this book would suit most people who know the basics of Microsoft Excel and want to tap on the power of its built-in functions to help in decision making.
This book was written using Microsoft Excel XP. However, most of the functions covered in this book can also be used in Microsoft Excel 97 and 2000.
So, do not wait to try these advanced functions! Turn on your PC and get a head start with the examples!
Palani Murugappan
DEDICATION
For Senthi Rajah
To whom I have always looked up as a fatherly figure
Functions in Excel
Functions in Excel are a defined set of formulas that is used to perform calculations using specific values known as arguments, in a particular order or structure. Functions accept a number or numbers as arguments and return a value. This value can be another number or a logical value such as TRUE / FALSE.
Excel’s functions are categorized into many categories such as Financial, Date & Time, Mathematics & Trigonometry, Statistical, Lookup & Reference, Database, Text, Logical, and Information.
This book will emphasize on some of the above categories, namely Financial, Date & Time, and Text.
All examples and exercises in this book were accomplished by using Microsoft Excel XP.
Invoking a function
The quickest way to invoke a function is by clicking the Insert Function ( ) icon button located to the left of the Formula Bar, as illustrated below.
Alternatively, instead of the above, click the Insert Menu. Select Function.
This displays the Insert Function dialog box as illustrated.
To view the categories available, click the Select a category drop down list. A complete list of all the categories available is displayed.
Select a category. For example, select the Financial category. A complete list of all the financial functions available is then displayed in the Select a function list box as illustrated.
For the purpose of illustration, assume the function IRR is selected. Clicking the button will then display the Function Arguments dialog box as illustrated.
This is where you key in the values for the arguments as in the above.
Once you become familiar with some of the commonly used functions, you need not go through this procedure. Rather, you may type in the formula of the function directly into a specific cell. For example, instead of using the Function Arguments dialog box to insert the IRR function, you could select a specific cell, for example C4 and type the function formula as illustrated.
Press the Enter key. The IRR value returned is 53% (in this example)
Accessing Microsoft Excel Help
There will come a point in time when you need to use a function or gather more information of a function that you want to use. The best way to do this is to use the built-in Microsoft Excel Help. To access this, click the Help Menu. Select Microsoft Excel Help as illustrated.
The What would you like to do?
help text box is displayed. Type your command here in the text box provided. For example, to gather further help on the IRR function, type IRR as below.
Click the button. Another identical help text box with options for the IRR function is displayed.
Click on the IRR worksheet function option. The Microsoft Excel Help window displays the information for the IRR function as illustrated.
Using the above steps, you can search for more information or help on other Excel functions and features.
Financial functions
Excel’s financial functions are of great help to users who frequently use complex financial equations manually. Many a times, most of these complex equations or formulas can be replaced with the appropriate financial function.
For example, Excel provides several financial functions that help solve problems associated with annuity. An annuity is a series of regular cash flows over a period of time. An example of annuity is the cash flow for a car repayment that occurs according to a regular time interval or period.
Excel also includes financial functions to analyze uneven cash flows and to calculate depreciation using the double-declining balance depreciation method.
FV function
The future value (FV) function is used to calculate the future value of an investment based on periodic intervals, constants repayments, and constant interest rate. It is what a lump sum or series of cash flow will grow to by the end of a specific time period taking the interest factor into account.
The mathematical equation for the future value can be described as:
FV = (-pmt + (1+rate)nper + rate * (1+rate)nper * pv)/rate
The syntax for the FV function consists of five arguments and is as follows:
FV(rate, nper, pmt, pv, type)
The first argument rate is the interest rate per period which is constant.
The second argument nper is the total number of payment periods in an annuity.
The third argument pmt is the constant payment made for each period and cannot change over the life of the annuity. It would normally contain the principal and interest but no other fees or taxes.
The fourth argument pv is the present value (PV). It is the lump sum amount that a series of future payments is worth today. If pv is omitted, it is assumed to be zero.
The fifth and last argument type indicates when payments are due and is denoted by a 0 (payment due at the end of period) or 1 (payment due at the beginning of period). Also, if type=0, then interest is applied and payment due at the end of the month (assuming payment is on a monthly basis). An example of this is the case of credit card payments where the interest on the account is not applied until the period's end (nor is the payment due).
It should be noted that for all arguments the cash paid out is represented by negative numbers, and cash received such as dividends is represented by positive numbers.
To understand the use of the FV function, assume the following data set is provided.
Based on the above data, what would be the future value for the amount deposited ($50,000) based on the interest rate of 8.75%?
The above data can be translated to the following arguments.
To calculate the future value, assume cell B12 is selected.
Click the Insert Function ( ) icon button. The Insert Function dialog box is opened. Select the Financial category followed by the FV function as illustrated.
Click the button. The Function Arguments dialog box is opened.
Click the Collapse ( ) button to collapse the Function Arguments dialog box. Click on cell B6 to select the interest rate.
Click the Expand ( ) button to expand the Function Arguments dialog box.
Click to select the Nper text box. Collapse the dialog box. Select cell B7.
Expand the dialog box.
Similarly, using the above steps to expand and collapse the Function Arguments dialog box, select cell B8 for the Pmt value.
For the Pv value, select cell B8, which is the amount deposited.
Finally, for the final argument Type,