Excel Finance Functions

In: Business and Management

Submitted By investmentfile
Words 2368
Pages 10
Financial Functions Using Microsoft Excel

FV | PV | RATE | NPV | IRR | PMT |

Printing Formulas |
FV
FV(rate,nper,pmt,pv,type)
Rate is the interest rate per period.
Nper is the total number of payment periods in an annuity.
Pmt is the payment made each period; it cannot change over the life of the annuity. Pmt must be entered as a negative number.
Pv is the present value, or the lump-sum amount that a series of future payments is worth right now. If pv is omitted, it is assumed to be 0 (zero). PV must be entered as a negative number.
Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0 which represents at the end of the period. If payments are due at the beginning of the period, type should be 1.

PV
PV(rate,nper,pmt,fv,type)
Rate is the interest rate per period. For example, if you obtain an automobile loan at a 10 percent annual interest rate and make monthly payments, your interest rate per month is 10%/12, or 0.83%. You would enter 10%/12, or 0.83%, or 0.0083, into the formula as the rate.
Nper is the total number of payment periods in an annuity. For example, if you get a four-year car loan and make monthly payments, your loan has 4*12 (or 48) periods. You would enter 48 into the formula for nper.
Pmt is the payment made each period and cannot change over the life of the annuity. Pmt must be entered as a negative amount.
Fv is the future value, or a cash balance you want to attain after the last payment is made. Fv must be entered as a negative amount.
Type is the number 0 or 1 and indicates when payments are due. If type is omitted, it is assumed to be 0 which represents at the end of the period. If payments are due at the beginning of the period, type should be 1.

NPV
NPV(rate,value1:value29),+cash investment
Rate is the rate of discount over the length of one period.…...

Similar Documents

Introduction of Excel Function Keys

...[데이터 입력]   |단축키 |설 명 | |Alt + Enter |셀 안에 있는 내용 입력 시 새 줄을 만들어 줍니다. | |Ctrl + Enter |셀 범위를 지정한 후 내용을 입력하고 Ctrl+Enter를 누르면 셀 범위에 동일한 내용이 | | |채워집니다. | |Shift + Enter |셀 입력을 완료하고 위의 이전 셀을 선택합니다. | |Tab |셀 입력을 완료하고 오른쪽에 있는 다음 셀을 | | |선택합니다. | |Shift + Tab |셀 입력을 완료하고 왼쪽에 있는 이전 셀을 선택합니다. | |F4 또는 Ctrl + Y |바로 전에 작업한 내용을 반복합니다. | |Ctrl + Z |바로 전에 작업한 내용을 취소합니다. | |Ctrl + D |빈 셀에 데이터를 입력한 후 아래 셀로 이동하고 Ctrl+D를 누르면 동일한 내용이 | | |채워집니다. | |Ctrl + R |빈 셀에 데이터를 입력한 후 오른쪽 셀로 이동하고 Ctrl+R을 누르면 동일한 내용이 | | |채워집니다. ...

Words: 418 - Pages: 2

Finance Function in a Global Corporation

...As companies globalize, they face new financial challenges. Here, the major challenge faced by the Global CEO is giving companies a powerful mechanism for arbitrage across national financial markets as capital markets open up within them. CFO has to balance the traditional question with new questions which arises due to the globalization. By exploiting their internal capital markets, CFO can create value in 3 functions: 1. Financing the internal capital market. 2. Managing Risk Globally, 3. Global Capital Budgeting. 1. Financing the internal capital market – A CFO can significantly reduce a groups overall tax bill by borrowing disproportionately in countries with high tax rates and lending the excess cash to operations in countries with lower rates. But the global CFO needs to be aware of downside of getting strategic about financing in these ways. Saddling the mangers of subsidiaries with debt can cloud their profit performance. 2. Managing Risk Globally – The existence of an internal capital market also broadens a firms Risk management options. Instead of managing all currency exposures through the financial market, global firm can offset natural currency exposures through their worldwide operations. Given this potential for minimizing risk, it might seem perverse that many multinationals let local subsidiaries and regions manage their risks separately. Doing so, however, can obscure the performance of local units, making it harder for headquarters to assess local......

Words: 291 - Pages: 2

Functions for Excel

