YEAR-END VALUES OF A DEPOSIT OF $100

Annual Rate= 10%

Simple annual Compound Interest
Year
Interest Annually Monthly Daily Continously
0 $100 $100 $100 $100 $100 $8
1 110 110.00 110.47 110.52 $110.52
2 120 121.00 122.04 122.14 $122.14 $7
3 130 133.10 134.82 134.98 $134.99
$6
4 140 146.41 148.94 149.17 $149.18

Year-end Values
5 150 161.05 164.53 164.86 $164.87 $5
6 160 177.16 181.76 182.20 $182.21
7 170 194.87 200.79 201.36 $201.38 $4
8 180 214.36 221.82 222.53 $222.55
9 190 235.79 245.04 245.93 $245.96 $3

10 200 259.37 270.70 271.79 $271.83

$2
11 210 285.31 299.05 300.37 $300.42
12 220 313.84 330.36 331.96 $332.01 $1
13 230 345.23 364.96 366.86 $366.93
14 240 379.75 403.17 405.44 $405.52
15 250 417.72 445.39 448.08 $448.17
16 260 459.50 492.03 495.19 $495.30
17 270 505.45 543.55 547.27 $547.39
18 280 555.99 600.47 604.82 $604.96
19 290 611.59 663.35 668.42 $668.59
20 300 672.75 732.81 738.70 $738.91
 Chart Title
$800

$700

$600
Year-end Values

$500

$400

$300

$200

$100
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

Years of deposit
Si mpl e Annual Interest
Annual l y Compounded Interest
Monthly Compounded Interest
Da i ly Compounded Interest
Continous l y Compounded Intere st

EFFECT OF COMPOUNDING
Simple Annual
Year Annual Copounding
interest interest
0 $100 $100
1 $10 $10.00
2 $10 $11.00
3 $10 $12.10
4 $10 $13.31
5 $10 $14.64
6 $10 $16.11
7 $10 $17.72
8 $10 $19.49
9 $10 $21.44
10 $10 $23.58
11 $10 $25.94
 12 $10 $28.53
13 $10 $31.38
14 $10 $34.52
15 $10 $37.97
16 $10 $41.77
17 $10 $45.95
18 $10 $50.54
19 $10 $55.60
20 $10 $61.16
Total $300 $672.75
 FUTURE VALUE OF AN INVESTMENT Future Value of a Lump-Sum-Payment
Annual Rate of interest, yearly compounded 10% For annual compounded interest
Number of years (T) 6 ��=��∗(1+�)^�
Periodic payments (PMT) 0 For compounding interest of more than on
Present value (PV) of investment -$30,000 ��=��∗(1+�/�)^(�∗�)
Payment at the beginning (1) or end (0) of periods 0
Future value (FV) $53,146.83 Example: A sum of $30,000 is invested at a
Total payment -$30,000 compounded annually. What is the value o
years—that is, six years after making the in
Interest $23,146.83

