Formula issue office home 2019

Question

I have set up a progressive IMPT daily interest charge table. When I use it, the first answer comes out correct(i believe), but in the second cell with the same formula using the same FV I get a different answer. The only 2 things that change in the formulas is the cell # of the period and the cell # of the FV which adjusts when each principal payment is deducted from the FV. I have 2 examples where the FV stayed the same, but the there are 2 different results. From what I can see the formulas are correct. Here is what I am talking about:

Balance	Daily Int.
$28,741.29
$28,741.29	($1.84)
$28,741.29	($1.63)

Here are the 2 formulas in order: =IPMT(2.34%/365,C3,$A$14*365,I2,) =IPMT(2.34%/365,C4,$A$14*365,I3,) Anyone have any ideas? Anything I am missing? I had this working before but not anymore.

joeuser2004 · Answer

&nbsp;laruso&nbsp; wrote: ``Anything I am missing?``&nbsp;Yes: attaching an Excel file (not an image) with all the data and formulas that demonstrate the problem.&nbsp;If you are worried about posting real dollar amounts, multiply all amounts that are not calculated by a secret number.&nbsp; That will keep the proportions of the results the same.&nbsp;-----laruso&nbsp; wrote:&nbsp; ``I have 2 examples where the FV stayed the same, but the there are 2 different results. [....] the 2 formulas in order: =IPMT(2.34%/365,C3,$A$14*365,I2,) [and] =IPMT(2.34%/365,C4,$A$14*365,I3,)``&nbsp;Presuably, the problem is with the values in C3 and C4 and their relationship to the A14*365.&nbsp;I also suspect that you do not need to and perhaps should not use IPMT for the calculation.&nbsp;&nbsp;Given a daily rate and nper in days, IPMT compounds interest daily.&nbsp; More importantly, it assumes a daily payment (calculated internally using PMT), varying amounts going to principal and interest, which almost certainly does not match reality.&nbsp;(And my wild guess:&nbsp; That probably explains the difference in interest amounts.&nbsp; But I really need to see the details to know for sure and to explain.)&nbsp;Conversely, if interest is indeed compounded daily with daily payments(!), the simple daily interest rate calculation (2.34%/365) might be incorrect.&nbsp;But without the details, I cannot (and should not) say anything with impunity.&nbsp;We need to know the terms of the financial arrangement (line of credit?&nbsp; savings?&nbsp; security; and what type?), ideally as they are stated in the disclosure statement.

laruso · Answer

JoeUser2004&nbsp;Thank you I do appreciate the inputs... I will take a closer look and try a different formula..&nbsp;

laruso · Answer

Finally fixed the problem. For some reason I guess the formula was corrupted after an auto restore. I deleted the first program and just rewrote a new file.. The IPMT formula as It had worked on the previous file worked great on this new file.

joeuser2004 · Answer

laruso&nbsp; 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.&nbsp; 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.&nbsp; 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.&nbsp; C4 can be any of 1695 to 1171, and J4 would appear to be $1.63.&nbsp;-----The correct interest calculation is much simpler. But the details depend on information that you have not shared, as I asked for.&nbsp; 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) - I2depending on the financial terms.For the latter formula, the daily rate is (1+A15)^(1/365) - 1, not A15/365.&nbsp;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.&nbsp;

Forum Discussion

Formula issue office home 2019

4 Replies

Resources