Case responses

Ch04ToolKit.xlsx

Home >Business & Finance homework help >Management homework help >Case responses

Chapter

Tool Kit	Chapter 4	10/27/15
The Time Value of Money
The worksheet shown below performs most of the calculations required for Chapter 4, and it was used to create many of the chapter's tables and figures. We pasted in a few dialog boxes for specific Excel functions and features; they are shown off to the right of where they were used. We encourage students to become familiar with Excel functions. It is also useful to learn how 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 (and in business courses) to prepare reports.
Although answers to the Self-Test questions within the chapter are generally quite easy and can be worked with a calculator, we also solved them with Excel as a check and also to provide some information on the solutions for students who might have questions. The tabs at the lower part of this screen take you to the solutions for self-tests in the various sections of the chapter. Even students who are not familiar with Excel should still be able to see the solution setup and then work the problem with a calculator.
4-2 Future Values
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 one 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 in the Figure below 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?
To answer this question, we show 4 methods: (1) the step-by-step using a regular calculator; (2) the formula approach using a regular calculator; (3) the financial calculator approach; and (4) the Excel approach.
Figure 4-1
Alternative Procedures for Calculating Future Values
INPUTS:
Investment = CF0 = PV =	−$100.00
Interest rate = I =	5%
No. of periods = N =	3
Time Line	Periods:	0	1	2	3
Cash flow:	−$100.00	0	0	FV = ?
1. Step-by-Step: Multiply by (1 + I) each step	$100.00 →	$105.00 →	$110.25 →	$115.76
2. Formula: FVN = PV(1+I)N	FV3 =	$100(1.05)3	=	$115.76
Inputs:	3	5	−100	0
3. Financial Calculator:	N	I/YR	PV	PMT	FV
Output:	$115.76
4. Excel Spreadsheet:	FV function:	FVN =	=FV(I,N,0,PV)
Fixed inputs:	FVN =	=FV(0.05,3,0,−100) =	$115.76
Cell references:	FVN =	=FV(C33,C34,0,C32) =	$115.76
In the Excel formula, the terms are entered in this sequence: interest, periods, 0 to indicate no periodic cash flows, and then the PV. The data can be entered as fixed numbers or, better yet, as cell references.
Figure 4-2 (just 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. The graph shows, simultaneously, the effects of time and interest rates. The data table used to create this figure is shown to the right of the figure.
Figure 4-2
Growth of $100 at Various Interest Rates and Time Periods	Future Value of $1
Periods (N)	Interest Rate (I)
115.7625	I = −20%	I = 0%	I = 5%	I = 10%	I = 20%
0	$100.00	$100.00	$100.00	$100.00	$100.00
1	$80.00	$100.00	$105.00	$110.00	$120.00
2	$64.00	$100.00	$110.25	$121.00	$144.00
3	$51.20	$100.00	$115.76	$133.10	$172.80
4	$40.96	$100.00	$121.55	$146.41	$207.36
5	$32.77	$100.00	$127.63	$161.05	$248.83
6	$26.21	$100.00	$134.01	$177.16	$298.60
7	$20.97	$100.00	$140.71	$194.87	$358.32
8	$16.78	$100.00	$147.75	$214.36	$429.98
9	$13.42	$100.00	$155.13	$235.79	$515.98
10	$10.74	$100.00	$162.89	$259.37	$619.17
4-3 Present Values
Mathematically, the present value is the opposite of the future value. Instead of compounding a present value forward to find the FV, you discount the FV back to find the PV. Thus, 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 below and assume that you will need $115.76 in 3 years. If a bank pays a guaranteed 5% interest rate each year, how much must you deposit now to have $115.76 in 3 years?
Figure 4-3
Present value of at Various Interest Rates and Time Periods
INPUTS:
Future payment = CFN = FV =	$115.76
Interest rate = I =	5.00%
No. of periods = N =	3
Time Line	Periods:	0	1	2	3
Cash flow:	PV = ?	0	0	$115.76
1. Step-by-Step:	$100.00	← $105.00	← $110.25	← $115.76
2. Formula: PV = FVN/(1+I)N	PV = $115.76/(1.05)3	=	$100.00
Inputs:	3	5	0	115.76
3. Financial Calculator:	N	I/YR	PV	PMT	FV
Output:	−$100.00
4. Excel Spreadsheet:	PV function:	PV =	=PV(I,N,0,FV)
Fixed inputs:	PV =	=PV(0.05,3,0,115.76) =	−$100.00
Cell references:	PV =	=PV(C99,C100,0,C98) =	−$100.00
In the Excel formula, the terms are entered in this sequence: interest, periods, 0 to indicate no periodic cash flows, and then the FV. The data can be entered as fixed numbers or, better yet, as cell references.
Figure 4-4 shows how the present value of $1 due in the future declines as either the interest rate or the time until receipt increases. The Data Table to the right provides the data used to draw the figure. At 0%, the PV of $1 always remains at $1, but at higher rates the value at the end of N years is lower the higher the rate, and at a given rate, the value declines the larger the value of N.
Figure 4-4
Present Value of $100 at Various Interest Rates and Time Periods
Present Value of $1
Periods (N)	Interest Rate (I)
86.3838	I = 0%	I = 5%	I = 10%	I = 20%
0	$100.0000	$100.0000	$100.0000	$100.0000
4	$100.0000	$82.2702	$68.3013	$48.2253
8	$100.0000	$67.6839	$46.6507	$23.2568
12	$100.0000	$55.6837	$31.8631	$11.2157
16	$100.0000	$45.8112	$21.7629	$5.4088
20	$100.0000	$37.6889	$14.8644	$2.6084
24	$100.0000	$31.0068	$10.1526	$1.2579
28	$100.0000	$25.5094	$6.9343	$0.6066
32	$100.0000	$20.9866	$4.7362	$0.2926
36	$100.0000	$17.2657	$3.2349	$0.1411
40	$100.0000	$14.2046	$2.2095	$0.0680
4-4 Finding the Interest Rate, I
Previously, we solved the basic equation 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.
INPUTS:
Present value (PV)	-$100.00
Future value (FV)	$150.00
No. of years (N)	10
OUTPUT:
Interest rate (I)	= RATE(N,0,PV,FV)
Interest rate (I)	4.14%
4-5 Finding the Number of Years, N
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%.
INPUTS:
Present value (PV)	-$500,000
Future value (FV)	$1,000,000
Interest rate (I)	4.50%
OUTPUT:
No. of years (N)	=NPER(I,0,PV,FV)
No. of years (N)	15.7473
4-6 Perpetuities
Perpetuities are securities that promise to make payments forever. The tale below shows how the present value of an ordinary annuity changes as the number of payments increases. 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.
Payment (PMT)	$25
Interest rate (I)	5.2%
Number of Periods	PV of Ordinary Annuity
1	$23.76
2	$46.35
3	$67.83
4	$88.24
5	$107.64
10	$191.18
15	$256.02
20	$306.34
25	$345.39
30	$375.70
40	$417.48
50	$442.65
60	$457.81
70	$466.94
80	$472.44
90	$475.75
100	$477.75
200	$480.75
500	$480.77
Notice in the table above that the PV of an ordinary annuity increases as the number of payments increases, as you would expect. However, the PV appears to begin leveling off. This is because the present value of a cash flow far in the future is very small and approaches zero as the time of the cash flow goes to infinity. In fact, the present value of a perpetuity can be found with a simple formula: Value = PMT / I .
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%
Value (PV): $25 / 0.052 =	$480.77
4-7 Annuities
An annuity is a series of equal cash flows. The cash flows can be at the end of the period or the beginning, but they must not change.
4-8 Future Value of an Ordinary Annuity
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 4-5
Summary: Future Value of an Ordinary Annuity
INPUTS:
Payment amount = PMT =	−$100
Interest rate = I =	5.00%
No. of periods = N =	3
1. Step-by-Step:	Periods:	0	1	2	3
Cash flow:	−$100	−$100	−$100
↓	↓	↓
↓	↓	$100.00
Multiply each payment by	↓	└ → →	$105.00
(1+I)N-t and sum these FVs to	└ → →	→ → → → → → →	$110.25
find FVAN:	$315.25
2. Formula:
FVAN	$315.25
Inputs:	3	5	0	−100
3. Financial Calculator:	N	I/YR	PV	PMT	FV
Output:	$315.25
4. Excel Spreadsheet:	FV function:	FVAN =	=FV(I,N,PMT,PV)
Fixed inputs:	FVAN =	=FV(0.05,3,-100,0) =	$315.25
Cell references:	FVAN =	=FV(C245,C246,C244,0) =	$315.25
4-9 Future Value of an Annuity Due
An annuity due also has regular, periodic payments, but unlike an ordinary annuity, the payments occur at the beginning of each period.
Figure Not In Textbook
Summary: Future Value of an Annuity Due
INPUTS:
Payment amount = PMT =	−$100
Interest rate = I =	5.00%
No. of periods = N =	3
1. Step-by-Step:	Periods:	0	1	2	3
Cash flow:	−$100	−$100	−$100
↓	↓	↓
↓	↓	└ → →	$105.00
Multiply each payment by	↓	└ → →	→ → → → → → →	$110.25
(1+I)N-t and sum these FVs to	└ → →	→ → → → → → →	→ → → → → → →	$115.76
find FVAN:	$331.01
2. Formula:
FVAN =	$331.01
Inputs: Mode = BEG	3	5	0	−100
3. Financial Calculator:	N	I/YR	PV	PMT	FV
Output:	$331.01
4. Excel Spreadsheet:	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(C280,C281,C279,0,1) =	$331.01
4-10 Present Value of Ordinary Annuities and Annuities Due
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 4-6
Summary: Present Value of an Ordinary Annuity
INPUTS:
Payment amount = PMT =	−$100
Interest rate = I =	5.00%
No. of periods = N =	3
1. Step-by-Step:	Periods:	0	1	2	3
Cash flow:	−$100	−$100	−$100
↓	↓	↓
$95.24	← ← ┘	↓	↓
Divide each payment by	$90.70	←←←←←←	← ← ┘	↓
(1+I)t and sum these PVs to	$86.38	←←←←←←	←←←←←←	← ← ┘
find PVAN:	$272.32
2. Formula:
PVAN	$272.32
Inputs:	3	5	−100	0
3. Financial Calculator:	N	I	PV	PMT	FV
Output:	272.32
4. Excel Spreadsheet:	PV function:	PVAN =	=PV(I,N,PMT,FV)
Fixed inputs:	PVAN =	=PV(0.05,3,-100,0) =	$272.32
Cell references:	PVAN =	=PV(C315,C316,C314,0) =	$272.32
PRESENT VALUE OF AN ANNUITY DUE (this table is 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.
Figure Not In Textbook
Summary: Present Value of an Annuity Due
INPUTS:
Payment amount = PMT =	−$100
Interest rate = I =	5.00%
No. of periods = N =	3
1. Step-by-Step:	Periods:	0	1	2	3
Cash flow:	−$100	−$100	−$100
↓	↓	↓
$100.00	↓	↓
Divide each payment by	$95.24	← ← ⤶	↓
(1+I)t and sum these PVs to	$90.70	←←←←←←	← ← ⤶
find PVAN:	$285.94
2. Formula:
PVAN =	=	$285.94
Inputs: Mode = BEG	3	5	−$100	0
3. Financial Calculator:	N	I	PV	PMT	FV
Output:	285.94
4. Excel Spreadsheet:	PV function:	PVAN =	=PV(I,N,PMT,FV)
Fixed inputs:	PVAN =	=PV(0.05,3,-100,0,1) =	$285.94
Cell references:	PVAN =	=PV(C351,C352,C350,0,1) =	$285.94
4-11 Finding Annuity Payments, Periods, and Interest Rates
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. How much must we save in each of the 5 years, assuming (a) end-of-year payments and (b) beginning-of-year payments?
No. of years (N)	5
Interest rate (I)	6%
Present value (PV)	$0
Future value (FV)	$10,000
a. END MODE	b. BEGIN MODE
Payment (PMT)	-$1,773.96	Payment (PMT)	-$1,673.55
=PMT(I,N,PV,FV)	=PMT(I,N,PV,FV,Type=1)
FINDING N
Suppose you decide to make end-of-year deposits, but you can only save $1,200 per year. Again assume that you would earn 6%. How long would it take you to reach your $10,000 goal?
BEGIN MODE
Interest rate (I)	6%	6%
Present value (PV)	$0	$0
Payment (PMT)	-$1,200	-$1,200
Future value (FV)	$10,000	$10,000
No. of years (N)	6.96	6.63
=NPER(I,PMT,PV,FV,0)	=NPER(I,PMT,PV,FV,1)
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?
BEGIN MODE
No. of years (N)	5	5
Present value (PV)	$0	$0
Payment (PMT)	-$1,200	-$1,200
Future value (FV)	$10,000	$10,000
Interest rate (I)	25.78%	17.54%
=RATE(N,PMT,PV,FV,0)	=RATE(N,PMT,PV,FV,1)
4-12 Uneven, or Irregular, Cash Flows
First, consider a security that pays $100 for 5 years plus a lump sum of $1,000 at the end of the 5th year. We can find the PV in several ways: (1) With a financial calculator using the step-by-step approach, or by finding the PV of the annuity plus the PV of the final $1,000 and then summing these two values, or by using the calculator's cash flow register, or (2) with Excel, using either the PV or the NPV function. We illustrate the step-by-step and the two Excel approaches below.
Figure Not Shown in Textbook
Present Value of an Annuity Plus Additional Final Payment
INPUTS:
Interest rate = I =	12.00%
No. of periods = N =	5
Payment amount = PMT =	$100
Future value = FV =	$1,000
1. Step-by-Step:
Periods:	0	1	2	3	4	5
PMT CFs:	$100.00	$100.00	$100.00	$100.00	$100.00
Additional CF:	$1,000.00
Total CFs:	$100.00	$100.00	$100.00	$100.00	$1,100.00
PVs of the CFs:	$89.29	$79.72	$71.18	$63.55	$624.17
PV of the CF Stream = Sum of the Individual PVs =	$927.90
2. Financial Calculator:
You could enter the cash flows into the cash flow register of a financial calculator, enter I/YR, and then press the NPV key to find the answer.	$927.90
3. Excel Spreadsheet:	PV Function:	PVAN =	=PV(I,N,PMT,FV)
PV fixed inputs:	PVAN =	=PV(0.12,5,100,1000) =	−$927.90
PV cell references:	PVAN =	=PV(C432,C433,C434,C435) =	−$927.90
NPV Function:	NPV =	=NPV(I,CFs)
NPV fixed inputs:	NPV =	=NPV(0.12,100,100,100,100,1100) =	$927.90
NPV cell references:	NPV =	=NPV(C432,C441:G441) =	$927.90
The Excel formula ignores the initial cash flow (in Year 0). When entering a cash flow range, Excel assumes that the first value occurs at the end of the first year. As we will see later, if there is an initial cash flow, it must be added separately to complete the NPV formula result. Notice too that you can enter cash flows one-by-one, but if the cash flows appear in consecutive cells, you can enter the cell range, as we did here.
Now consider an irregular cash flow stream, where the CFs can take on any value.
Figure 4-7
Present Value of an Irregular Cash Flow Stream
INPUTS:
Interest rate = I =	12%
1. Step-by-Step:
Periods:	0	1	2	3	4	5
Cash flow:	$0.00	$100.00	$300.00	$300.00	$300.00	$500.00
PVs of the CFs:	$89.29	$239.16	$213.53	$190.66	$283.71
PV of the Irregular CF Stream = Sum of the Individual PVs =	$1,016.35
2. Calculator:	You could enter the cash flows into the cash flow register of a financial calculator, enter I/YR, and then press the NPV key to find the answer.	$1,016.35
3. Excel Spreadsheet:	NPV function:	NPV =	= NPV(I,CFs)
Fixed inputs:	NPV =	=NPV(0.12,100,300,300,300,500)	$1,016.35
Cell references:	NPV =	=NPV(C467,C471:G471)	$1,016.35
The Excel formula ignores the initial cash flow (in Year 0). When entering a cash flow range, Excel assumes that the first value occurs at the end of the first year. As we will see later, if there is an initial cash flow, it must be added separately to complete the NPV formula result. Notice too that you can enter cash flows one-by-one, but if the cash flows appear in consecutive cells, you can enter the cell range, as we did here.
4-13 Future Value of an Uneven Cash Flow Stream
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, although financial calculators do have this function. One way around this is to solve for the NPV and then find the FV of this amount at the end of the cash flow stream.
Figure 4-8
Future Value of an Irregular Cash Flow Stream
INPUTS:
Interest rate = I =	12%
1. Step-by-Step:
Periods:	0	1	2	3	4	5
Cash flow:	$0.00	$100.00	$300.00	$300.00	$300.00	$500.00
FVs of the CFs:	$157.35	$421.48	$376.32	$336.00	$500.00
FV of the Irregular CF Stream = Sum of the Individual FVs =	$1,791.15
2. Calculator:	You could enter the cash flows into the cash flow register of a financial calculator, enter I/YR, and then press the NFV key to find the answer.	$1,791.15
3. Excel Spreadsheet	Step 1. Find NPV:	=NPV(C499,C503:G503)	$1,016.35
Step 2. Compound NPV to find NFV:	=FV(C499,G502,0,-G511)	$1,791.15
4-14 Solving for I with Irregular Cash Flows
Assume that a bond will pay $100 at the end of each of the next 5 years, plus an additional $1,000 at the end of the 5th year. The cost of the bond is $927.90. What rate of return would you earn if you bought the bond?
You could find the rate of return using Excel's IRR (for "internal rate of return") function or its RATE function, as shown below. The RATE function deals with situations where we have an annuity plus a final lump sum. The IRR function deals with any cash flow pattern, and it is easier to use. You could enter a guess as to the IRR, but this is not necessary.
Finding the Interest Rate, Annuity Plus Lump Sum
INPUTS:
Annuity pmts	$100
Future lump sum	$1,000
Periods:	0	1	2	3	4	5
Cash Flows:	-$927.90	$100	$100	$100	$100	$1,100
Excel Function Approach:	Cell references:	IRR =	=IRR(B532:G532)	12.00%
Cell references:	RATE =	=RATE(G531,B528,B532,B529)	12.00%
The IRR function is used to find the rate of return on capital budgeting projects, where the firm makes a capital expenditure and then expects to receive a series of cash inflows. Figure 4-9 illustrates this calculation. Note that the IRR function can be used even if one of the post-investment cash flows is negative. Change the 4th year CF from $300 to -$100 and see the IRR drop to 2.90%. Then change it back to $300.
Figure 4-9
IRR of an Uneven Cash Flow Stream
Periods:	0	1	2	3	4	5
Cash flows:	-$1,000	$100	$300	$300	$300	$500
1. Calculator:	You could enter the cash flows into the cash flow register of a financial calculator and then press the IRR key to find the answer.	12.55%
2. Excel IRR Function:	Cell references:	IRR =	=IRR(B546:G546)	12.55%
4-15 Semiannual and Other Compounding Periods
If $100 is invested in an account at an annual nominal interest rate of 12% for 1 year, what are the effective interest rates and the future values based on annual, semiannual, quarterly, monthly and daily compounding?
When you work this problem, recognize that with more compounding periods, you receive interest sooner than with annual compounding, so you will earn more "interest on interest." Therefore, you will end up with more money, and the effective interest rate will be higher, than with annual compounding.
Nominal annual rate =	12%
Amount invested =	$100
Number of years =	1
Figure 4-10
Effect on $100 of Compounding More Frequently than Once a Year
Frequency of Compounding	Nominal Annual Rate	Number of Periods per Year (M)a	Periodic Interest Rate (IPER)	Effective Annual Rate (EFF%)b	Future Valuec	Percentage Increase in FV
Annual	12%	1	12.0000%	12.0000%	$112.00
Semiannual	12%	2	6.0000%	12.3600%	$112.36	0.32%
Quarterly	12%	4	3.0000%	12.5509%	$112.55	0.17%
Monthly	12%	12	1.0000%	12.6825%	$112.68	0.12%
Daily	12%	365	0.0329%	12.7475%	$112.75	0.06%
a We used 365 days per year in the calculations.
bThe EFF% is calculated as (1 + IPER)M.
cThe Future value is calculated as $100(1 + EFF%).
ADD-ON INTEREST (Box: Truth in Lending)
Cost of Credit based on "Add-On" Interest. This table is not in the text, but the
procedure is discussed in the "Truth in Lending Box". This procedure is commonly
used by retailers, auto dealers, and many other lenders. The calculator solution is
explained in the text and also below. The Excel solution is explained just below.
Amount borrowed = Cost of TV. Disregards the advanced payment, handled separately.	$3,000.00
Nominal rate	8.00%
Amount of interest = interest rate x Amt borrowed	$240.00
Stated loan size = Amt borrowed + Interest	$3,240.00
Number of payments	12
Payment/month	-$270.00
0	1	2	3	4	5	6	7	8	9	10	11	12
Amt borrowed	$3,000.00
Monthly Pmts	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00
CF time line	$2,730.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	-$270.00	$0.00
IRR = periodic rate:	=IRR(B598:M598) =	1.4313%
APR rate:	=E600*G592 =	17.1758%
EFF%:	=(1+E600)^G592-1 =	18.5945%
Before the Truth in Lending Act, auto dealers, TV dealers, and even student loan officers would
make add-on loans and just tell customers about the 8% stated rate. After 1968, such lenders were
required to also report the much higher APR rate. But lenders are still not required to report the even
higher EFF%, which is the "true" rate that borrowers should base decisions on.
We showed the cash flows above as a"horizontal" time line, but it's easier to fit the analysis on the
screen using a vertical time line, as shown below. The calculations are identical, but the vertical setup
is better from a presentation standpoint if we have more cash flows than can be shown on the screen.
Periods	Borrowed	Payments	Monthly CFs
0	$3,000.00	-$270.00	$2,730.00
1	-$270.00	-$270.00
2	-$270.00	-$270.00
3	-$270.00	-$270.00
4	-$270.00	-$270.00
5	-$270.00	-$270.00
6	-$270.00	-$270.00
7	-$270.00	-$270.00
8	-$270.00	-$270.00
9	-$270.00	-$270.00
10	-$270.00	-$270.00
11	-$270.00	-$270.00
12	$0.00	$0.00
IRR = periodic rate:	1.4313%
APR rate:	17.176%
EFF%:	18.595%
To solve the problem with a calculator, first set the machine to BEGIN mode, then enter N = 12, PV =
3000, and PMT = -270. When you press the I/YR key to get the periodic rate, 1.431313, which you can
use to find the APR and EFF% as we did above.
4-16 Fractional Time Periods
Suppose you deposited $100 in a bank that pays a nominal rate of 10%, compounded daily, based on a 365-day year. How much would you have after 9 months?
It depends on whether interest is compounded or is simple interest.
Inputs
PV =	$100
INOM =	10%
Number of days in year =	365
Number of months interest charged =	9
Compounded Interest Results
IPER =	0.02740%
Number of days interest charged (rounded up) =	274
Ending amount =	$107.79
Interest owed =	$7.79
Simple Interest Results
Number of days interest charged =	274
Number of years interest charged =	0.7506849
Method 1: Interest owed= amount borrowed x annual rate x number of years =	$7.51
Method 2: Interest owed= amount borrowed x daily rate x number of days =	$7.51
4-17 Amortized Loans
If a loan is to be repaid in equal amounts on a monthly, quarterly, or annual basis it is called an amortized loan.
The figure below illustrates the amortization process. A company borrows $100,000, with the loan 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.
With a calculator, we solve for the required payment, then we construct an amortization table as shown in The figure below. It is far easier, and less prone to errors, to construct the amortization table with Excel, as we do here.
Figure 4-11
Loan Amortization Schedule, $100,000 at 6% for 5 Years
INPUTS:
Amount borrowed:	$100,000
Years:	5
Rate:	6%
Intermediate calculation:
PMT:	$23,739.64	=PMT(C678,C677,−C676)
Year	Beginning Amount (1)	Payment (2)	Interesta (3)	Repayment of Principalb (2) − (3) = (4)	Ending Balance (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 the $23,739.64 annual payment minus the interest charge for the year, $17,739.64 for Year 1.
Consider a 30-year home mortgage of $250,000 at an annual rate of 6%. How much interest will the borrower pay over the life of the loan?
INPUTS:
Amount borrowed:	$250,000
Years:	30
Rate:	6%
N =	360
I/YR =	0.5%
PV =	$250,000
FV =	$0
PMT =	−$1,498.8763	Using the PMT function.
Total payments =	$539,595.47
Total interest =	$289,595
How much interest does the borrower pay in the first year?
N =	348
I/YR =	0.5%
PMT =	−$1,498.88	We are rounding to 2 decimal places.
FV =	$0
PV =	$246,930.58	Using the PMT function and rounding to 2 decimal places.
Total payments in year =	$17,986.56
Principal paid in year =	$3,069.42
Interest paid in year =	$14,917.14
Suppose we consider a 15-year mortgage at the same interest rate. How much interest will the borrower pay over the life of the loan?
N =	180
I/YR =	0.5%
PV =	$250,000
FV =	$0
PMT =	−$2,109.6421	Using the PMT function.
Total payments =	$379,735.57
Total interest =	$129,736
4-18 Growing Annuities
Example 1. A 65-year-old retiree expects to live for 20 more years, currently has $1,000,000 of savings, expects to earn a 6% rate on his or her money, and expects inflation to average 3%. How much can he or she withdraw at the beginning of each year and keep the withdrawals constant in real terms, i.e., growing at the same rate as inflation and thus enabling him or her to maintain a constant standard of living?
Inputs
Number of years =	20
Nominal interest rate, rNOM =	6%
Available to invest = Portfolio =	$1,000,000
Inflation rate =	3%
Initial withdrawal (guess) =	$50,000
Withdrawal at beginning or end?	Beginning
Step 1: Set up an "Amortization Table" to show exactly what's happening. We begin with $1 million. But we immediately make the first withdrawal, hence have less than $1 million to invest. We don't know how much we can withdraw initially, so we make a "guess" of $50,000. We subtract the $50,000 from the initial portfolio and get $950,000, which is invested at 6% and thus earns $57,000. The earnings are added to the beginning balance, less the withdrawal, to produce the ending balance, which is carried forward to create the next beginning balance. This process is continued for 20 years.
We want to end up with a $0.00 ending balance. With the $50,000 initial withdrawal, we see that the ending balance is greater than zero. Therefore, we should make a larger initial withdrawal. We could just go through a series of trials and errors until we found an initial withdrawal that produced the zero ending balance. The amount that does the trick is $64,786.87708. Replace the $50,000 with 64786.87708 to prove that this value "works."
As you might guess, there are two much easier ways to find the initial withdrawal amount: (1) Use Excel's Goal Seek function, or (2) use an equation. We explain those procedures below, and we also graph the results. We see that the withdrawals rise every year with inflation, earnings decline, and the balance declines faster and faster, as the withdrawals increase and the earnings decline.
Withdrawal	Beginning Balance	BOY: Amount Withdrawn	Investable Funds	Earnings	Ending Balance
1	$1,000,000.00	$64,786.88	$935,213.12	$56,112.79	$991,325.91
2	$991,325.91	$66,730.48	$924,595.43	$55,475.73	$980,071.15
3	$980,071.15	$68,732.40	$911,338.75	$54,680.33	$966,019.08
4	$966,019.08	$70,794.37	$895,224.71	$53,713.48	$948,938.19
5	$948,938.19	$72,918.20	$876,019.99	$52,561.20	$928,581.19
6	$928,581.19	$75,105.75	$853,475.44	$51,208.53	$904,683.97
7	$904,683.97	$77,358.92	$827,325.05	$49,639.50	$876,964.55
8	$876,964.55	$79,679.69	$797,284.87	$47,837.09	$845,121.96
9	$845,121.96	$82,070.08	$763,051.88	$45,783.11	$808,835.00
10	$808,835.00	$84,532.18	$724,302.82	$43,458.17	$767,760.98
11	$767,760.98	$87,068.15	$680,692.84	$40,841.57	$721,534.41
12	$721,534.41	$89,680.19	$631,854.22	$37,911.25	$669,765.47
13	$669,765.47	$92,370.60	$577,394.88	$34,643.69	$612,038.57
14	$612,038.57	$95,141.71	$516,896.86	$31,013.81	$547,910.67
15	$547,910.67	$97,995.96	$449,914.70	$26,994.88	$476,909.59
16	$476,909.59	$100,935.84	$375,973.74	$22,558.42	$398,532.17
17	$398,532.17	$103,963.92	$294,568.25	$17,674.09	$312,242.34
18	$312,242.34	$107,082.84	$205,159.51	$12,309.57	$217,469.08
19	$217,469.08	$110,295.32	$107,173.76	$6,430.43	$113,604.18
20	$113,604.18	$113,604.18	-$0.00	-$0.00	-$0.00
Using Goal Seek:	1. Put the pointer on the orange cell for the Ending Balance after the 20th withdrawal.
2. Click Data, What-If-Analysis, Goal Seek to get a dialog box, which you then fill out as shown to the right.
3. You will be at the "Set cell" because you put the pointer there initially.
4. Go down to the "To value to" cell. You want to get 0 as the ending balance, so enter 0 here.
5. Now move down to the "By changing cell" box, then click on the yellow cell with the Year 1 withdrawal to select it.
6. Now click OK, and the initial withdrawal will change to $64,786.88, and the final balance will go to $0.00. You could increase the decimals shown to see the extra digits Excel calculated.
Calculator:	Step 1: Find the real rate of return, rr.
rr =	(1+rNOM)/(1 + inflation) - 1	Here is a formula for the present value of a growing annuity:
=	(1.06)/(1.03) - 1 =	0.0291262136	PVIFGADue = [1 – [(1 + g)/(1 + rNOM)]N] [(1 + rNOM)/(rNOM − g)]
rr =	2.9126214%	PVIFGADue =	15.4352246178
PMT =	PV / PVIFGADue =	$64,786.88
Step 2: Use the PMT function in Excel or a calculator to find the initial amount to be withdrawn. Be
sure to set the calculator to BEGIN mode, and make a similar adjustment to the Excel function.
N=	20
I= rr =	2.9126214%
PV =	-1,000,000
PMT =	$64,786.88	This is consistent with the value found using Goal Seek.
If the first withdrawal occurs at the end rather than the beginning of the first year, then the amount of investable funds during each year will be somewhat larger, and the initial withdrawal to leave a zero final balance will also be somewhat larger. We can modify the table by making the first withdrawal at the end of the year and then using Goal Seek to find the initial withdrawal, which is slightly higher than the case of the annuity due because the original funds earned interest for a year prior to the first withdrawal.
Inputs
Number of years =	20
Nominal interest rate, rNOM =	6%
Available to invest = Portfolio =	$1,000,000
Inflation rate =	3%
Initial withdrawal (guess) =	$50,000
Withdrawal at beginning or end?	End
Beginning Balance	EOY: Amount Withdrawn	Investable Funds	Earnings	Ending Balance
1	$1,000,000.00	$68,674.09	$1,000,000.00	$60,000.00	$991,325.91
2	$991,325.91	$70,734.31	$991,325.91	$59,479.55	$980,071.15
3	$980,071.15	$72,856.34	$980,071.15	$58,804.27	$966,019.08
4	$966,019.08	$75,042.03	$966,019.08	$57,961.14	$948,938.19
5	$948,938.19	$77,293.29	$948,938.19	$56,936.29	$928,581.19
6	$928,581.19	$79,612.09	$928,581.19	$55,714.87	$904,683.97
7	$904,683.97	$82,000.45	$904,683.97	$54,281.04	$876,964.55
8	$876,964.55	$84,460.47	$876,964.55	$52,617.87	$845,121.96
9	$845,121.96	$86,994.28	$845,121.96	$50,707.32	$808,835.00
10	$808,835.00	$89,604.11	$808,835.00	$48,530.10	$767,760.98
11	$767,760.98	$92,292.23	$767,760.98	$46,065.66	$721,534.41
12	$721,534.41	$95,061.00	$721,534.41	$43,292.06	$669,765.47
13	$669,765.47	$97,912.83	$669,765.47	$40,185.93	$612,038.57
14	$612,038.57	$100,850.22	$612,038.57	$36,722.31	$547,910.67
15	$547,910.67	$103,875.72	$547,910.67	$32,874.64	$476,909.59
16	$476,909.59	$106,991.99	$476,909.59	$28,614.58	$398,532.17
17	$398,532.17	$110,201.75	$398,532.17	$23,911.93	$312,242.34
18	$312,242.34	$113,507.81	$312,242.34	$18,734.54	$217,469.08
19	$217,469.08	$116,913.04	$217,469.08	$13,048.14	$113,604.18
20	$113,604.18	$120,420.43	$113,604.18	$6,816.25	$0.00
A modified version of the formula could also be used to determine the initial withdrawal:
rr =	(1+rNOM)/(1 + inflation) - 1
rr =	2.9126214%
Now use the PMT function in Excel or a calculator to find the initial amount to be withdrawn, assuming payments at the end of the year.
N=	20
I= rr =	2.9126214%
PV =	1,000,000
PMT =	$66,673.87
Adjusted PMT =	$68,674.09	= PMT(1+ Inflation). The adjustment accounts for Year 1 inflation.
Example 2, Growing Annuities: Initial deposit to accumulate a given sum. You need to accumulate $100,000 in 10 years. You plan to make an initial deposit today, then make 9 more deposits at the beginning of the next 9 years, but with the deposits increasing at the inflation rate. You expect to earn 6% on your funds, and you expect a 2% inflation rate. How large must your initial deposit be to enable you to reach your $100,000 target?
We can set up a table with an arbitrary initial deposit that grows at the inflation rate and is then compounded at the nominal rate for (N - t) years. The sum of the compounded amounts should total to $100,000. With an arbitrary initial amount the ending amount is not likely to be $100,000, so we use goal seek as shown in the completed dialog box to find the correct initial deposit.
Inputs:
Years	10
Amount Needed (FV)	$100,000
Nominal rate earned on account	6.00%
Inflation	2.00%
Beginning or End?	Beginning
Use Goal Seek in the following table to determine the initial deposit. Start with any value for BOY payment at time zero, then use Goal Seek to set the final balance to the target by changing the BOY t=0 payment.
BOY Payment
Period (t)	Initial(1+I)^t	Compounded value
0	$6,598.87	$11,817.57
1	$6,730.85	$11,371.62
2	$6,865.46	$10,942.51
3	$7,002.77	$10,529.58
4	$7,142.83	$10,132.24
5	$7,285.68	$9,749.89
6	$7,431.40	$9,381.97
7	$7,580.03	$9,027.93
8	$7,731.63	$8,687.26
9	$7,886.26	$8,359.43
N =	10	$0.00	$100,000.00
Calculator approach:
Find the real rate: rr = (1+rNOM)/(1 + inflation) - 1 =	3.921569%
Find the real required future amount, discounted at the inflation rate. This is our constant dollar future target: Target real FV = (Nominal FV)/(1 + Inflation)N =	$82,034.83
Use a calculator or the Excel PMT function to find the initial payment. The PV=0, FV=82034.83, rate=3.921569, and set to Beginning mode.	$6,598.87
This is consistent with the Goal Seek solution.

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 80 64.000000000000014 51.20000000000001 40.960000000000022 32.768000000000022 26.214400000000015 20.971520000000016 16.777216000000013 13.421772800000012 10.737418240000011 I = 0%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 110.00000000000001 121.00000000000001 133.10000000000005 146.41000000000005 161.05100000000004 177.15610000000009 194.87171000000012 214.35888100000011 235.79476910000014 259.37424601000021 I = 20%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 4 8 12 16 20 24 28 32 36 40 100 82.2702474791882 67.683936202868722 55.683741817755951 45.811152199140025 37.688948287300057 31.00679102826502 25.509363714308357 20.986616658127716 17.265741462150206 14.204568230027784 I = 10%

0 4 8 12 16 20 24 28 32 36 40 100 68.301345536507057 46.650738020973314 31.863081771035656 21.762913579014853 14.864362802414348 10.152559799477048 6.9343349494413253 4.7362440744766907 3.2349184307606649 2.2094928152179936 I = 20%

I = 20%

0 4 8 12 16 20 24 28 32 36 40 100 48.22530864197531 23.256803936137786 11.215665478461512 5.4087892932395416 2.6084053304588837 1.2579115212475329 0.60663171356458956 0.29255001618662696 0.1410831482381496 6.8037783679663191E-2

Years

FV of $100 After N Years

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

I = 20%

Years

FV of $100 After N Years

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

I = 20%

Years

FV of $100 After N Years

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

I = 20%

Years

FV of $100 After N Years

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

I = 20%

Years

FV of $100 After N Years

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

I = 20%

Years

FV of $100 After N Years

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

I = 20%

Years

FV of $100 After N Years

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

I = 20%

Years

FV of $100 After N Years

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

I = 20%

Years

FV of $100 After N Years

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

I = 20%

Years

FV of $100 After N Years

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

I = 20%

Years

FV of $100 After N Years

I = −20%

0 1 2 3 4 5 6 7 8 9 10 100 100 100 100 100 100 100 100 100 100 100 I = 5%

0 1 2 3 4 5 6 7 8 9 10 100 105 110.25 115.76250000000002 121.550625 127.62815625000002 134.0095640625 140.71004226562502 147.74554437890626 155.13282159785157 162.88946267774415 I = 10%

0 1 2 3 4 5 6 7 8 9 10 100 120 144 172.8 207.35999999999999 248.83199999999999 298.59839999999997 358.31807999999995 429.98169599999994 515.97803519999991 619.17364223999994

Years

FV of $100 After N Years

I = 0%

0 4 8 12 16 20 24 28 32 36 40 100 100 100 100 100 100 100 100 100 100 100 I = 5%

I = 20%

Years

FV of $100 After N Years

4-2

SECTION 4-2
SOLUTIONS TO SELF-TEST
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
Suppose you currently have $2,000 and plan to purchase a 3-year certificate of deposit (CD) that pays 4% 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
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
A company’s sales in 2009 were $100 million. If sales grow at 8%, what will they be 10 years later?
N	10
I	8%
PV ($M)	$100
PMT	$0	FV ($M)	$215.89
What would they be if they decline by 8% per year for 10 years?
N	10
I	-8%
PV ($M)	$100
PMT	$0	FV ($M)	$43.44
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
What would FV be if the growth rate were 10%?
N	100
I	10%
PV	$1
PMT	$0	FV	$13,780.61

4-3

SECTION 4-3
SOLUTIONS TO SELF-TEST
Suppose a risk-free bond promises to pay $2,249.73 in 3 years. If the going risk-free interest rate is 4%, how much is the bond worth today?
N	3
I	4%
PMT	$0
FV	$2,249.73	PV	$2,000.00
How would your answer change if the bond matured in 5 rather than 3 years?
N	5
I	4%
PMT	$0
FV	$2,249.73	PV	$1,849.11
If the risk-free interest rate is 6% rather than 4%, how much is the 5-year bond worth today?
N	5
I	6%
PMT	$0
FV	$2,249.73	PV	$1,681.13
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
What if the discount rate were 20%?
N	100
I	20%
PMT	$0
FV	$1,000,000	PV	$0.0121

4-4

SECTION 4-4
SOLUTIONS TO SELF-TEST
Suppose you can buy a U.S. Treasury bond which makes no payments until the bond matures 10 years from now, at which time it will pay you $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%
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%
What rate would you earn if you could buy the bond for $600?
N	10
PMT	$0
PV	$600.00
FV	$1,000	I =	5.24%
Microsoft earned $0.33 per share in 1997. Fourteen years later, in 2011, it earned $2.75. What was the growth rate in Microsoft’s earnings per share (EPS) over the 14-year period?
N	14
PMT	$0
PV	$0.33
FV	$2.75	I =	16.35%
If EPS in 2011 had been $2.00 rather than $2.75 what would the growth rate have been?
N	14
PMT	$0
PV	$0.33
FV	$2.00	I =	13.73%

4-5

SECTION 4-5
SOLUTIONS TO SELF-TEST
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
How long would it take if the rate were 10%?
I	10%
PMT	$0
PV	$1,000
FV	$2,000	N	7.27
A company's 2013 earnings per share were $2.75, and its growth rate during the prior 10 years was 16.35% per year. If that growth rate were maintained, how long would it take for EPS to double?
I	16.35%
PMT	$0
PV	$2.75
FV	$5.50	N	4.58

4-6

SECTION 4-6
SOLUTIONS TO SELF-TEST
What is 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
What would the value be if the perpetuity began its payments immediately?
The perpetuity formula values payments 1 through infinity. If a payment is to be received immediately, it must be added to the formula result.
PMT	£1,000
I	5%	PV	£21,000

4-8

SECTION 4-8
SOLUTIONS TO SELF-TEST
For an ordinary annuity with 5 annual payments of $100 and a 10% interest rate, for how many years will the 1st payment earn interest, and what is the compounded value of this payment at the end?
Annuity Data	1st Payment Data
N	5	4
I	10%	10%	Years of int	4
PMT	-$100	$0
PV	$0	-$100	Payment FV	$146.41
Answer this same question for the 5th payment.
Annuity Data	5th Payment Data
N	5	0
I	10%	10%	Years of int	0
PMT	-$100	0
PV	$0	-$100	Payment FV	$100.00
Assume that you plan to buy a condo 5 years from now, and you estimate that you can save $2,500 per year to get a down payment. You plan to deposit the money in a bank that pays 4% interest, and you will make the first deposit at the end of this year. How much will you have after 5 years?
N	5
I	4%
PMT	-$2,500
PV	$0	FV	$13,540.81
How would your answer change if the bank's interest rate were increased to 6%, or decreased 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

4-9

SECTION 4-9
SOLUTIONS TO SELF-TEST
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 being made immediately and deposited in a bank that pays 4%. How much will you have after 5 years?
BEGIN MODE
N	5
I	4%
PV	$0
PMT	-$2,500	FV	$14,082.44
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

4-10

SECTION 4-10
SOLUTIONS TO SELF-TEST
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
What would the PVA be if the interest rate were 4%?
N	10
I	4%
PMT	-$100
FV	$0	PV	$811.09
What if the interest rate were 0%?
N	10
I	0%
PMT	-$100
FV	$0	PV	$1,000.00
What would the PVAs be if we were dealing with annuities due?
Part a	Part b	Part c
BEGIN MODE	BEGIN MODE	BEGIN MODE
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
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 equally risky investments. What is the most you should pay for the annuity?
N	10
I	8%
PMT	-$100
FV	$0	PV	$671.01
If the payments began immediately, then how much would the annuity be worth?
BEGIN MODE
N	10
I	8%
PMT	-$100
FV	$0	PV	$724.69

4-11

SECTION 4-11
SOLUTIONS TO SELF-TEST
Suppose you inherited $100,000 and invested it at 7% per year. How large of a withdrawal could you make at the end of each of the next 10 years and end up with zero?
N	10
I	7%
PV	$100,000
FV	$0	PMT	-$14,237.75
How would your answer change if you made withdrawals at the beginning of each year?
BEGIN MODE
N	10
I	7%
PV	$100,000
FV	$0	PMT	-$13,306.31
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
How long would they last if you earned 0%?
I	0.0%
PV	$100,000
PMT	-$10,000
FV	$0	N	10.0
How long would they last if you earned the 7% but limited your withdrawals to $7,000 per year?
I	7.0%
PV	$100,000	* This result means that with $7,000 withdrawals, you would never exhaust the funds.
PMT	-$7,000
FV	$0	N	ERROR:#NUM!
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%
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 to him, what rate of return will your mother’s friend earn on the investment?
BEGIN MODE
N	10
PMT	-$10,000
PV	$60,000
FV	$0	I	13.70%
If you think a “fair” rate of return would be 6%, how much should you ask for the annuity?
BEGIN MODE
N	10
I	6%
PMT	-$10,000
FV	$0	PV	$78,016.92

4-12

SECTION 4-12
SOLUTIONS TO SELF-TEST
What is 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
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
What is the present value of the following uneven cash flow stream: $0 at Time 0, $100 at the end of Year 1 (or at Time 1), $200 at the end of Year 2, $0 at the end of Year 3, and $400 at the end of Year 4, assuming the interest rate is 8%?
Interest rate	8%
Year	0	1	2	3	4
CFs	$0	$100	$200	$0	$400
NPV	$558.07

4-13

SECTION 4-13
SOLUTIONS TO SELF-TEST
What is the future value of this cash flow stream: $100 at the end of 1 year, $150 after 2 years, and $300 after 3 years, assuming 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

4-14

SECTION 4-14
SOLUTIONS TO SELF-TEST
An investment costs $465 now and is expected to produce cash flows of $100 at the end of each of the next 4 years, plus an extra lump sum payment of $200 at the end of the 4th year. What is the expected rate of return on this investment?
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%
An investment costs $465 and is expected to produce cash flows of $100 at the end Year 1, $200 at the end of 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%

4-15

SECTION 4-15
SOLUTIONS TO SELF-TEST
What is 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
Compounded monthly?
N	36
I	0.67%
PV	-$100
PMT	$0	FV	$127.02
What is 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
Compounded monthly?
N	36
I	0.67%
PMT	$0
FV	$100	PV	$78.73
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%	=(1+B35/B36)^B36-1
19.56%	=EFFECT(B35,B36)

4-16

SECTION 4-16
SOLUTIONS TO SELF-TEST
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
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
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

4-17

SECTION 4-17
SOLUTIONS TO SELF-TEST
Consider again the example in Figure 4-11. If the loan were amortized over 5 years with 60 monthly payments, how much would each payment be, and how would the first payment be divided between interest and principal?
Years	5
Months = N	60
Nom. I	6%
Periodic I	0.5000%
PV	$100,000
FV	$0
PMT	$1,933.28
First payment interest:	$500.00	=B10*B11
First payment principal:	$1,433.28	=B13-C15
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 and 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
Beginning Amount (1)	Payment (2)	Interest (3)	Repayment of Principal (4)	Ending Balance (5)
Year
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, we just constructed the full amortization schedule.

4-18

SECTION 4-18
SOLUTIONS TO SELF-TEST
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	=((1+rNOM)/(1+Inflation))-1 =	4.7619%