Mortgage Effective Rate formula

[byerspr]

I have searched and searched for a formula to calculate what i'm calling the "effective interest rate" based on the pre-payment of a mortgage. The below website gives an effective interest rate if someone were to pre-pay a mortgage for context of what I'm looking to do.
hsh . com / prepayment - refinance - calculator . html


For example, a $350,000 30 yr mortgage at 4%, with an extra $500 applied each month calculates a 2.607% "effective interest rate" paid in 19.33 years. Really all thats being done behind the scenes from what I can tell, is that with the additional $500 monthly payment, the total interest paid over that 19.33 years is about $152,000. Their "effective interest rate" seems to be taking the new total interest of $152,000 and calculating what the rate would have to be on a $350,000 30 yr mortgage to only pay $152,000 in total interest.

To my knowledge, I dont believe there is a way to back into what a rate would be given the total interest paid, and cannot find ANYTHING on the internet in terms of a formula on how this can be calculated.
If there is a way to do this, knowing what that formula would be would be extremely helpful!

Thank you in advance!

[AskMeAboutExcel]

Interesting one. Invoked some headscratching but I've managed to get relatively close with an approximated model - although it doesn't fully reconcile

Table to left - shows mortgage structure with no additional repayments, clearing balance month 360. Excel Solver is used to find the monthly repayment in cell H3

Table in middle - shows mortgage structure with 500 additional repayment per month, clearing balance at year 19.33 as per your calcs above with 152k cumulative interest

Table to right - using same principal repaid as table in middle (but spread over 30 rather than 19.33 years) the annual interest rate in cell AM2 can be backsolved to reach the same cumulative interest paid as table in middle across a 30 year period.

[byerspr]

AskMeAboutExcel,
Thank you for taking the time to respond! This was very helpful!

[AskMeAboutExcel]

not convinced that the logic is watertight though, just a first stab at this

[curiouscat408]

For example, a $350,000 30 yr mortgage at 4%, with an extra $500 applied each month calculates a 2.607% "effective interest rate" paid in [232 months]. [....] Their "effective interest rate" seems to be taking the new total interest of [$152,320.17] and calculating what the rate would have to be on a $350,000 30 yr mortgage

There are a lot of inconsistencies in the calculations on that website. So we might never be able to duplicate their results exactly.

For example, on the one hand, referring to the original 30-yr loan of $350,000 at 4% annually, the website states that after the first regular payment: ``You have already paid $1,166.67 in interest out of a scheduled $251,545.60 total interest due for your loan``.

That is correct if we assume a regular payment of $1670.96 = ROUNDUP(PMT(4%/12,360,-350000),2). The total interest is indeed 360*1670.96 - 350000 = $251,545.60.

On the other hand, the website states that after the first regular payment: ``Without Prepayment... Your loan term remaining is 359 months [and] You have $250,377.77 in scheduled interest yet to pay``.

But 250377.77 + 1166.67 = $250,544.44, not $251,545.60.

I have not yet sussed out the source of the discrepancy. I suspect that the latter incorrect amount is derived from an amortization schedule with some calculations rounded monthly.

No Excel formula can accommodate periodic rounding of annuity calculations.

-----

One thing I'm not sure you realize: for the original 30-yr loan of $350,000, the extra $500 is not applied "each month".

Instead, the first payment in March 2022 is just the regular payment.

The extra payments start in April 2022. See the attached images.

The website does not allow us to specify the same date for the first payment and the start of the extra payments.

That complicates the calculations.

The remaining principal before the April payment is $349,495.71 = 350000 + 1166.67 - 1670.96.

(The first-period interest of 1166.67 = ROUND(350000*4%/12,2).)

Then, with a new regular payment of $2170.96 = 1670.96 + 500, the number of whole months to repay the loan is 232 = ROUNDUP(NPER(4%/12,2170.96,-349495.71),0).

Also note that due to rounding up the payment, as the website seems to do, the last payment after 231 regular payments is
$322.36 = ROUNDUP(FV(4%/12,231,2170.96,-349495.71)*(1+4%/12),2).

So I calculate the total interest for the 232 payments with an extra $500 to be 231*2170.96 + 322.36 - 349495.71 = $152,318.41, not $152,320.17. A small difference.

However, based on your assumption that the "effective interest rate" (a misnomer) is based on 30-yr loan of $350,000 with a total of $152,318.41 in interest, I calculate an annual rate of 2.568% (2.56800546387439%), not 2.607%, with the following formula:

=12*RATE(360, 1395.33, -350000)

where the payment is 1395.33 = ROUNDUP((152318.41 + 350000)/360,2).

I have not yet figured out how the website might have derived 2.607%. It's a work-in-progress.


-----

[byerspr]

Curiouscat408,
I agree with you, there are a lot of inconsistencies on that website. I've used it in the past more of a quick reference to get "close". The roundup formula you came up with works perfectly and that is exactly what I was looking for. Thank you so much for taking the time for the detailed response!

[curiouscat408]

However, based on your assumption that the "effective interest rate" (a misnomer) is based on 30-yr loan of $350,000 with a total of $152,318.41 in interest

@byerspr.... You're welcome.

BTW, the website also states: ``If you want to refinance your remaining $349,495.71 and pay the same $152,320.17 (sic) in total interest cost, you'll need to refinance to a new term below [....] 30-year term [at] 2.599%``

My calculation is 2.57134727332746% = 12*RATE(360,1393.93,-349495.71)

where the payment is 1393.93 = ROUNDUP((152318.41 + 349495.71)/360,2).

Even I substitute the website's total interest ($152,320.17), the 30-yr interest rate is still 2.5714020114147%.

Not close enough to 2.599%. Sigh.

(And that might be different from the website's "effective interest rate" (sic), whatever that is intended to mean.)

[curiouscat408]

I suspect that the latter incorrect amount is derived from an amortization schedule with some calculations rounded monthly.
[....]
So I calculate the total interest for the 232 payments with an extra $500 to be 231*2170.96 + 322.36 - 349495.71 = $152,318.41, not $152,320.17.

I am able to duplicate $152,320.17 by rounding up, not simple rounding, each monthly interest calculation in a normal amortization of a loan of $349,495.71 at 4%/12 monthly with 231 monthly payments of $2170.96 and a last payment of $324.12.

But that does not get us any closer to an annual rate of 2.599% (sic) for a loan of $349,495.71 with 360 monthly payments that results in a total interest of $152,320.17.

Instead, my result is 2.57135026023416%.

And that does not get us any closer to an "effective interest rate" of 2.607% (i.e. between 2.6065% and 2.60749999999999%) for a loan of $350,000 with 360 monthly payments that results in a total interest of $152,320.17.

All of my results with a total interest of $152,320.17 repay the loan in 354 or 355 payments instead of 360.

My best result with 360 payments has a total interest of $154,382.58, $2062.41 too much. And the last payment is $1.94, which seems impractical.