Microsoft Excel As A Financial Callculator Part I

Submitted By Justshllez1
Words: 6517
Pages: 27
Open Document
Microsoft Excel as a Financial Calculator Part I

Excel (and other spreadsheet programs) is the greatest financial calculator ever made. There is more of a learning curve than a regular financial calculator, but it is much more powerful. This tutorial will demonstrate how to use Excel's financial functions to handle basic time value of money problems using the same examples as in the calculator tutorials. I will keep the examples rather elementary, but if you already understand the basics of using Excel, this tutorial will help you to understand the financial functions.
Analogy to Calculator Financial Keys
All financial calculators have five financial keys, and Excel's basic time value functions are exactly analogous. The table below shows the equivalency between the calculator keys and Excel functions:
Purpose
Calculator Key
Excel Function
Solve for Number of Periods
N
NPer(rate, pmt, pv, fv, type)
Solve for periodic interest rate
I/Yr
Rate(nper,pmt,pv,fv,type,guess)
Solve for present value
PV
PV(rate,nper,pmt,fv,type)
Solve for annuity payment
PMT
PMT(rate,nper,pv,fv,type)
Solve for future value
FV
FV(rate,nper,pmt,pv,type)
Just as you have to supply at least three of the variables to solve a TVM problem in a financial calculator, you also have to supply at least three of the arguments to each Excel function. Note that in the table, the bold function arguments are required while those in italics are optional.
You don't need to memorize the order of the function arguments. You can use the Insert Function dialog box, which will prompt your for the arguments by name. Or, if you prefer to type the function directly, Excel will display a Smart Tag that shows the order of the arguments as you type.
Example 1 - Future Value of Lump Sums
We'll begin with a very simple problem that will provide you with most of the skills to perform financial math using Microsoft Excel:
Suppose that you have $100 to invest for a period of 5 years at an interest rate of 10% per year. How much will you have accumulated at the end of this time period?
In this problem, the $100 is the present value (PV), NPer is 5, and Rate is 10%. Open a new workbook and enter the data as shown below, but leave B5 blank for now.

To find the future value of this lump sum investment we will use the FV function, which is defined as:
FV(rate,nper,pmt,pv,type)
Select cell B5 and then type: =FV(B3,B2,0,-B1) and then press Enter. The answer that you get should be 161.05.
A Couple of Notes
1. Every time value of money problem has either 4 or 5 variables (corresponding to the 5 basic financial variables). Of these, you will always be given 3 or 4 and asked to solve for the other. In this case, we have a 4-variable problem and were given 3 of them (Nper, Rate, and PV) and had to solve for the 4th (FV). Be sure that any variables not in the problem are set to 0, otherwise they will be included in the calculation. In this case, we did not have an annuity payment (PMT), so the third argument in the FV function was set to 0.
2. Note that we left out the optional Type argument. In all of these functions, the Type argument tells Excel when the first cash flow occurs (0 if at the end of the period, 1 if at the beginning). This argument is identical to setting your financial calculator to End Mode or Begin Mode, and only affects the answer when there is an annuity payment. When solving lump sum problems such as this, the argument has no effect. If you had typed =FV(B3,B2,0,-B1,1) you would have gotten the same answer.
3. Note that, unlike most financial calculators, there is no argument to set the compounding frequency. This is actually a good thing, in my opinion, because those settings on financial calculators cause all kinds of trouble when people forget to set them correctly. In Excel functions, you must set NPer to be the total number of periods, Rate to be the interest rate per period, and PMT to be the annuity payment per period. So, if this
Show More

Related Documents: Microsoft Excel As A Financial Callculator Part I

Capital Budgeting Essay

in. We have calculated the projected income statement and projected cash flow for the next five years. After evaluating that information we were able to view the net present value and internal rate of return. Based off the findings of our research, we will be able to make a decision on the appropriate investment. The net present value and internal rate of return are capital budgeting methods that can be used to calculate the potential capital of an investment. It is used as a tool to determine if…

