Monthly Compound Interest with monthly withdrawal formula question

Originally Posted by Nic_s

I'm using this formula:
Answer = BaseValue*(1 + Interest)^NumberOfMonths
This translate to for example:
313842.8377 = 100000*(1 + 0.10)^12

First, your title says that you want to calculate "monthly compound interest". But that formula calculates the "future value": principal plus compounded interest. The formula for compound interest only would be:

=100000*(1 + 0.10)^12 - 100000

Second, it is unlikely that that is the correct formula. If you are compounding monthly, the interest rate must be a monthly rate. I suspect that 0.10 (10%) is an annual rate.

Typically, the (simple) annual rate is divided by 12 for monthly compounding periods. However, beware: sometimes the annual rate is stated as a compounded rate (usually called "yield"; sometimes calls APR, which stands for annual percentage rate -- confusing terminology!). In that case, the monthly rate would be (1+annualRate)^(1/12) - 1.

Let's assume a simple annual rate. So the formula for just compound interest would be:

=100000*(1 + 10%/12)^12 - 100000

Originally Posted by Nic_s

How do I work say a 1000 withdrawal into that for every month?

It is much easier if you use the Excel financial functions. For your formula above (calculating interest only):

=FV(10%/12, 12, 0, -100000) - 100000

Note that Excel financial functions require signed amounts: opposite signs for inflows and outflows. It is arbitrary which sign (plus or minus) that we use for inflow. I choose based on convenience.

To account for withdrawals:

=FV(10%/12, 12, 1000, -100000) - 100000

Note that that returns a negative number(!). That is because the monthly interest on 100,000 (about 833.33) is not sufficient to cover the withdrawal. In other words, your savings account has a reducing balance.

PS.... We can calculate how many months before the account is reduced to zero:

=NPER(10%/12, 1000, -100000)