...2013 Did not know there are so many functions for excel, some of the most useful tools in excel let you perform a variety of tasks automatically within a spreadsheet. They return different result depending on certain circumstance such as whether a particular condition is true or false. Excel includes a number of these functions that you can use to not only to make working with spreadsheets faster, but also to help ensure your accuracy. These functions let you code the decision-making process inside your worksheets rather than relying on the person who enters the data to make the choice and enter the result. For example, to see how a decision-making function can work, consider a situation where you charge a flat fee for shipping up to certain value of invoice and percentage of the invoice value above that. Instead of manually checking to see whether a flat fee or percentage value is appropriate and then entering the amount into a cell, you can have excel do the work for you. There are a number of Excel formulas that work in a similar way by allowing you to create one function to handle multiple situations. This is the IF function that I’m referring to, let's suppose we have a situation where the cost of shipping a product is $4.95 for any amount of product up to and including $20.00 in value and then 25 percent of the invoice price over that amount. The function looks like this: =IF (G15<=20, 4.95, G15*0.25). The syntax of the function reads: =IF (test, calculation or......

Words: 298 - Pages: 2

Excel

...ADVANCED EXCEL TRAINING Quality Management Objective  To provide the participants with an understanding of the advanced features of MS Excel Today is 7/22/2011 2 Advanced  ® EXCEL Topics Working with Data Lists Naming Cells and Ranges   Conditional Functions Lookup Functions   Pivot Tables Data Validation  Today is 7/22/2011 3 Naming Cells and Ranges Today is 7/22/2011 4 Name cells by using labels Name cells by using existing row and column labels Select the range you want to name, including the row or column labels. On In the Insert menu, point to Name, and then click Create. the Create names in box, designate the location that contains the labels by selecting the Top row, Left column, Bottom row, or Right column check box. Note A name created by using this procedure refers only to the cells that contain values and does not include the existing row and column labels. Today is 7/22/2011 5 Name a Cell or Range Using Names Box  1. Select the cell or range you want to name.  2. Click the Name box on the formula bar.  3. Type a name for the range. A range name can include uppercase or lowercase letters, numbers, and punctuation, but no spaces. Try to use a simple name that reflects the type of information in the range, such as BookTitle.  4. Press Enter. The range name will appear in the Name box whenever you select the range. Today is 7/22/2011 6 Conditional......

Words: 1579 - Pages: 7

Excel

...Function keys editing in a cell is turned off SHIFT+F2 adds or edits a cell comment. in excel ALT+F1 creates a chart of the data in the current range ALT+SHIFT insert a new worksheet Edits the active cell and positions the insertion point at the end of the cell contents. It also moves the insertion point into the Formula Bar when CTRL+F2 displays the Print Preview window When a cell reference or range is selected in a formula, F4 cycles through the various combinations of absolute and relative references. CTRL+F4 closes the selected workbook window Switches between the worksheet, Ribbon, task pane, and Zoom controls. In a worksheet that has been split (View menu, Manage This Window, Freeze Panes, Split Window command), F6 includes the split panes when switching between panes and the Ribbon area. SHIFT+F6 switches between the worksheet, Zoom controls, task pane, and Ribbon. CTRL+F6 switches to the next workbook window when more than one workbook window is open. Calculates all worksheets in all open workbooks. SHIFT+F9 calculates the active worksheet. CTRL+ALT+F9 calculates all worksheets in all open workbooks, regardless of whether they have changed since the last calculation. CTRL+ALT+SHIFT+F9 rechecks dependent formulas, and then calculates all cells in all open workbooks, including cells not marked as needing to be calculated. CTRL+F9 minimizes a workbook window to an icon. Creates a chart of the data in the current range. SHIFT+F11 inserts a new......

Words: 274 - Pages: 2

Excel

