Ross, Westerfield, Jaffe, and Jordan's Excel Master

Corporate Finance: Core Principles and Applications, 4th edition

by Brad Jordan and Joe Smolira
Version 4.0

Chapter 4

In these spreadsheets, you will learn how to use the following Excel f
FV
PV
Two-way data tables
RATE
NPER
FVSCHEDULE
NPV
EAR

APR
Exponential function
PV of an annuity
FV of an annuity
PMT
Annuity interest rate
Annuity periods
Nested function
Annuity due
Loan amortization worksheet
The following conventions are used in these spreadsheets:

1) Given data in blue

2) Calculations in red
NOTE: Some functions used in these spreadsheets may require that
the "Analysis ToolPak" or "Solver Add-In" be installed in Excel.
To install these, click on the Office button
then "Excel Options," "Add-Ins" and select
"Go." Check "Analysis ToolPak" and
"Solver Add-In," then click "OK."

the following Excel functions:

Chapter 4 - Section 2
The Multiperiod Case

Excel contains numerous financial functions, many of which relate to the time value of money. We will begin by usi
Example 4.3: Interest on Interest

Suh-Pyng Ku has made the following deposit at the First National bank of Kent. How much will she have at the
Number of periods:
Initial investment:
Interest rate per year:

At the end of the investment, she will have:

5
500
7.0%
$

701.28

