I was working on a Refinance this week and was confronted with various options: Different rate/point combinations, different lender fees, etc., and this got me thinking – I should just build a Net Present Value model to see which loan offered me the “best deal”. Without objectively assessing all the cash flows in and out, “best” is subjective. However, if you consider what the “present value” is of each offer and apply consistent criteria to each option, you can objectively assess the optimal solution. Therefore, I constructed a Net Present Value (NPV) model for my various options at hand. Present Value is simply the series of cash flows over time in terms of present dollars. It factors in inflation and what you could make investing your money elsewhere.

**Demonstration**

The following 3 options exist for a conventional 30 year mortgage refinance:

**5.25%**rate,**zero points, $4500**in closing fees at Lender 1.**4.75%**rate,**1 point, $4500**in closing fees at Lender 1.**4.875%**rate,**zero points, $2900**in closing fees at Lender 2

Can you look at these 3 options and clearly determine which is the “Best Deal”?

**Ignore the escrow for taxes and insurance since you should end up paying in roughly what you’ll get back from your existing lender at closing. **Assumes a point costs $3000 due to a $300,000 mortgage.*

**How to Construct your own NPV model**

- The first thing you need to do is determine what your
**discount rate**is. The discount rate is the return that could be earned on an investment in the financial markets with similar risk. Whereas business use a much higher number like say, 12%, individuals should use a much lower number which reflects the true expected return on what they’d be doing with the money otherwise. In my case, I’m using 4.5%, since it’s roughly the return you can get on intermediate term CDs and Money Markets over the long haul. I realize rates have dropped dropped dramatically in the past few months, but as recently as last year and for the several preceding years, you could easily earn 4-5% in such investments. In a past life, I would have considered using 8% matching something closer to the long run return in stocks, but I no longer consider that to be a consistent assumption. - Next, you need to
**consider your time horizon**. I’m going to construct my model as a series of years, but you can use months, days, whatever you want, as long as you set the discount rate to match the same time period (i.e. if you were using months, a 12% annual discount rate would be entered as 1%). I’m going to look a 10 year year example. It’s a 30 year conventional mortgage, but a 10 year horizon is usually plenty of time to assess a situation. Heck, I might move within 10 years or CD rates may skyrocket to 7% and the discount rate I had used is no longer relevant. - Finally, you must
**consider your cash flows**. In our case, there will be an upfront cash flow out for the points and closing fees. Subsequent years will be considered cash inflows due to the decreased mortgage payments. I simply subtracted the savings each month for the lower rate, annualized it and plugged in an annual savings number as my annual inflow. I realize it’s a bit more complicated, like consider the tax impact of interest mortgage deduction and the change in interest/principal amortization over time, but for the example I’m presenting here, I’ve simplified it a bit. I didn’t share my specific model since I’m switching from an ARM to a conventional and I had to include several other factors which really would have complicated the explanation. - The
**NPV Function**in Excel requires you to input the discount rate first, followed by the annual cash flows (positive or negative). You can hold <CTRL> and drag the mouse to capture all the values. I’ve displayed the formulas in blue in snapshot below if you want to try to recreate your own file.

**Now for the Formulas and Results:**

**Explanation:**

In “Present Dollars”, it turns out that option 3 has the highest value to the decision maker. While it may sound tough to believe, by making a decision today with a few hours of paperwork and some upfront investment, your present value increases by over $13,000 over the next 10 years; more than double that over 30. This is no different than someone putting $13,000 in your pocket today. If it is different, than your assumptions were incorrect. Other than not having $13,000 to spend immediately, these discounted cash flows coming back to you via lower mortgage payments are worth that much. It’s that simple!

**Additional Uses of NPV in your own Personal Finances**

NPV can be used for myriad situations you’ll face in life. Check this out – the day you buy a new puppy, you may have just set yourself back $70,000 in present day value!!! – Dog Cost Analysis. I used a similar method when I bought a tractor for mowing the lawn. I figured in what I’d be spending per year for a lawn service vs. buying a tractor and paying for gas. While I didn’t factor in what my time was worth and frankly, I would never pay someone else to mow my lawn anyway, it was still a neat exercise to show that my 10 year savings would be something around $6800, which discounted back, would still be several thousand dollars in NPV. NPV is really most useful in decided between various options as I highlighted in the Refi example.

You should consider getting some quick quotes from reputable lenders quickly and effortlessly from outfits such as Quicken Loans.

UPDATE 4/29/09:I actually ended up getting anincredible Refi deal. Get this:

–4.625%

-30 year conventional

-no points