...Microsoft Excel 2010 Formulas & Functions  Table of Contents Excel 2010 Formulas & Functions ……………………………………………….….. o Formula Basics ……………………………………………….….. o Order of Operation ……………………………………………….….. Conditional Formatting ……………………………………………….….. Cell Styles ……………………………………………….….. Formulas & Functions ……………………………………………….….. Basic Formulas ……………………………………………….….. o Add the Values in Rows or Columns ……………………………………………….….. o Find the Average, Maximum, or Minimum ……………………………………………….….. Ranges & Individual Cells ……………………………………………….….. Copy a Formula ……………………………………………….….. o Using the Fill Handle ……………………………………………….….. o Using Traditional Copy & Paste ……………………………………………….….. Referencing Cells in Formulas ……………………………………………….….. o Relative References ……………………………………………….….. o Absolute References ……………………………………………….….. o Mixed References ……………………………………………….….. o References to Other Worksheets ……………………………………………….….. o Other Workbook References ……………………………………………….….. Quick Reference Guide ……………………………………………….….. o Add, Subtract, Multiple & Divide ……………………………………………….….. o Understanding Error Values ……………………………………………….….. Practical Purposes of Excel Formulas ……………………………………………….….. o Using Date & Time Formulas ……………………………………………….….. Commonly Used Functions & Formulas ……………………………………………….….. o Math & Trig Icon ……………………………………………….….. Logical Icon ……………………………………………….….. Lookup & Reference Icon ……………………………………………….….. Date & Time Icon ……………………………………………….….. AutoSum Icon ……………………………………………….…........

Words: 5629 - Pages: 23

Excel

...New Perspectives Excel 2013 Tutorial 3: SAM Project 1a Spring Software ENHANCE A TIMESHEET Project Goal M Project Name Project Goal New Perspectives Excel 2013 Tutorial 3: SAM Project 1a Spring Software ENHANCE A TIMESHEET Project Goal M Project Name Project Goal PROJECT DESCRIPTION Devon Hastings is the human resources director for Spring Software, a software development company in Cambridge, Massachusetts. He has created a workbook for employees to use in recording the hours they work. He would like your help finishing the timesheet. GETTING STARTED * Download the following file from the SAM website: * NP_Excel2013_T3_P1a_FirstLastName_1.xlsx * Open the file you just downloaded and save it with the name: * NP_Excel2013_T3_P1a_FirstLastName_2.xlsx * Hint: If you do not see the .xlsx file extension in the Save file dialog box, do not type it. Excel will add the file extension for you automatically. * With the file NP_Excel2013_T3_P1a_FirstLastName_2.xlsx still open, ensure that your first and last name is displayed in cell B6 of the Documentation sheet. If cell B6 does not display your name, delete the file and download a new copy from the SAM website. PROJECT STEPS 1. On the Employee List worksheet, select cells A1:B1 and then use the Format Painter copy the format only to the range A2:B9. 2. In cell B1 enter bs001, in cell B2 enter cg001, in cell B3 type dh001, and then use Flash......

Words: 790 - Pages: 4

Excel Financial Functions

...Microsoft Excel for financial calculations? Microsoft Office Excel is a great computer program that is widely used throughout the financial industry. Excel is an invaluable tool in Finance and Accounting. Of course professionals also use special financial calculators. But a financial calculator can be expensive for students. It is not intuitive to use and unless they become investment bankers or realtors, most students will never use it after finishing the financial course. Fortunately, it is very easy to create a financial calculator for free if you have Excel on your computer. In fact, an Excel calculator can do much more than a dedicated financial calculator. Another advantage is the fact that if you learned how to use financial functions of Excel you will be able also to use a special financial calculator should the need arise. Below is a short introduction of how to start using MS Excel for financial calculations. Inserting Functions Excel comes with a wide array of functions that can easily be inserted into a spreadsheet. Adding a function is as easy as clicking on the "Insert Function" button on the top tool bar. Insert Function Button If you don't see the "Insert Function" button on your tool bar, you can easily add it by clicking: Tools > Customize > Commands > Find and click on Insert in the left-hand scroll-down menu. Then, find "Insert Function" in the right-hand scroll-down menu and click and drag it to your tool bar. Clicking on the "Insert Function"......

Words: 488 - Pages: 2

Learnings from Article Review – Finance Function in a Global Corporation