Words 923 - Pages 4

Calculate The Company's Weighted Average Cost Of Capital

market price = $309.29 Face value = $300.00 Annual coupon rate = 13.5% ( paid half yearly) Further flotation cost ( if any) = $1.50 (300* 13.5%) = 40.5/2 = 20.25 Present value of annuity = $20.25 Pmt = $20.25 MP= Pmt * 1 – ( 1)/ (1+i)^n i 20.25 * 1 – 1/ ( 1+ 0.00675)^ 14 0.0675 = $ 179.78 Discount back the face value to 0 = FV….. ( 1+i)^n…

Words 1785 - Pages 8

Pressco Inc Essay

be sold, which will have an estimated market value of $150,000 (Table A). This cash inflow will have a positive effect on net cash flow for 1986. v. In 1996 the new equipment will be sold, which will have an estimated salvage value at the end of its useful life of $250,000 (Table B). This cash inflow will also have a positive effect on net cash flow for 1996. Calculation of NPV based on cash inflows and outflows: In order to calculate the NPV for the project the sum of the net cash flows…

Words 1457 - Pages 6

Case Study 1 Essay examples

statistics and projected balances. Ratios, present values, and rates of return should all be considered before making large financial decisions. When presented with multiple investment opportunities comparison of calculations based on projected cash flows enables an investor to make well educated decisions based on predicted financial returns. The Net Present Value (NPV) is a method of analyzing and appraising the earning potential of investments. The NPV is the difference between cash flow coming…

Words 665 - Pages 3

backup study Essay

To move cash flow forward in time: Future Compounded Value of a Cash Flow FV= To move cash flow backwards in time: Present Discounted Value of a Cash Flow PV = Because we need to compare values at the same point in time, if there are different streams: Present Value of Cash Flow Stream: PV = → + + …+ Future Value of a Cash Flow Stream with a Present Value of PV: (See other iterations) Perpetuities ­ when a constant cash flow will occur at regular intervals indefinitely (no time…

Words 554 - Pages 3

My Doc Essay example

The net present value decision rule The net present value (NPV) method of capital investment appraisal is based on the view that a project will be regarded as successful if the present value of all expected cash inflows is greater than, or equal to, the capital invested at the outset. It is called net present value because, in calculation, the capital invested is deducted from the present value of the future cash flows. (Use of the word ‘net’ always means that one item is being deducted from another…

Words 924 - Pages 4

a-rod Finace for marketing decisions Essay examples

for marketing decisions for A-Rod How the concept of NPV is used to solve Quantitative answers The net present value is one of the capital appraising techniques used to determine the viability of a certain investments. It is used by existing or potential investors to gauge the suitability of project by comparing the discounted projects cash inflows and outflows. For a single project, the investor only accepts if the project has a positive NPV. For more than one project that is mutually exclusive…

Words 1150 - Pages 5

The Marginal Efficiency Of Capital Essay

theories of the business cycle. Fuller (2013) compares the Keynesian Marginal Efficiency of Capital approach to the Austrian Net Present Value approach, claiming that the two give different rankings of investment projects. This comment provides examples to show that this is only true if factor prices are held constant. If factor prices reflect the discounted present value of the project, then the different rankings between the approaches vanishes. This result further highlights a fundamental difference…

Words 1563 - Pages 7

Capital Budgeting Essay

maturity value or dividends in the case of bonds and stocks, operating cash flows in the case of capital projects. Second is to assess the riskiness of the cash flows. Next, determine…

Words 1764 - Pages 8

Essay about Net Present Value

Tutorial 5 (Chapter 6-The application of project evaluation methods) 4. Define the term ‘mutually exclusive projects’ and provide a simple example. Outline and justify the basic net present value rule applicable to them. How should this rule be modified when such projects have unequal lives? Two projects are said to be mutually exclusive if the selection of one project will automatically preclude the other project from being selected. This can be generalised to an n-project case. If a company…

Words 1190 - Pages 5