Calculating Investments

The financial functions built into Excel fall into three major categories: investments, depreciation, and securities. The functions included within each category accept similar arguments. To streamline this chapter, we'll first define the common arguments, and we'll then discuss their implementation in the individual functions.

Table 16-1 lists all the arguments used in functions dedicated to calculating investments.

Table 16-1: Investment Function Arguments
Open table as spreadsheet

Argument	Description
Future value	The value of an investment at the end of the term (0 if omitted)
value1, value2, . . . value n	Periodic payments (inflows) when individual amounts differ
Number of periods	Term of investment
Payment	Periodic payments when individual amounts are the same
Type	When payment is to be made (0 if omitted); 0 = at end of period; 1 = at beginning of period
Period	Number of an individual periodic payment
Present value	Value of investment today
Rate	Discount rate or interest rate
Guess	A starting interest rate for iterative calculations (10 percent if omitted)
Finance rate	The rate at which you borrow money to purchase an investment
Reinvestment rate	The rate at which you reinvest cash received from an investment

The PV Function

Present value is one of the most common methods for measuring the attractiveness of a long-term investment. Present value is today's value of the investment. It's determined by discounting the inflows (payments received) from the investment back to the present time. If the present value of the inflows is greater than the cost of the investment, the investment is a good one.

The PV function computes the present value of a series of equal periodic payments or of a lump-sum payment. (A series of equal payments is often called an ordinary annuity.) This function takes the arguments (rate, number of periods, payment, future value, type); for definitions of these arguments, see Table 16-1. To compute the present value of a series of payments, type a value for the payment argument, or to compute the present value of a lump-sum payment, type a value for the future value argument. For an investment with both a series of payments and a lump-sum payment, use both arguments.

Here's a real-world example of how this function works: Suppose you are presented with an investment opportunity that returns $1,000 each year over the next five years. To receive this annuity, you must invest $4,000. Are you willing to pay $4,000 today to earn $5,000 over the next five years? To decide whether this investment is acceptable, you need to determine the present value of the stream of $1,000 payments you will receive.

Because you could invest your money in a five-year CD money-market account at 4.5 percent, we'll use 4.5 percent as the discount rate of the investment. (Because this discount rate is a sort of hurdle over which an investment must leap before it becomes attractive to you, it's often called the hurdle rate.) To determine the present value of this investment, use the formula =PV(4.5%, 5,1000), which returns the value -4389.98, meaning you should be willing to spend $4,389.98 now to receive $5,000 over the next five years. (Negative values indicate money going out; positive values indicate money coming in.) Because your investment is only $4,000, you can surmise that this is an acceptable investment.

Suppose you're offered $5,000 at the end of five years instead of $1,000 for each of the next five years. Is the investment still as attractive? To find out, use the formula =PV(4.5%, 5,,5000). (Include a comma as a placeholder for the unused payment argument.) This formula returns the present value -4012.26, which means that, at a hurdle rate of 4.5 percent, you should be willing to spend $4,012.26 to receive $5,000 in five years. Although the proposal may not be nearly as attractive under these terms, it's still acceptable because your investment is only $4,000. However, it also makes a guaranteed 4.5 percent CD look much more attractive by comparison.

The NPV Function

The NPV function calculates the net present value, which is another common method for determining the profitability of an investment. In general, any investment that yields a net present value greater than zero is considered profitable. This function takes the arguments (rate, value1,value2, . . .); for definitions of these arguments, see Table 16-1. You can use as many as 254 inflow values as arguments, but you can include any number of values by using an array as an argument.

NPV differs from PV in two important respects. Whereas PV assumes constant inflow values, NPV allows variable payments. The other major difference is that PV allows payments and receipts to occur at either the beginning or the end of the period, whereas NPV assumes that all payments and receipts are evenly distributed and that they occur at the end of each period. If the cost of the investment must be paid up front, you should not include the cost as one of the function's inflow arguments but should subtract it from the result of the function. On the other hand, if the cost must be paid at the end of the first period, you should include it as a negative first inflow argument. Let's consider an example to help clarify this distinction.

Suppose you are contemplating an investment on which you expect to incur a loss of $85,000 at the end of the first year, followed by gains of $95,000; $140,000; and $185,000 at the ends of the second, third, and fourth years. You will invest $250,000 up front, and the hurdle rate is 8 percent. To evaluate this investment, use the formula =NPV(8%, -85000, 95000, 140000, 185000) -250000.

The result, -139.48, tells you not to expect a net profit from this investment. Note that the negative values in this formula indicate the money you spend on your investment. (You can use the Goal Seek command to determine what initial cost or interest rate would justify the investment. For more information about this command, see "Using the Goal Seek Command" on page 599.)

