Author Topic: how do I make an amortization spreadsheet?  (Read 3921 times)

strider3700

  • Pencil Stache
  • ****
  • Posts: 516
  • Location: northern BC
how do I make an amortization spreadsheet?
« on: November 14, 2012, 12:57:31 PM »
I'm trying to recreate my banks amortization schedule in a spreadsheet.

the opening two lines are   
Period   Payment Date   Principal   Interest   Total Interest   Balance      
1           11/16/2012   $89.3   $143.21   $143.21           $204062.75      
2           11/23/2012   $89.37   $143.14   $286.35           $203973.38

interest rate is  3.69
Payment amount is $232.51
payments are weekly

I can't seem to figure out how to get my interest on a line by line basis to work out.     I want to be able to mess with payment amounts to see how the change the pay off date
thanks for any help.

Secret Stache

  • Stubble
  • **
  • Posts: 128
Re: how do I make an amortization spreadsheet?
« Reply #1 on: November 14, 2012, 01:05:42 PM »

jpo

  • Pencil Stache
  • ****
  • Posts: 518
  • Age: 32
  • Location: North Carolina
Re: how do I make an amortization spreadsheet?
« Reply #2 on: November 14, 2012, 01:05:50 PM »
http://www.hughchou.org/calc/genloan.php

You can put in 52 payments per year, it will generate a schedule for you.

grantmeaname

  • Magnum Stache
  • ******
  • Posts: 4760
  • Age: 27
  • Location: NYC
  • Cast me away from yesterday's things
Re: how do I make an amortization spreadsheet?
« Reply #3 on: November 14, 2012, 01:06:15 PM »
Have you tried using IPMT and PPMT? If you can't get those to work, you could always just do (periodic interest rate) * (last period's ending balance).

secondcor521

  • Handlebar Stache
  • *****
  • Posts: 2235
  • Age: 50
  • Location: Boise, Idaho
  • Big cattle, no hat.
    • Age of Eon - Overwatch player videos
Re: how do I make an amortization spreadsheet?
« Reply #4 on: November 14, 2012, 01:18:20 PM »
It should just be:

previous period balance * interest rate / 52 = interest charged in current period

When I do that with your numbers, though, I don't get the exact answer they do:

204062.75 * 3.69 / 100 / 52 = $144.81

compared to the $143.14 that they show on line 2.

It could be that the interest rate you're listing (3.69) is the effective APR and not the nominal rate on the loan.  It could also be that they do the amortization slightly differently since you have a weekly payment -- I haven't seen that before, and I'm not sure how the bank deals with the fact that a year is typically 52 weeks and 1 day long.

If you don't mind a little inaccuracy, the above formula should work.  Your new balance will just be your old balance plus the interest as calculated above minus your payment amount.  To get rid of the inaccuracy you'd probably have to ask your bank for more details about exactly how they calculate things.

2Cor521

sibamor

  • 5 O'Clock Shadow
  • *
  • Posts: 53
  • Location: Colorado Springs
Re: how do I make an amortization spreadsheet?
« Reply #5 on: November 14, 2012, 01:37:42 PM »
Here is an excel spreadsheet for amortization for fixed rate calculations with balloon payment option.

matt_g

  • 5 O'Clock Shadow
  • *
  • Posts: 67
Re: how do I make an amortization spreadsheet?
« Reply #6 on: November 14, 2012, 07:07:51 PM »
This is the one I use, why re-invent the wheel.  Spend the extra time paying it off so you don't have to use this spreadsheet.

http://www.vertex42.com/ExcelTemplates/loan-amortization-schedule.html

sibamor

  • 5 O'Clock Shadow
  • *
  • Posts: 53
  • Location: Colorado Springs
Re: how do I make an amortization spreadsheet?
« Reply #7 on: November 15, 2012, 07:55:17 AM »
This is the one I use, why re-invent the wheel.  Spend the extra time paying it off so you don't have to use this spreadsheet.

http://www.vertex42.com/ExcelTemplates/loan-amortization-schedule.html

@matt_g: This, I like this. I'm switching all my loans to this spreadsheet.