Mortgage extra payments

[fjdurbin]

I was talking to a Realtor today and he said that if someone makes an extra mortgage payment each year a 30 year loan can be paid off in 17 years.

Is there a formula for calculating this? thanks...JD

 

[stnkynts]

http://office.microsoft.com/en-us/e...-and-loan-analysis-templates-HA001034640.aspx

 

[fjdurbin]

Creating a mortgage calculator is not my problem. What I want to be able to figure out is if I borrow 200,000 on a 30 year loan and I make 13 payments a year instead of 12 how many years do I shave off of my 30 years.

I don't want to use my mortgage calculator plus use brute force/trial and error to accomplish what I suspect can be done with a formula.

Thanks

 

[rob_sheeds]

=fvschedule

 

[Edleets]

You better use an amortization table to find exactly the time. However, I found a way of getting to a very close answer using the formula "NPer(rate,Pmt,PV,type)". The only trick is to add to the regular pmt the extra payment prorated by 12 months. I know it is not an exact match since using compund interest is based on actual timing. However, it is very, very close. See for example:

Loan = PV = 200,000
years = 30 (360 months)
interest = 8%/year
payment will be (using the pmt function) = $1,467.53

If not additional payment, the total of periods to pay off is:
Nper(8%/12,pmt,-PV,,) = 360 months = 30 years

Adding 1 aditional payment of $1,467.53 prorated by 12 months = 122.29 extra each month

the total period to pay off will be:
Nper(8%/12,pmt+122.29,-PV,,) = 277 months = 23 years

Do the amortization table and compare the results. almost the same !!!.

Thanks
Ed

 

[fjdurbin]

Thanks for the reply. I had found your suggestion in an article about mortgage calculations. I had put a column in my table for extra payments but it hadn't occurred to me to put 1/12 of the payment in each of the extra payment cells...JD

 

[mancemonster]

I've had a play around with this idea before...

What I do is...

Line 1..

<TABLE style="WIDTH: 385pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512 x:str><COLGROUP><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 987" width=54><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 1974" width=108><COL style="WIDTH: 167pt; mso-width-source: userset; mso-width-alt: 4059" width=222><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 2340" width=128><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17 width=54 x:num>200000</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=108 x:num="1333.3333333333333">=+A1*$E$1/12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 167pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=222 x:num="1467.5291477588" x:fmla="=1467.5291477588">=1467.5291477588</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=128 x:num="199865.80418557455" x:fmla="=+A1+B1-C1">=+A1+B1-C1</TD></TR></TBODY></TABLE>

Line 2

<TABLE style="WIDTH: 385pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512 x:str><COLGROUP><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 987" width=54><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 1974" width=108><COL style="WIDTH: 167pt; mso-width-source: userset; mso-width-alt: 4059" width=222><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 2340" width=128><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17 width=54 x:num="199865.80418557455">=+D1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=108 x:num="1332.4386945704971">=+A2*$E$1/12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 167pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=222 x:num="1467.5291477588">=+C1</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=128 x:num="199730.71373238627" x:fmla="=+A1+B1-C1">=+A2+B2-C2</TD></TR></TBODY></TABLE>

Dragged down to line 12.

Line 13

<TABLE style="WIDTH: 385pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=512 x:str><COLGROUP><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 987" width=54><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 1974" width=108><COL style="WIDTH: 167pt; mso-width-source: userset; mso-width-alt: 4059" width=222><COL style="WIDTH: 96pt; mso-width-source: userset; mso-width-alt: 2340" width=128><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 41pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 height=17 width=54 x:num="198329.27203805826">=+D12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 81pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=108 x:num="1322.1951469203884">=+A13*$E$1/12</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 167pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=222 x:num="1467.5291477588">=IF(D12<+C1,A13+B13,C1)</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 96pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl24 width=128 x:num="198183.93803721986" x:fmla="=+A1+B1-C1">=+A13+B13-C13
</TD></TR></TBODY></TABLE>

Copy down to line 360

E1 is your interest rate (in this case 8%)

Use tools goal seek set D360 to equal 0 by changing C1.

Then play with your over payments to see which month =0

Line No /12 will give you the year..


The timescale for how quickly 1 month extra payment will clear your loan will depend on...

1) When you make the payment... (Begining of the year, end or monthly)

2) The % Interest rate..


Regards

Roger

 

[Richard Schollar]

The time taken off the total mortgage term is pretty sensitive to the mortgage rate employed - higher rates will increase the compounding effect and thus reduce the term faster than lower rates for a given overpayment (which seems almost counter-intuitive)

 

[number kruncher]

Paying off the loan early sounds good, however, as the 'value' of money changes through the 30 years, it's actually not worth paying more for it now.

Also, the loaner gets their money faster, and if that's a bank, let them wait.

 

[fjdurbin]

I do have a very good interest rate and I will enjoy the interest deductions on my taxes. But if the govt. eliminates the deduction or if the money saved by paying off early is greater than what I could earn on the money otherwise then paying early is the better "investment."

I'm not a fan of debt.

As far as investments are concerned, I remember all of the investment counselors who told me, "the money you have in savings is losing you money due to inflation." I listened, invested a lot (at least a lot for me) and lost most. I sure wish that money were still in savings!