Financial Accounting

USING BUILT-IN FORMULAS IN EXCEL

Hi Everyone,

For those that have never used a built-in formula in Excel, please go through the following instructions below. It will help you understand how to use them:

1. Open up the “Excel Example” spreadsheet that I posted in the “Updates and Handouts” thread and look at the NPV Example in Example #1.

2. Put your cursor on the NPV answer cell (cell B12). Then look at the top left and note that in the formula bar it will read: =NPV(B5,B7:B11)+B6. Notice that the formula contains cell locations, not the actual numbers that are contained in the cells. ALWAYS use cell locations when using any type of Excel formula. Never input the data in the cell into the formula. The reason is that it is much more time consuming doing it this way and your cells will not become linked. When your cells are linked, you can make one small change to one cell it can change hundreds (or even thousands) of cells spontaneously. Using cell locations is much quicker than typing in =NPV(12%, 10, 20, 40, 65, -20) + -65. All you have to do is select the cells by clicking on the first cell to the far left, hold down the Shift button, and move the right arrow button to the right to select the cells when using the NPV or IRR formulas in Excel.

3. To duplicate the answer do the following: Put your cursor on the empty cell to the right of the NPV answer (cell C12). Then left click on the Insert Function button at the top left. (Note: It is the little fx button.) Then click on the drop down box for the “Or Select a Category Box”. Then click “All”. Then in the “Select a Function” box, the formulas are in alphabetical order so type in “N”. Then scroll down and highlight the NPV formula. Then click OK. This will open up the “Functions Argument” box. You will only use the Rate box and the Value1 box. For the rate box, left click on cell B5 in the spreadsheet since it contains the discount rate of 12% used in the example. Then tab down to the Value1 box and select cells B7 thru B11. The easiest way to do this is to left click on cell B7 and then push the shift key and right arrow key simultaneously to select the remaining cells which will be cells B8, B9, B10, and B11. Then click the OK button. Your answer on the spreadsheet will show $83.30 but we need to subtract the Year 0 initial investment from the calculation. So left click on cell B12 (the cell that contains the 83.30) and then at the top left, left click to place your cursor in the formula bar. Place it to the right of the last parenthesis. Then type in +B6 and push enter. Now your answer should be 18.30 which is the same as my answer in cell B12.

4. Then attempt to duplicate the answers of the other built-in formulas. The built-in formulas that are relevant to this course and that you should learn how to use include: NPV, IRR, PV, FV, NPER, RATE, PMT, PRICE, and YIELD. They are Examples #1, 2, 4 thru 8, 10 & 11.

Thanks!