Forum Thread

Mortgage comparison XLS?

Dr. J 25,030 3,353 November 9, 2015 at 09:39 AM
Deal
Score
+1
3,402 Views

Thread Details

I'm looking to refi again (tried it relatively early on in the mortgage and the appraisal came in just a hair low so I shelved the plans); I have found a few mortgage comparison calculators online but some are giving me very odd numbers for payback.

Simply, payback for the refi is the # of months (essentially) at which the reduced interest cost makes up for the upfront cost of the refi (as refis usually have closing costs). Lots of online calculators get this correct for ONE of my loan options but totally blow the rest by wide margins - when the rate is lower but the refi cost stays more or less the same, the PBP should get narrower but the online calcs are telling me its years out, which cannot be correct.

Yes I can do it manually (and have) but aside from endless google searches can someone put up a link to a good calculator XLS that also allows for comparisons? The best ones I've found are from Vertex24 [vertex42.com].

12 Comments

1

Sign up for a Slickdeals account to remove this ad.

#2
Reply Helpful Comment? 0 0
Joined Nov 2005
L10: Grand Master
25,030 Posts
3,353 Reputation
Original Poster
Pro
#3

That's fixed or ARM. XLS preferred since I can save it locally.
Reply Helpful Comment? 0 0
#4
Dr J., I do not have a spreadsheet for you, but felt it appropriate to chime in as you touched on the important factor of closing costs and the time spread to break even.

As each State is unique with different levels of closing costs associated with a refinance (or a purchase) there is no one right answer and when measuring benefits one must also take into account the time frame of how long they plan to have this loan.

I am taking the liberty of copying a post I had in another thread as this may also apply when choosing the best mortgage.

THE LOWEST RATE MAY NOT ALWAYS THE BEST RATE: While this goes contrary to the way we think, and especially the way us Slick Dealers think, it is critical to measure the costs associated with any Note Rate. This is easier said than done as there are different areas the fees may be associated.

a) Standard Origination Costs: The word 'points' does not encompass the overall cost of a loan; as it is just one of a dozen possible ways to disclose lender origination fees. Origination, Discount, Administrative, Underwriting, Credit Report, Doc Prep, Tax Service, Flood Cert, and Appraisal Fee are all possible fees that may be imposed by the lender.

On refinances there are Title and Settlement Fees which a lender will quote which may also vary considerably. (Purchases will have these fees however customarily coming from the Title/Escrow Company handling the purchase.)

So, always compare the costs associated with any quoted rate; as in the end while you think you got a great rate you very likely paid for it. More on this in a minute.

b) All Rates Are Available Every Day; and what changes daily (and sometimes multiple times a day in volatile markets) is the price of the Note Rate. In real time pricing there is rarely a true zero point (also known as 'par') price to a specific Note Rate; and there is either a cost or a lender credit associated with the specific rate.

A knowledgeable mortgage broker will show you the stack of rates available; based on a certain rate lock period. As everyone's scenario is unique, having choices can be very helpful to best suit a borrower's needs.

For example (and this is just for illustration purposes and does not constitute a quote)):

Loan Amount $250,000

RATE PRINCIPAL & INTEREST PRICE

3.75% 1157.79/mo $1610 (,644 discount fee)
3.875% 1175.59/mo $85 (.034 discount fee)
4.00% 1193.54/mo -$1462.50 (.585 lender credit)
4.125% 1211.62/mo -$3502.50 (1.401 lender credit)

Now, default thinking would lead one right to 3.875% at just about zero points.
However,
First, as discussed above, what other fees may be associated with this stack of pricing?
You must weigh the costs into the analysis!

Second, 3.875% sounds better than 4.00%, BUT, this is not about bragging rights and choosing the best option to suit your needs.

On a refinance do you want to pay closing costs out of your pocket? Or do you want the costs rolled into the new loan where you lose equity in your home? Most people simply opt to have the costs rolled into the new loan amount as they will not feel a thing, but depending on how long you are planning to keep this loan, evaluating a higher rate with a lender credit to absorb costs may be a very valid option.

This also goes for a purchase as well, as depending on how long you plan on keeping this loan, or if your assets are really tight and you really could use less out of pocket at the table, take a look at all options.

For example, on the example above:
The price difference between 3.875% and 4.125% is $3587.50
The difference in monthly payment is $36.03/mo
This means it would take 99.57 months, or 8.29 years for the break even point where the extra monthly payment exceeds the credit you received at closing. Over 8 years, and that is not taking into account that the extra $36/mo is tax deductible, so it really is not costing you that much.

