Mortgage Amortization Template for Excel by lbg52283


More Info
									A Time Value of Money Primer

    By David B. Hamm, MBA, CPA
for Finance and Quantitative Methods
Simple Interest and Discount: (1)
In its most basic form, interest is calculated by
multiplying principal (amount invested) by rate (percent
of interest) multiplied by time (number of periods the
interest is calculated). This is called simple interest.

                       I = Pr t

Example: A $1,000 deposit at 8% per year for three
years' simple interest: I = (1000)(.08)(3) = 240 A
$1000 deposit at 8% simple interest for three years
earns $240 interest.
 Simple Interest/Discount (2)

The future value (FV) of a simple interest calculation is
derived by adding the original principal back to the
interest earned.

$1,000 + $240 = $1,240
Expressed as a formula:

FV = P(1 + rt)
FV = (1000)+(1000)(.08)(3) = 1240
Simple Interest/Discount (3)
Note: usually simple interest is used in financial
institutions for interest periods of less than one year. If
the rate is expressed as an annual rate (normal
practice), then the time period (t) must be a fraction of
a year. Example: we invest $10,000 in an 8% , 90-day
certificate of deposit. Our total proceeds at the end of
the CD period are:

FV = (10000)+(10000)(.08)(90/365) = $10,197.26
Simple Interest/ Discount (4):
Often, if a bank or other financial institution loans a
sum for a short term, the lender will prefer to calculate
the interest up front and loan out the discounted
principal, or principal minus interest to be earned. The
interest to be paid up front on a loan is called discount
and the discounted principal, or the actual amount
loaned is called the present value (PV)

       PV = (1+rt)
Simple Interest/Discount (5):
Repeating the discount basic formula (simple interest):
       PV = (1+rt)

Example: If the bank loans out $10,000 for 90 days at
8% simple interest, the PV is:

       PV = 10000 / [1 + (.08)(90/365)]
          = 10000/ 1.019726
          = $9,806.56
Compound Interest (1):
However, if interest is left in the account to accumulate
for a longer period (usually longer than one year)
common practice (and usually state law!) requires that
after interest is earned and credited for a given period,
the new sum of principal + interest must now earn
interest for the next period, etc. This is compound
interest. To distinguish from simple interest, we use "n"
to refer to the number of "periods" in which the interest
is compounded and added to principal.

         FV = P(1 + r)n               PV = (1+r)n
Compound Interest (2):
Suppose we invest our original $1,000 for three years at
8%, compounded quarterly: (The rate per quarterly
period is 8% / 4 or 2%. The number of periods (n) is 3
x 4 = 12 quarterly periods.)

       FV = (1000)(1.02)12 = $1,268.24

If we wanted to know how much we'd have to invest
now (PV) at 8% compounded quarterly to earn $10,000
in three years:

       PV = 10000 / (1.02)12 = $7,884.93
Compound Interest (3):

Because raising interest factors to an exponent of "n"
was a difficult calculation before calculators, some
mathematicians used logarithmic functions to calculate
the exponent factor. Financial professionals acquired
tables of these functions so that either of the above
problems could be calculated simply by looking up a
FV factor (or to discount, a PV factor) based on the
interest rate and number of compounding periods and
multiplying the principal by the interest factor.
Compound Interest (4):

 Now, computerized spreadsheets can build in these
 financial functions and easily do the work for us.
 It will be our assumption in this class that you will
 have a computer present to calculate these
 financial functions. Our discussion will be based
 on MS Excel, but Quattro and Lotus and most
 other major spreadsheets have similar function
Basic Financial Functions in Excel
In the spreadsheet, it is often advisable to set up and
identify cells for your principal, your interest rate,
and the number of time periods. Setting up a simple
template in this fashion means you can easily update
your template for new calculations just by changing
amounts in the cells.
        Principal              -1000
        Rate (yr)                 8%
        Yrs                        3
Quick note: In Excel, present value (PV) is assumed
to be a cash outlay, and is thus expressed as a negative
Functions in Excel (2):
  The mathematical functions are accessed on the Excel
  taskbar with the " fx" key.

  Select "Financial" functions.

  We will most commonly compute =FV (future value)
  or =PV (present value). Each of the functions in
  Excel pops up a simple menu to follow to identify
  data. If you have annual rates or periods that need
  conversion to semiannual, quarterly, or monthly
  compounding, the function can multiply the number
  of periods or divide the rate for you in the menu cell.
Functions in Excel (3):
 1.   Try this problem in Excel:
   Invest $1,000 (present value) at 8% annual
 interest compounded quarterly for three years to
 see how much we can receive (future value)
 (hint: use the =FV function)
  1. Invest $1,000 at 8% compounded quarterly for 3 years:

             Principal          -1000     (enter as negative)
             Rate                 2%     (8% / 4 qtrs)
             Nper                  12    (3 yrs x 4 qtrs)

             Fut. Val      $1,268.24 =FV
Functions in Excel (4):
2. Now the reverse—how much would we have
to invest now (present value) at 8% compounded
quarterly to receive $10,000 (future value) in
three years? (use the =PV function)
 2. Discount investment required to realize $10,000 at 8% compounded
     quarterly in 3 years

           FV                 10000   (enter as positive amount)
           Rate                 2%    (8% / 4 qtrs)
           Nper                  12   3 yrs x 4 qtrs)

           Pres Val      ($7,884.93) =PV

  Again, Excel displays the PV amount as negative.
Consumer Loans (brief) (1):
Not so long ago, banks and finance companies
frequently calculated simple interest on consumer loans
using the add-on interest method:

        Payment = P +I

Add principal + interest over the life of the loan and
divide by the number of payments.

