Loading presentation...

Present Remotely

Send the link below via email or IM


Present to your audience

Start remote presentation

  • Invited audience members will follow you as you navigate and present
  • People invited to a presentation do not need a Prezi account
  • This link expires 10 minutes after you close the presentation
  • A maximum of 30 users can follow your presentation
  • Learn more about this feature in our knowledge base article

Do you really want to delete this prezi?

Neither you, nor the coeditors you shared it with will be able to recover it again.


Amortization Tables in Excel


Brandon Cox

on 25 March 2011

Comments (0)

Please log in to add your comment.

Report abuse

Transcript of Amortization Tables in Excel

Amortization Tables in Excel
When a loan is made, a payment
must be made each month to repay
the loan: The Monthly Payment.
The Monthly Payment is
comprised of two parts:
There fore, we have the following formula:

Monthly Payment = Interest + Principal
A loan is required to make
very large purchases.
a house
a car
First, calculate the monthly payment:

=PMT(Yearly Interest Rate / 12, Length of Loan, Loan Amount)
The interest rate is divided
by 12 because the rate is always
a yearly rate and we need
the monthly interest rate for this
Here's an example:

=pmt(.07/12, 360, 129000)
The length is always
given in months as well.
This house would cost the buyer 858.24 each month.
Unfortunately, not all of
that 858.34 can go to pay
off our loan. A portion of
that payment goes toward
interest (i.e. the bank)
Let's review our formula:
Monthly Payment = Interest + Principal

858.24 = Interest + Principal

The sum of money the bank
gets for loaning the money
for the loan.
The sum of money
that goes toward
paying off the loan.
How do we figure the interest and the principal?
We start with the interest...
Another formula...

Interest = Current Balance x Monthly Interest Rate
When we begin our loan, we owe the entire amount $129,000.
So our Interest = 129000 x (.07 / 12)
That's almost the entire monthly payment!!
The next month we won't owe 129,000 but a little bit less. The current balance
amount will go down each month.
Now we have 2 of our 3 numbers we need:
858.24 = 752.50 + Principal
We'll now rearrange this formula to solve for principal:
Principal = 858.24 - 752.50
Our last task is to find our new balance.
This will be needed for the next month
to find the interest amount.

Current Balance = Last Month's Balance - Principal Paid
Current Balance = 12,9000 - 105.74
Current Balance = 12,8894.26
Full transcript