For many, this is where they may realize that the lowest rate was not the best rate for their individual needs.

Again, I cannot stress enough that, between their transaction and their individual profile, everyone is unique, and a knowledgeable and experienced Mortgage Broker can provide you all options to choose what is best for you.

Hope you find this information helpful;
-Adam
Reply Helpful Comment? 0 0
-Adam
Old Hippy & Mortgage Pro
Joined Nov 2005
L10: Grand Master
25,030 Posts
3,353 Reputation
Original Poster
Pro
#5
Quote from tiedyed1 View Post :
Dr J., I do not have a spreadsheet for you, but felt it appropriate to chime in as you touched on the important factor of closing costs and the time spread to break even.

As each State is unique with different levels of closing costs associated with a refinance (or a purchase) there is no one right answer and when measuring benefits one must also take into account the time frame of how long they plan to have this loan.

I am taking the liberty of copying a post I had in another thread as this may also apply when choosing the best mortgage.

THE LOWEST RATE MAY NOT ALWAYS THE BEST RATE: While this goes contrary to the way we think, and especially the way us Slick Dealers think, it is critical to measure the costs associated with any Note Rate. This is easier said than done as there are different areas the fees may be associated.

a) Standard Origination Costs: The word 'points' does not encompass the overall cost of a loan; as it is just one of a dozen possible ways to disclose lender origination fees. Origination, Discount, Administrative, Underwriting, Credit Report, Doc Prep, Tax Service, Flood Cert, and Appraisal Fee are all possible fees that may be imposed by the lender.

On refinances there are Title and Settlement Fees which a lender will quote which may also vary considerably. (Purchases will have these fees however customarily coming from the Title/Escrow Company handling the purchase.)

So, always compare the costs associated with any quoted rate; as in the end while you think you got a great rate you very likely paid for it. More on this in a minute.

b) All Rates Are Available Every Day; and what changes daily (and sometimes multiple times a day in volatile markets) is the price of the Note Rate. In real time pricing there is rarely a true zero point (also known as 'par') price to a specific Note Rate; and there is either a cost or a lender credit associated with the specific rate.

A knowledgeable mortgage broker will show you the stack of rates available; based on a certain rate lock period. As everyone's scenario is unique, having choices can be very helpful to best suit a borrower's needs.

For example (and this is just for illustration purposes and does not constitute a quote)):

Loan Amount $250,000

RATE PRINCIPAL & INTEREST PRICE

3.75% 1157.79/mo $1610 (,644 discount fee)
3.875% 1175.59/mo $85 (.034 discount fee)
4.00% 1193.54/mo -$1462.50 (.585 lender credit)
4.125% 1211.62/mo -$3502.50 (1.401 lender credit)

Now, default thinking would lead one right to 3.875% at just about zero points.
However,
First, as discussed above, what other fees may be associated with this stack of pricing?
You must weigh the costs into the analysis!

Second, 3.875% sounds better than 4.00%, BUT, this is not about bragging rights and choosing the best option to suit your needs.

On a refinance do you want to pay closing costs out of your pocket? Or do you want the costs rolled into the new loan where you lose equity in your home? Most people simply opt to have the costs rolled into the new loan amount as they will not feel a thing, but depending on how long you are planning to keep this loan, evaluating a higher rate with a lender credit to absorb costs may be a very valid option.

This also goes for a purchase as well, as depending on how long you plan on keeping this loan, or if your assets are really tight and you really could use less out of pocket at the table, take a look at all options.

For example, on the example above:
The price difference between 3.875% and 4.125% is $3587.50
The difference in monthly payment is $36.03/mo
This means it would take 99.57 months, or 8.29 years for the break even point where the extra monthly payment exceeds the credit you received at closing. Over 8 years, and that is not taking into account that the extra $36/mo is tax deductible, so it really is not costing you that much.

For many, this is where they may realize that the lowest rate was not the best rate for their individual needs.

Again, I cannot stress enough that, between their transaction and their individual profile, everyone is unique, and a knowledgeable and experienced Mortgage Broker can provide you all options to choose what is best for you.

Hope you find this information helpful;
-Adam

Yes I was hoping to distill multiple permutations. I have been looking at Amerisave... I like how they list the various rates, points (they don't call them points but that's essentially what they are) as well as various non-traditional mortgage options (like 25, 20, 7 and 5 year terms).

