Ronald ross p. G. College (mba) koheda road, mangalpally (V) ibrahimpatnam (M), R. R. (Dist) 505510

Macros

Recording A Macro

Macros can speed up any common editing sequence you may execute in an Excel spreadsheet. In this example we will make a simple macro that will set all the margins on the page to one inch.

Click Tools|Macro|Record New Macro from the menu bar.

• 
  Name the macro in the Macro name field. The name cannot contain spaces and must not begin with a number.
  
• 
  If you would like to assign a shortcut key to the macro for easy use, enter the letter under Shortcut key. Enter a lower case letter to make a CTRL+number shortcut and enter an upper case letter to assign a CTRL+SHIFT+number shortcut key. If you select a shortcut key that Excel already uses, your macro will overwrite that function.
  
• 
  Select an option from the Store macro in drop-down menu.
  
• 
  Enter a description of the macro in the Description field. This is for your reference only so you remember what the macro does.
  
• 
  Click OK when you are ready to start recording.
  
• 
  Select options from the drop down menus and Excel will record the options you choose from the dialog boxes, such as changing the margins on the Page Setup window. Select File|Page Setup and change all the margins to 1". Press OK. Replace this step with whatever commands you want your macro to execute. Select only options that modify the worksheet. Toggle actions such as View|Toolbars that have no effect on the worksheet will not be recorded.
  

• 
  Click the Stop button the recording toolbar. The macro is now saved.
  

Running A Macro

• 
  To run a macro you have created, select Tools|Macro|Macros from the menu bar.
  
• 
  From the Macros window, highlight the Macro name in the list and click Run.
  

• 
  If the macro is long and you want to stop it while it is running, press BREAK (hold CTRL and press PAUSE).
  

Printing

Objective To control the printing options.

Instructions You will use the Page Setup, Print Preview and Print commands from the File menu.

Comments Having gone to all this work to create your spreadsheet, you will want to take away a printed copy.



Activity 10.1 Before printing, you will need to ensure that Excel is 'talking' the right printer language and attached to the correct printer queue. To do this you should consult the document Getting Started with Microsoft Windows (BEG 2).

Activity 10.2 Once the right printer has been selected, you will probably want to check how your page is set up. Select the Page Setup... command from the File menu. You should get the dialogue box as shown below:

Click on the Centre Horizontally and Centre Vertically check boxes. Then click .

Activity 10.3 We will now preview the spreadsheet on screen. Previewing your spreadsheet, before committing it to the printer saves you time, effort and money so it is recommended. As a precaution you are advised to save your work beforehand.

From the File menu choose the Print Preview command. The screen will display a preview of the printout. To examine the worksheet more closely, click on the Zoom button. When you are satisfied with the Preview, click on the

button. The spreadsheet window will reappear with a dialogue box checking that you are sending the print to the right place, how much of the sheet you want printed and how many copies. Click and a small dialog box appears giving the progress of the printing. Wait until this disappears before continuing with your work.

NPV PROJECT

 This segment will show you how to determine the NET PRESENT VALUE (NPV) of a series of cash flows.

In this example we have a series of cash flows starting at the beginning of Period 1, which we called Period 0. An outflow of 20 000, and then the next five years we have inflows of cash.

We would like to determine what the NET PRESENT VALUE is of these series of cash flows. In order to do this we can make use of the NET PRESENT VALUE function.

• 
  if we click on the cell
  
• 
  and go the Function Wizard
  
• 
  go into the Financial categories
  
• 
  and find the NPV function
  
• 
  This will appear, we are asked to fill it in.
  
• 
  The first criteria is to give it a Rate, which is a rate of discount over the length of one period. In this case it’s this cell here,
  
• 
  we now need to enter the Values and if you click on the first one you’ll see that it tells you here its Value 1, 2 all the way up to 29 of income, equally space in time and occurring at the end of each period.
  

Now this is very, very important because if you look at our numbers, Period 0 is actually the beginning of Period 1 and then Period 1 that’s the end, the end, the end.

If we assume the values of all these cells we’ll be incorrect because Excel will assume that this 20 000 investment actually occurs at the end of Period 1. So in order to do this is our Value 1

• 
  we highlight all cash flows that happen at the end of the period
  
• 
  and when we push Ok
  
• 
  you’ll see we get a NET PRESENT VALUE
  

However we have to be very careful because what we’ve told it to do, is give us the NET PRESENT VALUE of all the cash flows at the end of the years. We have excluded this, now because it happens at the beginning of Period 1 we don’t have to worry about present valuing it because the number is already present values.

So in order to get an accurate NET PRESENT VALUE, you need to subtract the initial investment.

• 
  so you’ll see we’ve got a cell here for Initial Investment,
  
• 
  you make that, equal to the beginning period
  
• 
  when we push enter
  
• 
  you’ll see I’ve summed these two,
  

so the NET PRESENT VALUE of these series of cash flows is actually this number here.

Returns the internal rate of return for a series of cash flows represented by the numbers in values. These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received for an investment consisting of payments (negative values) and income (positive values) that occur at regular periods.

IRR(values,guess)

Values   is an array or a reference to cells that contain numbers for which you want to calculate the internal rate of return.

• 
  Values must contain at least one positive value and one negative value to calculate the internal rate of return.
  
• 
  IRR uses the order of values to interpret the order of cash flows. Be sure to enter your payment and income values in the sequence you want.
  
• 
  If an array or reference argument contains text, logical values, or empty cells, those values are ignored.
  

Guess   is a number that you guess is close to the result of IRR.

• 
  Microsoft Excel uses an iterative technique for calculating IRR. Starting with guess, IRR cycles through the calculation until the result is accurate within 0.00001 percent. If IRR can't find a result that works after 20 tries, the #NUM! error value is returned.
  
• 
  In most cases you do not need to provide guess for the IRR calculation. If guess is omitted, it is assumed to be 0.1 (10 percent).
  
• 
  If IRR gives the #NUM! error value, or if the result is not close to what you expected, try again with a different value for guess.
  

Remarks

IRR is closely related to NPV, the net present value function. The rate of return calculated by IRR is the interest rate corresponding to a 0 (zero) net present value. The following formula demonstrates how NPV and IRR are related:

NPV(IRR(B1:B6),B1:B6) equals 3.60E-08 [Within the accuracy of the IRR calculation, the value 3.60E-08 is effectively 0 (zero).]

Example

The example may be easier to understand if you copy it to a blank worksheet.

How to copy an example

1. 
   Create a blank workbook or worksheet.
   
2. 
   Select the example in the Help topic.
   

Note  Do not select the row or column headers.

Selecting an example from Help

3. 
   Press CTRL+C.
   
4. 
   In the worksheet, select cell A1, and press CTRL+V.
   
5. 
   To switch between viewing the results and viewing the formulas that return the results, press CTRL+` (grave accent), or on the Tools menu, point to Formula Auditing, and then click Formula Auditing Mode.
   

1 2 3 4 5 6 7

A B Data Description -70,000 Initial cost of a business 12,000 Net income for the first year 15,000 Net income for the second year 18,000 Net income for the third year 21,000 Net income for the fourth year 26,000 Net income for the fifth year Formula Description (Result) =IRR(A2:A6) Investment's internal rate of return after four years (-2%) =IRR(A2:A7) Internal rate of return after five years (9%) =IRR(A2:A4,-10%) To calculate the internal rate of return after two years, you need to include a guess (-44%)