Amortization Schedules, Slides of Banking and Finance

Download Amortization Schedules and more Slides Banking and Finance in PDF only on Docsity!

Amortization SchedulesAmortization

Schedules

What^ is

an^ Amortization

Schedule

y^ An^ Amortization

Schedule^ is

a^ table^ which

calculates

both^ the^ interest

paid^ as^ well

as^ the^ principal

paid^ for

each period of a loan duration

In Excel we must create

each^ period

of^ a^ loan^ duration

.^ In^ Excel,^ we

must^ create

this^ table^ from

scratch^ with

the^ help^ of

a^ few^ functions

y^ Amortization Schedules are often used to calculateAmortization

Schedules^

are^ often^ used

to^ calculate

payments^ for

mortgages,

but^ can^ also

be^ used^ for

any

type^ of^ loan

(car,^ vacation,

or^ other^ expensive

item).

Assumptions/Base

Information

y^ Before^ an

Amortization

Schedule^ ca

n^ be^ created,

we

e o e a^

o^ a o^ Sc edu e ca

be c ea ed,

e

need:^ •^ Loan

Amount

(what^ you

are

•^ Loan^ Amount

(what^ you

are

trying^ to

pay^ off)

•^ Interest

Rate^ (Annual)

•^ Interest

Rate^ (Annual)

•^ Frequency of

Payments

(how

many^ payments

per^ year)

many^ payments

per^ year)

•^ Initial^

down^ payments

(if^ any)

L^ D^

i^ (i^

•^ Loan^ Duration

(in^ years)

Setting

up^ your

Amortization

ScheduleSchedule It is^ good^ to^ give^ your

Amortization^ Schedule a^ title^ so that others know what it is fora^ title^ so^ that^ others

know^ what^ it^ is^ for

. Usually^ after^ the^ title,

the^ user^ will^ enter and^ label^ the^ assumptions

that^ the^ table^ will be^ based^ on. Next,^ you^ should^ add

in^ column^ headings for^ your^ table^ so^ that

you^ know^ what^ each column^ in^ your^ table

is^ for. Lastly^ in^ order^ for^

your^ table^ to^ work

properly, you^ need^ to^ fill^ in^

the^ payment^ number

for^ the duration of the loan. For example, this loan hasduration^ of^ the^ loan.

For^ example,^ this^

loan^ has 4 payments^ per^ year

for^20 years,^ this^ means under^ Period,^ we^ should

list^ the^ numbers^ from

1 to^80 in^ increasing

order.^ This^ can^ be

done^ by Auto‐filling^ numbers like in this example. Auto^ filling^ numbers

like^ in^ this^ example.

Using the Function Arguments BoxUsing^ the

Function

Arguments

Box

In^ order^ to^ make

things^ easier,^ you should^ use^ the^

functions^ arguments

box should^ use^ the^

functions^ arguments

box for^ your^ schedule.

To^ do^ this,^ you

must first^ know^ the^ name

of^ the^ function

you would^ like^ to^ use.

In^ this^ case,^ we

chose the^ Payment^ or

PMT^ function.

For^ this example^ you^ will

type^ in^ =PMT(

.^ The open^ parentheses

is^ needed^ for^ the

box to^ be^ opento^ be^ open. Next,^ click^ on^ the

Insert^ Function

button on^ your^ screen^

(the^ picture^ to^ your

left marks^ this^ button

with^ a^ red^ square.)

PMT (Payment)PMT^ (Payment)

Rate*^ =^ The

Interest^ Rate^

for^ a single^ period in

the^ loan.^ In^ ourg p example^ we^ were

using^ 5.5%^ APR

and making^4 payments

per^ year. Nper*^ =^ The^ total

number^ of Nper^ =^ The^ total

number^ of payments^ in the^ loan.^ In^ this

case^ we^ have^4 payments per^ year^ for^20 years. Pv *^ =^ Present^

Value^ which^ is^

the amount^ that^ your

loan^ is^ for amount^ that^ your

loan^ is^ for.

*To^ make^ your^ table^ easily^ editable,

use^ cell^ referencing

whenever^ possible

IPMT (Interest Payment)IPMT^

(Interest

Payment)^ Rate *

=^ Interest^ Rate

for^ a single^ period^ in^ the^ loan.^ In^

our example^ we^ were

using^ 5.5%^ APR and^ making^4 payments

per^ year. Per *^ =^ The^ current

period Per^ =^ The^ current

period number.^ To^ ensure

your^ table works^ properly,

a^ cell^ must^ be selected^ (a^ number

should^ not^ be d) entered).Nper * =^ The^ total

number^ of payments^ in^ the

loan.^ In^ this payments^ in^ the

loan.^ In^ this case^ we^ have^4 payments

per^ year for^20 years. P^ *^ P^ t^

V l^ hi h^ i^

th

*^ Pay^ careful^ attention

to^ Absolute^ Cell Referencing^ for

this^ part!^ If^ you

do^ not reference^ cells^ correctly,

your^ table^ will^

NOT

work^ correctly^ when

auto^ filled^

Pv *^ =^ Present^

Value^ which^ is^

the amount^ that^ your

loan^ is^ for.

work^ correctly^ when

auto‐filled.

Row #1 of the ScheduleRow^

of^ the

Schedule^ Once

PPMT^ and^ IPMT

have^ been entered^ in^ the^ first

row,^ all^ that^ is left^ is^ to^ enter^ the

Beginning^ and Ending^ Balance

for^ the^ first^ row. Beginning^ Balance

• Since^ this^ is^ the Beginning^ Balance

Since^ this^ is^ the first^ period^ of^ the

loan,^ Beginning Balance^ is^ just^ the

Amount^ of^ the loan^ less^ any^ down/initial

payment ( f^ h^ )(if^ there^ is^ one).Ending^ Balance

*^ – Ending^ Balance

is the^ Beginning^ Balance

for^ the^ period the^ Beginning^ Balance

for^ the^ period plus^ the^ Principal

Payment^ for^ that month.*^ Pl^ t^ th t

C ll^ R f

Why^ we^ add^ Principal

Payment^

*^ Please^ note^ that

Cell^ References are^ needed^ for^ the

Ending^ Balance of^ the^ first^ period. Why^ we^ add^ Principal

Payment Excel^ sees^ payment

as^ money^ leaving^ you,

thus^ it is^ a^ negative^ number

(so^ the^ number^ is^ red

and^ in parenthesis).^ So^ instead

of^ subtracting^ the payment,^ we^ add^ it to^ the^ beginning^ balance.

R^

d

Row^

and^ on

The^ last

step^ in^ completing

your Amortization^ Schedule

is^ to^ highlight^ the Amortization^ Schedule

is^ to^ highlight^ the nd^2 row^ of^ your table^ (and^ ONLY

nd the 2 row)^ to^ include^ Beginning^ Balance, Principal^ Payment,

Interest^ Payment,

and E di^ B l^

N^ fill^

h^ f Ending^ Balance.

Next,^ auto‐fill

the^ rest^ of your^ table.If^ your^ table^ was

filled^ out^ correctly,

the y^

y Ending^ Balance

for^ the^ last^ line

of^ your table^ will^ be^ equal

to^ 0.