Increasing Perpetuity Calculator - Excel

Document Sample

```					                                                                                                    1/8/2008
Chapter 2. The Time Value of Money

The "Chapter" worksheet performs the calculations required for Chapter 2, and was used to create many of
the chapter exhibits (Tables and Figures). We pasted in a few dialog boxes for specific Excel functions and
features and show then off to the right of where they apply, but in general we encourage students who want
to know more about Excel to use the Excel Tutorial and refer to it as necessary. We also like to let students
know that Excel models can be used to create tables and graphs that can then be copied into Word
documents, which is the way we prepared the text manuscript for submission to the publisher. That
procedure is used often in business to prepare reports.
Although answers to the Self-Test questions within the chapter are generally quite easy and were found
with a calculator, we also solved them with Excel as a check and also to provide some information on the
solutions in case students have questions. The tabs at the lower part of this screen take you to these
solutions. Even if students are not familiar with Excel, they should still be able to see the solution setup
and then work out the answer with a calculator. Although we did not create the model specifically for use
in lectures, it could be used as such in a classroom where a projector is attached to a computer. The
instructor could scroll through the model and lecture on points as they come up. This would be more
useful if the students have some familiarity with Excel, but that is not really necessary because everything
the model does can also be done with a financial calculator.

FUTURE VALUES (Section 2.2)
A dollar in hand today is worth more than a dollar to be received in the future because, if you had it now,
you could invest it, earn interest, and end up with more than a dollar in the future. The process of going to
future values (FVs) from present values (PVs) is called compounding.

To illustrate, refer to our 3-year time line and assume that you plan to deposit \$100 in a bank that pays a
guaranteed 5% interest each year. How much would you have at the end of Year 3?
Figure 2-1. Summary of Future Value Calculations

Investment     = CF0 = PV =             -\$100.00
Interest rate = I =                      5.00%
No. of periods = N =                        3
Periods:          0                  1                2              3
|                  |                |              |
Cash Flow Time Line:           -\$100                                              FV = ?

Step-by-Step Approach:                                     \$100               \$105.00       \$110.25          \$115.76

Formula Approach: FVN = PV(1+I)N                                  FV3 =   \$100(1.05)3            =           \$115.76

3                 5             -\$100.00              \$0
Calculator Approach:                       N               I/YR               PV                PMT            FV
\$115.76

Excel Approach:                     FV Function:           FVN =          =FV(I,N,0,PV)
Fixed inputs:          FVN =          =FV(0.05,3,0,-100) =               \$115.76
Cell references:         FVN =          =FV(C15,C16,0,C14) =               \$115.76
In the Excel formula, the terms are entered in this sequence: interest, periods, 0 to indicate no intermediate cash
flows, and then the PV. The data can be entered as fixed numbers or as cell references.

The Compounding Process: A Graphic View
Figure 2-2 (shown below) shows how a \$1 investment grows over time at different interest rates. The
curves were created by solving for FV at different values for N and I. This allows you to simultaneously see
the effects of varying time and the interest rate.

The data table used to create this figure is shown to the right. For instruction on data tables, refer to the
Excel Tutorial.

Future Value
of \$1
6.00
I = 20%
5.00

4.00

3.00
I = 10%
2.00                                                                                 I = 5%
1.00
I = 0%
0.00
0       1        2        3          4       5          6       7         8       9         10
Periods
PRESENT VALUES (Section 2.3)
In many ways the present value is just the opposite of the future value. Instead of compounding a value
forward, you discount it back. If you know the PV, you can compound to find the FV, while if you know the
FV, you can discount to find the PV.

To illustrate, refer to the time line and assume that \$115.76 is due in 3 years. If a bank pays a guaranteed
5% interest rate each year, how much would you need to deposit now to have \$115.76 in 3 years?

Figure 2-3. Summary of Present Value Calculations

Future payment = CFN = FV =             \$115.76
Interest rate  = I =                     5.00%
No. of periods = N =                        3
Periods:         0                   1           2          3
|                   |           |          |
Cash Flow Time Line:          PV = ?                                    \$115.76

Step-by-Step Approach:                                   \$100.00           \$105.00       \$110.25    \$115.76

Formula Approach: PV = FVN / (1 + I)N                             PV = \$115.76/(1.05)3      =       \$100.00

3                 5                             \$0       \$115.76
Calculator Approach:                       N               I/YR              PV           PMT         FV
-\$100.00

Excel Approach:                     PV Function:           PV =           =PV(I,N,0,FV)
Fixed inputs:          PV =           =PV(0.05,3,0,115.76) =    -\$100.00
Cell references:         PV =           =PV(C65,C66,0,C64) =      -\$100.00

In the Excel function, 0 indicates that there are no intermediate cash flows.
The Discounting Process: A Graphic View
Figure 2-4 shows how a \$1 payment in the future has a lower and lower present value as the interest rate
and time until receipt increase. The data table to the right provides the data used to draw the figure.

Present Value
of \$1
I = 0%
1.00

0.80
I = 5%
0.60
I = 10%
0.40
I = 20%
0.20

0.00
0             10              20              30              40     Periods 50

FINDING THE INTEREST RATE (Section 2.4)
Previously, we solved equations to find FV and PV. However, we could just as easily solve for I or N. For
example, suppose we know that a given bond has a cost of \$100 and that it will return \$150 after 10 years.
Thus, we know PV, FV, and N, and we want to find the rate of return we would earn if we bought the bond.

Present value (PV)                  -\$100.00
Future value (FV)                    \$150.00
No. of years (N)                        10
Interest rate (I)                = RATE(N,0,PV,FV)
Interest rate (I)                     4.14%

FINDING THE NUMBER OF YEARS (Section 2.5)
Sometimes we need to know how long it will take to accumulate a given sum of money, given our
beginning funds and the rate we will earn on those funds. For example, suppose we believe that we could
retire comfortably if we had \$1 million, and we want to find how long it will take us to reach that goal,
assuming that we now have \$500,000 invested at 4.5%.

Present value (PV)                 -\$500,000
Future value (FV)                  \$1,000,000
Interest rate (I)                    4.50%
No. of years (N)                 =NPER(I,0,PV,FV)
No. of years (N)                      15.75
FUTURE VALUE OF AN ORDINARY ANNUITY (Section 2.7)
An ordinary annuity has regular, periodic payments that occur at the end of each period. Methods for
solving the future value of an ordinary annuity are shown below.

Figure 2-5. Summary: Future Value of an Ordinary Annuity

Payment amount = PMT =                \$100.00
Interest rate     = I =                5.00%
Number of periods = N =                   3

Periods:          0             1           2              3
|             |           |              |
Cash Flow Time Line:                         -\$100       -\$100          -\$100

Step-By-Step Approach.                                                          -\$100.00
Multiply each payment by                                                      -\$105.00
(1+I)N-t and sum these FVs to                                                 -\$110.25
find FVAN:                                                                    -\$315.25

Formula Approach:
 (1  I) N 1 
FVAN      =       PMT                    =                 \$315.25
     I     I
             
3              5          \$0           -\$100.00
Calculator Approach:                     N            I/YR         PV             PMT             FV
\$315.25

Excel Function Approach:            FV Function:     FVAN =    =FV(I,N,PMT,PV)
Fixed inputs:    FVAN =    =FV(0.05,3,-100,0)     =         \$315.25
Cell references:   FVAN =    =FV(C132,C133,-C131,0) =         \$315.25
FUTURE VALUE OF AN ANNUITY DUE (Section 2.8)
An annuity due also has regular, periodic payments, but unlike an ordinary annuity, the payments occur at
the beginning of each period.

Summary: Future Value of an Annuity Due (Not in Text)

Payment amount = PMT =                  \$100.00
Interest rate     = I =                  5.00%
Number of periods = N =                     3

Periods:          0                1                 2                 3
|                |                 |                 |
Cash Flow Time Line:           -\$100            -\$100             -\$100

Step-By-Step Approach.                                                                      -\$105.00
Multiply each payment by                                                                  -\$110.25
(1+I)N-t and sum these FVs to                                                             -\$115.76
find FVAN:                                                                                -\$331.01

Formula Approach:
 (1 I)N 1
FVAN(due) =      PMT            (1 I)          =                    \$331.01
 I       I
          
BEG MODE                  3                 5                0               -100
Calculator Approach:                       N                 I               PV               PMT               FV
331.01

Excel Function Approach:            FV Function:          FVAN =        =FV(I,N,PMT,PV,Type)
Fixed inputs:         FVAN =        =FV(0.05,3,-100,0,1)      =           331.01
Cell references:        FVAN =        =FV(C163,C164,-C162,0,1) =            331.01

In the Excel formula, the 1 at the end of the formula indicates that cash flows occur at the beginning of each period. A
0 or nothing would indicate end of period payments.
PRESENT VALUE OF AN ORDINARY ANNUITY (Section 2.9)
The present value of an ordinary annuity is the sum of the PVs of the individual cash flows. Methods for
solving the present value of an ordinary annuity are shown below.

Figure 2-6. Summary: Present Value of an Ordinary Annuity

Payment amount = PMT =                \$100.00
Interest rate     = I =                5.00%
Number of periods = N =                   3

Periods:         0                    1                   2             3
|                    |                   |             |
Cash Flow Time Line:                              -\$100               -\$100         -\$100

Step-By-Step Approach.                 \$95.24
Divide each payment by               \$90.70
(1+I)t and sum these PVs to          \$86.38
find PVAN:                          \$272.32

Formula Approach:
   1            1          
PVAN     =      PMT        
       -                   

=          \$272.32
N
   I       I (1  I)       
3                    5                               -100          0
Calculator Approach:                    N                    I                  PV           PMT          FV
272.32

Excel Function Approach:          PV Function:             PVAN =           =PV(I,N,PMT,FV)
Fixed inputs:            PVAN =           =PV(0.05,3,-100,0)      =   272.32
Cell references:           PVAN =           =PV(C197,C198,-C196,0) =    272.32
PRESENT VALUE OF AN ANNUITY DUE (not in text)
The difference between the present value of an ordinary annuity and an annuity due is that payments are
received earlier in an annuity due.

Summary: Present Value of an Annuity Due (Not in text)

Payment amount = PMT =              \$100.00
Interest rate     = I =              5.00%
Number of periods = N =                 3

Periods:        0                 1              2               3
|                 |              |               |
Cash Flow Time Line:        -\$100             -\$100          -\$100

Step-By-Step Approach.              -\$100.00
Divide each payment by             -\$95.24
(1+I)t and sum these PVs to        -\$90.70
find PVAN:                        -\$285.94

Formula Approach:
1       1               
PVAN     =      PMT   -                       (1  I) =         \$285.94
 I I (1  I) N          
                        
BEG MODE              3                 5                           -100            0
Calculator Approach:                   N                 I              PV           PMT            FV
285.94

Excel Function Approach:          PV Function:        PVAN =       =PV(I,N,PMT,FV,Type)
Fixed inputs:       PVAN =       =PV(0.05,3,-100,0,1)      =    285.94
Cell references:      PVAN =       =PV(C229,C230,-C228,0,1) =     285.94

FINDING ANNUITY PAYMENTS, PERIODS, AND INTEREST RATES                            (Section 2.10)
Fundamentally, this section is no different than previous TVM exercises. When solving for PMT, N, or I, you
must be given values for the other variables, and then you solve the problem.

FINDING PMT
Suppose we need to accumulate \$10,000 and have it available 5 years from now. Suppose further that we
can earn a return of 6% on our savings, which are currently zero.

No. of years (N)                       5
Interest rate (I)                     6%
Present value (PV)                    \$0
Future value (FV)                   \$10,000

END MODE                                          BEGIN MODE
Payment (PMT)   -\$1,773.96                         Payment (PMT)   -\$1,673.55
=PMT(I,N,PV,FV)                                    =PMT(I,N,PV,FV,Type)
FINDING N
Suppose you decide to make end-of-year deposits, but you can only save \$1,200 per year. Again assuming
that you would earn 6%, how long would it take you to reach your \$10,000 goal?

Interest rate (I)                     6%
Present value (PV)                    \$0
Payment (PMT)                       -\$1,200
Future value (FV)                   \$10,000

No. of years (N)                    6.96
=NPER(I,PMT,PV,FV)
FINDING I
Now suppose you can only save \$1,200 annually, but you still want to have the \$10,000 in 5 years. What
rate of return would enable you to achieve your goal?

No. of years (N)                       5
Present value (PV)                    \$0
Payment (PMT)                       -\$1,200
Future value (FV)                   \$10,000

Interest rate (I)                  25.78%
=RATE(N,PMT,PV,FV)

PERPETUITIES (Section 2.11)
Perpetuities are securities that promise to make payments forever. The present value of a perpetuity can be
found with a simple formula: Value = I / r . Note that we cannot calculate the future value of a perpetuity
because, since payments go on forever, this value would be infinitely large and thus meaningless.

Consider a British consol that pays a \$25 annual payment. If interest rates are currently 5.2%, what is the
value of the consol?

Payment (PMT)                        \$25
Interest rate (I)                    5.2%

Interest rate (I)                   \$480.77

If an annuity makes constant payments, then adding more payments to the security adds less value for
each additional payment. This helps explain why perpetuities' values are finite, while payments are infinite.
To see this better, consider the figure below (not in the text). The data used to construct the graph is
shown to the right in columns I through L. One hundred payments are analyzed and their present values,
the total value of an annuity of N number of years, and the contribution of the Nth payment are all shown in
the table.
Payments in an
Annuity
\$100
Value of 25-Year Annuity:                 \$907.70
Value of 50-Year Annuity:                 \$991.48
Value of 100-Year Annuity:                \$999.93
Value of Perpetuity:                    \$1,000.00

26-50:                   \$83.78
\$50
51-100:                    \$8.45

\$0
0      10        20       30      40        50       60       71          81     91
Years (N)

UNEVEN CASH FLOWS (Section 2.12)
An annuity has constant payments. Although many financial decisions do involve annuities, many others
involve uneven, or nonconstant, cash flows. With a spreadsheet, the present value of a series of uneven
cash flows (called the net present value) can be calculated easily .

First, consider a security that pays \$100 for 5 years and a lump sum of \$1,000 at the end of 5 years. We can
find the PV using either the PV function or the NPV function.

Summary of Uneven Cash Flow Present Value Calculations (Annuity plus Lump Sum)

Interest rate         =   I    =         12%

Periods:          0              1                 2                 3                  4            5
|              |                 |                 |                  |            |
Annuity CFs:            \$0            \$100              \$100              \$100               \$100         \$100
Lump sum CFs:                                                                                             \$1,000
Total CFs:           \$0            \$100              \$100              \$100               \$100        \$1,100
PV of CFs
\$89.29
79.72
71.18
63.55
624.17
\$927.90       = PV of cash flow stream = value of the asset

Excel Function Approach:            Fixed inputs:         PV =       =PV(0.12,5,-100,-1000)                927.90
Cell references        PV =       =PV(B335,G337,-C339,-G340)            927.90
Fixed inputs:    NPV =      =NPV(0.12,100,100,100,100,1100)           927.90
Cell references:    NPV =      =NPV(B335,C341:G341)                      927.90
Now consider an irregular cash flow stream (where CFs can take on any value).

Figure 2-7. PV of an Uneven Cash Flow Stream

Interest rate       =   I    =         12%

Periods:       0                  1               2                3               4               5
|                  |               |                |               |               |
CF Time Line:       \$0                \$100            \$300             \$300            \$300            \$500
PV of CFs
\$89.29
239.16
213.53
190.66
283.71
\$1,016.35          = PV of cash flow stream = value of the asset

Excel Function Approach:           Fixed inputs:      NPV =        =NPV(0.12,100,300,300,300,500)     1,016.35
Cell references:     NPV =        =NPV(B358,C362:G362)               1,016.35

Our Excel formula ignores the initial cash flow (in Year 0). When entering a cash flow range, Excel assumes
that the first value entered occurs at the end of the first year. If there is an initial cash flow, as we will see
later, that cash flow must be separately added to the NPV formula result. Notice too that you can enter
cash flows one-by-one, or if the cash flows appear in consecutive cells, you can enter the cell range.

FUTURE VALUE OF AN UNEVEN CASH FLOW STREAM (Section 2.13)
We find the future value of uneven cash flow streams by compounding rather than discounting. The step-
by-step approach works the same, but unfortunately, Excel does not have a net future value (NFV) function.
One way around this is to solve for the NPV and find the FV of this amount to the end of the cash flow
stream.

Figure 2-8. FV of an Uneven Cash Flow Stream

Interest rate       =   I    =         12%

Periods:          0               1               2                3               4                5
|               |               |                |               |                |
CF Time Line:          \$0             \$100            \$300             \$300            \$300             \$500
\$500.00
336.00
376.32
421.48
157.35
0.00
FV of cash flow stream =     \$1,791.15
Excel Function Approach:    First find NPV:   NPV =   =NPV(B383,C387:G387)     1,016.35
Then compound NPV for 5 years:   NFV =   =FV(B383,G385,0,-G397)   1,791.15
The NFV result using the Excel formulas is a negative number. This is because we used Excel's FV
function and entered the NPV as a positive value as the PV.

SOLVING FOR I WITH UNEVEN CASH FLOWS (Section 2.14)
Assume that an investment with the following positive cash flows has a cost of \$927.90. Find the rate of
return on this investment.

Finding the Interest Rate, Annuity Plus Lump Sum

Annuity pmts         \$100
Future lump sum     \$1,000

Periods:        0                1               2               3               4              5
|               |               |               |               |               |
CF Time Line:      -\$927.90          \$100            \$100            \$100            \$100           \$1,100

Excel Function Approach:        Cell references:     RATE =       =RATE(G411,B408,B413,B409)        12.00%
Excel Function Approach:        Cell references:      IRR =       =IRR(B413:G413)                   12.00%

Figure 2-9. IRR of an Uneven Cash Flow Stream

Periods:        0                1               2               3               4               5
|                |               |               |               |               |
CF Time Line:      -\$1,000           \$100            \$300            \$300            \$300            \$500

Excel Function Approach:        Cell references:      IRR =       =IRR(B423:G423)                   12.55%

SEMIANNUAL AND OTHER COMPOUNDING PERIODS (Section 2.15)

If \$100 is invested in an account at an annual nominal interest rate of 10% for 5 years, what are the future
values and effective interest rates for annual, semiannual, quarterly, monthly and daily compounding?

Nominal annual rate =             10%
Amount invested =              \$100
Number of years =                  5

Table 2-1. The Impact of Frequent Compounding

Number of
Frequency of       Nominal          periods          Periodic     Effective
Compounding       Annual Rate     per year (M)     Interest Rate Annual Rate Future Value
Annual               10%                       1          10.00%      10.000%     \$161.05
Semiannual           10%                       2           5.00%      10.250%     \$162.89
Quarterly            10%                       4           2.50%      10.381%     \$163.86
Monthly              10%                      12           0.83%      10.471%     \$164.53
Daily                10%                     365           0.03%      10.516%     \$164.86
AMORTIZED LOANS (Section 2.17)
If a loan is to be repaid in equal amounts on a monthly, quarterly, or annual basis it is said to be an
amortized loan.

Table 2-4 (replicated below) illustrates the amortization process. A homeowner borrows \$100,000 on a
mortgage loan, and the loan is to be repaid in 5 equal payments at the end of each of the next 5 years. The
lender charges 6% on the balance at the beginning of each year.
First, we solve for the required payment, then we construct an amortization table.

N                5
I              6%
PV            \$100,000
FV              \$0

PMT           -\$23,739.64

Table 2-2. Loan Amortization Schedule, \$100,000 at 6% for 5 Years
Amount borrowed:       \$100,000
Years:               5
Rate:            6%
PMT:     \$23,739.64

Beginning                                         Repayment of         Ending
a
Amount           Payment           Interest        Principalb         Balance
Year              (1)               (2)              (3)         (2) - (3) = (4)   (1) - (4) = (5)
1           \$100,000.00        \$23,739.64       \$6,000.00        \$17,739.64          \$82,260.36
2            \$82,260.36        \$23,739.64       \$4,935.62        \$18,804.02          \$63,456.34
3            \$63,456.34        \$23,739.64       \$3,807.38        \$19,932.26          \$43,524.08
4            \$43,524.08        \$23,739.64       \$2,611.44        \$21,128.20          \$22,395.89
5            \$22,395.89        \$23,739.64       \$1,343.75        \$22,395.89                 \$0.00

a
Interest in each period is calculated by multiplying the loan balance at the beginning of the year by
the interest rate. Therefore, interest in Year 1 is \$100,000(0.06) = \$6,000; in Year 2 it is
\$82,260.36(0.06) = \$4,935.62; and so on.
b
Repayment of principal is equal to the payment of \$23,739.64 minus the interest charge for the
year.

Growing Annuities (Section 2.18)
SECTION 2.2
SOLUTIONS TO SELF-TEST

2a What would the future value of \$100 be after 5 years at 10% compound interest?

N              5
I           10%
PV           \$100
PMT            \$0                  FV      \$161.05

3a Suppose you currently have \$2,000 and plan to purchase a 3-year certificate of
deposit (CD) that pays 4 percent interest compounded annually. How much will you
have when the CD matures?

N              3
I           4%
PV          \$2,000
PMT            \$0                  FV      \$2,249.73

3b How would your answer change if the interest rate were 5%, or 6%, or 20%?

Interest rate   \$2,249.73
5%         \$2,315.25
6%         \$2,382.03
20%         \$3,456.00

4 A company’s sales in 2006 were \$100 million. If sales grow at 8 percent, what will they
be 10 years later, in 2016?

N             10
I             8%
PV (\$M)         \$100
PMT             \$0               FV (\$M)    \$215.89

5a How much would \$1, growing at 5% per year, be worth after 100 years?

N             100
I            5%
PV             \$1
PMT             \$0                 FV      \$131.50

5b What would FV be if the growth rate were 10%?

N            100
I           10%
PV            \$1
PMT            \$0                  FV     \$13,780.61
SECTION 2.3
SOLUTIONS TO SELF-TEST

3a Suppose a U.S. government bond promises to pay \$2,249.73 three years from now. If the
going interest rate on 3-year government bonds is 4%, how much is the bond worth today?

N             3
I          4%
PMT           \$0
FV         \$2,250                    PV       \$2,000.00

3b How would your answer change if the bond matured in 5 rather than 3 years?

N             5
I          4%
PMT           \$0
FV         \$2,250                    PV       \$1,849.11

3c What if the interest rate on the 5-year bond were 6% rather than 4%?

N             5
I          6%
PMT           \$0
FV         \$2,250                    PV       \$1,681.13

4a How much would \$1,000,000 due in 100 years be worth today if the discount rate were 5%?

N            100
I           5%
PMT            \$0
FV       \$1,000,000                  PV       \$7,604.49

4b If the discount rate were 20%?

N            100
I          20%
PMT            \$0
FV       \$1,000,000                  PV        \$0.0121
SECTION 2.4
SOLUTIONS TO SELF-TEST

1a The U.S. Treasury offers to sell you a bond for \$585.43. No payments will be made until the
bond matures 10 years from now, at which time it will be redeemed for \$1,000. What interest rate
would you earn if you bought this bond for \$585.43?

N             10
PMT            \$0
PV         \$585.43
FV          \$1,000                     I        5.50%

1b What rate would you earn if you could buy the bond for \$550?

N             10
PMT            \$0
PV         \$550.00
FV          \$1,000                     I        6.16%

1c For \$600?

N             10
PMT            \$0
PV         \$600.00
FV          \$1,000                     I        5.24%

2a Microsoft earned \$0.12 per share in 1994. Ten years later, in 2004, it earned \$1.04. What was
the growth rate in Microsoft’s earnings per share (EPS) over the 10-year period?

N            10
PMT           \$0
PV          \$0.12
FV          \$1.04                      I        24.10%

2b If EPS in 2004 had been \$0.65 rather than \$1.04, what would the growth rate have been?

N            10
PMT           \$0
PV          \$0.12
FV           \$1                        I        18.41%
SECTION 2.5
SOLUTIONS TO SELF-TEST

1a How long would it take \$1,000 to double if it were invested in a bank that pays 6% per year?

I          6%
PMT           \$0
PV         \$1,000
FV         \$2,000                     N          11.90

1b How long would it take if the rate were 10%?

I          10%
PMT           \$0
PV         \$1,000
FV         \$2,000                     N          7.27

2a Microsoft’s 2004 earnings per share were \$1.04, and its growth rate during the prior 10 years
was 24.1% per year. If that growth rate were maintained, how long would it take for Microsoft’s
EPS to double?

I         24.1%
PMT           \$0
PV         \$1.04
FV         \$2.08                      N          3.21
SECTION 2.7
SOLUTIONS TO SELF-TEST

1a For an ordinary annuity with 5 annual payments of \$100 and a 10% interest rate, how many years
will the 1st payment earn interest, and what will this payment’s value be at the end?

N             5
I          10%                    Years of int       4
PMT         -\$100
PV           \$0                   Payments FV      \$146.41

1b Answer this same question for the 5th payment.

N             5
I          10%                    Years of int       0
PMT         -\$100
PV           \$0                   Payments FV      \$100.00

2a Assume that you plan to buy a condo 5 years from now, and you estimate that you can save
\$2,500 per year. You plan to deposit the money in a bank that pays 4% interest, and you will make the
first deposit at the end of the year. How much will you have after 5 years?

N            5
I          4%
PMT        -\$2,500
PV          \$0                         FV         \$13,540.81

2b How would your answer change if the interest rate were increased to 6%, or lowered to 3%?

N            5
I          6%
PMT        -\$2,500
PV          \$0                         FV         \$14,092.73

N            5
I          3%
PMT        -\$2,500
PV          \$0                         FV         \$13,272.84
SECTION 2.8
SOLUTIONS TO SELF-TEST

3a Assume that you plan to buy a condo 5 years from now, and you need to save for a
down payment. You plan to save \$2,500 per year, with the first payment made
immediately, and you will deposit the funds in a bank account that pays 4%. How much
will you have after 5 years?

N           5
I         4%
PV         \$0
PMT       -\$2,500                FV      \$14,082.44

2b How much would you have if you made the deposits at the end of each year?

N           5
I         4%
PV         \$0
PMT       -\$2,500                FV      \$13,540.81
SECTION 2.9
SOLUTIONS TO SELF-TEST

3a What is the PVA of an ordinary annuity with 10 payments of \$100 if the appropriate interest rate
is 10%?

N               10
I             10%
PMT            -\$100
FV              \$0                    PV        \$614.46

3b What would PVA be if the interest rate were 4%?

N               10
I             4%
PMT            -\$100
FV              \$0                    PV        \$811.09

3c What if the interest rate were 0%?

N               10
I             0%
PMT            -\$100
FV              \$0                    PV       \$1,000.00

3d How would the PVA values differ if we were dealing with annuities due?

Part a                                Part b                                Part c
N            10                       N            10                       N            10
I          10%                        I          4%                         I           0%
PMT         -\$100                     PMT         -\$100                     PMT         -\$100
FV           \$0                       FV           \$0                       FV           \$0
PV        \$675.90                     PV        \$843.53                     PV       \$1,000.00

4a Assume that you are offered an annuity that pays \$100 at the end of each year for 10 years.
You could earn 8% on your money in other investments with equal risk. What is the most you
should pay for the annuity?

N               10
I             8%
PMT            -\$100
FV              \$0                    PV        \$671.01

4b If the payments began immediately, how much would the annuity be worth?

N               10
I             8%
PMT            -\$100
FV              \$0                    PV        \$724.69
SECTION 2.10
SOLUTIONS TO SELF-TEST

1a Suppose you inherited \$100,000 and invested it at 7% per year. How much could you withdraw
at the end of each of the next 10 years?

N           10
I         7%
PV       \$100,000
FV          \$0                      PMT      -\$14,237.75

N           10
I         7%
PV       \$100,000
FV          \$0                      PMT      -\$13,306.31

2a If you had \$100,000 that was invested at 7% and you wanted to withdraw \$10,000 at the end of
each year, how long would your funds last?

I         7.0%
PV       \$100,000
PMT       -\$10,000
FV          \$0                        N          17.8

2b How long would they last if you earned 0%?

I         0.0%
PV       \$100,000
PMT       -\$10,000
FV          \$0                        N          10.0

2c How long would they last if you earned the 7% but limited your withdrawal to \$7,000 per year?

I         7.0%
PV       \$100,000                                          * This result means that with \$7,000
PMT        -\$7,000                                          withdrawals, you would never
FV          \$0                        N        #NUM!       exhaust the funds.

3 Your rich uncle named you as the beneficiary of his life insurance policy. The insurance
company gives you a choice of \$100,000 today or a 12-year annuity of \$12,000 at the end of each
year. What rate of return is the insurance company offering?

N           12
PMT        \$12,000
PV       \$100,000
FV          \$0                        I         6.11%
4a Assume that you just inherited an annuity that will pay you \$10,000 per year for 10 years, with
the first payment being made today. A friend of your mother offers to give you \$60,000 for the
annuity. If you sell it, what rate of return would your mother’s friend earn on his investment?

N            10
PMT        -\$10,000
PV         \$60,000
FV           \$0                        I         13.70%

4b If you think a “fair” return would be 6%, how much should you ask for the annuity?

N            10
I           6%
PMT        -\$10,000
FV           \$0                       PV      \$78,016.92
SECTION 2.11
SOLUTIONS TO SELF-TEST

1a What’s the present value of a perpetuity that pays \$1,000 per year, beginning one year from
now, if the appropriate interest rate is 5%?

PMT         \$1,000
I           5%                       PV        \$20,000

1b What would the value be if the annuity began its payments immediately?

PMT         \$1,000                                         **The perpetuity value formula
I           5%                       PV        \$21,000    values payments 1 through infinity.
immediately, it must be added to
the formula result.
SECTION 2.12
SOLUTIONS TO SELF-TEST

2a What’s the present value of a 5-year ordinary annuity of \$100 plus an additional \$500 at the end
of Year 5 if the interest rate is 6%?

Interest rate      6%

Year              0          1            2           3            4           5
Ann Pmt           \$0        \$100         \$100        \$100         \$100        \$100
Lump Sum                                                                       \$500
Total CFs          \$0        \$100         \$100        \$100         \$100        \$600

NPV         \$794.87

2b How would the PV change if the \$100 payments occurred in Years 1 through 10 and the \$500
came at the end of Year 10?

Interest rate      6%

Year              0          1            2           3            4           5            6         7      8      9     10
Ann Pmt           \$0        \$100         \$100        \$100         \$100        \$100         \$100      \$100   \$100   \$100   \$100
Lump Sum                                                                                                                   \$500
Total CFs          \$0        \$100         \$100        \$100         \$100        \$100         \$100      \$100   \$100   \$100   \$600

NPV         \$1,015.21

3a What’s the present value of the following uneven cash flow stream: \$0 at Time 0, \$100 in Year 1
(or at Time 1), \$200 in Year 2, \$0 in Year 3, and \$400 in Year 4 if the interest rate is 8%?

Interest rate      8%

Year            0          1            2           3            4
CFs            \$0        \$100         \$200         \$0          \$400

NPV         \$558.07
SECTION 2.13
SOLUTIONS TO SELF-TEST

3a What is the future value of this cash flow stream: \$100 at the end of 1 year, \$150 due after 2
years, and \$300 due after 3 years if the appropriate interest rate is 15%?

Interest rate    15%

Year           0            1           2            3
CFs           \$0          \$100        \$150         \$300

FV of CFs       \$0.00      \$132.25      \$172.50      \$300.00

NFV         \$604.75
SECTION 2.14
SOLUTIONS TO SELF-TEST

1 An investment costs \$465 and is expected to produce cash flows of \$100 at the end of each of
the next 4 years, then an extra lump sum payment of \$200 at the end of the 4th year. What is the
expected rate of return on this investment?

Interest rate    6%

Year            0            1           2            3           4
Ann Pmt        -\$465        \$100        \$100         \$100        \$100
Lump Sum                                                          \$200
Total CFs       -\$465        \$100        \$100         \$100        \$300

IRR         9.05%

2 An investment costs \$465 and is expected to produce cash flows of \$100 at the end Year 1, \$200
at the end or Year 2, and \$300 at the end of Year 3. What is the expected rate of return on this
investment?

Year          0            1           2            3
CFs         -\$465        \$100        \$200         \$300

IRR         11.71%
SECTION 2.15
SOLUTIONS TO SELF-TEST

2a What’s the future value of \$100 after 3 years if the appropriate interest rate is 8%,
compounded annually?

N              3
I           8%
PV          -\$100
PMT            \$0                    FV       \$125.97

2b Compounded monthly?

N             36
I          0.67%
PV          -\$100
PMT            \$0                    FV       \$127.02

3a What’s the present value of \$100 due in 3 years if the appropriate interest rate is 8%,
compounded annually?

N              3
I            8%
PMT            \$0
FV           \$100                  PV         \$79.38

3b Compounded monthly?

N             36
I            1%
PMT            \$0
FV           \$100                  PV         \$78.73

6 Credit card issuers must by law print their annual percentage rate (APR) on their
monthly statements. A common APR is 18%, with interest paid monthly. What is the
EFF% on such a loan?

Nominal rate      18%
Comp/year          12

Effective rate   19.56%
SECTION 2.16
SOLUTIONS TO SELF-TEST

1a Suppose a company borrowed \$1 million at a rate of 9%, simple interest, with interest paid
at the end of each month. The bank uses a 360-day year. How much interest would the firm
have to pay in a 30-day month?

Loan               \$1,000,000
Interest rate          9%
Days/year              360
Interest pd (days)      30

Interest paid        \$7,500

1b What would the interest be if the bank used a 365-day year?

Loan               \$1,000,000
Interest rate          9%
Days/year              365
Interest pd (days)      30

Interest paid      \$7,397.26

2a Suppose you deposited \$1,000 in a credit union that pays 7% with daily compounding and a
365-day year. What is the EFF%, and how much could you withdraw after 7/12 of a year?

Loan                \$1,000
Interest rate         7%
Comp/year             365                Time period (months)       7

Effective rate     7.250098%             Account value           \$1,041.67
SECTION 2.17
SOLUTIONS TO SELF-TEST

1 Suppose you borrowed \$30,000 on a student loan at a rate of 8% and now must repay it in 3
equal installments at the end of each of the next 3 years. How large would your payments be,
how much of the first payment would represent interest, how much would be principal, and what
would your ending balance be after the first year?

N             3
I           8%
PV         \$30,000
FV           \$0

PMT       -\$11,641.01

Loan Amortization Schedule, \$30,000 at 8% for 3 Years
Amount borrowed:            \$30,000
Years:                             3
Rate:                            8%
PMT:                     -\$11,641.01

Repayment
Beginning Payment     Interest  of Principal  Ending
Year      Amount (1)     (2)         (3)       (4)     Balance (5)
1        \$30,000.00 \$11,641.01 \$2,400.00 \$9,241.01 \$20,758.99
2        \$20,758.99 \$11,641.01 \$1,660.72 \$9,980.29 \$10,778.71
3        \$10,778.71 \$11,641.01 \$862.30   \$10,778.71      \$0.00

Rather than focus on Year 1 data, it was easier to just construct a full amortization schedule.
SECTION 2.18
SOLUTIONS TO SELF-TEST

3 If the nominal interest rate is 10% and the expected inflation rate is 5%, what is the expected real rate of return?

rNOM                       10%
Inflation                   5%

rr           4.7619%
d real rate of return?

```
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
 views: 131 posted: 11/30/2010 language: English pages: 33
Description: Increasing Perpetuity Calculator document sample