Compound Interest Calculator with monthly reductions

Hello

I am a novice when it comes to Excel and I would be very grateful if someone could help me with the following please.

I want to create a spreadsheet where some cells act as the input values (just plain cells for reverence) these input values will all be numbers up to a maximum of 2 decimal places for example


Capital Amount 500,000
% Annual Interest Rate 3.00
Duration Years 35
Added Monthly Amount 1,000
Subtracted Monthly Amount 1,800

So basically all the above numbers can be changed (one or more individually at will to see what the overall effect is)
The idea is the first three numbers above act as a standard compound interest calculator over the time period specified. However the last two numbers come into play as amounts many be add/subtracted each month which will effect that months (running balance) and therein the interest earned on said balance (as the balance has either been added to or taken away from).

To basic idea is, if you have X amount of starting capital (500,000) and earn a fixed interest rate of 3% over 35 years the amount will 'compound' up over the 35 years. However you have to take an income (subtracted monthly amount) which would likely be higher than the amount being earned each month in interest (so eventually the money will run out). However even though you are taking money out to pay expensed you are you getting interest each month on what if left in (so the money would last longer than if you had zero monthly interest)

Then now and then you may take a part time job in order to top up the amount (added monthly amount) which although may (or many not be depending on what figures you set) be less than what is being subtracted each month still slows down (or rather extends the time period) before the funds run out.

The above are jut round numbers for example, to explain what I am trying to achieve, and I would be very grateful if someone can help me out where by explain the formulas I need to use, the goal being 'Years Remaining Until Zero (or dips below minus zero) Capital Amount Left"

Thanks everyone for your help
CXMelga

@CXMelga.... Refer to the attached Excel file.

If the monthly deposits and withdrawals are the same(!) for all months over 35 years(!), we might use the example in columns A:B to calculate the ending balance after 35 years.

cmpnd int fv.jpg
Formula:
B6: =-FV(B2/12, B3*12, B4-B5, B1)

Note that B2/12 is the monthly interest rate, and B3*12 is the number of months.

Also note that I assume that deposits and withdrawals occur at the same time. In this case, I assume they are at the end of each monthly period (which does not have to be the end of the month per se).

In this model, I use positive values for deposits and the beginning balance, and negative values for withdrawals. Consequently, the ending balance (FV) is negative. I negate it so that it appears positive, a personal choice.

-----
However, if the monthly deposits and withdrawals might vary over time, or they do not occur at the same time, or we simply want to see the monthly progress of the balance, we must use an amortization schedule such as the one in columns D:I.

cmpnd int amort.jpg
Formulas:
D3: =D2+1
E3: =EDATE($E$2,D3)
H3: =I2*$B$2/12
I3: =I2+F3-G3+H3

For now, I assume the same regular deposits and withdrawals. So F3 has the formula =$B$4, and G3 is =$B$5.

PS.... This is a bare-bones amortization schedule. If you do a google search, you will find many online templates. Some might be more robust; some might not be very good.

-----

Thanks very much, I really appreciate your help

CXMelga