-reasonable fees(actually, the lowest fees of all the options I considered as well).Upon comparing with several other options (including my existing originator which was horrendous) and using my NPV model, none of them held a flame to what I was able to achieve with the mortgage consultant I worked with. Since I’m pleased with all aspects from the communication to the accuracy of the good faith effort to the expediency of closing the deal in less than a month, I can honestly say that I endorse Jeff Harris for anyone considering a refi. We live in different states and did everything remotely and efficiently, so anyone considering a refi should at least put him on your list of top options to evaluate; worked out great for me!

**Here’s his contact info:**

1-800-962-4145 or visit Jeff’s Webpage

Do you have any similar examples of decisions you need to make where NPV would be valuable?

### Related Articles

If you enjoyed this post, you can get free updates through RSS Feed or via Email whenever a new post is published. Rest assured that you can unsubscribe at any time via the automated system and your information will not be sold, archived or utilized for any other "nefarious" purposes.

{ 11 trackbacks }

{ 13 comments… read them below or add one }

That is a very nice way to weed through a list of confusing offers. If you are willing to make the spreadsheet available publicly for download, please let us readers know.

[Reply]

Sure, I’m glad you enjoyed. I haven’t researched how to leave a file for download in the article – if any WordPressers know how to, please let me know. Otherwise, anyone interested in a copy can send a note through my Contact page and I’d gladly email it when I get a chance.

[Reply]

This is great stuff. I have been learning about some of this type of things in my business classes in college. Well-written.

[Reply]

This is finance porn. My fiance gets annoyed when she finds me messing with my TI BAII Plus. Thanks for doing this, I don’t have the patience to write about the details of finance, but I love reading it.

Not sure how to download a template from WordPress, I’ve never done it either. Perhaps you can attach a link at the bottom that just says to email you for it. But again, I’m a lazy blogger.

[Reply]

Well, if you can have the file hosted, so you can have an html link, then this plugin allows you to insert html to point to the file

http://www.matteoionescu.com/wordpress/embed-html/

[Reply]

I appreciated your article as a consumer. As a mortgage broker, it’s often difficult to explain to borrowers the differences they see on various good faith estimates/rate comparison websites/etc, and this would help me elucidate the options I can offer.

If you’re willing to email it over, I’ll definitely give you credit when I use it. I direct my clients to various personal finance and mortgage blogs, because in the end they trust that I’ll give them all the information they need to make an informed decision. Trust breeds trust, and that’s how I earn business and referrals.

Thanks!

Christian

[Reply]

College loans if consolidated provide you with great benefits as they are the most flexible and consumer friendly loans. If you know the ideal way of how to consolidate your loan then you could derive benefits such as improving your credit rating and lowering debt to income ratio. There are various ways through which you could do this, however we will highlight five ways through which you could save money by refinancing student loans.

[Reply]

A great idea, and a great tool!

A suggestion: I noticed that the formula took into account only 10 years (column L). This may work fine if all annual cash flows are the same, and for the same number of years. But if, for example, you have 24 years left on your current 30 year mortgage, and you are comparing it to potential 30, 20, and 15 year options with differing rates and fees, and payment amounts, you really should carry the calcs out to full term. Other than that – GREAT TOOL!

[Reply]

Michelle C Reply:

August 29th, 2011 at 11:56 am

@mark finney, Mark – That’s exactly what I’m trying to do right now. Compare my current mortgage with 24 years left on it with a re-fi on another 30 year loan. How best can I adapt this spreadsheet to do that? Put a negative “savings” in years 26-30 for 12*monthly payment?

[Reply]

I appreciated the article regarding portfolio hedging using SPY. How do I get the spreadsheet that the author offered?

Thanks

[Reply]

The “Initial Payments at Closing” in Example 3 is wrong. It should be $2,900.00 not $4,500.00. The NPV is even greater than you indicate—–$14,981

[Reply]

Please answer this: If I have a 30yr mortgage @ 6%, monthly payment is $1,199 with 10 years remaining on loan balance verses getting a new 30yr mortgage @ 3.5% with a monthly payment of $898, which is better if I want to stay in the house until it’s paid off……. I think keeping the original mortgage?

This is probably a different problem/analysis— unless you plan to be out of the house @ 10 years.

[Reply]

This is a great article and you wrote it in an a way that is easy to understand, and put it in a useful context! There are so many ways that NPV can be useful, and a mortgage is particularly important… especially with the market the way it is.

[Reply]

this is a very rudimentary model for NPV. what about tax implications? can you make this more generic where your can input all the parameters?

[Reply]