Get documents about "Excel Amortization Table - PDF
Description
Excel Amortization Table document sample
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
Checking Your Cell Names
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
start with 2 and add 2 to each 1 2 3
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
