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...

To calculate the monthly payment for a $5M loan with a 7% annual interest rate compounded daily and a 30-year amortization period:

1. Convert the annual interest rate to a daily rate by dividing it by 360 (the number of days in a year for daily compounding). In this case, the daily interest rate would be 7%/360 = 0.0194%.
2. Calculate the number of payments over the life of the loan by multiplying the number of years by 12 (the number of months in a year). In this case, the number of payments would be 30 x 12 = 360.
3. Use the
   Code:

   PMT

   function to calculate the monthly payment. The syntax for the PMT function is:
   
   
   Code:

   =PMT(rate, nper, pv, [fv], [type])

   whe
   - rate: the interest rate per period (in this case, the daily rate divided by 30 to get the monthly rate)
   - nper: the total number of payments (in this case, 360)
   - pv: the present value of the loan (in this case, $5M)
   - fv (optional): the future value of the loan (usually 0 for a fully amortizing loan)
   - type (optional): when payments are due (0 for end of period, 1 for beginning of period)
   
   So the formula would be:
   
   
   Code:

   =PMT(0.00647, 360, 5000000)

4. Press enter and the result will be the monthly payment for the loan, which in this case is $33,587.24.

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.


"rkhughes" wrote:

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...

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

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.

"pdberger" wrote:

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.


"rkhughes" wrote:

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...

Errata....

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

I meant: it will __not__ fit reality. Doh!

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.

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.

"rkhughes" wrote:
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?

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

"rkhughes" wrote in message
...
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.

PS....

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