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)?
NPER with daily compounding interest?
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)
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...
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
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 ...
To Future Forum Readers,
Just for reference purposes:
Nper = Log((Pmt/Rate - FV) / (Pmt/Rate + PV)) / Log(1+Rate)
Don’t have an account yet? Register yourself now and be a part of our community!