Boston College



Excel Appendix E2

Guide to Useful Built-In Functions in Excel

Overview and Objectives

This Appendix provides a guide to some of the most useful built-in financial functions in Excel and also to some other nonfinancial functions and data analysis tools that may be useful in solving a variety of problems in finance. After reading this Appendix, you should be able to:

• Solve present value and future value problems involving single future cash flows, level annuities, and cash flow streams, all of whose payments are identical except the final payment, using the FV, PV, NPER, RATE and PMT functions

• Solve present value problems involving irregular cash flow streams with finite lives using the NPV and IRR functions

• Convert an Annual Percentage Rate (APR) into an Effective Annual Rate (EAR) and vice versa, using the EFFECT and NOMINAL functions

• For mortgages and other self-amortizing loans, find the portions of any payment that represent interest and principal repayment, the cumulative amount of interest and principal paid on the loan at any point and the remaining loan balance at any point using the IPMT, PPT, CUMIPMT and CUMPRINC functions

• Find the square root of a number using the SQRT function

• Find log (x) and ex using the LN and EXP functions

• Find a weighted average, given a set of numbers and a set of weights, using the SUMPRODUCT function

• Find the mean and standard deviation of a sample of data from a probability distribution using the AVERAGE and STDEV functions

• Estimate the coefficients in a simple, linear regression using Excel’s regression capability

• Find values for the cumulative normal distribution function using the NORMSDIST function

A. Single Payments and Annuities: FV, PV, NPER, RATE and PMT

Five of the most commonly-used financial functions are FV (future value), PV (present value), NPER (number of periods), RATE (interest or discount rate) and PMT (annuity payment). They can be used to solve a variety of problems involving either a single future payment or multiple future payments, provided all of the payments are the same (annuities), with the possible exception of the final payment. Each of the functions depends on a series of inputs, or arguments, and we can solve problems by calling up the functions and entering the values of the arguments. We can write each of the functions in terms of its arguments as:

• FV(Rate, Nper, Pmt, Pv, Type)

• PV(Rate, Nper, Pmt, Fv, Type)

• NPER (Rate, Pmt, Pv, Fv, Type)

• RATE (Nper, Pmt, Pv, Fv, Type, Guess)

• PMT(Rate, Nper, Pv, Fv, Type)

The five functions allow us to solve for any one of five variables, given values for the other four variables.

A1. A Single Future Payment

When there is only a single future payment, instead of a series of constant payments, we can ignore PMT, so we can solve for any one variable in terms of the other three. Equation (2.1) in Chapter 2 gives us the basic relationship among the four variables, so we can rewrite Equation (2.1) in terms of the function names:

FV = PV(1+RATE)NPER (E2.1)

That is, if we invest an amount PV now at the interest rate per period RATE, then after NPER periods, we will have FV. For any values of PV, RATE and NPER, we can call up the FV function in Excel and use it to solve for future value, given values of the other three arguments. When the function dialog box comes up and asks you to enter values for the arguments, you can simply skip over Pmt. The “Type” argument takes on a value of either 0 or 1, depending on whether the future payment occurs at the end or the beginning of the period. For example, if we want to know how much money we will have exactly 5 years from now, we can enter “5” for NPER and enter “0” for Type (or simply the leave the Type input blank). FV will then calculate the amount you will have at the end of the fifth year, starting now. On the other hand, if you want to know how much money you will have at the beginning of the fifth year from now, you can enter “5” for Nper and “1” for Type. The function will then calculate:

FV = PV(1+RATE) (NPER-1) (E2.2)

In this case, (NPER – 1) = (5 – 1) = 4, so we would get the same answer if we entered “4” for Nper and “0” (or blank) for Type. The beginning date of any period always coincides with the ending date of the previous period.