Example: a $5,000 car loan at 8% simple interest for 3
years = $1,200 interest. Therefore ($5,000 +1,200) / 36
months = $172.22 monthly payment
Consumer Loans (2):
Problem: this was charging interest on the full $5,000
for the whole life of the loan despite the principal being
partially paid down each month. The true annual
interest rate was therefore much higher than 8%.
(Using a financial function, the true APR (annual %
rate) would be 14.55% for the full 36 months.)

Current Federal and state consumer law requires that
the stated interest rate be reported not only as the
simple rate, but also as the true APR. Add-on loans,
while still used, are therefore much less popular or
common today.
Revolving Credit-Credit Cards (1):

   Some “revolving credit" accounts, such as some
store credit cards, calculate finance charges monthly
based on the unpaid balance from the previous
month--the unpaid balance method.

          I = Prt but P = previous balance +
finance charge + new charges - returns or payments.
Revolving Credit-Credit Cards (2):
   Most bank credit cards use the average daily balance
   method which computes the number of days in each
   month from date of each transaction and divides by
   the number of days in the month to figure an average
   daily balance to be entered into the I = Prt formula.

   •   Add outstanding balance for account for each
       day of the previous month
   •   Divide Step 1 total by number of days in
       previous month = average daily balance
   •   Use I= prt to find finance charge, where P is
       average daily balance in Step 2, r is annual
       interest rate, and t is no. of days in month/
Annuities (1):
An annuity is an interest bearing account into which
   we make, or we receive, payments of an equal
   amount each period until the annuity ends.

If the payment is made on the last day of each
    period, it is an ordinary annuity. (This is most
    typical and what we will illustrate.)

If the payment is made on the first day of each
    period, it is an annuity due. (not as common) MS
    Excel identifies the two types as "0" or
    blank=ordinary; "1" =annuity due.
Annuities (2):
 Some annuities have no "fixed" ending date, but
 rather continue for the life of the recipient. These
 are usually called life annuities and the payment is
 calculated for a number of periods based on life

 A perpetuity is an annuity with no ending date. (An
 example of a perpetuity is an endowed scholarship,
 where only interest is paid out as scholarship funds
 and the endowment principal remains invested
 "forever" or in perpetuity.)
Annuities (3):
 A sinking fund is a fund in which a regular annuity
 payment is made to accumulate to a future value to
 be used for some future purpose, such as paying off a
 bond issue or some other obligation.

 Before calculators, polynomials and logarithmic
 functions were used to calculate annuity tables for
 financial use. Now, we can simply use spreadsheet
 financial functions, usually using =PV, =FV, or
 =PMT in Excel and now inserting payment
 information where applicable.
Annuities (4):
Illustration: We need to accumulate a sinking fund
of $100,000 in ten years (120 months) to pay off a
note payable. If we can invest our funds at 8%
compounding monthly, how much must we deposit
per month?
    FV           100,000
    PV                 0

    Rate        0.006667   8% / 12
    Nper             120 10 yrs x 12

    =PMT        ($546.61)

Excel functions are available to find any of the above
variables, if we have the others.
Annuities (5):
Illustration (2): When Joe retires on his 65 th
birthday, his retirement fund carries a balance of
$240,000. If Joe transfers this balance into a fund
earning 8% to pay him or his heirs $2,000 per
month until the fund is exhausted, how long can
this annuity last?
  FV                 0
  PV          -240,000
  Pmt            2,000
  Rate        0.006667    8% / 12

  =NPER       242.2195
Approx 242 months—just over 20 years! (Assuming 8% is
consistent and there is no risk of loss of principal!)
Amortization (Mortgages) (1):
Finally, if we take out a long term loan, such as a
mortgage, or a car loan based on the true APR, the
interest expense is calculated for each month based on
the unpaid balance of the loan. A fixed monthly
payment is computed from which is first deducted the
monthly interest, and the balance is applied to reduce
principal. The new interest is then recalculated the
next month based on the lower principal. This
generates a schedule of all loan payments, interest and
principal applied, and outstanding balance called an
amortization schedule.
Amortization (Mortgages) (2):
In the early months of an amortization schedule,
much (perhaps most) of the monthly payment goes
toward interest because the unpaid balance is so
large. As the principal is paid down, more and more
of each payment is applied toward principal.
 Example: in a 30 year $100,000 home mortgage at 9%, the
 required monthly payment is $804.63 (round up 1 cent)
  PV            -100,000
  Nper               360 (30 yrs)
  Rate            0.0075 9% / 12
  FV                   0

  =PMT         $804.623
Amortization (Mortgages) (3):
Of the $804.63 payment, the first month's interest is
$750.00 (100,000 x .09/12). Therefore only
804.63-750.00 = $54.63 goes toward principal.

But by the last month of the mortgage, only about
$785.22 is left unpaid. Thus only $5.90 goes to interest
and the last loan payment is $791.12 to zero out the

In fact it is not until payment #269 (22 years, 5 months
into the loan) when the interest portion of the payment
is less than the principal portion! Ultimately we would
pay $189,653.30 in interest on our $100,000 loan over
the 30 years!
Amortization (Mortgages) (4):
We can build an amortization table using an Excel
spreadsheet to calculate the principal & interest
portion of all our payments:
                    = 9% / 12     =pyt - int = prev. - prin
Payment     Payment    Interest   Applied to          Unpaid
No           Amount   Payment      Principal        Balance
    start                                      $100,000.00
        1   $804.63     $750.00      $54.63      $99,945.37
        2   $804.63     $749.59      $55.04      $99,890.33
        3   $804.63     $749.18      $55.45      $99,834.88

This spreadsheet can be extended through all 360
monthly payments to total principal and interest
paid to the end of the mortgage

To top