MicrosoftВ® Office ExcelВ® 2007: Data Analysis and Business Modeling (Bpg -- Other)

Overview

• You are buying a copier. Would you rather pay $11,000 today or $3,000 a year for five years?

• If at the end of each of the next 40 years I invest $2,000 a year towards my retirement, and earn 8 percent a year on my investments, how much will I have when I retire?

• I am borrowing $10,000 for 10 months with an annual interest rate of 8 percent. What are my monthly payments? How much principal and interest am I paying each month?

• I want to borrow $80,000 and make monthly payments for 10 years. The maximum monthly payment I can afford is $1,000. What is the maximum interest rate I can afford?

• If I borrow $100,000 at 8 percent interest and make payments of $10,000 per year, how many years will it take me to pay back the loan?

When we borrow money to buy a car or house, we always wonder if we are getting a good deal. When we save for retirement, we are curious how large a nest egg we will have when we retire. In our daily work and personal lives, financial questions similar to these often arise. Knowledge of the Excel PV, FV, PMT, PPMT, IPMT, CUMPRINC, CUMIPMT, RATE, and NPER functions makes it easy to answer these types of questions.

• You are buying a copier. Would you rather pay $11,000 today or $3,000 a year for five years?

• The key to answering this question is being able to value the annual payments of $3,000 per year. We will assume the cost of capital is 12 percent per year. We could use the NPV function to answer this question but the Excel PV function provides a much quicker way to solve this problem. A stream of cash flows that involves the same amount of cash outflow (or inflow) each period is called an annuity. Assuming that each period’s interest rate is the same, an annuity can easily be valued using the Excel PV function. The PV function returns the value in today’s dollars of a series of future payments under the assumption of periodic, constant payments and a constant interest rate. The syntax of the PV function is PV(rate,#per,[pmt],[fv],[type]), where pmt, fv and type are optional arguments.

  Note

  When working with Microsoft Office Excel 2007 financial functions, we use the following conventions for the signs of Pmt (payment) and Fv (future value): money received has a positive sign and money paid out has a negative sign.

  • Rate is the interest rate per period. For example if you borrow money at 6 percent per year and the period is a year then rate=0.06. If the period is a month then rate=0.06/12=0.005.

  • #per is number of periods in the annuity. For our copier example, #per=5. If payments on the copier were made each month for five years then #per=60. Your rate must, of course, be consistent with #per. That is, if #per implies a period is a month you must use a monthly interest rate, whereas if #per implies a period is a year you must use an annual interest rate.

  • Pmt is the payment made each period. For our copier example, pmt=-$5,000. A payment has a negative sign whereas money received has a positive sign. At least one of Pmt or Fv must be included.

  • Fv is the cash balance (or future value) you want to have after the last payment is made. For our copier example, fv=0. For example, if we want to have a $500 cash balance after the last payment, then fv=$500. If we want to make an additional $500 payment at the end of a problem fv=–$500. If fv is omitted, it is assumed equal to 0.

  • Type is either 0 or 1 and indicates when payments are made. When type is omitted or equal to 0 then payments are made at the end of each period. When type=1, payments are made at the beginning of each period. Note that you may also write True instead of 1 and False instead of 0 in all functions discussed in this chapter.

    Figure 9-1 (see worksheet PV of file Excelfinfunctions.xlsx) indicates how to solve our copier problem.

    Figure 9-1: Example of PV function

• In cell B3 we computed the present value of paying $3,000 at the end of each year for five years with a 12 percent cost of capital using the formula =PV(0.12,5,–3000,0,0) Excel returns a NPV of $10,814.33. By omitting the last two arguments, we obtained the same answer with the formula=PV(0.12,5,–3000). Thus it is a better deal to make payments at the end of the year than to pay out $11,000 today.

• If we make payments on the copier of $3,000 at the beginning of each year for five years, the NPV of our payments is computed in cell B4 with the formula =PV(0.12,5,–3000,0,1). Note that changing the last argument from a 0 to a 1 changed the calculations from end of year to beginning of year. We find the present value of our payments to be $12,112.05. Therefore it is better to pay $11,000 today than make payments at the beginning of the year.

• Suppose we pay $3,000 at the end of each year and we must include an extra $500 payment at the end of Year 5. We may now find the present value of all our payments in cell B5 by including a future value of $500 with the formula =PV(0.12,5,–3000–,500,0). Note the $3,000 and $500 cash flows have negative signs because we are paying out the money. We find the present value of all these payments to equal $11,098.04.

• If at the end of each of the next 40 years I invest $2,000 a year towards my retirement, and earn 8 percent a year on my investments, how much will I have when I retire?

• In this situation we want to know the value of an annuity in terms of future dollars (40 years from now) and not today’s dollars. This is a job for the Excel FV (Future Value) function. The FV function gives the future value of an investment assuming periodic, constant payments with a constant interest rate. The syntax of the FV function is FV(rate,#per,[pmt],[pv],[type]), where pmt, pv and type are optional arguments.

  • Rate is the interest rate per period. In our case, rate=0.08.

  • #per is the number of periods in the future at which you want the future value computed. #per is also the number of periods during which the annuity payment is received. In our case, #per=40.

  • Pmt is the payment made each period. In our case pmt=–$2,000. The negative sign indicates we are paying money into an account. At least one of Pmt or Pv must be included.

  • Pv is the amount of money (in today’s dollars) owed right now. In our case pv=$0. If today we owed someone $10,000, then pv=$10,000 because they lent us $10,000 and we received it. If today we had $10,000 in the bank, then pv=–$10,000 because we must have paid $10,000 into our bank account. If pv is omitted it is assumed to equal 0.

  • Type is a 0 or 1 and indicates when payments are due or money is deposited. If type=0 or is omitted, then money is deposited at the end of the period. In our case, type is 0 or omitted. If type=1 then payments are made or money is deposited at the beginning of the period.

• In worksheet FV of file Excelfinfunctions.xlsx (see Figure 9-2) we enter in cell B3 the formula =FV(0.08,40,–2000) to find that in 40 years our nest egg will be worth $518,113.04. Note that we entered a negative value for our annual payment. This is because we paid the $2,000 into our account. We could also have obtained the same answer by entering the last two unnecessary arguments with the formula FV(0.08,40,–2000,0,0).

  Figure 9-2: Example of FV function

• If deposits were made at the beginning of each year for 40 years, then the formula (entered in cell B4) =FV(0.08,40,–2000,0,1) would yield the value in 40 years of our nest egg: $559,562.08.

• Finally, suppose that in addition to investing $2,000 at the end of each of the next 40 years we initially have $30,000 to invest. If we earn 8 percent per year on our investments, how much money will we have when we retire in 40 years? We can answer this question by setting pv=–$30,000 in the FV function. The negative sign is used because we have deposited or paid $30,000 into our account. In cell B5 the formula =FV(0.08,40,– 2000,–30000,0) yields a future value of $1,169,848.68.

• I am borrowing $10,000 for 10 months with an annual interest rate of 8 percent. What are my monthly payments? How much principal and interest am I paying each month?

• The Excel PMT function computes the periodic payments for a loan assuming constant payments and a constant interest rate. The syntax of the PMT function is PMT(rate, #per,pv,[fv],[type]), where fv and type are optional arguments.

  • Rate is the per-period interest rate of the loan. In our example we will use one month as a period, so rate=0.08/12=0.006666667.

  • #per is the number of payments made. In our case, #per=10.

  • Pv is the present value of all our payments. That is, pv is the amount of the loan. In our case, pv=$10,000. Pv is positive because we are receiving the $10,000.

  • Fv indicates the final loan balance you want to have after making the last payment. In our case fv=0. If fv is omitted, Excel assumes it to equal 0. Suppose we have taken out a balloon loan where we make payments at the end of each month, but at the conclusion of the loan we pay off the final balance by making a $1,000 balloon payment. Then fv=–$1,000. The $1,000 is negative because we are paying it out.

  • Type is a 0 or 1 and indicates when payments are due. If type=0 or is omitted, then payments are made at the end of the period. We will first assume end-of-month payments so type is 0 or omitted. If type=1, then payments are made or money is deposited at the beginning of period.

• In cell G1 of worksheet PMT of file Excelfinfunctions.xlsx (see Figure 9-3) we computed the monthly payment on a 10-month loan for $10,000, assuming an 8 percent annual interest rate and end-of-month payments with the formula=–PMT(0.08/12,10, 10000,0,0). We find the monthly payment is $1,037.03. Note the PMT function by itself returns a negative value because we will be making the payment to the company giving us the loan.

  Figure 9-3: Examples of PMT, PPMT, CUMPRINC, CUMIPMT, and IPMT functions

• If desired, we can use the Excel IPMT and PPMT functions to compute the amount of interest paid each month towards the loan and the amount of the balance paid down each month (this is called the payment on the principal).

• To determine the interest paid each month we use the IPMT function. The syntax of the IPMT function is IPMT(rate,per,#per,pv,[fv],[type]), where fv and type are optional arguments. The per argument indicates the period number for which you compute the interest. The other arguments mean the same as they did for the PMT function. Similarly, to determine the amount paid towards the principal each month we use the PPMT function. The syntax of the PPMT function is PPMT(rate,per,#per,pv,fv,type). The meaning of each argument is the same as it was for the IPMT function. By copying from F6 to F7:F16 the formula =–PPMT(0.08/12,C6,10,10000,0,0), we compute the amount of each month’s payment that is applied to the principal. For example, during Month 1 we pay only $970.37 towards the principal. As expected, the amount paid towards the principal increases each month. The minus sign is needed because the principal is paid to the company giving us the loan, and PPMT will return a negative number. By copying from G6 to G7:G16 the formula =–IPMT(0.08/12,C6,10,10000,0,0), we compute the amount of interest paid each month. For example, in Month 1 we pay $66.67 in interest. Of course, the amount of interest we pay each month decreases.

• Note that each month (Interest Paid)+(Payment Towards Principal)=(Total Payment). Sometimes the total is off by a penny due to rounding.

• We can also create our ending balances for each month in column H by using the relationship (Ending Month t Balance)=(Beginning Month t Balance)–(Month t Payment towards Principal). Note that in Month 1, Beginning Balance=$10,000. In column D, we create each month’s beginning balance by using the relationship (for t=2, 3, …10)(Beginning Month t Balance)=(Ending Month t–1 Balance). Of course, Ending Month 10 Balance=$0, as it should.

• Our interest each month can be computed as (Month t Interest)=(Interest rate)*(Beginning Month t Balance).For example, our month 3 interest payment can be computed as =(0.0066667)*($8,052.80)=$53.69.

• Note of course, that the NPV of all our payments is exactly $10,000. We checked this in cell D17 with the formula NPV(0.08/12,E6:E15). (See Figure 9-3.)

• If our payments were made at the beginning of each month, the amount of each payment is computed in cell D19 with the formula =–PMT(0.08/12,10,10000,0,1). Note that changing the last argument to 1 changes each payment to the beginning of the month. Because our lender is getting her money earlier, our monthly payments will be less than the end-of-month case. If we pay at the beginning of the month, our monthly payment is $1,030.16.

• Finally, suppose that we want to make a balloon payment of $1,000 at the end of 10 months. If we make our monthly payments at the end of each month, then the formula =–PMT(0.08/12,10,10000,–1000,0) in cell D20 computes our monthly payment. Our monthly payment turns out to be $940. Because $1,000 of our loan is not being paid with monthly payments, it makes sense that our new monthly payment is less than our original end-of-month payment of $1,037.03.