looking for an amortization calculator

Factual Questions
1

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.

2

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.

3

well, I’ll be damnned…:smack:

4

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,J412,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.

5

You need to enter additional payments as negative numbers.

It should look something like this:



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
6

even easier.

I used the built in amortization template already included in Excel

7

Eh, mine’s better :slight_smile:

8

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?

9

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.

10

They probably have an interest only loan. Wikipedia has an article on it.

11

For a while I was making extra principle payments quarterly and I used this calculator. It is flexible with regards to letting you assign different payments at different frequencies.