Discussion Re: Formula issue office home 2019 in Excel
https://techcommunity.microsoft.com/t5/excel/formula-issue-office-home-2019/m-p/2835975#M117600
<P><LI-USER uid="1180367"></LI-USER> wrote: ``The IPMT formula as It had worked on the previous file worked great on this new file.``</P><P><BR />Or is it a case of GIGO?</P><P><BR />That is, __if__ IPMT returns the correct amount of interest, it is only an accident of implementation.</P><P><BR />But I think it is more likely that IPMT returns incorrect amounts of interest, and you never realized it.</P><P><BR />In any case, it is not necessary to use IPMT, and it is simpler if we don't, IMHO.</P><P><BR />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.</P><P><BR />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.</P><P><BR /><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="ipmt misuse 1.jpg" style="width: 581px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/316624i4C445E50E47FF8D0/image-size/large?v=v2&px=999" role="button" title="ipmt misuse 1.jpg" alt="ipmt misuse 1.jpg" /></span></P><P><BR />Your original calculations appear in columns A:J. For my example, A14 is 1.</P><P><BR />But the interesting part is the amortization schedule in columns L:O.</P><P><BR />The point is: for IPMT($A$15/365,C3,$A$14*365,I2), IPMT calculates interest for a period (C3) based on the <EM><U>declining balance</U></EM> of a loan of an <U><EM>initial amount</EM></U> (I2) over some number of days (A14*365; presumably A14 is years) at a daily rate (A15/365), with <EM><U>daily payments</U></EM> that are calculated internally effectively by PMT(A15/365, A14*365, I2).</P><P><BR />So in general, the amount of interest that IPMT returns is <U><EM>not based on the amount in I2</EM></U>.</P><P><BR />Instead, it is <U><EM>based on the reduced balance</EM></U> prior to the period number in C3.</P><P><BR />The most reliable exception is when C3 is 1. Only then can we assume that IPMT returns the correct amount of interest.</P><P><BR />However, depending on the magnitude of A14*365, additional periods might <U><EM>appear</EM></U> to return the same amount of interest, when rounded. (Their more precise values differ.)</P><P><BR />In my example (A14=1 year), C3 can be 1 or 2 (L17:L18). Both would <EM><U>appear to be</U></EM> $1.84 (N17:N18) in J3, when rounded.</P><P><BR />Likewise, in order for J4 to <U><EM>appear to be</EM></U> $1.84 when rounded, C4 must be one of the period numbers in the amortization schedule that <EM><U>appears to be</U></EM> the same amount of interest, when rounded.</P><P><BR />But in your original example, J4 <U><EM>appears to be</EM></U> $1.63 instead of $1.84. So, C4 must be 43 or 44 (L59:L60) -- if A14 is 1.</P><P><BR />As we increase A14, C3 and C4 can be many more period numbers, and they might <U><EM>appear</EM></U> to agree. To demonstrate:</P><P><BR /><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="ipmt misuse 2.jpg" style="width: 588px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/316626i2470079AE741A988/image-size/large?v=v2&px=999" role="button" title="ipmt misuse 2.jpg" alt="ipmt misuse 2.jpg" /></span></P><P><BR />In this example (A14=5 years), C3 and C4 can be any of 1 to 8 (L17:L24), and they would <EM><U>appear to be</U></EM> $1.84 (N17:N24) in J3 and J4. C4 can be any of 218 to 227 (L234:L243), and J4 would <EM><U>appear to be</U></EM> $1.63 (N234:N243).</P><P><BR />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 <U><EM>appear to be</EM></U> $1.63.</P><P> </P><P>-----</P><P>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.</P><P><BR />If C3 and C4 are dates or period numbers, the interest in J3 can be calculated by</P><P><FONT color="#FF0000">=I2*(C3-C2)*$A$15/365</FONT> .</P><P><BR />(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.)</P><P><BR />That assumes that <U><EM>simple interest accumulates</EM></U> between scheduled payments. That is the most common financial terms.</P><P><BR />However, if interest is <EM><U>compounded daily</U></EM>, the formula in J3 might be</P><P><FONT color="#FF0000">=I2*(1+$A$15/365)^(C3-C2) - I2</FONT> or <FONT color="#FF0000">=I2*(1+$A$15)^((C3-C2)/365) - I2</FONT></P><P>depending on the financial terms.</P><P><BR />For the latter formula, the daily rate is (1+A15)^(1/365) - 1, not A15/365.</P><P> </P><P>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.</P><P> </P>Tue, 12 Oct 2021 06:06:06 GMTJoe User2021-10-12T06:06:06ZFormula issue office home 2019
https://techcommunity.microsoft.com/t5/excel/formula-issue-office-home-2019/m-p/2829378#M117326
<P>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: </P><TABLE width="163"><TBODY><TR><TD width="88">Balance</TD><TD width="75">Daily Int.</TD></TR><TR><TD>$28,741.29</TD><TD> </TD></TR><TR><TD>$28,741.29</TD><TD>($1.84)</TD></TR><TR><TD>$28,741.29</TD><TD>($1.63)</TD></TR></TBODY></TABLE><P> </P><P>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. </P>Sat, 09 Oct 2021 06:10:49 GMThttps://techcommunity.microsoft.com/t5/excel/formula-issue-office-home-2019/m-p/2829378#M117326laruso2021-10-09T06:10:49ZRe: Formula issue office home 2019
https://techcommunity.microsoft.com/t5/excel/formula-issue-office-home-2019/m-p/2830135#M117371
<P> </P><P><LI-USER uid="1180367"></LI-USER> wrote: ``Anything I am missing?``</P><P> </P><P>Yes: attaching an Excel file (not an image) with all the data and formulas that demonstrate the problem.</P><P> </P><P>If you are worried about posting real dollar amounts, multiply all amounts that are not calculated by a secret number. That will keep the proportions of the results the same.</P><P> </P><P>-----</P><P><LI-USER uid="1180367"></LI-USER> wrote: ``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,)``</P><P> </P><P>Presuably, the problem is with the values in C3 and C4 and their relationship to the A14*365.</P><P> </P><P>I also suspect that you do not need to and perhaps should not use IPMT for the calculation. </P><P> </P><P>Given a daily rate and nper in days, IPMT compounds interest daily. 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.</P><P> </P><P>(And my wild guess: That probably explains the difference in interest amounts. But I really need to see the details to know for sure and to explain.)</P><P> </P><P>Conversely, if interest is indeed compounded daily with daily payments(!), the simple daily interest rate calculation (2.34%/365) might be incorrect.</P><P> </P><P>But without the details, I cannot (and should not) say anything with impunity.</P><P> </P><P>We need to know the terms of the financial arrangement (line of credit? savings? security; and what type?), ideally as they are stated in the disclosure statement.</P>Sat, 09 Oct 2021 19:14:11 GMThttps://techcommunity.microsoft.com/t5/excel/formula-issue-office-home-2019/m-p/2830135#M117371Joe User2021-10-09T19:14:11ZRe: Formula issue office home 2019
https://techcommunity.microsoft.com/t5/excel/formula-issue-office-home-2019/m-p/2830479#M117397
<P><LI-USER uid="146717"></LI-USER> Thank you I do appreciate the inputs... I will take a closer look and try a different formula.. </P>Sun, 10 Oct 2021 02:33:31 GMThttps://techcommunity.microsoft.com/t5/excel/formula-issue-office-home-2019/m-p/2830479#M117397laruso2021-10-10T02:33:31ZRe: Formula issue office home 2019
https://techcommunity.microsoft.com/t5/excel/formula-issue-office-home-2019/m-p/2832637#M117504
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.Mon, 11 Oct 2021 06:01:20 GMThttps://techcommunity.microsoft.com/t5/excel/formula-issue-office-home-2019/m-p/2832637#M117504laruso2021-10-11T06:01:20ZRe: Formula issue office home 2019
https://techcommunity.microsoft.com/t5/excel/formula-issue-office-home-2019/m-p/2835975#M117600
<P><LI-USER uid="1180367"></LI-USER> wrote: ``The IPMT formula as It had worked on the previous file worked great on this new file.``</P><P><BR />Or is it a case of GIGO?</P><P><BR />That is, __if__ IPMT returns the correct amount of interest, it is only an accident of implementation.</P><P><BR />But I think it is more likely that IPMT returns incorrect amounts of interest, and you never realized it.</P><P><BR />In any case, it is not necessary to use IPMT, and it is simpler if we don't, IMHO.</P><P><BR />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.</P><P><BR />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.</P><P><BR /><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="ipmt misuse 1.jpg" style="width: 581px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/316624i4C445E50E47FF8D0/image-size/large?v=v2&px=999" role="button" title="ipmt misuse 1.jpg" alt="ipmt misuse 1.jpg" /></span></P><P><BR />Your original calculations appear in columns A:J. For my example, A14 is 1.</P><P><BR />But the interesting part is the amortization schedule in columns L:O.</P><P><BR />The point is: for IPMT($A$15/365,C3,$A$14*365,I2), IPMT calculates interest for a period (C3) based on the <EM><U>declining balance</U></EM> of a loan of an <U><EM>initial amount</EM></U> (I2) over some number of days (A14*365; presumably A14 is years) at a daily rate (A15/365), with <EM><U>daily payments</U></EM> that are calculated internally effectively by PMT(A15/365, A14*365, I2).</P><P><BR />So in general, the amount of interest that IPMT returns is <U><EM>not based on the amount in I2</EM></U>.</P><P><BR />Instead, it is <U><EM>based on the reduced balance</EM></U> prior to the period number in C3.</P><P><BR />The most reliable exception is when C3 is 1. Only then can we assume that IPMT returns the correct amount of interest.</P><P><BR />However, depending on the magnitude of A14*365, additional periods might <U><EM>appear</EM></U> to return the same amount of interest, when rounded. (Their more precise values differ.)</P><P><BR />In my example (A14=1 year), C3 can be 1 or 2 (L17:L18). Both would <EM><U>appear to be</U></EM> $1.84 (N17:N18) in J3, when rounded.</P><P><BR />Likewise, in order for J4 to <U><EM>appear to be</EM></U> $1.84 when rounded, C4 must be one of the period numbers in the amortization schedule that <EM><U>appears to be</U></EM> the same amount of interest, when rounded.</P><P><BR />But in your original example, J4 <U><EM>appears to be</EM></U> $1.63 instead of $1.84. So, C4 must be 43 or 44 (L59:L60) -- if A14 is 1.</P><P><BR />As we increase A14, C3 and C4 can be many more period numbers, and they might <U><EM>appear</EM></U> to agree. To demonstrate:</P><P><BR /><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="ipmt misuse 2.jpg" style="width: 588px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/316626i2470079AE741A988/image-size/large?v=v2&px=999" role="button" title="ipmt misuse 2.jpg" alt="ipmt misuse 2.jpg" /></span></P><P><BR />In this example (A14=5 years), C3 and C4 can be any of 1 to 8 (L17:L24), and they would <EM><U>appear to be</U></EM> $1.84 (N17:N24) in J3 and J4. C4 can be any of 218 to 227 (L234:L243), and J4 would <EM><U>appear to be</U></EM> $1.63 (N234:N243).</P><P><BR />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 <U><EM>appear to be</EM></U> $1.63.</P><P> </P><P>-----</P><P>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.</P><P><BR />If C3 and C4 are dates or period numbers, the interest in J3 can be calculated by</P><P><FONT color="#FF0000">=I2*(C3-C2)*$A$15/365</FONT> .</P><P><BR />(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.)</P><P><BR />That assumes that <U><EM>simple interest accumulates</EM></U> between scheduled payments. That is the most common financial terms.</P><P><BR />However, if interest is <EM><U>compounded daily</U></EM>, the formula in J3 might be</P><P><FONT color="#FF0000">=I2*(1+$A$15/365)^(C3-C2) - I2</FONT> or <FONT color="#FF0000">=I2*(1+$A$15)^((C3-C2)/365) - I2</FONT></P><P>depending on the financial terms.</P><P><BR />For the latter formula, the daily rate is (1+A15)^(1/365) - 1, not A15/365.</P><P> </P><P>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.</P><P> </P>Tue, 12 Oct 2021 06:06:06 GMThttps://techcommunity.microsoft.com/t5/excel/formula-issue-office-home-2019/m-p/2835975#M117600Joe User2021-10-12T06:06:06Z