Forum Discussion

laruso's avatar
laruso
Copper Contributor
Oct 09, 2021

Formula issue office home 2019

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: 

BalanceDaily 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. 

4 Replies

  • JoeUser2004's avatar
    JoeUser2004
    Bronze Contributor

     

    laruso  wrote: ``Anything I am missing?``

     

    Yes: attaching an Excel file (not an image) with all the data and formulas that demonstrate the problem.

     

    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.

     

    -----

    laruso  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,)``

     

    Presuably, the problem is with the values in C3 and C4 and their relationship to the A14*365.

     

    I also suspect that you do not need to and perhaps should not use IPMT for the calculation. 

     

    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.

     

    (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.)

     

    Conversely, if interest is indeed compounded daily with daily payments(!), the simple daily interest rate calculation (2.34%/365) might be incorrect.

     

    But without the details, I cannot (and should not) say anything with impunity.

     

    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.

    • laruso's avatar
      laruso
      Copper Contributor

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

      • laruso's avatar
        laruso
        Copper Contributor
        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.

Resources