Annual vs Quarterly Rates

[Steve]

When using the PMT function in Excel you typically divide the annual rate by
12 to get monthly compounding. This is OK when the payments are also monthly,
however, how do you calculate quarterly payments with monthly compounding???

 

[Fred Smith]

With all financial functions, the interest rate and the payment period have
to have the same mode (monthly, quarterly, etc.). As your payments are
quarterly, you need to calculate the quarterly-compounding interest rate.

I find it easiest to find the solution by asking the question: If I invested
$1 at x% compounded monthly, how much would I owe after one quarter? This
will tell you what the effective quarterly interest rate is. Calculate it
by:

=fv(Annrate/12,3,0,-1)-1

You can also use the EFFECT function, but I find FV easier to understand.

Regards,
Fred.

 

[joeu2004]

When using the PMT function in Excel you typically divide the annual
rate by 12 to get monthly compounding. [....] how do you calculate
quarterly payments with monthly compounding?

If you are borrower, why would you accept such conditions? You pay
more in interest. If you are US lender, I hope you correctly reflect
the APR in your "Truth in Lending" disclosure. Anyway, to answer you
question....

The effective quarterly rate is the monthly rate compounded for 3
months. It easier to explain by example. Consider a 5-year loan
of $10,000 at 12% compounded monthly, but paid quarterly. The payment
can be computed with one of the following formulas. They are
equivalent; choose the one that is easier for you to understand.

=PMT((1+12%/12)^3-1, 4*5, -10000)

=PMT(FV(12%/12,3,0,-1)-1, 4*5, -10000)

Note: By choosing -10000, PMT() results in a positive number. If
your formulas require a negative result, either use 10000 (without
"-") or write -PMT(...).

Real world consideration: The result should be rounded -- typically
rounded up -- at least to the smallest coin of the realm, typically
"cents". Rounding up ensures that the loan is paid off at least
within the required period without a balloon payment. In either case,
the last payment is usually different from the regular payments; and
if you round up to a dollar or higher (e.g. ten dollars), the number
of payments might be fewer than 4*5.