...Learnings from Article Review – Finance Function in a Global Corporation As companies globalize, they face new financial challenges. Here, the major challenge faced by the Global CEO is giving companies a powerful mechanism for arbitrage across national financial markets as capital markets open up within them. CFO has to balance the traditional question with new questions which arises due to the globalization. By exploiting their internal capital markets, CFO can create value in 3 functions: 1. Financing the internal capital market. 2. Managing Risk Globally, 3. Global Capital Budgeting. 1. Financing the internal capital market – A CFO can significantly reduce a groups overall tax bill by borrowing disproportionately in countries with high tax rates and lending the excess cash to operations in countries with lower rates. But the global CFO needs to be aware of downside of getting strategic about financing in these ways. Saddling the mangers of subsidiaries with debt can cloud their profit performance. 2. Managing Risk Globally – The existence of an internal capital market also broadens a firms Risk management options. Instead of managing all currency exposures through the financial market, global firm can offset natural currency exposures through their worldwide operations. Given this potential for minimizing risk, it might seem perverse that many multinationals let local subsidiaries and regions manage their risks separately. Doing so, however, can obscure the......

Words: 395 - Pages: 2

Excel Functions

...Excel Function-INFORMATION  Cell – The cell function returns information about the formatting, location, or content of a cell.For ex if you want to verify that a cell contains a numerical value instead of text before you perform a calculation on it, you can use the following formula =IF (Cell (“type”, A1) =”v”, A1*2, 0) This formula calculate A1*2 only if the cell A1 contains numerical value and returns to zero if it is text or it is blank. Syntax: Cell (info_type, (reference)) Following is the list of possible info_type values: “Address”-Reference of the first cell in the reference as text. “Col”-Column number of the cell in reference. “Color”-The value 1 if the cell is formatted in color for negative values; otherwise it returns to zero. “Content”-Value of the upper left cell in reference; not a formula “Filename”-Gives the path of the file in reference. Returns empty text ("") if the worksheet that contains reference has not yet been saved “Format”- Text value corresponding to the number format of the cell. The text values for the various formats are shown in the following table. Returns "-" at the end of the text value if the cell is formatted in color for negative values. Returns "()" at the end of the text value if the cell is formatted with parentheses for positive or all values. “Row”-Row number of the cell in reference “Type”-Text value correspondence to the type of data in the cell. Returns “b” for blank if the cell is empty,”l”for label if the cell......

Words: 287 - Pages: 2

Excel

...Applied Statistics Microsoft Excel Decomposition may reduce appeal emphasis on applications. Flow charts help make the analysis decision by identifying technique, computing statistics, and interpreting results. Associated data set accompanies the set purports to build a excel spreadsheet that discovers formula, illustrates the parameters, observes dynamic graphs and simulations that recodes, edits, validate, charts, and automatically links equations, data, and formulas to recalculate the data analysis. Excel recognizes statistical capability conjunction that carries out complex algorithms that implement correlated data columns. Excel quickly analyzes and interprets information filtered into a spreadsheet so the process is much easier then working out these formulas by hand. Excel spreadsheets create charts, graphs, and filters data all into one. Excel uses all of the basic mathematical operations such as addition, subtraction, division, and multiplication. Fractions and decimals are just as common for interpretation data into a excel spreadsheet. An advantage about excel is you can place any formula into the spreadsheet whether in ascending or descending order and using the same formula, it will automatically compute the answer for the next equation. Another advantage is if information is inaccurate, you can go back and check your work with just a click, unlike using y calculations or by hand you have to erase so much and start all over again, which could be time......

Words: 377 - Pages: 2

Learnings from Article Review – Finance Function in a Global Corporation

...Learnings From Article Review – Finance Function In a Global Corporation Learnings from Article Review – Finance Function in a Global Corporation As companies globalize, they face new financial challenges. Here, the major challenge faced by the Global CEO is giving companies a powerful mechanism for arbitrage across national financial markets as capital markets open up within them. CFO has to balance the traditional question with new questions which arises due to the globalization. By exploiting their internal capital markets, CFO can create value in 3 functions: 1. Financing the internal capital market. 2. Managing Risk Globally, 3. Global Capital Budgeting. 1. Financing the internal capital market – A CFO can significantly reduce a groups overall tax bill by borrowing disproportionately in countries with high tax rates and lending the excess cash to operations in countries with lower rates. But the global CFO needs to be aware of downside of getting strategic about financing in these ways. Saddling the mangers of subsidiaries with debt can cloud their profit performance. 2. Managing Risk Globally – The existence of an internal capital market also broadens a firms Risk management options. Instead of managing all currency exposures through the financial market, global firm can offset natural currency exposures through their worldwide operations. Given this potential for minimizing risk, it might seem perverse that many multinationals let local subsidiaries and......

