## Rate of return on mortgage points, calculator, please explain

### Rate of return on mortgage points, calculator, please explain

Can anyone explain to me what this calculator is doing:

https://www.mtgprofessor.com/calculator ... or11c.html

It calculates a rate of return you get on paying points on a mortgage, compared to another mortgage with less or no points.

I don't really get what this rate or return means.

https://www.mtgprofessor.com/calculator ... or11c.html

It calculates a rate of return you get on paying points on a mortgage, compared to another mortgage with less or no points.

I don't really get what this rate or return means.

### Re: Rate of return on mortgage points, calculator, please explain

The "rate of return" treats the points as a discrete investment. Very simple example: let's say you paid $5,000 in points and the lower interest rate lowered your mortgage payment by $50/month. It would be kinda like buying a bond that paid 12% annual interest ($600/$5000). There are a couple important differences: the "bond" is lost (rather than redeemed) when you pay off the mortgage, and the mortgage amortizes faster with a lower interest rate.

In general, points offer a very good rate of return (especially after taxes) if you hold the mortgage longer than the break-even period (usually around 3-5 years). I've seen rates of return of 10-20% after taxes, which is excellent for a "guaranteed" investment. On the other hand, there's a risk that you have a negative return if you pay off the mortgage early (by refinancing, selling the property, or just plain paying it off). Moving out and keeping the property as a rental still gets you the benefit of the points, although perhaps a smaller benefit after taxes if all of the interest becomes deductible, and it wasn't as a primary residence. So you need to look at your personal situation and decide how likely it will be that you keep the mortgage for a significant length of time.

In general, points offer a very good rate of return (especially after taxes) if you hold the mortgage longer than the break-even period (usually around 3-5 years). I've seen rates of return of 10-20% after taxes, which is excellent for a "guaranteed" investment. On the other hand, there's a risk that you have a negative return if you pay off the mortgage early (by refinancing, selling the property, or just plain paying it off). Moving out and keeping the property as a rental still gets you the benefit of the points, although perhaps a smaller benefit after taxes if all of the interest becomes deductible, and it wasn't as a primary residence. So you need to look at your personal situation and decide how likely it will be that you keep the mortgage for a significant length of time.

### Re: Rate of return on mortgage points, calculator, please explain

Hi, thanks for the reply.

Wouldn't the rate of return also include the lower amout of interest you are paying per month with the lower rate? And not just the lower monthly payment? It seems that if I'm paying less interest over the life of the loan and paying principal off faster, then that is also a return I'm earning from having paid the points.

That's why I'm wondering what the calculator on the Mortgage Professor's website is actually including in the rate of returns it specifies of a given period of time. And if it is an annual rate of return that the calculator is producing.

Or do you have a suggestion about how else to calculate the rate of return on different amounts of points?

I saw your post from this thread, viewtopic.php?t=287139#p4674662, talking about rate of return on mortgage points. But it's unclear to me how you're doing the calculation in the spreadsheet you link to there.

