mortgage interest

[Tracy]

Hi, What is the correct excel formula to calculate the monthly interest on
an interest only mortgage of £100,000 at a rate of 3.75% per year where the
interest is calculated daily?
At present I use

=((C22*C19)/12)

where C22 is £100,000 and C19 is 3.75% but the result is not the same as
what I am being charged each month.
Thanks, Tracy

 

[macropod]

Hi Tracy,

In part, the answer depends on how fequently interest is charged. Although it might be calculated daily, it might only be charged
monthly. Plus, some financial institutions calculate the daily & monthly interest on the basis of a year having 12 30-day months, so
interest for a 365 or 366 day year is calculated as if there were only 360 days (30 per month, even for February).

 

[B. R.Ramachandran]

Hi,

=C22*((1+C19/366)^30)-C22 (for a 30-day month in 2008, a leap year)

(for a 31-day month, change the "30" to 31. For a non-leap year, change the
"366" to 365)

Regards,
Ramachandran

 

[joeu2004]

What is the correct excel formula to calculate the monthly interest
on an interest only mortgage of £100,000 at a rate of 3.75% per
year where the interest is calculated daily? At present I use
=((C22*C19)/12)
where C22 is £100,000 and C19 is 3.75% but the result is not
the same as what I am being charged each month.

The answer might depend on the terms of your loan and the customs of
your jurisdiction (UK?). You have the correct formula for the
"typical" interest-only loan.

It would help us help you if you provided the amount you are actually
being charged. Otherwise we are just making wild-ass guesses about
the terms of the loan.

Some other questions to answer:

1. Is this a fixed-rate or variable-rate loan (specify which)?

2. Is the amount "charged each month" constant or variable (specify
which)?

3. Did the lender specify another rate -- usually called the effective
rate or APR -- which is based solely on the terms of the amortized
loan (i.e. principal, interest rate, loan term, calculation frequency)
excluding any up-front fees and payments?

4. Does your loan contract explain how the monthly payment is
calculated?