PMT: Daily interest compounding; monthly payment

[rkhughes]

Data: $5M loan
Rate: 7% annual; compounded daily (360 day year)
Amort: 30 years

How to calculate MONTHLY payment?

Can't figure out how to structure PMT function with different compounding
period than payment period...

 

[pdberger]

RK --

Try this:

A B
1 Loan $5,000,000
2 Ann Rate 7%
3 Yrs 30
4 Pmt =PMT(B2/12,B3*12,B1)

Should do it. HTH.

 

[joeu2004]

Data: $5M loan
Rate: 7% annual; compounded daily (360 day year)
Amort: 30 years

How to calculate MONTHLY payment?
Can't figure out how to structure PMT function with different
compounding period than payment period.

If you assume 360 days per year -- i.e. 30 days per month -- the following
structure is probably what you are expecting:

=roundup(pmt(fv(7%/360, 30, 0, -1)-1, 12*30, -5000000), 2)

Some people prefer the more efficient expression (1+7%/360)^30-1 instead of
the FV(...)-1 expression above. Choose whichever you understand better.

My problem with computing the payment in that way is that it will fit
reality. That is, I presume that: (a) the daily interest rate is really
7%/365 (and perhaps 7%/366 in leap years); and (b) in any case, daily
interest will be compounded for the true number of days in a period (or
between payments). Consequently, do not expect all the financial functions
and annuity schedule to be copacetic.

 

[rkhughes]

No, that doesn't solve the problem. Rate/12 compounds the interest MONTHLY
not DAILY. But Rate/365 calculates a DAILY payment, not a MONTHLY one.

 

[joeu2004]

Errata....

My problem with computing the payment in that way is that it will fit
reality.

I meant: it will __not__ fit reality. Doh!

 

[joeu2004]

If you assume 360 days per year -- i.e. 30 days per month -- the following
structure is probably what you are expecting:
=roundup(pmt(fv(7%/360, 30, 0, -1)-1, 12*30, -5000000), 2)

I used 7%/360 and 30 because you wrote "Rate: 7% annual; compounded daily
(360 day year)".

Rate/12 compounds the interest MONTHLY
not DAILY. But Rate/365 calculates a DAILY payment,
not a MONTHLY one

(I presume you mean that Rate/365 calculates the daily interest rate -- or
"compounds the interest daily", in your parlance.)

If you want to assume a 365-day year (and perhaps 366 in leap years), the
following is the best closed-form formula that I know of:

=roundup(pmt(fv(7%/365, 365/12, 0, -1)-1, 12*30, -5000000), 2)

Again, you might prefer (1+7%/365)^(365/12) instead of FV(...). And
arguably, you might substitute (3*365+366)/48 for 365/12 -- a slightly better
estimate.

In either case -- 7%/360 or 7%/365 -- the result will not fit reality. (Did
I remember to say "not" this time? ;-) If you create a 360-month annuity
schedule, you will discover a large balloon payment in the last period.

You could tweak the payment upward until the last payment is less than or
equal to the regular monthly payment.

 

[rkhughes]

Thank you for taking the time to understand my question and respond. The
calculation you offered was helpful. And it came out close to the bank's
figure.

After tweaking it various ways, the following seems to match the bank
payment a bit better (but still not exactly):

=PMT(Rate/360,360*(365/12),$C$16,,1)*(365/12)

Which basically calculates the daily payment and then multiplies it by the
average number of days in a month.

As you point out, the problem is getting it to fit with "reality" (by which
I mean: "the bank's number")

Thank you again for your help.

RKH

 

[joeu2004]

the following seems to match the bank
payment a bit better (but still not exactly):
=PMT(Rate/360,360*(365/12),$C$16,,1)*(365/12)
[....]
As you point out, the problem is getting it to fit with "reality" (by which
I mean: "the bank's number")

It would be nice if you shared __all__ the factual information that you have.

What is the payment amount determined by the bank?

 

[NickHK]

Maybe you could approach your bank for an explanation of how they arrive at
the required payments.
I would hope they are under some obligation to make such calculation public
knowledge, although possibly not on a large sign on the wall.
Also, the banking regulator for your jurisdiction may be useful in
explanation the method(s) that apply locally.
I have found such requests fruitful in the past, although I have had no need
so far with the banking sector.

NickHK

 

[joeu2004]

PS....

It would be nice if you shared __all__ the factual information that you have.
What is the payment amount determined by the bank?

Actually, more than that:

1. The amount of regular payments.
2. The number of regular payments (excluding the last payment, if irregular).
3. At least one of the following (and preferably both, to double-check):
a. The amount of the last payment.
b. The total amount of all payments.
4. Any information that the lender provided about the daily interest rate.