Formula issue office home 2019

laruso  wrote: ``The IPMT formula as It had worked on the previous file worked great on this new file.``

Or is it a case of GIGO?

That is, __if__ IPMT returns the correct amount of interest, it is only an accident of implementation.

But I think it is more likely that IPMT returns incorrect amounts of interest, and you never realized it.

In any case, it is not necessary to use IPMT, and it is simpler if we don't, IMHO.

Apparently, you are not interested in learning what you might be (probably are) doing incorrectly. But for posterity, I think I can demonstrate with the following.

You did not share the details of the financial arrangement, as I asked. So I will make some inferences, just for demonstration purposes.  See the attached Excel file.

Your original calculations appear in columns A:J. For my example, A14 is 1.

But the interesting part is the amortization schedule in columns L:O.

The point is: for IPMT($A$15/365,C3,$A$14*365,I2), IPMT calculates interest for a period (C3) based on the declining balance of a loan of an initial amount (I2) over some number of days (A14*365; presumably A14 is years) at a daily rate (A15/365), with daily payments that are calculated internally effectively by PMT(A15/365, A14*365, I2).

So in general, the amount of interest that IPMT returns is not based on the amount in I2.

Instead, it is based on the reduced balance prior to the period number in C3.

The most reliable exception is when C3 is 1. Only then can we assume that IPMT returns the correct amount of interest.

However, depending on the magnitude of A14*365, additional periods might appear to return the same amount of interest, when rounded. (Their more precise values differ.)

In my example (A14=1 year), C3 can be 1 or 2 (L17:L18). Both would appear to be $1.84 (N17:N18) in J3, when rounded.

Likewise, in order for J4 to appear to be $1.84 when rounded, C4 must be one of the period numbers in the amortization schedule that appears to be the same amount of interest, when rounded.

But in your original example, J4 appears to be $1.63 instead of $1.84. So, C4 must be 43 or 44 (L59:L60) -- if A14 is 1.

As we increase A14, C3 and C4 can be many more period numbers, and they might appear to agree. To demonstrate:

In this example (A14=5 years), C3 and C4 can be any of 1 to 8 (L17:L24), and they would appear to be $1.84 (N17:N24) in J3 and J4.  C4 can be any of 218 to 227 (L234:L243), and J4 would appear to be $1.63 (N234:N243).

FYI, for A14=30 years, C3 and C4 can be any of 1 to 66, and J3 and J4 would appear to be $1.84.  C4 can be any of 1695 to 1171, and J4 would appear to be $1.63.

-----

The correct interest calculation is much simpler. But the details depend on information that you have not shared, as I asked for.  The following makes some assumptions.

If C3 and C4 are dates or period numbers, the interest in J3 can be calculated by

=I2*(C3-C2)*$A$15/365 .

(Note: In my example, since I assume that C3 is 1, I don't know what C2 might be. I leave it empty. So the formula above might not work as intended in my example, since C2 is treated as if it is zero.)

That assumes that simple interest accumulates between scheduled payments. That is the most common financial terms.

However, if interest is compounded daily, the formula in J3 might be

=I2*(1+$A$15/365)^(C3-C2) - I2 or =I2*(1+$A$15)^((C3-C2)/365) - I2

depending on the financial terms.

For the latter formula, the daily rate is (1+A15)^(1/365) - 1, not A15/365.

Again, that depends on the financial terms, which you have not shared. And of course, the calculation of interest in the amortization schedule must also change.