Mtge calculation (Dly compound interest and multiple interest rate

[Spudson]

All,

I'm based in the UK and I'm trying to get to the bottom of a dilemma that
I'm facing - "How do I calculate how much the monthly repayment will be for a
loan that features two interest rates?"

I know the following:

Principal - £50,000
Interest compounded - Daily (probably 360 days)
Interest rate - 5% for 10 years
Reverting to - 7% for 5 years
Total mortgage duration - 15 years

I've already tried the following function
"PMT((1+5%/360)^(360/12)-1,15*12,-50000)", but this only allows for one
interest rate and doesn't account for the rate change, later in the term.

Any pointers???

Many thx.

S

 

[sebastienm]

Hi,
I am not completely sure of bellow, maybe someone else can correct me if
wrong:

When you pay a mortgage, a portion of the payment pays for the principal,
the rest is for the interest; these 2 portions are given by PPMT and IPMT
PMT(...) = PPMT(...) + IPMT(...) (check online help for these functions)
and the vary at each payment period.
By summing PPMT over the 10yrs of payment, you should get the overall
portion of the Principal you have paid, therefore "Principal - Sum(PPMT)"
gives you what is left to pay , which should be the amount on which you have
to apply the new rate for 5 years (equivalent to borrow this amount at new
rate for 5 yrs).

I hope it puts you a bit closer to the right track.

 

[Spudson]

Thx Sebastien.

When I use this function, I cannot seem to get it to work effectively for
the capitalisation of daily interest. It likes annual interest - but throws
a wobblie when I incorporate the daily calculation.

Any other suggestions??

S