This formula does not include the up-front cost of the investment as an argument for the NPV function. However, if you fund the initial $250,000 investment at the end of the first year instead of at the beginning, the formula is =NPV(8%,(-250000-85000), 95000, 140000, 185000). The result, $18,379.04, would suggest that this might be a profitable investment.

The FV Function

The FV function determines the future value of an investment and is essentially the opposite of present value, computing the value at some future date of an investment that makes payments as a lump sum or as a series of equal periodic payments. This function takes the arguments (rate, number of periods, payment, present value, type); for definitions of these arguments, see Table 16-1. Use the payment argument to compute the future value of a series of payments and the present value argument to compute the future value of a lump-sum payment.

Suppose you're thinking about starting an IRA. You plan to deposit $4,000 in the IRA at the beginning of each year, and you expect the average rate of return to be 6 percent per year for the entire term. Assuming you're now 30 years old, how much money will your account accumulate by the time you're 65? Use the formula =FV(6%, 35, -4000,, 1) to learn that your IRA balance will be $472,483.47 at the end of 35 years.

Now assume you started an IRA account three years ago and have already accumulated $7,500 in your account. Use the formula =FV(6%, 35, -4000, -7500, 1) to learn that your IRA will grow to $530,129.12 at the end of 35 years.

In both of these examples, the type argument is 1, because payments occur at the beginning of the period. Including this argument is particularly important in financial calculations that span many years. If you omit the type argument (1) in the preceding formula, Excel assumes you add money to your account at the end of each year and returns the value $503,384.77-a difference of $26,744.35!

The PMT Function

The PMT function computes the periodic payment required to amortize a loan over a specified number of periods. This function takes the arguments (rate, number of periods, present value, future value, type); for definitions of these arguments, see Table 16-1.

