# Excel Amortization Table - PDF

Description

Excel Amortization Table document sample

Shared by:
Categories
-
Stats
views:
294
posted:
7/6/2010
language:
English
pages:
32
Document Sample

```							Financial Functions, Data
Tables, and Amortization
Schedules

Chapter 4
What we will cover
 Controlling thickness and color of outlines
and borders
 Naming cells
 Using the PMT function to calculate
monthly payments
 Using the present value (PV) and future
value (FV) functions
 Creating data tables
What we will cover

 Adding a pointer to a data table
 Creating an amortization schedule
 Changing values in a worksheet
 Setting print area to print only sections
of a worksheet
 Setting print options
What we will cover
 Using formula checking
 Hiding and unhiding cell gridlines, rows,
columns, sheets, and workbooks
 Protecting and unprotecting cells
Financial Functions
 Excel has 17 different functions that are
dedicated to just finance. Some are:
 Depreciation Methods
– declining balance depreciation,
– double declining balance depreciation,
– sum of the year’s digits depreciation,
– straight line depreciation

Financial Functions, cont.
 present value
 future value
 internal rate of return
 payments
 and so on...
Parts of the Worksheet
 loan calculator
– calculate payments on a loan where you can vary
the number of years and interest rate

 data table
– displays payments at a range of interest rates
because the loan calculator will only show you
payments at one interest rate at a time

 amortization table
– will show how much you would need to pay off
your loan early after each year
Loan Calculator

Amortization Table

Data Table for Varying
Interest Rates
Sample Loan Calculator

A                B                     C             D
1                                Loan Calculator

2   Date            =now()              Interest Rate     6.00% (given)

3   Item            house (given)       Years of Loan     30 (given)

4   Price           650000 (given)      Monthly Payment   calculated

5   Down Payment    65000 (given)       Total Interest    calculated

6   Loan Amount     calculated          Total Cost        calculated

Labels in column A used as names            Labels in column C used as names
for the cells in column B                   for the cells in column D
Which formula for total interest over the life
of a loan is easier to read and understand?

(12 * YEARS_OF_LOAN * MONTHLY PAYMENT - LOAN AMOUNT)

OR

(12*E2*E3-C5)
Cell Names

 If more than one word, you must use an
underline between the words to trick Excel
into thinking that they are really just one
word:
Loan_amount
Total_interest

 Cell names are not case sensitive
price = Price = PRICE = pRiCe
Ways to Name Cells
 Easiest
– Select cell or range of cells
– Type the name in the name box
– Press Enter
 Alternative
– Select cell or range of cells, Click Formulas
Ribbon Tab
– Click Define Name Button
– Type the name
– Click OK
Naming Cells
 Using labels in left column to name the cells in the right
column
– Select cells in a par of adjacent columns
A               B                     C          D
1                               Loan Calculator

2   Date           =now()              Interest Rate   6.00% (given)

3   Item                        house Years of Loan    25 (given)

4   Price                   650000 Monthly Payment     calculated

5   Down Payment                65000 Total Interest   calculated

6   Loan Amount         calculated Total Cost          calculated

– Click Formulas Ribbon Tab
– Click Create
– Click OK

ribbon

name list

name list
button
Reason for Data Tables
 You can compare payments at different interest
rates without having to put each rate in the loan
calculator and then write down the payment.
 With a data table, you can see payments at
several different interest rates at one time because
they are all listed, one beneath the other.
 You can also see how much you would pay in
total interest at each interest rate as well as how
much the total cost of the house would be at each
interest rate.
Review--Incrementing Numbers
Creates a series of numbers (generally in a column) by telling Excel the starting
number and the amount to increase the next number. Then use the fill handle to
indicate the range,

Type 2 in A1 and 4 in A2 to start the
A             B
series and tell that Excel it should
succeeding cell down the worksheet.            2       4             6
3       6             9
Column B starts with 3 and adds 3 to           4       8             12
each number down the worksheet.
Input Cell
A           B                       C              D
1                            Loan Calculator
2 Date                        xxx Interest Rate                   5.75%
3 Item                      Home Years of loan                         18
4 Price               265,000.00 Monthly Payment             \$1,748.00
5 Down Payment        \$30,000.00 Total Interest            \$142,740.46
6 Loan Amount         235,000.00 Total Cost                \$407,740.46
7                   Interest Rate Schedule (Data Table)
8         Rate   Monthly Payment        Total Interest    Total Cost
9                     =D4                   =D5             =D6
10        4.50%
11        4.75%
12        5.00%
See next slide
13        5.25%
14        5.50%
15        5.75%
Input Cell

A           B                          C              D
1                               Loan Calculator
2 Date                          xxx Interest Rate                       5.75%
3 Item                     Home Years of loan                              18
4 Price                265,000.00 Monthly Payment                 \$1,748.00
5 Down Payment         \$30,000.00 Total Interest               \$142,740.46
6 Loan Amount          235,000.00 Total Cost                   \$407,740.46
7                   Interest Rate Schedule (Data Table)
8         Rate   Monthly Payment          Total Interest     Total Cost
9                   \$1,748.80             \$142,740.46       \$407,740.46
10        4.50%
11        4.75%
12        5.00%
13        5.25%
14        5.50%
15        5.75%
Input Cell

A           B                          C              D
1                               Loan Calculator
2 Date                          xxx Interest Rate                       4.50%
3 Item                     Home Years of loan                              18
4 Price                265,000.00 Monthly Payment                 \$1,589.36
5 Down Payment         \$30,000.00 Total Interest               \$108,302.37
6 Loan Amount          235,000.00 Total Cost                   \$407,740.46
7                   Interest Rate Schedule (Data Table)
8         Rate   Monthly Payment          Total Interest     Total Cost
9                   \$1,748.80             \$142,740.46       \$407,740.46
10        4.50%      \$1,589.36             \$108,302.37       \$407,740.46
11        4.75%
12        5.00%
13        5.25%
14        5.50%
15        5.75%
Input Cell

A           B                          C              D
1                               Loan Calculator
2 Date                          xxx Interest Rate                       4.75%
3 Item                     Home Years of loan                              18
4 Price                265,000.00 Monthly Payment                 \$1,620.58
5 Down Payment         \$30,000.00 Total Interest               \$115,044.51
6 Loan Amount          235,000.00 Total Cost                    \$380,04451
7                   Interest Rate Schedule (Data Table)
8         Rate   Monthly Payment          Total Interest     Total Cost
9                   \$1,748.80             \$142,740.46       \$407,740.46
10        4.50%      \$1,589.36             \$108,302.37       \$373,302.37
11        4.75%      \$1,620.58             \$115,044.51       \$380,04451
12        5.00%
13        5.25%
14        5.50%
15        5.75%
A           B                          C            D
1                               Loan Calculator
2 Date                          xxx Interest Rate                  5.00%
3 Item                     Home Years of loan                            18
4 Price                265,000.00 Monthly Payment              \$1,652.13
5 Down Payment         \$30,000.00 Total Interest             \$121,859.98
6 Loan Amount          235,000.00 Total Cost                 \$386,859.98
7                   Interest Rate Schedule (Data Table)
8         Rate   Monthly Payment          Total Interest   Total Cost
9                   \$1,748.80             \$142,740.46      \$407,740.46
10        4.50%      \$1,589.36             \$108,302.37      \$373,302.37
11        4.75%      \$1,620.58             \$115,044.51      \$380,04451
12        5.00%      \$1,652.13             \$121,859.98      \$386,859.98
13        5.25%
14        5.50%
15        5.75%
A            B                         C           D
1                               Loan Calculator
2 Date                          xxx Interest Rate                 5.25%
3 Item                      Home Years of loan                          18
4 Price                 265,000.00 Monthly Payment            \$1,684.02
5 Down Payment          \$30,000.00 Total Interest           \$128,748.21
6 Loan Amount           235,000.00 Total Cost               \$393,748.21
7                   Interest Rate Schedule (Data Table)
\$407,740.46
8         Rate   Monthly Payment        Total Interest    Total Cost
9                   \$1,748.80             \$142,740.46     \$407,740.46
10        4.50%      \$1,589.36             \$108,302.37     \$373,302.37
11        4.75%      \$1,620.58             \$115,044.51     \$380,04451
12        5.00%      \$1,652.13             \$121,859.98     \$386,859.98
13        5.25%      \$1,684.02             \$128,748.21     \$393,748.21
14        5.50%
15        5.75%
A           B                          C            D
1                               Loan Calculator
2 Date                          xxx Interest Rate                  5.50%
3 Item                     Home Years of loan                            18
4 Price                265,000.00 Monthly Payment              \$1,716.24
5 Down Payment         \$30,000.00 Total Interest             \$135,708.58
6 Loan Amount          235,000.00 Total Cost                 \$400,708.58
7                   Interest Rate Schedule (Data Table)
8         Rate   Monthly Payment          Total Interest   Total Cost
9                   \$1,748.80             \$142,740.46      \$407,740.46
10        4.50%      \$1,589.36             \$108,302.37      \$373,302.37
11        4.75%      \$1,620.58             \$115,044.51      \$380,04451
12        5.00%      \$1,652.13             \$121,859.98      \$386,859.98
13        5.25%      \$1,684.02             \$128,748.21      \$393,748.21
14        5.50%      \$1,716.24             \$135,708.58      \$400,708.58
15        5.75%
A           B                          C            D
1                               Loan Calculator
2 Date                          xxx Interest Rate                  5.75%
3 Item                     Home Years of loan                            18
4 Price                265,000.00 Monthly Payment              \$1,748.00
5 Down Payment         \$30,000.00 Total Interest             \$142,740.46
6 Loan Amount          235,000.00 Total Cost                 \$407,740.46
7                   Interest Rate Schedule (Data Table)
8         Rate   Monthly Payment          Total Interest   Total Cost
9                   \$1,748.80             \$142,740.46      \$407,740.46
10        4.50%      \$1,589.36             \$108,302.37      \$373,302.37
11        4.75%      \$1,620.58             \$115,044.51      \$380,04451
12        5.00%      \$1,652.13             \$121,859.98      \$386,859.98
13        5.25%      \$1,684.02             \$128,748.21      \$393,748.21
14        5.50%      \$1,716.24             \$135,708.58      \$400,708.58
15        5.75%      \$1,748.80             \$142,740.46      \$407,740.46
Adding a Pointer to a Data Table
To make the cell where the interest rate is the same as the original interest rate in
the loan analysis appear different from the other cells, use conditional formatting.

A                  B                          C                    D
1                                         Loan Calculator
2     Date                                xxx   Interest Rate                         5.75%
3     Item                             Home     Years of loan                               18
4     Price                        265,000.00   Monthly Payment                    \$1,748.00
5     Down Payment                 \$30,000.00   Total Interest                   \$142,740.46
6     Loan Amount                  235,000.00   Total Cost                       \$407,740.46
7                                      Interest Rate Schedule
8             Rate         Monthly Payment            Total Interest          Total Cost
9                             \$1,748.80               \$142,740.46           \$407,740.46
10             4.50%           \$1,589.36               \$108,302.37           \$373,302.37
11             4.75%           \$1,620.58                \$115,044.51           \$380,04451
12             5.00%           \$1,652.13               \$121,859.98           \$386,859.98
13             5.25%           \$1,684.02               \$128,748.21           \$393,748.21
14             5.50%           \$1,716.24               \$135,708.58           \$400,708.58
15             5.75%           \$1,748.80               \$142,740.46           \$407,740.46
Amortization Table

 The purpose of this table is to give pay off
amounts at the end of each year of the loan
as well as some other useful information.
 Strips out interest that would have been
charged in the years after.
 For example, a pay off amount at the end
of year 10 would not include interest for
years 11, 12, and so on.
Year        Beginning                   Ending                      Paid on                Interest Paid
Balance                    Balance                     Principal

1        235,000.00                227,326.79                    7,673.21                  13,312.37
2        227,326.79                219,200.56                    8,126.23                  12,859.35
3        219,200.56                210,594.56                    8,606.00                  12,379.58
…
18         20,346.32                         0.00                 20,346.32                    639.26

Beginning     At the beginning of year 1, it is the amount of money borrowed. The Beginning Balance is the
Balance       initial loan amount in the loan calculator.

Ending        This is how much you still owe at the end of a period (such as a year). The balance at the end of
Balance       a year is equal to the present value of the monthly payments paid over the remaining life of the
loan.

Paid on       This is how much of your monthly payments actually went to reduce the principle
Principal     The amount paid on the principal is equal to the beginning balance less the ending balance.

Interest      This is how much went to the lender for lending you the money for the house. The interest paid
Paid          during the year is equal to 12 times the monthly payment less the amount paid on the principal.
IF function in the amortization table
You are doing an amortization table built to accommodate
25-year loans.

You want to be able to use this worksheet for shorter loans
as well, such as a five-year car loan.

In an amortization table built for 25-year loans, a loan of
five years would result in negative numbers showing for
years 6 and beyond, all the way down to year 25 in the
table.

The ending balance should be 0 in the rows for the years
beyond the end of the loan, in other words, years 6 through
25.

You can use an if function to show 0 instead of negative
numbers in the rows for years beyond year 5.
Protecting a Worksheet

 Prevents someone else such as a vacation
replacement or trainee or someone who is just
distracted from accidentally destroying your
worksheet. You can lock that person out of
certain cells.
 Designate those cells that you want to be
unprotected.
 Then protect the entire worksheet.
Using Goal Seek to find an interest
rate for a specific monthly payment

 Assume the most you can afford is \$1,100 a
month for the monthly payment.
 You will need to find an interest rate that will
result in a \$1,100 monthly payment so that
you can shop for that interest rate or a lower
one.
 You will use goal seeking to find the interest
rate.
Formatting with borders and fill

2

3

1

Use the features in this dialog box in a certain
order. Select color, then line style, then the line
itself or a preset.

```
Related docs