As shown in the textbook, the future value of $1 is found by the equation FV = $1 (1 + r )t . In Excel, the carat ( ^
occurs with compound interest, not simple interest. To see the difference between simple interest and compound
Initial investment:
Interest rate per year:

$500
10%

What is the value of the investment each year over the next 5 years? How much of the interest is simple interest an
question with the following table:

Year
1
2
3
4
5
Totals

Beginning
Simple
Compound
Total
Amount
Interest
Interest
Interest
$
500.00 $
50.00 $
$
50.00
550.00
50.00
5.00
55.00
605.00
50.00
10.50
60.50
665.50
50.00
16.55
66.55
732.05
50.00
23.21
73.21
$
250.00 $
55.26 $
305.26

So what does simple interest look like compared to compound interest? We can use Excel to draw a graph for us. F
simple interest and the total compound interest.

Year

Amount with
Simple
Interest

Total
Compound
Interest

1
2
3
4
5

$
$
$
$
$

550.00
600.00
650.00
700.00
750.00

$
$
$
$
$

5.00
15.50
32.05
55.26

Now we can graph the contribution of compounding to the future value of our investment.

Future Value, Simple Interest, and Compound Interest

$900
$800
$32.05

Future Value ($)

$700

$15.50

$600
$500

$5.00
$-

$400
$300
$200
$100
$1

3
Time (years)

To see the effect of compound interest, change the interest rate and see how the compound interest grows as the

RWJ Excel Tip

To insert this bar chart, we highlighted the columns we wanted in the graph, went to the Insert tab, and then selec
get the border shadowing effect, we right-clicked on the graph, selected Format Plot Area and chose the Shadow o

In the past, future value tables were very common. Future value tables calculated the future interest factor for a va
a future value table relatively quickly in Excel. By the way, we will show you a much more efficient method in the n
Number of
Periods
0
1
2
3
4

0%
1.000
1.000
1.000
1.000
1.000

5%
1.000
1.050
1.103
1.158
1.216

Interest Rate
10%
1.000
1.100
1.210
1.331
1.464

15%
1.000
1.150
1.323
1.521
1.749

5
6
7
8
9
10

1.000
1.000
1.000
1.000
1.000
1.000

1.276
1.340
1.407
1.477
1.551
1.629

1.611
1.772
1.949
2.144
2.358
2.594

2.011
2.313
2.660
3.059
3.518
4.046

An important fact about compound interest is that it results in exponential growth. To see the exponential growth
like this:

Future Value of $1 for Different Periods and R

$7

Future value of $1

$6
$5
$4
$3
$2
$1
$0
0

Time (years)

RWJ Excel Tip

There is a "bug" in Excel when graphing a table like the one above. If a table has text in the header row and column
graph. However, when the header row and column are numbers, Excel will not use the numbers in the legend, but
in the legend and on the vertical axis, try the following: First, select just the data in the data and ignore the header
choose "Select Data." In the left hand column, highlight the data series you want to include a legend for (Series 1, S
allows you to choose the "Series Name." To include the number in the legend, simply select the cell that has the he
every column in the table. To include the column with the number of years as the horizontal axis, go to the "Horizo
the array that has the correct values for the horizontal axis.

Now that we have calculated the future value of a lump sum with the equation, we will use Excel's FV function to c
investment opportunity:
Interest rate per year:

12%

Number of years:
Initial investment:

3
400

How much will you have at the end of the investment. Using the FV function, we find that you will have:
Future value:

561.97

RWJ Excel Tip

To use the FV function, we entered the following:

The Rate is simply the interest rate, Nper is the number if periods, and Pv is the present value. We left the paymen
detail later. Notice also that we put a negative sign in front of the present value. Excel works like a calculator in tha
positive number we would have simply gotten a negative answer. Since we prefer our answers to show as positive,

Example 4.6: How Much for That Island?

Consider Peter Minuit's purchase of Manhattan Island from the American Indians. Using the FV function, if th
would that investment be worth today?
Purchase price:
Interest rate:
Number of years:
Value today:
RWJ Excel Tip

24
10%
387

250,715,046,098,361,000.00

Two things about the above example. First, we did not want to change the column width for the entire spreadshee
display, we merged 3 cells by using the merge icon:
In merging cells, you simply select the cells you want me
that if you notice, the future value has all zeroes in the last three digits of the dollar amount and in the cents. You m
that while Excel is very precise, it only calculates to 15 significant digits. Although this generally does not create a p
should consider if you are using very large or very small numbers.

If for some reason you do need more accurate calculations, www.precisioncalc.com has an add-in to Excel available

Present Value and Discounting

Now that we have used the FV function, we will skip entering an equation to find the present value, but rather talk
syntax.
Example 4.7: Multiperiod Discounting
Bernard Dumas will receive a lump sum payment in the future. What is the value of that payment today?
Future value:
Number of periods:
Interest rate:

Present value:

10,000
3
8%
7,938.32

RWJ Excel Tip

To use the PV function, we entered the following:

The Rate is simply the interest rate, Nper is the number of periods, and Fv is the future value. We left the payment
Notice also that we put a negative sign in front of the future value. Excel works like a calculator in that it expects ca
number we would have simply gotten a negative answer. Since we prefer our answers to show as positive, we ente

Suppose we want to create a table with the present value factors for different interest rates and periods. A two-wa
up a basic PV problem, with $1 as the future value.
Future value:
Number of periods:
Interest rate:

Present value:

1
2
9%
0.8417

RWJ Excel Tip

To set up a two-way data table, first create the rows and columns for the table. Next, in the upper left hand corner
calculations in the cell. Next, select the cell with the equation you want to use in the data table, go to the Data tab
enter the variables in your table that correspond to the row and column numbers you entered. For this data table

Notice that Excel made our choices absolute references by default. Just hit OK and the data table will be filled in au
corner showing in this case, but remember we could hide this number by right-clicking, selecting Format Cells, choo
semicolon.

Number of Periods

0.8417
0
1
2
3
4
5
6
7
8
9

0%
1.00000
1.00000
1.00000
1.00000
1.00000
1.00000
1.00000
1.00000
1.00000
1.00000

Interest Rate
5%
1.00000
0.95238
0.90703
0.86384
0.82270
0.78353
0.74622
0.71068
0.67684
0.64461

10%
1.00000
0.90909
0.82645
0.75131
0.68301
0.62092
0.56447
0.51316
0.46651
0.42410

RWJ Excel Tip

We have the legend on the left hand side of the table running vertically. To do this, we merged the cells, typed in t
Cells." Using the "Alignment" option, we moved the "Text" wheel to vertical.

Graphically, the present value factors look like this:

Present Value of $1 for Different Periods an

$1.2000

Present value of $1

$1.0000
$0.8000
$0.6000
$0.4000
$0.2000
$1

5
Time (years)

Finding the Interest Rate

Finding the interest rate necessary for a present value to reach a desired future value in a desired time period is a r
Example 4.8: Finding the Rate

A customer of Beatty Corp. wants to buy a tugboat today. Rather than paying immediately, he will pay at some poi
charge to neither gain or lose on the sale?
Present value:
Future value:
Number of periods:
Interest rate:

$
$

38,610
50,000
3
9.00%

RWJ Excel Tip

To find the interest rate, we used the RATE function and entered the following:

NPER is the number of periods, Pv is the present value, and Fv is the present value. We left the payment and type b
also that we put a negative sign in front of the present value. Excel works like a calculator in that it expects cash flo
and the future value negative.

Finding the Number of Periods

Excel also has a built-in function to calculate the number of periods necessary for a lump sum today to reach a desi

You are saving up to buy the Godot Company and have the following information. How long must you wait to buy t
Present value:
Future value:
Interest rate:
Number of periods:

$ 2,300,000
$ 10,000,000
5%
30.12

RWJ Excel Tip

To find the number of periods, we used the NPER function and entered the following:

Rate is the interest rate, Pv is the present value, and Fv is the future value. We left the payment and type blank for
put a negative sign in front of the present value. Excel works like a calculator in that it expects cash flows. We could
value negative.

Future Value with Changing Interest Rates

Suppose you are going to make a lump sum deposit today, and the interest rate you will receive will change every y
have in 6 years?
Present value:
Year
1
2
3
4
5
6

$
100
Interest rate
8%
6%
10%
15%
11%
9%

One way to calculate the future value is to compound the value each year. In year 1, we will receive the year 1 inte
calculate the future value in year 2 at the year 2 interest rate, and so on. Doing this, we find that the value each ye

Year
1
2
3
4
5
6

Value at
year end
$
108.00
114.48
125.93
144.82
160.75
175.21

While this process is more repetitive than difficult, Excel has a function that will calculate the future value of this am
future value is:
Future value:

175.21

RWJ Excel Tip

To use the FVSCHEDULE, we entered:

In this function, Principal is the beginning deposit and Schedule is an array that contains the interest rates for each

Net Present Value

Net present value is the present value of all outflows, plus the present value of all inflows. Unfortunately, as we wi
present value.
Example 4.10: NPV
Finance.com has the following investment opportunity for a new high-speed computer. Should Finance.com make
t
0
1
2
3
Return:
NPV:

Cash flow
$
(50,000)
25,000
20,000
15,000
7%
$3,077.73

RWJ Excel Tip

To calculate the NPV of the project using the NPV function, we entered the following:

Notice one very important thing: We did not include the cash flow at time 0 in the NPV function. The reason is simp
did not truly create a function that calculated the NPV, but rather created a function that calculated the present va
cash flows, we use the NPV function to calculate the present value of the cash flows beyond time 0, then add the c
simply go to the NPV cell above.

We can use the NPV function to find the present value of any series of cash flows. For example, to find the present
text, we would do something like the following:
Interest rate:
1
2
3
4
5
6
7
8
9
10
11
12

$
$
$
$
$
$
$
$
$
$
$
$

12%
Salary
12,000,000
12,000,000
12,000,000
10,900,000
10,900,000
10,000,000
9,000,000
9,600,000
9,000,000
3,000,000
3,000,000
3,000,000

Contract present value:

$60,792,671.66

alue of money. We will begin by using equations before moving to Excel's functions.

Kent. How much will she have at the end of her savings period?

$1 (1 + r )t . In Excel, the carat ( ^ ) raises a number to a power. Of course, compounding only

een simple interest and compound interest, consider the following example:

h of the interest is simple interest and how much is compound interest? We will answer this

Amount
with
Ending
Simple
Amount
Interest
$
550.00 $
550.00
605.00
600.00
665.50
650.00
732.05
700.00
805.26
750.00

n use Excel to draw a graph for us. First we need to set up a table that shows the value with

investment.

est, and Compound Interest

$55.26
$32.05

Compound interest
Amount with simple interest

he compound interest grows as the interest rate changes.

ent to the Insert tab, and then selected Column. We chose the 2-D Stacked Column option. To
t Plot Area and chose the Shadow option.

ed the future interest factor for a variety of interest rates and time periods. We can construct
much more efficient method in the next section.

20%
1.000
1.200
1.440
1.728
2.074

2.488
2.986
3.583
4.300
5.160
6.192

wth. To see the exponential growth in practice, we can graph the future value table. It looks

Different Periods and Rates

0%
5%
10%
15%
20%

10

s text in the header row and column, Excel will automatically use the text in the legend of the
use the numbers in the legend, but rather include them in the graph. To include the numbers
a in the data and ignore the header row and column. Next, right click on the entire chart and
nt to include a legend for (Series 1, Series 2, etc.,) then select "Edit." This brings up a box that
simply select the cell that has the header you want to include. You will need to repeat this for
he horizontal axis, go to the "Horizontal (Category) Axis Labels," select "Edit", then highlight

, we will use Excel's FV function to calculate the future value. Suppose you have the following

e find that you will have:

present value. We left the payment and type blank for now, but we will discuss this in more
e. Excel works like a calculator in that it expects cash flows. If we had left the present value as a
fer our answers to show as positive, we entered a negative in front of the present value.

Indians. Using the FV function, if the purchase price of the island was invested, how much

mn width for the entire spreadsheet to display the future value. To get the future value to
u simply select the cells you want merged into one cell and click on the icon. The second thing is
ollar amount and in the cents. You might think this is strange, and indeed it is. The reason is
gh this generally does not create a problem in most calculations, it is something that you

com has an add-in to Excel available that will calculate to 32,767 digits.

nd the present value, but rather talk about the PV function. The PV syntax is similar to the FV

e value of that payment today?

e future value. We left the payment and type blank for now, but we will discuss these later on.
like a calculator in that it expects cash flows. If we had left the future value as a positive
nswers to show as positive, we entered a negative in front of the future value.

nterest rates and periods. A two-way data table allows us to do this very easily. First, we'll set

Next, in the upper left hand corner, enter the equation you would like to use into the
n the data table, go to the Data tab, What-If Analysis, then Data Table. Excel will prompt you to
ers you entered. For this data table , our entries were:

and the data table will be filled in automatically. We left the calculation in the upper left hand
clicking, selecting Format Cells, choosing Custom, and entering the custom type as a

e
15%
1.00000
0.86957
0.75614
0.65752
0.57175
0.49718
0.43233
0.37594
0.32690
0.28426

20%
1.00000
0.83333
0.69444
0.57870
0.48225
0.40188
0.33490
0.27908
0.23257
0.19381

this, we merged the cells, typed in the text, right clicked on the cells and selected "Format

for Different Periods and Rates

0%
5%
10%
15%
20%

10

Time (years)

value in a desired time period is a relatively simple problem using Excel.

mmediately, he will pay at some point in the future. What interest rate would the Beatty Corp.

lue. We left the payment and type blank for now, but we will discuss these later on. Notice
calculator in that it expects cash flows. We could have also made the present value positive

or a lump sum today to reach a desired amount in the future.

on. How long must you wait to buy the company?

owing:

left the payment and type blank for now, but we will discuss these later on. Notice also that we
that it expects cash flows. We could have also made the present value positive and the future

e you will receive will change every year. With the following assumptions, how much will you

ear 1, we will receive the year 1 interest rate. We will use the value at the end of year 1 to
this, we find that the value each year is:

l calculate the future value of this amount. Using the FVSCHEDULE function, we find that the

contains the interest rates for each period.

all inflows. Unfortunately, as we will see, computer programmers don't understand net

mputer. Should Finance.com make this investment? What is the net present value?

owing:

the NPV function. The reason is simple. When the programmers created the NPV function, they
ction that calculated the present value of cash flows. So, to calculate the NPV of a series of
lows beyond time 0, then add the cash flow at time zero to the result. To see how we did this,

ws. For example, to find the present value of the Sidney Crosby contract we discussed in the

Chapter 4 - Section 3
Compounding Periods
Excel has functions to calculate the effective annual rate and the annual percentage rate.
Example 4.11: EARs

Suppose Fernando Zapatero makes a deposit in an account with the following stated annual interest rate and comp
account?
APR:
Compounding periods per year:
EAR:

24%
12
26.82%

RWJ Excel Tip

To calculate the effective annual rate, we can use the EFFECT function as follows:

In the EFFECT function, Nominal_rate is the APR and Npery is the number of compounding periods per year.

Of course, you may have the EAR and need to find the APR. Remember that the APR is the legally quoted interest r

As a lender, you know the interest rate and the number of compounding periods per year. In order to earn this inte
EAR:
Compounding periods per year:
APR:

18%
12
16.67%

RWJ Excel Tip

To calculate the annual percentage rate, we can use the NOMINAL function as follows:

In the NOMINAL function, Effect_rate is the EAR and Npery is the number of compounding periods per year.

Continuous Compounding

Excel does not have a function for continuous compounding, but it does have a function that calculates the expone

Example 4.14: Continuous Compounding

Suppose Linda DeFond makes a deposit into an account with the following APR. The account has continuous compo
APR:

5.50%

EAR:

5.65%

RWJ Excel Tip

To calculate the exponential function, we use EXP, found under the Math & Trig functions, as follows:

The sole argument of this Excel function is Number, which is the number we want to calculate the exponential valu
this case. Since we are dealing with interest rates, we need to calculate the exponential function for the interest ra

l interest rate and compounding periods per year. What is the EAR of this

periods per year.

egally quoted interest rate on loans.

n order to earn this interest rate, what rate do you quote?

periods per year.

t calculates the exponential function (e ).

t has continuous compounding. What is the EAR of this account?

as follows:

te the exponential value. If you notice, the function returns 1.056540615 in

ction for the interest rate and then subtract 1 (one.)

Chapter 4 - Section 4
Simplifications

Excel does not have built-in functions for perpetuities, growing perpetuities, or growing annuities. Each of these ca
cell. We'll leave this up to you and instead concentrate on the broad array of annuity calculations in this section.
Present Value for Annuity Cash Flows

Finding the present value of an annuity is a simple task in Excel. Remember the Pmt argument in the PV and FV fun
the annuity payment. Finding the present value of an annuity uses the PV function with the annuity payment in th

Example 4.19: Lottery Valuation

Mark Young just won the state lottery and will receive the following set of equal payments beginning one year from
Annual payment:
Number of years for payments:
Interest rate:

Present value:

50,000
20
8%
490,907.37

RWJ Excel Tip

To find the present value of this annuity, we used the following arguments:

Rate is simply the interest rate, Nper is the number of periods, and Pmt is the annuity payment. Since there is no p
negative sign in front of the payment. Excel works like a calculator in that one of the cash flows must be positive an
payment as a positive number we would have gotten a negative answer. Since we prefer our answers to show as p

Future Value for Annuities

We can find the future value of an annuity using the Pmt argument in the FV function. Suppose you are saving for r
will you have when you retire?
Example 4.20: Retirement Investing
Suppose you make the following deposits into a Roth IRA for retirement? How much will you have in your account
Annual savings:
Number of years to save:
Interest rate:

Future value:

3,000
30
6%
237,174.56

RWJ Excel Tip

To find the future value of this annuity, we used the following arguments:

Rate is simply the interest rate, Nper is the number of periods, and Pmt is the annuity payment. Since there is no p
negative sign in front of the payment. Excel works like a calculator in that one of the cash flows must be positive an
payment as a positive number we would have gotten a negative answer. Since we prefer our answers to show as p

The NPER and RATE functions can be used with the future value to find the number of periods or interest rate in th
value.

Finding the Annuity Payment

To find the annuity payment, Excel uses the PMT function. Suppose you are buying a house with the following term
Purchase price:
Number of months for repayment:
Monthly interest rate:

175,000
240
0.50%

Monthly payment:

1,253.75

RWJ Excel Tip

To find the annuity payment, we used the following arguments:

Rate is simply the interest rate, Nper is the number of periods, and Pv is the present value. Since there is no future
sign in front of the present value. Excel works like a calculator in that one of the cash flows must be positive and on
present value as a positive number we would have gotten a negative answer. Since we prefer our answers to show
value.

Finding the Interest Rate

To find the interest rate for an annuity, Excel uses the RATE function. Suppose you are saving for retirement and kn
retirement balance. What interest rate is necessary for you to reach your goal?
Retirement goal:
Annual amount to save:
Number of years to save:
Interest rate:

$
$

1,500,000
4,000
35
11.40%

RWJ Excel Tip

To find the interest rate, we used the following arguments:

Nper is the number of periods, Pmt is the annuity payment, and Fv is the future value. Since there is no present val
sign in front of the payment. Excel requires that one of the cash flows be positive and one of the cash flows be neg
future value negative and would have received the same answer.

Finding the Number of Payments

You ran a little short on your vacation, so you charged on your credit card. With the following assumptions, how lo
Amount owed:
Monthly payment:
Monthly interest rate:

$
$

Number of months to pay off card:

Number of years to pay off card:
To find the number of periods, Excel uses the NPER function.
RWJ Excel Tip
To find the number of periods, we used the following arguments:

3,000
50
1.50%
154.65
12.89

Rate is the interest rate per period, Pmt is the annuity payment, and Pv is the present value. Since there is no futur
negative sign in front of the payment. Excel requires that one of the cash flows be positive and one of the cash flow
the future value negative and would have received the same answer.

Trick 1: A Delayed Annuity

One of the tricks of annuities is getting the timing right. When we are dealing with a delayed annuity, there are a c
Example 4.21 Delayed Annuities
Suppose Danielle Caravello will receive the following annuity payments. What is the value of the payments today?
Number of payments:
Amount of each payment:
Date of first payment:
Interest rate:

4
500
6
10%

First, we can find the present value of the annuity payments, which is:
Annuity present value:

1,584.93

Now that we have the lump sum value, we can find the present value of the lump sum. We also need to remember
the first payment, so we need to subtract one from the date of the first payment. So, the value of the annuity toda
Value today:

984.12

RWJ Excel Tip

Of course, we could solve this question with one calculation cell. Excel allows you to "nest" one function inside ano
calculation inside the present value of the lump sum without the intermediate step of calculating the present value
results in a cleaner looking spreadsheet.

Value today:

984.12

Of course, there is another way to calculate the value of this annuity today. Suppose we set up the cash flows in a t
Year
1
2
3
4
5
6
7
8
9
10
Interest rate

Beginning
$
$
$
$
$
$
$
$
$
$

500
500
500
500
10%

With the cash flows set up like this, we can use the NPV function to find the value of the cash flows today, which w
Value today:

984.12

Trick 2: Annuity Due

So far, we have talked about ordinary annuities, that is, the payments occur at the end of the period. What about a
the period.

Example 4.22: Annuity Due

Suppose Mark DeYoung, who won the lottery in a previous example, still wins the lottery but now the payments ar
winnings today?
Beginning of period annuity deposit:
Number of years:
Interest rate:

50,000
20
8%

Present value:

$ 530,179.96

RWJ Excel Tip

To calculate the present value of an annuity due, we use the PV function and set the type of payments to beginnin

In the FV and PV functions, the Type represents the payment type. If this argument is left blank or a 0 (zero) is ente
entered, Excel uses beginning of period payments.

Example 4.23: Infrequent Annuities

Ann Chen is going to received the following annuity payments. What is the value of the payments today?
Annuity payment:
Years between payments:
Years for payments:
EAR:

450
2
20
6%

We can answer this problem in two steps. First, we need to find the effective rate for the years between the annui
Effective multi-year rate:

12.36%

Now we can use this rate to find the value today, which is:
Value today:

2,505.57

Of course, we could always nest the functions to calculate the value today in one cell:
Value today:

2,505.57

rowing annuities. Each of these can be calculated by inputting the equations directly into a
nuity calculations in this section.

Pmt argument in the PV and FV functions that we left blank in Section 4.2? The Pmt stands for
on with the annuity payment in the Pmt argument.

payments beginning one year from now. What is the value of the payments today?

nuity payment. Since there is no present value, we left this blank. Notice also that we put a
the cash flows must be positive and one of the cash flows must be negative. If we had left the
e prefer our answers to show as positive, we entered a negative in front of the payment.

ction. Suppose you are saving for retirement. Based on the following assumptions, how much

much will you have in your account when your golden years start?

nuity payment. Since there is no present value, we left this blank. Notice also that we put a
the cash flows must be positive and one of the cash flows must be negative. If we had left the
e prefer our answers to show as positive, we entered a negative in front of the payment.

ber of periods or interest rate in the same manner we used these functions with the present

ng a house with the following terms. What is your monthly mortgage payment?

sent value. Since there is no future value, we left this blank. Notice also that we put a negative
cash flows must be positive and one of the cash flows must be negative. If we had left the
nce we prefer our answers to show as positive, we entered a negative in front of the present

ou are saving for retirement and know how much you will save every year, as well as a target

value. Since there is no present value, we left this blank. Notice also that we put a negative
e and one of the cash flows be negative. We could have made the payment positive and the

the following assumptions, how long will it take you to pay off your credit card?

esent value. Since there is no future value, we left this blank. Notice also that we put a
e positive and one of the cash flows negative. We could have made the payment positive and

th a delayed annuity, there are a couple of ways to handle the calculation in Excel.

the value of the payments today?

p sum. We also need to remember that the present value of an annuity is one period before
t. So, the value of the annuity today is:

u to "nest" one function inside another. Below, we nested the present value of the annuity
tep of calculating the present value of the annuity. Although this is a little more difficult, it also

pose we set up the cash flows in a table like this:

e of the cash flows today, which will be:

he end of the period. What about annuities due, where the payments occur at the beginning of

e lottery but now the payments are in the form of an annuity due. What is the value of his

the type of payments to beginning of period like this:

ent is left blank or a 0 (zero) is entered, Excel uses end of period payments. If a 1 (one) is

of the payments today?

e for the years between the annuity payments, which is:

e cell:

Chapter 4 - Section 5
Loan Amortization
Equal Principal Payment

Amortization tables are an excellent application of Excel's abilities. Because an amortization table is repetitive, onc
fill in the rest of the amortization table. Suppose we have the following 15 year loan that requires equal principal p
Loan amount:
Interest rate:

350,000
9%

This means that the equal annual principal payments will be:
Principal payments:

23,333.33

So, the equal principal payment amortization table will look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Total

Beginning
Total
Interest
Principal
Balance
Payment
Paid
Payment
$ 350,000.00 $ 54,833.33 $ 31,500.00 $ 23,333.33
326,666.67
52,733.33
29,400.00
23,333.33
303,333.33
50,633.33
27,300.00
23,333.33
280,000.00
48,533.33
25,200.00
23,333.33
256,666.67
46,433.33
23,100.00
23,333.33
233,333.33
44,333.33
21,000.00
23,333.33
210,000.00
42,233.33
18,900.00
23,333.33
186,666.67
40,133.33
16,800.00
23,333.33
163,333.33
38,033.33
14,700.00
23,333.33
140,000.00
35,933.33
12,600.00
23,333.33
116,666.67
33,833.33
10,500.00
23,333.33
93,333.33
31,733.33
8,400.00
23,333.33
70,000.00
29,633.33
6,300.00
23,333.33
46,666.67
27,533.33
4,200.00
23,333.33
23,333.33
25,433.33
2,100.00
23,333.33
$ 602,000.00 $ 252,000.00 $ 350,000.00

RWJ Excel Tip

To create the table, we first set up the header row and column. The beginning balance references the loan amount
earlier calculation of the principal payment, and the interest paid is the beginning balance multiplied by the interes
this for the second period, except that the beginning balance in period 2 is the ending balance in period 1. Since we
and interest rate, we can copy and paste the second row to fill in the table. To find the total payments, total intere
sum button.

Equal Payment

Creating an equal payment amortization schedule is similar to the equal principal amortization schedule. First, we n
which we can calculate using the PMT function we discussed earlier. The loan payment will be:
Loan amount:
Interest rate:

350,000
9%

This means that the equal annual payments will be:

Equal payments:

43,420.61

So, the equal annual payment amortization table will look like this:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

Beginning
Total
Interest
Principal
Balance
Payment
Paid
Payment
$ 350,000.00 $ 43,420.61 $ 31,500.00 $ 11,920.61
338,079.39
43,420.61
30,427.15
12,993.46
325,085.93
43,420.61
29,257.73
14,162.88
310,923.05
43,420.61
27,983.07
15,437.53
295,485.52
43,420.61
26,593.70
16,826.91
278,658.61
43,420.61
25,079.27
18,341.33
260,317.27
43,420.61
23,428.55
19,992.05
240,325.22
43,420.61
21,629.27
21,791.34
218,533.88
43,420.61
19,668.05
23,752.56
194,781.32
43,420.61
17,530.32
25,890.29
168,891.03
43,420.61
15,200.19
28,220.42
140,670.61
43,420.61
12,660.35
30,760.25
109,910.36
43,420.61
9,891.93
33,528.68
76,381.68
43,420.61
6,874.35
36,546.26
39,835.42
43,420.61
3,585.19
39,835.42
$ 651,309.13 $ 301,309.13 $ 350,000.00

RWJ Excel Tip

To create the table, we first set up the header row and column. The beginning balance referenced the loan amoun
calculation of the payment amount, the interest payment is the beginning balance multiplied by the interest rate (w
is the total payment minus the interest paid. The ending balance is the beginning balance minus the principal paym
that the beginning balance in period 2 is the ending balance in period 1. Since we have used absolute references fo
paste the second row to fill in the table. To find the total payments, total interest payments, and total principal pay

RWJ Excel Tip

Loan amortization tables are so common that Excel has a built-in worksheet to calculate a loan amortization. To fin
select Insert, then the Spreadsheet Solutions tab. Below you will see the built-in spreadsheet options. We selected
Loan Amortization worksheet. We entered the values in the table at the top and the entire loan amortization table

"Balloon" or "Bullet" Loans

Balloon loans are loans that are amortized over a relatively long schedule, but at some point during the life of the l
going to be a little fancier here and set up the problem so that it works with any repayment schedule, whether ann
loan with the following characteristics:

Example 4.28: Partial Amortization, or "Bite the Bullet"

Suppose we have a commercial mortgage with the following characteristics. What will the monthly payment be? H
Loan amount:
Amortization period (years):
Time balloon payment due (years):
Payments per year:
Interest rate (APR):

100,000
20
5
12
12.00%

So, based on the original amortization schedule, the payments will be:
Monthly payment:

1,101.09

The balloon payment is the present value of the remaining payments, so the balloon payment will be:
Balloon payment:

91,744.33

Of course, we could have nested the functions to find the balloon payment as well like this:
Balloon payment:

91,744.33

amortization table is repetitive, once we get the first couple of rows, we can copy and paste to
loan that requires equal principal payments each year:

Ending
Balance
$ 326,666.67
303,333.33
280,000.00
256,666.67
233,333.33
210,000.00
186,666.67
163,333.33
140,000.00
116,666.67
93,333.33
70,000.00
46,666.67
23,333.33
-

balance references the loan amount, the principal payment is an absolute reference to the
ng balance multiplied by the interest rate (which is an absolute reference.) We then repeated
ending balance in period 1. Since we have used absolute references for the principal payment
ind the total payments, total interest payments, and total principal payments, we used the

al amortization schedule. First, we need to calculate the loan payment for the 15 year loan,
ayment will be:

Ending
Balance
$ 338,079.39
325,085.93
310,923.05
295,485.52
278,658.61
260,317.27
240,325.22
218,533.88
194,781.32
168,891.03
140,670.61
109,910.36
76,381.68
39,835.42
0.00

balance referenced the loan amount, the total payment is an absolute reference to the earlier
nce multiplied by the interest rate (which is an absolute reference,) and the principal payment
ng balance minus the principal payment. We then repeated this for the second period, except
we have used absolute references for the total payment and interest rate, we can copy and
st payments, and total principal payments over the life of the loan, we used the sum button.

calculate a loan amortization. To find this worksheet, right-click on one of the worksheet tabs,
n spreadsheet options. We selected the Loan Amortization worksheet and Excel inserted the
d the entire loan amortization table was constructed automatically.

at some point during the life of the loan, the remaining principal of the loan is repaid. We are
y repayment schedule, whether annually, monthly, or any other period. Suppose we have a

hat will the monthly payment be? How big will the balloon payment be?

lloon payment will be:

well like this:

Loan Amortization Schedule

Loan amount $
Annual interest rate
Loan period in years
Number of payments per year
Start date of loan
Optional extra payments

Enter values
250,000.00
3.10 %
30
12
8/1/2013

Loan summary
Scheduled payment
Scheduled number of payments
Actual number of payments
Total early payments
Total interest

Lender name:
Pmt.
No.
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
31
32
33
34
35
36
37

Payment Date
9/1/2013
10/1/2013
11/1/2013
12/1/2013
1/1/2014
2/1/2014
3/1/2014
4/1/2014
5/1/2014
6/1/2014
7/1/2014
8/1/2014
9/1/2014
10/1/2014
11/1/2014
12/1/2014
1/1/2015
2/1/2015
3/1/2015
4/1/2015
5/1/2015
6/1/2015
7/1/2015
8/1/2015
9/1/2015
10/1/2015
11/1/2015
12/1/2015
1/1/2016
2/1/2016
3/1/2016
4/1/2016
5/1/2016
6/1/2016
7/1/2016
8/1/2016
9/1/2016

Scheduled
Payment

Beginning Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

250,000.00
249,578.29
249,155.50
248,731.61
248,306.62
247,880.54
247,453.36
247,025.07
246,595.68
246,165.17
245,733.56
245,300.83
244,866.98
244,432.02
243,995.92
243,558.71
243,120.36
242,680.88
242,240.26
241,798.51
241,355.61
240,911.58
240,466.39
240,020.05
239,572.56
239,123.92
238,674.11
238,223.15
237,771.02
237,317.72
236,863.25
236,407.60
235,950.78
235,492.78
235,033.60
234,573.23
234,111.67

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Extra Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Total Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Principal
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

421.71
422.80
423.89
424.98
426.08
427.18
428.29
429.39
430.50
431.61
432.73
433.85
434.97
436.09
437.22
438.35
439.48
440.62
441.75
442.89
444.04
445.19
446.34
447.49
448.65
449.80
450.97
452.13
453.30
454.47
455.64
456.82
458.00
459.18
460.37
461.56
462.75

Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

645.83
644.74
643.65
642.56
641.46
640.36
639.25
638.15
637.04
635.93
634.81
633.69
632.57
631.45
630.32
629.19
628.06
626.93
625.79
624.65
623.50
622.35
621.20
620.05
618.90
617.74
616.57
615.41
614.24
613.07
611.90
610.72
609.54
608.36
607.17
605.98
604.79

Ending Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

249,578.29
249,155.50
248,731.61
248,306.62
247,880.54
247,453.36
247,025.07
246,595.68
246,165.17
245,733.56
245,300.83
244,866.98
244,432.02
243,995.92
243,558.71
243,120.36
242,680.88
242,240.26
241,798.51
241,355.61
240,911.58
240,466.39
240,020.05
239,572.56
239,123.92
238,674.11
238,223.15
237,771.02
237,317.72
236,863.25
236,407.60
235,950.78
235,492.78
235,033.60
234,573.23
234,111.67
233,648.91

Pmt.
No.
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88

Payment Date
10/1/2016
11/1/2016
12/1/2016
1/1/2017
2/1/2017
3/1/2017
4/1/2017
5/1/2017
6/1/2017
7/1/2017
8/1/2017
9/1/2017
10/1/2017
11/1/2017
12/1/2017
1/1/2018
2/1/2018
3/1/2018
4/1/2018
5/1/2018
6/1/2018
7/1/2018
8/1/2018
9/1/2018
10/1/2018
11/1/2018
12/1/2018
1/1/2019
2/1/2019
3/1/2019
4/1/2019
5/1/2019
6/1/2019
7/1/2019
8/1/2019
9/1/2019
10/1/2019
11/1/2019
12/1/2019
1/1/2020
2/1/2020
3/1/2020
4/1/2020
5/1/2020
6/1/2020
7/1/2020
8/1/2020
9/1/2020
10/1/2020
11/1/2020
12/1/2020

Scheduled
Payment

Beginning Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

233,648.91
233,184.96
232,719.82
232,253.47
231,785.92
231,317.16
230,847.18
230,376.00
229,903.60
229,429.97
228,955.13
228,479.05
228,001.75
227,523.21
227,043.44
226,562.43
226,080.17
225,596.67
225,111.92
224,625.92
224,138.66
223,650.15
223,160.37
222,669.33
222,177.01
221,683.43
221,188.57
220,692.43
220,195.02
219,696.31
219,196.32
218,695.04
218,192.46
217,688.58
217,183.40
216,676.92
216,169.12
215,660.02
215,149.60
214,637.86
214,124.80
213,610.42
213,094.70
212,577.66
212,059.28
211,539.55
211,018.49
210,496.08
209,972.32
209,447.21
208,920.74

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Extra Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Total Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Principal
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

463.95
465.15
466.35
467.55
468.76
469.97
471.19
472.40
473.62
474.85
476.07
477.30
478.54
479.77
481.01
482.25
483.50
484.75
486.00
487.26
488.52
489.78
491.04
492.31
493.58
494.86
496.14
497.42
498.70
499.99
501.28
502.58
503.88
505.18
506.48
507.79
509.10
510.42
511.74
513.06
514.39
515.71
517.05
518.38
519.72
521.06
522.41
523.76
525.11
526.47
527.83

Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

603.59
602.39
601.19
599.99
598.78
597.57
596.36
595.14
593.92
592.69
591.47
590.24
589.00
587.77
586.53
585.29
584.04
582.79
581.54
580.28
579.02
577.76
576.50
575.23
573.96
572.68
571.40
570.12
568.84
567.55
566.26
564.96
563.66
562.36
561.06
559.75
558.44
557.12
555.80
554.48
553.16
551.83
550.49
549.16
547.82
546.48
545.13
543.78
542.43
541.07
539.71

Ending Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

233,184.96
232,719.82
232,253.47
231,785.92
231,317.16
230,847.18
230,376.00
229,903.60
229,429.97
228,955.13
228,479.05
228,001.75
227,523.21
227,043.44
226,562.43
226,080.17
225,596.67
225,111.92
224,625.92
224,138.66
223,650.15
223,160.37
222,669.33
222,177.01
221,683.43
221,188.57
220,692.43
220,195.02
219,696.31
219,196.32
218,695.04
218,192.46
217,688.58
217,183.40
216,676.92
216,169.12
215,660.02
215,149.60
214,637.86
214,124.80
213,610.42
213,094.70
212,577.66
212,059.28
211,539.55
211,018.49
210,496.08
209,972.32
209,447.21
208,920.74
208,392.91

Pmt.
No.
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139

Payment Date
1/1/2021
2/1/2021
3/1/2021
4/1/2021
5/1/2021
6/1/2021
7/1/2021
8/1/2021
9/1/2021
10/1/2021
11/1/2021
12/1/2021
1/1/2022
2/1/2022
3/1/2022
4/1/2022
5/1/2022
6/1/2022
7/1/2022
8/1/2022
9/1/2022
10/1/2022
11/1/2022
12/1/2022
1/1/2023
2/1/2023
3/1/2023
4/1/2023
5/1/2023
6/1/2023
7/1/2023
8/1/2023
9/1/2023
10/1/2023
11/1/2023
12/1/2023
1/1/2024
2/1/2024
3/1/2024
4/1/2024
5/1/2024
6/1/2024
7/1/2024
8/1/2024
9/1/2024
10/1/2024
11/1/2024
12/1/2024
1/1/2025
2/1/2025
3/1/2025

Scheduled
Payment

Beginning Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

208,392.91
207,863.72
207,333.16
206,801.23
206,267.92
205,733.24
205,197.18
204,659.73
204,120.89
203,580.66
203,039.04
202,496.02
201,951.59
201,405.76
200,858.51
200,309.86
199,759.78
199,208.29
198,655.37
198,101.02
197,545.24
196,988.03
196,429.37
195,869.27
195,307.73
194,744.73
194,180.28
193,614.37
193,047.00
192,478.16
191,907.86
191,336.08
190,762.82
190,188.09
189,611.87
189,034.15
188,454.95
187,874.25
187,292.05
186,708.35
186,123.14
185,536.42
184,948.18
184,358.42
183,767.14
183,174.33
182,579.99
181,984.11
181,386.70
180,787.74
180,187.23

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Extra Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Total Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Principal
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

529.19
530.56
531.93
533.30
534.68
536.06
537.45
538.84
540.23
541.62
543.02
544.43
545.83
547.24
548.66
550.07
551.49
552.92
554.35
555.78
557.22
558.66
560.10
561.55
563.00
564.45
565.91
567.37
568.84
570.31
571.78
573.26
574.74
576.22
577.71
579.20
580.70
582.20
583.70
585.21
586.72
588.24
589.76
591.28
592.81
594.34
595.88
597.42
598.96
600.51
602.06

Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

538.35
536.98
535.61
534.24
532.86
531.48
530.09
528.70
527.31
525.92
524.52
523.11
521.71
520.30
518.88
517.47
516.05
514.62
513.19
511.76
510.33
508.89
507.44
506.00
504.54
503.09
501.63
500.17
498.70
497.24
495.76
494.28
492.80
491.32
489.83
488.34
486.84
485.34
483.84
482.33
480.82
479.30
477.78
476.26
474.73
473.20
471.66
470.13
468.58
467.03
465.48

Ending Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

207,863.72
207,333.16
206,801.23
206,267.92
205,733.24
205,197.18
204,659.73
204,120.89
203,580.66
203,039.04
202,496.02
201,951.59
201,405.76
200,858.51
200,309.86
199,759.78
199,208.29
198,655.37
198,101.02
197,545.24
196,988.03
196,429.37
195,869.27
195,307.73
194,744.73
194,180.28
193,614.37
193,047.00
192,478.16
191,907.86
191,336.08
190,762.82
190,188.09
189,611.87
189,034.15
188,454.95
187,874.25
187,292.05
186,708.35
186,123.14
185,536.42
184,948.18
184,358.42
183,767.14
183,174.33
182,579.99
181,984.11
181,386.70
180,787.74
180,187.23
179,585.17

Pmt.
No.
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190

Payment Date
4/1/2025
5/1/2025
6/1/2025
7/1/2025
8/1/2025
9/1/2025
10/1/2025
11/1/2025
12/1/2025
1/1/2026
2/1/2026
3/1/2026
4/1/2026
5/1/2026
6/1/2026
7/1/2026
8/1/2026
9/1/2026
10/1/2026
11/1/2026
12/1/2026
1/1/2027
2/1/2027
3/1/2027
4/1/2027
5/1/2027
6/1/2027
7/1/2027
8/1/2027
9/1/2027
10/1/2027
11/1/2027
12/1/2027
1/1/2028
2/1/2028
3/1/2028
4/1/2028
5/1/2028
6/1/2028
7/1/2028
8/1/2028
9/1/2028
10/1/2028
11/1/2028
12/1/2028
1/1/2029
2/1/2029
3/1/2029
4/1/2029
5/1/2029
6/1/2029

Scheduled
Payment

Beginning Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

179,585.17
178,981.56
178,376.39
177,769.65
177,161.35
176,551.48
175,940.03
175,327.00
174,712.39
174,096.19
173,478.39
172,859.00
172,238.02
171,615.42
170,991.22
170,365.41
169,737.98
169,108.93
168,478.25
167,845.95
167,212.01
166,576.43
165,939.21
165,300.35
164,659.83
164,017.66
163,373.83
162,728.34
162,081.18
161,432.35
160,781.84
160,129.65
159,475.78
158,820.22
158,162.96
157,504.01
156,843.36
156,180.99
155,516.92
154,851.13
154,183.62
153,514.39
152,843.43
152,170.73
151,496.30
150,820.12
150,142.20
149,462.53
148,781.10
148,097.91
147,412.95

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Extra Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Total Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Principal
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

603.61
605.17
606.74
608.30
609.87
611.45
613.03
614.61
616.20
617.79
619.39
620.99
622.59
624.20
625.81
627.43
629.05
630.68
632.31
633.94
635.58
637.22
638.86
640.52
642.17
643.83
645.49
647.16
648.83
650.51
652.19
653.87
655.56
657.26
658.95
660.66
662.36
664.07
665.79
667.51
669.23
670.96
672.70
674.43
676.18
677.92
679.67
681.43
683.19
684.95
686.72

Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

463.93
462.37
460.81
459.24
457.67
456.09
454.51
452.93
451.34
449.75
448.15
446.55
444.95
443.34
441.73
440.11
438.49
436.86
435.24
433.60
431.96
430.32
428.68
427.03
425.37
423.71
422.05
420.38
418.71
417.03
415.35
413.67
411.98
410.29
408.59
406.89
405.18
403.47
401.75
400.03
398.31
396.58
394.85
393.11
391.37
389.62
387.87
386.11
384.35
382.59
380.82

Ending Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

178,981.56
178,376.39
177,769.65
177,161.35
176,551.48
175,940.03
175,327.00
174,712.39
174,096.19
173,478.39
172,859.00
172,238.02
171,615.42
170,991.22
170,365.41
169,737.98
169,108.93
168,478.25
167,845.95
167,212.01
166,576.43
165,939.21
165,300.35
164,659.83
164,017.66
163,373.83
162,728.34
162,081.18
161,432.35
160,781.84
160,129.65
159,475.78
158,820.22
158,162.96
157,504.01
156,843.36
156,180.99
155,516.92
154,851.13
154,183.62
153,514.39
152,843.43
152,170.73
151,496.30
150,820.12
150,142.20
149,462.53
148,781.10
148,097.91
147,412.95
146,726.23

Pmt.
No.
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241

Payment Date
7/1/2029
8/1/2029
9/1/2029
10/1/2029
11/1/2029
12/1/2029
1/1/2030
2/1/2030
3/1/2030
4/1/2030
5/1/2030
6/1/2030
7/1/2030
8/1/2030
9/1/2030
10/1/2030
11/1/2030
12/1/2030
1/1/2031
2/1/2031
3/1/2031
4/1/2031
5/1/2031
6/1/2031
7/1/2031
8/1/2031
9/1/2031
10/1/2031
11/1/2031
12/1/2031
1/1/2032
2/1/2032
3/1/2032
4/1/2032
5/1/2032
6/1/2032
7/1/2032
8/1/2032
9/1/2032
10/1/2032
11/1/2032
12/1/2032
1/1/2033
2/1/2033
3/1/2033
4/1/2033
5/1/2033
6/1/2033
7/1/2033
8/1/2033
9/1/2033

Scheduled
Payment

Beginning Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

146,726.23
146,037.73
145,347.45
144,655.39
143,961.54
143,265.90
142,568.47
141,869.23
141,168.18
140,465.33
139,760.65
139,054.16
138,345.84
137,635.70
136,923.71
136,209.89
135,494.23
134,776.71
134,057.34
133,336.12
132,613.03
131,888.07
131,161.24
130,432.53
129,701.94
128,969.47
128,235.10
127,498.83
126,760.66
126,020.58
125,278.60
124,534.69
123,788.87
123,041.11
122,291.43
121,539.81
120,786.24
120,030.73
119,273.27
118,513.85
117,752.47
116,989.13
116,223.81
115,456.51
114,687.23
113,915.97
113,142.71
112,367.45
111,590.19
110,810.93
110,029.65

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Extra Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Total Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Principal
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

688.50
690.28
692.06
693.85
695.64
697.44
699.24
701.05
702.86
704.67
706.49
708.32
710.15
711.98
713.82
715.67
717.51
719.37
721.23
723.09
724.96
726.83
728.71
730.59
732.48
734.37
736.27
738.17
740.08
741.99
743.90
745.83
747.75
749.68
751.62
753.56
755.51
757.46
759.42
761.38
763.35
765.32
767.30
769.28
771.27
773.26
775.26
777.26
779.27
781.28
783.30

Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

379.04
377.26
375.48
373.69
371.90
370.10
368.30
366.50
364.68
362.87
361.05
359.22
357.39
355.56
353.72
351.88
350.03
348.17
346.31
344.45
342.58
340.71
338.83
336.95
335.06
333.17
331.27
329.37
327.47
325.55
323.64
321.71
319.79
317.86
315.92
313.98
312.03
310.08
308.12
306.16
304.19
302.22
300.24
298.26
296.28
294.28
292.29
290.28
288.27
286.26
284.24

Ending Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

146,037.73
145,347.45
144,655.39
143,961.54
143,265.90
142,568.47
141,869.23
141,168.18
140,465.33
139,760.65
139,054.16
138,345.84
137,635.70
136,923.71
136,209.89
135,494.23
134,776.71
134,057.34
133,336.12
132,613.03
131,888.07
131,161.24
130,432.53
129,701.94
128,969.47
128,235.10
127,498.83
126,760.66
126,020.58
125,278.60
124,534.69
123,788.87
123,041.11
122,291.43
121,539.81
120,786.24
120,030.73
119,273.27
118,513.85
117,752.47
116,989.13
116,223.81
115,456.51
114,687.23
113,915.97
113,142.71
112,367.45
111,590.19
110,810.93
110,029.65
109,246.35

Pmt.
No.
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292

Payment Date
10/1/2033
11/1/2033
12/1/2033
1/1/2034
2/1/2034
3/1/2034
4/1/2034
5/1/2034
6/1/2034
7/1/2034
8/1/2034
9/1/2034
10/1/2034
11/1/2034
12/1/2034
1/1/2035
2/1/2035
3/1/2035
4/1/2035
5/1/2035
6/1/2035
7/1/2035
8/1/2035
9/1/2035
10/1/2035
11/1/2035
12/1/2035
1/1/2036
2/1/2036
3/1/2036
4/1/2036
5/1/2036
6/1/2036
7/1/2036
8/1/2036
9/1/2036
10/1/2036
11/1/2036
12/1/2036
1/1/2037
2/1/2037
3/1/2037
4/1/2037
5/1/2037
6/1/2037
7/1/2037
8/1/2037
9/1/2037
10/1/2037
11/1/2037
12/1/2037

Scheduled
Payment

Beginning Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

109,246.35
108,461.03
107,673.68
106,884.30
106,092.87
105,299.40
104,503.89
103,706.31
102,906.68
102,104.98
101,301.21
100,495.37
99,687.44
98,877.42
98,065.32
97,251.11
96,434.80
95,616.38
94,795.85
93,973.20
93,148.42
92,321.52
91,492.47
90,661.29
89,827.95
88,992.47
88,154.82
87,315.02
86,473.04
85,628.89
84,782.55
83,934.03
83,083.32
82,230.41
81,375.30
80,517.98
79,658.44
78,796.69
77,932.70
77,066.49
76,198.04
75,327.34
74,454.40
73,579.20
72,701.73
71,822.01
70,940.00
70,055.73
69,169.16
68,280.31
67,389.16

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Extra Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Total Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Principal
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

785.32
787.35
789.38
791.42
793.47
795.52
797.57
799.63
801.70
803.77
805.85
807.93
810.02
812.11
814.21
816.31
818.42
820.53
822.65
824.78
826.91
829.04
831.19
833.33
835.49
837.64
839.81
841.98
844.15
846.33
848.52
850.71
852.91
855.11
857.32
859.54
861.76
863.98
866.21
868.45
870.70
872.95
875.20
877.46
879.73
882.00
884.28
886.56
888.85
891.15
893.45

Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

282.22
280.19
278.16
276.12
274.07
272.02
269.97
267.91
265.84
263.77
261.69
259.61
257.53
255.43
253.34
251.23
249.12
247.01
244.89
242.76
240.63
238.50
236.36
234.21
232.06
229.90
227.73
225.56
223.39
221.21
219.02
216.83
214.63
212.43
210.22
208.00
205.78
203.56
201.33
199.09
196.84
194.60
192.34
190.08
187.81
185.54
183.26
180.98
178.69
176.39
174.09

Ending Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

108,461.03
107,673.68
106,884.30
106,092.87
105,299.40
104,503.89
103,706.31
102,906.68
102,104.98
101,301.21
100,495.37
99,687.44
98,877.42
98,065.32
97,251.11
96,434.80
95,616.38
94,795.85
93,973.20
93,148.42
92,321.52
91,492.47
90,661.29
89,827.95
88,992.47
88,154.82
87,315.02
86,473.04
85,628.89
84,782.55
83,934.03
83,083.32
82,230.41
81,375.30
80,517.98
79,658.44
78,796.69
77,932.70
77,066.49
76,198.04
75,327.34
74,454.40
73,579.20
72,701.73
71,822.01
70,940.00
70,055.73
69,169.16
68,280.31
67,389.16
66,495.71

Pmt.
No.
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343

Payment Date
1/1/2038
2/1/2038
3/1/2038
4/1/2038
5/1/2038
6/1/2038
7/1/2038
8/1/2038
9/1/2038
10/1/2038
11/1/2038
12/1/2038
1/1/2039
2/1/2039
3/1/2039
4/1/2039
5/1/2039
6/1/2039
7/1/2039
8/1/2039
9/1/2039
10/1/2039
11/1/2039
12/1/2039
1/1/2040
2/1/2040
3/1/2040
4/1/2040
5/1/2040
6/1/2040
7/1/2040
8/1/2040
9/1/2040
10/1/2040
11/1/2040
12/1/2040
1/1/2041
2/1/2041
3/1/2041
4/1/2041
5/1/2041
6/1/2041
7/1/2041
8/1/2041
9/1/2041
10/1/2041
11/1/2041
12/1/2041
1/1/2042
2/1/2042
3/1/2042

Scheduled
Payment

Beginning Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

66,495.71
65,599.94
64,701.87
63,801.48
62,898.76
61,993.70
61,086.31
60,176.58
59,264.49
58,350.05
57,433.25
56,514.08
55,592.53
54,668.60
53,742.29
52,813.58
51,882.48
50,948.97
50,013.04
49,074.70
48,133.94
47,190.74
46,245.11
45,297.04
44,346.51
43,393.53
42,438.09
41,480.18
40,519.80
39,556.93
38,591.58
37,623.74
36,653.39
35,680.54
34,705.17
33,727.28
32,746.87
31,763.93
30,778.44
29,790.41
28,799.83
27,806.69
26,810.98
25,812.70
24,811.84
23,808.40
22,802.36
21,793.73
20,782.49
19,768.64
18,752.16

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Extra Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Total Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Principal
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

895.76
898.07
900.39
902.72
905.05
907.39
909.73
912.08
914.44
916.80
919.17
921.55
923.93
926.31
928.71
931.11
933.51
935.92
938.34
940.76
943.19
945.63
948.07
950.52
952.98
955.44
957.91
960.38
962.86
965.35
967.85
970.35
972.85
975.37
977.89
980.41
982.94
985.48
988.03
990.58
993.14
995.71
998.28
1,000.86
1,003.44
1,006.04
1,008.63
1,011.24
1,013.85
1,016.47
1,019.10

Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

171.78
169.47
167.15
164.82
162.49
160.15
157.81
155.46
153.10
150.74
148.37
145.99
143.61
141.23
138.83
136.44
134.03
131.62
129.20
126.78
124.35
121.91
119.47
117.02
114.56
112.10
109.63
107.16
104.68
102.19
99.69
97.19
94.69
92.17
89.66
87.13
84.60
82.06
79.51
76.96
74.40
71.83
69.26
66.68
64.10
61.51
58.91
56.30
53.69
51.07
48.44

Ending Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

65,599.94
64,701.87
63,801.48
62,898.76
61,993.70
61,086.31
60,176.58
59,264.49
58,350.05
57,433.25
56,514.08
55,592.53
54,668.60
53,742.29
52,813.58
51,882.48
50,948.97
50,013.04
49,074.70
48,133.94
47,190.74
46,245.11
45,297.04
44,346.51
43,393.53
42,438.09
41,480.18
40,519.80
39,556.93
38,591.58
37,623.74
36,653.39
35,680.54
34,705.17
33,727.28
32,746.87
31,763.93
30,778.44
29,790.41
28,799.83
27,806.69
26,810.98
25,812.70
24,811.84
23,808.40
22,802.36
21,793.73
20,782.49
19,768.64
18,752.16
17,733.07

Pmt.
No.
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360

Payment Date
4/1/2042
5/1/2042
6/1/2042
7/1/2042
8/1/2042
9/1/2042
10/1/2042
11/1/2042
12/1/2042
1/1/2043
2/1/2043
3/1/2043
4/1/2043
5/1/2043
6/1/2043
7/1/2043
8/1/2043

Scheduled
Payment

Beginning Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

17,733.07
16,711.34
15,686.97
14,659.95
13,630.28
12,597.95
11,562.95
10,525.28
9,484.93
8,441.90
7,396.16
6,347.73
5,296.59
4,242.73
3,186.15
2,126.84
1,064.79

$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54

Extra Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

Total Payment
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,067.54
1,064.79

Principal
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

1,021.73
1,024.37
1,027.02
1,029.67
1,032.33
1,035.00
1,037.67
1,040.35
1,043.04
1,045.73
1,048.43
1,051.14
1,053.86
1,056.58
1,059.31
1,062.05
1,062.04

Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

45.81
43.17
40.52
37.87
35.21
32.54
29.87
27.19
24.50
21.81
19.11
16.40
13.68
10.96
8.23
5.49
2.75

Ending Balance
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

16,711.34
15,686.97
14,659.95
13,630.28
12,597.95
11,562.95
10,525.28
9,484.93
8,441.90
7,396.16
6,347.73
5,296.59
4,242.73
3,186.15
2,126.84
1,064.79
-

$
$

Loan summary
1,067.54
360
360
134,314.76

Cumulative Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

645.83
1,290.58
1,934.23
2,576.79
3,218.24
3,858.60
4,497.86
5,136.01
5,773.04
6,408.97
7,043.78
7,677.48
8,310.05
8,941.50
9,571.82
10,201.01
10,829.08
11,456.00
12,081.79
12,706.43
13,329.94
13,952.29
14,573.50
15,193.55
15,812.44
16,430.18
17,046.76
17,662.17
18,276.41
18,889.48
19,501.37
20,112.09
20,721.63
21,329.99
21,937.16
22,543.14
23,147.93

Cumulative Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

23,751.52
24,353.92
24,955.11
25,555.10
26,153.88
26,751.45
27,347.80
27,942.94
28,536.86
29,129.55
29,721.02
30,311.26
30,900.26
31,488.03
32,074.56
32,659.85
33,243.89
33,826.68
34,408.22
34,988.50
35,567.53
36,145.29
36,721.79
37,297.01
37,870.97
38,443.65
39,015.06
39,585.18
40,154.02
40,721.57
41,287.82
41,852.79
42,416.45
42,978.81
43,539.87
44,099.62
44,658.05
45,215.18
45,770.98
46,325.46
46,878.62
47,430.44
47,980.94
48,530.10
49,077.92
49,624.39
50,169.52
50,713.31
51,255.73
51,796.81
52,336.52

Cumulative Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

52,874.87
53,411.85
53,947.46
54,481.70
55,014.55
55,546.03
56,076.12
56,604.83
57,132.14
57,658.06
58,182.58
58,705.69
59,227.40
59,747.70
60,266.58
60,784.05
61,300.09
61,814.72
62,327.91
62,839.67
63,349.99
63,858.88
64,366.32
64,872.32
65,376.86
65,879.95
66,381.59
66,881.76
67,380.46
67,877.70
68,373.46
68,867.74
69,360.55
69,851.87
70,341.70
70,830.04
71,316.88
71,802.22
72,286.06
72,768.39
73,249.21
73,728.51
74,206.29
74,682.55
75,157.28
75,630.48
76,102.15
76,572.27
77,040.85
77,507.89
77,973.37

Cumulative Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

78,437.30
78,899.67
79,360.48
79,819.71
80,277.38
80,733.47
81,187.98
81,640.91
82,092.25
82,542.00
82,990.15
83,436.71
83,881.65
84,324.99
84,766.72
85,206.83
85,645.32
86,082.19
86,517.42
86,951.02
87,382.99
87,813.31
88,241.99
88,669.01
89,094.38
89,518.10
89,940.15
90,360.53
90,779.24
91,196.27
91,611.62
92,025.29
92,437.27
92,847.56
93,256.14
93,663.03
94,068.21
94,471.68
94,873.43
95,273.46
95,671.77
96,068.35
96,463.19
96,856.30
97,247.67
97,637.28
98,025.15
98,411.26
98,795.61
99,178.20
99,559.02

Cumulative Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

99,938.06
100,315.32
100,690.81
101,064.50
101,436.40
101,806.50
102,174.80
102,541.30
102,905.98
103,268.85
103,629.90
103,989.12
104,346.52
104,702.08
105,055.80
105,407.67
105,757.70
106,105.87
106,452.19
106,796.64
107,139.22
107,479.93
107,818.77
108,155.72
108,490.78
108,823.95
109,155.23
109,484.60
109,812.06
110,137.62
110,461.25
110,782.97
111,102.75
111,420.61
111,736.53
112,050.51
112,362.54
112,672.62
112,980.74
113,286.90
113,591.10
113,893.32
114,193.56
114,491.83
114,788.10
115,082.38
115,374.67
115,664.95
115,953.23
116,239.49
116,523.73

Cumulative Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

116,805.95
117,086.14
117,364.30
117,640.42
117,914.49
118,186.51
118,456.48
118,724.39
118,990.23
119,254.00
119,515.70
119,775.31
120,032.84
120,288.27
120,541.61
120,792.84
121,041.96
121,288.97
121,533.86
121,776.62
122,017.26
122,255.75
122,492.11
122,726.32
122,958.37
123,188.27
123,416.00
123,641.57
123,864.96
124,086.16
124,305.19
124,522.02
124,736.65
124,949.08
125,159.30
125,367.30
125,573.08
125,776.64
125,977.97
126,177.06
126,373.90
126,568.50
126,760.84
126,950.92
127,138.73
127,324.27
127,507.53
127,688.51
127,867.20
128,043.59
128,217.68

Cumulative Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

128,389.46
128,558.92
128,726.07
128,890.89
129,053.38
129,213.53
129,371.34
129,526.79
129,679.89
129,830.63
129,979.00
130,124.99
130,268.61
130,409.83
130,548.67
130,685.10
130,819.13
130,950.75
131,079.95
131,206.73
131,331.07
131,452.98
131,572.45
131,689.47
131,804.03
131,916.13
132,025.76
132,132.92
132,237.59
132,339.78
132,439.48
132,536.67
132,631.36
132,723.54
132,813.19
132,900.32
132,984.92
133,066.97
133,146.48
133,223.44
133,297.84
133,369.68
133,438.94
133,505.62
133,569.72
133,631.22
133,690.13
133,746.43
133,800.12
133,851.19
133,899.63

Cumulative Interest
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$
$

133,945.44
133,988.61
134,029.13
134,067.01
134,102.22
134,134.76
134,164.63
134,191.82
134,216.33
134,238.13
134,257.24
134,273.64
134,287.32
134,298.28
134,306.51
134,312.01
134,314.76

Chapter 4 - Master it!

Excel is a tool for solving problems, but with many time value of money problems, you may still need to draw

This is a classic retirement problem. A friend is celebrating her birthday and wants to start saving for her antic
retirement and retirement spending goals:
Years until retirement:
Amount to withdraw each year:
Years to withdraw in retirement:
Interest rate:

$
$

30
90,000
20
8%

Because your friend is planning ahead, the first withdrawal will not take place until one year after she retires.
for her retirement fund.
a.

If she starts making these deposits in one year and makes her last deposit on the day she retires, what amoun
withdrawals at retirement?

b.

Suppose your friend has just inherited a large sum of money. Rather than making equal annual payments, she
cover her retirement needs. What amount does she have to deposit today?

c.

Suppose your friend's employer will contribute to the account each year as part of the company's profit shari
a family trust several years from now. What amount must she deposit annually now to be able to make the d
Employer's annual contribution:
Years until trust fund distribution:
Amount of trust fund distribution:

$
$

1,500
20
25,000

oblems, you may still need to draw a time line.

nd wants to start saving for her anticipated retirement. She has the following years to

lace until one year after she retires. She wants to make equal annual deposits into her account

on the day she retires, what amount must she deposit annually to be able to make the desired

making equal annual payments, she has decided to make one lump sum deposit today to
y?

s part of the company's profit sharing plan. In addition, your friend expects a distribution from
nually now to be able to make the desired withdrawals at retirement?

Master it! Solution

In order to answer any of these questions, first we need to know how much your friend will need when she is
each of the parts of the problem, we will solve for this amount now, which will be:
Amount needed at retirement:
a.

The amount your friend must save each year to fund her retirement is:
Amount to save each year:

b.

The lump sum your friend must deposit today to fund her retirement is:
Lump sum deposited today:

c.

To find the amount of the annual deposit now, it is easier to break down the components of the problem. Do
deposit, we get:
Value of employer's contribution at retirement:
Value of trust fund at retirement:
Amount to save each year now:

ch your friend will need when she is ready to retire. Since this amount will be the same for
h will be:

he components of the problem. Doing so for each of the following to find your friend's annual