[Edit: Just to try to be clear. I understand what rate of return means in general. My question in the OP was meant to be: How is the rate of return on the Mortgage Professor's website being calculated? What is being factored into what constitutes the rate of return on mortgage points? And also, why does it vary over time?]

Wouldn't the rate of return also include the lower amout of interest you are paying per month with the lower rate? And not just the lower monthly payment? It seems that if I'm paying less interest over the life of the loan and paying principal off faster, then that is also a return I'm earning from having paid the points.

That's why I'm wondering what the calculator on the Mortgage Professor's website is actually including in the rate of returns it specifies of a given period of time. And if it is an annual rate of return that the calculator is producing.

Or do you have a suggestion about how else to calculate the rate of return on different amounts of points?

I saw your post from this thread, viewtopic.php?t=287139#p4674662, talking about rate of return on mortgage points. But it's unclear to me how you're doing the calculation in the spreadsheet you link to there.

[Edit: Just to try to be clear. I understand what rate of return means in general. My question in the OP was meant to be: How is the rate of return on the Mortgage Professor's website being calculated? What is being factored into what constitutes the rate of return on mortgage points? And also, why does it vary over time?]

### Re: Rate of return on mortgage points, calculator, please explain

In fact, the rate of return should include primarily interest difference. Suppose you pay points on a loan which reduce the monthly payment by $50 and the interest on the first payment by $60 (that is, the first payment covers $10 more principal because of the lower rate). Your net worth is $60 higher because of the rate reduction; $50 is in your pocket, and the other $10 is growing at the mortgage rate. The rate of return thus includes the $50 for one month, and the $10 until the loan payoff time.cb474 wrote: ↑Fri Aug 14, 2020 11:56 pm Wouldn't the rate of return also include the lower amout of interest you are paying per month with the lower rate? And not just the lower monthly payment? It seems that if I'm paying less interest over the life of the loan and paying principal off faster, then that is also a return I'm earning from having paid the points.

As a result, a good estimate for the break-even on points is slightly longer than the ratio of points to interest rate. If you pay 0.75 points to get a 0.125% rate reduction, you don't quite break even in six years, because you paid 0.75% of the original balance, but the 0.125% savings was applied to a decreasing balance, and you paid the 0.75% up front but got the 0.125% back over six years. (A check of the Mortgage Professor's calculator, with a 3.5% 30-year loan or 3.375% with 0.75 points, shows a break-even of about seven years.)

### Re: Rate of return on mortgage points, calculator, please explain

I can't speak to how Mortgage Professor's website calculates the rate of return, because I didn't write the code and it doesn't show how how it works under the hood. But, my spreadsheet agrees pretty closely (within 0.1-0.2%) with the "After-tax" number that website generates, so I assume it uses similar methods.cb474 wrote: ↑Fri Aug 14, 2020 11:56 pm Hi, thanks for the reply.

Wouldn't the rate of return also include the lower amout of interest you are paying per month with the lower rate? And not just the lower monthly payment? It seems that if I'm paying less interest over the life of the loan and paying principal off faster, then that is also a return I'm earning from having paid the points.

That's why I'm wondering what the calculator on the Mortgage Professor's website is actually including in the rate of returns it specifies of a given period of time. And if it is an annual rate of return that the calculator is producing.

Or do you have a suggestion about how else to calculate the rate of return on different amounts of points?

I saw your post from this thread, viewtopic.php?t=287139#p4674662, talking about rate of return on mortgage points. But it's unclear to me how you're doing the calculation in the spreadsheet you link to there.

[Edit: Just to try to be clear. I understand what rate of return means in general. My question in the OP was meant to be: How is the rate of return on the Mortgage Professor's website being calculated? What is being factored into what constitutes the rate of return on mortgage points? And also, why does it vary over time?]

I used a discounted cash flow analysis to calculate the rate of return. Specifically, I used Excel's XIRR function, which calculates the internal rate of return for the

*difference*in cash flows between the baseline mortgage and the mortgage with points. In technical terms, the internal rate of return is the discount rate that makes the net present value of the difference in cash flows equal to zero. If that's too esoteric, just think of it like treating the difference in payments with no points vs points as a discrete investment, and then calculating the rate of return on that investment.

There are four components to the difference in cash flow:

- The points payment up front (obviously, negative cash flow)
- Difference in payments (these are positive cash flow, because payments with points are smaller). The way I'm doing it, it doesn't matter what the breakdown of interest vs. principal is here, because the difference in amortization is captured in the next item
- Differences mortgage amortization. With a lower interest rate, the mortgage amortizes (pays down the principal) faster, so if you pay off the mortgage before the full term, you owe less money when you pay off the mortgage.
- Differences in taxes. If points are deductible, you get a tax credit to partially offset the points, and likewise for interest paid. My spreadsheet can calculate any combination of deductible or non-deductible points or interest. It doesn't currently handle partial deductibility, like if interest is capped by the $750,000 federal limit, but this would be straightforward to add.

- Large negative RoR <3 years (you lose your points and don't get much in return)
- Break-even (0% RoR) in 3 years
- RoR gets good very quickly from here forward: 8.3% in 4 years, 12.7% in 5 years, 15% in 6 years, peaking at 17.3% around 10 years
- RoR actually drops a bit over the rest of the loan, down to 16.3% if held full-term. Don't get led into thinking that points aren't as good a "deal" if held for full-term though; even though the RoR is slightly less, because you're getting a still-excellent return for a much longer amount of time. For example, if you could buy a 10-year bond paying 17% or a 30-year bond paying 16%, which would you take? I'd take the 30-year bond for sure, because it will produce many more absolute dollars.

### Re: Rate of return on mortgage points, calculator, please explain

The return is based on four things, which can be considered as parameters to the RATE function:

- How long the mortgage is assumed to be held before it is refinanced or paid off. (Nper)
- The dollar amount of the points, treated as an investment. (Pv)
- The reduction in monthly payment from choosing the lower rate mortgage with points. (Pmt)
- The difference in remaining loan balance between the two options after the specified holding period. (Fv)

**fyre4ce**'s spreadsheet [2] that you referenced. (Except to simplify things I'm assuming no down payment and a 0% tax rate.)

Code: Select all

```
1 Loan 480,000
2 Loan years 30
3 Case 1 Case 2 Diff
4 Rate 2.3230% 3.6250%
5 Points 4.1667% 0.0000% 20,000
6 Payment 1,852.70 2,189.05 336.34
7 Hold Years Balance Balance Return
---------- ------- ------- ------
8 2 457,335 461,633 4,297 (32.86%)
9 5 421,304 431,465 10,161 13.18% [3]
10 15 281,357 303,597 22,240 20.30%
11 30 0 0 0 20.13%
```

- Mortgage Professor's results agree with these four cases.
- The spreadsheet doesn't exactly agree because of a small bug: it omits the final month's payment difference from the cash flow.
- Here are the calculations showing how the 13.18% return for a 5-year holding period is calculated, using the Excel PMT, FV, and RATE functions.
Code: Select all

`20,000 = 480000 * 4.1667% 1,852.70 = PMT(2.323% / 12, 30 * 12, -480000, 0, 0) 2,189.05 = PMT(3.625% / 12, 30 * 12, -480000, 0, 0) 421,304 = FV( 2.323% / 12, 5 * 12, 1852.70, -480000, 0) 431,465 = FV( 3.625% / 12, 5 * 12, 2189.05, -480000, 0) 13.18% = RATE( 5 * 12, 336.34, -20000, 10161, 0) * 12`

### Re: Rate of return on mortgage points, calculator, please explain

Thanks to everyone for the technical explanations. I'll have to take some time to digest this, since many of these concepts are new to me.

*

fyre4ce, it's interesting that the loan you recently analyzed showed a breakeven point of 3 years. That's basically what I found just by comparing amortization tables for the different loans I'm considering and adding up the interest paid over different periods of time and the monthly savings on the lower mortgage payment, to figure out at what point is the remaining balance on the two loans is the same (if I get points I'm planning to roll the cost into the loan balance, so that's why I looked at it that way). I guess grabiner's rule of thumb to simply divide the points paid by the amount of reduction in the interest rate would also come up with a 3 year break even point for the loan you analyzed.

I have the extra complication when comparing loans, that if I take the points, as I said, I'm planning to roll the cost into the balance of the loan, so I can't simply use a spreadsheet that assumes the loan amount stays the same, with and without points.

One question I have now, is that when you say, for example, that the rate of return is 12.7% in 5 years, does that mean that it's as if having a 12.7% annualized rate of return for those five years?

Also, I downloaded your spreadsheet. Is there a way to use to to compare two different loans both with points, one with less the other more? It seems to be set up to assume that one loan has to have no points on it. This differs from the Mortgage Professor's calculator, where you can simply enter the rate and points for two different loans and compare them. Also, your spreadsheet does not include the cost of the points in the balance of the loan, as I'm planning to do. Is there a way to modify it to do that as well? (I'm really a novice spreadsheet user--I understand the outputs, but a lot of the functions are fairly opaque to me.) I'd like to see the whole chart like in your spreadsheet.

*

#Cruncher, thanks for the examples in the tables. That's helpful. It took me a minute to get it, but I think I understand what you're showing there.

*

Lastly, for now, does it seem like the right way to think about this is that whatever amount of points that has the best rate of return, over the time I expect to be in the house, is the best deal (assuming also that I'm confident I will not sell the house or otherwise payoff the loan before the breakeven time period)? I guess I'm also assuming that rates are so low right now that it's pretty unlikely I will refinance again, perhaps ever (I'm looking at rates in the mid to low 2.x% range).

*

fyre4ce, it's interesting that the loan you recently analyzed showed a breakeven point of 3 years. That's basically what I found just by comparing amortization tables for the different loans I'm considering and adding up the interest paid over different periods of time and the monthly savings on the lower mortgage payment, to figure out at what point is the remaining balance on the two loans is the same (if I get points I'm planning to roll the cost into the loan balance, so that's why I looked at it that way). I guess grabiner's rule of thumb to simply divide the points paid by the amount of reduction in the interest rate would also come up with a 3 year break even point for the loan you analyzed.

I have the extra complication when comparing loans, that if I take the points, as I said, I'm planning to roll the cost into the balance of the loan, so I can't simply use a spreadsheet that assumes the loan amount stays the same, with and without points.

One question I have now, is that when you say, for example, that the rate of return is 12.7% in 5 years, does that mean that it's as if having a 12.7% annualized rate of return for those five years?

Also, I downloaded your spreadsheet. Is there a way to use to to compare two different loans both with points, one with less the other more? It seems to be set up to assume that one loan has to have no points on it. This differs from the Mortgage Professor's calculator, where you can simply enter the rate and points for two different loans and compare them. Also, your spreadsheet does not include the cost of the points in the balance of the loan, as I'm planning to do. Is there a way to modify it to do that as well? (I'm really a novice spreadsheet user--I understand the outputs, but a lot of the functions are fairly opaque to me.) I'd like to see the whole chart like in your spreadsheet.

*

#Cruncher, thanks for the examples in the tables. That's helpful. It took me a minute to get it, but I think I understand what you're showing there.

*

Lastly, for now, does it seem like the right way to think about this is that whatever amount of points that has the best rate of return, over the time I expect to be in the house, is the best deal (assuming also that I'm confident I will not sell the house or otherwise payoff the loan before the breakeven time period)? I guess I'm also assuming that rates are so low right now that it's pretty unlikely I will refinance again, perhaps ever (I'm looking at rates in the mid to low 2.x% range).

### Re: Rate of return on mortgage points, calculator, please explain

Good catch! My mistake was I modeled a sale at the start of the period, when it should be at the end of the period. This had the effect of ignoring the last month's interest.#Cruncher wrote: ↑Sat Aug 15, 2020 12:47 pm

- Mortgage Professor's results agree with these four cases.
- The spreadsheet doesn't exactly agree because of a small bug: it omits the final month's payment difference from the cash flow.
- Here are the calculations showing how the 13.18% return for a 5-year holding period is calculated, using the Excel PMT, FV, and RATE functions.
Code: Select all

`20,000 = 480000 * 4.1667% 1,852.70 = PMT(2.323% / 12, 30 * 12, -480000, 0, 0) 2,189.05 = PMT(3.625% / 12, 30 * 12, -480000, 0, 0) 421,304 = FV( 2.323% / 12, 5 * 12, 1852.70, -480000, 0) 431,465 = FV( 3.625% / 12, 5 * 12, 2189.05, -480000, 0) 13.18% = RATE( 5 * 12, 336.34, -20000, 10161, 0) * 12`

Updated spreadsheet: https://drive.google.com/file/d/1rXCwck ... sp=sharing

I also capped interest deductions at $750k federal and $1M state.

### Re: Rate of return on mortgage points, calculator, please explain

If you want to modify my spreadsheet to assume the points are rolled into the loan balance, it would be straightforward - there would be a few formulas that would need to be changed. The cash flows would be accurate, but it would be difficult or impossible to calculate a rate of return. The reason is that rate of return calculation works best with a large cash investment up-front, and then streams of payments coming in later. By rolling the points into the loan, there is no initial investment, just a small savings in the payment. But after a certain point, the loan balance with points will become less than without, so the stream of cash flows will be all positive. An all-positive stream of cash flows has no mathematically defined rate of return. I would recommend just assuming the RoR of this strategy would be just the RoR of the points, minus the interest rate of the loan, because you're effectively borrowing the money for the points and paying interest on it.cb474 wrote: ↑Sat Aug 15, 2020 7:11 pm Thanks to everyone for the technical explanations. I'll have to take some time to digest this, since many of these concepts are new to me.

*

fyre4ce, it's interesting that the loan you recently analyzed showed a breakeven point of 3 years. That's basically what I found just by comparing amortization tables for the different loans I'm considering and adding up the interest paid over different periods of time and the monthly savings on the lower mortgage payment, to figure out at what point is the remaining balance on the two loans is the same (if I get points I'm planning to roll the cost into the loan balance, so that's why I looked at it that way). I guess grabiner's rule of thumb to simply divide the points paid by the amount of reduction in the interest rate would also come up with a 3 year break even point for the loan you analyzed.

I have the extra complication when comparing loans, that if I take the points, as I said, I'm planning to roll the cost into the balance of the loan, so I can't simply use a spreadsheet that assumes the loan amount stays the same, with and without points.

One question I have now, is that when you say, for example, that the rate of return is 12.7% in 5 years, does that mean that it's as if having a 12.7% annualized rate of return for those five years?

Also, I downloaded your spreadsheet. Is there a way to use to to compare two different loans both with points, one with less the other more? It seems to be set up to assume that one loan has to have no points on it. This differs from the Mortgage Professor's calculator, where you can simply enter the rate and points for two different loans and compare them. Also, your spreadsheet does not include the cost of the points in the balance of the loan, as I'm planning to do. Is there a way to modify it to do that as well? (I'm really a novice spreadsheet user--I understand the outputs, but a lot of the functions are fairly opaque to me.) I'd like to see the whole chart like in your spreadsheet.

If you want to compare two different levels of points, that's easy. Put in the interest rates accurately, and just put the points as the

*difference*between the points levels. For example, if your baseline interest rate is 2.625%, and you can get either 2.325% with 0.75 points or 2.0% with 2 points, then first run the analysis with (2.625% + 0 points) vs (2.375% + 0.75 points). Then, run (2.375% + 0 points) vs (2.0% + 1.25 points) and see what the RoR is for going to the next level. The website or spreadsheet won't get the initial cash outlay correct, but it will be the same error from both options, so cancel itself out.

When my spreadsheet or the website report a rate of return of, say, 12.7%, that is annualized. So, if you spent $5,000 in points and got a 12.7% RoR after 5 years, it would be like getting a future payment of $9,091 (= $5000 x (1+12.7%)^5). A better analogy is buying a $5,000 bond that pays $635 (= $5,000 x 12.7%) in interest every year, and then is redeemed for face value after 5 years. Again, this is an excellent investment,

*if*you hold the loan for at least the break-even period.

### Re: Rate of return on mortgage points, calculator, please explain

Thanks again for all the help an explanations.

Or can I just treat the 2.875% loan as zero points and subtract .295 points from the 2.375% loan and treat it as if it has 1.150 points? Would that introduce any errors in the calculate RoR or the cash flow?

Also, I think if you really want to make an apples to apples comparison, perhaps you should assume you will pay extra principal on the loan with points each month, equivalent to the difference in the two loans monthly payments. That way you have two loans with the same monthly payment, but one has a lower interest rate and principal being paid off much faster, albeit with the upfront cost of the points. You're earning both the RoR on the points and the RoR on the extra principal payments made with the extra cash for monthly payments, which saves you the interest you'd otherwise pay on that principal.

Perhaps this explains why when I manually adjusted the beginning balance of the loan with points, in your spreadsheet, to include the points in the balance, the spreadsheet said the rate of return over every month was 0%. I guess that was just signaling a does not computer error.fyre4ce wrote: ↑Sun Aug 16, 2020 2:05 am If you want to modify my spreadsheet to assume the points are rolled into the loan balance, it would be straightforward - there would be a few formulas that would need to be changed. The cash flows would be accurate, but it would be difficult or impossible to calculate a rate of return. The reason is that rate of return calculation works best with a large cash investment up-front, and then streams of payments coming in later. By rolling the points into the loan, there is no initial investment, just a small savings in the payment. But after a certain point, the loan balance with points will become less than without, so the stream of cash flows will be all positive. An all-positive stream of cash flows has no mathematically defined rate of return. I would recommend just assuming the RoR of this strategy would be just the RoR of the points, minus the interest rate of the loan, because you're effectively borrowing the money for the points and paying interest on it.

I don't have a zero points baseline to compare to. That's what I was trying to ask. I'm trying to compare 2.875%, with .295 points, as a baseline, to 2.375% with 1.445 points. The Mortgage Professors calculator allows one to enter points on both of the loans being compared. But your spreadsheet is set up to have the baseline loan be not points. How can I adjust it to allow the baseline loan to also have points?If you want to compare two different levels of points, that's easy. Put in the interest rates accurately, and just put the points as thedifferencebetween the points levels. For example, if your baseline interest rate is 2.625%, and you can get either 2.325% with 0.75 points or 2.0% with 2 points, then first run the analysis with (2.625% + 0 points) vs (2.375% + 0.75 points). Then, run (2.375% + 0 points) vs (2.0% + 1.25 points) and see what the RoR is for going to the next level. The website or spreadsheet won't get the initial cash outlay correct, but it will be the same error from both options, so cancel itself out.

Or can I just treat the 2.875% loan as zero points and subtract .295 points from the 2.375% loan and treat it as if it has 1.150 points? Would that introduce any errors in the calculate RoR or the cash flow?

What's interesting to me is that the advice you see over and over in this forum and online is that you should calculate the breakeven period by dividing the cost of the points by the amount it lowers your monthly payment, to get the months to break even. That tends to come up with break even periods of five to six years or longer. But that seems to just be wrong, because it doesn't include the extra principal being paid each month, with the lower interest rate, even though the overall monthly payment is lower. When you include that, the breakeven periods seem to come out to be more like three or even two years.When my spreadsheet or the website report a rate of return of, say, 12.7%, that is annualized. So, if you spent $5,000 in points and got a 12.7% RoR after 5 years, it would be like getting a future payment of $9,091 (= $5000 x (1+12.7%)^5). A better analogy is buying a $5,000 bond that pays $635 (= $5,000 x 12.7%) in interest every year, and then is redeemed for face value after 5 years. Again, this is an excellent investment,ifyou hold the loan for at least the break-even period.

Also, I think if you really want to make an apples to apples comparison, perhaps you should assume you will pay extra principal on the loan with points each month, equivalent to the difference in the two loans monthly payments. That way you have two loans with the same monthly payment, but one has a lower interest rate and principal being paid off much faster, albeit with the upfront cost of the points. You're earning both the RoR on the points and the RoR on the extra principal payments made with the extra cash for monthly payments, which saves you the interest you'd otherwise pay on that principal.

### Re: Rate of return on mortgage points, calculator, please explain

Yes that's right. I tried to modify the spreadsheet to include financed points, and got erroneous results. As I said, the IRR calculation doesn't work well for certain types of cash flows, and can have multiple solutions, or no solutions. Calculating a Net Present Value for the two options is probably a better choice. NPV doesn't have the computational problems that IRR does; for any arbitrary stream of cash flows, there exists a unique NPV given a discount rate. If you define positive cash flow as payments coming to you, and negative as paying out, the option with the larger NPV will be the better one.cb474 wrote: ↑Sun Aug 16, 2020 4:33 pm Thanks again for all the help an explanations.

Perhaps this explains why when I manually adjusted the beginning balance of the loan with points, in your spreadsheet, to include the points in the balance, the spreadsheet said the rate of return over every month was 0%. I guess that was just signaling a does not computer error.fyre4ce wrote: ↑Sun Aug 16, 2020 2:05 am If you want to modify my spreadsheet to assume the points are rolled into the loan balance, it would be straightforward - there would be a few formulas that would need to be changed. The cash flows would be accurate, but it would be difficult or impossible to calculate a rate of return. The reason is that rate of return calculation works best with a large cash investment up-front, and then streams of payments coming in later. By rolling the points into the loan, there is no initial investment, just a small savings in the payment. But after a certain point, the loan balance with points will become less than without, so the stream of cash flows will be all positive. An all-positive stream of cash flows has no mathematically defined rate of return. I would recommend just assuming the RoR of this strategy would be just the RoR of the points, minus the interest rate of the loan, because you're effectively borrowing the money for the points and paying interest on it.

For NPV calculations, you need to assume a discount rate, which should be the rate of return you'd get on the money if you did something else with it. If you'd otherwise invest surplus money in a 80/20 investment blend, maybe 7% would be a reasonable discount rate.

Yup, you've got it. You subtract out the points for the baseline case. In effect, you could consider them as "closing costs" or "loan origination expenses" or something that would be the same for both loan options. The baseline point payment won't appear in the cash flow, but that's not a problem; you'll still get an accurate IRR.cb474 wrote: ↑Sun Aug 16, 2020 4:33 pmI don't have a zero points baseline to compare to. That's what I was trying to ask. I'm trying to compare 2.875%, with .295 points, as a baseline, to 2.375% with 1.445 points. The Mortgage Professors calculator allows one to enter points on both of the loans being compared. But your spreadsheet is set up to have the baseline loan be not points. How can I adjust it to allow the baseline loan to also have points?If you want to compare two different levels of points, that's easy. Put in the interest rates accurately, and just put the points as thedifferencebetween the points levels. For example, if your baseline interest rate is 2.625%, and you can get either 2.325% with 0.75 points or 2.0% with 2 points, then first run the analysis with (2.625% + 0 points) vs (2.375% + 0.75 points). Then, run (2.375% + 0 points) vs (2.0% + 1.25 points) and see what the RoR is for going to the next level. The website or spreadsheet won't get the initial cash outlay correct, but it will be the same error from both options, so cancel itself out.

Or can I just treat the 2.875% loan as zero points and subtract .295 points from the 2.375% loan and treat it as if it has 1.150 points? Would that introduce any errors in the calculate RoR or the cash flow?

I agree, standard advice here and elsewhere is often misleading. In fact, I didn't realize how much of a difference the faster amortization with paying points makes with respect to the RoR of the points.cb474 wrote: ↑Sun Aug 16, 2020 4:33 pmWhat's interesting to me is that the advice you see over and over in this forum and online is that you should calculate the breakeven period by dividing the cost of the points by the amount it lowers your monthly payment, to get the months to break even. That tends to come up with break even periods of five to six years or longer. But that seems to just be wrong, because it doesn't include the extra principal being paid each month, with the lower interest rate, even though the overall monthly payment is lower. When you include that, the breakeven periods seem to come out to be more like three or even two years.When my spreadsheet or the website report a rate of return of, say, 12.7%, that is annualized. So, if you spent $5,000 in points and got a 12.7% RoR after 5 years, it would be like getting a future payment of $9,091 (= $5000 x (1+12.7%)^5). A better analogy is buying a $5,000 bond that pays $635 (= $5,000 x 12.7%) in interest every year, and then is redeemed for face value after 5 years. Again, this is an excellent investment,ifyou hold the loan for at least the break-even period.

Also, I think if you really want to make an apples to apples comparison, perhaps you should assume you will pay extra principal on the loan with points each month, equivalent to the difference in the two loans monthly payments. That way you have two loans with the same monthly payment, but one has a lower interest rate and principal being paid off much faster, albeit with the upfront cost of the points. You're earning both the RoR on the points and the RoR on the extra principal payments made with the extra cash for monthly payments, which saves you the interest you'd otherwise pay on that principal.

I don't think you need to model extra principal payments to have an accurate analysis. Just calculating a RoR on the points, like we've done here, should be enough. I'm in the minority here at BH by thinking that points are usually a really good investment. A break-even period of ~3 years and an annualized return of 12-15%+ over ~5-30 years seems pretty good to me. I realize there are risks that can cause loss of most of the "principal" (points) but this is common for high-risk investments, and we're usually talking about a 5-10 grand "investment" - not small but not a crippling loss for most. It also has the advantage of reducing the size of a large fixed payment, which is great for cash flow and dealing with financial difficulties. Obviously there are exceptions, mostly home buyers who know or are at high risk of selling and paying off the loan in a short time.

### Re: Rate of return on mortgage points, calculator, please explain

It depends on the break-even, and on your own characteristics; how long do you expect to keep the loan?fyre4ce wrote: ↑Sun Aug 16, 2020 8:11 pm I don't think you need to model extra principal payments to have an accurate analysis. Just calculating a RoR on the points, like we've done here, should be enough. I'm in the minority here at BH by thinking that points are usually a really good investment. A break-even period of ~3 years and an annualized return of 12-15%+ over ~5-30 years seems pretty good to me. I realize there are risks that can cause loss of most of the "principal" (points) but this is common for high-risk investments, and we're usually talking about a 5-10 grand "investment" - not small but not a crippling loss for most. It also has the advantage of reducing the size of a large fixed payment, which is great for cash flow and dealing with financial difficulties. Obviously there are exceptions, mostly home buyers who know or are at high risk of selling and paying off the loan in a short time.

When I took out my mortgage in 2013, I paid an extra 2.25 points on a 15-year loan to lower the rate from 3% to 2.625%. The break-even was about eight years. This made sense to me, because the interest was fully deductible (and still is; I donate enough to charity that I would itemize even without the mortgage), and I didn't expect to pay off or refinance a loan at such a low rate. But I didn't anticipate the future drop in rates. At 3%, I would have paid off the loan in 2019, after six years, and even at 2.625%, I paid it off in 2020 (when the 1.78% after tax was attractive compared to a 1.2% return on municipal bonds). Thus I took a small net loss on the points.

### Re: Rate of return on mortgage points, calculator, please explain

Falling rates are definitely bad for points. There is still some benefit, in that it could still delay or obviate the need for a refinance, which has some cost and hassle. But if you are confident rates will drop quickly, I wouldn't recommend points. If points drop slowly, stay flat, or rise, that's when points see a big benefit.grabiner wrote: ↑Sun Aug 16, 2020 9:38 pmIt depends on the break-even, and on your own characteristics; how long do you expect to keep the loan?fyre4ce wrote: ↑Sun Aug 16, 2020 8:11 pm I don't think you need to model extra principal payments to have an accurate analysis. Just calculating a RoR on the points, like we've done here, should be enough. I'm in the minority here at BH by thinking that points are usually a really good investment. A break-even period of ~3 years and an annualized return of 12-15%+ over ~5-30 years seems pretty good to me. I realize there are risks that can cause loss of most of the "principal" (points) but this is common for high-risk investments, and we're usually talking about a 5-10 grand "investment" - not small but not a crippling loss for most. It also has the advantage of reducing the size of a large fixed payment, which is great for cash flow and dealing with financial difficulties. Obviously there are exceptions, mostly home buyers who know or are at high risk of selling and paying off the loan in a short time.

When I took out my mortgage in 2013, I paid an extra 2.25 points on a 15-year loan to lower the rate from 3% to 2.625%. The break-even was about eight years. This made sense to me, because the interest was fully deductible (and still is; I donate enough to charity that I would itemize even without the mortgage), and I didn't expect to pay off or refinance a loan at such a low rate. But I didn't anticipate the future drop in rates. At 3%, I would have paid off the loan in 2019, after six years, and even at 2.625%, I paid it off in 2020 (when the 1.78% after tax was attractive compared to a 1.2% return on municipal bonds). Thus I took a small net loss on the points.

*Edit:*Even if you're interested in paying points, it pays to analyze the options for your unique case. Some of the lenders I've talked to have very strange point structures, for example, where 0.25 points gets 0.25% interest rate reduction, and a further 1.25 points only reduces rate by a further 0.125%. Each person's tax situation is unique, and with the current tax law, points and/or interest may be only partially deductible, or not at all. With a break-even period of 5 years or less, I'd give it some serious consideration.

### Re: Rate of return on mortgage points, calculator, please explain

Yeah, the points with my lender are not linear at all. At first you pay about .333 points for a 1/8 drop in the interest rate, starting at 2.875%. But the move to 2.375% is cheaper for some reason. After that the points go from about .5 points, to .75, to almost 1.00 points for the subsequent 1/8 interest rate reductions. So that's part of why I'm trying to figure out which is the best deal.fyre4ce wrote: ↑Sun Aug 16, 2020 10:36 pmEdit:Even if you're interested in paying points, it pays to analyze the options for your unique case. Some of the lenders I've talked to have very strange point structures, for example, where 0.25 points gets 0.25% interest rate reduction, and a further 1.25 points only reduces rate by a further 0.125%. Each person's tax situation is unique, and with the current tax law, points and/or interest may be only partially deductible, or not at all. With a break-even period of 5 years or less, I'd give it some serious consideration.

### Re: Rate of return on mortgage points, calculator, please explain

The nonlinearity is common with the first drop. When I was shopping for my mortgage, the first 0.125% reduction could be anywhere from 0.125 to 0.75 points as rates varied daily, but every subsequent 0.125 reduction was 0.75 points. I paid 2.75 points for 2.625%; the lender also offered 2.00 for 2.75%, 1.25 for 2.875%, 0.50 for 3.00%, and 0.00 (not -0.25) for 3.125%. On other days, it was 0.125 for 3.00% and 0.00 from 3.125%.fyre4ce wrote: ↑Sun Aug 16, 2020 10:36 pm Even if you're interested in paying points, it pays to analyze the options for your unique case. Some of the lenders I've talked to have very strange point structures, for example, where 0.25 points gets 0.25% interest rate reduction, and a further 1.25 points only reduces rate by a further 0.125%. Each person's tax situation is unique, and with the current tax law, points and/or interest may be only partially deductible, or not at all. With a break-even period of 5 years or less, I'd give it some serious consideration.

### Re: Rate of return on mortgage points, calculator, please explain

fyre4ce wrote: ↑Sun Aug 16, 2020 2:05 amIf ... the points are rolled into the loan balance, ... it would be difficult or impossible to calculate a rate of return. ... By rolling the points into the loan, there is no initial investment, just a small savings in the payment. But after a certain point, the loan balance with points will become less than without, so the stream of cash flows will be all positive. An all-positive stream of cash flows has no mathematically defined rate of return.

I've modified the little spreadsheet in my previous post adding the options for points to be paid separarely or to be folded into the loan balance. I've also added a column to show the present value of the cash flows after four sample holding periods. This is computable for all possible holding periods; while, as

**fyre4ce**says above, the rate of return can't be calculated for holding periods after the cash flows become all positive. It also allows the breakeven holding period to be computed using Excel's Goal Seek tool.

First are the results where the points are not added to the loan balance. In this case, the returns agree with the Mortgage Professor for the holding period of 2, 15, or 30 years.

**Sample cash flows if points are paid separately**

Code: Select all

```
1 Disc rate 0.000%
2 Borrow points FALSE
3 Loan years 30
4 Base loan 100,000
5 Case 1 Case 2 Diff
6 Rate 2.375% 2.875%
7 Points 1.445% 0.295% 1,150
8 Total loan 100,000 100,000
9 Payment 388.65 414.89 26.24
10 Hold Years Balance Balance Rate PV
11 2 95,317 95,675 358 (10.73%) (162)
12 2.3339 94,513 94,928 415 0.00% 0 [1]
13 15 58,805 60,605 1,800 27.64% 5,373
14 30 0 0 (0) 27.37% 8,296
```

**Sample cash flows if points are folded into loan**

Code: Select all

```
1 Disc rate 0.0000%
2 Borrow points TRUE
3 Loan years 30
4 Base loan 100,000
5 Case 1 Case 2 Diff
6 Rate 2.375% 2.875%
7 Points 1.445% 0.295% 0
8 Total loan 101,445 100,295
9 Payment 394.27 416.12 21.85
10 Hold Years Balance Balance Rate PV
11 2 96,694 95,957 (737) 34.06% (213)
12 2.4615 95,565 94,920 (645) (0.00%) 0 [1]
13 15 59,654 60,784 1,129 #NUM 5,062
14 30 0 0 (0) #NUM 7,865
```

Code: Select all

```
Disc rate 0
Borrow points FALSE
Loan years 30
Base loan 100000
Case 1 Case 2 Diff
Rate 0.02375 0.02875
Points 0.01445 0.00295 =IF(B2,0,B4*(B7-C7))
Total loan =$B4+IF($B2,$B4*B7,0) =$B4+IF($B2,$B4*C7,0)
Payment =PMT(B6/12,$B3*12,-B8,0,0) =PMT(C6/12,$B3*12,-C8,0,0) =C9-B9
Hold Years Balance Balance Rate PV
2 =FV(B$6/12,$A11*12,B$9,-B$8,0) =FV(C$6/12,$A11*12,C$9,-C$8,0) =C11-B11 =12*RATE(A11*12,D$9,-D$7,D11,0) =-PV(B$1/12,A11*12,D$9,D11,0)-D$7
2.33389375310815
15
30
```

- Time to break even. I.e., for the cash flows to add to $0.

**0 = 2.4615 * 12 * 21.85 - 645**. . . . . points folded into loan

**0 = 2.3339 * 12 * 26.24 + 415 - 1150**points paid separately - If you have trouble pasting, try "Paste Special" and "Text".