As we did in Equations (2.2), (2.3) and (2.6) in Chapter 2, we can solve Equation (E2.1) for any of the other three variables in terms of the remaining variables. This is what the Excel functions PV, NPER and RATE do[1]. As noted in Appendix E1, if you enter a positive number for the argument Pv in the FV function or a positive number for the argument Fv in the PV function, the functions will give you a negative number for an answer. Excel treats inflows as positive and outflows as negative, and it adheres to the convention that if you receive money today (an inflow), you must pay money in the future (an outflow), while if you receive money in the future (an inflow), you must pay money (an outflow) today. It is up to you whether you want to enter the arguments Pv or Fv as positive or negative numbers. Be aware, however, that when you use either the NPER or RATE functions, the values entered for the Pv and Fv arguments must have opposite signs.

You can try all four of the functions on the problems that follow. Solutions appear in the Excel workbook “Appendix E2 workbook” (see the tab for “Section A1 Probs”).

Section A1 Sample Problems

1. What is the future value of $1000 invested now at 5% per year for 15 years?

2. What amount must be invested now at 5% per year to give $3000 in 15 years?

3. How many periods will it take to have $3000 if we invest $1000 now at 5%?

4. What interest rate must I earn for $1000 invested now to grow to $3000 in 15 years?

5. What is the present value of $1000 received 10 years from now at 5%?

6. What amount must I be paid 10 years from now at 5% to have a present value of $1000?

7. How many periods in the future will I receive $2000 at 5% for it to be worth $1000 now?

8. What discount rate sets the present value of $2000 received 10 years from now equal to $1000?

A2. A Series of Future Payments, All Identical Except (Possibly) the Last

We can also use the same four functions, plus the PMT function, to solve problems in which there are multiple future payments, all of them identical, with the possible exception of the final payment. In cases in which the final payment is no different from the others, the algebraic equations governing present value and future value – Equations (3.3) and (3.7) from Chapter 3, rewritten in terms of the function names – are:

[pic] (E2.3)

[pic] (E2.4)

The same PV and FV functions that we used above can now be used to solve Equations (E2.3) and (E2.4) once we enter a value for the argument “Pmt.” Note that we could also solve either (E2.3) or (E2.4) for PMT, and the PMT function in Excel calculates this solution for us. If we enter values for the arguments Pv, Nper and Rate, the PMT function solves Equation (E2.3) for the payment per period, while if we enter values for the arguments Fv, Nper and Rate, the PMT function solves Equation (E2.4) for the payment per period. As before, entering “1” for the Type argument shifts all payments to the beginning of each period, while leaving Type blank (or entering a “0”) indicates that all payments are made at the end of the period, as embodied in Equations (E2.3) and (E2.4).

As above, when using the NPER and RATE functions, the values entered for the Pv and Fv arguments must have opposite signs. Note also that, while we can solve either (E2.3) or (E2.4) for NPER, albeit with somewhat more difficulty than we solved (E2.1), we cannot necessarily solve either equation for RATE. To find RATE, Excel uses an iterative, trial and error solution process. This is why the argument Guess appears as part of the RATE function. If you wish to provide a possible value of the interest or discount rate from which to start the iterative process, you may enter a value for Guess. For the kinds of problems you will encounter in this course, RATE will quickly find the solution, and there is no need to provide a value for Guess.

Finally, if there is a final payment in the series of future payments that differs from all of the other payments, you can enter this different amount as the Fv argument in the PV, NPER or RATE functions. For example, if we have a bond that makes a series of coupon payments plus a principal payment at maturity, then the present value of the bond is given by Equation (4.4) in Chapter 4, or rewriting the equation in terms of the function names:

[pic] (E2.5)

If we enter a value for the Fv argument, the PV, NPER and RATE functions will solve (E2.5), by trial and error if necessary. Note that if you have a bond that makes semiannual coupon payments, the value you get from the RATE function represents a semiannual rate, or yield. If you wish to express the rate in annualized terms, as is customary in the market, you will need to multiply the result from the rate function by two.

Section A1 Sample Problems

9. What is the present value of a 10-year annuity at 5% with annual payments of $100, beginning in one year?

10. What is the present value of a 10-year annuity at 5% with annual payments of $100, beginning immediately?

11. What is the future value, 10 years from now, of a 10-year annuity at 5% with annual payments of $100 (starting in one year)?

12. How many periods are there in an annuity at 5% with annual payments of $100 and a present value of $1500?

