I’m trying to calculate how many months it will take for an account to go to zero, given a starting balance, an interest rate, and a withdrawal rate. I’m using NPER() in Google Sheets and if the interest rate is 0, it calculates it correctly, but when I increase the interest rate, I get a shorter amount of time instead of a longer amount of time.

The function NPER is defined as:

NPER(rate, paymentAmount, presetValue, [futureValue])

So, given the following:

Balance: 1,000,000
Interest Rate: 0% monthly
Withdrawal amount: 16,667 monthly (100,000 yearly)

NPER(interestRate, withdrawalAmount, balance) = 120 months

That is correct.

But if I change the interest rate:

Balance: 1,000,000
Interest Rate: 1% monthly
Withdrawal amount: 16,667 monthly (100,000 yearly)

NPER(interestRate, withdrawalAmount, balance) = 79.24 months

That’s not what I’m expecting. I would expect it to be longer than the original 120 months because while I’m withdrawing monthly, it’s also growing monthly. If I change it to 2% withdrawal monthly, it gets even shorter (61.80 months).

I’m definitely doing something wrong, or maybe NPER() isn’t the correct function to use.