To keep things simple, I've just been looking at various terms and rates where the negative points are close (but not over) the estimated closing costs (which they lump into 2 figures that total about $2k regardless of term, rate or points). The other consideration is the payment, which I'd like to keep at more or less where we are now. Unfortunately any "automatic" calculator I find craps out with the lower terms for some reason.... probably because the payback is nearly immediate.

I am pretty sure, though, that the "negative" points are not refundable, meaning they must all be used as credits toward closing or any excess is forfeited. On that note I really wish they offered a ~ 16-17 year option.

For me I am moving from a 5.25% with FICO's 820+ so just about ANY option right now has an immediate payback provided the term is as long or shorter than current (just under 25 years). Amerisave's rates/points are the same for 25 and 30; There is a rate/points combo in the 20 yr list that puts me at a comparable payment with negative points to compensate for the closing costs, so basically no downside. Or I could go shorter (15) but that would jack up my (required) payment by about $300 or so. While I am sure I could handle it, for me it's worth the peace of mind just making overpayments with a slightly higher rate.
Reply Helpful Comment? 0 0
Last edited by Dr. J November 11, 2015 at 06:27 PM
#6
While you can obtain any amortization between 5 and 30 years, there are three primary pricing tiers:
30 year, 20 Year (which is very close to the 30) and 15 year (as a 10 year is very close to the 15). Most of my lenders offer the any term; so an 18 year Fixed would be priced the same as a 20 year.

While interest rates are still historically low I find many refinancing into a 30 Fixed which provides them the flexibility as well as the ability to save more towards investments that hope to yield higher returns.

In respect to the pricing with a lender rate credit (negative pricing), that credit offsets closing costs as well as prepaid expenses(i.e. escrow); and if there is a small (up to $500) excess the lender will customarily put it towards a principal reduction; or if larger force to close at a lower rate with less credit.

I know you were looking for a spreadsheet, but sometimes the simple math the old fashion way prevails. Either way, get out of that 5.25% asap!

-Adam
Reply Helpful Comment? 0 0
Joined Nov 2005
L10: Grand Master
25,030 Posts
3,353 Reputation
Original Poster
Pro
#7
Quote from tiedyed1 View Post :
While you can obtain any amortization between 5 and 30 years, there are three primary pricing tiers:
30 year, 20 Year (which is very close to the 30) and 15 year (as a 10 year is very close to the 15). Most of my lenders offer the any term; so an 18 year Fixed would be priced the same as a 20 year.

While interest rates are still historically low I find many refinancing into a 30 Fixed which provides them the flexibility as well as the ability to save more towards investments that hope to yield higher returns.

In respect to the pricing with a lender rate credit (negative pricing), that credit offsets closing costs as well as prepaid expenses(i.e. escrow); and if there is a small (up to $500) excess the lender will customarily put it towards a principal reduction; or if larger force to close at a lower rate with less credit.

I know you were looking for a spreadsheet, but sometimes the simple math the old fashion way prevails. Either way, get out of that 5.25% asap!

-Adam
I realize 30 is an option, but generally as the term lowers, so does the rate. So I could get a 30 at a higher rate than a 20 (or 15) and my payments would be lower, but the rate would be higher meaning more interest expense in the long run. Now, I realize I can get the 30 and make payments like I would if I had the 20 or 15, but the rate is still higher and will result in more interest expense than the 20 or 15 although not nearly as much as if the 30 was paid without the extra payments. This is often a discussion I have when I hear Dave Ramsey chime on people to get the lowest term possible because the rate will be lower - but then you lose a tremendous amount of flexibility. Not all of us are multi-millionaires and can buy houses with cash.

In the end it's a personal decision if the added expense of the higher rate is worth the flexibility you "buy" for that.
Reply Helpful Comment? 0 0
Joined Oct 2007
Foxiest of the Hounds
12,490 Posts
340 Reputation
#8
It's not an XLS but it does include important factors such as prepayment and tax deduction implications...

https://www.dinkytown.net/java/Mo...nance.html
Reply Helpful Comment? 0 0
Quote from toolio20 View Post :
Achievements are not for the meek, it is better to forfeit inheritance of the earth so that the mighty may look upon my gamerscore, and despair. Achievements are for men who dream; men who dare; men who achieve! Men who carve out entire galaxies with nothing more than nimble cheetos-dusted fingers and their wits, and in our leisurely twilight days we shall sit by softly flickering fires with our descendants recounting the tales of these unimaginable glories.