13. What is the value of a bond with 10 years to maturity that has a face value of $100, a coupon rate of 6%, payable semiannually, and a yield of 7%?

14. If the bond in #13 above has a current price of $110, what is its annualized yield?

B. Uneven, Multiple Cash Flows: NPV and IRR

When we face a series of cash flows with different amounts each period, the PV, FV, NPER, RATE and PMT functions no longer work. Rather, we have to use the NPV and IRR functions:

• NPV (Rate, Value1, Value2, …)

• IRR (Values, Guess)

We know from Equation (2.9) in Chapter 2 that the net present value (NPV) of an investment, with an initial investment outlay I and future cash flows Ct received in Years 1 through T, is calculated as:

[pic] (E2.6)

Excel’s NPV function will make this calculation with one qualification. We can enter the value of the discount rate as the Rate argument in the function, and the values of the future cash flows cash flows C1, … CT as the Value1, …, ValueT arguments, respectively. In fact, we don’t have to enter the future cash flows one by one. Rather, we can select a whole series of cash flows whose values have been entered sequentially in a spreadsheet row or column. For example, if future cash flow values for Years 1 through 5 have been entered across Row 2 in Cells A2, B2, C2, D2 and E2, we can select the five cells, and they will show up in the function as “A2:E2.” However, the NPV function cannot handle a Year 0 cash flow. If there is a Year 0 cash flow, which often corresponds to the initial investment outlay, it must be added separately to the value produced by Excel’s NPV function in order to give the true NPV, as embodied in Equation (E2.6).

The IRR function finds the value of r in (E2.6) that sets NPV, as defined by (E2.6) equal to zero. Since the IRR cannot usually be found algebraically, the IRR function uses the same iterative trial-and-error procedure that the RATE function does. You can enter a guess as to the correct IRR from which to start the iterations, but this is usually not necessary[2]. In the absence of a guess, you need enter only the series of cash flow values in the IRR function, and you can select these as a group instead of entering them one by one. It is important to be aware, though, that unlike the NPV function, the IRR function does include any Year 0 cash flow.

Section B Sample Problems

15. What is the NPV of the following set of cash flows at a discount rate of 5%?

|C0 |C1 |C2 |C3 |C4 |C5 |

|-10000 |2000 |3000 |6000 |4000 |2500 |

16. What is the IRR of the cash flow stream in Problem 15 above?

C. APR and EAR: the NOMINAL and EFFECT Functions

