Mortgage Effective Rate formula

Originally Posted by byerspr

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.


-----