<< Back to all Blogs
Login or Create your own free blog
Home > Amortization Schedules

Amortization Schedules

February 20th, 2006 at 09:15 pm

There was a question about setting up amortization schedules. I use excel to do this. It's pretty easy once you get started.

You first need a bit of information. The first is your interest rate. The second is how is your interest calculated. Most mortgages are calculated as monthly interest (so the timing of your payment isn't important as long as you get it in before the late charge kicks in). Most cars and other loans are calculated as daily simple interest and the timing of your payment can make a big difference. You also need your current balance.

To figure your monthly or daily interest rate, divide your yearly rate by 12 or 365 (occasionally there will be a 360 DSI loan, but you'll catch that when it comes along). Don't forget to convert interest rates to decimals - 8% = 0.08.

So first, set up your columns. We'll do mortgage first, since it is simpler.

Column A - the month.
Column B - the current principal balance
Column C - =B*the monthly interest rate
Column D - the payment you are making
Column E - =B+C-D (this is your balance after making your payment)

Then it's a matter of cut and paste to get the rest. Column A you fill in, Column B - type "=E1". When you cut and paste it will go to "E2" etc. Note - I say E1, if you are on any row other than 1, use that row.

Okay for cars you need an extra column
Column A - the date you are making the payment
Column B - =A-the last date you made a payment (when you set this up, after the first row just put A2-A1, again changing the cell numbers if you need to).
Column C - current balance
Column D - =B*interest rate*C
Column E - the payment you are making
Column F - =C+D-E (your new balance).

This probably makes as much sense as mud, but open excel and see if you can figure it out. If not, maybe Jeffrey and/or Nate can walk me through posting a screen shot of an excel worksheet.

There are shorter ways to do this, but this provides a lot more information and is easy to work with.

5 Responses to “Amortization Schedules”

  1. cercis Says:

    I forgot to say that on the date subtraction, go to format > cells and choose number, otherwise it will look really funny. But it's strictly an aesthetic thing, it won't make any difference in your calculations.

  2. PrincessPerky Says:

    Oh my I asked, I am not sure I can do that! I will try though.

  3. cercis Says:

    Perky - I tried to use actual formulas which should help - for instance * = times in excel. You can click on the cell you want to multiply rather than typing it (it's a fun little short cut).

    If you have too much trouble, we'll see if we can figure out how to do a screen shot.

  4. Gakline Says:

    Here's one that is already set up. Check out my entry (the second one) Just download the file. I promise no viruses.


  5. Gakline Says:

    A real link (cross your fingers)Amortization

Leave a Reply

(Note: If you were logged in, we could automatically fill in these fields for you.)
Will not be published.

* Please spell out the number 4.  [ Why? ]

vB Code: You can use these tags: [b] [i] [u] [url] [email]

Supporting Sites: