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

" wrote:

I wrote:
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)".

But in a response, you wrote:
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.