I'm trying to calculate the number of periods until my assets are depleted
using the following information: Assets equal $88k, interest rate equals 2%,
monthly draw on asset equals $100. When I use NPER it applies the interest
rate to the payment ($100) instead of to the remaining balance on the asset
which is giving me some crazy results! Any suggestions are appreciated.
If 2% is a nominal annual interest rate compounded monthly, so that the
effective monthly interest rate is 2%/12, then you'll never deplete your assets
by taking out $100 per month. The interest earned on 88,000 in the first month
would be $146.66 (rounding down to the nearest cent), and the interest earned on
87,900 in the first month would be $146.50. Either way, your ending balance
would be higher than your initial balance if your monthly draws are only $100.
In general, you'd need a monthly draw larger than the perpetuity draw, original
principal times monthly effective interest rate, in order to deplete principal.
