Microsoft Excel Functions Vol 2

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

palani12@yahoo.com

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,