Words: 257 - Pages: 2

The Finance Function

...The Finance Function in a Global Corporation Historically, the finance functions in large U.S. and European firms have focused on cost control, operating budgets, and internal auditing. But as corporations go global, a world of finance opens up within them, presenting new opportunities and challenges for CFOs. Rather than simply make aggregate capital-structure and dividend decisions, for example, they also have to wrestle with the capital structure and profit repatriation policies of their companies’ subsidiaries. Capital budgeting decisions and valuation must reflect not only divisional differences but also the complications introduced by currency, tax, and country risks. Incentive systems need to measure and reward managers operating in various economic and financial settings. The Globally Competent Finance Function The existence of what amounts to internal markets for capital gives global corporations a powerful mechanism for arbitrage across national financial markets. But in managing their internal markets to create a competitive advantage, finance executives must delicately balance the financial opportunities they offer with the strategic opportunities and challenges presented by operating in multiple institutional environments, each of which has it own legal regime and political risks. There is also a critical managerial component: What looks like savvy financial management can ruin individual and organizational motivation. As we’ll see in the following pages,......

Words: 947 - Pages: 4

Excel

...Difference (cell D10 of the Summary sheet) should be $88,188.00 On the Employee Data sheet: a. Set a Data Validation rule (p. EX 438) on the Job Status column for a drop down List of choices CN, FT or PT. You will need to key these in the Source box. Separate each choice by a comma (,). a) Set the Input Message to say Please select CN, FT or PT from the drop down list. b) Set the Error Alert to Stop Style with the message You did not choose a Status from the list. Use the Employee Data sheet: a) In cell I2, create an IF(And function to determine if an employee is eligible for an award. To be eligible, employees must be FT and be employed at least one year. If they are eligible, display “Yes” in column I, if not display “No”. (p. EX 370, use in conjunction with IF ) b) Copy the formula down to all employees (fill in Column I). Again on the Employee Data sheet a) In cell J2, create an IF function with a nested Vlookup (p. EX 387)as the value_if_true to determine the amount of Award money earned. Use column I (Eligible for Award) as the test for “Yes”, then use a Vlookup (with the array in cells M1:N6) to display the appropriate award amount earned, with 0 being inserted for anyone not eligible (No in column I). b) Copy this formula for all employees in column J. Use the Award Analysis sheet: a) In cell C4, enter a CountIF formula (p. EX 405) to count the number of employees eligible for each award amount (Hint use column J). ......

Words: 1218 - Pages: 5

Desai Finance Function in a Global

...ORGANIZATION & CULTURE FINANCE & ACCOUNTING MARKETING MANAGING TECHNOLOGY MARKETING STRATEGY & COMPETITION HUMAN RESOURCES MARKETING ORGANIZATION & CULTURE MANAGING TECHNOLOGY FINANCE & ACCOUNTING ORGANIZATION & CULTURE MARKETING STRATEGY & COMPETITION HUMAN RESOURCES MANAGING TECHNOLOGY FINANCE & ACCOUNTING ORGANIZATION & CULTURE MARKETING STRATEGY & COMPETITION HUMAN RESOURCES MANAGING TECHNOLOGY MARKETING STRATEGY & Honing Your HUMAN RESOURCES ORGANIZATION & CULTURE FINANCE & ACCOUNTING HUMAN RESOURCES COMPETITIONCompetitive Edge Finance Function in a Global Corporation The H 108 Harvard Business Review | by Mihir A. Desai HISTORICALLY, the finance functions in large U.S. and European firms have focused on cost control, operating budgets, and internal auditing. But as corporations go global, a world of finance opens up within them, presenting new opportunities and challenges for CFOs. Rather than simply make aggregate capital-structure and dividend decisions, for example, they also have to wrestle with the capital structure and profit repatriation policies of their companies’ subsidiaries. Capital budgeting decisions and valuation must reflect not only divisional differences but also the complications introduced by currency, tax, and country risks. Incentive systems need to measure and reward managers operating in various economic and financial settings. The existence of what amounts to internal markets for capital gives global corporations a powerful mechanism...

Words: 2206 - Pages: 9