# looking for an amortization calculator **URL:** \ **Category:** Factual Questions\ **Created:** [April 2, 2013, 2:21pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624 "2013-04-02T14:21:08Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Sigene](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@Sigene](https://boards.straightdope.com/u/Sigene)\ **Post date:** [April 2, 2013, 2:21pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624/1 "2013-04-02T14:21:08Z")
I’m pretty sure this is GQ I’d like to find an amortization calculator that I can put in the relevant principal, interest rate, number of years for the mortgage and payments. But the one I want needs to allow me to add one time payments to the principal, so I can see how much interest Id save and how much shorter the loan would be for. I’ve found a couple online, but none of them allow me the option of adding principal payments to see how it affects my total interest paid or lets me know how much sooner I can pay off the loan. ---
**Author:** ![Machine\_Elf](https://avatars.discourse-cdn.com/v4/letter/m/82dd89/32.png) [@Machine\_Elf](https://boards.straightdope.com/u/Machine_Elf)\ **Post date:** [April 2, 2013, 2:43pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624/2 "2013-04-02T14:43:51Z")
You can build your own in an Excel spreadsheet. That gives you the freedom to set it up any way you want, incorporating arbitrarily large/small payments for certain periods. ---
**Author:** ![Sigene](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@Sigene](https://boards.straightdope.com/u/Sigene)\ **Post date:** [April 2, 2013, 3:45pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624/3 "2013-04-02T15:45:26Z")
well, I’ll be damnned…:smack: ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [April 2, 2013, 4:02pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624/4 "2013-04-02T16:02:17Z")
Excel version: c4: Starting Principle amount, as a positive number d4: =$L$4 e4: blank spot to enter an additional payment amount (this will be the whole ‘e’ column) f4: =($I$4/12)_C4 g4: =SUM(C4:F4) i4: = interest rate, written as a percentage or with the decimal - so 0.05 or 5%, for example j4: the term, in years l4: =PMT(I4/12,J4_12,C4,0,0) c5: =G4 After you type in c5, select all of the rest of d4 through g4 and copy the formula down below in row 5 (don’t copy c4 over c5). After that, you can drag the formulas from all of c5 through g5 down all the way down until you cover all of the periods in your loan. These are the column labels I used, if it helps: Column C: Principle (at the start of the period) Column D: Standard payment Column E: Additional Payment Column F: Interest Accrued During Period Column G: Prinicple Remaining at the End of Period I also added a Column B, where I list the period numbers - you could use dates here for convenience. I4 is the monthly interest rate. J4 is the term, in years. L5 is the calculated payment. You can double check that everyhting is working right by not entering any addiitonal payments and dragging the formulas down all of the way until the end of the loan term and you should see an ending priciniple amount that is zero, or extremely close to zero. ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [April 2, 2013, 4:05pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624/5 "2013-04-02T16:05:35Z")
You need to enter additional payments as negative numbers. It should look something like this: ```auto period principle payment add'l payment interest end prin interest term (in years) std payment 1 $100,000.00 $(2,997.09) $- $416.67 $97,419.58 5% 3 ($2,997.09) 2 $97,419.58 $(2,997.09) $- $405.91 $94,828.40 3 $94,828.40 $(2,997.09) $- $395.12 $92,226.43 4 $92,226.43 $(2,997.09) $- $384.28 $89,613.62 5 $89,613.62 $(2,997.09) $- $373.39 $86,989.92 6 $86,989.92 $(2,997.09) $- $362.46 $84,355.29 7 $84,355.29 $(2,997.09) $- $351.48 $81,709.68 8 $81,709.68 $(2,997.09) $- $340.46 $79,053.04 9 $79,053.04 $(2,997.09) $- $329.39 $76,385.34 10 $76,385.34 $(2,997.09) $- $318.27 $73,706.52 11 $73,706.52 $(2,997.09) $- $307.11 $71,016.55 12 $71,016.55 $(2,997.09) $- $295.90 $68,315.36 ``` ---
**Author:** ![Sigene](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@Sigene](https://boards.straightdope.com/u/Sigene)\ **Post date:** [April 2, 2013, 5:09pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624/6 "2013-04-02T17:09:36Z")
even easier. I used the built in amortization template already included in Excel ---
**Author:** ![Darth\_Panda](https://avatars.discourse-cdn.com/v4/letter/d/ee7513/32.png) [@Darth\_Panda](https://boards.straightdope.com/u/Darth_Panda)\ **Post date:** [April 2, 2013, 5:43pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624/7 "2013-04-02T17:43:59Z")
Eh, mine’s better 🙂 ---
**Author:** ![phouka](https://avatars.discourse-cdn.com/v4/letter/p/c5a1d2/32.png) [@phouka](https://boards.straightdope.com/u/phouka)\ **Post date:** [April 2, 2013, 5:56pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624/8 "2013-04-02T17:56:42Z")
Related question: My mom recently commented that she and Dad had paid off the interest on their mortgage. This makes no sense to me. I understand that monthly payments are a combination of principal and interest, and the further out you get, the more you’re paying towards principal and the less towards interest. I also understand that the documentation for a mortgage will tell you exactly how much interest you’ll pay over the life of the mortgage, but, don’t you finish paying off the interest when you pay off the mortgage? ---
**Author:** ![President\_Johnny\_Gentle](https://avatars.discourse-cdn.com/v4/letter/p/5f9b8f/32.png) [@President\_Johnny\_Gentle](https://boards.straightdope.com/u/President_Johnny_Gentle)\ **Post date:** [April 2, 2013, 6:52pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624/9 "2013-04-02T18:52:11Z")
> [@phouka](#): > > Related question: > > My mom recently commented that she and Dad had paid off the interest on their mortgage. This makes no sense to me. I understand that monthly payments are a combination of principal and interest, and the further out you get, the more you’re paying towards principal and the less towards interest. I also understand that the documentation for a mortgage will tell you exactly how much interest you’ll pay over the life of the mortgage, but, don’t you finish paying off the interest when you pay off the mortgage? Technically, yes, interest is accruing throughout the life of the mortgage. However, as you note, it is possible to calculate on a fixed-rate mortgage exactly how much interest you’ll pay through the life of the loan. I would interpret your mom’s comments as saying that the sum of the mortgage payments made to date are equal to the amount of interest over the life of the loan. Which means that the total of the remaining payments will be equal to the original principal. ---
**Author:** ![Sigene](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@Sigene](https://boards.straightdope.com/u/Sigene)\ **Post date:** [April 2, 2013, 7:43pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624/10 "2013-04-02T19:43:51Z")
They probably have an interest only loan. Wikipedia has an article on it. ---
**Author:** ![USCDiver](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/uscdiver/32/354_2.png) [@USCDiver](https://boards.straightdope.com/u/USCDiver)\ **Post date:** [April 2, 2013, 7:52pm UTC](https://boards.straightdope.com/t/looking-for-an-amortization-calculator/654624/11 "2013-04-02T19:52:41Z")
For a while I was making extra principle payments quarterly and I used [this calculator](http://www.decisionaide.com/mpcalculators/extrapaymentscalculator/extrapayments1.asp). It is flexible with regards to letting you assign different payments at different frequencies.