Sign up for a Slickdeals account to remove this ad.

Joined Nov 2005
L10: Grand Master
25,030 Posts
3,353 Reputation
Original Poster
Pro
#9
Thanks for the great input; now I am deciding on which term/rate/points combo to choose and more specifically whether to choose a lower rate and pay points.

For payback, you can't look at interest since rolling the additional points cost into the refi needs to be taken into account - so the metric I have been looking at is where the loan balance of the two "crosses".

For the options I've picked it looks to be about 11 years out - meaning it's better to choose the higher rate with "negative" points (basically wipes out closing costs plus a little bit) than the lower rate and roll the points and closing costs into the loan. The total interest difference between the two is about $5k but the difference in closing $$ is about $3k (not adjusted for inflation obv)
Reply Helpful Comment? 0 0
#10
Quote from Dr. J View Post :
Thanks for the great input; now I am deciding on which term/rate/points combo to choose and more specifically whether to choose a lower rate and pay points.

For payback, you can't look at interest since rolling the additional points cost into the refi needs to be taken into account - so the metric I have been looking at is where the loan balance of the two "crosses".

For the options I've picked it looks to be about 11 years out - meaning it's better to choose the higher rate with "negative" points (basically wipes out closing costs plus a little bit) than the lower rate and roll the points and closing costs into the loan. The total interest difference between the two is about $5k but the difference in closing $$ is about $3k (not adjusted for inflation obv)
Glad you posted this as this is what I was trying to illustrate above (i.e. that the lowest rate is not always the best rate). Also keep in mind the difference in interest being tax deductible which if factored in may bring you out even longer tan 11 years, making the slightly higher rate with lender credit to cover costs make even more sense.
-Adam
Old Hippy & Mortgage Pro
Reply Helpful Comment? 0 0
Joined Nov 2005
L10: Grand Master
25,030 Posts
3,353 Reputation
Original Poster
Pro
#11
Quote from tiedyed1 View Post :
Glad you posted this as this is what I was trying to illustrate above (i.e. that the lowest rate is not always the best rate). Also keep in mind the difference in interest being tax deductible which if factored in may bring you out even longer tan 11 years, making the slightly higher rate with lender credit to cover costs make even more sense.
-Adam
Old Hippy & Mortgage Pro

Yeah I've not been considering the interest writeoff in the calculations, just to keep things simple. The way that's done is to basically reduce the mortgage rate by the tax rate. I've never understood people's obsession with mortgage interest deductions.... it's ALWAYS better to not pay the interest in the first place than to get a ~ 20% discount on it.
Reply Helpful Comment? 0 0
#12
11 years seems like a long time to "break even" but I haven't done the math. I'm in a different situation where I'm currently 3 years into a 30 year fixed at 4% but knowing that we will be selling the home in the next 36 months (at worst 60 months but very unlikely events would have to occur for that to be the case) and I am looking at a 5 year ARM that comes in around the 3.375% rate with a little over a point in lender credit.

The breakeven between my current loan and the proposed loan would end up around 14 months, but I'm basing that on making the same payment I currently make, but with the new scenario I'm paying $100 less interest each month.

Sounds like you know what you're doing and maybe you have looked at it already but maybe look at it as if you make that 15 year payment amount (lets say $2500) regardless of the loan you chose, then see where the break even comes from.
Reply Helpful Comment? 0 0
Joined Oct 2007
Foxiest of the Hounds
12,490 Posts
340 Reputation
#13
Quote from Dr. J View Post :
Yeah I've not been considering the interest writeoff in the calculations, just to keep things simple. The way that's done is to basically reduce the mortgage rate by the tax rate. I've never understood people's obsession with mortgage interest deductions.... it's ALWAYS better to not pay the interest in the first place than to get a ~ 20% discount on it.
Of course, just as it's always better not to have any form of debt whatsoever. As for your calculations, you should indeed consider the tax implications since a significant amount of money may be at stake. The calculator I linked to already factors it in the results though, so make sure you pay attention to all the various break-even points it spits out.
Reply Helpful Comment? 0 0
Page 1 of 1
1
Join the Conversation
Add a Comment
 
Copyright 1999 - 2016. Slickdeals, LLC. All Rights Reserved. Copyright / Infringement Policy  •  Privacy Policy  •  Terms of Service  •  Acceptable Use Policy (Rules)  •  Interest-Based Ads
Link Copied to Clipboard