Mortgage with Quarterly Capitalisation & Monthly Payments

[Beth]

Hello

Is there a function I can use in Excel to calculate the payment on a
mortgage where;

1. Interest is calculated daily but capitalised quarterly
2. Payments are made monthly

I have used the PMT function but it does not take into account that the
interest is daily.

Any help would be appreciated.
Thank you

 

[Joel]

Use PMT!

If your interest is 6% yearly, then your daily interest rate is either
..06/360 or .06/365. different countries use different standards. US uses
360 days in the year and number of days in a month as 30. there are 5 days
(or 6 in leap years) that arre bank holidays. Other countries use different
standards making the calculations more complicated.

then use 30 days as the monthly period.

for example

=PMT(.06/365, 30, $1000)

 

[joeu2004]

Is there a function I can use in Excel to calculate the payment on a
mortgage where;
1. Interest is calculated daily but capitalised quarterly
2. Payments are made monthly

I think the answer to your question is "no". But I am having some
trouble understanding your situation and requirements completely.

I have used the PMT function but it does not take into account that
the interest is daily.  

The PMT function can be used only when the payment amount is intended
to be constant for the entire term of the loan.

My understanding of the situation you describe above is: when the
interest is capitalized quarterly, the payment amount "may" change.
In fact, I don't see how it cannot change.

If you have some specific numbers to work with, it might be helpful in
interpreting your situation and requirements, perhaps leading to a
better answer.

Barring that, I would create a monthly amortization schedule. The
details can be provided. But they are somewhat complicated and not
worth the trouble if this solution does not fit your requirements.

I hope you will post back with more information.

 

[Fred Smith]

Right now, you have interest compounded quarterly. As you state, the PMT
function (and, in fact, all financial functions) require the payment and
interest rate be for the same period. Therefore, you need to convert your
interest rate to a compounded monthly rate.

Suppose your annual interest rate is 6%. Your compounded quarterly interest
rate is therefore 1.5%. If you borrowed $100, then one quarter later, you
would owe $101.50. So the question that needs to be answered is: what
monthly interest rate turns $100 into $101.50 after three months? The Rate
function will answer this, as in:

=rate(3,0,100,-101.50)
=0.498%

Use this in your PMT function, as in:

=pmt(rate(3,0,100,-101.50),nper,pv,fv,type)

Regards,
Fred.

 

[joeu2004]

Suppose your annual interest rate is 6%. Your compounded
quarterly interest rate is therefore 1.5%. If you borrowed $100,
then one quarter later, you would owe $101.50.

Doesn't that analysis assume that the principal remains at $100 for
the entire quarter?

Doesn't the fact that we are making monthly payments and interest is
calculated daily change your assumption?

Moreover, are you assuming a constant payment amount for the entire
term of the loan? ("nper" in your formula?) Is that a valid
assumption for this type of loan?

I don't know. But a google search for "define: capitalization"
indicates that the term means that the unpaid interest is added to the
principal, and the payment "may" change.

If the payment does not change, I ass-u-me that means that the balloon
payment increases when the loan matures. But I have trouble with that
interpretation.

Whadaya think?


----- original posting -----

 

[Fred Smith]

You're looking for problems which don't exist. While compound interest has
complexities, you don't have to make it harder than it is. When you are
converting the compounding period, the type of loan, and its payment
structure have nothing to do with it.

You just have to look at the results to realize the validity of the
calculation.
If the annual interest rate is 6%, and the loan was compounded monthly, the
monthly rate would be 0.5%
In this case, with interest compounded quarterly, the monthly rate turns out
to be 0.498%. That makes sense.

Take a look at the documentation for the holy grail of financial
functions -- the HP12C calculator. You'll see it does these conversions the
same way.

Regards,
Fred.

Suppose your annual interest rate is 6%. Your compounded
quarterly interest rate is therefore 1.5%. If you borrowed $100,
then one quarter later, you would owe $101.50.

Doesn't that analysis assume that the principal remains at $100 for
the entire quarter?

Doesn't the fact that we are making monthly payments and interest is
calculated daily change your assumption?

Moreover, are you assuming a constant payment amount for the entire
term of the loan? ("nper" in your formula?) Is that a valid
assumption for this type of loan?

I don't know. But a google search for "define: capitalization"
indicates that the term means that the unpaid interest is added to the
principal, and the payment "may" change.

If the payment does not change, I ass-u-me that means that the balloon
payment increases when the loan matures. But I have trouble with that
interpretation.

Whadaya think?


----- original posting -----

 

[joeu2004]

You're looking for problems which don't exist.

Wouldn't be the first time!

You just have to look at the results to realize the validity
of the calculation.

I did just that, and I confirmed that the structure of your solution
is indeed correct. Live and learn!

The daily computation of interest introduces only a slight numerical
error in the end, which of course can be compensated for in real life
by adjusting the last payment.

I would suggest one minor change to your formulation. I would compute
the monthly rate with RATE(3,0,-1,1+6%/4). Arguably, just a style
difference. But I think it is less error-prone than RATE(3,0,1,-1-6%/
4).

Thanks for the clear explanation. I hope the OP benefitted as much as
I did.

 

[Fred Smith]

I agree. Your rate formula is actually the one I use. When I'm explaining it
to others, I use
$100 rather than $1, because I find they understand it better ($101.15 is an
easier number to understand than $1.0015). I also agree with your choice of
signs. For posterity, we can also show the more general formula:

=Rate(12/CompoundingPeriodsPerYear,0,-1,1+AnnualRate/CompoundingPeriodsPerYear)

Regards,
Fred

You're looking for problems which don't exist.

Wouldn't be the first time!

You just have to look at the results to realize the validity
of the calculation.

I did just that, and I confirmed that the structure of your solution
is indeed correct. Live and learn!

The daily computation of interest introduces only a slight numerical
error in the end, which of course can be compensated for in real life
by adjusting the last payment.

I would suggest one minor change to your formulation. I would compute
the monthly rate with RATE(3,0,-1,1+6%/4). Arguably, just a style
difference. But I think it is less error-prone than RATE(3,0,1,-1-6%/
4).

Thanks for the clear explanation. I hope the OP benefitted as much as
I did.

 

[Junge]

I find it more understandable for pupils to let the signs depend of whether
you are the debitor or the creditor.
Here talking about mortgage I guess you are the debitor receiving the $100
and paying the $101,50.
The signs therefore must be +100(for filling your pocket), and -101,5(for
leaving your pocket)