Home mortgage amortization with two different interest rates?

[Buzz L.]

Hey, folks!

I was wondering if anyone could help me use Excel to solve this problem. I
sure would appreciate it! I've been told by a mortgage broker that I've
qualified for the "special deal" (yeah right!) and instead of paying a PMI
(mortgage insurance) that my interest rate will be 0.375% higher at the
beginning of the loan and will drop to the standard rate after I've achieved
20% equity in the property I'm considering. The broker then extols the
virtues of writing off the interest and how wonderful life will be if I take
this route. I want to calculate it out because I think he's full of it.

So, is there a way to calculate with Excel an initial rate (mine would be
6.497%) and, after I've achieved a 20% equity, drop the rate to 6.122% and
see what the total interest would be?

The whole situation gets sticky when you take into consideration tax
concerns and stuff but I'll worry about that later. I just want to find out
upfront if paying $30 per month for mortgage insurance is cheaper than the
extra interest on the loan.

If anyone could help I sure would appreciate it!

Thanks.

Buzz

 

[Domenic]

I'm not in the mortgage business, but here are a few thoughts...

If the criteria for the drop in interest rate were based on paying off
20% of your mortgage instead of achieving 20% equity in your property,
then one could make the necessary calculations and compare one against
the other.

But since the criteria is based on achieving 20% equity in your
property, the calculations would be made difficult since property values
fluctuate according to market conditions. Also:

1) Who will determine the property's worth?

2) Who will pay for an appraisal, if one is needed?

Obviously, the longer it takes to achieve the target, the more interest
you'll end up paying. And worse, if property values decline during the
term of your mortgage and you never achieve the target, no savings will
be had.

In the face of such uncertainties, I wonder if that deal is really all
that "special".

 

[Buzz L.]

Excellent points! As far as I understand, however, the 20% equity is based
solely on the initial purchase price (190,000 in my case). I will put down
19,000 and will borrow 171,000. When I've paid my principle down from
171,000 to 152,000 then the rate will go from 6.497% to 6.122%.

Is that what you were saying would be easy to calculate? I suppose that
152,000 is approximately 11.1% of the actual borrowed amount. I think
that's right. If you can tell me how to calculate that in Excel I'd really
appreciate it!

Thanks for your help!

Buzz

 

[Domenic]

Assuming that interest is compounded monthly and your mortgage is
amortized over 25 years...

1) Interest paid over 25 years @ 6.497% is $175,285.1133:

=CUMIPMT(6.497%/12,25*12,171000,1,300,0)

2) Interest paid over 25 years @ 6.122% is $163,362.9141:

=CUMIPMT(6.122%/12,25*12,171000,1,300,0)

3) Total interest paid at the rate of 6.497% at first and 6.122% after
target is achieved is $160,497.4570 ($59,770.8901 and $100,726.5669,
respectively).

It will take you 68.8480852 months (payments) to reduce your principle
to $152,000...

=NPER(6.497%/12,PMT(6.497%/12,25*12,171000),171000,-152000)

therefore, interest paid to that point based on 6.497% is $59,770.8901:

=CUMIPMT(6.497%/12,25*12,171000,1,68.8480852,0)

It will take you 218.9466653 months (payments) to pay off your mortgage
based on your monthly payment remaining the same at $1,154.2837 and your
reduced interest rate of 6.122%:

=NPER(6.122%/12,-1154.28,152000,,0)

Therefore, interest paid from the time your interest rate is reduced
until your mortgage is paid off is $100,726.5669:

=(218.9466653*1154.2837)-152000

Hope this helps!

 

[Buzz L.]

Thanks, Domenic...

How did you arrive at your conclusion for step 3?

Thanks!

Buzz

 

[Domenic]

How did you arrive at your conclusion for step 3?

We need to calculate interest paid for two periods.

The first period is from the beginning of the loan until your principle
is reduced to $152,000 at a rate of 6.497%.

The second period is from the time the target is reached until the loan
is paid off at a rate of 6.122%.

First period...

Before we can use CUMIPMT to calculate the interest paid for the first
period, we need to know how many periods (payments) it will take to
reduce the balance to $152,000. For that we can use NPER.

It will take you 68.8480852 months (payments) to reduce your principle
to $152,000:

=NPER(6.497%/12,PMT(6.497%/12,25*12,171000),171000,-152000)


Interest paid to that point based on 6.497% is $59,770.8901:

=CUMIPMT(6.497%/12,25*12,171000,1,68.8480852,0)

Second period...

In order to calculate the interest paid for the second period, we need
to know how many payments it will take to pay off the mortgage with
continuing payments of $1,154.2837 and at the new rate of 6.122%.

It will take you 218.9466653 months (payments) to pay off your mortgage:

=NPER(6.122%/12,-1154.28,152000,,0)

So, if it's going to take you 218.9466653 payments of $1,154.2837 to pay
of your loan, that means you'll pay in total $252,726.5669.

$252,726.5669 less $152,000 gives you $100,726.5669, which represents
the interest paid for the second period.

Then, to get the total interest paid for both periods, add the two and
you'll get $160,497.4570 ($59,770.8901 and $100,726.5669)

The conclusion...

If you opt for the standard rate of 6.122% for the 25 years, you'll pay
$163,362.9141 in interest.

If you opt for the special deal, you'll pay $160,497.4570 in interest
over the life of the mortgage.

So, it would appear that you would save $2,865.4571 in interest over the
life of the mortgage by opting for the special deal.

That's possible because, while your monthly payment is more at the
beginning due to the higher interest rate, more of the monthly payment
will go towards the principle after the interest rate is reduced and
your monthly payment remains the same. This accelerated payment would
be in effect for the majority of the life of the mortgage giving you the
benefit of such savings.

 

[Buzz L.]

Thanks so much, Domenic! I really appreciate it. I'm off to study those
formulas some more and see if I can't whip up a good spreadsheet to play
with them a bit. I'm not sure how the mortgage company may do it but I
would imagine that they'd tell me I had a lower payment after they dropped
my rate to keep their $$$ coming in. I'll have to stay "on guard" and keep
up the standard payment to receive those extra benefits!

Thanks again for all of your help!!!

Buzz

 

[Domenic]

I'm not sure how the mortgage company may do it but I
would imagine that they'd tell me I had a lower payment after they dropped
my rate to keep their $$$ coming in.

If they lower your payment after dropping your rate, then that would
definitely change things.

Ensure that the monthly payment is based on the higher interest rate to
begin with, then ensure that the monthly payment doesn't change when the
interest rate drops.

And have your mortgage company confirm for you how long it would take to
pay off the mortgage. The last thing you want is any surprises.

 

[Norman Harker]

Hi Buzz!

Re: I'm not sure how the mortgage company may do it

The mortgage company probably uses daily compounding on the
outstanding balance.

I have a model that does this and which allows variable rates and
additional payments. I'll send a copy on direct request to email
address below. It seems that this could be adapted to do what you want
by using an IF function to vary these two elements.

It's completed all bar finalisation of the notes on formulas used.