NPER with daily compounding interest?

    Howdy, folks. I'm familiar with NPER, but I've come to realize that it's compounding interest based on the number of periods, whereas my bank is compounding interest daily. Is there an alternative formula that will calculate the number of periods remaining on a loan that will allow for interest to compound daily, and recognize that payments are made at a different time scale (e.g., biweekly or monthly)?

    Hi,


    For a Daily compound interest rate, it is much safer to go back to the basics, and use the original formula for Future Value :


    '=Loan Amount * (1 + Annual Interest Rate / 365) ^ (Number of Years * 365)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    I can use that for total compounding interest accrued, but it would assume no payments made. I could do a table to build out the loan with number of days, compound the interest, and reduce the amount of principal for each period (and get the number of periods that way), but I am hoping to find the equivalent of NPER that uses daily compounding when considering how many payments will be needed. If NPER is doing this, I'm getting some funny results and need to reconsider my inputs...

    Hi,


    Quite obviously, you are well versed in Finance :)


    You have probably already tested your annual interest rate divided by 30 for bi-weekly constant payments.

    But for a daily compounding interest, you are 100% right, designing your own table will give you the Total flexibility required:


    1. To adjust the payment Amount and Frequency

    2. To take into account Recurring or Irregular Extra Payments

    3. To adjust the original Term and the Remaining Number of Years

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Ha! Just enough to be dangerous. A bit of my education hanging on by a thread.


    This feels like a weird gap on Microsoft's part... they offer the NPER function, but the majority of banks these days accrue interest more frequently than the gap between payments. Shouldn't be hard to build out, IMO... but I don't feel like going through building a custom formula so here we are.

    Should you feel like embarking on a "fully-flexible-and-manual-simulation" worksheet, avoiding altogether Excel functions and relying only on core Finance formulas, do not hesitate to attach your sample file ... ;)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    To Future Forum Readers,


    Just for reference purposes:


    Nper = Log((Pmt/Rate - FV) / (Pmt/Rate + PV)) / Log(1+Rate)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Smiley" icon, below, in the bottom right corner :)

    Like 1

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login