FUTURE VALUE OF A SERIES OF EQUAL PAYMENTS (ANNUITY) Future Value of an Annuity (A series of Equ
Annual Rate of interest, monthly compounded 5% For annual compounded interest
Number of years (T) 2 ��=𝑃𝑀𝑇/�∗ 〖 [(1+�) 〗 ^�−1]
Periodic payments (PMT) -$10,000 For compounding interest of more than on
Present value (PV) of investment $0 ��=���/(�/�)∗ 〖 [(1+�/�) 〗 ^(�∗�)−
Payment at the beginning (1) or end (0) of periods 0
Future value (FV) $251,859.21
Total payment -$240,000 Example: The CFO of the Baker Company i
Interest $11,859.21 month into a mutual fund to accumulate c
purchased at the end of two years. The mo
percent annual rate, compounded monthly
mutual fund at the end of two years?

FUTURE VALUE OF A LUMP SUM AND AN ANNUITY Future Value of a Lump Sum and an Annuit
Annual Rate of interest, monthly compounded 5% For annual compounded interest
Number of years (T) 2 ��=��∗(1+�)^�+ 𝑃𝑀𝑇/�∗ 〖 [(1+�) 〗 ^�
Periodic payments (PMT) -$10,000
For compounding interest of more than on
Present value (PV) of investment -$200,000 ��=��∗(1+�/�)^(�∗�)+ ���/(�/�)∗ 〖
Payment at the beginning (1) or end (0) of periods 0
Future value (FV) $472,847.47
Total payment -$440,000 Example: Suppose that in addition to depo
Interest $32,847.47 month into a mutal fund for 2 years, the CF
an initial deposit of $200,000 at the beginn
be available at the end of 2 years?
 Lump-Sum-Payment
ounded interest

interest of more than one per year

(�∗�)

of $30,000 is invested at an annual rate of interest of 10 percent,

ually. What is the value of the investment at the end of six
years after making the investment?

n Annuity (A series of Equal Payment)

ounded interest
(1+�) 〗 ^�−1]

interest of more than one per year

〖 [(1+�/�) 〗 ^(�∗�)−1]

O of the Baker Company invests $10,000 at the end of each

ual fund to accumulate capital for new equipment that will be
end of two years. The money invested will earn interest at a 5
te, compounded monthly. How much will be available in the
e end of two years?

Lump Sum and an Annuity (A series of Equal Payment)

ounded interest
+ 𝑃𝑀𝑇/�∗ 〖 [(1+�) 〗 ^�−1]

interest of more than one per year

(�∗�)+ ���/(�/�)∗ 〖 [(1+�/�) 〗 ^(�∗�)−1]

e that in addition to depositing $10,000 at the end of each

al fund for 2 years, the CFO of the Baker Company begins with
of $200,000 at the beginning of the first month. How much will
e end of 2 years?
 PRESENT VALUE (PV) OF A FUTURE PAYMENT
Present Value of a Lump
Annual Rate of interest, yearly compounded 10% For annual compounde
Number of years (T) 6 𝑃�=(𝐹�_𝑇)/(1+�)^𝑇
Periodic payments (PMT) 0
For compounding intere
Future value (FV) of investment $30,000 ��=(��_�)/(1+�/�)^(�
Payment at the beginning (1) or end (0) of periods 0
Present Value (PV) -$16,934.22 Example: How large a lu
interest of 10 percent, c

Present Value of an Ann

PRESENT VALUE (PV) OF AN ANNUITY For annual compounde
𝑃�=𝑃𝑀𝑇/�∗[1−1/(1+
Annual Rate of interest, monthly compounded 5%
Number of years (T) 5 For compounding intere
Periodic payments (PMT) -$200 𝑃�=𝑃𝑀𝑇/(�/�)∗[1−
Future value (FV) of investment $0
Payment at the beginning (1) or end (0) of periods 0
Example: What is the p
Present Value (PV) $10,598.14 each month for the nex
compounded monthly.

Application of Present V
CHOOSING THE BEST PROJECTS
PROJECTS Example: Suppose you
A B C Alternative A: Year-end
Alternative B: A single, l
Periodic cashflows $7,000 $0 $2,600 Alternative C: Year-end
Lump-sum cashflow $0 $31,000 $20,000 $20,000 at the end of fo
Discount rate, annually compounded 6% 6% 6% Which alternative woul
Number of years 4 4 4
Present value of each projects $24,255.74 $24,554.90 $24,851.15

Application of Present

Example: At what disco

C equal? What is the pr
rate?
 Application of Present
CHOOSING THE BEST PROJECTS
Example: At what disco
PROJECTS C equal? What is the pr
A B C rate?
Periodic cashflows $7,000 $0 $2,600
Lump-sum cashflow $0 $31,000 $20,000
Discount rate, annually compounded 8.59% 8.59% 8.59%
Number of years 4 4 4
Present value of each projects $22,884.35 $22,295.90 $22,884.35
Defference in the PV of the project A and C -$0.00

Application of Present Values: Effect of changing the discount rate (Sensitivity

Analysis)

Example: Evaluate the effect of changes in the discount rate of money from 0 percent
to 12 percent on the present values of the three future cash inflows of Example 7.9.
Use increments of 1 percent in the discount rate, and indicate which alternative is the
best choice at each discount rate.
Present Value of a Lump-Sum-Payment
For annual compounded interest
𝑃�=(𝐹�_𝑇)/(1+�)^𝑇

For compounding interest of more than one per year

��=(��_�)/(1+�/�)^(�∗�)

Example: How large a lump sum of money would an individual need to invest at an annual rate of
interest of 10 percent, compounded annually, in order to have $30,000 at the end of six years?

Present Value of an Annuity (Series of Equal Periodic Payments)

For annual compounded interest
𝑃�=𝑃𝑀𝑇/�∗[1−1/(1+�)^𝑇 ]
For compounding interest of more than one per year

𝑃�=𝑃𝑀𝑇/(�/�)∗[1−1/(1+�/�)^(𝑇∗�) ]

Example: What is the present value of a series of monthly payments of $200 made at the end of
each month for the next five years? Assume that the discount rate is 5 percent per year,
compounded monthly.

Application of Present Values: Choosing the best project

Example: Suppose you are given the following three cash inflows from which to choose.
Alternative A: Year-end receipts of $7,000 for each of the next four years
Alternative B: A single, lump-sum receipt of $31,000 at the end of four years
Alternative C: Year-end receipts of $2,600 for each of the next four years plus a lump-sum receipt of
$20,000 at the end of four years
Which alternative would you choose if the discount rate of money was 6 percent, and why?

Application of Present Values: Determine the discount rate for equal PV'S

Example: At what discount rate are the present values of the future cash flows of Alternatives A and
C equal? What is the present value of the future cash flows of Alternative B at the same discount
rate?
Application of Present Values: Determine the discount rate for equal PV'S

Example: At what discount rate are the present values of the future cash flows of Alternatives A and
C equal? What is the present value of the future cash flows of Alternative B at the same discount
rate?

SENSITIVITY ANALYSIS
Discount rate Projects Best
Annually compunded A B C Choice
0% $28,000.00 $31,000.00 $30,400.00 B
1% $27,313.76 $29,790.39 $29,364.72 B
2% $26,654.10 $28,639.21 $28,377.00 B
3% $26,019.69 $27,543.10 $27,434.20 B
4% $25,409.27 $26,498.93 $26,533.81 C
5% $24,821.65 $25,503.78 $25,673.52 C
6% $24,255.74 $24,554.90 $24,851.15 C
7% $23,710.48 $23,649.75 $24,064.65 C
8% $23,184.89 $22,785.93 $23,312.13 C
9% $22,678.04 $21,961.18 $22,591.78 A
10% $22,189.06 $21,173.42 $21,901.92 A
11% $21,717.12 $20,420.66 $21,240.98 A
12% $21,261.45 $19,701.06 $20,607.47 A
 PRESENT VALUE (PV) OF UNEQUAL CASHFLOWS
Year 1 2 3 4
Year-end cashflows $4,500 $8,000 $10,000 $5,000
Annual discount rate 10%
Method 1: Manually discount all the cashflows
Present value $ 4,090.91 $ 6,611.57 $ 7,513.15 $ 3,415.07
Sum of the present values $ 4,090.91 $ 10,702.48 $ 18,215.63 $ 21,630.69
Method 2: Applying the NPV() function of Excel
Present value $4,090.91 $10,702.48 $18,215.63 $21,630.69
Note: NPV() accepts maximum 29 cashflows
FUTURE VALUE OF UNEQUAL CASHFLOWS
Method 1: Manually compound all the cashflows
Future values $ 6,588.45 $ 10,648.00 $ 12,100.00 $ 5,500.00
Sum of the Future values $ 6,588.45 $ 17,236.45 $ 29,336.45 $ 34,836.45
Method 2: Combine NPV() and FV() functions
Present value $22,872.54
Interest rate 10%
Number of years 5
Future value $36,836.45

NPV() vs PV() functions:

PV() assumes that all periodic payments are equal in successive periods.
NPV() allow for various patterns of periodic payments: negative, zero, positive

PV() allows the cashflows at either the end or the beginning of the periods
NPV() assumes that all the cashflows take place at the end of the periods
 Present Value of a Series of Unequal Cashflow Stream
5 𝑃�=(𝐶𝐹_1)/(1+�)^1 +(��_2)/(1+�)^2 +…+(��_𝑇)/(1+�)^𝑇 =∑24_(𝑘=1)^𝑇▒(𝐶𝐹_𝑘)
$2,000 (1+�)^𝑘
���=𝐶𝐹_0+(��_1)/(1+�)^1 +(��_2)/(1+�)^2 +…+(��_�)/(1+�)^� =∑_(�=0)^�▒(��_�)/
For the compounding frequencies of more than once per year, adjustments to interests an
number
For of periods arefrequencies
the compounding applied. of more than once per year, adjustments to interests an
number of periods are applied.
$ 1,241.84 ExampleFind the present value of the following cash flows, each of which is received at ye
$ 22,872.54 for the next five
ExampleFind theyears:
present value of the following cash flows, each of which is received at ye
for the next five years:
$22,872.54
If the discount rate of money is 10 percent per year, what is the present value of the strea
If the discount
future rate of money is 10 percent per year, what is the present value of the strea
cash flows?
future cash flows?
Note: Usually we will deal extensively with the PV of unequal cashflows. The FV of
$ 2,000.00 Note: Usually
streams is lesswe will deal
common in extensively with thethe
practice. However, PVFVof unequal cashflows.
is also useful The
in some FV of
cases in
streams
financialisplanning.
less common in practice.
Finding the FV is However, the FV
just a straight is also useful
application in some
of the cases
concepts of in
$ 36,836.45 financial planning. Finding the FV is just a straight application of the concepts of
compounding.
compounding.
^𝑇 =∑24_(𝑘=1)^𝑇▒(𝐶𝐹_𝑘)/

+�)^� =∑_(�= 0)^�▒(��_�)/(1+�)^�

r, adjustments to interests and the
r, adjustments to interests and the
ach of which is received at year end
ach of which is received at year end

he present value of the stream of

he present value of the stream of

qual cashflows. The FV of such CF

qual cashflows.
so useful The
in some FV of
cases in such CF
so useful
ation in some
of the cases
concepts of in
ation of the concepts of
Case study: An Investment Decision

An investor has the choice of two alternatives for investing $10,000. Alternative A returns a single lump
sum of $30,000 at the end of the fourth year—that is, four years after making the investment. Alternative B
returns a cash inflow of $5,800 at the end of each year for the next four.
a. If the discount rate of money is 10 percent, compounded annually, which investment has the higher net
present value?
b. If the discount rate of money is 20 percent, compounded annually, which investment has the higher net
present value?
c. What is the discount rate at which the two alternatives are equally attractive?
d. Prepare a one-variable input table to show the sensitivity of the net present values of the two
alternatives to discount rates from 10 to 20 percent.
e. Prepare a chart of the results from part d.
 Case study: AN INVESTMENT DECISION
rns a single lump Year-end cashflows Part d (Sensitivity
Year
stment. Alternative B Alternative A Alternative B NPV of Alterna
t has the higher net 0 -$10,000 -$10,000 Discount rates
1 0 $5,800 10%
t has the higher net 2 0 $5,800 11%
3 0 $5,800 12%
of the two 4 $30,000 $5,800 13%
Part a 14%
Discount Rate 10% 15%
NPV of Alternatives $10,490.40 $8,385.22 16%
Best choice Alternative A --- 17%
Part b 18%
Discount Rate 20% 19%
NPV of Alternatives $4,467.59 $5,014.66 20%
Best choice --- Alternative B
Part c (Method 1: Set the difference=0)
Discount Rate 17.43%
NPV of Alternatives $5,777.76 $5,777.76
Difference = $0.00
Part c (Method 2: Set NPV_A=NPV_B)
Discount Rate 17.43%
NPV of Alternatives $5,777.76 $5,777.76

Part e: NPV profile of 2 alternatives

$11,000

$10,000

$9,000
NPV OF ALTERNATIVES

$8,000

$7,000

$6,000

$5,000

$4,000
10% 11% 12% 13% 14% 15% 16% 17% 18

DISCOUNT RATE

Al ternative A Al ternative B
$4,000
10% 11% 12% 13% 14% 15% 16% 17% 18

DISCOUNT RATE

Al ternative A Al ternative B
ISION
Part d (Sensitivity Analysis) Part d (Sensitivity Analysis by Columns)
NPV of Alternatives NPV of Alternatives (use Data Table)
Alternative A Alternative B Discount rates Alternative A Alternative B
$10,490.40 $8,385.22 10% $10,490.40 $8,385.22
$9,761.93 $7,994.18 11%
$9,065.54 $7,616.63 12%
$8,399.56 $7,251.93 13%
$7,762.41 $6,899.53 14%
$7,152.60 $6,558.87 15%
$6,568.73 $6,229.45 16%
$6,009.50 $5,910.76 17%
$5,473.67 $5,602.36 18%
$4,960.06 $5,303.80 19%
$4,467.59 $5,014.66 20%

Note: to use the Data_table, we have to identify the independent

variable (Discounte Rate) and dependent variables (NPV of Alternative
A and B)
1. Choose the WHOLE area of the table
2. If the independent variable changes by column, use "Column input
Cell", refer to the original cell
3. If the independent variable changes by row, use "Row input Cell",
refer to the original cell

alternatives

16% 17% 18% 19% 20%

ATE

Al ternative B
 16% 17% 18% 19% 20%

ATE

Al ternative B
 Part d (Sensitivity Analysis by Rows)
Discount rates 10% 11% 12% 13% 14% 15% 16%
Alternative A $10,490.40
Alternative B $8,385.22

ntify the independent

riables (NPV of Alternative

olumn, use "Column input

ow, use "Row input Cell",

s)
17% 18% 19% 20%
 Example: The Morgan Company plans to borrow money to purchase an office building for its headquarters. The building it h
make a down payment of $2 million and take a first mortgage on the balance of $8 million. The lender agrees to provide a 3
interest rate of 10 percent, compounded monthly, with monthly payments at the end of each month. How much will Morga

MONTHLY MORTGAGE PAYMENT

Discount rate, monthly compounded 10%
Loan life, years 30
Amount of loan (PV) $8,000,000
Future value (FV) 0
Payment at the beginning (1) or end (0) of the month 0
Monthly payment -$70,205.73

Example: To save for a new computer system that will be purchased two years from the present, the financial manager of A
bank account that pays a nominal annual rate of interest of 6 percent, compounded monthly. The deposits will be made at t
will cost $20,000 when it is purchased two years from the present. What should be the amount of the monthly deposits?

ARGOSY SERVICES
Discount rate, monthly compounded 6%
Loan life, years 2
Amount of loan (PV) $0
Future value (FV) $20,000
Payment at the beginning (1) or end (0) of the month 1
Monthly payment -$782.50

Example: The CFO of the Baker Company needs $500,000 for new equipment that will be purchased at the end of two years
deposit of $200,000 at the beginning of the first month. The money invested will earn interest at a 5 percent annual rate, co
Company have to deposit at the end of each month for two years (in addition to the initial deposit of $200,000) for the mut

PERIODIC PAYMENTS FOR A GIVEN PV AND FV

Interest rate, monthly compounded 5%
Number of years to invest 2
Initial investment (PV) -$200,000
Future value (FV) $500,000
Payment at the beginning (1) or end (0) of the month 0
Monthly payment -$11,078.08

Example: If the Morgan Company pays off its $8 million mortgage by monthly payments of $70,205.73 at the end of each m
for the first and last months of the mortgage?
 MORGAN COMPANY MORTGAGE PAYMENT
Discount rate, monthly compounded 10%
Loan life, years 30
Amount of loan (PV) $8,000,000
Future value (FV) 0
Payment at the beginning (1) or end (0) of the month 0
Monthly payment -$70,205.73
Period from the start of mortgage 1 360
Payment to interest -$66,666.67 -$580.21
Payment to principal -$3,539.06 -$69,625.51
Total payment for each period -$70,205.73 -$70,205.73

Example: The CFO of the Morgan Company wants to know how much interest the company will pay on its $8 million mortga
company will pay toward reducing the principal during the first year.

MORGAN COMPANY MORTGAGE PAYMENT

Discount rate, monthly compounded 10%
Loan life, years 30
Amount of loan (PV) $8,000,000
Future value (FV) 0
Payment at the beginning (1) or end (0) of the month 0
Monthly payment -$70,205.73
Start period 1
End period 12
Cumulative payment to interest -$797,998.42
Cumulative payment to principal -$44,470.29
Total payment of the year -$842,468.71

Example: For planning purposes, the CFO of the Morgan Company (see preceeding example) needs a table and chart that sh
change during each of the 30 years of the mortgage.

MORGAN COMPANY MORTGAGE PAYMENT

Discount rate, monthly compounded 10% Year
Loan life, years 30 1
Amount of loan (PV) $8,000,000 2
Future value (FV) 0 3
Payment at the beginning (1) or end (0) of the month 0 4
Monthly payment -$70,205.73 5
6
7
MORGAN COMPANY YEARLY MORTGAGE PAYMENT
8
$800,000 9
10
$700,000

$600,000
 MORGAN COMPANY YEARLY MORTGAGE PAYMENT
$800,000

$700,000 11
12
13
$600,000
14
15
$500,000
16
17
PAYMENT

$400,000 18
19
$300,000 20
21
22
$200,000
23
24
$100,000
25
26
$0 27
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30
28
YEAR 29
30
Payment to Interest Payment to Princi pal
Total
 for its headquarters. The building it has selected has a price tag of $10 million. The company will Periodic Payments
lion. The lender agrees to provide a 30-year mortgage on the principal of $8 million at an annual
of each month. How much will Morgan pay monthly on their mortgage? Many business and
period to the next.
• Monthly payment
• Monthly receipts
• Monthly or biwee
• Annual payments
• Annual investmen
capital investments
Moving a single ann
value, and vice vers

Excel’s PMT() functi

a specified number
periodic amount or

e present, the financial manager of Argosy Services wants to put aside monthly amounts into a
onthly. The deposits will be made at the beginning of each month, and the new computer system
e amount of the monthly deposits?

be purchased at the end of two years. The CFO of the Baker Company begins with an initial
interest at a 5 percent annual rate, compounded monthly. How much would the CFO of the Baker
itial deposit of $200,000) for the mutual fund to have a future value of $500,000?

ts of $70,205.73 at the end of each month for 30 years, how much interest will the company pay
pany will pay on its $8 million mortgage during the first year of the mortgage, and how much the

mple) needs a table and chart that show how the annual payments to interest and to principal

Mortgage Payments
Interest Principal Total
$797,998.42 $44,470.29 $842,468.71
$793,341.80 $49,126.91 $842,468.71
$788,197.57 $54,271.14 $842,468.71
$782,514.68 $59,954.03 $842,468.71
$776,236.70 $66,232.00 $842,468.71
$769,301.35 $73,167.36 $842,468.71
$761,639.77 $80,828.94 $842,468.71
$753,175.92 $89,292.78 $842,468.71
$743,825.80 $98,642.90 $842,468.71
$733,496.60 $108,972.11 $842,468.71
 $722,085.80 $120,382.91 $842,468.71
$709,480.13 $132,988.57 $842,468.71
$695,554.49 $146,914.21 $842,468.71
$680,170.65 $162,298.05 $842,468.71
$663,175.93 $179,292.78 $842,468.71
$644,401.63 $198,067.08 $842,468.71
$623,661.42 $218,807.29 $842,468.71
$600,749.44 $241,719.27 $842,468.71
$575,438.27 $267,030.44 $842,468.71
$547,476.69 $294,992.01 $842,468.71
$516,587.18 $325,881.53 $842,468.71
$482,463.12 $360,005.59 $842,468.71
$444,765.83 $397,702.87 $842,468.71
$403,121.14 $439,347.56 $842,468.71
$357,115.71 $485,352.99 $842,468.71
$306,292.91 $536,175.79 $842,468.71
$250,148.30 $592,320.41 $842,468.71
$188,124.61 $654,344.09 $842,468.71
$119,606.24 $722,862.47 $842,468.71
$43,913.09 $798,555.62 $842,468.71
$17,274,061.22 $8,000,000.00 $25,274,061.22
Periodic Payments and Receipts

Many business and personal situations involve periodic payments or receipts that are equal from one
period to the next. Examples include:
• Monthly payments on home mortgages or automobile loans
• Monthly receipts from retirement systems
• Monthly or biweekly deductions from pay checks into company credit unions
• Annual payments for home and life insurance
• Annual investments into sinking funds to accumulate cash to replace equipment or make other future
capital investments.
Moving a single annuity payment or receipt back and forth in time to find its equivalent present or future
value, and vice versa, is an important operation in financial analysis.

Excel’s PMT() function calculates the value of a periodic payment for paying back (i.e., amortizing) a loan in
a specified number of periods and at a specified interest rate. It is also used to calculate the value of a
periodic amount or deposit that should be set aside to accumulate a future amount.
Case study: Inverson's home mortgage

Mr. and Mrs. Iverson have applied for a mortgage loan on a new home. The new home has a price of $250,000.
The Iversons will make a down payment of $50,000 and take a 30-year mortgage on the balance. The mortgage
company will charge a nominal annual interest rate of 9 percent, compounded monthly.

a. What will be the month-end mortgage payments the Iversons will pay?

b. The loan is made July 1, and the Iversons will make their first month-end payment at the end of July. When
computing their taxable income, the Iversons can deduct the interest they paid on their mortgage during the
calendar year for which they file their income tax. If the Iversons continue to make their monthly payments by
the end of each month, how much interest will be a deductible expense on their income tax for the calendar
year in which they took out their home mortgage? (Note that the first calendar year of the mortgage is from
July to December; that is, from months one to six of the mortgage.) How much interest will be a deductible
expense for the next year after that? How much interest will they be able to deduct as an allowable expense for
the second calendar year.

c. What would be the total amount of interest the Iversons would pay if their mortgage continued in effect for
the entire 30 years?

d. Suppose that at the end of three years (i.e., 36 months) from the time they took out the mortgage, the
financial conditions of the Iversons have improved and interest rates have declined. The Iversons then wish to
consider paying off the remaining balance due on their mortgage with a new mortgage having a
life of 20 years. The new 20-year mortgage the Iversons will take out to replace their original mortgage has a
nominal annual interest rate of 8 percent, compounded monthly. What will be the principal of the new
mortgage and the Iversons’ monthly payments on it?

e. At the end of an additional five years after taking out the new mortgage (i.e., a total of eight years frome. At
the end of an additional five years after taking out the new mortgage (i.e., a total of eight years from their
original home mortgage), the Iversons have added to their family and need a larger home. As part
of the transactions for buying the new home, they need to pay off the balance due on the mortgage for their
old home. How much will be the unpaid balance of their mortgage on their old home at this point?

f. What will be the market value of the home at the time of sale (i.e., eight years from its purchase) if its market
value appreciates at a rate of 3.5 percent per year over the eight years that the Iversons have owned the home?
Assuming that the Iverson’s pay a fee of 6 percent of the selling price, and the selling
price is the same as the market value at the time of sale, how much will the Iversons receive from the sale of
their home after paying the selling expenses and paying off the balance due on the mortgage?

g. Create a one-variable input table that shows the effect of the rate of appreciation for the market value of the
Iversons’ home on the net proceeds they will receive from the sale of their home after paying the 6 percent
sellers fee and the unpaid balance of their mortgage. Use appreciation rates of 2, 3, 4, and 5
percent.

h. Create a two-variable input table that shows the effect of changes in the rate of appreciation for the market
value of the Iversons’ home and the rate of interest for their 20-year mortgage on the net proceeds they will
receive from the sale of their home. Use appreciation rates of 2, 3, 4, and 5 percent and mortgage rates of
interest of 6, 7, 8, 9, and 10 percent.
quant:
as a price of $250,000.
balance. The mortgage

he end of July. When

mortgage during the
monthly payments by
tax for the calendar
he mortgage is from
will be a deductible
n allowable expense for

ontinued in effect for

he mortgage, the
Iversons then wish to
aving a
inal mortgage has a
pal of the new

f eight years frome. At

t years from their
e. As part
e mortgage for their
this point?

purchase) if its market

have owned the home?

eive from the sale of

gage?

he market value of the

aying the 6 percent
nd 5

ciation for the market

t proceeds they will
mortgage rates of
 INVERSONS' HOME MORTGAGE
Initial Mortgage
Home price $ 250,000.00
Downpayment $ 50,000.00
Mortgage principal $ 200,000.00
Annual interest rate 9.00%
Term, years 30.00
Monthly payment $ 1,609.25 a
Interest, months 1-6 (July-Dec) $ 8,987.59 b1
Interest, month 7-18 (Jan-Dec next year) $ 17,881.90 b2
Cumulative payments to principal at month 36 (first 3 years) $ 4,495.74
Mortgage principal at month 36 $ 195,504.26
Interest, months 1-360 $ 379,328.28 c
Interest, month 1-360 (Alternative calculation: Total payment-principal) $ 379,328.28 c
Total payments after 360 months $ 579,328.28

New mortgage at the end of 3 years

Mortgage principal $ 195,504.26
Annual Interest rate 8.00%
Term, years 20.00
Monthly payment $ 1,635.28 d

Payoff the new mortgage after 5 more years ( 8 years from start)
Cumulative payments to principal at month 60th of the new mortgage $ 24,388.01
Unpaid balance at the end of month 60 of new mortgage $ 171,116.25 e

Gain on investment
Annual rate of appreciation of home value 3.50%
Selling price=market value of home at the end of 8th year $ 329,202.26 f1
Selling expenses, as percentage of selling price 6.00%
Selling expenses $ 19,752.14
Unpaid mortgage balance at the time of sale $ 171,116.25
Net receipts from sale $ 138,333.88
Dollar gain from downpayment $ 88,333.88 f2
Annual compound rate of appreciation of down payment 13.57% f3

Additional analysis
Initial down payment $ 50,000.00
Total monthly payment for the first mortgage (3 years) $ 57,932.83
Total monthly payment for the second mortgae (5 years) $ 98,116.56
Total payment $ 206,049.39
Net receipt from sale $ 138,333.88
Dollar gain/loss -$ 67,715.51
For more accurate analysis, we have to draw a time line and all payments after 8
years and the receipts after 8th year then find the IRR of the investment
 Part g
One variable input table
Annual rate of Net receipts from
appreciation sale
Base 3.50% $ 138,333.88
2.00%
3.00%
4.00%
5.00%

Part h
Two-variable Input Table
Annual rate of
Appreciation Annual rate of interest mortgage
Base $ 138,333.88 6% 7% 8% 9% 10%
2.00%
3.00%
4.00%
5.00%
 Example: The Morgan Company plans to borrow money to purchase an office building for its headquarters. The building it h
first mortgage on the balance of $8 million. The lender agrees to provide a 30-year mortgage on the principal of $8 million a
month. How much will Morgan pay monthly on their mortgage?

MONTHLY MORTGAGE PAYMENT

Discount rate, monthly compounded 10%
Loan life, years 30
Amount of loan (PV) $8,000,000
Future value (FV) 0
Payment at the beginning (1) or end (0) of the month 0
Monthly payment -$70,205.73

Example: Suppose the CFO for the Morgan Company plans to make monthly payments of $100,000 rather than $70,250.73.
monthly?

MONTHLY MORTGAGE PAYMENT

Discount rate, monthly compounded 10%
Loan life, years 30
Amount of loan (PV) $8,000,000
Future value (FV) 0
Payment at the beginning (1) or end (0) of the month 0
Monthly payment -$70,205.73
New value of monthly payment -$ 100,000.00
Number of months to payoff the mortgage 132.38
New loan life (Years) 11.03

Example: The CFO of the Morgan Company notes that the solution to the previous example is a non-integer number of mont
in order to pay off the loan at the end of 11 years—that is, in exactly 132 months?

MONTHLY MORTGAGE PAYMENT

Discount rate, monthly compounded 10%
Loan life, years 30
Amount of loan (PV) $8,000,000
Future value (FV) 0
Payment at the beginning (1) or end (0) of the month 0
Desired value of monthly payment -$100,159.02
Previous value of monthly payment -$ 100,000.00
Number of months to payoff the mortgage 132.00
New loan life (Years) 11.00

Key Takeaway: The NPER command calculates the number of periods needed at a given interest rate to pay off a loan of spe
headquarters. The building it has selected has a price tag of $10 million. The company will make a down payment of $2 million and take a
on the principal of $8 million at an annual interest rate of 10 percent, compounded monthly, with monthly payments at the end of each

00,000 rather than $70,250.73. How long would it then take to repay the $8 million mortgage at 10 percent annual interest, compounded

a non-integer number of months that is slightly longer than 11 years. To what amount would she have to increase the monthly payments

est rate to pay off a loan of specified amount or to accumulate a specified future amount
ayment of $2 million and take a
payments at the end of each

annual interest, compounded

ncrease the monthly payments

 ExampleAs a result of spending $25,000 to purchase special equipment to produce its products, a company estimates that
return is expected for the investment?

RETURN ON INVESTMENT
Equipment cost -$ 25,000.00
Year-end after-tax cash inflow $ 12,000.00
Number of years for cash inflows 3
After-tax annual rate of returns 20.71%

Key Takeaway: The RATE function calculates the rate of return for an investment that generates a series of equal periodic pa
oducts, a company estimates that its after-tax cash flow will increase by $12,000 for each of the next three years. What rate of

rates a series of equal periodic payments in successive periods or a single lump-sum payment
years. What rate of
Case study: Foremost Mortgage Company

Doris Eppley is a branch manager of the Foremost Mortgage Company. Carlos and Maria Hernandez
have come to the company to arrange a mortgage for the purchase of a new house. The purchase
price of the new house is $200,000. Mr. and Mrs. Hernandez propose to make a down payment of
$20,000 and take out a 30-year first mortgage on the remainder. Ms. Eppley advises them that the
current nominal annual rate of interest for 30-year first mortgages on homes is 10.25 percent,
compounded monthly. The loan is to be repaid in monthly installments beginning at the end of the
loan’s first month.

a. What are the monthly payments to pay back the loan fully at the end of 30 years? What is the
total amount of interest that Mr. and Mrs. Hernandez will pay during that time?
b. If Mr. and Mrs. Hernandez increase their monthly payments by $10, how soon will their loan be
paid off, and how much interest will they have paid? How much interest will they have saved by
paying their loan off early as a result of increasing their monthly payments by $10?

c. Determine the number of months for paying off their mortgage and the total interest Mr. and Mrs.
Hernandez will have paid during that time if they increase their monthly payments by $l0, $20, $30,
or
$40. Do not round-off the fractional portions of months; show the number of months to 2 decimal
places (e.g., 292.45). Save the answers on your spreadsheet. (You can solve this and the next part by
programming them as part of your spreadsheet or you can use Scenario Analysis.)

d. Add a new section to your spreadsheet. Begin by showing the number of months for paying off
the Hernandez’s mortgage with the values from part c rounded to the nearest whole number. (For
example, if adding $20 to the monthly payment results in the answer 292.45 months in part c, round
the number of periods to 292 months in part d.) Use these new values for the number of months (or
periods) to recompute the amount of the monthly payments. (If done correctly, you will find that the
new monthly payments should be within 20 cents, more or less, of the values of $10, $20, $30, or
$40.) Determine how much interest Mr. and Mrs. Hernandez will have paid by the time they have
completely paid off their mortgage at the new monthly payments. Also determine how much
interest the couple will pay over the lifetime of the mortgage.
FOREMOST MORTGAGE COMPANY
Purchase price of the new home $200,000
Downpayment $20,000
Mortgage Data
Principal $180,000
Term or durations, years 30.00
Nominal annual interest rate, compounded monthly 10.25%
Given the increases of $10, $20,$30 and $40 to m
Calculated value Base case payment
Increase in monthly payment $0 $10 $20
Monthly payment $1,612.98 $1,622.98 $1,632.98
Number of months to maturity 360 346.10 333.81
Number of years to maturity 30 28.84 27.82
Total interest paid over mortgage term
Calculated as PMT*No. of months-principal $400,673.64 $381,713.12 $365,112.90
Calculated as CUMIPMT() function $ 400,673.64 $ 381,712.37 $ 365,102.67
Interest saved by increasing monthly payment $0.00 $18,960.52 $35,560.74

Rounding months to the nearest whole numbe

recalculating monthly payment
Calculated values Base case
Number of months to maturity 360.00 346.00 334.00
Number of years to maturity 30.00 28.83 27.83
Monthly payment $1,612.98 $1,623.06 $1,632.82
Increase in monthly payment $0.00 $10.08 $19.84
Total interest paid over mortgage term
Calculated as PMT*No. of months-principal $400,673.64 $381,578.28 $365,362.66
Calculated as CUMIPMT() function $ 400,673.64 $ 381,578.28 $ 365,362.66
Interest saved by increasing monthly payment $0.00 $19,095.36 $35,310.98

Total Interest savings over the life of the mortgage

$70,000.00

$60,000.00

$50,000.00
Interest Savings

$40,000.00

$30,000.00

$20,000.00

$10,000.00

$0.00
$0 $10 $30 $40

Increase in monthly payment

 $20,000.00

I
$10,000.00

$0.00
$0 $10 $30 $40

Increase in monthly payment

ses of $10, $20,$30 and $40 to monthly
payment
$30 $40
$1,642.98 $1,652.98
322.82 312.89 Note: Be aware of the problem when using the CUMIPMT
function with non-integer values for the number of months to
26.90 26.07 maturity. Excel rounds down the values to an integer number of
months (i.e., to 346, 333, 322, and 312) for calculating the total
$350,390.24 $337,195.12 interest paid over the mortgage’s term. This results in values
that are lower than the correct values.
$ 350,379.82 $ 337,183.42
$50,283.40 $63,478.52

ths to the nearest whole number and

alculating monthly payment

323.00 313.00
26.92 26.08
$1,642.81 $1,652.86
$29.83 $39.88

$350,628.11 $337,345.83
$ 350,628.11 $ 337,345.83
$50,045.53 $63,327.81

$40
$40
Effect of tax and inflation on return of investment

Effect of Taxes
If a person invests $10,000 at a nominal 10 percent annual interest, he will hav
However, if his income tax rate is 30 percent, he must pay $300 tax on the gain
only 7 percent. In general, the after-tax rate of return is given by the formula:
�_(𝑎𝑓𝑡𝑒�−𝑡𝑎𝑥)=�_𝑛𝑜�𝑖�𝑎𝑙∗(1−𝑡)
Thus, for the conditions stated,
�_𝑎𝑓𝑡𝑒�=0.1∗(1−0.3)=0.07=7%
Effect of Inflation
Inflation robs money of its buying power. It is said to be the cruelest form of ta

If a person invests $10,000 at a nominal 10 percent annual interest, he will hav

of one year. That is a dollar gain of $1,000 over his initial investment. However,
have been purchased a year earlier for only $10,000. To put it another way, the
(1+0.04) = $10,576.92 a year earlier in order to keep up with the reduction in b
gain for inflation. This represents a gain in real buying power of $576.92. In oth
gain on investment after adjusting for inflation is $10,000*(1+10%)- $10
the effective rate of interest after correcting for inflation is given by the formul
�_𝑒𝑓𝑓=(�_𝑛𝑜�𝑖𝑛𝑎𝑙−𝑖_𝑖𝑛𝑓𝑙𝑎𝑡𝑖𝑜𝑛)/(1+𝑖_𝑖𝑛𝑓𝑙𝑎𝑡𝑖𝑜𝑛 )
Thus, for the conditions stated,
�_𝑒𝑓𝑓=(0.1−0.04)/(1+0.04)=0.0577=5.77%

Combined effect of inflation and taxes

From the preceding solution for the effect of taxes, the investor of $10,000 rec
interest. The effect of 4 percent inflation is to reduce the buying power of the $
$288.46, or a 2.88 percent gain in buying power. This result can also be obtaine
�_𝑎𝑡𝑖= (�_𝑛𝑜�𝑖𝑎𝑙 (1−𝑡)−𝑖_𝑖𝑛𝑓𝑙𝑎𝑡𝑖𝑜𝑛)/(1+𝑖_𝑖𝑛𝑓𝑙𝑎𝑡𝑖𝑜𝑛 ) 𝑤ℎ𝑒�𝑒 �_𝑎𝑡
Thus, for the conditions stated,
�_𝑎𝑡𝑖= (0.1 (1−0.03)−0.04)/(1+0.04)=0.028846=2.88%

Of course, if the investor had done nothing, the buying power of his $10,000 w
been a loss of $384.62 in buying power, or a return of -3.85 percent for not inv
after taxes and inflation by 6.73 percent (calculated as 2.88% minus a loss of 3.
ment

rcent annual interest, he will have $11,000 at the end of one year. That is a dollar gain of $1,000 over his initial investment.
he must pay $300 tax on the gain of $1,000. That leaves him with an after-tax dollar gain of $700, or an after-tax rate of return of
return is given by the formula:

aid to be the cruelest form of taxation, for it reduces the buying power of older people and others on a fixed income.

rcent annual interest, he will have $11,000 at the end

r his initial investment. However, if the inflation rate is 4 percent per year, it will cost $10,400 to purchase the same goods that could
0,000. To put it another way, the purchasing power of $11,000 after one year would have required an initial investment of $11,000/
o keep up with the reduction in buying power due to inflation. The amount $10,576.92 is the real gain after adjusting the nominal
buying power of $576.92. In other words, the effective rate of return is $576.92/$10,000 = 0.0577 = 5.77%. Put another way, the
tion is $10,000*(1+10%)- $10,000*(1+4%) = $11,000 - 10,400 = 600 and the return rate = 600/10,400 = 5.77%. In general,
r inflation is given by the formula of Fisher realation
𝑖𝑛𝑓𝑙𝑎𝑡𝑖𝑜𝑛 )

5.77%

axes, the investor of $10,000 receives an after-tax cash inflow of $10,700 after a year for his investment of $10,000 at 10 percent
educe the buying power of the $10,700 to $10,700/(1+0.04) = $10,288.46. The net gain after inflation and taxes is therefore only
er. This result can also be obtained by combining the previous equations in the form
(1+𝑖_𝑖𝑛𝑓𝑙𝑎𝑡𝑖𝑜𝑛 ) 𝑤ℎ𝑒�𝑒 �_𝑎𝑡𝑖=�𝑒𝑡𝑢�𝑛 𝑎𝑓𝑡𝑒� 𝑡𝑎𝑥 𝑎𝑛𝑑 𝑖𝑛𝑓𝑙𝑎𝑡𝑖𝑜𝑛

=0.028846=2.88%

e buying power of his $10,000 would be only $10,000/1.04 = $9,615.38 at the end of one year due to inflation. This would have
eturn of -3.85 percent for not investing. Thus, by investing rather than not investing, the investor has increased his buying power
lated as 2.88% minus a loss of 3.85%).
itial investment.
r-tax rate of return of

ed income.

he same goods that could

al investment of $11,000/
r adjusting the nominal
. Put another way, the
400 = 5.77%. In general,

$10,000 at 10 percent
taxes is therefore only

tion. This would have

sed his buying power