Suppose you want to take out a 30-year mortgage for $300,000. Assuming an interest rate of 6 percent, what will your monthly payments be? First, divide the 6 percent interest rate by 12 to arrive at a monthly rate (0.5 percent). Next, convert the number of periods into months by multiplying 30 by 12 (360). You can include these computations as arguments using the formula =PMT((6%/12), (30*12), 300000) to compute the monthly mortgage payment, which turns out to be -$1,798.65. (The result is negative because it's a cost to you.)

The IPMT Function

The IPMT function computes the interest part of an individual payment made to repay an amount over a specified time period, with constant periodic payments and a constant interest rate. This function takes the arguments (rate, period, number of periods, present value, future value, type); for definitions of these arguments, see Table 16-1.

Suppose you borrow $200,000 for 30 years at 6 percent interest. The formula =IPMT((6/12)%, 1, 360, 200000) tells you that the interest component of the payment due for the first month is an even -$1,000.00. The formula =IPMT((6/12)%, 360, 360, 200000) tells you that the interest component of the final payment of the same loan is -$5.97.

TROUBLESHOOTING 

The PMT function produces unrealistic results.

Sometimes you might find that the PMT function seems to produce unrealistic results-such as payments that are excessively large. As is the case with all functions used for calculating investments, make sure you are using the same units for both the rate and nper (number of periods) arguments. If, for example, you type 6% for the rate, you must type the nper argument in years, because 6 percent is an annual rate. If you type 6% for the rate and 360 as the term, Excel returns the payment required to amortize a loan at either 6 percent per month for 30 years or 6 percent per year for 360 years! You can resolve your problem by either dividing 6 percent by 12 (which is the standard way of expressing a loan) or typing 30 for nper, indicating the term in years. Note, however, that these two options are not equivalent-they yield very different results because of the way interest is calculated. You should use the same units that your lender uses, which is probably annual interest rate divided by 12 and nper expressed in months.

The PPMT Function

The PPMT function is similar to the IPMT function, except it computes the principal component of an individual payment when a loan is repaid over a specified time with constant periodic payments and a constant interest rate. If you compute both IPMT and PPMT for the same period, you can add the results to obtain the total payment. The PPMT function takes the arguments (rate, period, number of periods, present value, future value, type); for definitions of these arguments, see Table 16-1.

If you borrow $200,000 for 30 years at 6 percent interest, the formula =PPMT((6/12)%, 1, 360, 200000) tells you that the principal component of the payment for the first month of the loan is -$199.10. The formula =PPMT((6/ 12)%, 360, 360, 200000) tells you that the principal component of the final payment of the same loan is -$1193.14.

The NPER Function

The NPER function computes the number of periods required to amortize a loan, given a specified periodic payment. This function takes the arguments (rate, payment, present value, future value, type); for definitions of these arguments, see Table 16-1.

Suppose you can afford mortgage payments of $2,000 per month and you want to know how long it will take to pay off a $300,000 loan at 6 percent interest. The formula =NPER((6/12)%, -2000, 300000) tells you that your mortgage payments will extend over 278 months.

If the payment is too small to amortize the loan at the indicated rate of interest, the function returns an error value. The monthly payment must be at least equal to the period interest rate times the principal amount; otherwise, the loan will never be amortized. For example, the formula =NPER((6/12)%, -1000, 300000) returns the #NUM! error value. In this case, the monthly payment must be at least $1,501 to amortize the loan (although it would take more than 120 years worth of payments at that amount).

The RATE Function

The RATE function determines the rate of return of an investment that generates a series of equal periodic payments or a single lump-sum payment. This function takes the arguments (number of periods, payment, present value, future value, type, guess); for definitions of these arguments, see Table 16-1. You use either the payment argument to compute the rate for a series of equal periodic payments or the future value argument to compute the rate of a lump-sum payment.

Suppose you're considering an investment that will pay you four annual $1,000 payments. The investment costs $3,000. To determine the actual annual rate of return on your investment, type the formula =RATE(4, 1000, -3000). This formula returns 13 percent, an excellent rate of return on this investment.

Note 

The RATE function uses iteration to compute the rate of return. The function begins by computing the net present value of the investment at the guess rate. If that first net present value is greater than zero, the function selects a higher rate and repeats the net present value calculation; if the first net present value is less than zero, the function selects a lower rate for the second iteration. RATE continues this process until it arrives at the correct rate of return or until it has gone through 20 iterations. For more information about iteration, see "Working with Circular References" on page 464.

If you receive the #NUM! error value when you enter the RATE function, Excel probably cannot calculate the rate within 20 iterations. Try typing a different guess rate to give the function a running start. A rate from 10 percent through 100 percent usually works.

The IRR Function

The IRR function determines the internal rate of return of an investment, which is the rate that causes the net present value of the investment to equal zero. In other words, the internal rate of return is the rate that causes the present value of the inflows from an investment to equal the cost of the investment.

Internal rate of return, like net present value, compares one investment opportunity with another. An attractive investment is one whose net present value, discounted at the appropriate hurdle rate, is greater than zero. Turn that equation around, and you can see that the discount rate required to generate a net present value of zero must be greater than the hurdle rate. Thus, an attractive investment is one for which the discount rate required to yield a net present value of zero-that is, the internal rate of return-is greater than the hurdle rate.

The IRR function takes the arguments (values, guess). (For definitions of these arguments, see Table 16-1.) The values argument is an array or a reference to a range of cells that contain numbers. Only one values argument is allowed, and it must include at least one positive and one negative value. IRR ignores text, logical values, and blank cells. IRR assumes that transactions occur at the end of a period and returns the equivalent interest rate for that period's length. The guess argument is optional, but if you receive the #NUM! error value, try including a guess to help Excel reach the answer.

Suppose you agree to buy an income property for $350,000 and rent it. Over the next ten years, you expect to receive net rental income starting at $40,000 the first year, increasing by $1,000 per year. You can set up a simple worksheet that contains your investment and income information. Type the 11 values, starting with the initial investment amount, in cells A1:A11 on the worksheet. (Be sure to type the initial $350,000 investment in cell A1 as a negative value.) Then the formula =IRR(A1:A11) returns the internal rate of return of 4.46 percent. If the hurdle rate is 3.5 percent, you can consider this property to be a good investment.

The MIRR Function

The MIRR function calculates the modified internal rate of return of an investment. The difference from the IRR function is that MIRR takes into account the cost of the money you borrow to finance the investment. MIRR assumes you'll reinvest the cash it generates and that transactions occur at the end of a period. It then returns the equivalent interest rate for that period's length.

The MIRR function takes the arguments (values, finance rate, reinvestment rate). (For definitions of these arguments, see Table 16-1.) The values argument must be an array or a reference to a range of cells that contain numbers. This argument represents a series of payments and income occurring at regular periods. You must include at least one positive and one negative value in the values argument.

Suppose you borrow $120,000 at 7 percent interest to acquire an investment that will return increasing amounts of income over five years. If cells A1 through A6 contain the values -120000, 22000, 24000, 28000, 31000, and 33000, representing the initial investment (as a negative value) and the subsequent cash inflows from that investment, the formula =MIRR(A1:A6, 7%, 3.5%) returns a modified internal rate of return of 4 percent.