Bruce wrote:

=-CUMIPMT(D4/4, D6/12*4, D3, 1,12,0)

[....]

But I am not confident it is correct

It is not, for many reasons.

Refer to the Excel file "qtr pmt mnthly int.xlsx" (click here). Ignore any preview errors and request to log in. Just proceed to download the file.

-----

First, for 60 months, the endPeriod number is D6/3 = 20, not 12.

-----

Second, for a loan of $100,000 at 5% paid quarterly (every 3 months) for 60 months, the payment is not $5000 (D5).

If the payments were $5000, the total interest would be $5000*60/3 - $100,000, which is zero.

Fortunately (or unfortunately), CUMIPMT calculates the quarterly payment internally, equivalent to:

=PMT(D4/4, D6/3, -D3)

which is about $5682.04 (5682.03896318044).

So if the CUMIPMT paradigm were correct, total interest would be (see the "cumul int" worksheet):

=PMT(D4/4, D6/3, -D3)*D6/3 - D3

or

=-CUMIPMT(D4/4, D6/3, D3, 1, D6/3, 0)

which is about $13,640.78 (13640.7792636088).

The non-robust amortization schedule in columns G:L is a "proof of concept".

-----

But finally, that paradigm assumes that interest compounds quarterly, not monthly. Is that right?

I suspect no: interest compounds monthly.

In that case, the CUMIPMT paradigm is not correct as written, insofar as the quarterly rate is not D4/4.

Instead, the quarterly rate is (1+D4/12)^3 - 1.

So the quarterly payment is (see the "cmpnd int" worksheet):

=PMT((1+D4/12)^3-1, D6/3, -D3)

which is about $5684.99 (5684.99189781671)

And the formulas for total interest are:

=PMT((1+D4/12)^3-1, D6/3, -D3)*D6/3 - D3

or

=-CUMIPMT((1+D4/12)^3-1, D6/3, D3, 1, D6/3, 0)

which is about $13,699.84 (13699.8379563342).

Again, the non-robust amortization schedule in columns G:L is a "proof of concept".

-----

Aside.... Usually, I deprecate the use of IPMT and CUMIPMT because the payment is calculated internally. In the real-world, the payment is usually rounded, often to the dollar.

Also note that those functions do not work correctly for type=1, payments in advance (beginning), which is unusual except for leases.

So instead, I usually prefer equivalent formulas that rely on the FV function. LMK if you need those formulas.

Bruce wrote:

Is there a way to adjust the formula (=-CUMIPMT((1+D4/12)^3-1, D6/3, D3, 1, D6/3, 0) - so that it calculates the interest in the first 12 months

=-CUMIPMT((1+D4/12)^3 - 1, D6/3, D3, 1, 4, 0)

which is 4684.44229310701.

The highlighted arguments are the starting and ending __period__ numbers, and your periods are __quarters__.

Note that your quarters are numbered 1, 2, 3, 4, 5 etc up to D6/3, and each quarter is 3 months (12/4).

So, for example:

2nd 12 months: =-CUMIPMT((1+D4/12)^3 - 1, D6/3, D3, 5, 8, 0)

3rd 12 months: =-CUMIPMT((1+D4/12)^3 - 1, D6/3, D3, 9, 12, 0)

etc

More generally, for "year" in Y1 (1, 2, 3 etc up to D6/12):

=-CUMIPMT((1+D4/12)^3 - 1, D6/3, D3, 4*Y1-3, 4*Y1, 0)

(Here, "year" means 12 months, not a calendar year.)