how do I make an amortization spreadsheet?

[strider3700]

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]

Download this template from Microsoft's web site

http://office.microsoft.com/en-us/templates/loan-amortization-schedule-TC010073881.aspx?CTT=1

[jpo]

http://www.hughchou.org/calc/genloan.php

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

[grantmeaname]

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]

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]

Here is an excel spreadsheet for amortization for fixed rate calculations with balloon payment option.

[matt_g]

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]

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.