In Chapter 3, the Annual Percentage Rate (APR) for a loan contract that entails m compounding periods per year is defined as the annualized (i.e., multiplied by m) interest rate per compounding period used in making interest calculations in a loan contract. The Effective Annual Rate (EAR) is defined as the annually compounded rate that results in the same amount of money after one year as one plus APR/m, compounded m times. The relationship between APR and EAR is given in Equation (3.9) in Chapter 3, repeated here as (E2.7:

[pic] (E2.7)

While Equation (E2.7) is not difficult to work with, Excel does have functions, NOMINAL and EFFECT, which allow us to move back and forth between an APR (called the “nominal” rate in Excel) and the EAR (called “Effect” in Excel):

• EFFECT (nominal rate, npery)

• NOMINAL (effect rate, npery)

In both functions, the argument npery is the number of compounding periods in a year.

Section C Sample Problems

17. A loan with an APR of 8% calls for monthly payments? What is the EAR?

18. A loan with an EAR of 8% makes quarterly payments. What is the APR?

D. Mortgages and other Self-Amortizing Loans: the IPMT, PPMT, CUMIPMT and CUMPRINC Functions

Some additional financial functions in Excel are useful for mortgage problems and other problems involving self-amortizing loans (loans that make constant payments, sufficient to pay back all principal plus interest due over the life of the loan). These are the IPMT, PPMT, CUMIPMT and CUMPRINC functions. They allow us to find, for any given payment during the life of the loan: how much of that payment goes to paying interest, how much goes to paying down principal, how much total interest has been paid to that point and how much total principal has been paid to that point. The functions with their arguments are:

• IPMT (Rate, Per, Nper, Pv, Fv, Type)

• PPMT (Rate, Per, Nper, Pv, Fv, Type)

• CUMIPMT (Rate, Nper, Pv,Start_period, End_period, Type)

• CUMPRINC (Rate, Nper, Pv,Start_period, End_period, Type)

In these functions, the arguments Rate, Nper, Pv, Fv and Type are defined as above. The present value of the payments, Pv, as of the date the loan is taken out is also the initial loan balance. In the IPMT and PPMT functions, Per represents the number of the loan payment period for which we want to find the interest portion or the principal portion. In CUMIPMT and CUMPRINC, the argument Start_period is the first period (= 1 if we wish to start from the beginning of the mortgage) of the total time span over which we wish to find cumulative interest or principal paid, while End_period is the last such period.

In the IPMT and PPMT functions, we can ignore Type if payments are made at the end of each period. In PPMT, we can also ignore Fv unless the loan is not fully-amortizing (that is, an additional principal payment, Fv, is due at the loan’s maturity date.

Be aware, however, that CUMIPMT and CUMPRINC have two quirks. First, the initial loan balance, Pv, must be entered as a positive number. Second, the Type argument in these functions cannot be ignored. Even if all loan payments occur at the end of the period, you must enter a “0” for Type, rather than leaving it blank.

You may also sometimes wish to know, the remaining principal on a loan at any given future point. You can do this by calculating the cumulative principal payments made from Period 1 to the period in question, using the CUMPRINC function, and then subtracting the result from the initial loan balance, Pv. However, recalling that the remaining principal at any point on a self-amortizing loan is equal to the present value at that point of the remaining loan payments (see Section 3.2.2 in Chapter 3), it may be at least as easy to calculate the remaining principal at any point as the present value of the loan payments remaining at that point.

Section D Sample Problems

Note: all problems in this section refer to the mortgage whose terms are outlined in Problem 19:

19. What is the monthly payment on a 30-year $400,000 home mortgage if the APR is 6%?

20. How much of the 100th monthly payment goes toward interest?

21. How much of the 100th monthly payment goes toward principal?

22. After 100 payments have been made, how much cumulative total interest has been paid?

23. After 100 payments have been made, how much cumulative total principal has been paid?

24. What is the remaining balance on the mortgage after the 100th payment has been made?

E. Some Useful Mathematical Functions: SUM, SUMPRODUCT, SQRT, LN and EXP

There are also some nonfinancial built-in functions in Excel that you may find useful in solving finance problems. Under the “Math & Trig” category in Excel’s Function Library, a few that you may have occasion to use are:

• SUM (number 1, number 2, …)

• SUMPRODUCT (array1, array2, …)

• SQRT (number)

• LN (number)

• EXP (number)

You are probably already familiar with SUM, which produces the sum of a column or row of numbers. You do not need to enter the numbers one by one, but can simply select them from the column or row where they appear in the spreadsheet. One possible use for this function would be if you needed to calculate a payback period for an investment (see Section 6.1.2 in Chapter 6), or the length of time it takes for the future cash inflows to cover the initial investment outlay. Sample Problem 25 below provides an example. To make the calculation, you can keep a running sum of the cash flows, year by year, and the payback period is the number of years it takes for the running sum to equal or exceed the initial investment outlay.

SUMPRODUCT multiplies the corresponding entries in two arrays (rows or columns) of numbers and adds them up. An application is calculating a weighted average, where one array is a set of weights, and the second array is the set of numbers we are averaging. The expected return on a portfolio of securities is an example of such a weighted average (see Section 7.1.3 in Chapter 7). As with the SUM function, we can select each of the arrays, rather than entering their values one by one.

The SQRT function calculates the square root of a number. You can obtain the same result by raising that number to the power 0.5. Thus:

[pic] (E2.8)

An application of SQRT is finding the standard deviation of a security’s returns by taking the square root of its variance of returns (See Section 7.1.1 in Chapter 7).

The LN function finds the natural logarithm of a number. An application occurs when we wish to know how many years it will take for an initial investment to double in value if the interest rate is 7% (see Section 2.1.3 in Chapter 2). We wish to find T such that:

[pic] (E2.9)

We can solve for T if we take the natural logarithm of both sides of Equation (E2.9):

[pic] (E2.10)

Finally, the function EXP function, which stands for the exponential function, finds ex for any value x, where e is the base of the natural logarithms. An application is continuously compounded interest. We know from Section 3.2 in Chapter 3 that the future value of $1, T years from now, at a continuously compounded rate APR of r is erT, which we can calculate as EXP(r*T).

Section E Sample Problems

25. What is the payback period for the following investment?

Year 0 1 2 3 4 5

Ct ($mil) -10 1 3 5 3 2

26. What is the expected return on a portfolio of securities, with the following individual security expected returns and portfolio weights:

Security A B C D E

Security Expected Return 6% 10% 8% 12% 20%

Portfolio weight 25% 10% 15% 20% 30%

27. If the variance of a security’s returns is .065, what is the security’s return standard deviation?

28. How many years does it take to double your initial investment at an annually compounded interest rate of 7% per year?

29. What is the value, $10 years from now, of $1 invested today at a continuously compounded interest rate (APR) of 7% per year?

F. Statistical Functions: AVERAGE, STDEV and NORMSDIST and the Histogram and Regression Tools

You may also find a few of Excel’s Statistical functions and statistical tools useful in finance. The functions themselves can be found in the Function Library (“Formulas” tab) under “More Functions.” The functions used in this book are:

• AVERAGE (number 1, number 2, …)

• STDEV(number 1, number 2, …)

• NORMSDIST(z)

In addition, in the “Data Analysis” menu, under the Data tab in Excel, we will have occasion to use the histogram and regression tools.

AVERAGE calculates a simple average of a set of numbers, which can be selected as an array going down a column or across a row. If you have a sample of observations from a larger probability distribution, AVERAGE is useful for estimating the mean (or expected value) of the distribution from the average of the sample values. You can use AVERAGE to estimate expected returns on securities or portfolios using a sample of past realized return observations.

STDEV calculates the sample standard deviation from a set of numbers. Again, you can select the entire set of numbers as an array going down a column or across a row. For example, if you have a sample of realized returns, rt, for a set of time periods, t, for a security or portfolio, you can estimate the return standard deviation from the sample. If you have a total of n observed returns, with an average value Ave, STDEV uses the formula:

[pic] (E2.11)

Note that the sum of the squared deviations of the sample observations from their average is divided by (n-1), rather than by n. This is because the standard deviation is estimated with (n-1) “degrees of freedom.” We have n total observations, but since we are dealing with a sample from the true probability distribution and not the entire population, we use up one degree of freedom in estimating the true population mean with the sample average[3]. AVERAGE and STDEV are used to estimate expected returns and return standard deviations in Section 7.1.2 of Chapter 7.

The NORMSDIST function returns the height of the standard normal cumulative distribution function for a value z that represents a value from the probability distribution that lies z standard deviations above the mean (or below the mean if z takes on a negative value). The standard normal distribution has a mean value of zero and standard deviation of 1.0. For example, you can use the function to calculate NORMSDIST(0) = 0.5. Here, you are calculating the height of the cumulative distribution for the mean value itself (i.e., 0 standard deviations away from the mean), and the answer tells us that half of the distribution lies below the mean. A further implication is that the other half of the distribution lies above the mean, which we expect from the fact that the normal distribution is symmetrical around its mean value. We use the NORMSDIST function in Chapter 12 (“Options”) to calculate option values using the Black-Scholes option pricing model.

The histogram tool under “Data Analysis” in Excel is useful when you want to group a set of numbers in designated “bins” or value ranges. Suppose, for example, that you are trying to determine how often one of your division managers comes up with ideas for profitable projects. You have the calculated net present values (NPVs) for the last 20 project proposals the manager has submitted, and you wish to group them according to the number of projects whose NPVs are < -40 ($mil), between -40 and -30, between -30 and -20, between -20 and -10, between -10 and zero, between zero and + 10, between +10 and +20, between +20 and +30, between +30 and +40 and > +40. The NPVs and the upper value for each bin are shown in Figure E2.1 below. Calling up the histogram tool brings up a dialog box in which we can designate the range of values to be grouped, the upper values in each bin and the range of cells where we want the histogram to appear. In Figure E2.1, this dialog box has been filled in.

Clicking “OK” in the dialog box then fills in the histogram as shown in Figure E2.2. We see that

[pic]

Figure E2.1 Completed Dialog Box for Histogram of NPVs

we see that 7 of the 20 NPVs are negative, while the rest are positive. The NPVs are not particularly bunched in any particular bin, and 14 of the 20 NPVs are distributed fairly evenly across the range from -$10 million to +$40 million. The data and the completed histogram are also shown in the tab “Figure E2.2 Data” in the Appendix E2 workbook if you would like to experiment with different bin designations.

The histogram tool is useful for grouping observed returns for securities or portfolios in order to get an idea of what the true probability distribution of returns might look like. If we are willing to make an assumption about the nature of the true distribution (the returns are normally distributed, for example), we can even estimate the likelihood that the return will fall within a certain range. We use the histogram tool in Section 7.1.2 of Chapter 7.

[pic]

Figure E2.2 Completed Histogram of NPVs

Finally, let’s consider the regression tool under Data Analysis in Excel. In this course, we run some simple, linear regressions of a sample of return observations for a security or portfolio against the returns over the same period on an overall stock market index in order to estimate the “beta” of the security or portfolio, or its sensitivity to movements in the overall market. This is done in Section 7.3.1 in Chapter 7.

To illustrate, consider Problem 33 in the Section F Sample Problems below. You are asked to regress 12 monthly returns for Waste Management Corporation’s stock (the “Y” or dependent variable) over the period June 2009 through May 2010 against the returns on the Standard & Poor’s (S&P) 500 index (the “X” or independent variable) for the same period. The data are shown in the tab “Figure E2.3 Data” in the Appendix E2 workbook.

If you scroll down the Data Analysis menu and click on the Regression tool, this brings up a dialog box, as shown in Figure E2.3. You can then select the data for the “Input Y Range” (the Waste Management returns that you are trying to “explain” using the market returns), the “Input X Range” (the S&P 500 returns, which serve as a proxy for the market returns), and a cell range where you would like the regression output to be displayed (this range does not have to match exactly the size of the regression output – just be careful you do not overwrite any data that you want to have appear in your worksheet). Clicking on “OK” then produces the regression output. It is left to you to try this for yourself in Problem 33 below.

[pic]

Figure E2.3. Completed Regression Dialog Box for Sample Problem 33

Section F Sample Problems

Note: Problems 30 – 33 make use of 12 months (June 2009 through May 2010) of annualized returns for the Standard & Poor’s (S&P) 500 Index and the common stock of Waste Management Corporation. The data can be found in Tab 7.8 of the Chapter 7 workbook, and they are also copied to the Tab “Figure E2.3 Data” in the Appendix E2 workbook.

30. What was the average return for the S&P 500 Index and for Waste Management for the 12 months June 2009 through May 2010?

31. What was the standard deviation of returns for the S&P 500 Index and for Waste Management for the 12 months June 2009 through May 2010?

32. Construct a histogram of the Waste Management’s 12 monthly returns using the intervals (-100% to -50%), (-50% to 0%), (0% to +50%) and (+50% to +100%).

33. Regress Waste Management’s returns over this 12-month period against the S&P 500 Index Returns. What is Waste Management’s estimated beta for this period, and what is the regression R2?

34. Calculate NORMSDIST (z) for z = 1 and z = 2. How do you interpret your answers? What fraction of the area under a normal density function lies within one standard deviation of the mean? Within two standard deviations of the mean?

-----------------------

[1] Ignore (i.e., leave blank) the last argument, “Guess”, in the RATE function for now.

[2] For an exception, see Problem 2 in Chapter 6.

[3] If you have an entire population of equally likely values from a probability distribution, the average of these numbers is equal to the true population mean. In this case, you can use the function STDEVP to calculate the standard deviation of the distribution. STDEVP substitutes n in place of (n-1) in Equation (E2.11), because, since you know the true population mean, no degree of freedom is used in estimating its value. For large samples, of course, there is little difference between the values calculated by STDEV and STDEVP.

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download